How to Programmatically Customize Number Formatting in C# Excel XLSX

Number formatting in Excel enhances the readability and clarity of data, making it easier to interpret. It ensures that numbers are displayed correctly, such as rounding decimal places or formatting as currency, which helps avoid confusion. Consistent formatting across a dataset reduces errors and improves data integrity. It also contributes to the professional appearance of reports, which is essential when sharing or presenting data. Proper formatting is necessary for specific calculations, as Excel treats the various formats (like dates or percentages) differently. Excel’s customization options allow for tailored number formats that suit particular needs. Ultimately, number formatting improves both the presentation and functionality of your data.

Why Use Programmatic Customization

Microsoft Excel intelligently applies the relevant data formatting automatically when data is entered manually, populated from a source, or copied from another location, such as an HTML table. However, there are situations when the applied format changes the data’s purpose or nature. Some examples include:

• numbers stored in some regional formats are stored in General format and thus are not evaluated correctly in formulas
• identifiers with leading zeros are converted to Number format, therefore losing the zeros
• dates are converted to their number counterparts

Such situations require manually setting the correct and relevant Number Format to the data.

In this blog, we will review some popular data scenarios where number formatting is required in an Excel file and how they can be applied using the Document Solutions for Excel API library for .NET. We will explore the following topics:

• Set up and Installation
• Common Number Formatting Scenarios
• Advanced Customizations

Ready to Try It Out? Download Document Solutions for Excel Today!

Setting Up DsExcel for Number Formatting

Installation and Setup of DsExcel API

To begin, download and install the DsExcel package to your application using the NuGet Package Manager. Search for the package “DS.Documents.Excel” on NuGet and install it to the application as shown below:

NumberFormat Property in DsExcel

DsExcel supports the NumberFormat property with every text object, such as Chart’s Title, DataLabels, Cell/Range, Text Style, Pivot Fields, etc. It accepts a string in the following syntax:

<format for positive number>;<format for negative number>;<format for zero>;<format for text>

In most common scenarios, the format code contains one section of the syntax, such as "General" or "##.00," and the format is applied to all data types—positive numbers, negative numbers, zeros, and text. However, several data scenarios use multiple sections of the syntax separated by a semicolon ( ; ), as covered in the topics listed below.

Common Number Formatting Scenarios

Import Dates Stored as Text

Often, numeric or date-type data generated by enterprise applications are stored as texts. This happens mostly when data is stored as JSON or CSV. For example:

[
. . . 
  {
    "Area": "North America",
    "City": "Chicago",
    "Category": "Consumer Electronics",
    "Name": "Bose 785593-0050",
    "Revenue": 92800,
    "SalesFrom": "1/10/2022",
    "SalesTo": "1/11/2022"
  },
  . . .
]

When this data is loaded into a workbook’s cell, it is stored in General number format. To save it as a date in the generated Excel spreadsheet, apply the number format to the respective data columns after importing data as depicted in the code below:

// Create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.AutoParse = true;
// Get data from json file
string jsonText = string.Empty;
using (Stream stream = GetResourceStream("json\\DataBinding.json"))
using (StreamReader reader = new StreamReader(stream))
{
    jsonText = reader.ReadToEnd();
}
IWorksheet worksheet = workbook.ActiveSheet;
worksheet.DataSource = new JsonDataSource(jsonText);            
worksheet.Columns[5].NumberFormat = "dd/mm/yyyy";
worksheet.Columns[6].NumberFormat = "d/m/yy";
// Save to an excel file
workbook.Save("JsonSource.xlsx");

The output is shown in the image below:

Handling Large Numbers

For numbers longer than 12 digits, Excel usually displays them in scientific format rather than the standard number format, such as UPC/GTIN code, ID, etc.

This is due to Excel's 15-digit precision limit, which was implemented to preserve accuracy. To change long numbers from scientific notation to a standard number format, set the NumberFormat property as depicted in the code below:

IWorkbook workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Worksheets.Add().Name = "Big Number";
workbook.Worksheets["Big Number"].Range["A1"].Value = "From Data";
workbook.Worksheets["Big Number"].Range["B1"].Value = "Converted to number";            

workbook.Worksheets["Big Number"].Range["A2:B2"].Value = 6589423145697;            
workbook.Worksheets["Big Number"].Range["B2"].NumberFormat = "0";

The following image depicts a long number displayed in Excel with and without the number format applied:

Format Numbers as Thousands, Millions, or Billions

When dealing with large numbers, especially in terms of price, amount, or specific figures such as population sizes, it is common to display the numbers on a shorter scale, such as in the thousands or millions. Large numbers can be formatted to different number scales using NumberFormat, as depicted in the code below:

workbook.Worksheets.Add().Name = "NumberScale";

workbook.Worksheets["NumberScale"].Range["A1"].Value = "Number scale Format";
workbook.Worksheets["NumberScale"].Range["A1:D1"].MergeCells = true;

workbook.Worksheets["NumberScale"].Range["A3"].Value = "Number";
workbook.Worksheets["NumberScale"].Range["B3"].Value = "Thousands";
workbook.Worksheets["NumberScale"].Range["C3"].Value = "Million";
workbook.Worksheets["NumberScale"].Range["D3"].Value = "Billion";

workbook.Worksheets["NumberScale"].Range["A4:D4"].Value = 456321456;
workbook.Worksheets["NumberScale"].Range["B4"].NumberFormat = "0.00, \"K\"";    
workbook.Worksheets["NumberScale"].Range["C4"].NumberFormat = "$0.00,, \"M\"";    
workbook.Worksheets["NumberScale"].Range["D4"].NumberFormat = "$#.##,,, \"B\"";

Representing a number on different scales is depicted in the image below:

Adding Leading and Trailing Zeros

Numbers representing entities, such as Postal/Zip code and system-generated identifiers (which can start or end in zero, such as 00123, 1234.5600, etc.), are automatically converted to numbers in Excel by removing the leading and trailing zeros. To retain the zeros, set the Range’s NumberFormat property by using the placeholder “0”, as depicted in the code snippet below:

workbook.Worksheets.Add().Name = "Zeros";
workbook.Worksheets["Big Number"].Range["A1"].Value = "From Data";
workbook.Worksheets["Big Number"].Range["B1"].Value = "Converted to number";

workbook.Worksheets["Big Number"].Range["A2"].Value = 00123;
workbook.Worksheets["Big Number"].Range["A2"].NumberFormat = "00000";

workbook.Worksheets["Big Number"].Range["B2"].Value = 1234.5600;
workbook.Worksheets["Big Number"].Range["B2"].NumberFormat = "#.0000";

The numbers will appear with and without the proper number formatting, as shown in the image below:

Conditional Formatting for Numbers and Text

Suppose the dataset contains a mix of positive numbers, negative numbers, and text content - for instance, a survey result with positive or negative ratings and comments. To programmatically apply conditional formatting, NumberFormat can highlight the values for quick readability. NumberFormat supports the eight primary colors, whose values are Black, Green, Red, White, Blue, Magenta, Yellow, and Cyan. To add these colors within the NumberFormat, utilize the square brackets [ ] for the respective number, as shown in the code below:

object[,] data = new object[,]{
      {"Name", "City", "Result"},
      {"Richard", "New York", 10 },
      {"Nia", "New York", 5},
      {"Jared", "New York",-6 },
      {"Natalie", "Washington",-2 },
      {"Damon", "Washington", "Unimpressive" },
      {"Angela", "Washington", "Unimpressive" }
};

workbook.Worksheets.Add().Name = "Conditional Format";
workbook.Worksheets["Conditional Format"].Range["A1:C7"].Value = data;            
workbook.Worksheets["Conditional Format"].Range["C2:C7"].NumberFormat = "General;[Red]-General;;[Blue]General";
workbook.Worksheets["Conditional Format"].Columns.AutoFit();

See the “Result” column in the image below:

Conditionally Format Numbers by Replacing them with Text

Data like test scores, Boolean flags, age, etc., are often meant to be binary values. For analysis purposes, it is required to replace those numbers with meaningful, categorized, or readable text such as Pass/Fail, True/False, and so on. This can be achieved by setting the NumberFormat property with the desired condition placed within square brackets [ ], as shown below:

object[,] data1 = new object[,]{
    {"Name", "Subject", "Result"},
    {"Richard", "English", 70 },
    {"Richard", "Elective-I", 65},
    {"Richard", "Science",45 },
    {"Richard", "Mathematics",56 },
    {"Richard", "Elective-II", 25 },
    {"Richard", "Language", 38 },
    {"Natalie", "English", 30 },
    {"Natalie", "Elective-I", 45},
    {"Natalie", "Science",53 },
    {"Natalie", "Mathematics",28 },
    {"Natalie", "Elective-II", 32 },
    {"Natalie", "Language", 69 }
};
workbook.Worksheets.Add().Name = "DisguiseNumber";
workbook.Worksheets["DisguiseNumber"].Range["A1:C13"].Value = data1;
//disguise number without color code
workbook.Worksheets["DisguiseNumber"].Range["C2:C13"].NumberFormat = "[<35]\"Fail\";\"Pass\"";
//disguise number with color code
//workbook.Worksheets["DisguiseNumber"].Range["C2:C13"].NumberFormat = "[<35][Red]\"Fail\";[Green]\"Pass\"";
workbook.Worksheets["DisguiseNumber"].Columns.AutoFit();

The image below shows the original data in column C and the converted text in column D based on whether or not the condition was met.

Advanced Customizations

Displaying Data in Different Locale Formats

With cloud systems and global businesses, data is not necessarily available in a specific locale. Data, especially numbers and dates, is stored and displayed as text or converted to a different format in Excel.

Using the NumberFormat property, we can add the locale explicitly to display the values in a specific format. With DsExcel, working with culture-specific data requires the three steps below:

1. Set the culture info for the workbook
2. Parse the numbers/dates
3. Apply the number formatting

workbook.Worksheets.Add().Name = "Culture";             
            
workbook.Worksheets["Culture"].Range["A1"].Value = "Date as Text";
workbook.Worksheets["Culture"].Range["A2"].Value = "12/27/2022";
workbook.Worksheets["Culture"].Range["A3"].Value = "08/07/2022";

workbook.Culture = CultureInfo.GetCultureInfo("en-US"); //set culture of data available
workbook.AutoParse = true;

workbook.Worksheets["Culture"].Range["B1"].Value = "Date converted to system locale (not US)";
workbook.Worksheets["Culture"].Range["B2"].Value = "12/27/2022";
workbook.Worksheets["Culture"].Range["B3"].Value = "08/07/2022";

workbook.Worksheets["Culture"].Range["C1"].Value = "Formatted to US date format";
workbook.Worksheets["Culture"].Range["C2"].Value = "12/27/2022";
workbook.Worksheets["Culture"].Range["C3"].Value = "08/07/2022";
workbook.Worksheets["Culture"].Range["C2:C3"].NumberFormat = "[$-en-US]mm-dd-yyyy";

workbook.Worksheets["Culture"].Columns.AutoFit();

A converted date value based on the system locale (achieved through the above code) is shown below:

Check out the Document Solutions for Excel, .NET demo to apply a number format to culture-specific data!

Converting Decimals to Fractions

Fractions are a special way to present decimal numbers. To display decimals as fractions, include a forward slash (/) when setting the NumberFormat property, separating the integer portion with a space. For example:

• "# #/#" or "# ##/##", depending on the number of fraction remainder digits to be shown.
• "###/###", to display an improper fraction with up to 3 digits.
• "##/N", rounding fractions to a specific denominator with N^th base

The code snippet below showcases how to handle various fractional output formats based on your desired result:

workbook.Worksheets.Add().Name = "Fractions";

workbook.Worksheets["Fractions"].Range["A1"].Value = "Decimal";
workbook.Worksheets["Fractions"].Range["B1"].Value = "# ##/##";
workbook.Worksheets["Fractions"].Range["C1"].Value = "###/###";
workbook.Worksheets["Fractions"].Range["D1"].Value = "##/4";

workbook.Worksheets["Fractions"].Range["A2:D2"].Value = 0.25;         
workbook.Worksheets["Fractions"].Range["A3:D3"].Value = 5.64;     
workbook.Worksheets["Fractions"].Range["A4:D4"].Value = 2.5;

workbook.Worksheets["Fractions"].Range["B2:B4"].NumberFormat = "# ##/##";
workbook.Worksheets["Fractions"].Range["C2:C4"].NumberFormat = "###/###";
workbook.Worksheets["Fractions"].Range["D2:D4"].NumberFormat = "##/4";

workbook.Worksheets["Fractions"].Columns.AutoFit();

Below is an image of what the above code generates, showcasing the different fractional values displayed:

Hide a Cell Value

Hiding a cell value is an uncommon and unique scenario for most applications, but it does have its uses for situations where performing a hidden calculation from a cell that does not display any value may be ideal. For example, showing an icon set without the value in a cell. In the absence of a transparent color setting in Excel, cell content can be hidden using the NumberFormat property, as shown in the code below:

workbook.Worksheets[0].Range["A1"].NumberFormat = ";;;;" ;

Conclusion

Mastering number formatting in Excel is essential to ensure your data is both visually clear and functionally accurate. By understanding and utilizing Excel’s built-in number formats, along with programmatic customization options through tools like the DsExcel API, you can manage a variety of data scenarios—from handling large numbers and importing dates as text, to formatting for specific locales or hiding cell values. Proper number formatting not only prevents errors and ensures consistent data representation but also enhances the readability and professional appearance of your reports and analyses. Whether you're working with financial data, scientific figures, or survey results, using the right number format is key to presenting data in a way that is both meaningful and easy to interpret. With the flexibility of DsExcel’s advanced customization features, you can further streamline your workflows and ensure your data meets the exact needs of your business or project.

Ready to Try It Out? Download Document Solutions for Excel Today!

Learn More

DsExcel follows Microsoft Excel’s number format, so you can learn more about number codes and custom number formats from Microsoft. To learn how DsExcel can help you or your company accomplish your goals, please check out our extensive demos and documentation, or schedule a demo with our Document Solutions product manager.