A Better Way To Export Gridviews To Excel

4.68 (22 votes)

There are countless articles that demonstrate exporting ASP.NET GridViews to Excel. They pretty much all do the same thing - they use the RenderControl method to generate HTML and then save it to a file with a .xls extension. This kind of works, but the resulting file is actually an HTML file masquerading as an Excel file. And that has two flaws: it cannot be used as a data source because providers complain that it is not in an acceptable format, and users of Office 2007 or newer are always confronted with a warning about the contents of the file when they try to open it, causing confusion, suspicion or annoyance. This article shows how to use the free, open source library to export the content of a GridView control to a genuine .xlsx file that doesn't suffer these problems.

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.

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 74016

10 Comments

- Satyabrata Mohapatra

Excellent article.

- A2H

Good one Mike

- Shanice

Hi.

I'm working with mvc. I need to add the above code in the business logic layer, however the Response. is not picking up.

- Mike

@Shanice

You shouldn't have references to the Response object in your Business Logic Layer. You should return a stream from your BLL method instead.

- TimPat10

Great article. I've noticed that date fields have to be re-formatted when the spreadsheet is pulled up (or else they appear as decimal numbers). Any way around that?

Thanks!

- Jack Carr

Do you know of any VB version of the example? I'm working with pages that are entirely VB and don't want to try and convert it to C#. Thanks for any help you might be on this.

- Alisa

Thanks for this example! I am also asking about the date fields having to be re-formatted when the spreadsheet is opened. Is there a way to set that before export?

- Bob_the_WebBuilder

Hello,

I've found this yesterday and I implemented it today. And it works. Awesome. Thank you.

- ranjith

Hello Mike. i am exporting from gridview, because i have some images in my gridview. but i am getting error with " using (var context = new NorthwindContext())".
????????

- Mike

@ranjith,

You get that kind of error as a result of copying and pasting code without modifying it to suit your application.

Recent Comments

Gfw 03/02/2017 09:48
In response to Free SSL Certificates On IIS With LetsEncrypt
I have used WinSimple for about the last 9 months - works great. One thing that you want to make of...

Ted Driver 02/02/2017 13:24
In response to Free SSL Certificates On IIS With LetsEncrypt
This looks great is you have command line access to your web server - what about those of us on Is...

Carl T. 06/11/2016 05:43
In response to Server.MapPath Equivalent in ASP.NET Core
Very succinct and easy to follow. Worked perfectly the first time for me. Thanks!!...

Manoj Kulkarni 04/11/2016 05:47
In response to Entity Framework Core DbContext Updated
Great post....

Sivu 19/10/2016 08:21
In response to Entity Framework Core TrackGraph For Disconnected Data
Oh that's very very very nice ! Thanks for the write up Mike, much appreciated for the taking the to...

Mark 12/10/2016 16:42
In response to ASP.NET Web Pages vNext or Razor Pages
Although "Web Pages" was removed from the roadmap, has it just been renamed to "Razor Pages"?...

Satyabrata 12/10/2016 09:20
In response to Entity Framework Core TrackGraph For Disconnected Data
Nice article. Please write more articles featuring ASP.Net web pages. Thank you...

Julian 26/09/2016 14:27
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Fantastic, many thanks Mike! Had got half way down this road before finding your article - saved...

Abolfazl Roshanzamir 14/09/2016 05:36
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Nice article. Thanke you so much ....

cyrus 02/09/2016 15:12
In response to ASP.NET Web Pages vNext or Razor Pages
I've got some news. As Damian stated in this link: https://github.com/aspnet/Mvc/issues/5208 “We...