[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
  [Expand]Scheduler
  [Collapse]Spreadsheet
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Collapse]Cell Basics
     Cell Data Types
     Dates and Times in Cells
     Error Types
     Cell Referencing
     Formatting Cells
   [Expand]Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Shapes
    Printing
    Protection
    Find and Replace
   [Expand]Mail Merge
   [Expand]Visual Elements
    Services
    Themes and Templates
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Gantt Control
  [Expand]Diagram Control
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
  [Expand]Scheduler (legacy)
 [Expand]Scaffolding Wizard
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[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]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Cell Data Types

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

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 the Range.Value property returns 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 provides 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)

Identify the Type

Obtain the Cell Value Object

Empty

The default cell value type.

If a cell contains any data, you can assign an empty value to it by setting the Range.Value property to null or CellValue.Empty.

Refer to the How to: Clear Cells of Content, Formatting, Hyperlinks and Comments document for more information.

cell.Value = null

- or -

cell.Value = CellValue.Empty

CellValue.IsEmpty = true

CellValue.Type = CellValueType.None

CellValue.Empty

Numeric

The cell's Range.Value property is assigned to a numeric type value (for example, 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 = "#,#"

More examples:

How to: Specify Number or Date Format for Cell Content

CellValue.IsNumeric = true

CellValue.Type = CellValueType.Numeric

CellValue.NumericValue

Numeric

(Date and Time)

The cell's Range.Value property is assigned to the System.DateTime object the CellValue.FromDateTime method returns, 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.

Refer to the Dates and Times in Cells document for more information.

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"

More examples:

How to: Specify Number or Date Format for Cell Content

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 the CellValue.Error* field returns (for example, CellValue.ErrorDivisionByZero, CellValue.ErrorInvalidValueInFunction, CellValue.ErrorName, etc.).

- or -

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

Refer to the Error Types document for more information.

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?​​​​​​​