Example

This example demonstrates how to use NumberFormat property to apply data formatting.

Output file: console-numberformat.xls

[C#]
using System;
using NativeExcel;

namespace Console_NumberFormat
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileName = "console-numberformat.xls";
            CreateWorkbook(FileName);
            OpenWorkbookWithExcel(FileName);
        }

        static void CreateWorkbook(string FileName) {
            //Create a new workbook
            IWorkbook book = NativeExcel.Factory.CreateWorkbook();
            //Add worksheet
            IWorksheet sheet = book.Worksheets.Add();
            IRange range = sheet.Cells[2,1,8,5]; //Range A2:E8"
            range.ColumnWidth = 20;

            //numeric values
            int row = 1;
            range.Rows[row++].Value = 100; 
            range.Rows[row++].Value = 3.4567; 
            range.Rows[row++].Value = 0.345; 
            range.Rows[row++].Value = 0; 
            range.Rows[row++].Value = -0.545; 
            range.Rows[row++].Value = -12.45622; 
            range.Rows[row++].Value = -200; 

            //number format for numeric values
            range.Columns[1].NumberFormat = "#,##0"; //without decimal places
            range.Columns[2].NumberFormat = "#,##0.00"; //with two decimal places
            range.Columns[3].NumberFormat = "#,##0.00;[Red]-#,##0.00"; 
            range.Columns[4].NumberFormat = "$#,##0.00;($#,##0.00)"; 
            range.Columns[5].NumberFormat = "General"; 

            range = sheet.Cells[11,1,17,5]; //Range A11:E17"
            row = 1;

            //date values
            range.Rows[row++].Value = DateTime.Today; 
            range.Rows[row++].Value = DateTime.Today.AddDays(-1); 
            range.Rows[row++].Value = DateTime.Today.AddDays(-2); 
            range.Rows[row++].Value = DateTime.Today.AddDays(-3); 
            range.Rows[row++].Value = DateTime.Today.AddDays(-4); 
            range.Rows[row++].Value = DateTime.Today.AddDays(-5); 
            range.Rows[row++].Value = DateTime.Today.AddDays(-6); 
            
            range.Columns[1].NumberFormat = "DD.MM.YYYY"; 
            range.Columns[2].NumberFormat = "MMMM, YYYY";
            range.Columns[3].NumberFormat = "MM.DD.YYYY hh:mm:ss"; 
            range.Columns[4].NumberFormat = "DD-MMM-YY"; 
            range.Columns[5].NumberFormat = "DD-MMM-YYYY"; 
           
            //Save workbook
            book.SaveAs(FileName);
        }

        static void OpenWorkbookWithExcel(string FileName){
            try {
                System.Diagnostics.Process.Start(FileName);
            } catch {
                Console.WriteLine(FileName + " created in application folder");
            }
        }
    }
}

[Visual Basic]
imports System
imports NativeExcel

Module Console_NumberFormat

    Sub Main()
        Dim FileName As String = "console-numberformat.xls"
        CreateWorkbook(FileName)
        OpenWorkbookWithExcel(FileName)
    End Sub

    Sub CreateWorkbook(FileName As String)
        'Create a new workbook
        Dim book As IWorkbook = NativeExcel.Factory.CreateWorkbook()
        'Add worksheet
        Dim sheet As IWorksheet = book.Worksheets.Add()

        Dim range As IRange = sheet.Cells(2,1,8,5) 'Range A2:E8
        range.ColumnWidth = 20

        'numeric values
        Dim row As Integer = 1
        range.Rows(row).Value = 100
        row = row + 1
        range.Rows(row).Value = 3.4567
        row = row + 1
        range.Rows(row).Value = 0.345
        row = row + 1
        range.Rows(row).Value = 0
        row = row + 1
        range.Rows(row).Value = -0.545
        row = row + 1
        range.Rows(row).Value = -12.45622
        row = row + 1
        range.Rows(row).Value = -200

        'number format for numeric values
        range.Columns(1).NumberFormat = "#,##0"     'without decimal places
        range.Columns(2).NumberFormat = "#,##0.00"  'with two decimal places
        range.Columns(3).NumberFormat = "#,##0.00;[Red]-#,##0.00"
        range.Columns(4).NumberFormat = "$#,##0.00;($#,##0.00)"
        range.Columns(5).NumberFormat = "General"

        range = sheet.Cells(11,1,17,5)  'Range A11:E17
        row = 1

        'date values
        range.Rows(row).Value = DateTime.Today
        row = row + 1
        range.Rows(row).Value = DateTime.Today.AddDays(-1) 
        row = row + 1
        range.Rows(row).Value = DateTime.Today.AddDays(-2) 
        row = row + 1
        range.Rows(row).Value = DateTime.Today.AddDays(-3) 
        row = row + 1
        range.Rows(row).Value = DateTime.Today.AddDays(-4) 
        row = row + 1
        range.Rows(row).Value = DateTime.Today.AddDays(-5) 
        row = row + 1
        range.Rows(row).Value = DateTime.Today.AddDays(-6)
            
        range.Columns(1).NumberFormat = "DD.MM.YYYY"
        range.Columns(2).NumberFormat = "MMMM, YYYY"
        range.Columns(3).NumberFormat = "MM.DD.YYYY hh:mm:ss"
        range.Columns(4).NumberFormat = "DD-MMM-YY"
        range.Columns(5).NumberFormat = "DD-MMM-YYYY"

        'Save workbook
        book.SaveAs(FileName)

    End Sub

    Sub OpenWorkbookWithExcel(FileName As String)
        Try
           System.Diagnostics.Process.Start(FileName)
        Catch
           Console.WriteLine(FileName + " created in application folder")
        End Try  
    End Sub

End Module

---