ExcelCell Number Format

Following example demonstrates 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.

ExcelCell Number Format Screenshot — Screenshot

See the full code below, use Run Example to execute.

C#
VB.NET

using GemBox.Spreadsheet;
using System;
using System.IO;

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = ExcelFile.Load("NumberFormat.xlsx");

        var ws = ef.Worksheets[0];

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

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

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

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

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

        // Auto-fit columns
        for (int i = 0; i < 5; i++)
            ws.Columns[i].AutoFit();

        ef.Save("Number Format.xlsx");
    }
}

Imports GemBox.Spreadsheet
Imports System.IO

Module Samples

    Sub Main()

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

        Dim ef As ExcelFile = ExcelFile.Load("NumberFormat.xlsx")

        Dim ws = ef.Worksheets(0)

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

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

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

        For i As Integer = 0 To ws.Rows.Count
            Dim sourceCell = ws.Cells(i, 0)

            If (sourceCell.Value IsNot Nothing) Then
                ws.Cells(i, 2).Value = sourceCell.Value.ToString()
            End If

            ws.Cells(i, 3).Value = sourceCell.Style.NumberFormat
            ws.Cells(i, 4).Value = sourceCell.GetFormattedValue()
        Next

        ' Auto-fit columns
        For i As Integer = 0 To 4
            ws.Columns(i).AutoFit()
        Next

        ef.Save("Number Format.xlsx")

    End Sub

End Module

Check next sample.