Mastering Excel Number Formatting in C#

#csharp #dotnet

In the realm of C# development, precise control over how numbers appear in Excel files is far more than a cosmetic concern—it’s a cornerstone of data integrity, professional communication, and user trust. Whether you’re generating financial reports for stakeholders, exporting transaction logs for audits, or creating dashboards for operational teams, consistent and accurate number formatting directly impacts how data is interpreted.

This guide explores how to leverage the free library Free Spire.XLS for .NET to master Excel number formatting in C#.

Why Number Formatting Matters

Before diving into technical details, let’s clarify why meticulous number formatting is critical:

Professionalism: Consistent formats (e.g., "$1,234.56" instead of "1234.56") signal attention to detail, enhancing the credibility of your reports.
Clarity: Formats like percentages ("12.3%") or dates ("2024-03-15") eliminate ambiguity, ensuring readers interpret values correctly.
Compliance: Industries like finance or healthcare often mandate specific formats (e.g., ISO dates, currency codes) for regulatory adherence.
Usability: Thousands separators, aligned decimals, and color-coded negatives reduce cognitive load, making large datasets easier to parse.

Installation

Free Spire.XLS is a free library that simplifies Excel file manipulation, including robust number formatting. To get started, install Free Spire.XLS via NuGet (the easiest method):

Open the NuGet Package Manager in Visual Studio.
Search for FreeSpire.XLS and install the latest version.
Alternatively, use the Package Manager Console:

   Install-Package FreeSpire.XLS

Supported Number Format Types

Free Spire.XLS supports a wide range of number formats, controlled via the NumberFormat property of CellRange objects. Below is an expanded breakdown of common formats, their syntax, and use cases:

Format Type	C# Format String Example	Output Example	Use Case
Basic Integer	`"0"`	`123`	Counts, IDs, or whole numbers
Fixed Decimals	`"0.00"`	`123.46`	Precise measurements (e.g., weight in kg)
Thousands Separator	`"#,##0"`	`1,234`	Large numbers (e.g., population)
Currency (General)	`"$#,##0.00"`	`$1,234.56`	Global currency display
Percentage	`"0.0%"`	`12.3%`	Growth rates, proportions
Date	`"yyyy-MM-dd"`	`2024-03-15`	Standardized date logs
Scientific Notation	`"0.00E+00"`	`1.23E+03`	Very large/small numbers (e.g., scientific data)
Custom Text + Number	`"Order: 0000"`	`Order: 0123`	Invoices, reference codes

Set Excel Number Format - C# Code Example

using Spire.Xls;

namespace SetNumberFormat
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Integer
            sheet.Range["A1"].NumberValue = 123;
            sheet.Range["A1"].NumberFormat = "00";

            // Thousands separator
            sheet.Range["A2"].NumberValue = 1234.5678;
            sheet.Range["A2"].NumberFormat = "#,##0.00";

            // Percentage
            sheet.Range["A3"].NumberValue = 0.12345;
            sheet.Range["A3"].NumberFormat = "0.0%";

            // Number with text
            sheet.Range["A4"].NumberValue = 1234;
            sheet.Range["A4"].NumberFormat = "\"Quantity: \"0";

            // Currency format
            sheet.Range["A5"].NumberValue = 1234.5678;
            sheet.Range["A5"].NumberFormat = "￥#,##0.00";

            // Scientific notation
            sheet.Range["A6"].NumberValue = 1234.5678;
            sheet.Range["A6"].NumberFormat = "0.00E+00";

            // Date
            sheet.Range["A7"].NumberValue = 45930;
            sheet.Range["A7"].NumberFormat = "yyyy-MM-dd";

            // Time
            sheet.Range["D13"].NumberValue = 0.5;
            sheet.Range["D13"].NumberFormat = "h:mm:ss AM/PM";

            // Save the result
            workbook.SaveToFile("NumberFormats.xlsx", ExcelVersion.Version2016);
        }
    }
}

Conclusion

Mastering Excel number formatting in C# with Free Spire.XLS ensures your reports are professional, readable, and fit for their intended audience. The examples in this guide provide a foundation, but don’t hesitate to experiment with custom formats to match your specific business needs.

DEV Community