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>

1using System;
2using System.Data;
3using System.Web.UI;
4using System.Web.UI.WebControls;
5using GemBox.Spreadsheet;
6
7namespace MediumTrustSampleCs
8{
9    public partial class _Default : System.Web.UI.Page
10    {
11        protected void Page_Load(object sender, EventArgs e)
12        {
13            SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
14
15            // By specifying a location that is under ASP.NET application's control, 
16            // GemBox.Spreadsheet can use file system operations to retrieve font data even in Medium Trust environment.
17            FontSettings.FontsBaseDirectory = Server.MapPath("Fonts/");
18
19            if (!Page.IsPostBack)
20            {
21                DataTable people = new DataTable();
22
23                people.Columns.Add("ID", typeof(int));
24                people.Columns.Add("FirstName", typeof(string));
25                people.Columns.Add("LastName", typeof(string));
26
27                Session["people"] = people;
28
29                this.LoadDataFromFile(Request.PhysicalApplicationPath + "InData.xlsx");
30
31                this.SetDataBinding();
32            }
33        }
34
35        protected void Export_Click(object sender, EventArgs e)
36        {
37            DataTable people = (DataTable)Session["people"];
38
39            // Create Excel file.
40            ExcelFile ef = new ExcelFile();
41            ef.DefaultFontName = "Calibri";
42            ExcelWorksheet ws = ef.Worksheets.Add("DataSheet");
43            ws.InsertDataTable(people, new InsertDataTableOptions(0, 0) { ColumnHeaders = true });
44
45            // Stream or export a file to ASP.NET client's browser.
46            ef.Save(this.Response, "Report." + this.RadioButtonList1.SelectedValue);
47        }
48
49        private void LoadDataFromFile(string fileName)
50        {
51            DataTable people = (DataTable)Session["people"];
52
53            ExcelFile ef = ExcelFile.Load(fileName);
54
55            ExcelWorksheet ws = ef.Worksheets[0];
56
57            ws.ExtractToDataTable(people, new ExtractToDataTableOptions("A1", ws.Rows.Count));
58        }
59
60        private void SetDataBinding()
61        {
62            DataTable people = (DataTable)Session["people"];
63            DataView peopleDataView = people.DefaultView;
64
65            this.GridView1.DataSource = peopleDataView;
66            peopleDataView.AllowDelete = true;
67            this.GridView1.DataBind();
68        }
69
70        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
71        {
72            DataTable people = (DataTable)Session["people"];
73
74            people.Rows[e.RowIndex].Delete();
75            this.SetDataBinding();
76        }
77
78        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
79        {
80            this.GridView1.EditIndex = e.NewEditIndex;
81            this.SetDataBinding();
82        }
83
84        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
85        {
86            int i;
87            int rowIndex = e.RowIndex;
88            DataTable people = (DataTable)Session["people"];
89
90            for (i = 1; i <= people.Columns.Count; i++)
91            {
92                TextBox editTextBox = this.GridView1.Rows[rowIndex].Cells[i].Controls[0] as TextBox;
93
94                if (editTextBox != null)
95                    people.Rows[rowIndex][i - 1] = editTextBox.Text;
96            }
97
98            this.GridView1.EditIndex = -1;
99            this.SetDataBinding();
100        }
101
102        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
103        {
104            this.GridView1.EditIndex = -1;
105            this.SetDataBinding();
106        }
107    }
108}

1Imports System.IO
2Imports System.Xml
3Imports System.Text
4Imports System.Data
5Imports GemBox.Spreadsheet
6
7Public Class _Default
8    Inherits System.Web.UI.Page
9
10    Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
11
12        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
13
14        ' By specifying a location that is under ASP.NET application's control, 
15        ' GemBox.Spreadsheet can use file system operations to retrieve font data even in Medium Trust environment.
16        FontSettings.FontsBaseDirectory = Server.MapPath("Fonts/")
17
18        If Not Page.IsPostBack Then
19            Dim people As DataTable = New DataTable()
20
21            people.Columns.Add("ID", Type.GetType("System.Int32"))
22            people.Columns.Add("FirstName", Type.GetType("System.String"))
23            people.Columns.Add("LastName", Type.GetType("System.String"))
24
25            Session("people") = people
26
27            Me.LoadDataFromFile(Request.PhysicalApplicationPath & "InData.xlsx")
28            Me.SetDataBinding()
29        End If
30
31    End Sub
32
33    Sub Export_Click(ByVal sender As Object, ByVal s As EventArgs)
34
35        Dim people As DataTable = DirectCast(Session("people"), DataTable)
36
37        ' Create Excel file.
38        Dim ef As ExcelFile = New ExcelFile
39        ef.DefaultFontName = "Calibri"
40        Dim ws As ExcelWorksheet = ef.Worksheets.Add("DataSheet")
41        ws.InsertDataTable(people, New InsertDataTableOptions(0, 0) With {.ColumnHeaders = True})
42
43        ' Stream or export a file to ASP.NET client's browser.
44        ef.Save(Me.Response, "Report." & Me.RadioButtonList1.SelectedValue)
45
46    End Sub
47
48    Sub LoadDataFromFile(ByVal fileName As String)
49
50        Dim ef As ExcelFile = ExcelFile.Load(fileName)
51        Dim ws As ExcelWorksheet = ef.Worksheets(0)
52
53        Dim people As DataTable = DirectCast(Session("people"), DataTable)
54        ws.ExtractToDataTable(people, New ExtractToDataTableOptions("A1", ws.Rows.Count))
55
56    End Sub
57
58    Sub SetDataBinding()
59
60        Dim people As DataTable = DirectCast(Session("people"), DataTable)
61        Dim peopleDataView As DataView = people.DefaultView
62
63        Me.GridView1.DataSource = peopleDataView
64        peopleDataView.AllowDelete = True
65        Me.GridView1.DataBind()
66
67    End Sub
68
69    Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
70
71        Dim people As DataTable = DirectCast(Session("people"), DataTable)
72
73        people.Rows(e.RowIndex).Delete()
74        Me.SetDataBinding()
75
76    End Sub
77
78    Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
79
80        Me.GridView1.EditIndex = e.NewEditIndex
81        Me.SetDataBinding()
82
83    End Sub
84
85    Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
86
87        Dim i As Integer
88        Dim rowIndex As Integer = e.RowIndex
89        Dim people As DataTable = Session("people")
90
91        For i = 1 To people.Columns.Count
92            Dim editTextBox As TextBox = TryCast(Me.GridView1.Rows(rowIndex).Cells(i).Controls(0), TextBox)
93
94            If Not (editTextBox Is Nothing) Then
95                people.Rows(rowIndex)(i - 1) = editTextBox.Text
96            End If
97        Next
98
99        Me.GridView1.EditIndex = -1
100        Me.SetDataBinding()
101
102    End Sub
103
104    Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
105
106        Me.GridView1.EditIndex = -1
107        Me.SetDataBinding()
108
109    End Sub
110
111End Class

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.