0

I have a program with an option to write db contents to an excel spreadsheet. This functionality is contained on a page called 'Search_aspx'.

Response.ClearContent()
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment; filename=FileName.xls")
Response.Cache.SetCacheability(HttpCacheability.Private)
Dim dg As New DataGrid
dg.DataSource = dtResults
dg.DataBind()
Dim sw As New System.IO.StringWriter()
Dim htw As New HtmlTextWriter(sw)
dg.RenderControl(htw)
Response.Write(sw.ToString)
Response.Flush()
Response.Close()

This worked fine forever, but last week I migrated the solution to a new server (Server03 to Server08), and now it is not working. When I try to save, it asks if I want to "Save search_aspx" instead of "Save FileName.xls".

What is the issue here?

EDIT:

All righty... I took the advice posted below and followed Mason's suggestion, and the page still asks if I want to save 'Search_aspx'. Now have EPPlus installed in my solution, properly referenced etc....

Response.ClearContent()
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("Content-Disposition", "attachment; filename=FileName.xlsx")
Dim package As New ExcelPackage()
Dim sheet = package.Workbook.Worksheets.Add("Data")
sheet.Cells.LoadFromDataTable(dtResults, False)
Response.BinaryWrite(package.GetAsByteArray())
Response.Flush()
Response.Close()
mrwienerdog
  • 815
  • 3
  • 18
  • 35

1 Answers1

1

The real issue is you aren't creating an XLS file. You're creating an HTML file with a .xls extension. I discuss several reasons why that's a bad idea and go into detail about what you can do instead on my blog.

The short and sweet version is that there's no good way practice to generate a .xls file in an ASP.NET environment, and you should either generate .xlsx files with a decent library (discussed on my blog) or switch to another data format such as CSV.

mason
  • 31,774
  • 10
  • 77
  • 121
  • Completely understand... However, given our requirements, I need a 'quick and dirty' method of creating an xls. I do understand that it's not a 'true' xls, however the users only require something in that format. When using propre libraries, the process is far too long comparatively. – mrwienerdog Mar 13 '15 at 16:07
  • @mrwienerdog Can't the users use .xlsx files? Every version of Office since Office 2003 and Office XP supports it. And no, the process is not "too long". It's quite easy, as my blog demonstrates. – mason Mar 13 '15 at 16:08
  • Thanks, sir... I took your advice, and installed EPPlus. Made an edit above, but TL;DR: it's still be-buggered. – mrwienerdog Mar 13 '15 at 18:15
  • @mrwienerdog Great, I like your code now. Try wrapping the file name in quotes, ex: `Response.AddHeader("Content-Disposition", "attachment; filename=\"FileName.xlsx\"")` (or whatever VB code is for escaped double quotes). – mason Mar 13 '15 at 19:38
  • Still getting the same issue... Poop. Thanks for trying to help me through this, by the way... Appreciate it. – mrwienerdog Mar 13 '15 at 19:42
  • @mrwienerdog Happening for all browsers? Or just specific ones? – mason Mar 13 '15 at 19:43
  • Boo.... It's been a while since I've had to bother with other browsers, unfortunately we're a business institution and it's all IE (10 in this case). Stupid f(&*%&ing IE... Is there any way in dumb old IE? It's just fine in Chrome and FF. Found another SO link that mentions using an asp button instead of link and just use the onclick of the button.... Gonna give that a try. – mrwienerdog Mar 13 '15 at 19:46
  • When you press F12 and look at the developer tools in IE 10, what is the browser mode and what is the document mode? – mason Mar 13 '15 at 20:04
  • @mrwienerdog Try doing a call to `Response.ClearHeaders()` before you add your headers. – mason Mar 13 '15 at 20:21
  • Unfortunately it's still the same. Thank God I'm off in five minutes. – mrwienerdog Mar 13 '15 at 20:25