vba 在 C# 中使用 EPPLUS 设置列数据格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42104198/
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 Column Data Format using EPPLUS in C#
提问by Chris K.
Problem
问题
When I export a Microsoft Excel spreadsheetusing the EPPLUSlibrary I need to set both the column's data format as well as the cell format such that the resultant dates in the Microsoft Excel spreadsheet can be recognized by Microsoft Excel's formulasthat deal with date and time.
当我Microsoft Excel spreadsheet使用EPPLUS库导出 a 时，我需要设置列的数据格式和单元格格式，以便 Microsoft Excel 电子表格中的结果日期可以通过Microsoft Excel's formulas处理日期和时间来识别。
What was tried
尝试了什么
I can set the cell's format easily using:
我可以使用以下方法轻松设置单元格的格式：
currentWorkSheet.Cells["L" + currentRowNumber.ToString()].Style.Numberformat.Format = "m/d/yyyy";
currentWorkSheet.Cells["L" + currentRowNumber.ToString()].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
The problem is that even with this number formatting and styling the date in the cell is still not recognized as a date, it is only formattedas a date.
问题是，即使使用此数字格式和样式，单元格中的日期仍然无法识别为日期，它仅被格式化为日期。
Upon research I found that I need to set the column to have a "Date" data as shown in this image: 
经过研究，我发现我需要将列设置为具有“日期”数据，如下图所示：
I tried setting the column's data type as shown but this doesn't work:
我尝试设置列的数据类型，如图所示，但这不起作用：
currentWorkSheet.Column(10).Style.Numberformat.Format = "Date";
What can I do to get the column's data type to be a date?
我该怎么做才能使列的数据类型成为日期？
回答by tretom
I tried it and I found that, if I enter the date format exactly as it's set in my system, then Excel takes it as date value type, otherwise takes it as user defined. My system date format is "dd.MM.yyyy" and as a result of this code in the Excel file cell "A3" has date format, cell "A2" had user defined. You might check this out.
我试过了，我发现，如果我输入的日期格式与系统中设置的完全一样，那么 Excel 会将其作为日期值类型，否则将其作为用户定义的类型。我的系统日期格式是“dd.MM.yyyy”，由于 Excel 文件单元格“A3”中的此代码具有日期格式，单元格“A2”由用户定义。你可以看看这个。
My testcase was:
我的测试用例是：
using (ExcelPackage testFile = new ExcelPackage(new System.IO.FileInfo(@"c:\Data\test.xlsx")))
{
ExcelWorksheet testSht = testFile.Workbook.Worksheets[1];
testSht.Cells[1, 1].Value = new DateTime(2017, 1, 1);
testSht.Cells[2, 1].Style.Numberformat.Format = "dd-mm-yyyy";
testSht.Cells[2, 1].Formula = "=Date(" + DateTime.Now.Year + "," + DateTime.Now.Month + "," + DateTime.Now.Day + ")";
testSht.Cells[3, 1].Value = new DateTime(2017, 1, 1);
testSht.Cells[3, 1].Style.Numberformat.Format = "dd.MM.yyyy";
testFile.Save();
}
