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.

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

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.

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(" ", "")
            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(" ", "")
            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.

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

btw, that error has nothing to do with ClosedXML.