This project has moved. For the latest updates, please go here.

Export GridView from ASP.NET to Excel

Feb 21, 2012 at 7:57 PM

Hi everybody,

I'm trying to export a GridView from ASP.NET to Excel, here my code:

       //Create the workbook
        var workbook = new XLWorkbook();
        workbook.Worksheets.Add("Sample");
 
        gvUpLoad.AllowPaging = false;
 
        // Create Response
        HttpResponse response = Response;
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        Page pageToRender = new Page();
        HtmlForm form = new HtmlForm();
 
        // GridView to add
        form.Controls.Add(gvUpLoad);
        pageToRender.Controls.Add(form);
        response.Clear();
        response.Buffer = true;
 
        //Prepare the response
        response.Clear();
        response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        response.AddHeader("content-disposition""attachment;filename=HelloWorld.xlsx");
 
        response.Charset = "UTF-8";
        response.ContentEncoding = Encoding.Default;
        pageToRender.RenderControl(htw);
        response.Write(sw.ToString());
 
        //Flush the workbook to the Response.OutputStream
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            workbook.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(response.OutputStream);
            MyMemoryStream.Close();
        }
 
        response.End();

the problem seems to be this line: 'response.Write(sw.ToString())' because when I omitted
I can open the file with Excel but obviously without information and when I put it the
Excel shows me "Excel can not open the file HelloWorld.xlsx the format or the extension are not valid."
when I try to open the file.

Thank very much for any idea in advance.

Xabier.
Coordinator
Feb 21, 2012 at 8:03 PM

The only thing that you should write to the response is the Excel file, otherwise you'll get a corrupted file.

Feb 22, 2012 at 12:05 AM
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.
Apr 4, 2012 at 6:01 PM
Xabier wrote:
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.

Hello,

I'm also trying to export a GridView from ASP.NET to Excel but I'm using VB language, I want to put this in a button so I changed your code to this:

 

'Create workbook
        Dim workbook As New XLWorkbook

        'Create table
        Dim dt As New DataTable

        'Table Name
        dt.TableName = "Table"

        'No AllowPaging
        GridView1.AllowPaging = False

        'Add columns to DataTable
        Dim i As Integer
        If Not GridView1.HeaderRow Is Nothing Then
            For i = 0 To GridView1.HeaderRow.Cells.Count
                dt.Columns.Add(GridView1.HeaderRow.Cells(i).Text)
            Next
        End If

        'Add each data rows to table

        Dim row As GridViewRow
        For Each row In GridView1.Rows
            Dim dr As DataRow
            dr = dt.NewRow()
            For i = 0 To row.Cells.Count
                dr(i) = row.Cells(i).Text.Replace("&nbsp;", "")
            Next
            dt.Rows.Add(dr)
        Next

        'Add footer row to table

        If Not GridView1.FooterRow Is Nothing Then
            Dim dr As DataRow
            dr = dt.NewRow()

            For i = 0 To GridView1.FooterRow.Cells.Count
                dr(i) = GridView1.FooterRow.Cells(i).Text.Replace("&nbsp;", "")
            Next
            dt.Rows.Add(dr)
        End If

        'Add DataTable as a worksheet
        workbook.Worksheets.Add(dt)

        'Create Response
        Dim httpresponse = Response

        'Prepare Response
        httpresponse.Clear()
        httpresponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        httpresponse.AddHeader("content-disposition", "attachment;filename=" + Me.lblRes1.Text + ".xlsx")

        'Flush the workbook to the Response.OutputStream
        Using memoryStream As New MemoryStream()
            workbook.SaveAs(memoryStream)
            memoryStream.WriteTo(httpresponse.OutputStream)
            memoryStream.Close()
        End Using
        httpresponse.End()

 

 

But I'm having a problem in this line: in this line: 'dt.Columns.Add(GridView1.HeaderRow.Cells(i).Text)'shows me an error:

"System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values. Parameter name: index ".

What does it mean?

I appreciate your response.

Coordinator
Apr 4, 2012 at 6:12 PM

That's a zero based collection so you have to iterate to .Count - 1

btw, that error has nothing to do with ClosedXML.