Cell Data Types
- 5 minutes to read
A CellValue object specifies the value for each cell in a worksheet. To access the cell value object, use the CellRange.Value property. The data within the cell defines the cell value:
- If the cell does not contain any data, the cell value is empty (CellValue.IsEmpty is set to
true). Refer to the following example for more information on how to remove cell content: How to: Clear Cells of Content, Formatting, Hyperlinks and Comments. - If a constant is assigned to the CellRange.Value property, the cell value is not calculated or changed.
- If the cell value is the result of calculating a formula assigned to the cell by the CellRange.Formula property, the cell value is calculated dynamically.
The following cell value types are available:
Cell values can have various display formats. For example, you can display a numeric value as a decimal number, a percentage or currency value, a date or time value, and so on.
Use the CellRange.Value property to retrieve the cell value. The Value property returns a CellValue object that provides APIs to determine the cell’s value type and retrieve the value as an object of the corresponding type.
To get the string that specifies the formatted value as it is displayed in a cell, use the Cell.DisplayText property.
#Empty
Empty is the default cell value type. If a cell is empty, the CellValue.IsEmpty property returns true, and the CellValue.Type property returns CellValueType.None.
If a cell contains any data and you want make the cell empty, set the CellRange.Value property to null or CellValue.Empty:
cell.Value = null;
cell.Value = CellValue.Empty;
#Numeric
The type of a cell is considered numeric if the cell’s Value is a value of any numeric type (for example, Int32, Double, and so on) or the cell’s Formula property value is an expression that returns a number.
cell.Value = 12345678;
cell.Formula = "= SUM(12000000,345678)";
For cells with numeric data, the CellValue.IsNumeric property returns true and the CellValue.Type returns CellValueType.Numeric.
To get the cell value as a numeric, use the CellValue.NumericValue property.
Use the NumberFormat property to change the display format for a numeric cell:
cell.NumberFormat = "#,#";
See the following page for information about number formatting rules: Review guidelines for customizing a number format.
For more examples, refer to the following help topics:
- How to: Specify Number or Date Format for Cell Content
- How to: Clear Cells of Content, Formatting, Hyperlinks and Comments
#Numeric (Date and Time)
For date-time cells, the cell’s Value property can contain one of the following values:
- DateTime object
- An object that the CellValue.FromDateTime(DateTime, Boolean) method returns
- 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)";
Use the NumberFormat property to change the display format for a date-time cell:
cell.NumberFormat = "m/d/yy";
See the following page for information about date-time formatting rules: Review guidelines for customizing a number format (Guidelines for date and time formats)
For cell with dates and times, CellValue.IsNumeric and CellValue.IsDateTime properties return true and the CellValue.Type property returns CellValueType.DateTime.
To obtain the date-time cell value, use CellValue.DateTimeValue or CellValue.NumericValue (returns a serial number for the corresponding date-time value).
Refer to the following help topics for more information about date-time cell values:
#Text
Text cells contain a String object or a Formula that results to a string value:
cell.Value = "Sample Text";
cell.Formula = "= PROPER("sample text")";
For cells with text data, the CellValue.IsText property returns true and the CellValue.Type property returns CellValueType.Text.
To obtain the text assigned to a cell, use the CellValue.TextValue property.
#Boolean
Boolean cells contain a Boolean object or a Formula that results to TRUE or FALSE:
cell.Value = true;
cell.Formula = "= TRUE()";
For cells with Boolean data, the CellValue.IsBoolean property returns true and the CellValue.Type property returns CellValueType.Boolean.
To obtain the data value assigned to a cell, use the CellValue.BooleanValue property.
#Error
An error cell’s Value property can contain one of the following values:
- An object that the
CellValue.Error*field (for example, CellValue.ErrorDivisionByZero). For more information about possible error types, refer to the following help topic: Error Types. - The cell’s Formula property value is an error code (for example, “=#DIV/0!”, “=#N/A”, and so on.)
- An expression that cannot be calculated correctly (for example, an expression that contains an invalid function name, value, division by zero, and so on)
cell.Value = CellValue.ErrorDivisionByZero;
cell.Formula = "= #DIV/0!";
cell.Formula = "=5/0";
For cells with errors, the CellValue.IsError property returns true and the CellValue.Type property returns CellValueType.Error.
To obtain the error assigned to a cell, use the CellValue.ErrorValue property.
#Image
The Value property of image cells contains images.
cell.Value = File.ReadAllBytes("image.png");
cell.Value = new MemoryStream(imageBytes);
For cells with images, the CellValue.IsCellImage property returns true and the CellValue.Type property returns CellValueType.CellImage.
To obtain the image assigned to a cell, use the CellValue.ImageValue property.
For more information, refer to the following help topic: How to: Place a Picture in a Cell.