EPPlus日期单元格数据类型不工作 [英] EPPlus Date Cell Datatype Not Working
问题描述
我必须接受一个IEnumerable并从中产生一个Excel文档一些代码。在IEnumerable的对象有日期字段，我想那些被格式化为在Excel中的日期。然而，当你在Excel看它，日期不似乎是日期数据类型的，直到你双击单元格中，然后按Enter。当你这样做，值移动到右对齐，而表中的过滤器正常工作。如果你不这样做的双击，输入的东西，值被左对齐和表过滤器把它当作文本，而不是一个日期。我需要的Excel把它当作一个日期开箱。
I have some code that accepts an IEnumerable and generates an Excel document from it. The objects in the IEnumerable have a date field, and I want those to be formatted as dates in Excel. However, when you look at it in Excel, the dates don't seem to be of the "date" data type until you double click in the cell, then press enter. When you do that, the values moves to right justified, and table filters work correctly. If you don't do the double click and enter thing, the value is left justified and table filters treat it as text instead of a date. I need Excel to treat it as a date out of the box.
下面是我的代码。
/// <summary>
/// Converts any IEnumerable to an Excel Document. Objects appear in the order declared in the class.
/// </summary>
/// <typeparam name="T">Any object.</typeparam>
/// <param name="objects">List of objects to generate document from.</param>
/// <returns>Byte array representing a .xlsx file.</returns>
public static byte[] ToExcelDocument<T>(this IEnumerable<T> objects)
{
int currentrow = 1;
Type type = typeof(T);
List<PropertyInfo> propertyinfos = type.GetProperties().ToList();
int numcolumns = propertyinfos.Count;
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(type.Name + "(s)");
for (int i = 0; i < numcolumns; i++)
{
ws.Cells[currentrow, i + 1].Value = propertyinfos[i].Name;
}
currentrow++;
foreach (object o in objects)
{
for (int i = 0; i < propertyinfos.Count; i++)
{
if (o.GetType().GetProperty(propertyinfos[i].Name).PropertyType == typeof(DateTime))
{
ws.Cells[currentrow, i + 1].Style.Numberformat.Format = "m/d/yyyy";
DateTime dt = (DateTime)(o.GetType().GetProperty(propertyinfos[i].Name).GetValue(o, null));
}
else if (o.GetType().GetProperty(propertyinfos[i].Name).PropertyType == typeof(DateTime?))
{
DateTime? dt = (DateTime?)(o.GetType().GetProperty(propertyinfos[i].Name).GetValue(o, null));
if (dt.HasValue)
{
ws.Cells[currentrow, i + 1].Style.Numberformat.Format = "m/d/yyyy";
ws.Cells[currentrow, i + 1].Value = dt.Value.ToString("MM/dd/yyyy");
}
}
else
{
ws.Cells[currentrow, i + 1].Value = o.GetType().GetProperty(propertyinfos[i].Name).GetValue(o, null);
}
}
currentrow++;
}
ExcelAddressBase eab = new ExcelAddressBase(1, 1, currentrow - 1, numcolumns);
ws.Tables.Add(eab, "MyTable");
ws.Tables["MyTable"].TableStyle = OfficeOpenXml.Table.TableStyles.Medium15;
ws.Cells.AutoFitColumns();
MemoryStream ms = new MemoryStream();
pck.SaveAs(ms);
return ms.ToArray();
}
我认为 ws.Cells [currentrow，我+ 1] .Style.Numberformat.Format =M / D / YYYY; 将足以达到我想要的，但它似乎并不奏效。当你右击在Excel单元格，然后转到单元格格式，充分显示了日期选择，但它似乎并没有为表筛选工作。而不是排序最旧到最新的我知道，从一种到z。
I would think that ws.Cells[currentrow, i + 1].Style.Numberformat.Format = "m/d/yyyy"; would be enough to accomplish what I want, but it doesn't appear to be working. When you right click the cell in Excel and go to Format Cells, it shows that date is selected, yet it doesn't seem to work for table filters. Instead of "sort oldest to newest" I get "sort from a to z".
推荐答案
不要使用toString（）方法，
Dont use a ToString(),
ws.Cells[currentrow, i + 1].Value = dt.Value; // dont do this -> .ToString("MM/dd/yyyy");
它不能确定DATAFORMAT什么话。
我曾与十进制数一个类似的问题，我是想预格式化他们。
It cannot determine what the DataFormat is then. I had a similiar issue with decimal numbers, i was trying to "preformat" them.
在还你的第一个'如果'，在那里你检查值是一个日期，不应该返回值被放入Excel工作表，而不是到一个名为DT新的变量？
Also in your first 'if', where you check if the value is a date, shouldnt the returned value be put into the excel sheet, instead of into a new variable called dt?
这篇关于EPPlus日期单元格数据类型不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
