How to Set Number Format in Excel using Spire.XLS in C#, VB.NET

Number formatting is a way to control how a number is displayed using numeric format string. For example, you can use format string '0.00' to format 1234.5678 as 1234.57. Numeric format strings often consist of one or more custom numeric specifiers listed as below:

  • "#" - Digit placeholder
  • "0" - Zero placeholder
  • "," - Decimal point
  • "." - Decimal separator
  • "[Red]" - Color specifier
  • "%" - Percentage placeholder

To learn more about how format specifier works in a format string, please refer to "Custom Numeric Format Strings".

After creating a numeric format string, we can apply it to Range.NumberFormat property which sets format code for the Range object. In the following section, let's see more detailed steps for how to set number format in Excel with code.

Main Steps:

Step 1: Initialize workbook and worksheet.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

Step 2: Input a number value for the specified call and set the number format.

sheet.Range["B3"].Text = "0";
sheet.Range["C3"].NumberValue = 1234.5678;
sheet.Range["C3"].NumberFormat = "0";

sheet.Range["B4"].Text = "0.00";
sheet.Range["C4"].NumberValue = 1234.5678;
sheet.Range["C4"].NumberFormat = "0.00";

sheet.Range["B5"].Text = "#,##0.00";
sheet.Range["C5"].NumberValue = 1234.5678;
sheet.Range["C5"].NumberFormat = "#,##0.00";

sheet.Range["B6"].Text = "$#,##0.00";
sheet.Range["C6"].NumberValue = 1234.5678;
sheet.Range["C6"].NumberFormat = "$#,##0.00";

sheet.Range["B7"].Text = "0;[Red]-0";
sheet.Range["C7"].NumberValue = -1234.5678;
sheet.Range["C7"].NumberFormat = "0;[Red]-0";

sheet.Range["B8"].Text = "0.00;[Red]-0.00";
sheet.Range["C8"].NumberValue = -1234.5678;
sheet.Range["C8"].NumberFormat = "0.00;[Red]-0.00";

sheet.Range["B9"].Text = "#,##0;[Red]-#,##0";
sheet.Range["C9"].NumberValue = -1234.5678;
sheet.Range["C9"].NumberFormat = "#,##0;[Red]-#,##0";

sheet.Range["B10"].Text = "#,##0.00;[Red]-#,##0.000";
sheet.Range["C10"].NumberValue = -1234.5678;
sheet.Range["C10"].NumberFormat = "#,##0.00;[Red]-#,##0.00";

sheet.Range["B11"].Text = "0.00E+00";
sheet.Range["C11"].NumberValue = 1234.5678;
sheet.Range["C11"].NumberFormat = "0.00E+00";

sheet.Range["B12"].Text = "0.00%";
sheet.Range["C12"].NumberValue = 1234.5678;
sheet.Range["C12"].NumberFormat = "0.00%";

Step 3: Change the background color of the range and set column width to autofit.

sheet.Range["B3:B12"].Style.KnownColor = ExcelColors.Gray25Percent; 
sheet.AutoFitColumn(2);
sheet.AutoFitColumn(3);

Step 4: Save and launch the file.

workbook.SaveToFile("Sample.xls");
System.Diagnostics.Process.Start("Sample.xls",ExcelVersion.Version97to2003);

Output:

How to Set Number Format in Excel using Spire.XLS in C#, VB.NET

Entire Code:

[C#]
//initialize workbook and worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

sheet.Range["B1"].Text = "NUMBER FORMATTING";
sheet.Range["B1"].Style.Font.IsBold = true;

//input value for a call and set the number format
sheet.Range["B3"].Text = "0";
sheet.Range["C3"].NumberValue = 1234.5678;
sheet.Range["C3"].NumberFormat = "0";

sheet.Range["B4"].Text = "0.00";
sheet.Range["C4"].NumberValue = 1234.5678;
sheet.Range["C4"].NumberFormat = "0.00";

sheet.Range["B5"].Text = "#,##0.00";
sheet.Range["C5"].NumberValue = 1234.5678;
sheet.Range["C5"].NumberFormat = "#,##0.00";

sheet.Range["B6"].Text = "$#,##0.00";
sheet.Range["C6"].NumberValue = 1234.5678;
sheet.Range["C6"].NumberFormat = "$#,##0.00";

sheet.Range["B7"].Text = "0;[Red]-0";
sheet.Range["C7"].NumberValue = -1234.5678;
sheet.Range["C7"].NumberFormat = "0;[Red]-0";

sheet.Range["B8"].Text = "0.00;[Red]-0.00";
sheet.Range["C8"].NumberValue = -1234.5678;
sheet.Range["C8"].NumberFormat = "0.00;[Red]-0.00";

sheet.Range["B9"].Text = "#,##0;[Red]-#,##0";
sheet.Range["C9"].NumberValue = -1234.5678;
sheet.Range["C9"].NumberFormat = "#,##0;[Red]-#,##0";

sheet.Range["B10"].Text = "#,##0.00;[Red]-#,##0.000";
sheet.Range["C10"].NumberValue = -1234.5678;
sheet.Range["C10"].NumberFormat = "#,##0.00;[Red]-#,##0.00";

sheet.Range["B11"].Text = "0.00E+00";
sheet.Range["C11"].NumberValue = 1234.5678;
sheet.Range["C11"].NumberFormat = "0.00E+00";

sheet.Range["B12"].Text = "0.00%";
sheet.Range["C12"].NumberValue = 1234.5678;
sheet.Range["C12"].NumberFormat = "0.00%";

//change background color and set column width to autofit
sheet.Range["B3:B12"].Style.KnownColor = ExcelColors.Gray25Percent;
sheet.AutoFitColumn(2);
sheet.AutoFitColumn(3);

//save and launch the file
workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start("Sample.xls");
[VB.NET]
'initialize workbook and worksheet
Dim workbook As New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)

sheet.Range("B1").Text = "NUMBER FORMATTING"
sheet.Range("B1").Style.Font.IsBold = True

'input value for a call and set the number format
sheet.Range("B3").Text = "0"
sheet.Range("C3").NumberValue = 1234.5678
sheet.Range("C3").NumberFormat = "0"

sheet.Range("B4").Text = "0.00"
sheet.Range("C4").NumberValue = 1234.5678
sheet.Range("C4").NumberFormat = "0.00"

sheet.Range("B5").Text = "#,##0.00"
sheet.Range("C5").NumberValue = 1234.5678
sheet.Range("C5").NumberFormat = "#,##0.00"

sheet.Range("B6").Text = "$#,##0.00"
sheet.Range("C6").NumberValue = 1234.5678
sheet.Range("C6").NumberFormat = "$#,##0.00"

sheet.Range("B7").Text = "0;[Red]-0"
sheet.Range("C7").NumberValue = -1234.5678
sheet.Range("C7").NumberFormat = "0;[Red]-0"

sheet.Range("B8").Text = "0.00;[Red]-0.00"
sheet.Range("C8").NumberValue = -1234.5678
sheet.Range("C8").NumberFormat = "0.00;[Red]-0.00"

sheet.Range("B9").Text = "#,##0;[Red]-#,##0"
sheet.Range("C9").NumberValue = -1234.5678
sheet.Range("C9").NumberFormat = "#,##0;[Red]-#,##0"

sheet.Range("B10").Text = "#,##0.00;[Red]-#,##0.000"
sheet.Range("C10").NumberValue = -1234.5678
sheet.Range("C10").NumberFormat = "#,##0.00;[Red]-#,##0.00"

sheet.Range("B11").Text = "0.00E+00"
sheet.Range("C11").NumberValue = 1234.5678
sheet.Range("C11").NumberFormat = "0.00E+00"

sheet.Range("B12").Text = "0.00%"
sheet.Range("C12").NumberValue = 1234.5678
sheet.Range("C12").NumberFormat = "0.00%"

'change background color and set column width to autofit
sheet.Range("B3:B12").Style.KnownColor = ExcelColors.Gray25Percent
sheet.AutoFitColumn(2)
sheet.AutoFitColumn(3)

'save and launch the file
workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003)
System.Diagnostics.Process.Start("Sample.xls")