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