Edit this page
Available for: UI for ASP.NET AJAX | UI for ASP.NET MVC | UI for WPF | UI for WinForms | UI for Silverlight

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.

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.

As previously explained, the number format does not change the value of the cell, only its appearance. In Figure 1 the value of the cell is set to a double number with a lot of digits after the decimal separator. When entered the default format here is General.

Figure 1: General format

Rad Spread Processing Features Number Formatting 01

If you need to specify the number of digits after the decimal separator, you can take advantage of Number formatting. For example, applying a number format that uses a thousand separator and limits the decimal places produces the following result:

Figure 2: Number format

Rad Spread Processing Features Number Formatting 02

The format string applied in Figure 1 is #,##0.00.

Number formatting is not restricted to number values. The following example illustrates how to apply a format to text values. Column A in the worksheet below contains the textual representation of the numbers 1-5:

Figure 3: Textual representation in Number format

Rad Spread Processing Features Number Formatting 03

To alter the values, so that each of them contains the string "apple" after the number, you can use the "@ apples" format string. The "@" wild card is replaced with the text of the cell in the result string. The result will be the following:

Figure 4: Textual representation in Number format with format string

Rad Spread Processing Features Number Formatting 04

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.

Example 1 demonstrates how to apply scientific format to cell A1 and percentage format to the A2:B3 cell range in the ActiveWorksheet:

[C#] Example 1: Apply format

public void ApplyFormat()
{
    Workbook workbook = new Workbook();
    Worksheet worksheet = workbook.Worksheets.Add();

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

    CellSelection cellSelectionA2B3 = worksheet.Cells[new CellRange(new CellIndex(1, 0), new CellIndex(2, 1))];
    CellValueFormat percentageFormat = new CellValueFormat("0.00%");
    cellSelectionA2B3.SetFormat(percentageFormat);
}

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 2 demonstrates how to get the Number format of cell A1 and cell range A2:B3:

[C#] Example 2: Get number format

public void GetFormat()
{
    Workbook workbook = new Workbook();
    Worksheet worksheet = workbook.Worksheets.Add();

    CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)];
    CellSelection cellSelectionA2B3 = worksheet.Cells[new CellRange(new CellIndex(1, 0), new CellIndex(2, 1))];

    CellValueFormat cellSelectioA1Format = cellSelectionA1.GetFormat().Value;
    CellValueFormat cellSelectioA2Format = cellSelectionA2B3.GetFormat().Value;
}