Export Data from ASP.NET web page to Excel in multiple sheets

Question

I am working on a web application which consists of some reports. These report is generated on a location wise data

The report should have a feature to be extracted to an excel file. For which I use render control.

private void ExprotToExcel()
    {
        Response.Clear();

Response.AddHeader("content-disposition", "attachment;filename=ExportData.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";

        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        mainReport.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();

    }

The code works fine. Now the problem is that the user should also be able to export the report based on multiple locations. What I mean is that suppose there are 2 locations for which the report needs to be extracted. The report should be exported to the excel file directly in multiple sheets on the same workbook. I can use the above code to do that but I need multiple sheets and not multiple workbook.

Can anyone please guide me on the right path?

Edit : Forgot to mention that I have used multiple gridviews, data view and label controls on the webpage.

Answer 1

You can use a library for this:

http://epplus.codeplex.com/

Beware of NOT use Office Interop libraries in a web environment.

http://support.microsoft.com/kb/257757