Cell Data Types
- 4 minutes to read
Each cell in a worksheet has a value that is specified by the CellValue object. To access this object, use the CellRange.Value property. A cell value is determined by the data contained within the cell:
- If the cell does not contain any data, the cell value is empty (CellValue.IsEmpty is set to true). Refer to the How to: Clear Cells of Content, Formatting, Hyperlinks and Comments example to learn how to remove cell contents.
- The cell value is determined by a constant assigned to the cell via the CellRange.Value property. In this case, the cell value is neither calculated nor changed.
- The cell value is determined by a value resulting from a formula assigned to a cell via the CellRange.Formula property. In this case, the cell value is calculated dynamically.
A cell value can be of one of the following types: empty, numeric, text, Boolean or error. Cell values may have various display formats. For example, a numeric value can be displayed as a decimal number, a percentage or currency value, a date or time value, etc.
Use the properties of the CellValue object returned by the CellRange.Value property to retrieve information about the cell value type, and get the cell value itself as an object of the corresponding type. To get the string specifying the formatted value as it is displayed in a cell, use the Cell.DisplayText property.
The table below lists the available cell value types and provides examples on how a value of each type can be input, formatted, displayed and obtained.
Cell Value Type
Sample Display Format
Identify the Type
Obtain the Cell Value Object
The default cell value type.
For details, refer to the How to: Clear Cells of Content, Formatting, Hyperlinks and Comments document.
cell.Value = null
cell.Value = CellValue.Empty
CellValue.IsEmpty = true
The cell's CellRange.Formula property is assigned to an expression that returns a number.
cell.Value = 12345678
cell.Formula = "= SUM(12000000,345678)"
cell.NumberFormat = "#,#"
CellValue.IsNumeric = true
(Date and Time)
The cell's CellRange.Formula property is assigned to an expression that returns the serial number of a date or time.
For details, refer to the Dates and Times in Cells document.
cell.Value = new DateTime(2012, 12, 10);
workbook.DocumentSettings.Calculation.Use1904DateSystem = true;
cell.Value = CellValue.FromDateTime(new DateTime(2012, 12, 10), true);
cell.Value = 41253;
cell.Formula = "=DATE(2012,12,10)";
cell.NumberFormat = "m/d/yy"
CellValue.IsNumeric = true
CellValue.IsDateTime = true
The cell's CellRange.Formula property is assigned to an expression that returns text.
cell.Value = "Sample Text"
cell.Formula = "= PROPER("sample text")"
CellValue.IsText = true
The cell's CellRange.Formula property is assigned to an expression that returns TRUE or FALSE.
cell.Value = true
cell.Formula = "= TRUE()"
CellValue.IsBoolean = true
The cell's CellRange.Value property is assigned to the CellValue object returned by the CellValue.Error* field (for example, CellValue.ErrorDivisionByZero, CellValue.ErrorInvalidValueInFunction, CellValue.ErrorName, etc.).
The cell's CellRange.Formula property is assigned to an error code (e.g., "=#DIV/0!", "=#N/A", etc.) or an expression that cannot be calculated correctly (for example, an expression containing an invalid function name, value, division by zero, etc.)
For details, refer to the Error Types document.
cell.Value = CellValue.ErrorDivisionByZero
cell.Formula = "= #DIV/0!"
cell.Formula = "=5/0"
CellValue.IsError = true