CODEWITHSUNDEEP
c#excelxls
Rob
Rob

Reputation: 3574

Exporting Gridview to Excel - External table is not in the expected format

This is my code, but I end up with an Unexpected format error. Something must be wrong with the MIME-type, however I found this one as being te original official MIME-type.

What am I missing? Each time I try to open any document I create by this method seems to be "corrupt", but when I click the message away everything works fine.

Message: The file you are trying to open, 'name.ext', 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?

However, I need to get rid of this warning, since I cannot import the file because it's invalid. If I save the file as 2003-2007 format .xls, it seems to be fixed. But this is not a working solution.

   protected void ExportToExcel(string fileName)
    {
        var cmd = new SqlCommand(query);
        var dt = GetData(cmd);

        var writer = new StringWriter();

        var gridview = new GridView();
        gridview.AllowPaging = false;
        gridview.DataSource = dt;
        gridview.DataBind();

        Response.Clear();

        var hw = new HtmlTextWriter(writer);
        gridview.RenderControl(hw);
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        //  this.EnableViewState = false;

        Response.Write(writer.ToString());
        //    Response.Flush();
        Response.End();
    }

Upvotes: 0

Views: 775

Answers (2)

Dan
Dan

Reputation: 11

Thought I'd add a comment for future reference. One of my macros that was doing a TransferSpreadsheet (export) started failing, giving the "External table is not in the expected format." error window. I went to the path of the destination file and opened it. Found that the last file created contained 'garbage' characters. I deleted the Excel file and ran my macro again with no issue.

Upvotes: 1

Antonio Bakula
Antonio Bakula

Reputation: 20693

To be direct, you are not doing excel export. You are sending html table with fake response headers to trick the browser to open this content with excel.

That can fail in many ways, in fact can be interpreded as malicious behaviour. I think that going this way is only calling for troubles.

Better way is to use native excel library and do export with it. Here is the example of exporting DataTable to real excel file (2007+)

https://stackoverflow.com/a/9569827/351383

Upvotes: 2

Related Questions