ExcelPackage Excel Export and DateTime Format
April 13, 2021
If you want to export to Microsoft Office Excel from within your .Net code, you can use this method.
string path = String.Format(@"{0}\{1}.xlsx", Helper.GetValue("DocumentPath"), Guid.NewGuid().ToString("N"));
using (ExcelPackage package = new ExcelPackage())
{
foreach (DataTable table in ds.Tables)
{
if (table.Rows.Count > 0)
{
string sheetName = table.TableName;
var sheet = pck.Workbook.Worksheets.Add(sheetName);
sheet.Cells.LoadFromDataTable(table, true);
sheet.Cells.AutoFitColumns();
sheet.Row(1).Style.Font.Bold = true;
}
}
using (FileStream fs = new FileStream(path, FileMode.CreateNew, FileAccess.Write))
{
package.SaveAs(fs);
}
}
If the date formats are not translated properly during this process, you can check the types of all columns in the datatable with a code block as below and make them appear in the format you want.
int colNumber = 1;
foreach (DataColumn col in table.Columns)
{
if (col.DataType == typeof(DateTime))
{
sheet.Column(colNumber).Style.Numberformat.Format = "dd-MM-yyyy HH:mm";
}
colNumber++;
}