在 C# 中将 Excel 列(或单元格)格式化为文本?
当我将数据表中的值复制到 Excel 工作表时,我丢失了前导零。这是因为 Excel 可能将这些值视为数字而不是文本。
我正在复制值,如下所示:
myWorksheet.Cells[i + 2, j] = dtCustomers.Rows[i][j - 1].ToString();
如何将整列或每个单元格格式化为文本?
一个相关的问题,如何转换 myWorksheet.Cells[i + 2, j] 以在 Intellisense 中显示样式属性?
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.
I am copying the values like so:
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(10)
下面是一些代码,用于将 A 列和 C 列格式化为 SpreadsheetGear for .NET 中的文本,它具有类似的 API Excel - 除了 SpreadsheetGear 通常具有更强的类型这一事实之外。弄清楚如何将其转换为与 Excel / COM 一起使用应该不会太难:
免责声明:我拥有 SpreadsheetGear LLC
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:
Disclaimer: I own SpreadsheetGear LLC
如果将单元格格式设置为文本优先添加带前导零的数值,则前导零将被保留,而不必通过添加撇号来扭曲结果。如果您尝试手动将前导零值添加到 Excel 中的默认工作表,然后将其转换为文本,则前导零将被删除。如果您先将单元格转换为文本,然后添加您的值,那就可以了。以编程方式执行此操作时也适用相同的原则。
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 Interop 解决方案:
此代码应在将数据放入 Excel 之前运行。列号和行号从 1 开始。
更多细节。尽管已接受的参考 SpreadsheetGear 的回复看起来几乎正确,但我对此有两个担忧:
通过 Excel 互操作进行通信,无需任何第三方库,
范围如“A:A”。
Solution that worked for me for Excel Interop:
This code should run before putting data to Excel. Column and row numbers are 1-based.
A bit more details. Whereas accepted response with reference for SpreadsheetGear looks almost correct, I had two concerns about it:
communication thru Excel interop without any 3rdparty libraries,
ranges like "A:A".
在写入 Excel 之前需要更改格式:
Before your write to Excel need to change the format:
我最近也在与这个问题作斗争,并且我从上述建议中学到了两件事。
其误导性在于您现在在单元格中具有不同的值。幸运的是,当您复制/粘贴或导出到 CSV 时,不包含撇号。
结论:使用撇号,而不是数字格式来保留前导零。
I've recently battled with this problem as well, and I've learned two things about the above suggestions.
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.
Conclusion: use the apostrophe, not the numberFormatting in order to retain the leading zeros.
使用您的
WorkSheet.Columns.NumberFormat
,并将其设置为字符串“@”
,示例如下:注意:此文本格式将适用于您的孔 Excel 工作表!
如果您希望特定列应用文本格式,例如第一列,您可以这样做:
或者这会将 woorkSheet 的指定范围应用到文本格式:
Use your
WorkSheet.Columns.NumberFormat
, and set it tostring "@"
, here is the sample:Note: this text format will apply for your hole excel sheet!
If you want a particular column to apply the text format, for example, the first column, you can do this:
or this will apply the specified range of woorkSheet to text format:
我知道这个问题已经过时了,但我仍然愿意做出贡献。
应用
Range.NumberFormat = "@"
只是部分解决问题:应用撇号表现得更好。它将格式设置为文本,将数据向左对齐,如果您使用类型公式检查单元格中值的格式,它将返回2含义文本
I know this question is aged, still, I would like to contribute.
Applying
Range.NumberFormat = "@"
just partially solve the problem: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 type formula, it will return 2 meaning text
您需要将列格式化为字符串。
您可以使用链接 https://supportcenter.devexpress .com/ticket/details/t679279/import-from-excel-to-gridview
ExcelDataSource的转换,也可以参考https://supportcenter.devexpress.com/ticket/details/t468253/how-to-convert-exceldatasource-to-datatable< /a>
You need to format the column to be a string.
You can use the link https://supportcenter.devexpress.com/ticket/details/t679279/import-from-excel-to-gridview
For converting the ExcelDataSource, you can also refer to https://supportcenter.devexpress.com/ticket/details/t468253/how-to-convert-exceldatasource-to-datatable