Excel cell number format

The following example shows how you can use GemBox.Spreadsheet to get formatted cell value. Formatted cell value is a textual representation of a cell value which is formatted with a number format applied on that cell.

Using this example you can also find out how specific number format from MS Excel should be used in GemBox.Spreadsheet API. Create new Excel file with MS Excel and populate its A2 cell in the first worksheet with a value and number format. Use that file as input to this example ('Upload your file' below) and run the example. Use value from output file's D column in GemBox.Spreadsheet API.

Excel cell numeric and text values formatted with GemBox.Spreadsheet
Excel cell numeric and text values formatted with GemBox.Spreadsheet
Upload your file (Drag file here) 
using GemBox.Spreadsheet;

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

        var workbook = ExcelFile.Load("%InputFileName%");

        var worksheet = workbook.Worksheets[0];

        worksheet.Cells[0, 2].Value = "ExcelCell.Value";
        worksheet.Columns[2].Style.NumberFormat = "@";

        worksheet.Cells[0, 3].Value = "CellStyle.NumberFormat";
        worksheet.Columns[3].Style.NumberFormat = "@";

        worksheet.Cells[0, 4].Value = "ExcelCell.GetFormattedValue()";
        worksheet.Columns[4].Style.NumberFormat = "@";

        for (int i = 1; i < worksheet.Rows.Count; i++)
        {
            var sourceCell = worksheet.Cells[i, 0];

            worksheet.Cells[i, 2].Value = sourceCell.Value?.ToString();
            worksheet.Cells[i, 3].Value = sourceCell.Style.NumberFormat;
            worksheet.Cells[i, 4].Value = sourceCell.GetFormattedValue();
        }

        // Set column widths.
        var columnWidths = new double[] { 192, double.NaN, 122, 236, 200 };
        for (int i = 0; i < columnWidths.Length; i++)
            if (!double.IsNaN(columnWidths[i]))
                worksheet.Columns[i].SetWidth(columnWidths[i], LengthUnit.Pixel);

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

Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook = ExcelFile.Load("%InputFileName%")

        Dim worksheet = workbook.Worksheets(0)

        worksheet.Cells(0, 2).Value = "ExcelCell.Value"
        worksheet.Columns(2).Style.NumberFormat = "@"

        worksheet.Cells(0, 3).Value = "CellStyle.NumberFormat"
        worksheet.Columns(3).Style.NumberFormat = "@"

        worksheet.Cells(0, 4).Value = "ExcelCell.GetFormattedValue()"
        worksheet.Columns(4).Style.NumberFormat = "@"

        For i As Integer = 0 To worksheet.Rows.Count

            Dim sourceCell = worksheet.Cells(i, 0)

            worksheet.Cells(i, 2).Value = sourceCell.Value?.ToString()
            worksheet.Cells(i, 3).Value = sourceCell.Style.NumberFormat
            worksheet.Cells(i, 4).Value = sourceCell.GetFormattedValue()
        Next

        ' Set column widths.
        Dim columnWidths = New Double() {192, Double.NaN, 122, 236, 200}
        For i As Integer = 0 To columnWidths.Length - 1
            If Not Double.IsNaN(columnWidths(i)) Then worksheet.Columns(i).SetWidth(columnWidths(i), LengthUnit.Pixel)
        Next

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

Number Format Builder

To create a number format string, you can use a NumberFormatBuilder static class that allows you to create different number formats in a simple way.

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

Number formats created with GemBox.Spreadsheet
Screenshot of Excel file with formatted cells
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 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: December 4, 2020 | Author: Stipo Rubic