Cell Value Types
This article briefly describes how to work with the Value property of the cells in the document model and focuses on the different supported types of values.
Working with the Value Property of Cells
Since cells are the atomic data units of a worksheet, their Value property is used frequently. The property is of type ICellValue – an interface that is implemented by all five concrete value types supported by the document model: EmptyCellValue, BooleanCellValue, NumberCellValue, FormulaCellValue and TextCellValue. The ICellValue interface exposes several properties and methods:
RawValue: Тhe property holds the string value entered by the user, opposed to the result value displayed in the cell. For example, when the user enters the string "=1+2" in a cell, the Value of the cell becomes a FormulaCellValue instance with result value of "3" and RawValue of "=1+2".
ValueType and ResultValueType: Тhe two properties are of type CellValueType and provide information about the type of the Value and its result. For example, the FormulaCellValue with RawValue of "=1+2" has a Formula ValueType and a Number ResultValueType. Similarly, the string "=CONCATENATE("abc", "def")" produces a FormulaCellValue with Formula ValueType and Text ResultValueType.
GetValueAsString() and GetResultValueAsString(): Тhe two methods require a CellValueFormat argument that specifies how to present the Value and the ResultValue of the ICellValue object as strings.
To access the Value property of cells, first you need to create a CellSelection object that designates the cell or region of cells you would like to work with. Just like all other properties of the worksheet's cells, the Value property has three methods that respectively set, get and clear its value: SetValue(), GetValue() and ClearValue().
The SetValue() method has multiple overloads that allow you to pass a double, a string, a Boolean value, a DateTime instance or an ICellValue object.
The Excel Number Formats differ from the .NET ones. The proper way of getting the formatted cell value is to get the number format first using CellSelection.GetFormat().Value. Then, pass the retrieved CellValueFormat to the ICellValue.GetResultValueAsString method. It will also calculate the formulas contained in the cell. Read more in the Number Formatting article.
Get Formatted Cell's Value
CellSelection cell = worksheet.Cells[rowIndex, columnIndex];
ICellValue cellValue = cell.GetValue().Value;
CellValueFormat cellFormat = cell.GetFormat().Value;
string formattedValue = cellValue.GetResultValueAsString(cellFormat);
Example 1 sets the Value of cell A1 to "Total".
Example 1: Set string value
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("Total");
Another option for setting the cell value is to use the Create() method of the CellValueFactory class to produce an ICellValue and then assign the value using the SetValue(ICellValue) method. Similarly to SetValue(), the method Create() has multiple overloads that allow you to enter a string, double or Boolean value.
Example 2 creates a NumberCellValue with value 3.14 and assigns it to cell A1.
Example 2: Create NumberCellValue
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
ICellValue value = CellValueFactory.Create(3.14);
worksheet.Cells[0, 0].SetValue(value);
If you now retrieve the Value of cell A1, the GetValue() method will return an instance of NumberCellValue with RawValue equal to "3.14" and both ValueType and ResultValueType equal to Number.
Example 3 retrieves the value created in Example 2.
Example 3: Retrieve cell value
RangePropertyValue<ICellValue> rangeValue = worksheet.Cells[0, 0].GetValue();
ICellValue value = rangeValue.Value;
Note that the GetValue() method does not return an ICellValue directly, but it provides an RangePropertyValue
IsIndeterminate: The Boolean property indicates whether the Value property is consistent among all cells in the specified CellSelection. If all cells have one and the same Value, IsIndeterminate is set to false. However, if the Value property varies throughout the cells in the CellSelection, the IsIndetermine property is set to true and the Value property of the RangePropertyValue
instance is set to its default value – EmptyCellValue. -
Value: If the IsIndeterminate property is set to false, Value holds the ICellValue of the whole CellSelection region. If the IsIndeterminate property is set to true, then the cells in the CellSelection region contain different values and the Value property is set to its default – EmptyCellValue.
Example 4: Retrieve and use IsIndeterminate and Value properties
Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets.Add(); worksheet.Cells[0, 0].SetStyleName("Good"); worksheet.Cells[0, 1].SetStyleName("Bad"); RangePropertyValue<string> cell00Value = worksheet.Cells[0, 0].GetStyleName(); //cell at [0, 0] cell00Value.IsIndeterminate is False, cell00Value.Value is Good RangePropertyValue<string> cell01Value = worksheet.Cells[0, 1].GetStyleName(); //cell at [0, 0] cell01Value.IsIndeterminate is False, cell01Value.Value is Bad RangePropertyValue<string> twoCellsValue = worksheet.Cells[0, 0, 0, 1].GetStyleName(); //cells at [0, 0, 0, 1] twoCellsValue.IsIndeterminate is True, twoCellsValue.Value is Normal
Empty Cell Value
EmptyCellValue is the default cell value type. When an empty worksheet is added to the workbook, the Value property of all its cells is of type EmptyCellValue. The RawValue of an EmptyCellValue is an empty string and its ValueType is Empty.
If a cell already contains another value, you can set an empty value for it using the ClearValue() method.
Example 5 clears the value of cell A1.
Example 5: Clear value
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].ClearValue();
Boolean Cell Value
As its name suggests, the BooleanCellValue type contains a value of type bool. The RawValue of each BooleanCellValue is either "TRUE" or "FALSE" and the ValueType is Boolean. You can easily assign to a cell a BooleanCellValue using the SetValue(bool) overload.
Example 6 sets the values of cells A1 and B1 to true and false BooleanCellValues, respectively.
Example 6: Set boolean value
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue(true);
worksheet.Cells[0, 1].SetValue(false);
Another way to assign cells Boolean values is through using the static Create() method of the CellValueFactory class to produce a BooleanCellValue and then passing this cell value instance to the SetValue() method.
Example 7 illustrates how to create a BooleanCellValue using the CellValueFactory class.
Example 7: Create a BooleanCellValue using CellValueFactory
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
ICellValue booleanCellValueTrue = CellValueFactory.Create(true);
ICellValue booleanCellValueFalse = CellValueFactory.Create(false);
worksheet.Cells[0, 0].SetValue(booleanCellValueTrue);
worksheet.Cells[0, 1].SetValue(booleanCellValueFalse);
Number Cell Value
The NumberCellValue contains a value of type double. The ValueType of each NumberCellValue instance is Number and the RawValue is the string representation of the double it holds internally. Note that the RawValue of the NumberCellValue may be different from the value that appears in the cell since a cell value format may be applied. For example, if two cells present to the user "50.00%" and "5.00E-01", actually they both contain a NumberCellValue with a RawValue "0.5". The values appear differently because percentage format has been applied to the first cell and scientific format – to the second cell. In fact, dates are also number values presented in date format. For example, 16 September 2020 is actually the number 44090. You can find a detailed description of the possible number formats and examples of their automatic assigning in the Number Formatting article.
The SetValue() method has several overloads that produce a NumberCellValue. You can use SetValue(double) and pass the double value or SetValue(DateTime) and hand in the a DateTime instance that will be internally converted to a number. Also, you can use the SetValue(string) and pass the string representation of the number you would like to set. Note that the SetValue(string) method attempts to parse the string you provide to all values and turns it into a TextCellValue only if it cannot produce any of the other types of values.
Example 8 shows how to set the value of cell A1 to 1.23.
Example 8: Set number cell value
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("1.23");
Date Values
You can use the SetValue(string) method to assign dates.
Example 9 sets the value of cell A1 to 6 October 1987 (in culture "en-US"). Once set, the value of A1 is a NumberCellValue with RawValue 32056. Because the document model detects that this is a date string, a date format is automatically applied to the cell, so that the value appears as a date.
Example 9: Set date as number cell value
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("10/06/1987");
Contained double value: 32056
Applied format: m/d/yyyy
If you later access the value of the cell applied in Example 9, you will get its double representation - 32056. To obtain the date that this value represents, you can use the ConvertDoubleToDateTime() of the FormatHelper class to convert the raw number to a date or the GetValueAsString() method, which will return the date as a string. Both approaches are demonstrated in Example 10.
Example 10: Get date value
ICellValue cellValue = worksheet.Cells[0, 0].GetValue().Value;
CellValueFormat format = worksheet.Cells[0, 0].GetFormat().Value;
string resultAsString = cellValue.GetValueAsString(format);
double rawValueAsNumber = Convert.ToDouble(cellValue.RawValue);
DateTime? resultAsDateTime = FormatHelper.ConvertDoubleToDateTime(rawValueAsNumber).Value.Date;
Formula Cell Value
The FormulaCellValue contains a value of type RadExpression, which represents an arithmetic expression, built-up by constants, operators, cell references and functions. The CellValueType of FormulaCellValues is Formula and their RawValue should start with an equal sign (=).
Again, there are a couple of approaches to set a FormulaCellValue to a cell: invoking the Setvalue() method with a string and passing to the SetValue() method an ICellValue instance that is retrieved through the Create() method of the CellValueFactory class. Note, however, that both methods produce a FormulaCellValue only if the current CellValueFormat is not Text. If the format of the cell is Text, the SetValue() and Create() methods produce a TextCellValue. That said, if you would like to use present formula values as strings, you can set the CellValueFormat property of the specified cell before you input the values.
You can set a FormulaCellValue through the SetValue() method by passing a string that starts with an equal sign (=).
Example 11 shows how you can create a formula that refers to another cell.
Example 11: Create formula referring to another cell
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("=A2");
After this code is executed the value of cell A1 will be the same as the value of cell A2. When the value of A2 is modified, the change will be reflected in A1 automatically.
An expression can also contain a predefined function that performs a given calculation. The document model offers a number of built-in functions. You can read more about the available functions in the Functions article. Note that if the current CellValueFormat of the cell is Text ("@"), the method will produce a TextCellValue instead of a FormulaCellValue.
Example 12 illustrates the use of the SUM built-in function.
Example 12: Use built-in function
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("=SUM(A2, 3)");
Another way of setting a FormulaCellValue is passing an ICellValue object to the SetValue() method. Once again, the CellValueFactory's Create() method is used. Note that unlike the BooleanCellValue and the NumberCellValue, you need to pass the Worksheet and the CellIndex instances as parameters. These two arguments are required in case the RadExpression contains references to cells in the other worksheets. Additionally, this overload of the Create() method requires the current cell value format to be passed as a parameter, and based on its value, the method provides the new cell value format value that should be applied. Note that if the current CellValueFormat is Text ("@"), the method produces a TextCellValue instead of a FormulaCellValue.
Example 13 creates a FormulaCellValue using the CellValueFactory class.
Example 13: Create FormulaCellValue
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellIndex cellIndex = new CellIndex(0, 0);
ICellValue cellValue;
CellValueFormat newFormatValue;
CellValueFactory.Create("=A2 + B2", worksheet, cellIndex, CellValueFormat.GeneralFormat, out cellValue, out newFormatValue);
worksheet.Cells[cellIndex].SetValue(cellValue);
Using the SetValue() method executes internal checks for the current cell value type using the current format and other conditions. If you are sure that the passed value is a formula, you can use the SetValueAsFormula(string text) method directly which will improve the performance.
Example 14 shows how you can use the method.
Example 14: Create FormulaCellValue through SetValueAsFormula()
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellIndex cellIndex = new CellIndex(0, 0);
worksheet.Cells[cellIndex].SetValueAsFormula("=B1+B2");
Depending on your requirements, you can obtain the formula from the cell represented by its definition or by the evaluated result value. Example 15 shows both possibilities.
Example 15: Get the value of a cell containing formula
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("=SUM(A2, 3)");
worksheet.Cells[1, 0].SetValue(10);
ICellValue cellValue = worksheet.Cells[0, 0].GetValue().Value;
CellValueFormat format = worksheet.Cells[0, 0].GetFormat().Value;
string valueAsString = cellValue.GetValueAsString(format); // =SUM(A2, 3)
string resultValue = cellValue.GetResultValueAsString(format); // 13
Text Cell Value
As its name suggests, the TextCellValue contains a value of type string and its ValueType is Text.
You can set a TextCellValue using the SetValue(string) method. Note that before producing a TextCellValue, the SetValue() method attempts to parse the incoming string to all other cell value types. That said, if you pass the string "true", the cell will be assigned a BooleanCellValue. If you would like the cell to contain the string "true", pass the SetValue(string) method the string "=true".
Example 16 sets the value of cell A1 to the string "some test".
Example 16: Set TextCellValue
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("some text");
If you would like to avoid the default parsing of the input string and always produce a TextCellValue, you need to set the CellValueFormat of the cells to Text ("@") and then enter the values.
Example 17 enters the string "=1+2" into a cell, however, because of the applied cell value format, the cell is assigned a TextCellValue instead of a FormulaCellValue.
Example 17: Explicitly apply text value type
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetFormat(new CellValueFormat("@"));
worksheet.Cells[0, 0].SetValue("=1+2");
The same result could be achieved using the Create() method of the CellValueFactory class. Example 18 enters the string "=1+2" into a cell and applies cell value format.
Example 18: Create TextCellValue
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellIndex cellIndex = new CellIndex(0, 0);
ICellValue cellValue;
CellValueFormat newFormatValue;
CellValueFactory.Create("=1 + 2", worksheet, cellIndex, new CellValueFormat("@"), out cellValue, out newFormatValue);
worksheet.Cells[cellIndex].SetValue(cellValue);
If you are sure that the value is a string and need to create a text cell value, you can use the SetValueAsText(string text) method directly. This would avoid the internal checks and parsing that are usually executed and improve the performance.
Example 19 shows how to utilize the SetValueAsText() method.
Example 19: Create TextCellValue through SetValueAsText
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellIndex cellIndex = new CellIndex(0, 0);
worksheet.Cells[cellIndex].SetValueAsText("This is most certainly a text.");