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#]

using Spire.Xls;
namespace SetNumberFormat
{
    class Program
    {

        static void Main(string[] args)
        {
            //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]

Imports Spire.Xls
Namespace SetNumberFormat
	Class Program

		Private Shared Sub Main(args As String())
			'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")
		End Sub
	End Class
End Namespace