Step 1: Download and install EasyXLS Excel Library for .NET
To download the trial version of EasyXLS Excel Library, press the below button:
If you already own a license key, you may login and download EasyXLS from your account.
Step 2: Create a C# or VB.NET project
If don't have a project, create one as ASP.NET web application, windows forms app, console application, class library or service.
Step 3: Include EasyXLS library into project
EasyXLS.dll must be added as reference to your project. EasyXLS.dll can be found after installing EasyXLS, in "Dot NET version" folder.
Step 4: Run C# or VB.NET code that exports GridView to Excel
The below example shows how to export GridView to Excel in C# and VB.NET from ASP.NET web application.
The source code that export the GridView is split in 3 sections. The first section contains the ASP.NET web page. The second one is the class behind the HTML web page. The third one is the method that exports data from GridView to Excel file.
ASP.NET web page source code The web page contains the GridView and a button that exports GridView data to Excel file.
ASP.NET source code behind The class behind ASP.NET page contains the method that bind the GridView and the method that exports the GridView to Excel file. The GridView is populated with some custom data, but it also can be data from a SQL database.
using System;
using System.Data;
using EasyXLS;
namespace WebApplication
{
public partial class ExportToExcelForm : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.BindGridView();
}
protected void ExportGridViewToExcel(object sender, EventArgs e)
{
...(check source code)
}
private void BindGridView()
{
// Create and populate the datatable that stores GridView data
// Data can be also be bind from database
DataTable dataTable = new DataTable();
DataColumn[] columns = { new DataColumn("ID"), new DataColumn("Data") };
Object[] row1 = { "1", "Data 1" };
Object[] row2 = { "2", "Data 2" };
Object[] row3 = { "3", "Data 3" };
dataTable.Columns.AddRange(columns);
dataTable.Rows.Add(row1);
dataTable.Rows.Add(row2);
dataTable.Rows.Add(row3);
// Set the GridView data
gridView.DataSource = dataTable;
gridView.DataBind();
}
}
}
Imports EasyXLS
Partial Public Class ExportToExcelForm Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles Me.Load
BindGridView()
End SubProtected Sub ExportGridViewToExcel(ByVal sender As Object, ByVal e As System.EventArgs)
...(check source code)
End Sub
Private Sub BindGridView()
' Create and populate the datatable that stores GridView data
'Data can be also be bind from databaseDim dataTable As New DataTable()
Dim columns As DataColumn() = {New DataColumn("ID"), New DataColumn("Data")}
Dim row1 As Object() = {"1", "Data 1"}
Dim row2 As Object() = {"2", "Data 2"}
Dim row3 As Object() = {"3", "Data 3"}
dataTable.Columns.AddRange(columns)
dataTable.Rows.Add(row1)
dataTable.Rows.Add(row2)
dataTable.Rows.Add(row3)
' Set the GridView data
gridView.DataSource = dataTable
gridView.DataBind()
End Sub
End Class
Export GridView to Excel button source code The method export the GridView data to Excel file and also the formatting for header and cells is kept.
protected void ExportGridViewToExcel(object sender, EventArgs e)
{
// Create a dataset that keeps the gridview datatable
DataSet dataSet = new DataSet();
dataSet.Tables.Add((DataTable)gridView.DataSource);
// Apply the formatting of the GridView to the exported cells
ExcelAutoFormat xlsAutoformat = new ExcelAutoFormat();
// Format the cells - GridView header
ExcelStyle xlsStyleHeader = new ExcelStyle();
xlsStyleHeader.setBackground(gridView.HeaderStyle.BackColor);
xlsStyleHeader.setForeground(gridView.HeaderStyle.ForeColor);
if (gridView.HeaderStyle.Font.Name.Length == 0)
xlsStyleHeader.setFont("Times New Roman");
else
xlsStyleHeader.setFont(gridView.HeaderStyle.Font.Name);
if (gridView.HeaderStyle.Font.Size.Unit.Value == 0)
xlsStyleHeader.setFontSize(12);
else
xlsStyleHeader.setFontSize((int)gridView.HeaderStyle.Font.Size.Unit.Value);
xlsStyleHeader.setBold(gridView.HeaderStyle.Font.Bold);
xlsAutoformat.setHeaderRowStyle(xlsStyleHeader);
// Format the cells - GridView row data
ExcelStyle xlsStyleRow = new ExcelStyle();
xlsStyleRow.setBackground(gridView.RowStyle.BackColor);
if (gridView.RowStyle.Font.Name.Length == 0)
xlsStyleRow.setFont("Times New Roman");
else
xlsStyleRow.setFont(gridView.RowStyle.Font.Name);
if (gridView.RowStyle.Font.Size.Unit.Value == 0)
xlsStyleRow.setFontSize(12);
else
xlsStyleRow.setFontSize((int)gridView.RowStyle.Font.Size.Unit.Value);
xlsAutoformat.setTableStyle(xlsStyleRow);
// Choose a name for the Excel filestring fileName = "ExportedGridView.xlsx";
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// Export Excel file and prompt the "Open or Save Dialog Box"
ExcelDocument workbook = new ExcelDocument();
workbook.easy_WriteXLSXFile_FromDataSet(Response.OutputStream,
dataSet, xlsAutoformat, "GridView");
// Dispose memory
workbook.Dispose();
Response.End();
}
Protected Sub ExportGridViewToExcel(ByVal sender As Object, _
ByVal e As System.EventArgs)
' Create a dataset that keeps the gridview datatableDim dataSet As New DataSet()
dataSet.Tables.Add(gridView.DataSource)
' Apply the formatting of the GridView to the exported cellsDim xlsAutoformat As ExcelAutoFormat = New ExcelAutoFormat()
' Format the cells - GridView headerDim xlsStyleHeader As ExcelStyle = New ExcelStyle()
xlsStyleHeader.setBackground(gridView.HeaderStyle.BackColor)
xlsStyleHeader.setForeground(gridView.HeaderStyle.ForeColor)
If (gridView.HeaderStyle.Font.Name.Length = 0) Then
xlsStyleHeader.setFont("Times New Roman")
Else
xlsStyleHeader.setFont(gridView.HeaderStyle.Font.Name)
End If
If (gridView.HeaderStyle.Font.Size.Unit.Value = 0) Then
xlsStyleHeader.setFontSize(12)
Else
xlsStyleHeader.setFontSize(gridView.HeaderStyle.Font.Size.Unit.Value)
End If
xlsStyleHeader.setBold(gridView.HeaderStyle.Font.Bold)
xlsAutoformat.setHeaderRowStyle(xlsStyleHeader)
' Format the cells - GridView row dataDim xlsStyleRow As ExcelStyle = New ExcelStyle()
xlsStyleRow.setBackground(gridView.RowStyle.BackColor)
If (gridView.RowStyle.Font.Name.Length = 0) Then
xlsStyleRow.setFont("Times New Roman")
Else
xlsStyleRow.setFont(gridView.RowStyle.Font.Name)
End If
If (gridView.RowStyle.Font.Size.Unit.Value = 0) Then
xlsStyleRow.setFontSize(12)
Else
xlsStyleRow.setFontSize(gridView.RowStyle.Font.Size.Unit.Value)
End If
xlsAutoformat.setTableStyle(xlsStyleRow)
' Choose a name for the Excel file Dim fileName As String = "ExportedGridView.xlsx"
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=" + fileName)
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"' Export Excel file and prompt the "Open or Save Dialog Box"Dim workbook As New ExcelDocument()
workbook.easy_WriteXLSXFile_FromDataSet(Response.OutputStream, _
dataSet, xlsAutoformat, "GridView")
' Dispose memory
workbook.Dispose()
Response.End()
End Sub
Formatting cells
EasyXLS™ enables you to format cells, rows and columns in order to set the fonts and colors.
