I am exporting content of a grid in my application through a function provided by that grid which ouputs in xml. I send that data to server and the from server I change the response as:


Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "filename=Report.xls;attachment");

This gives user an option to either open excel file directly or save it. If user opens it directly, without saving the file first. Excel opens up with a message:
"The file you are trying to open, 'Report[1].xls', is in 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?"
options provided by message box: [Yes] | [No] | [Help].

• Why does excel think that format is not right, where, if I open file after saving it, I don't see this error?

After I click on [Yes] button in message box, file opens up fine, headers with filter options.
Now, if I try to insert "Pivot" on this spread sheet. By selecting few cells and rows with/without headers and then selecting "Insert"->"Pivot" as soon I press [Ok] on Dialogbox to confrim my Pivoting, I see this error:
"Datasource reference is not valid", but If I save this file on desktop and reopen it then pivoting works all fine:

• Any clue what would cause this error?