Export Excel file in ASP.NET
The following example is a ASP.NET Web Forms application that exports generated Excel file 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.


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="MediumTrustSampleCs._Default" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<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;
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
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")
' 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