Number Formatting

Applying different formats to a number changes the appearance of the number. It is important to note, however, that a format does not change the value it is applied to. It only changes the way the value appears in the cell. This article explains the how to use the predefined number formats.

More information of how to create your own number format or modify one of the predefined types you can find in Format Codes help article.

Available Number Formats

The document model exposes the following categories of predefined formats:

• General format: The default number format applied to a number. Typically, numbers formatted with this format are displayed exactly as they are typed. If the number is 12 or more digits, however, the General number format applies scientific notation.

• Number format: Used for the general display of numbers. The format specifies the number of decimal places and indicates whether a thousands separator is used. Additionally, the Number format specifies how negative numbers are displayed.

• Currency format: Used for general monetary values. Numbers in this format are displayed with the default currency symbol. The format specifies the number of decimal places and indicates whether a thousands separator is used. Additionally, the Currency format specifies how negative numbers are displayed.

• Accounting format: Used for monetary values. Unlike the Currency format, it aligns the currency symbols and the values in a column. The format specifies the number of decimal places used.

• Date format: Treats a number as date and time serial number and displays it as a date value.

• Time format: Treats a number as date and time serial number and displays it as a time value.

• Percentage format: Displays the cell value multiplied by 100 and followed by a percent (%) symbol. The format specifies the number of decimal places used.

• Fraction format: Displays a cell value as a fraction.

• Scientific format: Displays a number in scientific notation. The number is transformed into a real number followed by E+n, where E (which stands for Exponent) multiplies the real number by 10 to the nth power. For example, a 2-decimal scientific format displays 12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power. The format specifies the number of decimal places used.

• Text format: Treats the content of a cell as text and displays the content exactly as it is typed.

• Special format: Designed to display numbers as postal codes (ZIP Code), phone numbers, or Social Security numbers.

• Custom format: Allows modifying any of the predefined formats. The format also allows creating a new custom number format that is added to the list of number format codes. For more information check the Format Codes help article.

The Date, Time and Currency formats are influenced by your OS regional settings. For more information, go to Localization.

Applying a Number Format

The number format is represented by the CellValueFormat class. You can set it to a given CellSelection object using its SetFormat() method.

When working with CellValueFormat you need to keep in mind that its constructor accepts culture-dependent format and converts them to culture-independent using the current thread format settings (e.g. in Bulgarian culture format passed as 0,00 will be converted to 0.00).

The following examples demonstrate how to apply a predefined format to a CellSelection:

Example 1: Apply general format

CellValueFormat generalFormat = new CellValueFormat("General"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); 
cellSelectionA1.SetFormat(generalFormat); 

Example 1 produces the following result:

Example 2: Apply number format

CellValueFormat numberFormat = new CellValueFormat("0.00"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); 
cellSelectionA1.SetFormat(numberFormat); 

Example 2 produces the following result:

Example 3: Apply currency format

CellValueFormat currencyFormat = new CellValueFormat("$#,##0.00"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); 
cellSelectionA1.SetFormat(currencyFormat); 

Example 3 produces the following result:

Example 4: Apply accounting format

CellValueFormat accountingFormat = new CellValueFormat("($* #,##0.00);($* (#,##0.00);($* \" - \"??);(@_)"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); 
cellSelectionA1.SetFormat(accountingFormat); 

Example 4 produces the following result:

Example 5: Apply date format

CellValueFormat dateFormat = new CellValueFormat("m/d/yyyy"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); // OR cellSelectionA1.SetValue(new DateTime(1903, 5, 18, 13, 37, 46)); 
cellSelectionA1.SetFormat(dateFormat); 

Example 5 produces the following result:

In order to show milliseconds in Date Format the predefined format could be modified like: "m/d/yyyy HH:mm:ss.SSS". More information of how to create your own number format or modify one of the predefined types you can find in Format Codes help article.

Example 6: Apply time format

CellValueFormat timeFormat = new CellValueFormat("h:mm:ss AM/PM"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); // OR cellSelectionA1.SetValue(new DateTime(1903, 5, 18, 13, 37, 46)); 
cellSelectionA1.SetFormat(timeFormat); 

Example 6 produces the following result:

In order to show milliseconds in Time Format the predefined format could be modified like: "HH:mm:ss.SSS". More information of how to create your own number format or modify one of the predefined types you can find in Format Codes help article.

Example 7: Apply percentage format

CellValueFormat percentageFormat = new CellValueFormat("0.00%"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); 
cellSelectionA1.SetFormat(percentageFormat); 

Example 7 produces the following result:

Example 8: Apply fraction format

CellValueFormat fractionFormat = new CellValueFormat("# ?/?"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); 
cellSelectionA1.SetFormat(fractionFormat); 

Example 8 produces the following result:

Example 9: Apply scientific format

CellValueFormat scientificFormat = new CellValueFormat("0.00E+00"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); 
cellSelectionA1.SetFormat(scientificFormat); 

Example 9 produces the following result:

Example 10: Apply text format

CellValueFormat textFormat = new CellValueFormat("@"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); 
cellSelectionA1.SetFormat(textFormat); 

Example 10 produces the following result:

Example 11: Apply special format

CellValueFormat specialFormat = new CellValueFormat("00000-0000"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(1234.56789); 
cellSelectionA1.SetFormat(specialFormat); 

Example 11 produces the following result:

Example 12: Apply custom format

CellValueFormat specialFormat = new CellValueFormat("[Green]$#,##0_);[Red]$(#,##0);\"zero\";[Blue]\"Text: \" @"); 
 
CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
cellSelectionA1.SetValue(123456789); 
cellSelectionA1.SetFormat(specialFormat); 
 
CellSelection cellSelectionB1 = worksheet.Cells[new CellIndex(0, 1)]; 
cellSelectionB1.SetValueAsText("Progress"); 
cellSelectionB1.SetFormat(specialFormat); 
 
CellSelection cellSelectionA2 = worksheet.Cells[new CellIndex(1, 0)]; 
cellSelectionA2.SetValue(0); 
cellSelectionA2.SetFormat(specialFormat); 
 
CellSelection cellSelectionB2 = worksheet.Cells[new CellIndex(1, 1)]; 
cellSelectionB2.SetValue(-1234.56789); 
cellSelectionB2.SetFormat(specialFormat); 

Example 12 produces the following result:

More information of how to create your own number format or modify one of the predefined types you can find in Format Codes help article.

Retrieving a Number Format

You can retrieve the number format of any cell selection using the GetFormat() method of CellSelection class. The method returns an object of type RangePropertyValue, which exposes two properties:

• IsIndeterminate: Determines if the CellValueFormat is consistent among all cells in the specified CellSelection. If the CellValueFormat is one and the same for all cells, IsIndeterminate is set to false. However, if the CellValueFormat varies throughout the cells in the CellSelection, the IsIndetermine property is set to true and the Value property of the RangePropertyValue object is set to its default value.

• Value: Holds the CellValueFormat for the cells. If the IsIndeterminate property is set to false, Value contains CellValueFormat of the whole CellSelection region. If the IsIndeterminate property is set to true, this indicates that the CellValueFormat is not the same for all cells in the CellSelection and the Value property is set to the default CellValueFormat.

Example 13 demonstrates how to get the Number format of cell A1:

Example 13: Get number format

CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
 
CellValueFormat cellSelectioA1Format = cellSelectionA1.GetFormat().Value;