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, or how it's displayed in Excel. 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

C#
VB.NET

View on GitHub

using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the 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 the 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

C#
VB.NET

View on GitHub

using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the 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 the 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

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy

Published: December 13, 2018 | Modified: December 19, 2022 | Author: Stipo Rubic

Excel cell number format

Number Format Builder

See also

Next steps