Stop Date Auto-Format when Exporting from DataGrid to Excel in C#

1
votes

I am currently formatting a Date for a specific Excel file Export from a DataSet/DataGrid.
The Date is formatted like so:

DateTime date = Convert.ToDateTime(entry.Date);
string formatdate = String.Format("{0:yyyy/MM/dd}", date);

Once creating the DataSet is said and done, I use the following code to Export the DataSet to an Excel file:

public static void ExportDStoExcel(DataSet ds, string filename)
    {
        HttpResponse response = HttpContext.Current.Response;
        response.Clear();
        response.Charset = "";

        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                DataGrid dg = new DataGrid();
                dg.DataSource = ds.Tables[0];
                dg.DataBind();
                dg.RenderControl(htw);
                response.Write(sw.ToString());
                response.End();
            }
        }

    }

My only problem is once I export this to Excel, Excel Auto-Formats the Dates like this: MM/DD/YYYY instead of YYYY/MM/DD.

I understand this could be achieved manually by opening in Excel, but the Export is being built into an Automated System and needs to be hard coded.

Is there any way of bypassing Excel's DateTime Auto-Formatting?

3
c#exceldatetime

3 Answers

1
votes

Right now you are just outputting HTML table, that Excel interprets how it likes. You'd have bring yourself down to Excel's level to be able to specify column's properties (set type to Text instead of General). This means that you need to generate actual xls file (there are various libraries out there for that). Or (if restriction to Office 2010 is acceptable) got with Open XML format which you can write with regular .NET API.

4
votes

I had the same issue and solved it by adding a non breaking space (&nbsp) in front of the text. Stopped Excel from auto-formatting. Not the cleanest solution but did the trick for me...

1
votes

You can style excel cells with mso-number-format

mso-number-format:"\\@"

\@ will tell excel to treat all data in text format only. So auto format won't happen.

Please update your code like this:

response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
response.Write("<head><style> td {mso-number-format:\\@;} </style></head><body>");

using (StringWriter sw = new StringWriter())
{
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
    {
        DataGrid dg = new DataGrid();
        dg.DataSource = ds.Tables[0];
        dg.DataBind();
        dg.RenderControl(htw);
        response.Write(sw.ToString());

        response.Write("</body></html>");
        response.End();
    }
}

OR

you can try with specific date format also. Refer: http://cosicimiento.blogspot.in/2008/11/styling-excel-cells-with-mso-number.html

mso-number-format:"yyyy\/mm\/dd"