Support Center

Export GridView and/or DataGrid control to Excel file

Currently GemBox.Spreadsheet lacks an API support for direct exporting GridView and/or DataGrid controls to an Excel file.

However, we do have an API for importing and exporting a DataTable to an Excel file which can be used to export GridView's or DataGrid's DataSource property. The following link is a demonstration sample of InsertDataTable method usage:
http://www.gemboxsoftware.com/SampleExplorer/Spreadsheet/Import_ExportDataTable/InsertDataTable

With the above approach we lose style and formatting that was used on the control, so as an alternative what we can do is use the control's HTML representation and convert it to an Excel file.

For example, here is a sample "Default.aspx" which contains GridView and Button controls:

Now, in order to export the GridView content to an excel file on the "Export GridView" button's click event, we can do the following:

C# code

public partial class _Default : Page
{
    protected void btnGridViewExport_Click(object sender, EventArgs e)
    {
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        GridView1.RenderControl(htw);

        ExcelFile excel;
        var options = LoadOptions.HtmlDefault;
        using (var htmlStream = new MemoryStream(options.Encoding.GetBytes(sw.ToString())))
            excel = ExcelFile.Load(htmlStream, options);

        excel.Save(this.Response, "GridView.xlsx");
    }

    // Note, it is important to override the form verification in order to successfully call a GridView1.RenderControl method.
    public override void VerifyRenderingInServerForm(Control control)
    {

    }
}

VB.NET code

Public Class _Default
    Inherits Page

    Protected Sub btnGridViewExport_Click(sender As Object, e As EventArgsHandles btnGridViewExport.Click
        Dim sw As New StringWriter()
        Dim htw As New HtmlTextWriter(sw)
        GridView1.RenderControl(htw)

        Dim excel As ExcelFile
        Dim options = LoadOptions.HtmlDefault
        Using htmlStream = New MemoryStream(options.Encoding.GetBytes(sw.ToString()))
            excel = ExcelFile.Load(htmlStream, options)
        End Using

        excel.Save(Me.Response, "GridView.xlsx")
    End Sub

    ' Note, it is important to override the form verification in order to successfully call a GridView1.RenderControl method.
    Public Overrides Sub VerifyRenderingInServerForm(control As Control)

    End Sub

End Class

And here is the resulting "GridView.xlsx" file:

7 of 12 people found this page helpful
Subscribe to this article to get an email notification when it is updated.

0 Comments

  • There are no comments.