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).
The following example shows how you can use GemBox.Spreadsheet to 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).
<%@ 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 GemBox.Spreadsheet;
using System;
using System.Data;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace MediumTrust
{
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 GemBox.Spreadsheet
Imports System
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports System.Web.UI.WebControls
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
Notice the use of Custom 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. Also, 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();
this.Response.SuppressContent = true;
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()
Me.Response.SuppressContent = True
System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest()
End Sub
