Export Excel file with ASP.NET

The following example is a ASP.NET Web Forms application that exports generated Excel files directly to the browser.

Use the ExcelFile.Save method from C# or VB.NET code in your ASP.NET application to stream (download) an Excel file directly to ASP.NET HttpResponse.OutputStream. For additional examples of its usage in ASP.NET MVC and ASP.NET Web API applications, see the ExcelFile.Save method help page.

Note 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 ASP.NET application's control.

Screenshot
Export Excel in ASP.NET Screenshot

See the full code below. 

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">   
    <title>GemBox.Spreadsheet ASP.NET Sample</title>    
</head>
<body>
    <form id="form1" runat="server">   
        <asp:GridView ID="GridView1" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84" 
            BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" 
            OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" >
            <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="Export" runat="server" Text="Export" OnClick="Export_Click" />
        <br />        
    </form>
</body>
</html>

using System;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using GemBox.Spreadsheet;

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

            // By specifying a location that is under ASP.NET application's control, 
            // GemBox.Spreadsheet can use file system operations to retrieve font data even in Medium Trust environment.
            FontSettings.FontsBaseDirectory = Server.MapPath("Fonts/");

            if (!Page.IsPostBack)
            {
                DataTable 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 + "InData.xlsx");

                this.SetDataBinding();
            }
        }

        protected void Export_Click(object sender, EventArgs e)
        {
            DataTable people = (DataTable)Session["people"];

            // Create Excel file.
            ExcelFile ef = new ExcelFile();
            ef.DefaultFontName = "Calibri";
            ExcelWorksheet ws = ef.Worksheets.Add("DataSheet");
            ws.InsertDataTable(people, new InsertDataTableOptions(0, 0) { ColumnHeaders = true });

            // Stream or export a file to ASP.NET client's browser.
            ef.Save(this.Response, "Report." + this.RadioButtonList1.SelectedValue);
        }

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

            ExcelFile ef = ExcelFile.Load(fileName);

            ExcelWorksheet ws = ef.Worksheets[0];

            ws.ExtractToDataTable(people, new ExtractToDataTableOptions("A1", ws.Rows.Count));
        }

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

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

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            DataTable people = (DataTable)Session["people"];

            people.Rows[e.RowIndex].Delete();
            this.SetDataBinding();
        }

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

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int i;
            int rowIndex = e.RowIndex;
            DataTable people = (DataTable)Session["people"];

            for (i = 1; i <= people.Columns.Count; i++)
            {
                TextBox editTextBox = this.GridView1.Rows[rowIndex].Cells[i].Controls[0] as TextBox;

                if (editTextBox != null)
                    people.Rows[rowIndex][i - 1] = editTextBox.Text;
            }

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

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

Public Class _Default
    Inherits System.Web.UI.Page

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

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

        ' By specifying a location that is under ASP.NET application's control, 
        ' GemBox.Spreadsheet can use file system operations to retrieve font data even in Medium Trust environment.
        FontSettings.FontsBaseDirectory = Server.MapPath("Fonts/")

        If Not Page.IsPostBack Then
            Dim people As DataTable = 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 & "InData.xlsx")
            Me.SetDataBinding()
        End If

    End Sub

    Sub Export_Click(ByVal sender As Object, ByVal s As EventArgs)

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

        ' Create Excel file.
        Dim ef As ExcelFile = New ExcelFile
        ef.DefaultFontName = "Calibri"
        Dim ws As ExcelWorksheet = ef.Worksheets.Add("DataSheet")
        ws.InsertDataTable(people, New InsertDataTableOptions(0, 0) With {.ColumnHeaders = True})

        ' Stream or export a file to ASP.NET client's browser.
        ef.Save(Me.Response, "Report." & Me.RadioButtonList1.SelectedValue)

    End Sub

    Sub LoadDataFromFile(ByVal fileName As String)

        Dim ef As ExcelFile = ExcelFile.Load(fileName)
        Dim ws As ExcelWorksheet = ef.Worksheets(0)

        Dim people As DataTable = DirectCast(Session("people"), DataTable)
        ws.ExtractToDataTable(people, New ExtractToDataTableOptions("A1", ws.Rows.Count))

    End Sub

    Sub SetDataBinding()

        Dim people As DataTable = DirectCast(Session("people"), DataTable)
        Dim peopleDataView As DataView = people.DefaultView

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

    End Sub

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

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

        people.Rows(e.RowIndex).Delete()
        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_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)

        Dim i As Integer
        Dim rowIndex As Integer = e.RowIndex
        Dim people As DataTable = Session("people")

        For i = 1 To people.Columns.Count
            Dim editTextBox As TextBox = TryCast(Me.GridView1.Rows(rowIndex).Cells(i).Controls(0), TextBox)

            If Not (editTextBox Is Nothing) Then
                people.Rows(rowIndex)(i - 1) = editTextBox.Text
            End If
        Next

        Me.GridView1.EditIndex = -1
        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