Gridview export for Excel with custom value formatting

Question

I have content 4 on my page:

• one gridview
• query buttons that execute database views and display query results inside a view in gridview
• export to excel button which exports gridview to excel
• send an email using excel above as an attachment

they work fine, however I noticed a strange problem with formatting some cells inside the column, the column has two formats applied to the values, "number" and "total", being the "number" of the wrong one.

Here are some photos of the first few results to illustrate what I am saying:

in sql server

on the content page

in excel

, sql- , XXXXX.etc( ), "",

:

GridView

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true"></asp:GridView>

excel

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();
    }

}

, "" xls?

Answer 1

@Poormina, , :

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();
    }

}

excel , "", "", :

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

, , , , ITMREF_0 XXXXX.YYYY.ZZZZZ, :

12000073 , excel , 22284.01.01 , excel

EDIT: , :

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

excel , , ,

2:

, , excel , gridview, , "" , .

:

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);
}

- ?

Answer 2

        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 3

You can use this. it worked for me ..

foreach (GridViewRow row in dummyGridView.Rows)
                {
                    foreach (TableCell item in row.Cells)
                    {
                        item.Attributes.Add("class", "textmode");
                    }
                }