How do you generate an Excel 2007 file in ASP.NET without getting a warning message?

Question

Whenever I open an xls file I generated on my ASP.NET page with Excel 2007, I get the following error:

The file you are trying to open, 'filename.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

If I click 'Yes', everything looks fine, but it's annoying to our customers to have to click through it each time. If they use 2003, no problem, but on 2007 it gives this error. Here's the code I use:

string style = @"<style> .text { mso-number-format: \@ } </style> ";

Response.Clear();
Response.AddHeader("content-disposition", string.Format("attachment;filename=OperationsReport_Rejects_{0}.xls", DateTime.Today.ToString("yyyyMMdd")));
Response.Charset = "";
Response.ContentType = "application/vnd.xls";

StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

reportOperationsRejectsGridView.RenderControl(htmlWrite);
Response.Write(style);
Response.Write(stringWrite.ToString());
Response.End();

Does anyone know what is causing it to behave differently on 2003 and 2007?

Answer 1

Excel 2003 and 2007 use different formats, unless the user specifically does a "Save As" and chooses a backwardly-compatible format.

A quick look in a hex viewer or hex editor will show you the large difference in the file formats.

Answer 2

Office 2007 uses XML based file formats that are different from the previous binary file formats.

What Excel 2007 saves is not an .xls file, but an .xlsx file.

What you are creating is actually neither. It's an HTML export of an excel file. Some versions will seamlessly import the HTML as an excel document, some versions will warn you that it's actually not an Excel document, before importing it.

If you create an actual .xls file, Excel 2007 will open it in compatibility mode without complaining.

Answer 3

SpreadsheetGear for .NET can save as real xls or xlsx workbooks - avoiding this error message.

You can see a number of live samples on our Excel Reporting Samples page here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC