ClosedXML : Export GridView from ASP.NET to Excel

Export GridView from ASP.NET to Excel

http://closedxml.codeplex.com/discussions/337700

Hello,

I resolved this topic converting the GridView to a DataTable and then adding it as a WorkSheet.

        // Create the workbook
        var workbook = new XLWorkbook();
 
        // Create the table
        DataTable dt = new DataTable();
 
        // Important because the sheet must have a name
        dt.TableName = "Information";
 
        // Not allowing Paging to avoid potential problems with GridView
        // Replaces gvUpLoad with your own GridView
        gvUpLoad.AllowPaging = false;
 
        // Loop the GridView to copy it as DataTable
        // add the columns to the datatable            
        if (gvUpLoad.HeaderRow != null)
        {
            for (int i = 0; i < gvUpLoad.HeaderRow.Cells.Count; i++)
            {
                dt.Columns.Add(gvUpLoad.HeaderRow.Cells[i].Text);
            }
        }
 
        //  add each of the data rows to the table
        foreach (GridViewRow row in gvUpLoad.Rows)
        {
            DataRow dr;
            dr = dt.NewRow();
 
            for (int i = 0; i < row.Cells.Count; i++)
            {
                dr[i] = row.Cells[i].Text.Replace("&nbsp;", "");
            }
            dt.Rows.Add(dr);
        }
 
        //  add the footer row to the table
        if (gvUpLoad.FooterRow != null)
        {
            DataRow dr;
            dr = dt.NewRow();
 
            for (int i = 0; i < gvUpLoad.FooterRow.Cells.Count; i++)
            {
                dr[i] = gvUpLoad.FooterRow.Cells[i].Text.Replace("&nbsp;", "");
            }
            dt.Rows.Add(dr);
        }
 
        // Add a DataTable as a worksheet
        workbook.Worksheets.Add(dt);
 
        // Create Response
        HttpResponse response = Response;
        
        //Prepare the response
        response.Clear();
        response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        response.AddHeader("content-disposition", "attachment;filename=HelloWorld.xlsx");
 
        //Flush the workbook to the Response.OutputStream
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            workbook.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(response.OutputStream);
            MyMemoryStream.Close();
        }
 
        response.End(); 

I hope this can help anyone else.

Xabier.