Export GridView Data to Excel using OpenXml






4.92/5 (8 votes)
ASP.NET GridView to Excel conversion.
Introduction
This article tells how to export ASP.NET GridView data to an Excel file using Office OpenXML SDK 2.0.
Background
Exporting GridView data to Excel is a common functionality you would have come across while working in web forms. There are many ways to do this. Indeed, couple of approaches require Microsoft ACE engine to be installed on the server. I thought why not we try do this using Office OpenXML?
Using the code
So I started with creating an ASPX page (would look like the below one…)
An Export button click event would do:
DataTable table = new DataTable(); // create an empty data table
CreateTable(grdvTest, ref table); // fill it with grid view data
// ExportToExcel(table);
// copy this file from Temp directory to the current working directory
// Response.Redirect(localCopy);
CreateTable(grdvTest, ref table); // fill it with grid view data
// ExportToExcel(table);
// copy this file from Temp directory to the current working directory
// Response.Redirect(localCopy);
Let’s see what these method internally do!
CreateTable
will create columns and populates the row.- Export to Excel will create a file in systems temporary location and returns the file name.
- Once the temporary file ready, stream it to client using
Response.Redirect
. The Excel file would look like:
for (int i = 0; i < grdvTest.HeaderRow.Cells.Count; i++)
table.Columns.Add(grdvTest.HeaderRow.Cells[i].Text);
// fill rows
foreach (GridViewRow row in grdvTest.Rows)
{
DataRow dr;
dr = table.NewRow();
for (int i = 0; i < row.Cells.Count; i++)
{
dr[i] = row.Cells[i].Text.Replace(" ", "");
}
table.Rows.Add(dr);
}
string excelfile = Path.GetTempPath() +
Guid.NewGuid().ToString() + ".xlsx";
using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(
excelfile,
DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
CreateExcelParts(excelDoc, table);
}
return excelfile;
Points of Interest
This method actually creates an Excel file on server (without using interops) and streams the file to client.
History
This article can further be extended to include style information during data export.