How to Set Cell Data Formats in C# with IronXL

How Do I Set Cell Data Formats in Excel?

The FormatString property can be accessed through both cells and ranges. Therefore, it is possible to set data formats for individual cells, columns, rows, and any selected range. This flexibility makes IronXL particularly powerful when you need to work with Excel files in C# without Interop.

Data formatting is essential for presenting information clearly in spreadsheets. Whether creating financial reports with currency values, scientific data with precise decimal places, or date-based analytics, proper formatting ensures your data is both readable and professional. IronXL simplifies this process by providing direct access to Excel's formatting engine through a straightforward API.

:path=/static-assets/excel/content-code-examples/how-to/set-cell-data-format.cs

using IronXL;
using IronXL.Formatting;
using System;
using System.Linq;

// Create a new workbook
WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Set the data format to 12300.00%
workSheet["A1"].Value = 123;
workSheet["A1"].FormatString = BuiltinFormats.Percent2;

// Set the data format to 123.0000
workSheet["A2"].Value = 123;
workSheet["A2"].FormatString = "0.0000";

// Set data display format to range
DateTime dateValue = new DateTime(2020, 1, 1, 12, 12, 12);
workSheet["A3"].Value = dateValue;
workSheet["A4"].Value = new DateTime(2022, 3, 3, 10, 10, 10);
workSheet["A5"].Value = new DateTime(2021, 2, 2, 11, 11, 11);

IronXL.Range range = workSheet["A3:A5"];

// Set the data format to 1/1/2020 12:12:12
range.FormatString = "MM/dd/yy h:mm:ss";

workBook.SaveAs("dataFormats.xlsx");

Imports IronXL
Imports IronXL.Formatting
Imports System
Imports System.Linq

' Create a new workbook
Private workBook As WorkBook = WorkBook.Create()
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Set the data format to 12300.00%
Private workSheet("A1").Value = 123
Private workSheet("A1").FormatString = BuiltinFormats.Percent2

' Set the data format to 123.0000
Private workSheet("A2").Value = 123
Private workSheet("A2").FormatString = "0.0000"

' Set data display format to range
Private dateValue As New DateTime(2020, 1, 1, 12, 12, 12)
Private workSheet("A3").Value = dateValue
Private workSheet("A4").Value = New DateTime(2022, 3, 3, 10, 10, 10)
Private workSheet("A5").Value = New DateTime(2021, 2, 2, 11, 11, 11)

Private range As IronXL.Range = workSheet("A3:A5")

' Set the data format to 1/1/2020 12:12:12
range.FormatString = "MM/dd/yy h:mm:ss"

workBook.SaveAs("dataFormats.xlsx")

:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-assign-stringvalue.cs

// Assign value as string
workSheet["A1"].StringValue = "4402-12";

' Assign value as string
workSheet("A1").StringValue = "4402-12"

How Do I Use Built-in Formats in IronXL?

The IronXL library offers a variety of predefined format strings that can be accessed through the IronXL.Formatting.BuiltinFormats class for formatting Excel cells. This allows you to customize how data is displayed in your Excel spreadsheets. These built-in formats cover the most common business scenarios and are compatible with all Excel versions.

When creating spreadsheets programmatically, applying consistent formatting is crucial for maintaining professional standards across your documents. The BuiltinFormats class provides over 30 pre-configured format patterns that match Excel's standard formatting options.

:path=/static-assets/excel/content-code-examples/how-to/set-cell-data-format-builtin-formats.cs

using IronXL;
using IronXL.Formatting;

// Create a new workbook
WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Use builtin formats
workSheet["A1"].Value = 123;
workSheet["A1"].FormatString = BuiltinFormats.Accounting0;

workBook.SaveAs("builtinDataFormats.xlsx");

Imports IronXL
Imports IronXL.Formatting

' Create a new workbook
Private workBook As WorkBook = WorkBook.Create()
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Use builtin formats
Private workSheet("A1").Value = 123
Private workSheet("A1").FormatString = BuiltinFormats.Accounting0

workBook.SaveAs("builtinDataFormats.xlsx")

What Built-in Data Formats Are Available?

For the Duration type, input values are represented in days. For example, 1 day equals 24 hours, and 1.05 days equals 25 hours and 12 minutes. This is particularly useful when working with Excel data that involves time tracking or project management. Here are the available format types:

What Does Each Format Type Do?

• General: Default format displaying numbers without specific formatting.
• Duration1: Shows duration in minutes and seconds like "mm:ss."
• Duration2: Shows duration in hours, minutes, and seconds like "[h]:mm:ss."
• Duration3: Shows duration in minutes, seconds, and milliseconds like "mm:ss.0."
• Accounting0: Accounting format without decimals, parentheses for negatives like "#,##0_);(#,##0)."
• Accounting0Red: Accounting format without decimals, red for negatives like "#,##0_);Red."
• Accounting2: Accounting format with two decimals, parentheses for negatives like "#,##0.00;(#,##0.00)."
• Accounting2Red: Accounting format with two decimals, red for negatives like "#,##0.00_);Red."
• Time1: 12-hour format with AM/PM like "h:mm AM/PM."
• Time2: 12-hour format with seconds and AM/PM like "h:mm:ss AM/PM."
• Time3: 24-hour format without seconds like "h:mm."
• Time4: 24-hour format with seconds like "h:mm:ss."
• ShortDate: Short date format like "m/d/yy."
• ShortDateAndTime: Short date and time format like "m/d/yy h:mm."
• LongDate1: Long date with month, day, year like "d-mmm-yy."
• LongDate2: Long date with day and abbreviated month like "d-mmm."
• LongDate3: Long date with abbreviated month and year like "mmm-yy."
• Fraction1: Fractional values with one digit like "# ?/?."
• Fraction2: Fractional values with two digits like "# ??/??."
• Scientific1: Scientific notation with one decimal like "##0.0E+0."
• Scientific2: Scientific notation with two decimals like "0.00E+00."
• Percent: Percentage without decimals like "0%."
• Percent2: Percentage with two decimals like "0.00%."
• Currency0: Currency without decimals, parentheses for negatives like "$#,##0_);($#,##0)."
• Currency0Red: Currency without decimals, red for negatives like "$#,##0_);Red."
• Currency2: Currency with two decimals, parentheses for negatives like "$#,##0.00;($#,##0.00)."
• Currency2Red: Currency with two decimals, red for negatives like "$#,##0.00_);Red."
• Thousands0: Numbers with thousands separator, no decimals like "#,##0."
• Thousands2: Numbers with thousands separator, two decimals like "#,##0.00."
• Number0: Numbers without decimals like "0."
• Number2: Numbers with two decimals like "0.00."
• Text: Plain text format like "@."

Data formatting and number formatting in Excel control how numbers, dates, times, and other data display in cells, enhancing readability and ensuring data accuracy. With data formats, you can present information in specific formats like percentages or currencies. Similarly, number formats let you customize decimal places and display options.

How Do I Apply Multiple Formats to Different Ranges?

In real-world applications, you often need to apply different formats to various parts of your spreadsheet. This example demonstrates formatting a financial report with multiple data types. This approach is particularly useful when exporting to Excel in C# from databases or other data sources:

/* :path=/static-assets/excel/content-code-examples/how-to/set-cell-data-format-financial-report.cs */
using IronXL;
using IronXL.Formatting;
using System;

// Create a financial report with multiple formats
WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Headers
workSheet["A1"].Value = "Financial Report Q4 2023";
workSheet["A1"].Style.Font.Bold = true;

// Revenue section with currency formatting
workSheet["A3"].Value = "Revenue";
workSheet["B3"].Value = 2500000;
workSheet["B3"].FormatString = BuiltinFormats.Currency0;

// Growth percentage
workSheet["A4"].Value = "YoY Growth";
workSheet["B4"].Value = 0.157;
workSheet["B4"].FormatString = BuiltinFormats.Percent2;

// Date formatting
workSheet["A6"].Value = "Report Date";
workSheet["B6"].Value = DateTime.Now;
workSheet["B6"].FormatString = BuiltinFormats.LongDate1;

// Apply accounting format to expense columns
IronXL.Range expenseRange = workSheet["B8:B12"];
expenseRange.FormatString = BuiltinFormats.Accounting2Red;

// Save the formatted report
workBook.SaveAs("FinancialReport_Q4_2023.xlsx");

/* :path=/static-assets/excel/content-code-examples/how-to/set-cell-data-format-financial-report.cs */
using IronXL;
using IronXL.Formatting;
using System;

// Create a financial report with multiple formats
WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Headers
workSheet["A1"].Value = "Financial Report Q4 2023";
workSheet["A1"].Style.Font.Bold = true;

// Revenue section with currency formatting
workSheet["A3"].Value = "Revenue";
workSheet["B3"].Value = 2500000;
workSheet["B3"].FormatString = BuiltinFormats.Currency0;

// Growth percentage
workSheet["A4"].Value = "YoY Growth";
workSheet["B4"].Value = 0.157;
workSheet["B4"].FormatString = BuiltinFormats.Percent2;

// Date formatting
workSheet["A6"].Value = "Report Date";
workSheet["B6"].Value = DateTime.Now;
workSheet["B6"].FormatString = BuiltinFormats.LongDate1;

// Apply accounting format to expense columns
IronXL.Range expenseRange = workSheet["B8:B12"];
expenseRange.FormatString = BuiltinFormats.Accounting2Red;

// Save the formatted report
workBook.SaveAs("FinancialReport_Q4_2023.xlsx");

IRON VB CONVERTER ERROR developers@ironsoftware.com

This example shows how different format types combine to create professional financial documents. For more advanced formatting options, including conditional formatting, you can extend these concepts further.

When working with large datasets or creating reports programmatically, consistent formatting becomes critical. IronXL's formatting capabilities integrate seamlessly with other features like math functions and edit formulas, allowing you to create fully automated reporting solutions.

For developers migrating from Office Interop or seeking better performance, IronXL's formatting system provides a significant advantage. Unlike traditional approaches, you can format thousands of cells efficiently without COM interop overhead. To learn more about Excel automation best practices, explore our comprehensive Excel documentation.