This is my first question on this forum, so please forgive any newbie errors.
I have .Net code, within a web application, that creates an html table dynamically from data pulled from a database, then streams it to the user's web browser to be opened in Excel. This works just fine in Chrome. It also works in IE 11 as long as the user, after clicking the "Download" link, chooses the "Save" option (saving the file before opening it), rather than the "Open" option - which tries to read the file being streamed to the client. But in IE 11, if they choose the "Open" option, they will get a warning from Excel about the format of the file (because it is an html file - same thing happens if they save first, and this is acceptable), but when they click "Yes" to open anyway, they see an "Unable to read file." error message.
In one case, I was able to fix this problem by replacing instances if "&" in the data with the word "and". However, in other cases, I cannot determine what particular data value might be causing the issue. And again, the file opens just fine as long as the user saves it first. (In fact, it even works fine when I click "Open" instead of "Save" in my development environment - but not when I do it from the test server where the code is deployed.)
So, I am looking either for a way to remove/replace all problematic data displayed within the "td" elements of the html table, or, better yet, some way to just make the "Open" option have the same result as the "Save" option. Again, the html table content opens just fine as long as the file is saved first.
The html table is generated by a C# class, but the page that streams the file is in VB.Net. I don't think the C# code would be useful (but I can post it in part if anyone thinks it would be), but here is the VB code in relevant part:
Response.Clear()
Response.ClearContent()
Response.ClearHeaders()
Dim htmlTable As String
'[snip - populate htmlTable with data from database]
Dim fileName As String = "Whatever.xls"
Response.Buffer = false
Response.ContentType = "application/vnd.ms-excel"
Response.AppendHeader("Content-Disposition", "attachment; filename=""" & fileName & """")
Response.AddHeader("Content-Length", ASCIIEncoding.ASCII.GetByteCount(htmlTable).ToString())
Using ms As New MemoryStream(Encoding.ASCII.GetBytes(htmlTable))
ms.WriteTo(Response.OutputStream)
Response.Flush()
End Using
UPDATE: I may have fixed this issue by "cleansing" the data with the (C#) code below, but I still don't understand why the file ever has to be saved first, which would have made this unnecessary. Any answer would be welcome.
private string GetDataRow(DataRow dr, List<DataColumnInfo> fieldInfoList)
{
var sb = new StringBuilder("<tr valign='top' align='left'>");
// see http://stackoverflow.com/questions/4619909/format-html-table-cell-so-that-excel-formats-as-text
var forceExcelText = "mso-number-format:\"\\@'\"";
fieldInfoList.ForEach(c =>
{
var content = dr[c.FieldName].ToString()
.Replace("&", "and")
.Replace("<", "")
.Replace(">", "");
// replace all non-asccii
content = Encoding.ASCII.GetString(Encoding.ASCII.GetBytes(dr[c.FieldName].ToString()));
sb.Append("<td style='" + forceExcelText + "'>" + content + "</td>");
});
sb.Append("</tr>");
return sb.ToString();
}
NEW UPDATE - the above code did not fix the problem. Still get error in some cases when choose "Open" option instead of "Save" first. If I save first, all is fine.
