Excel not opening .xls made from C# StringBuilder

Question

The following code was written several months ago and tested perfectly, with that funny little warning message in Excel. Now, though, after the C# code runs without errors and the browser downloads the exported .xls file, the Excel application opens but displays nothing, as if it didn't open the exported file. Can anyone see problems with this code or know why Excel suddenly wouldn't open this document? Many thanks in advance!

The C# Code:

[HttpGet]
public void DownloadReport()
{
    string filename = "ReportExport";

    //Report source data organized here

    StringBuilder sb = new StringBuilder();
    sb.Append("<table>");
    sb.Append("<tr>");
    foreach (DataColumn column in reportData.Columns)
    {
        sb.Append("<th>");
        sb.Append(column.ColumnName);
        sb.Append("</th>");
    }
    sb.Append("</tr>");
    foreach (DataRow row in reportData.Rows)
    {
        sb.Append("<tr>");
        foreach (DataColumn column in reportData.Columns)
        {
            sb.Append("<td>");
            if (row[column] == null)
            {
                sb.Append("");
            }
            else
            {
                sb.Append(row[column]);
            }
            sb.Append("</td>");
        }
        sb.Append("</tr>");
    }
    sb.Append("</table>");
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    string style = @"<style> .textmode { } </style>";
    Response.Write(style);
    Response.Output.Write(sb.ToString());
    Response.Flush();
    Response.End();
}

You are not generating a XLS file. The stated code generates a HTML fragment and tells the browser that this is a Excel file. Excel itself checks the data and cannot parse it (it is expecting binary data). Better use a Excel generator library. — Ralf Bönning, Aug 16 '16 at 18:46
@rboe - thank you for the response. This code was previously generating an Excel file and is based on the principles explained here: http://www.icodefor.net/2016/07/export-data-to-excel-sheet-in-asp-dot-net-c-sharp.html. The question is why would it work before and suddenly stop working? — jle, Aug 16 '16 at 18:50
this approach relies on the ability of Excel to discover automatically other formats (Text as HTML in this case) instead of the orginally format. I would not rely on that this autodiscovery feature will not change its behaviour over time. — Ralf Bönning, Aug 16 '16 at 18:56
Have you tried the earlier post [here](http://stackoverflow.com/questions/2937465/what-is-correct-content-type-for-excel-files) — Aishu, Aug 16 '16 at 19:40
@Aishu - thanks for the response. It looks like the correct content type is being used: "application/vnd.ms-excel" — jle, Aug 16 '16 at 19:51

score 0 · Answer 1 · edited May 23 '17 at 12:16

0

You are not generating a XLS file. The stated code generates a HTML fragment and tells the browser that this is a Excel file. Excel itself checks the data and cannot parse it (it is expecting binary data). Better use a Excel generator library.

Have a look at Create Excel (.XLS and .XLSX) file from C#

Personally I have made good experiences with NPOI and the Open XML SDK.

edited May 23 '17 at 12:16

Community

1
1

answered Aug 16 '16 at 18:49

Ralf Bönning

14,515
5
49
67

thank you for the response. This code was previously generating an Excel file and is based on the principles explained here: http://www.icodefor.net/2016/07/export-data-to-excel-sheet-in-asp-dot-net-c-sharp.html. The question is why would it work before and suddenly stop working? – jle Aug 16 '16 at 18:53

score 0 · Accepted Answer · answered Sep 08 '16 at 14:57

0

Adding Response.ClearHeaders() before setting the header seems to have done the trick. Perhaps the content type had been previously specified and needed to be reset.

answered Sep 08 '16 at 14:57

jle

269
8
25

Excel not opening .xls made from C# StringBuilder

2 Answers2