Home > GemBox.Spreadsheet

Export Excel files from ASP.NET GridView control

When using the GemBox.Spreadsheet library in an ASP.NET application, a common task that you may need is to export an Excel file to the client's browser. For this, you can use the ExcelFile.Save method from C# or VB.NET code that directly streams or downloads the spreadsheet to the user.

The following example shows how you can create an Excel file in an ASP.NET Web Forms application by exporting GridView control. You can export just data (using the grid's DataTable source) or both data and formatting (using the grid's HTML representation).

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="AspNetGridView._Default" EnableEventValidation="false" %>

<!DOCTYPE html>
<html lang="en-US">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Export GridView to Excel in ASP.NET</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server"
            BackColor="#DEBA84" CellPadding="3" CellSpacing="2"
            BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
            AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" 
            OnRowDeleting="GridView1_RowDeleting"
            OnRowUpdating="GridView1_RowUpdating"
            OnRowEditing="GridView1_RowEditing"
            OnRowCancelingEdit="GridView1_RowCancelingEdit" >
            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
            <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#D54A06" Font-Bold="True" ForeColor="White"/>
        </asp:GridView>
        <br>
        <asp:RadioButtonList ID="RadioButtonList1" runat="server">
            <asp:ListItem Value="xls">XLS</asp:ListItem>
            <asp:ListItem Value="xlsx" Selected="True">XLSX</asp:ListItem>
            <asp:ListItem Value="ods">ODS</asp:ListItem>
            <asp:ListItem Value="csv">CSV</asp:ListItem>
            <asp:ListItem Value="html">HTML</asp:ListItem>
            <asp:ListItem Value="mht">MHTML</asp:ListItem>
            <asp:ListItem Value="pdf">PDF</asp:ListItem>
        </asp:RadioButtonList>
        <br>
        <asp:Button ID="ExportData" runat="server" style="font-size:24px;padding:4px;margin-bottom:8px;"
            Text="Export GridView data"
            OnClick="ExportData_Click" />
        <br>
        <asp:Button ID="ExportDataAndFormatting" runat="server" style="font-size:24px;padding:4px;margin-bottom:8px;"
            Text="Export GridView data and formatting"
            OnClick="ExportDataAndFormatting_Click" />
    </form>
</body>
</html>
using GemBox.Spreadsheet;
using System;
using System.Data;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace AspNetGridView
{
    public partial class _Default : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

            if (!Page.IsPostBack)
            {
                var people = new DataTable();
                people.Columns.Add("ID", typeof(int));
                people.Columns.Add("FirstName", typeof(string));
                people.Columns.Add("LastName", typeof(string));

                Session["people"] = people;

                this.LoadDataFromFile(Request.PhysicalApplicationPath + "InputData.xlsx");
                this.SetDataBinding();
            }
        }

        /// <summary>
        /// Export GridView data to Excel file.
        /// </summary>
        protected void ExportData_Click(object sender, EventArgs e)
        {
            var people = (DataTable)Session["people"];

            // Create Excel file.
            var workbook = new ExcelFile();
            var worksheet = workbook.Worksheets.Add("DataSheet");

            // Export DataTable that's used as GridView data source into Excel sheet.
            worksheet.InsertDataTable(people, new InsertDataTableOptions("A1") { ColumnHeaders = true });

            // Stream Excel file to client's browser.
            workbook.Save(this.Response, "Report." + this.RadioButtonList1.SelectedValue);
        }

        /// <summary>
        /// Export GridView data and formatting to Excel file.
        /// </summary>
        protected void ExportDataAndFormatting_Click(object sender, EventArgs e)
        {
            var stringWriter = new StringWriter();
            var htmlWriter = new HtmlTextWriter(stringWriter);

            // Export GridView control as HTML content.
            this.GridView1.RenderControl(htmlWriter);

            var htmlOptions = LoadOptions.HtmlDefault;
            var htmlData = htmlOptions.Encoding.GetBytes(stringWriter.ToString());

            using (var htmlStream = new MemoryStream(htmlData))
            {
                // Load HTML into Excel file.
                var workbook = ExcelFile.Load(htmlStream, htmlOptions);

                // Rename Excel sheet.
                var worksheet = workbook.Worksheets[0];
                worksheet.Name = "StyledDataSheet";

                // Delete Excel column that has Delete and Edit buttons.
                worksheet.Columns.Remove(0);

                // Stream Excel file to client's browser.
                workbook.Save(this.Response, "Styled Report." + this.RadioButtonList1.SelectedValue);
            }
        }

        // Override verification to successfully call GridView1.RenderControl method.
        public override void VerifyRenderingInServerForm(Control control)
        { }

        private void LoadDataFromFile(string fileName)
        {
            var people = (DataTable)Session["people"];

            // Load Excel file.
            var workbook = ExcelFile.Load(fileName);
            var worksheet = workbook.Worksheets[0];

            // Import Excel data into DataTable that's used as GridView data source.
            worksheet.ExtractToDataTable(people, new ExtractToDataTableOptions("A1", worksheet.Rows.Count));
        }

        private void SetDataBinding()
        {
            var people = (DataTable)Session["people"];
            var peopleDataView = people.DefaultView;
            peopleDataView.AllowDelete = true;

            this.GridView1.DataSource = peopleDataView;
            this.GridView1.DataBind();
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            var people = (DataTable)Session["people"];
            people.Rows[e.RowIndex].Delete();
            this.SetDataBinding();
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            var people = (DataTable)Session["people"];

            for (int i = 1; i <= people.Columns.Count; i++)
            {
                var editTextBox = this.GridView1.Rows[e.RowIndex].Cells[i].Controls[0] as TextBox;
                if (editTextBox != null)
                    people.Rows[e.RowIndex][i - 1] = editTextBox.Text;
            }

            this.GridView1.EditIndex = -1;
            this.SetDataBinding();
        }

        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            this.GridView1.EditIndex = e.NewEditIndex;
            this.SetDataBinding();
        }

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            this.GridView1.EditIndex = -1;
            this.SetDataBinding();
        }
    }
}
Imports GemBox.Spreadsheet
Imports System
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports System.Web.UI.WebControls

Public Class _Default
    Inherits System.Web.UI.Page

    Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        If Not Page.IsPostBack Then

            Dim people As New DataTable()
            people.Columns.Add("ID", Type.GetType("System.Int32"))
            people.Columns.Add("FirstName", Type.GetType("System.String"))
            people.Columns.Add("LastName", Type.GetType("System.String"))

            Session("people") = people

            Me.LoadDataFromFile(Request.PhysicalApplicationPath & "InputData.xlsx")
            Me.SetDataBinding()

        End If
    End Sub

    ''' <summary>
    ''' Export GridView data to Excel file.
    ''' </summary>
    Sub ExportData_Click(ByVal sender As Object, ByVal s As EventArgs)

        Dim people = DirectCast(Session("people"), DataTable)

        ' Create Excel file.
        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("DataSheet")

        ' Export DataTable that's used as GridView data source into Excel sheet.
        worksheet.InsertDataTable(people, New InsertDataTableOptions("A1") With {.ColumnHeaders = True})

        ' Stream Excel file to client's browser.
        workbook.Save(Me.Response, "Report." & Me.RadioButtonList1.SelectedValue)

    End Sub

    ''' <summary>
    ''' Export GridView data and formatting to Excel file.
    ''' </summary>
    Sub ExportDataAndFormatting_Click(ByVal sender As Object, ByVal s As EventArgs)

        Dim stringWriter As New StringWriter()
        Dim htmlWriter As New HtmlTextWriter(stringWriter)

        ' Export GridView control as HTML content.
        Me.GridView1.RenderControl(htmlWriter)

        Dim htmlOptions = LoadOptions.HtmlDefault
        Dim htmlData = htmlOptions.Encoding.GetBytes(stringWriter.ToString())

        Using htmlStream As New MemoryStream(htmlData)
            ' Load HTML into Excel file.
            Dim workbook = ExcelFile.Load(htmlStream, htmlOptions)

            ' Rename Excel sheet.
            Dim worksheet = workbook.Worksheets(0)
            worksheet.Name = "StyledDataSheet"

            ' Delete Excel column that has Delete and Edit buttons.
            worksheet.Columns.Remove(0)

            ' Stream Excel file to client's browser.
            workbook.Save(Me.Response, "Styled Report." & Me.RadioButtonList1.SelectedValue)
        End Using

    End Sub

    ' Override verification to successfully call GridView1.RenderControl method.
    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    End Sub

    Sub LoadDataFromFile(ByVal fileName As String)

        Dim people = DirectCast(Session("people"), DataTable)

        ' Load Excel file.
        Dim workbook = ExcelFile.Load(fileName)
        Dim worksheet = workbook.Worksheets(0)

        ' Import Excel data into DataTable that's used as GridView data source.
        worksheet.ExtractToDataTable(people, New ExtractToDataTableOptions("A1", worksheet.Rows.Count))

    End Sub

    Sub SetDataBinding()

        Dim people = DirectCast(Session("people"), DataTable)
        Dim peopleDataView = people.DefaultView
        peopleDataView.AllowDelete = True

        Me.GridView1.DataSource = peopleDataView
        Me.GridView1.DataBind()

    End Sub

    Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)

        Dim people = DirectCast(Session("people"), DataTable)
        people.Rows(e.RowIndex).Delete()
        Me.SetDataBinding()

    End Sub

    Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)

        Dim people = CType(Session("people"), DataTable)

        For i As Integer = 1 To people.Columns.Count
            Dim editTextBox = TryCast(Me.GridView1.Rows(e.RowIndex).Cells(i).Controls(0), TextBox)
            If editTextBox IsNot Nothing Then people.Rows(e.RowIndex)(i - 1) = editTextBox.Text
        Next

        Me.GridView1.EditIndex = -1
        Me.SetDataBinding()

    End Sub

    Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)

        Me.GridView1.EditIndex = e.NewEditIndex
        Me.SetDataBinding()

    End Sub

    Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)

        Me.GridView1.EditIndex = -1
        Me.SetDataBinding()

    End Sub

End Class
An ASP.NET application that exports data and formatting from GridView to Excel file
Screenshot of an ASP.NET application with GridView control
Exported GridView data and GridView formatting into Excel files with C# and VB.NET
Screenshot of Excel files exported from an ASP.NET application

The ExcelFile.Save method should not be placed inside the try-catch block. This is because it calls HttpResponse.End which raises ThreadAbortException to terminate the current request.

You can avoid raising that exception by using HttpApplication.CompleteRequest to stream or download the Excel file from ASP.NET applications, as shown in the next example.

/// <summary>
/// Export GridView data to Excel file.
/// </summary>
protected void ExportData_Click(object sender, EventArgs e)
{
    // ...

    // Stream Excel file to client's browser.
    var excelName = "Report.xlsx";
    var excelOptions = SaveOptions.XlsxDefault;

    this.Response.Clear();
    this.Response.ContentType = excelOptions.ContentType;
    this.Response.AddHeader("Content-Disposition", "attachment; filename=" + excelName);

    workbook.Save(this.Response.OutputStream, excelOptions);

    this.Response.Flush();
    this.Response.SuppressContent = true;
    System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();
}
''' <summary>
''' Export GridView data to Excel file.
''' </summary>
Protected Sub ExportData_Click(sender As Object, e As EventArgs)

    ' ...

    ' Stream Excel file to client's browser.
    Dim excelName = "Report.xlsx"
    Dim excelOptions = SaveOptions.XlsxDefault

    Me.Response.Clear()
    Me.Response.ContentType = excelOptions.ContentType
    Me.Response.AddHeader("Content-Disposition", "attachment; filename=" & excelName)

    workbook.Save(Me.Response.OutputStream, excelOptions)

    Me.Response.Flush()
    Me.Response.SuppressContent = True
    System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest()
End Sub

See also

How to create Excel or PDF files on Azure How to create Excel or PDF files on Azure Create Excel or PDF files in a Docker container from .NET Core Create Excel or PDF files in a Docker container from .NET Core

Next steps

GemBox.Spreadsheet is a .NET library that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy