How do I export 2 gridviews into 2 separate sheets in one MS Excel file?

Question

How do I export 2 gridviews (GridView1 & GridView2) into 2 separate sheets in one MS Excel file with the click of 1 button? Currently, I'm able to export only 1 gridview to an Excel sheet which the filename is the same as the sheet name. But I would like to 2 gridviews into 2 separate sheets which I would like sheet name to be define/set by myself. Thanks

public void ExportGridToExcel()
{
      Response.Clear();
      Response.Buffer = true;
      Response.ClearContent();
      Response.ClearHeaders();
      Response.Charset = "";

      string FileName ="Export"+DateTime.Now+".xls";

      StringWriter strwritter = new StringWriter();
      HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);

      Response.Cache.SetCacheability(HttpCacheability.NoCache);
      Response.ContentType = "application/vnd.ms-excel";
      Response.AddHeader("Content-Disposition","attachment;filename=" + FileName);

      GridView1.GridLines = GridLines.Both;
      GridView1.HeaderStyle.Font.Bold = true;
      GridView1.RenderControl(htmltextwrtter);

      Response.Write(strwritter.ToString());
      Response.End();
}

Answer 1

//Edit my answer since there is only 1 export button: Take a look at this question:

firstly add these namespace:

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

So basically you should have (or go ahead and make them) 2 DataTable (to bind to 2 gridviews): dt1 and dt2

Make a dataset and add 2 datatables to it

DataSet dataset = new DataSet();
        dataset.Tables.Add(dt1);
        dataset.Tables.Add(dt2);

then

//Print using Ofice InterOp
    Excel.Application excel = new Excel.Application();

    var workbook = (Excel._Workbook)(excel.Workbooks.Add(Missing.Value));

    for (var i = 0; i < dataset.Tables.Count; i++)
    {

            if (workbook.Sheets.Count <= i)
            {
                workbook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing,
                                    Type.Missing);
            }

            //NOTE: Excel numbering goes from 1 to n
            var currentSheet = (Excel._Worksheet)workbook.Sheets[i + 1]; 

            for (var y = 0; y < dataset.Tables[i].Rows.Count; y++)
            {
                for (var x = 0; x < dataset.Tables[i].Rows[y].ItemArray.Count(); x++)
                {
                    currentSheet.Cells[y+1, x+1] = dataset.Tables[i].Rows[y].ItemArray[x];
                }
            }
    }

    string outfile = @"C:\APP_OUTPUT\EXCEL_TEST.xlsx";

    workbook.SaveAs( outfile, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing);

    workbook.Close();
    excel.Quit();

to change worksheet name, I believe you can do:

currentSheet.Name = "your sheet name"

Answer 2

you need to modifiy your code so that you will create a worksheet in the excel file and manually copy the data from grid to excel file and create this code as a function that takes grid,sheetname and sheetid like 1 ,2 and so on as arguments and then within button click event call it twice once for grid1 and once for grid2 with different sheetnames and sheet ids 1 and 2 respectively. the code for the function is as follows.

Write these two line of code in button click event and after that call the function twice once for each grid by passing excel app and workbook also as arguments.

     // creating Excel Application
     Microsoft.Office.Interop.Excel._Application app  = new Microsoft.Office.Interop.Excel.Application();

    // creating new WorkBook within Excel application
    Microsoft.Office.Interop.Excel._Workbook workbook =  app.Workbooks.Add(Type.Missing);

 public void ExportToExcel(Microsoft.Office.Interop.Excel._Application app, Microsoft.Office.Interop.Excel._Workbook workbook,GridView gridview,string SheetName,int sheetid)
 {  


        // creating new Excelsheet in workbook
         Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                   

       // see the excel sheet behind the program
        app.Visible = true;

       // get the reference of first sheet. By default its name is Sheet1.
       // store its reference to worksheet
        worksheet = workbook.Sheets["Sheet"+ sheetid];
        worksheet = workbook.ActiveSheet;

        // changing the name of active sheet
        worksheet.Name = sheetname; 

        // storing header part in Excel
        for(int i=1;i<gridview.Columns.Count+1;i++)
        {
              worksheet.Cells[1, i] = gridview.Columns[i-1].HeaderText;
        }



        // storing Each row and column value to excel sheet
        for (int i=0; i < gridview.Rows.Count-1 ; i++)
        {
            for(int j=0;j<gridview.Columns.Count;j++)
            {
                worksheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Value.ToString();
            }
        }


        // save the application
        workbook.SaveAs("c:\\output.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        // Exit from the application
      app.Quit();
    }