1. IronXL
  2. 操作指南
  3. 设置单元格数据格式
如何在 Excel 中设置单元格数据格式

How to Set Cell Data Formats

Chaknith Bin
Chaknith Bin
已更新:
This article was translated from English: Does it need improvement?
Translated
View the article in English

Data formatting and number formatting in Excel allow 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.

Quickstart: Apply Built-In Format to a Single Cell in One Line

This sample shows how easy it is to create a new Excel workbook and apply a built-in format to a specific cell using IronXL’s API. You’ll be up and running in seconds, with no Interop dependencies—just set “FormatString” and save.

Nuget IconGet started making PDFs with NuGet now:

  1. Install IronXL with NuGet Package Manager

    PM > Install-Package IronXL.Excel

  2. Copy and run this code snippet.

    IronXL.WorkBook book = IronXL.WorkBook.Create(); book.DefaultWorkSheet["B2"].FormatString = IronXL.Formatting.BuiltinFormats.Currency2;

  3. Deploy to test on your live environment

    Start using IronXL in your project today with a free trial
    arrow pointer

Minimal Workflow (5 steps)

  1. Download the C# library to set data format
  2. Open existing or create a new Excel spreadsheet
  3. Set the FormatString property of the cell, column, row, or range to the desired data format
  4. Use the built-in data format with BuiltinFormats class
  5. Export the edited spreadsheet in various file types


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")
$vbLabelText   $csharpLabel
Data Format

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 like 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"
$vbLabelText   $csharpLabel

Use Builtin Formats Example

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.

: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")
$vbLabelText   $csharpLabel

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:

All Available Data Formats

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 considered 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).

常见问题解答

如何使用C#在Excel中设置单元格数据格式？

您可以通过访问工作表并设置单元格的FormatString属性，使用IronXL在Excel中设置单元格数据格式。这允许自定义数据的显示方式，如数字、日期和时间，而无需使用Interop。

使用C#库进行Excel数据格式化有什么好处？

使用像IronXL这样的C#库进行Excel数据格式化，使您能够以编程方式控制数据的外观，增强可读性，确保数据准确性，并避免使用Interop的复杂性。它简化了如设置数字格式、日期和自定义格式等任务。

如何使用C#在Excel单元格中应用自定义数字格式？

要使用IronXL对单元格应用自定义数字格式，请加载Excel工作簿，访问工作表，并将单元格的FormatString属性设置为所需的自定义格式。例如，要将数字显示为百分比，请使用sheet["A1"].FormatString = "0.00%"。

我可以使用C#库保留Excel单元格中的前导零吗？

可以，使用IronXL时，可以通过在设置单元格的值时使用StringValue属性来保留前导零。这种方法可以防止自动转换并保留单元格中的前导零。

如何使用C#将Excel单元格格式化为会计样式？

要使用IronXL将Excel单元格格式化为会计样式，利用BuiltinFormats类。例如，您可以通过sheet["C3"].FormatString = IronXL.Formatting.BuiltinFormats.Accounting2将两个小数位的会计格式应用到单元格。

C#库中的Excel有哪些预定义的数据格式？

IronXL提供了如会计、持续时间、时间、日期、分数、科学、百分比、货币和数字等预定义的数据格式。这些内置格式简化了Excel电子表格中数据呈现的自定义。

如何使用C#在Excel中将负货币值显示为红色？

要使用IronXL将负货币值显示为红色，设置FormatString属性为包含红色负数的货币格式，如Currency0Red或Currency2Red。例如：sheet["B4"].FormatString = IronXL.Formatting.BuiltinFormats.Currency2Red。

IronXL中的Number0和Number2格式有何不同？

在IronXL中，Number0格式不显示小数位，而Number2格式显示两个小数位数。 这些格式有助于定制Excel工作表中数据的精确度。

如何在使用C#格式化Excel工作簿后保存更改？

在用IronXL格式化Excel工作簿后，要保存更改，请在WorkBook对象上使用SaveAs方法。指定所需的文件名和格式来保存更新的工作簿。

在使用C#库设置Excel数据格式时，您有哪些故障排除技巧？

在使用IronXL设置Excel数据格式时，确保库已正确集成，验证所用的格式字符串是否准确，并检查工作簿保存过程中的任何异常。此外，考虑使用StringValue属性进行精确的数据输入。

Chaknith Bin
Chaknith Bin
  立即与工程团队聊天
软件工程师
Chaknith 在 IronXL 和 IronBarcode 工作。他在 C# 和 .NET 方面有着深厚的专业知识，帮助改进软件并支持客户。他从用户互动中获得的见解有助于更好的产品、文档和整体体验。
准备开始了吗？
Nuget 下载 1,686,155 | 版本: 2025.11 刚刚发布