ExcelPackage Excel Export and DateTime Format

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++;
   }

Add a Comment

Your email address will not be published. Required fields are marked *