Exporting The Razor WebGrid To Excel

This article looks at how you can provide your users with the ability to export the contents of a Razor Web Pages WebGrid to an Excel file.

First, I should point out that the methods featured in this article result in a chunk of HTML being saved with a .xls extension. Since HTML is supported natively by Microsoft Office, the resulting file will open in Excel (almost) happily. You may get a warning that the file format doesn't match the extension when opening the file, but if you accept that warning, the data will display just fine. However, the file will not work as a data source for a mail merge, nor can you connect to it via OleDB.

The first thing you need is a grid:

@{
    Page.Title = "Export To Excel";
    var db = Database.Open("Northwind");
    var query = "SELECT CustomerID, CompanyName, ContactName, Address, City, Country, Phone FROM Customers";
    var data = db.Query(query);
    var grid = new WebGrid(data, ajaxUpdateContainerId: "grid");
}
<h1>Export to Excel</h1>
<div id="gridContainer">
    <div id="grid">
        @grid.GetHtml(    
            tableStyle : "table",
            alternatingRowStyle : "alternate",
            headerStyle : "header",
            columns: grid.Columns(
                grid.Column("CustomerID", "ID"),
                grid.Column("CompanyName", "Company Name"),
                grid.Column("ContactName", "Contact Name"),
                grid.Column("Address"),
                grid.Column("City"),
                grid.Column("Country"),
                grid.Column("Phone")
            )
        )
        <img src="/images/excel-icon.png" id="excel" alt="Export to Excel" title="Export to Excel" />
    </div>
</div>

When the user clicks on the image, it should result in the data being downloaded as an Excel file. At the moment, it is just an image, and it appears below the grid. Here's a little bit of jQuery to move the image to the footer area of the grid and to change the cursor when the user hovers over it:

<script type="text/javascript">
    $(function () {
        $('#excel').appendTo($('tfoot tr td')).on('hover', function () {
            $(this).css('cursor', 'pointer');
        });
        $('#excel').on('click', function () {
            $('<iframe src="/GenerateExcel"></iframe>').appendTo('body').hide();
        });
    });
</script>

The jQuery code also adds a handler to the click event of the image. It creates an iframe which it then adds to the body element, and then it makes the display property equal 'none' using the jQuery hide command. The src for the iframe is a file called GenerateExcel.cshtml, which is responsible for creating the Excel file. The hidden iframe technique is a clean way to manage downloads via AJAX without leaving the current page. Adding an iframe dynamically like this effectively "sucks" the HTTP response from the src URL through to the current page.

Here's the code for GenerateExcel:

@{
    Layout = null;
    var db = Database.Open("Northwind");
    var sql = "SELECT CustomerID, CompanyName, ContactName, Address, City, Country, Phone FROM Customers";
    var data = db.Query(sql);
    var grid = new WebGrid(data, canPage: false, canSort: false);
    Response.AddHeader("Content-disposition", "attachment; filename=report.xls");
    Response.ContentType = "application/octet-stream";
}
@grid.GetHtml(
    columns: grid.Columns(
        grid.Column("CustomerID", "ID"),
        grid.Column("CompanyName", "Company Name"),
        grid.Column("ContactName", "Contact Name"),
        grid.Column("Address"),
        grid.Column("City"),
        grid.Column("Country"),
        grid.Column("Phone")
    )
)

As with any file that is intended to deliver a non-html response, the Layout is set to null to prevent stray HTML being included in the output. The same query is executed against the database and a WebGrid is created and printed to the page. The WebGrid has paging and sorting disabled, which results in all the data from the query being displayed. The Content-Disposition value is set to attachment, and the Content-type is set to application/octet-stream. This combination results in the browser offering a choice to the user - save or open, rather than attempting to display the response.

This all works fine, but there is a limitation. The WebGrid only accepts CSS class names as a means to style the output. That works in a web page but not in an Excel worksheet. If you want to have any control over the style of your worksheet, you have to eschew the WebGrid in favour of your own HTML table, where you can inject inline styles. Here's a revised version of GenerateExcel.cshtml that adds gridlines to the cells, and make the header row bold:

@{
    Layout = null;
    var db = Database.Open("Northwind");
    var sql = "SELECT CustomerID, CompanyName, ContactName, Address, City, Country, Phone FROM Customers";
    var data = db.Query(sql);
    var columns = data.First().Columns;
    Response.AddHeader("Content-disposition", "attachment; filename=report.xls");
    Response.ContentType = "application/octet-stream";
}
<table style="border: 1px solid #a59f9f;">
    <tr style="font-weight: bold">
    @foreach(var column in columns){
        <td style="border: 1px solid #a59f9f;">@column</td>
    }
    </tr>
    @foreach(var row in data){
        <tr>
        @foreach(var column in columns){
            <td style="border: 1px solid #a59f9f;">@row[column]</td>
        }
        </tr>
    }
</table>

And there you have it. As was mentioned at the top of this article, the files generated through this method ar pseudo-Excel files. The technique only works if you generate 97-2003 Excel files (.xls extension). If you attempt to generate OpenXML-based Excel 2007+ (.xlsx) files, the result will fail to open. If you need to generate files that can act as OLEDB data sources, or you need to have an 'x' on the end of your file extension, you should use OLDB to generate the files instead.

The source code for the sample site that accompanies this article is available as a GitHub repo.

 

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

2 Comments

- Nathan

Great article as always, thank you!

Quick question. What is the correct method for altering the javascript for it to call the GenerateExcel method by passing it a variable containing an ID. This could then be used to query the SQL and thus return a subset of the customers table.

My approach so far is to append a query string to the iframe "src" value. This seems to generate the correct outcome but I cannot figure out the syntax for it using an ID variable from razor code in the same file?

Thanks

$('<iframe src="GenerateExcelStyled?id=">

- David

Thank you for the great article, it was very helpful.

Is there a way to have the freeze pane option automatically inserted into the export?

Recent Comments

Justin Kusuma 7/24/2015 3:38 AM
In response to Posting Data With jQuery AJAX In ASP.NET Razor Web Pages
Hi Mike, thanks much for sharing such an article :) Really help me a lot... further, I'd like to...

Michael Easterbrook 7/22/2015 5:35 PM
In response to Inline Razor Syntax Overview
I removed the @ symbols and I am still getting the same error. It only occurs when I have an "if" a...

Sujay 7/22/2015 1:36 PM
In response to ASP.NET MVC, Entity Framework, One-to-Many and Many-to-Many INSERTS
can you explain how to link two tables so that it forms many to many relationship?(Article and...

Max G 7/21/2015 9:29 PM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi, I've opted for this solution in one of my applications but i've found that the apppool is and I...

Michael Easterbrook 7/20/2015 4:31 PM
In response to Inline Razor Syntax Overview
When I have the following code: @foreach (var procRow in procRowDecade) { if (@procRow[3] +...

Shanice 7/18/2015 10:58 PM
In response to A Better Way To Export Gridviews To Excel
Hi. I'm working with mvc. I need to add the above code in the business logic layer, however the...

Matt 7/18/2015 6:29 PM
In response to Nested Layout Pages with Razor
Cheers sir, nice explanation :)...

Keshavan 7/17/2015 9:06 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi Mike, I have followed exactly as illustrated in blog, I get error "StdSchedulerFactory.cs" not...

Paul Thiel 7/16/2015 5:17 PM
In response to ASP.NET 5 By Numbers
Comments Below: "The new version of ASP.NET is called ASP.NET 5. It is a framework for developing...

saket singh 7/16/2015 8:42 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
hi Mike, great tutorial on Quartz.net , but i have One Problem , Everything is working fine as as...