Number formats

The following example shows you how to apply number formats including date, currency, percentage and custom patterns.

C# example

using System;
using Independentsoft.Office.Spreadsheet;
using Independentsoft.Office.Spreadsheet.Styles;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook book = new Workbook();

            // Register custom number formats
            MasterCellFormat dateFormat = new MasterCellFormat();
            dateFormat.NumberFormat = new NumberFormat(1, "dd/mm/yyyy");

            MasterCellFormat currencyFormat = new MasterCellFormat();
            currencyFormat.NumberFormat = new NumberFormat(2, "$#,##0.00");

            MasterCellFormat percentFormat = new MasterCellFormat();
            percentFormat.NumberFormat = new NumberFormat(3, "0.00%");

            MasterCellFormat customFormat = new MasterCellFormat();
            customFormat.NumberFormat = new NumberFormat(4, "#,##0.000");

            book.Styles.MasterCellFormats.Add(dateFormat);
            book.Styles.MasterCellFormats.Add(currencyFormat);
            book.Styles.MasterCellFormats.Add(percentFormat);
            book.Styles.MasterCellFormats.Add(customFormat);

            Worksheet sheet = new Worksheet();

            Cell a1 = new Cell(DateTime.Now);
            a1.Format = new CellFormat();
            a1.Format.NumberFormatID = 1;
            sheet["A1"] = a1;

            Cell a2 = new Cell(9999.99);
            a2.Format = new CellFormat();
            a2.Format.NumberFormatID = 2;
            sheet["A2"] = a2;

            Cell a3 = new Cell(0.1234);
            a3.Format = new CellFormat();
            a3.Format.NumberFormatID = 3;
            sheet["A3"] = a3;

            Cell a4 = new Cell(1234567.89);
            a4.Format = new CellFormat();
            a4.Format.NumberFormatID = 4;
            sheet["A4"] = a4;

            book.Sheets.Add(sheet);
            book.Save("c:\\test\\output.xlsx", true);
        }
    }
}

VB example

Imports System
Imports Independentsoft.Office.Spreadsheet
Imports Independentsoft.Office.Spreadsheet.Styles

Module Module1
    Sub Main(ByVal args As String())

        Dim book As New Workbook()

        Dim dateFormat As New MasterCellFormat()
        dateFormat.NumberFormat = New NumberFormat(1, "dd/mm/yyyy")

        Dim currencyFormat As New MasterCellFormat()
        currencyFormat.NumberFormat = New NumberFormat(2, "$#,##0.00")

        Dim percentFormat As New MasterCellFormat()
        percentFormat.NumberFormat = New NumberFormat(3, "0.00%")

        Dim customFormat As New MasterCellFormat()
        customFormat.NumberFormat = New NumberFormat(4, "#,##0.000")

        book.Styles.MasterCellFormats.Add(dateFormat)
        book.Styles.MasterCellFormats.Add(currencyFormat)
        book.Styles.MasterCellFormats.Add(percentFormat)
        book.Styles.MasterCellFormats.Add(customFormat)

        Dim sheet As New Worksheet()

        Dim a1 As New Cell(DateTime.Now)
        a1.Format = New CellFormat()
        a1.Format.NumberFormatID = 1
        sheet("A1") = a1

        Dim a2 As New Cell(9999.99)
        a2.Format = New CellFormat()
        a2.Format.NumberFormatID = 2
        sheet("A2") = a2

        Dim a3 As New Cell(0.1234)
        a3.Format = New CellFormat()
        a3.Format.NumberFormatID = 3
        sheet("A3") = a3

        Dim book2 As New Workbook()
        book.Sheets.Add(sheet)
        book.Save("c:\test\output.xlsx", True)

    End Sub
End Module

Spreadsheet .NET > Tutorial > Number formats

Need help? Ask our developers: