How to export two gridviews in same excel file on different worksheets?

Question

I have two gridviews on my page currently. I am able to export one gridview to the excel file. But for the second gridview I want it to open in another worksheet in the same file. Can anyone just help me get started and tell me how to go about it?

Here's what my code looks like:

protected void btnExport_Click(object sender, EventArgs e)
{
    GridView gridView = null;
    System.IO.StringWriter stringWrite = null;
    System.Web.UI.HtmlTextWriter htmlWrite = null;
    try
    {
        gridView = new GridView();
        List<string> columns = new List<string>();
        columns.Add("CREATE_AR_YN");
        columns.Add("GL_DEBIT_ACCT");
        columns.Add("ALLOC_METHOD");
        columns.Add("CUST_NAME");
        columns.Add("DEFAULT_PYMT_TERMS");
        columns.Add("AR_BILL_GROUP");

        BoundField bf = null;
        for (int i = 0; i < columns.Count; i++)
        {
            bf = new BoundField();
            bf.DataField = columns[i];
            bf.HeaderText = columns[i];
            bf.HeaderStyle.BackColor = System.Drawing.Color.FromName("#81DAF5");
            gridView.Columns.Add(bf);
            gridView.AutoGenerateColumns = false;
        }

        List<FMAProfile> custList = GetSelectedCustomerProfile();
        gridView.DataSource = custList;
        gridView.DataBind();
        Response.Clear();
        Response.ClearHeaders();
        Response.Cache.SetCacheability(HttpCacheability.Private);
        Response.AddHeader("content-disposition", "attachment;filename=" + "CustomerProfile" + ".xls");
        Response.Charset = "";
        //Response.ContentType = "application/vnd.xls";
        Response.ContentType = "application/vnd.ms-excel";
        //Response.Buffer = false;
        stringWrite = new System.IO.StringWriter();
        htmlWrite = new HtmlTextWriter(stringWrite);
        gridView.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }
    catch (Exception ex)
    {
        if (ex.Message.Trim().IndexOf("Thread was being aborted") == -1)
        {
            Log(ex.ToString());
        }
    }
    finally
    {
        if (gridView != null)
        {
           gridView.Dispose();
           gridView = null;
        }
        if (stringWrite != null)
        {
            stringWrite.Dispose();
        }
        stringWrite = null;
        if (htmlWrite != null)
        {
            htmlWrite.Dispose();
        }
        htmlWrite = null;
    }
}

What kind of GridViews are these? .NET, Infragistics? Could you post a code example please? — Ian R. O'Brien, Dec 11 '12 at 20:10
This may be a duplicate: http://stackoverflow.com/questions/7632782/export-gridview-to-multiple-excel-sheet — Ian R. O'Brien, Dec 11 '12 at 20:22
@IanO'Brien From the code above I suspect this is ASP.NET and Office Interop is NOT supported in that context (according to MS). — Yahia, Dec 11 '12 at 20:28
its coded in c# .net2.0 . do u guys have any leads ??? and @lanO'Brien I did look at that thread before but didn't get much out of it thats why i asked it here. Thanks. — HereToLearn_, Dec 11 '12 at 20:33

score 1 · Answer 1 · answered Dec 11 '12 at 20:32

You are not exporting a real XLS but basically an HTML which Excel does/can import...

For some in-depth information regarding the HTML approach to creating Excel files with sample code see http://www.c-sharpcorner.com/UploadFile/kaushikborah28/79Nick08302007171404PM/79Nick.aspx

Also checkout the official documentation at http://msdn.microsoft.com/en-us/library/Aa155477%28office.10%29.aspx

I am not really sure that you can create multiple worksheets in HTML...

Options to create real Excel files:

MS provides the OpenXML SDK V 2.0 (free) - see http://msdn.microsoft.com/en-us/library/bb448854%28office.14%29.aspx

This can read+write MS Office files (including Excel).

Another option see http://www.codeproject.com/KB/office/OpenXML.aspx

IF you need more like rendering, formulas etc. then there are different free and commercial libraries like ClosedXML, EPPlus, Aspose.Cells, SpreadsheetGear, LibXL and Flexcel etc.

BEWARE: Office Interop is not supported in ASP.NET (which seems to be what your project is built in).

How to export two gridviews in same excel file on different worksheets?

1 Answers1