在 C# 中将 Excel 列(或单元格)格式化为文本? [英] Format an Excel column (or cell) as Text in C#?

问题描述

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

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.

我正在像这样复制值:

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

如何将整列或每个单元格的格式设置为文本?

How do I format a whole column or each cell as Text?

一个相关问题，如何在 Intellisense 中转换 myWorksheet.Cells[i + 2, j] 以显示样式属性?

A related question, how to cast myWorksheet.Cells[i + 2, j] to show a style property in Intellisense?

推荐答案

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

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:

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:	mpTextFormat.xlsx", FileFormat.OpenXMLWorkbook);

免责声明:我拥有 SpreadsheetGear LLC

Disclaimer: I own SpreadsheetGear LLC

这篇关于在 C# 中将 Excel 列(或单元格)格式化为文本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！