ASP.NET: Exporting a report to Excel, with HTTP Compression
I often find myself asked to code a page to display data from a database in a format users of a ASP.NET web site can view in Excel.
This is not a hard thing to do and I thought I'd explain how to do it here, with a couple of enhancements:
- Offer the option to export it as XML (so our data can be imported into another application via HTTP)
- Offer the option to display the data as a plain HTML table
- Compress the data using GZip HTTP compression, if the client supports it, to make the page load faster as it may be a lot of data.
Getting the data as XML is easy if we just use a DataSet as we can use a method on the DataSet to create it all for us:
myDataSet.WriteXml(myMemoryStream, XmlWriteMode.IgnoreSchema);
The way to get data to load into Excel is to:
- Format it as a CSV file (comma separated values), each record in a line of it's own, into a MemoryStream;
- Send the following two HTTP headers to the browser:
- Response.AddHeader("Content-Type", "application/vnd.ms-excel");
- Response.AddHeader("Content-disposition", ": csv; filename=document_" + Guid.NewGuid().ToString("N") + ".csv");
To compress the data, we take the MemoryStream we filled with data and examine the Headers sent by the client to see if it supports compression. This is held in a header called "Accept-Encoding". We are using the built-in Gzip compression that comes as part of the .NET 2.0 framework so we need to look for a value of "x-gzip" or "gzip" in this header. If we find it, then the client knows how to decompress our page if we send it Gzipp'ed. (This is means is true for all major browsers out in the last few years, by the way; many longer than that.)
If we don't find this header, then we will just send the page to the client as plain text as usual as this means the client can't understand gzip (or isn't prepared to!)
If the client accepts compressed pages, we have to tell the client the data it's getting is compressed so it knows to decompress it before displaying it to the user. We do this by adding another Header:
Response.AppendHeader("Content-Encoding", "gzip");
That's all we need to do. Now we just squash down the contents of our MemoryStream.
In the the complete method (enclosed as an attachment), we take in a MemoryStream full of data we want to send to our client browser (which is usually HTML of course but is also CSV data in our example), and return another MemoryStream we can send to the Client.
We then just send the MemoryStream to the browser:
Response.OutputStream.Write(memoryStream.ToArray(), 0, Convert.ToInt32(memoryStream.Length));
This will cause the browser to load the data into separate columns in Excel, right in the browser.
I enclose as an attachment to this post the source code of an ASPX page that demonstrates all of these concepts; assuming a DataSet of data and formatting it as CSV, HTML or XML and optionally compressing as shown in the method above. This page has no UI, and is intended to be called from a page that does with relevant QueryString parameters indicating the format of the desired report.