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

in the content page

in 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

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true"></asp: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?

Answer 1

This worked for me

        var grid = new GridView();
        grid.DataSource = candidates;
        grid.DataBind();
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=candidates.xls");
        Response.ContentType = "application/excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        grid.RenderControl(htw);
        string style = @"<style> td { mso-number-format:\@;} </style>";
        Response.Write(style);
        Response.Write(sw.ToString());
        Response.End();

Answer 2

@Poormina, thank you for your input, my code is now:

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);
        string style = @"<style> .textmode {mso-number-format:General} </style>";
        Response.Output.Write(sWriter.ToString());
        Response.Flush();
        Response.End();
    }
    catch (Exception ex)
    {
        Label1.Text = ex.ToString();
    }

}

however the excel formatting remains the same, some cells are formatted as "number" and others as "general", i also tried:

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

but the result is the same, another thing that i should point out is that not all records within the ITMREF_0 should be like XXXXX.YYYY.ZZZZZ, i'm gonna post a screenshot bellow:

the 12000073 is correct and excel should display it like that, 22284.01.01 should also be displayed like that in excel

i would appreciate some help

EDIT : i figured it out, i just needed to add:

string style = @"<style> TD { mso-number-format:\@; } </style>";
        Response.Write(style);

excel still gives me a warning saying that the number is formatted as text or that it has an apostrophe but that's ok

EDIT 2:

as i said in the first post i also have a button that sends an email with an excel file as attachment that has the gridview data in it, i want to do the same thing for this method, format the columns as "general" however i can't figure out how.

here's the send email button code:

protected void Buttonmail_Click(object sender, EventArgs e)
{
    fn_AttachGrid();
}

public void fn_AttachGrid()
{

    StringWriter sWriter = new StringWriter();
    HtmlTextWriter hWriter = new HtmlTextWriter(sWriter);
    GridView1.RenderControl(hWriter);
    MailMessage mail = new MailMessage();
    mail.IsBodyHtml = true;
    mail.To.Add(new MailAddress(txtto.Text));
    mail.Subject = "Foi";
    System.Text.Encoding Enc = System.Text.Encoding.ASCII;
    byte[] mBArray = Enc.GetBytes(sWriter.ToString());
    string style = @"<style> TD { mso-number-format:\@; } </style>";
    Response.Write(style);
    System.IO.MemoryStream mAtt = new System.IO.MemoryStream(mBArray, false);
    mail.Attachments.Add(new Attachment(mAtt, "rotina.xls"));
    mail.Body = "Foi detectado o seguinte problema";
    SmtpClient smtp = new SmtpClient();
    mail.From = new MailAddress("email_from", "name displayed");
    smtp.Host = "smtp.gmail.com";
    smtp.UseDefaultCredentials = true;
    System.Net.NetworkCredential NetworkCred = new System.Net.NetworkCredential();
    NetworkCred.UserName = "email_from";
    NetworkCred.Password = "password";
    smtp.Credentials = NetworkCred;
    smtp.EnableSsl = true;
    smtp.Port = 587;
    smtp.Send(mail);
    ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "anything", "alert('Enviado com sucesso.');", true);
}

could someone help me?