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:
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
