[Expand]General Information
[Expand]WinForms Controls
[Collapse]ASP.NET Controls and MVC Extensions
 [Expand]Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Collapse]ASP.NET WebForms Controls
   Getting Started
  [Expand]Grid View
  [Expand]Tree List
  [Expand]Card View
  [Expand]Reporting
  [Expand]Chart Control
  [Expand]Pivot Grid
  [Collapse]Spreadsheet
   [Expand]Product Information
    Getting Started
   [Collapse]Concepts
    [Expand]Spreadsheet Document
     Supported Formats
     Import and Export
    [Collapse]Cell Basics
      Cell Data Types
      Dates and Times in Cells
      Error Types
      Cell Referencing
      Formatting Cells
    [Expand]Spreadsheet Formulas
     Defined Names
     Data Validation
    [Expand]Data Presentation
    [Expand]Charting
     Printing
    [Expand]Mail Merge
     Protection
     Adaptivity
     Keyboard Shortcuts
   [Expand]Visual Elements
  [Expand]Rich Text Editor
  [Expand]Scheduler
  [Expand]Site Navigation and Layout
  [Expand]HTML Editor
  [Expand]Gauges
  [Expand]Vertical Grid
  [Expand]Data Editors
  [Expand]Docking and Popups
  [Expand]File Management
  [Expand]Data and Image Navigation
  [Expand]Multi-Use Site Controls
  [Expand]Spell Checker
  [Expand]Query Builder
 [Expand]ASP.NET MVC Extensions
 [Expand]Localization
 [Expand]Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Cell Data Types

Each cell in a worksheet has a value that is specified by the CellValue object. To access this object, use the Range.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).
  • The cell value is determined by a constant assigned to the cell via the Range.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 Range.Formula property. In this case, the cell value is calculated dynamically.

A cell value can be of one of the following types: numeric, text, Boolean or error. Data values of these types can 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 Range.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 of how a value of each type can be input, formatted, displayed and obtained.

Cell Value Type

Cell Content

Sample Input

Sample Display Format

Displayed String

(Cell.DisplayText)

Identify the Type

Obtain the Cell Value Object

Numeric

The cell's Range.Value property is assigned to a value of any numeric type (e.g., System.Int32, System.Double, etc.).

- or -

The cell's Range.Formula property is assigned to an expression that returns a number.

cell.Value = 12345678

- or -

cell.Formula = "= SUM(12000000,345678)"

cell.NumberFormat = "#,#"

CellValue.IsNumeric = true

CellValue.Type = CellValueType.Numeric

CellValue.NumericValue

The cell's Range.Value property is assigned to the System.DateTime object, an object returned by the CellValue.FromDateTime method, or a numeric value representing a serial number of a date or time.

- or -

The cell's Range.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);

- or -

workbook.DocumentSettings.Calculation.Use1904DateSystem = true;
cell.Value = CellValue.FromDateTime(new DateTime(2012, 12, 10), true);

- or -

cell.Value = 41253;

- or -

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

Text

The cell's Range.Value property is assigned to the System.String object.

- or -

The cell's Range.Formula property is assigned to an expression that returns text.

cell.Value = "Sample Text"

- or -

cell.Formula = "= PROPER("sample text")"

CellValue.IsText = true

CellValue.Type = CellValueType.Text

CellValue.TextValue

Boolean

The cell's Range.Value property is assigned to the System.Boolean object.

- or -

The cell's Range.Formula property is assigned to an expression that returns TRUE or FALSE.

cell.Value = true

- or -

cell.Formula = "= TRUE()"

CellValue.IsBoolean = true

CellValue.Type = CellValueType.Boolean

CellValue.BooleanValue

Error

The cell's Range.Value property is assigned to the CellValue object returned by the CellValue.Error* field (for example, CellValue.ErrorDivisionByZero, CellValue.ErrorInvalidValueInFunction, CellValue.ErrorName, etc.).

- or -

The cell's Range.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.)

cell.Value = CellValue.ErrorDivisionByZero

- or -

cell.Formula = "= #DIV/0!"

- or -

cell.Formula = "=5/0"

CellValue.IsError = true

CellValue.Type = CellValueType.Error

CellValue.ErrorValue

Expanded See Also

Is this topic helpful?​​​​​​​