How to speed up export of .NET DataGrid to Excel?

Question

I am using the folowing function to export the information from my DataGrid to Excel. But when it asks me if I want to Save or Export, it takes too long. Anybody knows why/anybody got similar experiences?

void ExportToExcel3()
{
    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");

    Response.Charset = "";
    Response.ContentType = "application/vnd.xls";
    System.IO.StringWriter stringWrite = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    GridView2.RenderControl(htmlWrite);
    Response.Write(stringWrite.ToString());
    Response.End();
}

Answer 1

When looking at the performance of your solution, it's important to realize that you're not actually exporting your DataGrid to Excel: you're in fact writing its contents as HTML, and then 'tricking' Windows into opening that HTML using Excel. Excel will then convert the HTML table to a worksheet as well as it can.

The bottleneck here is most likely Excel's conversion process: especially on large tables, parsing the HTML is a lot of work, involving lots of slow guesswork about datatypes and conversions. Possibly, the HTML is even so unwieldy that downloading it from the server to the client introduces a noticeable delay as well.

Since you don't control the code involved with either of those bottlenecks, there's not much you can do to eliminate them, other than by avoiding them altogether by switching to a more efficient format, i.e. native Excel instead of HTML.

There are several .NET libraries that can help you with this, both commercial as well as free. Googling around will most likely find lots of other alternatives as well. Please do note that your server-side code will become significantly more involved: you'll have to set up the spreadsheet structure/formatting, and loop through the DataGrid to export each row. But the end-result, the XLS file, will be more compact and efficient for it, and will also be more useful (i.e. editable) for your users than your current HTML export.