Exporting to excel loses the date format

Question

I am exporting the contents of SP to excel. One of the columns brings the date format as 08/2015 but when exporting to excel, the format gets changed to Aug-2015.

I did a google on the same and found that including the below code does the trick;

string style = @"<style> .text { mso-number-format:\@; } </style> ";

The exporting to excel (dataset to excel) works below;

 /// <summary>
    /// This method can be used for exporting data to excel from dataset
    /// </summary>
    /// <param name="dgrExport">System.Data.DataSet</param>
    /// <param name="response">System.Web.Httpresponse</param>
    public static void DataSetToExcel(System.Data.DataSet dtExport, System.Web.HttpResponse response, string strFileName)
    {

        string style = @"<style> .text { mso-number-format:\@; } </style> ";

        //Clean up the response Object
        response.Clear();
        response.Charset = "";

        //Set the respomse MIME type to excel
        response.ContentType = "application/vnd.ms-excel";

        //Opens the attachment in new window
        response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName.ToString() + ".xls;");
        response.ContentEncoding = Encoding.Unicode;
        response.BinaryWrite(Encoding.Unicode.GetPreamble());

        //Create a string writer
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();

        //Create an htmltextwriter which uses the stringwriter
        System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);

        //Instantiate the datagrid

        System.Web.UI.WebControls.GridView dgrExport = new System.Web.UI.WebControls.GridView();

        //Set input datagrid to dataset table
        dgrExport.DataSource = dtExport.Tables[0];

        //bind the data with datagrid
        dgrExport.DataBind();

        //Make header text bold
        dgrExport.HeaderStyle.Font.Bold = true;

        //bind the modified datagrid
        dgrExport.DataBind();

        //Tell the datagrid to render itself to our htmltextwriter
        dgrExport.RenderControl(htmlWrite);

        response.Write(style);

        //Output the HTML
        response.Write(stringWrite.ToString());

        response.End();
    }

Where am i making a mistake? please guide!

Thanks!

Solution 1

I don't really understand a fair bit of the code (not fluent in asp.net) but I will say that if you want to force text in an excel sheet you need to define the target area as text before putting your data in there.

If my understanding of the code is correct this:

response.Write(style);

Needs to be before this.

dgrExport.RenderControl(htmlWrite);

Edit: Perhaps an alternate solution

The bit of google code you have found sets the format of the cells as text. In all likelyhood you want excel to treat the date as a date which has a display format of MM/YYYY.

maybe try replacing this:

string style = @"<style> .text { mso-number-format:\@; } </style> "

with

string style = @"<style> .text { mso-number-format:\mm/yyyy; } </style> "

I am not sure if / or \ is an escape character in ASP.net so the exact snytax might be different. In excel terms number format @ means text and mm/yyyy will mean a date with the display format that you want.

Solution 2

Problem is not with the Date format, Excel converts the data as per the DataType (Default is GENERAL) of CELL. To prevent the data conversion you have to provide the data type (TEXT) along with the data.

you have used the correct code, but style sheet .text is not applied on your data. Apply the style sheet on ALL the <TD> tags. It will 100% work and will retain your data as is you will provide (Date- 08/2015, 0001 or any data).

string style = @"<style> TD { mso-number-format:\@; } </style> ";

Solution 3

Here is some sample code.

Response.AddHeader("content-disposition", "attachment; filename=Report.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
DataGrid g = new DataGrid();
DataTable d = new System.Data.DataTable();
d = (DataTable)Session["ReportData"];        
g.DataSource = d;
g.DataBind();       
foreach (DataGridItem i in g.Items)
{
    foreach (TableCell tc in i.Cells)
        tc.Attributes.Add("class", "text");
}
g.RenderControl(htmlWrite);
string style = @"<style> .text { mso-number-format:\@; } </style> ";
Response.Write(style);
Response.Write(stringWrite.ToString());
Response.End();

Comments

[Pynner]

i've added something else to try

[Arun Singh]

Smith, Addition of ' is will change your original data. Have you tried the above code?

[hks]

Yes.... string style = @"<style> TD { mso-number-format:\@; } </style> "; Working Perfectly Fine