Click or drag to resize

Setting Values and Formatting Excel spreadsheet cells with ExpertXls

An Excel worksheet can contain data of various types like number values, date and time values, texts, boolean values, etc. These type of values can be assigned to a cell or a range of cells using the Value property of the ExcelRange class. The library will determine Excel data type based on the .NET type of the object assigned to the Value property. For example, if a value of .NET type System.Double will be assigned to the Value property of the ExcelRange, the Excel type of all the cells in the range will be set to Number and the value of the cells will be specified value. The behavior is similar for values of System.DateTime or System.String types.

Additionally, the ExcelRange class defines a set of properties like Text, NumberValue or DateTimeValue which are the equivalent of the Value property when this property is assigned with values of type System.String, System.Double or System.DateTime.

The formatting information that will be used when the values are displayed by an Excel viewer can be specified using the ExcelRange.Style.Number.NumberFormatString property of the ExcelRange. It is recommended to set the formatting string for a cell or for a range of cells after a value was assigned to that cell or range. This ensures the formatting is correctly applied for that value.

Code Samples

Below there are a few code samples for setting values of various type and for applying various formatting strings to the cells in a worksheet:

Copy
// set a text value
worksheet["A7"].Value = "This is a string value assigned with 'Value' property";
worksheet["A8"].Text = "This is a string value assigned with 'Text' property";

// set a number value
double numberValue = 1234567.809;
worksheet["A16"].Value = numberValue;
worksheet["A16"].Style.Number.NumberFormatString = "#,##0.00";

// set a date value
string dateFormatString = "m/d/yyyy";
worksheet["A32"].Value = DateTime.Now;
worksheet["A32"].Style.Number.NumberFormatString = dateFormatString;