在 C# 中使用 Microsoft.Office.Interop.Excel 在 excel 列中设置数据类型，如数字、文本和日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13827709/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set data type like number, text and date in excel column using Microsoft.Office.Interop.Excel in c#
提问by Hyman1987
My problem is that I am trying to set data type to an excel column in C#, in this case the data types number, text and date. I am using the DLL Microsoft.Office.Interop.Excel. I don′t know how to set those formats to the cells of an entire excel column using C# has programming language.
我的问题是我试图将数据类型设置为 C# 中的 excel 列，在这种情况下，数据类型为数字、文本和日期。我正在使用 DLL Microsoft.Office.Interop.Excel。我不知道如何使用 C# 编程语言将这些格式设置为整个 excel 列的单元格。
采纳答案by Sid Holland
To set a range to text:
将范围设置为文本：
xlYourRange.NumberFormat = "@";
You can also prefix a value you put in a cell with an apostrophe for it to format it as text:
您还可以使用撇号为您放入单元格的值添加前缀，以便将其格式化为文本：
xlYourRange.Value = "'0123456";
To set a range to number
将范围设置为数字
xlYourRange.NumberFormat = "0";
Obviously if you want to set the format for the entire column then your range will be the column.
显然，如果您想为整个列设置格式，那么您的范围将是该列。
xlYourRange = xlWorksheet.get_Range("A1").EntireColumn;
EDIT:
编辑：
Dates are a bit more complicated and will also depend on your regional settings:
日期有点复杂，还取决于您的区域设置：
// Results in a Date field of "23/5/2011"
xlRange.NumberFormat = "DD/MM/YYYY";
xlRange.Value = "23/5/2011";
// Results in a Custom field of "23/5/2011"
xlRange.NumberFormat = "DD-MM-YYYY";
xlRange.Value = "23/5/2011";
// Results in a Custom field of "05/23/2011"
xlRange.NumberFormat = "MM/DD/YYYY";
xlRange.Value = "5/23/2011";
// Results in a Custom field of "05-23-2011"
xlRange.NumberFormat = "MM-DD-YYYY";
xlRange.Value = "5/23/2011";
// Results in a Date field of "23/05/2011"
xlRange.NumberFormat = "DD/MM/YYYY";
xlRange.Value = "5/23/2011";
// Results in a Custom field of "23-05-2011"
xlRange.NumberFormat = "DD-MM-YYYY";
xlRange.Value = "5/23/2011";
// Results in a Custom field of "23/5/2011"
xlRange.NumberFormat = "MM/DD/YYYY";
xlRange.Value = "23/5/2011";
// Results in a Custom field of "23/5/2011"
xlRange.NumberFormat = "MM-DD-YYYY";
xlRange.Value = "23/5/2011";
回答by jp2code
There is an answer to a question I asked on this some time back.
前段时间我问过的一个问题有一个答案。
Most notably, the data types this guy uses in his answer are:
最值得注意的是，这个人在他的回答中使用的数据类型是：
private struct ExcelDataTypes
{
public const string NUMBER = "NUMBER";
public const string DATETIME = "DATETIME";
public const string TEXT = "TEXT"; // also works with "STRING".
}
Look familiar?
看起来熟悉？
If you are interested, here is the link:
如果你有兴趣，这里是链接：
Insert DataTable into Excel Using Microsoft Access Database Engine via OleDb
回答by Jordan
Yes, with the date format everything is more complicated - even more complicated than Sid Hollandhas mentioned. The reason is in some localization problems. For example, if your Windows system has Russian localization, you should use Russian letters in the date formats, like "ДД.MM.ГГГГ" or "ГГГГ-MM-ДД", and, therefore, you should be able to extract and apply these letters. See more or less complete description and solution here: https://stackoverflow.com/a/35418176/2199512
是的，使用日期格式，一切都变得更加复杂——甚至比Sid Holland提到的还要复杂。原因在于一些本地化问题。例如，如果你的Windows系统有俄语本地化，你应该在日期格式中使用俄语字母，如“ДД.MM.ГГГГ”或“ГГГГ-MM-ДД”，因此，你应该能够提取和应用这些字母。在此处查看或多或少完整的描述和解决方案：https: //stackoverflow.com/a/35418176/2199512
