export gridview to excel with custom value formatting

Question

i have in my content page 4 things:

a single gridview
query buttons that execute database views and display the results of the queries inside the view in the gridview
export to excel button that exports the gridview into excel
send email with the excel above as an attachment

they work fine, however i noticed a strange problem with the formatting of some of the cells within a column, the column has 2 formats applied to the values, "number" and "general", being "number" the incorrect one.

here are some pics of the first few results to illustrate what i'm saying:

in sql server

sql

in the content page

content page

in excel

excel

notice that in sql server and on the page, the cells are displayed with the correct formatting, which is XXXXX.etc (general formatting) but the cells with more numbers get formatted as "number"

i'll post some code bellow:

gridview

export to excel button

protected void Buttonexcel_Click(object sender, EventArgs e)
{

    try
    {
        Response.Clear();
        Response.Buffer = true;
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.Charset = "";
        Response.AddHeader("content-disposition", "attachment;filename=dados.xls");
        StringWriter sWriter = new StringWriter();
        HtmlTextWriter hWriter = new HtmlTextWriter(sWriter);
        GridView1.RenderControl(hWriter);
        Response.Output.Write(sWriter.ToString());
        Response.Flush();
        Response.End();
    }
    catch (Exception ex)
    {
        Label1.Text = ex.ToString();
    }

}

is there a way that i can force only "general" formatting for the entire xls?

export gridview to excel with custom value formatting

2 Answers