CODEWITHSUNDEEP
c#exceldatetimedatatable
Rahul Gokani
Rahul Gokani

Reputation: 1708

Export DataTable to Excel without using Interop Date Fromat change when open the excel file

I have exported DataTable to excel file. But when I open the excel file the DateTime format is changed.
When I export the data DateTime field contains "7/21/2016 12:00:00 AM" value. And when I open the excel file DateTime Field shows "7/21/2016 0:00" value.
But when I checked in excel file formula bar DateTime value shows correct "7/21/2016 12:00:00 AM".

enter image description here

enter image description here

Is there anything that I can do to fix this?

I have used below code to export DataTable to excel:

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + curEventName + ".xls");

HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
////sets font
HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
  "borderColor='#000000' cellSpacing='0' cellPadding='0' " +
  "style='font-size:10.0pt; font-family:\"Times New Roman\"; background:white;'> <TR>");
//am getting my grid's column headers
int columnscount = dt_exportResponse.Columns.Count;

for (int j = 0; j < columnscount; j++)
{      //write in new column
    HttpContext.Current.Response.Write("<Td>");
    //Get column headers  and make it as bold in excel columns
    HttpContext.Current.Response.Write("<B>");
    HttpContext.Current.Response.Write(dt_exportResponse.Columns[j].ColumnName);
    HttpContext.Current.Response.Write("</B>");
    HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in dt_exportResponse.Rows)
{//write in new row
    HttpContext.Current.Response.Write("<TR>");
    for (int i = 0; i < dt_exportResponse.Columns.Count; i++)
    {
        HttpContext.Current.Response.Write("<Td>");
        HttpContext.Current.Response.Write(row[i].ToString());
        HttpContext.Current.Response.Write("</Td>");
    }

    HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();

Upvotes: 0

Views: 677

Answers (1)

Bruce David Wilner
Bruce David Wilner

Reputation: 467

This is not a bug. This is just an issue of how you are instructing Excel to display given cells of type DATE. You can override that for any cell(s) by modifying Format->Cells:Number, or you can use the variety of built-in date processing functions to cobble together whatever ad hoc date/time format you may wish, like 22 Sat October '16 if that suits you.

Upvotes: 1

Related Questions