Exporting Data to Excel using ADO.Net

If you want to export some data into excel in ASP.Net without having Excel on your server a good way to do it is it use a GridView and ADO.Net to do this. There are a few little tricks/snags you can quickly hit, so I’m going to show a few I’ve found.

The basic code renders the content of the GridView to the response object with a content type of Excel:

Response.AddHeader("content-disposition", "inline;filename=book1.xls")
Response.ContentType = "application/ms-excel"

Dim sw As New StringWriter()
Dim htw As New HtmlTextWriter(sw)

This works a treat except for the following situations:

  1. Content you want to display as text
  2. Site running HTTPS
  3. text with spaces

My fixes for these are: 1. If you have cells containing text quite often it will be misrepresented e.g. 3/4 will be turned into a date when exported to Excel. The fix for this is to apply a class e.g. “text” to all cells in the GridView you want displayed as text and then apply a style to the response e.g.

Dim style As String = "<style> .text { mso-number-format:\@; } </style> "

2. Running with a site under https kept setting the content filename to the name of the aspx page. There’s a few extra settings to get this to work:

Response.CacheControl = "private"
Response.AppendHeader("Content-Length", location.ExportData.Length.ToString())
Response.Charset = System.Text.UTF8Encoding.UTF8.WebName
Response.ContentEncoding = System.Text.UTF8Encoding.UTF8
Response.AppendHeader("Pragma", "public")
Response.AppendHeader("Cache-Control", "max-age=0")

3. Content with spaces.  Quite a bit of content had multiple consecutive spaces that “disappeared” on render. Need to turn the “ “ into the &#160;

Technorati Tags: ,,,