Number Formats

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:

1. Select a cell or group of cells.
   
   
2. From the Format menu, select Cells, or, right-click the highlighted cells and select Format Cells.
   
   
3. Select the Number tab.
   
   
4. Select a number format category from the list on the left.
   
   
5. Set additional attributes (if available) on the right and click Ok.

How to Set Number Formats with ExcelWriter

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:

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

// --- 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:

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

For a complete list of number format constants, see The NumberFormat Object.

The NumberFormat Object

The NumberFormat object is a helper class that you can use to create custom number format strings. NumberFormat contains methods for creating accounting, currency, fraction, number, percentage, and scientifice display formats. For example, the method NumberFormat.CreateAccounting creates an accounting format string. This method takes three parameters:

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

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

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);

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.