在 C# 中将 Excel 列（或单元格）格式化为文本？

Format an Excel column (or cell) as Text in C#?

提问by Tony_Henrich

I am losing the leading zeros when I copy values from a datatable to an Excel sheet. That's because probably Excel treats the values as a number instead of text.

当我将值从数据表复制到 Excel 工作表时，我丢失了前导零。那是因为 Excel 可能将这些值视为数字而不是文本。

I created the worksheet in C# and I am copying the values like so:

我在 C# 中创建了工作表，我正在复制这样的值：

myWorksheet.Cells[i + 2, j] = dtCustomers.Rows[i][j - 1].ToString();

How do I format a whole column or each cell as Text? A related question, how to cast myWorksheet.Cells[i + 2, j]to show a style property in Intellisense?

如何将整列或每个单元格格式化为文本？一个相关的问题，如何myWorksheet.Cells[i + 2, j]在 Intellisense 中转换以显示样式属性？

采纳答案by Joe Erickson

Below is some code to format columns A and C as text in SpreadsheetGear for .NETwhich has an API which is similar to Excel - except for the fact that SpreadsheetGear is frequently more strongly typed. It should not be too hard to figure out how to convert this to work with Excel / COM:

下面是一些代码，用于将列 A 和 C 格式化为用于 .NET 的 SpreadsheetGear 中的文本，它有一个类似于 Excel 的 API - 除了 SpreadsheetGear 经常被强类型化的事实。弄清楚如何将其转换为使用 Excel / COM 应该不会太难：

IWorkbook workbook = Factory.GetWorkbook();
IRange cells = workbook.Worksheets[0].Cells;
// Format column A as text.
cells["A:A"].NumberFormat = "@";
// Set A2 to text with a leading '0'.
cells["A2"].Value = "01234567890123456789";
// Format column C as text (SpreadsheetGear uses 0 based indexes - Excel uses 1 based indexes).
cells[0, 2].EntireColumn.NumberFormat = "@";
// Set C3 to text with a leading '0'.
cells[2, 2].Value = "01234567890123456789";
workbook.SaveAs(@"c:\tmp\TextFormat.xlsx", FileFormat.OpenXMLWorkbook);

Disclaimer: I own SpreadsheetGear LLC

免责声明：我拥有 SpreadsheetGear LLC

回答by Scott

I've recently battled with this problem as well, and I've learned two things about the above suggestions.

我最近也在与这个问题作斗争，我从上述建议中学到了两件事。

1. Setting the numberFormatting to @ causes Excel to left-align the value, and read it as if it were text, however, it still truncates the leading zero.
2. Adding an apostrophe at the beginning results in Excel treating it as text and retains the zero, and then applies the default text format, solving both problems.

1. 将 numberFormatting 设置为 @ 会导致 Excel 左对齐该值，并将其作为文本读取，但是，它仍会截断前导零。
2. 在开头添加撇号会导致 Excel 将其视为文本并保留零，然后应用默认文本格式，从而解决这两个问题。

The misleading aspect of this is that you now have a different value in the cell. Fortuately, when you copy/paste or export to CSV, the apostrophe is not included.

这样做的误导性方面是您现在在单元格中拥有不同的值。幸运的是，当您复制/粘贴或导出到 CSV 时，不包括撇号。

Conclusion: use the apostrophe, not the numberFormatting in order to retain the leading zeros.

结论：使用撇号，而不是 numberFormatting 以保留前导零。

回答by Arsac

   if (dtCustomers.Columns[j - 1].DataType != typeof(decimal) && dtCustomers.Columns[j - 1].DataType != typeof(int))
   {
      myWorksheet.Cells[i + 2, j].NumberFormat = "@";
   }

回答by svenGUTT

If you set the cell formatting to Text priorto adding a numeric value with a leading zero, the leading zero is retained without having to skew results by adding an apostrophe. If you try and manually add a leading zero value to a default sheet in Excel and then convert it to text, the leading zero is removed. If you convert the cell to Text first, then add your value, it is fine. Same principle applies when doing it programatically.

如果在添加带有前导零的数值之前将单元格格式设置为“文本” ，则将保留前导零，而不必通过添加撇号来扭曲结果。如果您尝试手动将前导零值添加到 Excel 中的默认工作表，然后将其转换为文本，则会删除前导零。如果您先将单元格转换为文本，然后添加您的值，就可以了。以编程方式执行此操作时适用相同的原则。

        // Pull in all the cells of the worksheet
        Range cells = xlWorkBook.Worksheets[1].Cells;
        // set each cell's format to Text
        cells.NumberFormat = "@";
        // reset horizontal alignment to the right
        cells.HorizontalAlignment = XlHAlign.xlHAlignRight;

        // now add values to the worksheet
        for (i = 0; i <= dataGridView1.RowCount - 1; i++)
        {
            for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
            {
                DataGridViewCell cell = dataGridView1[j, i];
                xlWorkSheet.Cells[i + 1, j + 1] = cell.Value.ToString();
            }
        }

回答by sansalk

Before your write to Excel need to change the format:

在您写入 Excel 之前需要更改格式：

xlApp = New Excel.Application
xlWorkSheet = xlWorkBook.Sheets("Sheet1")

Dim cells As Excel.Range = xlWorkSheet.Cells

'set each cell's format to Text
cells.NumberFormat = "@"

'reset horizontal alignment to the right
cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

回答by sarh

Solution that worked for me for Excel Interop:

对我来说适用于 Excel Interop 的解决方案：

myWorksheet.Columns[j].NumberFormat = "@";      // column as a text
myWorksheet.Cells[i + 2, j].NumberFormat = "@"; // cell as a text

This code should run beforeputting data to Excel. Column and row numbers are 1-based.

此代码应在将数据放入 Excel之前运行。列号和行号从 1 开始。

A bit more details. Whereas accepted response with reference for SpreadsheetGear looks almost correct, I had two concerns about it:

再详细一点。虽然对 SpreadsheetGear 参考的接受响应看起来几乎是正确的，但我对此有两个担忧：

1. I am not using SpreadsheetGear. I was interested in regular Excel communication thru Excel interop without any 3rdparty libraries,
2. I was searching for the way to format column by number, not using ranges like "A:A".

1. 我没有使用 SpreadsheetGear。我对通过 Excel 互操作进行常规 Excel 通信感兴趣，无需任何 3rdparty 库，
2. 我正在寻找按数字格式化列的方法，而不是使用“A：A”之类的范围。

回答by Lester

Use your WorkSheet.Columns.NumberFormat, and set it to string "@", here is the sample:

使用您的WorkSheet.Columns.NumberFormat，并将其设置为string "@"，这是示例：

Excel._Worksheet workSheet = (Excel._Worksheet)_Excel.Worksheets.Add();
//set columns format to text format
workSheet.Columns.NumberFormat = "@";

Note: this text format will apply for your hole excel sheet!

注意：此文本格式将适用于您的孔 Excel 表！

If you want a particular column to apply the text format, for example, the first column, you can do this:

如果您希望特定列应用文本格式，例如，第一列，您可以执行以下操作：

workSheet.Columns[0].NumberFormat = "@";

or this will apply the specified range of woorkSheet to text format:

或者这会将指定范围的 woorkSheet 应用于文本格式：

workSheet.get_Range("A1", "D1").NumberFormat = "@";

回答by KR Akhil

//where [1] - column number which you want to make text

ExcelWorksheet.Columns[1].NumberFormat = "@";


//If you want to format a particular column in all sheets in a workbook - use below code. Remove loop for single sheet along with slight changes.

  //path were excel file is kept


     string ResultsFilePath = @"C:\Users\krakhil\Desktop\TGUW EXCEL\TEST";

            Excel.Application ExcelApp = new Excel.Application();
            Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(ResultsFilePath);
            ExcelApp.Visible = true;

            //Looping through all available sheets
            foreach (Excel.Worksheet ExcelWorksheet in ExcelWorkbook.Sheets)
            {                
                //Selecting the worksheet where we want to perform action
                ExcelWorksheet.Select(Type.Missing);
                ExcelWorksheet.Columns[1].NumberFormat = "@";
            }

            //saving excel file using Interop
            ExcelWorkbook.Save();

            //closing file and releasing resources
            ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
            Marshal.FinalReleaseComObject(ExcelWorkbook);
            ExcelApp.Quit();
            Marshal.FinalReleaseComObject(ExcelApp);

回答by Patrik

I know this question is aged, still, I would like to contribute.

我知道这个问题已经过时了，但我还是想做出贡献。

Applying Range.NumberFormat = "@"just partially solve the problem:

应用Range.NumberFormat = "@"只是部分解决问题：

• Yes, if you place the focus on a cell of the range, you will read textin the format menu
• Yes, it align the data to the left
• But if you use the typeformula to check the type of the value in the cell, it will return 1 meaning number

• 是的，如果您将焦点放在范围的单元格上，您将阅读格式菜单中的文本
• 是的，它将数据左对齐
• 但是如果你使用类型公式来检查单元格中值的类型，它会返回1 表示数字

Applying the apostroph behave better. It sets the format to text, it align data to left and if you check the format of the value in the cell using the typeformula, it will return 2 meaning text

应用撇号表现更好。它将格式设置为text，将数据向左对齐，如果您使用类型公式检查单元格中值的格式，它将返回2 表示文本