Setting Values and Formatting Excel spreadsheet cells with ExpertXls |
A cell holds a text, a number, a date and time, a boolean or a formula. Set the value with the Value property: the Excel type of the cell is given by the .NET type of the value. A System.Double or an integer becomes a number, a System.DateTime a date, a System.Boolean a boolean and a System.String a text.
The typed properties Text, NumberValue, DateTimeValue and BooleanValue set and read a value of a given type. Text always stores a text, also for a value that looks like a number, such as a code with leading zeros.
How a value is displayed is controlled by the Excel number format of the cell, Style.Number.NumberFormatString. The value itself does not change.
// a text, a number, a date and a boolean worksheet["A1"].Value = "A text value"; worksheet["A2"].Value = 1234567.809; worksheet["A3"].Value = new DateTime(2026, 10, 17, 14, 35, 0); worksheet["A4"].Value = true; // a code with leading zeros stays a text worksheet["A5"].Text = "00123"; // number formats: the same number displayed in different ways worksheet["B2"].Value = 1234567.809; worksheet["B2"].Style.Number.NumberFormatString = "#,##0.00"; worksheet["C2"].Value = 0.256; worksheet["C2"].Style.Number.NumberFormatString = "0.0%"; worksheet["D2"].Value = -1500; worksheet["D2"].Style.Number.NumberFormatString = "#,##0;[Red]-#,##0"; // date and time formats worksheet["B3"].Value = DateTime.Now; worksheet["B3"].Style.Number.NumberFormatString = "dddd, mmmm d, yyyy"; worksheet["C3"].Value = DateTime.Now; worksheet["C3"].Style.Number.NumberFormatString = "h:mm AM/PM"; // read the values back if (worksheet["A2"].IsNumber) Console.WriteLine(worksheet["A2"].NumberValue);