Cell Data Types

• Nov 29, 2024
• 4 minutes to read

Each cell in a worksheet has a value the CellValue object specifies. To access this object, use the CellRange.Value property. The data in a cell determines its value:

• If the cell does not contain any data, the cell value equals CellValue.Empty (CellValue.IsEmpty is set to true). Refer to the following article for more information: How to: Clear Cells of Content, Formatting, Hyperlinks and Comments.
• A constant assigned to the cell via the CellRange.Value property determines the cell’s value. In this case, the cell’s value is neither calculated nor changed.
• A value resulting from a formula assigned to a cell by the CellRange.Formula property determines the cell’s value. In this case, the cell’s value is calculated dynamically.

A cell’s value can be empty, numeric, text, Boolean or error, and can have various display formats. For example, a numeric value can be displayed as a decimal number, a percentage or currency, a date or time, etc.

Use the properties of the CellValue object returned by CellRange.Value to retrieve information about the cell’s value type, and get the cell’s value 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 lists examples of how to input, format, display, and obtain these values.

Cell Value Type	Cell Content	Sample Input	Sample Display Format	Displayed String (Cell.DisplayText)	Cell Type API	Cell Value API	More Examples
Empty	The default cell value type. If a cell contains any data, you can assign an empty value to it by setting the CellRange.Value property to null or CellValue.Empty.	cell.Value = null cell.Value = CellValue.Empty			CellValue.IsEmpty = true CellValue.Type = CellValueType.None	CellValue.Empty	How to: Clear Cells of Content, Formatting, Hyperlinks and Comments
Numeric	The cell’s CellRange.Value property is assigned to a value of any numeric type (for example, Int32, Double, etc.) or 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 CellValue.Type = CellValueType.Numeric	CellValue.NumericValue	How to: Specify Number or Date Format for Cell Content
Numeric (Date and Time)	The cell’s CellRange.Value property is assigned to the DateTime object, an object returned by the CellValue.FromDateTime method, or a numeric value that is a serial number of a date or time. The cell’s CellRange.Formula property is assigned to an expression that returns the serial number of a date or time.	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.Type = CellValueType.DateTime CellValue.IsDateTime = true	CellValue.DateTimeValue CellValue.NumericValue	Dates and Times in Cells How to: Specify Number or Date Format for Cell Content
Text	The cell’s CellRange.Value property is assigned to the String object or 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 CellValue.Type = CellValueType.Text	CellValue.TextValue
Boolean	The cell’s CellRange.Value property is assigned to the Boolean object or 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 CellValue.Type = CellValueType.Boolean	CellValue.BooleanValue
Error	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 (for example, “=#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.)	cell.Value = CellValue.ErrorDivisionByZero cell.Formula = "= #DIV/0!" cell.Formula = "=5/0"			CellValue.IsError = true CellValue.Type = CellValueType.Error	CellValue.ErrorValue	Error Types
Image	The cell’s Value property is assigned to an image	cell.Value = File.ReadAllBytes("image.png"); cell.Value = new MemoryStream(imageBytes); cell.Value = DXImage.FromStream(imageStream);			CellValue.IsCellImage = true; CellValue.Type = CellValueType.CellImage	CellValue.ImageValue	How to: Place a Picture in a Cell