Cell Data Types

• Mar 19, 2021
• 2 minutes to read

A cell value is stored in the CellValue object and can be accessed by the Cell.Value property. You can specify a value in two ways:

• directly, by setting the Value property: cell.Value = 10;
• as the result of a formula (Formula): cell.Formula = "= SUM(3,7)";

The Spreadsheet determines the type of the inserted/calculated data and sets the cell’s Type property value. Use the appropriate TypeValue property to get the cell value.

The available cell value types are listed below.

Type	Identify the Type	Value	Sample Input	Display Text
None	IsEmpty	-	cell.Value = null;	-
Numeric	IsNumeric	NumericValue	cell.Value = 12345678; cell.NumberFormat = "#,#";	12,345,678
DateTime	IsDateTime	DateTimeValue	cell.Value = new DateTime(2012, 12, 10); cell.NumberFormat = "m/d/yy";	12/10/12
Text	IsText	TextValue	cell.Value = "Sample Text";	Sample Text
Boolean	IsBoolean	BooleanValue	cell.Value = true;	TRUE
Error	IsError	ErrorValue	cell.Formula = "=5/0";	#DIV/0!

Number Format

You can format Numeric and DateTime cell values. For instance, a number can appear in a cell as a percentage, decimal, currency, accounting, date or time value.

Assign a format pattern to the Cell.NumberFormat property to format a value in a cell. For more information, please review the following topic: How to: Specify Number or Date Format for Cell Content.

Use the Cell.DisplayText property to get the formatted value as it is displayed in a cell.

C#

worksheet.Cells["A1"].Formula = "= Now()";
worksheet.Cells["A1"].NumberFormat = "m/d/yy h:mm";
// worksheet.Cells["A1"].DisplayText returns "5/20/2020 13:08"

worksheet.Cells["B1"].Value = 1.6;
worksheet.Cells["B1"].NumberFormat = "0.0%";
// worksheet.Cells["B1"].DisplayText returns "160.0%"

Note

Some settings, such as the group separator or decimal separator, depends on the culture used in your application. If you need to create culture-independent settings, apply recommendations from the following KB article to adjust them: How to create culture-independent settings in a project.

Online Demo

Cell Value