"The file you are trying to open, '[filename]', is in a different format" Excel Error

On a recent project, the client asked me to allow some reports to be exported to Excel (XLS.) I've had such a request 1000 times over the years and quickly went to work rendering the grid as HTML, sending its HTML as a response and setting the Response.Header to the Excel MIME type... Simple.

Or so I thought. Turns out that Office 2007 doesn't like that much. When you open such a spreadsheet in Excel 2007 you get an error like:

"The file you are trying to open, '[filename]', 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?"
(Yes | No | Help)

After searching for hours, I finally ran into this document from MS that essentially says this is a "feature" of the new Excel and no matter how much everyone hates it, they won't fix it. Here's an excerpt from that document:

"The current design does not allow you to open HTML content from a web site in Excel... So ASP pages that return HTML and set the MIME type to something like XLS to try to force the HTML to open in Excel instead of the web browser (as expected) will always get the security alert... If you use an HTML MIME type, then the web browser will open the content instead of Excel. So there is no good workaround for this case because of the lack of a special MIME type for HTML/MHTML that is Excel specific. You can add your own MIME type if you control both the web server and the client desktops that need access to it, but otherwise the best option is to use a different file format or alert your users of the warning and tell them to select Yes to the dialog." [Emphasis added]

In other words, give up because there's no good solution. Here's some bad solutions you could try, though:

• Display a message that says something like, "If you are using Office 2007, please select "Yes" from the resulting dialog."
• Include a registry script that the user can optionally run to change their HKCU\Software\Microsoft\Office\12.0\Excel\Security\ExtensionHardening DWord to 0, disabling this useless prompt (more details.)
• Only export to CSV, not Excel. Sure, it's not as pretty but it'll work.
• Instead of using this much simpler spreadsheet generation method, instead open a template spreadsheet on the server as a data-source, write to it (using SQL), and save it with a unique file name for the user to download. Of course you'll then have to do stuff like ensuring your saved files have unique file names (perhaps using GUID) and deleting the old spreadsheets from the File System.
  

VSOfficeDeveloper: Known Problems, Bugs, and Fixes : Excel 2007 Extension Warning On Opening Excel Workbook from a Web Site