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.

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.

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