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.ClearContent() Response.AddHeader("content-disposition", "inline;filename=book1.xls") Response.ContentType = "application/ms-excel" Dim sw As New StringWriter() Dim htw As New HtmlTextWriter(sw) GridView1.RenderControl(htw) Response.Write(sw.ToString()) Response.End()
This works a treat except for the following situations:
- Content you want to display as text
- Site running HTTPS
- 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> " Response.Write(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.Cache.SetCacheability(HttpCacheability.Private) 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