Excel Number Format

VB C#

using IronXL;
using System;
using System.Linq;

// Load an existing WorkSheet
WorkBook workBook = WorkBook.Load("sample.xls");
WorkSheet workSheet = workBook.WorkSheets.First();

// Set data display format to cell
// The cell value will look like 12300%
workSheet["A2"].Value = 123;
workSheet["A2"].FormatString = "0.0%";

// The cell value will look like 123.0000
workSheet["A2"].First().FormatString = "0.0000";

// Set data display format to range
DateTime dateValue = new DateTime(2020, 1, 1, 12, 12, 12);
workSheet["A3"].Value = dateValue;
workSheet["A4"].First().Value = new DateTime(2022, 3, 3, 10, 10, 10);
workSheet["A5"].First().Value = new DateTime(2021, 2, 2, 11, 11, 11);

var range = workSheet["A3:A5"];

// The cell(A3) value will look like 1/1/2020 12:12:12 PM
range.FormatString = "MM/dd/yy h:mm:ss";

workBook.SaveAs("numberFormats.xls");

Imports IronXL
Imports System
Imports System.Linq

' Load an existing WorkSheet
Private workBook As WorkBook = WorkBook.Load("sample.xls")
Private workSheet As WorkSheet = workBook.WorkSheets.First()

' Set data display format to cell
' The cell value will look like 12300%
Private workSheet("A2").Value = 123
Private workSheet("A2").FormatString = "0.0%"

' The cell value will look like 123.0000
workSheet("A2").First().FormatString = "0.0000"

' Set data display format to range
Dim dateValue As New DateTime(2020, 1, 1, 12, 12, 12)
workSheet("A3").Value = dateValue
workSheet("A4").First().Value = New DateTime(2022, 3, 3, 10, 10, 10)
workSheet("A5").First().Value = New DateTime(2021, 2, 2, 11, 11, 11)

Dim range = workSheet("A3:A5")

' The cell(A3) value will look like 1/1/2020 12:12:12 PM
range.FormatString = "MM/dd/yy h:mm:ss"

workBook.SaveAs("numberFormats.xls")

Install-Package IronXL.Excel

Excel Number Format

We can use the FormatString property to format the display value of any Excel Cell or Range in C# with IronXL.

Using workSheet["A2"] will only select the Range of the specified address. To access a Cell, the First() method can be used. However, the FormatString property can be set directly from both Cell and Range. Therefore, the First() method can often be omitted. More Excel number formats can be applied using the same method as shown in the code example below.

You can download a project file from this link.

How to use Excel `NumberFormat` in C#

Download C# library to use Excel number format
Load an existing Excel file with the Load method
Set the FormatString property of a cell to specify the number format.
Export the edited spreadsheet with the SaveAs method

using IronXL; // Import the IronXL namespace to use its Excel manipulation features.

class ExcelNumberFormatExample
{
    static void Main()
    {
        // Load the existing Excel workbook
        WorkBook workbook = WorkBook.Load("example.xlsx");

        // Get the first worksheet from the workbook
        WorkSheet workSheet = workbook.WorkSheets.First();

        // Format cell "A2" in the worksheet to display numbers with two decimal places
        // Using "0.00" format will ensure that numbers are displayed with two decimal places.
        workSheet["A2"].Value = 123.456789;
        workSheet["A2"].FormatString = "0.00";

        // Save the changes to a new Excel file
        workbook.SaveAs("formatted_example.xlsx");
    }
}

using IronXL; // Import the IronXL namespace to use its Excel manipulation features.

class ExcelNumberFormatExample
{
    static void Main()
    {
        // Load the existing Excel workbook
        WorkBook workbook = WorkBook.Load("example.xlsx");

        // Get the first worksheet from the workbook
        WorkSheet workSheet = workbook.WorkSheets.First();

        // Format cell "A2" in the worksheet to display numbers with two decimal places
        // Using "0.00" format will ensure that numbers are displayed with two decimal places.
        workSheet["A2"].Value = 123.456789;
        workSheet["A2"].FormatString = "0.00";

        // Save the changes to a new Excel file
        workbook.SaveAs("formatted_example.xlsx");
    }
}

Imports IronXL ' Import the IronXL namespace to use its Excel manipulation features.

Friend Class ExcelNumberFormatExample
	Shared Sub Main()
		' Load the existing Excel workbook
		Dim workbook As WorkBook = WorkBook.Load("example.xlsx")

		' Get the first worksheet from the workbook
		Dim workSheet As WorkSheet = workbook.WorkSheets.First()

		' Format cell "A2" in the worksheet to display numbers with two decimal places
		' Using "0.00" format will ensure that numbers are displayed with two decimal places.
		workSheet("A2").Value = 123.456789
		workSheet("A2").FormatString = "0.00"

		' Save the changes to a new Excel file
		workbook.SaveAs("formatted_example.xlsx")
	End Sub
End Class

$vbLabelText $csharpLabel

In this code example, the IronXL library is used to manipulate Excel files. The WorkBook.Load method loads an existing Excel file, and the workSheet["A2"] syntax selects a specific cell within the worksheet to apply formatting. The FormatString property is then set to "0.00" to ensure that the cell value is displayed with two decimal places. Finally, workbook.SaveAs saves the modified workbook to a new file.