将Excel列(或单元格)格式化为c#中的文本?

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表中时，我将丢失前导0。这是因为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?

如何将整个列或每个单元格格式化为文本?一个相关的问题，如何铸造我的工作表。单元格[i + 2, j]在智能意义上显示样式属性?

8 个解决方案

#1

Below is some code to format columns A and C as text in SpreadsheetGear for .NET which 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中的文本，它的API类似于Excel——除了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公司

#2

If you set the cell formatting to Text prior to 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();
            }
        }

#3

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

#4

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. 将数字格式设置为@将导致Excel左对齐值，并将其读取为文本，但是，它仍然截断前导0。
3. 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.
4. 在开头添加撇号会导致Excel将其视为文本并保留0，然后应用默认的文本格式，从而解决这两个问题。

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.

结论:使用撇号而不是数字格式来保持前导零。

#5

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

#6

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 before putting 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. 我没有使用电子表格。我感兴趣的是在没有任何第三方图书馆的情况下，通过Excel interop进行常规的Excel通信。
3. I was searching for the way to format column by number, not using ranges like "A:A".
4. 我在寻找按数字格式化列的方法，而不是使用“A:A”这样的范围。

#7

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 = "@";

#8

//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);