In Excel worksheets, the raw data is often displayed as plain numbers that lack intuitiveness. By setting number format, these numbers can be transformed into a more understandable form. For example, setting sales data in a currency format, i.e., adding a currency symbol and a thousands separator, can make the represented amounts clear at a glance. Formatting market share data into a percentage format can clearly shows the proportions of each part, facilitating quick comparisons and analysis. In this article, you will learn how to set number formats in Excel cells in C# using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Symbols in Excel Number Format
In Excel number format codes, several symbols are used to define how numbers are displayed. Below is a detailed explanation of the common symbols:
| Symbols | Description |
| 0 | A required digit placeholder, padded with zeros if necessary. |
| # | An optional digit placeholder. It does not display insignificant zeros. |
| ? | An optional digit placeholder. It adds spaces to align decimal points and trailing zeros for fractions. |
| . | Represents the decimal point. |
| , | Serves as a thousands separator. |
| ; | Separates different sections of a number format code for positive, negative, zero, and text values. |
| % | Multiplies the number by 100 and adds a percentage sign. |
| E - + | Scientific notation. |
| Currency ($, €, ¥, etc.) | Displays the respective currency symbol. |
| [Color] | Specifies a color for the number display. |
| Date/Time (yyyy, mmmm, mm, dd, hh, ss, AM/PM) | Represent year, full month name, month, day, hour, minute, second, and 12-hour clock markers respectively. |
Set Number Format in Excel Cells in C#
Spire.XLS for .NET provides the CellRange.NumberValue property to set the number value of a cell and the CellRange.NumberFormat property to set the number format with format code. The detailed steps are as follows:
- Create a Workbook instance.
- Get a specified worksheet through Workbook.Worksheets[] property.
- Get a specified cell through Worksheet.Range[] property.
- Add text to specified cells through CellRange.Text property.
- Add numeric values to specified cells through CellRange.NumberValue property, and then set the number formats through CellRange.NumberFormat property.
- Save the result file using Workbook.SaveToFile() method.
- C#
using Spire.Xls;
namespace SetNumberFormat
{
class Program
{
static void Main(string[] args)
{
// Create a Workbook instance
Workbook workbook = new Workbook();
// Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
// Add text to sepcified cells and set cell styles
sheet.Range["B3"].Text = "Value";
sheet.Range["C3"].Text = "Number Format";
sheet.Range["D3"].Text = "Display";
sheet.Range["E3"].Text = "Notes";
sheet.Range["B3:E3"].Style.Font.IsBold = true;
sheet.Range["B3:E3"].Style.KnownColor = ExcelColors.SkyBlue;
// Number padded with zeros
sheet.Range["B4"].Text = "123";
sheet.Range["C4"].Text = "0000";
sheet.Range["D4"].NumberValue = 123;
sheet.Range["D4"].NumberFormat = "0000";
sheet.Range["E4"].Text = "Number padded with zeros";
// Two-digit decimal number with thousands separator
sheet.Range["B5"].Text = "1234.5678";
sheet.Range["C5"].Text = "#,##0.00";
sheet.Range["D5"].NumberValue = 1234.5678;
sheet.Range["D5"].NumberFormat = "#,##0.00";
sheet.Range["E5"].Text = "Two-digit decimal number with thousands separator";
// Negative number in red
sheet.Range["B6"].Text = "-1234.5678";
sheet.Range["C6"].Text = "0;[Red]-0";
sheet.Range["D6"].NumberValue = -1234.5678;
sheet.Range["D6"].NumberFormat = "0;[Red]-0";
sheet.Range["E6"].Text = "Negative number in red";
// Percentage with one decimal place
sheet.Range["B7"].Text = "0.12345";
sheet.Range["C7"].Text = "0.0%";
sheet.Range["D7"].NumberValue = 0.12345;
sheet.Range["D7"].NumberFormat = "0.0%";
sheet.Range["E7"].Text = "Percentage with one decimal place";
// Number with text
sheet.Range["B8"].Text = "1234.5678";
sheet.Range["C8"].Text = "\"Quantity: \"0";
sheet.Range["D8"].NumberValue = 1234.5678;
sheet.Range["D8"].NumberFormat = "\"Quantity: \"0";
sheet.Range["E8"].Text = "Number with text";
// Number with currency symbol
sheet.Range["B9"].Text = "1234.5678";
sheet.Range["C9"].Text = "$#,##0.00";
sheet.Range["D9"].NumberValue = 1234.5678;
sheet.Range["D9"].NumberFormat = "$#,##0.00";
sheet.Range["E9"].Text = "Number with currency symbol";
// Scientific notation format
sheet.Range["B10"].Text = "1234.5678";
sheet.Range["C10"].Text = "0.00E+00";
sheet.Range["D10"].NumberValue = 1234.5678;
sheet.Range["D10"].NumberFormat = "0.00E+00";
sheet.Range["E10"].Text = "Scientific notation";
// Fraction
sheet.Range["B11"].Text = "0.5";
sheet.Range["C11"].Text = "# ?/?";
sheet.Range["D11"].NumberValue = 0.5;
sheet.Range["D11"].NumberFormat = "# ?/?";
sheet.Range["E11"].Text = "Fraction";
// Date
sheet.Range["B12"].Text = "45930";
sheet.Range["C12"].Text = "yyyy-MM-dd";
sheet.Range["D12"].NumberValue = 45930;
sheet.Range["D12"].NumberFormat = "yyyy-MM-dd";
sheet.Range["E12"].Text = "Date";
// Time
sheet.Range["B13"].Text = "0.5";
sheet.Range["C13"].Text = "h:mm:ss AM/PM";
sheet.Range["D13"].NumberValue = 0.5;
sheet.Range["D13"].NumberFormat = "h:mm:ss AM/PM";
sheet.Range["E13"].Text = "Time";
// Set cell styles for the used range
sheet.AllocatedRange.Style.Font.FontName = "Calibri";
sheet.AllocatedRange.Style.Font.Size = 11;
sheet.AllocatedRange.Style.HorizontalAlignment = HorizontalAlignType.Left;
// Autofit column width
sheet.AutoFitColumn(2);
sheet.AutoFitColumn(3);
sheet.AutoFitColumn(4);
sheet.AutoFitColumn(5);
// Save the result file
workbook.SaveToFile("ExcelNumberFormat.xlsx", ExcelVersion.Version2016);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
