Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Excerpt

A number format is a string that is used to format dates or numbers. For example, if you enter the value 0.563 in cell A4 and the number format string assigned to cell A4 is "0.00%", the value displayed will be 56.30%. Changing a cell's number format will not change the actual value that is used in calculations. In Microsoft Excel, you will see the cell's actual value in the formula bar.

Number Formats in Excel

In Excel, number formats can be assigned to cells through the Format Cells dialog. To apply a new number format to cells in Excel:

...

To assign a number format with ExcelWriter's ExcelApplication, use the Style object's NumberFormat property. The following lines create a percentage style and assign it to a cell:

Code Block
c#
c#

ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create(ExcelApplication.FileFormat.Xlsx);

//--- Create a percentage style and assign
//--- a number format to it.
GlobalStyle stylPercent = wb.CreateStyle();
stylPercent.NumberFormat = "0.00%";

//--- Assign a numeric value and
//--- stylPercent to cell A4 to display
//--- 56.30% in the cell.
ws.Cells["A4"].Value = "0.563";
ws.Cells["A4"].Style = stylPercent;

ExcelWriter includes a set of built-in format constants to help you quickly assign commonly used display formats. For example, the constant NumberFormat.DateFormat.DayOfWeekMonthDayYear represents the format string "dddd, mmmm dd, yyyy". The following assigns this format to a date style:

Code Block
c#
c#

GlobalStyle styleDate = wb.CreateStyle();
styleDate.NumberFormat = NumberFormat.DateFormat.DayOfWeekMonthDayYear;

...

The following example creates an accounting style with two decimal places, a dollar sign and negative values in red:

Code Block
c#
c#

ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create(ExcelApplication.FileFormat.Xlsx);

GlobalStyle stylCurrency = wb.CreateStyle();
NumberFormat numFormat = wb.NumberFormat;
String acctFormat = numFormat.CreateAccounting(2, true, NumberFormat.Color.Red);
stylCurrency.NumberFormat = acctFormat;

Area ar = wb.Worksheets[0].CreateArea("B7:H24");
ar.ApplyStyle(stylCurrency);