A Better Way To Export Gridviews To Excel

EPPlus is an open source .NET library that reads and writes .xlsx files using the Open XML format which is the underlying technology behind Excel 2007 onwards. It is possible to use the Open XML SDK directly, but this library makes things much easier for the developer. It is available from Nuget using the following command in the Package Manager Console:

install-package epplus

I'll show a couple of approaches to this problem. One features the a GridView with a DataTable being bound to it, and the other uses a LINQ to Entities query as the datasource. Both examples use the same aspx code, a simple Gridview with autogenerate columns set to true, and a button whose click event kicks off the export generation and download:

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="ExportGridViewToExcel.aspx.cs" Inherits="WebFormsTest.ExportGridViewToExcel" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
    <asp:GridView ID="GridView1" runat="server" />
    <asp:Button ID="ExportToExcel" runat="server" Text="Export To Excel" OnClick="ExportToExcel_Click" />
</asp:Content>

Both examples will use the Northwind database and fetch the contents of the Products table for display and export. The Page_Load method for both approaches is identical:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridView1.DataSource = GetProducts();
        GridView1.DataBind();
    }
}

Here's the button click event handler and GetProducts method for the Entity Framework approach:

protected void ExportToExcel_Click(object sender, EventArgs e)
{
    var products = GetProducts();
    GridView1.DataSource = products;
    GridView1.DataBind();
    ExcelPackage excel = new ExcelPackage();
    var workSheet = excel.Workbook.Worksheets.Add("Products");
    var totalCols = GridView1.Rows[0].Cells.Count;
    var totalRows = GridView1.Rows.Count;
    var headerRow = GridView1.HeaderRow;
    for (var i = 1; i <= totalCols; i++ )
    {
        workSheet.Cells[1, i].Value = headerRow.Cells[i - 1].Text;
    }
    for (var j = 1; j <= totalRows; j++ )
    {
        for (var i = 1; i <= totalCols; i++)
        {
            var product = products.ElementAt(j-1);
            workSheet.Cells[j + 1, i].Value = product.GetType().GetProperty(headerRow.Cells[i - 1].Text).GetValue(product, null);
        }
    }
    using (var memoryStream = new MemoryStream())
    {
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;  filename=products.xlsx");
        excel.SaveAs(memoryStream);
        memoryStream.WriteTo(Response.OutputStream);
        Response.Flush();
        Response.End();
    }
}

public List<Product> GetProducts()
{
    using (var context = new NorthwindContext())
    {
        return context.Products.ToList();
    }
}

This approach also requires the inclusion of a few extra using directives at the top of the file:

using OfficeOpenXml;
using System.IO;
using WebFormsTest.Models;

The first is the main namespace in the EPPlus library and the second is required to make the MemoryStream class available. The final one is the namespace for the Product class in this particular project. The GetProducts method returns a List<Product> from a LINQ query. The code in the button click event handler calls this method and binds the data to the GridView. The GridView will not be displayed or rendered to HTML, however. The main reason for binding the data is because the GridView provides a fairly simple way to get the column names for the spreadsheet via the HeaderRow property. First, a new ExcelPackage is created, which represents a workbook. A work sheet is added, then the HeaderRow is iterated to provide values for the first row of cells in the work sheet. Rows and columns in EPPlus work sheets have a 1-based index, rather than the 0 based index system that .NET developers are used to.

Next, the List<Product> is iterated to obtain the values for the cells using Reflection to get the values of just the properties that were bound to the GridView. Finally, the Response's ContentType is set appropriately and the package is saved as a Stream which is then written to the OutputStream property of the Response object. User gets a nice download which opens without any complaints or warnings.

Next is the GetProducts method and the button click event handler for the ADO.NET DataTable approach:

protected void ExportToExcel_Click(object sender, EventArgs e)
{
    var products = GetProducts();
    ExcelPackage excel = new ExcelPackage();
    var workSheet = excel.Workbook.Worksheets.Add("Products");
    var totalCols = products.Columns.Count;
    var totalRows = products.Rows.Count;

    for (var col = 1; col <= totalCols; col++)
    {
        workSheet.Cells[1, col].Value = products.Columns[col-1].ColumnName;
    }
    for (var row = 1; row <= totalRows; row++)
    {
        for (var col = 0; col < totalCols; col++)
        {
            workSheet.Cells[row + 1, col + 1].Value = products.Rows[row - 1][col];
        }
    }
    using (var memoryStream = new MemoryStream())
    {
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;  filename=products.xlsx");
        excel.SaveAs(memoryStream);
        memoryStream.WriteTo(Response.OutputStream);
        Response.Flush();
        Response.End();
    }
}

public DataTable GetProducts()
{
    using (var conn  = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindContext"].ConnectionString))
    using (var cmd = new SqlCommand("SELECT * FROM Products", conn))
    using (var adapter = new SqlDataAdapter(cmd))
    {
        var products = new DataTable();
        adapter.Fill(products);
        return products;
    }
}

Here are the extra using directives at the top of the file:

using OfficeOpenXml;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

In this approach, there is no need to rebind the data to the GridView in the button click event handler. The DataTable API provides easy access to both the schema and the data itself. In all other respects, the code is similar to the Entity Framework example.

Summary

This article has shown how to use the EPPlus library to take the data from a GridView and export it to genuine .xlsx files, eliminating the problems associated with adding a .xls extension to some HTML. It is a much better way to export Gridviews to Excel.