Excel cell number format

Besides setting the cell's value, with GemBox.Spreadsheet you can also set different number formats that will result in the changed appearance of the cell's numeric or date values.

To apply a number format to a cell or cell range, assign the desired number format code to the CellStyle.NumberFormat property on the ExcelCell or CellRange object. To read more about Excel number formats see the Number format codes page.

Note that the format doesn't change the cell's value, it only changes its textual representation, how it's displayed in an Excel application. You can use the ExcelCell.GetFormattedValue method to get that textual representation.

The following example shows how you can write formatted values like currency, date and time, percentage, fraction, etc. in an Excel file from C# and VB.NET.

Excel cells with formatted values and number format codes
Screenshot of Excel cells with formatted values and their number format codes
View on GitHub 
using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Formats");

        worksheet.Rows[0].Style = workbook.Styles[BuiltInCellStyleName.Heading1];
        worksheet.Columns[0].Width = 25 * 256;
        worksheet.Columns[1].Width = 35 * 256;
        worksheet.Columns[2].Width = 25 * 256;

        worksheet.Cells[0, 0].Value = "Value & Format";
        worksheet.Cells[0, 1].Value = "Format";
        worksheet.Cells[0, 2].Value = "Type";

        // Sample data with values and formats.
        var data = new (object Value, string Format)[]
        {
            (1.23, "0"),
            (1.23, "0.00"),
            (1.2345, "0.000"),
            (-2.345, @"0.00_);[Red]\(0.00\)"),
            (2.34, @"\$#,##0.00"),
            (2345.67, @"#,##0.00\ [$€-1]"),
            (new DateTime(2012, 11, 9, 0, 0, 0), @"[$-F800]dddd\,\ mmmm\ dd\,\ yyyy"),
            (new DateTime(2012, 12, 5, 0, 0, 0), @"[$-409]mmmm\ d\,\ yyyy;@"),
            (new DateTime(2012, 8, 10, 0, 0, 0), @"yyyy\-mm\-dd\ \(dddd\)"),
            (new DateTime(2012, 8, 12, 0, 13, 0), @"[$-409]m/d/yy\ h:mm\ AM/PM;@"),
            (new DateTime(2012, 8, 1, 21, 10, 0), @"[$-409]h:mm\ AM/PM;@"),
            (new DateTime(1900, 1, 1, 6, 45, 30), "[h]:mm:ss"),
            (0.0123, "0%"),
            (0.0123, "0.00%"),
            (120000, "0.00E+00"),
            (1.25, @"# ?/?"),
            (1.25, @"#\ ?/100"),
            ("Sample text", "@")
        };

        for (int i = 0; i < data.Length; i++)
        {
            var item = data[i];

            // Write value and set number format to a cell.
            worksheet.Cells[i + 1, 0].Value = item.Value;
            worksheet.Cells[i + 1, 0].Style.NumberFormat = item.Format;

            // Write number format as cell's value.
            worksheet.Cells[i + 1, 1].Value = item.Format;

            // Write data type as cell's value.
            worksheet.Cells[i + 1, 2].Value = item.Value.GetType().ToString();
        }

        workbook.Save("Number Formats.%OutputFileType%");
    }
}

Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Formats")

        worksheet.Rows(0).Style = workbook.Styles(BuiltInCellStyleName.Heading1)
        worksheet.Columns(0).Width = 25 * 256
        worksheet.Columns(1).Width = 35 * 256
        worksheet.Columns(2).Width = 25 * 256

        worksheet.Cells(0, 0).Value = "Value & Format"
        worksheet.Cells(0, 1).Value = "Format"
        worksheet.Cells(0, 2).Value = "Type"

        ' Sample data with values and formats.
        Dim data = New(Value As Object, Format As String)() _
        {
            (1.23, "0"),
            (1.23, "0.00"),
            (1.2345, "0.000"),
            (-2.345, "0.00_);[Red]\(0.00\)"),
            (2.34, "\$#,##0.00"),
            (2345.67, "#,##0.00\ [$€-1]"),
            (New DateTime(2012, 11, 9, 0, 0, 0), "[$-F800]dddd\,\ mmmm\ dd\,\ yyyy"),
            (New DateTime(2012, 12, 5, 0, 0, 0), "[$-409]mmmm\ d\,\ yyyy;@"),
            (New DateTime(2012, 8, 10, 0, 0, 0), "yyyy\-mm\-dd\ \(dddd\)"),
            (New DateTime(2012, 8, 12, 0, 13, 0), "[$-409]m/d/yy\ h:mm\ AM/PM;@"),
            (New DateTime(2012, 8, 1, 21, 10, 0), "[$-409]h:mm\ AM/PM;@"),
            (New DateTime(1900, 1, 1, 6, 45, 30), "[h]:mm:ss"),
            (0.0123, "0%"),
            (0.0123, "0.00%"),
            (120000, "0.00E+00"),
            (1.25, "# ?/?"),
            (1.25, "#\ ?/100"),
            ("Sample text", "@")
        }

        For i = 0 To data.Length - 1
            Dim item = data(i)

            ' Write value and set number format to a cell.
            worksheet.Cells(i + 1, 0).Value = item.Value
            worksheet.Cells(i + 1, 0).Style.NumberFormat = item.Format

            ' Write number format as cell's value.
            worksheet.Cells(i + 1, 1).Value = item.Format

            ' Write data type as cell's value.
            worksheet.Cells(i + 1, 2).Value = item.Value.GetType().ToString()
        Next

        workbook.Save("Number Formats.%OutputFileType%")

    End Sub
End Module

Number Format Builder

For creating different number format codes in a simpler way, GemBox.Spreadsheet provides a NumberFormatBuilder helper class. With it, you can easily apply the desired number formats.

The following example shows how to create the most common Excel number formats.

Number formats created with helper class called Number Format Builder
Screenshot of number formats created with Number Format Builder
View on GitHub 
using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("sheet");
        worksheet.Columns[0].SetWidth(200, LengthUnit.Pixel);

        // Show the value as a number with two decimal places and a thousands separator.
        worksheet.Cells[0, 0].Style.NumberFormat =
            NumberFormatBuilder.Number(2, useThousandsSeparator: true);
        worksheet.Cells[0, 0].Value = 2500.333;

        // Show the value in Euros and display negative values in parentheses.
        worksheet.Cells[1, 0].Style.NumberFormat =
            NumberFormatBuilder.Currency("€", 2, useParenthesesToDisplayNegativeValue: true);
        worksheet.Cells[1, 0].Value = -50;

        // Show the value in an accounting format with three decimal places.
        worksheet.Cells[2, 0].Style.NumberFormat =
            NumberFormatBuilder.Accounting(3, currencySymbol: "$");
        worksheet.Cells[2, 0].Value = -50;

        // Show the value in the ISO 8061 date format.
        worksheet.Cells[3, 0].Style.NumberFormat =
            NumberFormatBuilder.DateTimeIso8061();
        worksheet.Cells[3, 0].Value = DateTime.Now;

        // Show the value as a percentage.
        worksheet.Cells[4, 0].Style.NumberFormat =
            NumberFormatBuilder.Percentage(2);
        worksheet.Cells[4, 0].Value = 1/3d;

        // Show the value as a fraction with 100 as the denominator.
        worksheet.Cells[5, 0].Style.NumberFormat =
            NumberFormatBuilder.FractionWithPreciseDenominator(100);
        worksheet.Cells[5, 0].Value = 1/3d;

        // Show the value in scientific notation using two decimal places.
        worksheet.Cells[6, 0].Style.NumberFormat =
            NumberFormatBuilder.Scientific(2);
        worksheet.Cells[6, 0].Value = Math.Pow(Math.PI, 10);

        workbook.Save("Number Format Builder.%OutputFileType%");
    }
}
Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("sheet")
        worksheet.Columns(0).SetWidth(200, LengthUnit.Pixel)

        ' Show the value as a number with two decimal places and a thousands separator.
        worksheet.Cells(0, 0).Style.NumberFormat =
            NumberFormatBuilder.Number(2, useThousandsSeparator:=True)
        worksheet.Cells(0, 0).Value = 2500.333

        ' Show the value in Euros And display negative values in parentheses.
        worksheet.Cells(1, 0).Style.NumberFormat =
            NumberFormatBuilder.Currency("€", 2, useParenthesesToDisplayNegativeValue:=True)
        worksheet.Cells(1, 0).Value = -50

        ' Show the value in an accounting format with three decimal places.
        worksheet.Cells(2, 0).Style.NumberFormat =
            NumberFormatBuilder.Accounting(3, currencySymbol:="$")
        worksheet.Cells(2, 0).Value = -50

        ' Show the value in the ISO 8061 date format.
        worksheet.Cells(3, 0).Style.NumberFormat =
            NumberFormatBuilder.DateTimeIso8061()
        worksheet.Cells(3, 0).Value = DateTime.Now

        ' Show the value as a percentage.
        worksheet.Cells(4, 0).Style.NumberFormat =
            NumberFormatBuilder.Percentage(2)
        worksheet.Cells(4, 0).Value = 1 / 3D

        ' Show the value as a fraction with 100 as the denominator.
        worksheet.Cells(5, 0).Style.NumberFormat =
            NumberFormatBuilder.FractionWithPreciseDenominator(100)
        worksheet.Cells(5, 0).Value = 1 / 3D

        ' Show the value in scientific notation using two decimal places.
        worksheet.Cells(6, 0).Style.NumberFormat =
            NumberFormatBuilder.Scientific(2)
        worksheet.Cells(6, 0).Value = Math.Pow(Math.PI, 10)

        workbook.Save("Number Format Builder.%OutputFileType%")
    End Sub
End Module

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.

Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.

Published: December 13, 2018 | Modified: September 8, 2021 | Author: Stipo Rubic