Cell Data Types

  • 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.

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