This article tells how to export ASP.NET GridView data to an Excel file using
Office OpenXML SDK 2.0.
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(); CreateTable(grdvTest, ref table); CreateTable(grdvTest, ref table);
Let’s see what these method internally do!
CreateTable will create columns and populates the row.
for (int i = 0; i < grdvTest.HeaderRow.Cells.Count; i++)
foreach (GridViewRow row in grdvTest.Rows)
dr = table.NewRow();
for (int i = 0; i < row.Cells.Count; i++)
dr[i] = row.Cells[i].Text.Replace(" ", "");
- Export to Excel will create a file in systems temporary location and returns the file name.
string excelfile = Path.GetTempPath() +
Guid.NewGuid().ToString() + ".xlsx";
using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(
- Once the temporary file ready, stream it to client using
Response.Redirect. The Excel file would look like:
Points of Interest
This method actually creates an Excel file on server (without using interops) and streams the file to client.
This article can further be extended to include style information during data export.