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

<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?

You could try adding a style using Response.Write to convert numbers to string. Look at this link: aspsnippets.com/Articles/… — Poornima
– Poornima, Commented Dec 6, 2013 at 17:41

skunk · Accepted Answer · 2016-01-27 11:09:46Z

4

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

answered Jan 27, 2016 at 11:09

skunk

831 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2983177 · Accepted Answer · 2013-12-09 14:35:36Z

@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:

example

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?

i figured it out, i just needed to do this string style = @"<style> TD { mso-number-format:\@; } </style>"; byte[] mBArray = Enc.GetBytes(style + sWriter.ToString());
in export to excel i am not getting format or color as i am using in gridiview.

Collectives™ on Stack Overflow

export gridview to excel with custom value formatting

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related