How to Set Cell Data Formats in C# with IronXL
IronXL enables formatting Excel cells with built-in data formats like currency, percentages, dates, and custom number formats by setting the FormatString property on cells or ranges without Interop dependencies.
This sample shows how to create a new Excel workbook and apply a built-in format to a specific cell using IronXL's API. No Interop dependencies needed - just set FormatString and save.
-
1Install IronXL with NuGet Package Manager
-
2Copy and run this code snippet.
IronXL.WorkBook book = IronXL.WorkBook.Create(); book.DefaultWorkSheet["B2"].FormatString = IronXL.Formatting.BuiltinFormats.Currency2;C# -
3Deploy to test on your live environment
Start using IronXL in your project today with a free trial
Minimal Workflow (5 steps)
- Download the C# library to set data format
- Open existing or create a new Excel spreadsheet
- Set the FormatString property of the cell, column, row, or range to the desired data format
- Use the built-in data format with BuiltinFormats class
- Export the edited spreadsheet in various file types
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.
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")
How Can I Set Cell Values as Strings Without Conversion?
When setting the value in IronXL, use StringValue instead of Value to directly assign the exact value to the cell without automatic conversion. This works like placing an apostrophe before the cell value in Excel. This technique is particularly useful when working with product codes, phone numbers, or other data that should remain as text. For more advanced string manipulation, check out how to write Excel values in .NET.
// 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.
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:
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");Imports IronXL
Imports IronXL.Formatting
Imports System
' Create a financial report with multiple formats
Dim workBook As WorkBook = WorkBook.Create()
Dim workSheet As 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
Dim expenseRange As IronXL.Range = workSheet("B8:B12")
expenseRange.FormatString = BuiltinFormats.Accounting2Red
' Save the formatted report
workBook.SaveAs("FinancialReport_Q4_2023.xlsx")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 system integrates 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.
Frequently Asked Questions
What is the main advantage of using IronXL for Excel cell formatting in C#?
IronXL enables Excel cell formatting in C# without relying on Interop, allowing developers to use built-in data formats like currency, percentages, dates, and custom number formats through the `FormatString` property.
How can I format a single cell in Excel using IronXL?
To format a single cell, set the `FormatString` property on the desired cell or range. For example, `workBook.DefaultWorkSheet["B2"].FormatString = IronXL.Formatting.BuiltinFormats.Currency2;` applies a currency format to cell B2.
Can IronXL format entire ranges of cells in an Excel sheet?
Yes, IronXL allows setting data formats for individual cells, columns, rows, and any selected range using the `FormatString` property, making it versatile for handling extensive formatting tasks.
What built-in formats are available in IronXL?
IronXL provides over 30 built-in format patterns accessible through the `IronXL.Formatting.BuiltinFormats` class, covering common scenarios like currency, percentage, scientific notation, and various date and time formats.
How do I handle string values in IronXL without automatic conversion?
To prevent automatic conversion of string values, use the `StringValue` property instead of `Value`. This is useful for maintaining data like product codes or phone numbers as text.
Is it possible to apply multiple formats in a single spreadsheet using IronXL?
Yes, you can apply different formats to various spreadsheet parts using IronXL. This is ideal for creating financial reports with diverse data types and maintaining professionalism across documents.
Does IronXL offer any performance benefits over Interop for Excel formatting?
IronXL provides significant performance improvements over traditional Interop by eliminating COM interop overhead, allowing efficient formatting of thousands of cells without relying on Office components.
How does IronXL's formatting enhance Excel reporting in C#?
IronXL’s formatting system integrates seamlessly with math functions and formula edits, making it invaluable for creating fully automated Excel reports programmatically.
What is a quick way to start using IronXL for formatting Excel cells?
To get started with IronXL, download the C# library, create a new or open an existing Excel worksheet, set the `FormatString` property to the desired format, and export your edited spreadsheet.
How does IronXL support data format customization?
Besides built-in formats, IronXL allows you to define custom formats using the `FormatString` property, providing flexibility when presenting specific types of data in Excel cells.
Curtis Chau holds a Bachelor’s degree in Computer Science (Carleton University) and specializes in front-end development with expertise in Node.js, TypeScript, JavaScript, and React. Passionate about crafting intuitive and aesthetically pleasing user interfaces, Curtis enjoys working with modern frameworks and creating well-structured, visually appealing manuals.
