Export Excel files from ASP.NET GridView

When using GemBox.Spreadsheet in ASP.NET application, a common task that you could 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 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).

Notice the use of private fonts by specifying a value for the FontSettings.FontsBaseDirectory property. This enables the ASP.NET application to export an Excel file into PDF format in a Medium Trust Environment since the specified location is under the ASP.NET application's control.

An ASP.NET application that exports data and formatting from GridView to Excel file
Screenshot of an ASP.NET with GridView control
Exported GridView data and GridView formatting into Excel files with C# and VB.NET
Screenshot of exported Excel files from ASP.NET application
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="MediumTrust._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 System;
using System.Data;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using GemBox.Spreadsheet;

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

        // To be able to save ExcelFile to PDF format in Medium Trust environment,
        // you need to specify a font files location that is under your ASP.NET application's control.
        FontSettings.FontsBaseDirectory = Server.MapPath("Fonts/");

        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 System
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports GemBox.Spreadsheet

Partial Public Class _Default
    Inherits System.Web.UI.Page

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

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

        ' To be able to save ExcelFile to PDF format in Medium Trust environment,
        ' you need to specify a font files location that is under your ASP.NET application's control.
        FontSettings.FontsBaseDirectory = Server.MapPath("Fonts/")

        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

Note, 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. However, note that this method does not work in applications with a Medium Level Trust.

/// <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();
    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()
    System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest()
End Sub

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.