Reading Cell Value in Excel Addin C#

Value:

Excel COM offers various methods to pick a value from cell but as it is build on strong type hence, it is always Null Reference Exception which bothers to a developer. Sometimes if developer uses .Text it gives an error and need to use .Value when developer wants only checking or manipulating the text within a cell. Sometimes developer uses .Value gets an error and need to use .Text. Usually it accepts either or without an issue, but sometimes it does make a difference. Let’s figure it out.

Excel offers followings to read a value from a Cell in Excel Worksheet:

• Text
• Value2
• Value

Text:

.Text gives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get ####

Value2:

.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)

Value:

.Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date. Using .Value or .Text is usually a bad idea because you may not get the real value from the cell, and they are slower than .Value2

Common way to read value:

private string ReadValue(Excel.Range oRange)
{
	return (string)oRange.Cells[1,1].Text;
}

This is just not end of the world, DOT NET offers rich classes and object model to parse values having methods to perform on the fly casting like TryParse, Convert which you can use to transform your values in desired format.

Share on Facebook

Tweet