0

Here's the export piece of my code:

private void ExportGridView()
{
    // Exports the data in the GridView to Excel
    // First, fill the datagrid with the results of the session variable
    DataTable gridDataSource = (DataTable)Session["SSRegisterSplit"];

    DataGrid dgGrid = new DataGrid();
    dgGrid.DataSource = gridDataSource;
    dgGrid.DataBind();

    // Exports the data in the GridView to Excel
    string attachment = "attachment; filename=Claim_Details_" + LPI_ID + ".xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    dgGrid.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.End();
}

When it exports, it shows up in my footer automatically with an option to Open or Save.

If I choose "Open", Excel launches and then I get an error box:

The file you are trying to open, 'Claim_Details_1586.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 choose 'Yes', it opens the file but not all the records are in it.

Any ideas on what's happening/how to fix it?

EDIT:

Putting a break point in the function, I noticed that when it gets to Response.End(); it throws the error:

Thread Was being Aborted.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Its microsofts bug, on msdn you can find topic about this. I solved this problem using ClosedXML – kgzdev Jan 26 '17 at 17:46
  • Start using a specialized library for creating Excel files, like [EPPlus](http://stackoverflow.com/documentation/epplus/drafts/98280) for example. All you are doing now is creating a HTML page with an .xls extension. – VDWWD Jan 26 '17 at 19:16

1 Answers1

0

I think this is what you want: Is Response.End() considered harmful?

Don't use Response.End() Consider using: 

Response.Flush()
Response.SuppressContent = True
HttpContext.Current.ApplicationInstance.CompleteRequest()

—————————————————————————————

However. I'm pretty sure that the ”save an html file with .XLS extension” will always result in Excel showing an error.

The alternative is to pick something from http://nugetmusthaves.com/Tag/Excel to create a proper xls file. I've used EPPlus before but this page suggests that ClosedXML will do it for you in 5 lines.

If you aren't familiar with installing NuGet packages, then start here: https://learn.microsoft.com/en-gb/nuget/tools/package-manager-ui

Community
  • 1
  • 1
Chris F Carroll
  • 11,146
  • 3
  • 53
  • 61
  • Weird. I no longer get the Response.End issue, but it still says it's in a different format than specified. But it opens. I hate to have an error like that (which I can't control) display to my users, even though it appears to not effect them. – Johnny Bones Jan 26 '17 at 18:09
  • Does ContentType ="application/vnd.ms-excel" fare any better? – Chris F Carroll Jan 26 '17 at 18:14
  • Just tried that. No impact. I get the full dataset exported into the Excel file, it will open fine if I click through that error message, but the fact that the error message shows up is going to confuse the users. – Johnny Bones Jan 26 '17 at 18:20
  • I added a bit. I used a text editor to create an html file with a table in it, and Excel behaves as you describe. So that issue is not asp.net related. What you need is a 'proper' tool for creating an excel spreadsheet. Which might only take 10-20 mins to get to grips with. – Chris F Carroll Jan 26 '17 at 18:27