Excel 2008 Cant Parse HTML

Question

I need to export a gridview to excel, I put the return html code from the gridview to a HtmlTextWriter and put this into the response.

The result file work fine in excel, excel can parse the html and the result is readable, work perfect on excel 2003 and 2007, but in some machines with Excel 2008 (MACOS) excel shows only the raw html code and can't process this html code.

Any idea to configure excel?

This is the code to convert:

public static void ToExcel(GridView gridView, string fileName)
{
    HttpResponse response = HttpContext.Current.Response;
    response.Clear();
    response.Buffer = true;

    fileName = fileName.Replace(".xls", string.Empty) + ".xls";

    response.AddHeader("content-disposition",
                       "attachment;filename=" + fileName);
    response.Charset = "";
    response.ContentEncoding = Encoding.Unicode;
    response.BinaryWrite(Encoding.Unicode.GetPreamble());
    response.ContentType = MimeTypes.GetContentType(fileName);

    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
    gridView.AllowPaging = false;
    //gridView.DataBind();

    //Change the Header Row back to white color
    gridView.HeaderRow.Style.Add("background-color", "#FFFFFF");

    //Apply style to Individual Cells
    for (int i = 0; i < gridView.HeaderRow.Cells.Count; i++)
    {
        gridView.HeaderRow.Cells[i].Style.Add("background-color", "yellow");    
    }

    for (int i = 0; i < gridView.Rows.Count; i++)
    {
        GridViewRow row = gridView.Rows[i];

        //Change Color back to white
        row.BackColor = System.Drawing.Color.White;

        //Apply text style to each Row
        row.Attributes.Add("class", "textmode");

        //Apply style to Individual Cells of Alternating Row
        if (i % 2 != 0)
        {
            for (int j = 0; j < row.Cells.Count; j++)
            {
                row.Cells[j].Style.Add("background-color", "#C2D69B");
            }
        }
    }

    gridView.RenderControl(hw);

    //style to format numbers to string
    string style = @"<style> .textmode { mso-number-format:\@; } </style>";
    response.Write(style);
    response.Output.Write(sw.ToString());
    response.Flush();
    response.End(); 
}

Answer 1

Hmm, you could do something like...

        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=report.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        var stringWrite = new System.IO.StringWriter();
        var htmlWrite = new HtmlTextWriter(stringWrite);
        this.gridView.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();

Answer 2

Excel 2008 for Mac does not have VBA. I don't know how C# add-ins (I presume that is what this is) run if there is no VBA environment. I know for my VBA files and add-ins there is nothing that can be done.

Answer 3

In general, relying on Excel parsing your Html is very fragile. As you have discovered, the Mac version of Excel can have troubles with it. Instead, you should consider using Spreadsheet XML or an engine like NPOI to build an actual Excel file.

With either an Html document or an Xml document, Excel will throw a warning to the user on download from the web that the file is not of the same type expected (e.g., you have an Html file claiming to be an Excel file based on the content-type.). If you build an actual Excel file, you won't have the compatibility problems nor will users get this warning.

Spreadsheet XML

NPOI