使用C#导出到Excel时,Excel将日期列视为常规列 [英] Excel treating date column as general when exporting to Excel using C#
问题描述
我正在使用EPPlus库在Excel中导出报告。其中一列是日期类型,但是在导出到Excel之后,当我打开该文件时,除非我右键单击该列并将其类型设置为日期,否则它将将该列视为常规。
I am using EPPlus library to export a report in Excel. One of the columns is of date type but after exporting to Excel, when I open that file, it treats that column as "General" unless I right click on it and set its type to "Date".
以下是我的代码:
private void ExportToExcel(DataTable dataTable)
{
//Althought the column type is already date in dataTable but even if I use following line Excel doesn't treat it as Date
dataTable.Columns["Created On"].DataType = typeof(DateTime);
foreach (DataRow row in dataTable.Rows)
{
row["Created On"] = Convert.ToDateTime(row["Created On"].
ToString()).ToString("yyyy-MM-dd");
}
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("MyReport");
var totalCols = dataTable.Columns.Count;
var totalRows = dataTable.Rows.Count;
string fileName = "MyReport[" + DateTime.Now.ToString("yyyy-MM-dd") + "].xlsx";
for (var col = 1; col <= totalCols; col++)
{
workSheet.Cells[1, col].Value = dataTable.Columns[col - 1].ColumnName;
workSheet.Cells[1, col].Style.Font.Bold = true;
}
for (var row = 1; row <= totalRows; row++)
{
for (var col = 0; col < totalCols; col++)
{
workSheet.Cells[row + 1, col + 1].Value = dataTable.Rows[row - 1][col];
}
}
workSheet.Cells.Style.Font.Size = 11;
workSheet.Cells.AutoFitColumns();
//Rest of the code
}
以下是该列在Excel中的外观的屏幕截图。
Following is a screenshot as to how this column looks like in Excel.
如何确保Excel将我的日期列视为Date?
How can I make sure Excel treat my date column as Date?
推荐答案
将文本设置为
To Set text as DateTime
format in epplus you need to set the format type for a cells
EG:
1)对于简单的短日期模式
1) For simple short date Pattern
workSheet.Cells[row, 3].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
2)如果您想要日期时间,时间等特定格式,则
2) If you want specific format like datetime with time,hour etc then
workSheet.Column(dateColIndex).Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss AM/PM";
这篇关于使用C#导出到Excel时,Excel将日期列视为常规列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!