empty excel file when exporting to Gridview

Question

hope you can help me. I am developing a web page that produces a GridView in aspx which works and a button that when is clicked exports the gridview to excel file. This second case doesn't work for me, the file is empty. Here it is the code:

protected void Button2_Click(object sender, EventArgs e) //click en botón Exportar- -Click to export
{
   try
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "CUSS - Global Stats.xls"));
        Response.ContentType = "application/ms-excel";
        Response.ContentEncoding = System.Text.Encoding.Unicode;  
        System.IO.StringWriter writer = new StringWriter();
        System.Web.UI.HtmlTextWriter html = new System.Web.UI.HtmlTextWriter(writer);
        GridView1.AllowPaging = false;
        GridView1.RenderControl(html); //gpp prints outs
        Response.Write(writer.ToString());
        Response.Flush();
        Response.End();
    }
    catch (Exception)
    {
    }

Gridview is properly produce in asp as shown in pic.GridViewResult

 protected void Button1_Click(object sender, EventArgs e)
    {

            ...
            GridView1.DataSource = Prints.ToList();
            GridView1.DataBind();
            ...

    }

Can you pls help me?

That's not an Excel file, that's an HTML table with a fake content type. Use a library like EPPlus to create real `xlsx` files. — Panagiotis Kanavos, Sep 25 '18 at 14:51
Start using a specialized library for creating Excel files, like [EPPlus](https://github.com/JanKallman/EPPlus) for example. All you are doing now is creating a HTML page with an .xls extension. — VDWWD, Sep 25 '18 at 14:51
And handle the error. Your catch block is empty so if there is a problem you wouldn't know it. — VDWWD, Sep 25 '18 at 14:52
Where did you find the code you posted in your question? I'm curious, I want to track down whoever is recommending to the world to export HTML and give it an XLS extension. A good example of [Cargo Cult Programming](https://exceptionnotfound.net/cargo-cult-programming-the-daily-software-anti-pattern/). — mason, Sep 25 '18 at 15:26
@mason, I ask myself that same question. Where do the people find that method of exporting data like that...This question type comes every few days... — VDWWD, Sep 25 '18 at 15:53
@VDWWD There can't be that many people out there saying to do it that way. There's gotta be some source. If we could track it down and get them to stop espousing their bad ideas, that might save us from having to repeat ourselves all the time. — mason, Sep 25 '18 at 15:59
@mason, are you asking from source, here you go: https://stackoverflow.com/questions/17047296/export-grid-view-to-excel — trilero, Sep 27 '18 at 10:02
@trilero Thank you. The top two answers there are not a good idea to use. Take a look at [my blog](http://masonmcg.com/Blog/entry/properly-generating-excel-files-in-net) for a better way of doing things. — mason, Sep 27 '18 at 12:38
@VDWWD You see that? Wonder what the best way to clean that up is. For now I've downvoted. I think I'll comment and ask them to remove their answers, but we'll need to convince them their way of doing things is a terrible idea. It'd probably be helpful if we create a canonical Q&A so we can close questions like this as a duplicate in the future. Might be useful to get the community's input on Meta. I'm tired of seeing people suggesting this dirty hack, it really does the entire world a disservice. — mason, Sep 27 '18 at 14:27
@mason, asking this on Meta is a good idea. Shall I post a question or you? — VDWWD, Sep 28 '18 at 11:07

score 0 · Answer 1 · answered Sep 25 '18 at 15:41

There should be a better way to do it, but I 'make' my gridview again: using ClosedXML.Excel; Click event:

public void button1_Click(object sender, EventArgs e)
{
    using(XLWorkbook libro_trabajo = new XLWorkbook())
    {
        DataSet ps = datos_referencias();

        libro_trabajo.Worksheets.Add(ps);
        libro_trabajo.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        libro_trabajo.Style.Font.Bold = true;
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=reporte_clientes.xlsx");

        using (MemoryStream memoria = new MemoryStream())
        {

            libro_trabajo.SaveAs(memoria);
            memoria.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();

        }
    }
}

Getting my data set:

public DataSet datos_referencias()
{

    DataSet ds = new DataSet();
    DataTable tabla_detallado = new DataTable("Clientes");
    tabla_detallado.Columns.Add(new DataColumn("Cliente", typeof(string)));
    objeto = new Conexion();
    try
    {
        objeto.abrir_conexion_mysql();
        objeto.cadena_comando_mysql = "SELECT * from ...ETC ";
        objeto.aplicar_comando_mysql_extraccion();
        while (objeto.leer_comando.Read())
        {
            DataRow fila = tabla_detallado.NewRow();
            fila["Cliente"] = (objeto.leer_comando.GetString(1)).ToUpper();                
            tabla_detallado.Rows.Add(fila);
        }

    }
    finally {objeto.cerrar_conexion_mysql_extraccion(); }
    ds.Tables.Add(tabla_detallado);
    return ds;
}

This doesn't "make a GridView" again. It just retrieves the underlying data and uses it to generate the file - which is exactly how it should be done. — mason, Sep 25 '18 at 15:43
Thanks mason, I'm new and didn't know how to explain it properly — kuroyakedo, Sep 25 '18 at 15:57

score 0 · Answer 2 · answered Feb 19 '19 at 07:42

0

Maybe because of make response GZip ?

On filter ?

response.Filter = New GZipStream(response.Filter, CompressionMode.Compress)

answered Feb 19 '19 at 07:42

Zvi Redler

1,708
1
18
29

empty excel file when exporting to Gridview

2 Answers2