Writing table contents to xls from vb.net

Question

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()

Answer 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.