Export To Excel - XLWorkbook - Date and Time NumberFormat

Question

Dear Team,

 My Requirement is -

1.  I have to export Datatable to .xlsx file. 

2. For all Date Columns in Datatable, the excel cell should be of "Date" Format instead of Text or General.

This is because, the user can filter data according to date as required.

3. For all Time Columns in Datatable, the excel cell should be of "Time" Format instead of Text or General.  

 

I have tried using the following, but I am not proper result.

 

Please guide. 

 

My method -

---------------

  private void ExportToXLSX(DataTable table, string s)
{
try
{
DataTable dtdatetimenew = new DataTable();
string currdate = DateTime.Now.ToString("dd-MMM-yyyy");
string fname = currdate + "_" + s;

if (table != null)
{

using (XLWorkbook wb = new XLWorkbook())
{
DataColumnCollection columns = table.Columns;

foreach (DataColumn dc in table.Columns)
{
if (dc.ColumnName.Contains("Date"))
{

wb.Style.NumberFormat.Format = "mm/dd/yyyy";
}
if (dc.ColumnName.Contains("Time"))
{
wb.Style.NumberFormat.Format = "hh:mm:ss";

}
}

wb.Worksheets.Add(table, "DumpAndCount");

Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

Response.AddHeader("content-disposition", "attachment;filename='" + fname + "'.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}

}
catch (Exception err) { err.Message.ToString(); }
}