Value, Text and Value2 Excel Addin C#

Ways of retrieving values from an object (Cells or Range):

Prerequisites:

• Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
• Create Excel Addin in C# code style (Visual Studio Tools for Office)
• Create a ribbon designer and put button

When we work on an enterprise project or deep programming and come across various issues like Casting, Value Conversions, Getting null value from cell however cell has values. Also, a VSTO or COM Developer always make VBA as a benchmark to build the code which is obvious. Excel offers three ways to read values from a cell. Lets take a look one by one with very standard definitions then we will look for real life scenario.

1. .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 ####
2. .Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)
3. .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

Scenario -1:

Problem statement (applies to C# VSTO COM Addins): You are trying read value from a cell and cell column is hidden (Note: cell contains value) now you try to access the same using code by using .Text property. Surprise!!! the returned value is “” (Empty string). The same I tried in Excel VBA and I get result. Put the head more inside the screen and try understanding what is wrong.

Solution: Very odd but practically true if you change property from .Text to .Value and hold it in a var type object, you get the output :)))))

Code Example:

public static void SomeOperation()
{
	var objVal=xlSheet.Cells[1,1].Value;
	//Now cast your value as per your wish from var
}

Scenario -2:

Problem statement (applies to C# VSTO COM Addins): Casting issue, When you play with Excel Sheet using C# VSTO and reading data you will realize there is static method which can help you picking the data straight from the Cell, some times it is unable to cast the value of cell, some times null exceptions and so on. Then question comes which common standard property (.Text, .Value, .Value2) developer should apply to read any type values from a cell without any exception or error?

Solution: Always read the value in string format by explicit cast and the code would be like this:

public static void SomeOperation()
{
	string strVal=(string)(xlSheet.Cells[1,1] as Range).Text;
}

Above method can read any type of value from excel sheet’s cell without any exception, it could be any possible number, string, or empty cell.

Next>>Closing parent window from Child Window C# (Threading with WPF MVVM) with Win APIs

Share on Facebook

Tweet