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 lang="en-US">
<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++)
{
var editTextBox = this.GridView1.Rows[rowIndex].Cells[i].Controls[0] as
System.Web.UI.WebControls.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 = TryCast(Me.GridView1.Rows(rowIndex).Cells(i).Controls(0),
System.Web.UI.WebControls.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