Export GridView Data to Excel using OpenXml

• Download source code - 1.3 MB

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!

1. CreateTable will create columns and populates the row.

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("&nbsp;", "");
    }
    table.Rows.Add(dr);
}

2. 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(
    excelfile,
    DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
    {
      CreateExcelParts(excelDoc, table);
    }
return excelfile;

3. 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.

History

This article can further be extended to include style information during data export.