How to Set Cell Data Formats

by Chaknith Bin

Data formatting and number formatting in Excel allows you to control how numbers, dates, times, and other data are displayed 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.

The IronXL library allows you to set data format or number format in C#. This library simplifies the process of creating, formatting, and manipulating Excel files programmatically, making it a valuable tool for data handling and presentation tasks in C# applications.

Install with NuGet

Install-Package IronXL.Excel

nuget.org/packages/IronXL.Excel/

or

Download DLL

Download DLL

Manually install into your project

Set Cell Data Formats Example

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.

: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")

Set Cell Value as String

When setting the value in IronXL, use StringValue instead of Value to directly assign the exact value to the cell without automatic conversion. This usage is similar to placing an apostrophe before the cell value in Excel.

: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"

Use Builtin Formats Example

The IronXL library offers a variety of predefined format strings which can be accessed through the IronXL.Formatting.BuiltinFormats class for formatting Excel cells, allowing you to customize how data is displayed in your Excel spreadsheets.

: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")

Available Builtin Data Formats

For the Duration type, input values are represented in days. For example, 1 day is equal to 24 hours, and 1.05 days is equivalent to 25 hours and 12 minutes. Here are the available format types:

Description

• General: The default format that displays numbers as they are without any specific formatting.
• Duration1: Displays duration values in minutes and seconds, like "mm:ss." Number input into the Value property will be consider as days.
• Duration2: Displays duration values in hours, minutes, and seconds, like "[h]:mm:ss."
• Duration3: Displays duration values in minutes, seconds, and milliseconds, like "mm:ss.0."
• Accounting0: Formats accounting values without decimal digits, using parentheses for negative values, like "#,##0_);(#,##0)."
• Accounting0Red: Formats accounting values without decimal digits, using red color for negative values, like "#,##0_);Red."
• Accounting2: Formats accounting values with two decimal digits, using parentheses for negative values, like "#,##0.00;(#,##0.00)."
• Accounting2Red: Formats accounting values with two decimal digits, using red color for negative values, like "#,##0.00_);Red."
• Time1: Formats time values in 12-hour clock format with AM/PM, like "h:mm AM/PM."
• Time2: Formats time values in 12-hour clock format with seconds and AM/PM, like "h:mm:ss AM/PM."
• Time3: Formats time values in 24-hour clock format without seconds, like "h:mm."
• Time4: Formats time values in 24-hour clock format with seconds, like "h:mm:ss."
• ShortDate: Formats date values in a short format, like "m/d/yy."
• ShortDateAndTime: Formats date and time values in a short format, like "m/d/yy h:mm."
• LongDate1: Formats date values in a long format with month, day, and year, like "d-mmm-yy."
• LongDate2: Formats date values in a long format with day and abbreviated month, like "d-mmm."
• LongDate3: Formats date values in a long format with abbreviated month and year, like "mmm-yy."
• Fraction1: Formats fractional values with one digit, like "# ?/?."
• Fraction2: Formats fractional values with two digits, like "# ??/??."
• Scientific1: Formats numbers in scientific notation with one digit after the dot, like "##0.0E+0."
• Scientific2: Formats numbers in scientific notation with two digits after the dot, like "0.00E+00."
• Percent: Formats percentage values without decimal digits, like "0%."
• Percent2: Formats percentage values with two decimal digits, like "0.00%."
• Currency0: Formats currency values without decimal digits, using parentheses for negative values, like "$#,##0_);($#,##0)."
• Currency0Red: Formats currency values without decimal digits, using red color for negative values, like "$#,##0_);Red."
• Currency2: Formats currency values with two decimal digits, using parentheses for negative values, like "$#,##0.00;($#,##0.00)."
• Currency2Red: Formats currency values with two decimal digits, using red color for negative values, like "$#,##0.00_);Red."
• Thousands0: Formats numbers with thousands separator and no decimal digits, like "#,##0."
• Thousands2: Formats numbers with thousands separator and two decimal digits, like "#,##0.00."
• Number0: Formats numbers without decimal digits, like "0."
• Number2: Formats numbers with two decimal digits, like "0.00."
• Text: Formats text values as plain text, like "@" (no specific formatting applied).