Export GridView to multiple Excel sheet

Question

I have two Gridview in my Web application.I need ,while clicking the (ExcelExpot) button the values to be Export in Excel Accordingly Sheet1 and Sheet2.

  protected void ExportToExcel()
    {

        this.GridView1.EditIndex = -1;
        Response.Clear();
        Response.Buffer = true;
        string connectionString = (string)ConfigurationSettings.AppSettings["ConnectionString"];
        SqlConnection sqlconnection = new SqlConnection(connectionString);
        String sqlSelect = "select * from login";
        sqlconnection.Open();
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(sqlSelect, connectionString);
        //DataTable dt1
        DataTable dt1 =new DataTable();
        mySqlDataAdapter.Fill(dt1);

        //LinQ Query for dt2
        var query = (from c in dt.AsEnumerable()
        select new {id= c.Field<string>("id"),name=c.Field<string>("name"),city=c.Field<string>("city")}) ;
        DataTable dt2 = new DataTable();
        d2=query.CopyToDatatable();

        DataSet ds=new DataSet();
        ds.Tabls.Add(dt1);
        ds.Tabls.Add(dt2);
        Excel.Application excelHandle1 = PrepareForExport(ds);
        excelHandle1.Visible = true;

    } 
  // code for PrepareForExport(ds);
         PrepareForExport(ds)
             {

                two tables in two worksheets of Excel...

              }

maybe duplicate: http://stackoverflow.com/questions/2452465/export-gridview-to-excel — Massimiliano Peluso, Oct 03 '11 at 08:48
aned what is the rule? some records on Sheet1 and some other on Sheet2 or is it ok for you to put all records in 1 sheet then use Excel Interop to clone that sheet? — Davide Piras, Oct 03 '11 at 08:59
the Gridviews are having,different data not possible to combine. — Jay, Oct 03 '11 at 09:06

Icarus · Answer 1 · 2011-10-11T15:23:50.897

Doing this with EPPlus is a piece of cake. No Interop assemblies required and literally 2 lines of code do all the magic:

ws.Cells["A1"].LoadFromDataTable(dt1, true);
ws2.Cells["A1"].LoadFromDataTable(dt2, true);

Complete code:

protected void ExportExcel_Click(object sender, EventArgs e)
{

     //LinQ Query for dt2
    var query = (from c in dt.AsEnumerable()
    select new {id= c.Field<string>("id"),name=c.Field<string>("name"),city=c.Field<string>("city")}) ;
    DataTable dt2 = new DataTable();
    dt2=query.CopyToDatatable();

    //DataTable dt1
    DataTable dt1 =new DataTable();
    mySqlDataAdapter.Fill(dt1);

    using (ExcelPackage pck = new ExcelPackage())
    {
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Page 1");
        ExcelWorksheet ws2 = pck.Workbook.Worksheets.Add("Page 2");

        ws.Cells["A1"].LoadFromDataTable(dt1, true);
        ws2.Cells["A1"].LoadFromDataTable(dt2, true);

        //Write it back to the client
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
        Response.BinaryWrite(pck.GetAsByteArray());
        Response.Flush();
        Response.End();
    }
}

Note that I copied and paste it your code to gather the data. I expect these lines to produce a DataTable.

score 4 · Accepted Answer · answered Oct 12 '11 at 10:54

I agree with @Andrew Burgess and have implemented his code into one of my projects. Just for the record theres a few small errors in the code which will cause some COM Exceptions. The corrected code is below (the issue was that Excel numbers sheets, rows, columns from 1 to n not from zero).

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

        //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();

Andrew Burgess · Answer 3 · 2011-10-11T12:51:01.873

You'll need to create the workbook, add more sheets if needed (defaults with three), and then fill out the cells.

Top of the file:

using Excel=Microsoft.Office.Interop.Excel;

And then the main code for generating the Excel file

Excel.Application excel = new 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);
    }

    var currentSheet = (Excel._Worksheet)workbook.Sheets[i];
    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, x] = dataset.Tables[i].Rows[y].ItemArray[x];
        }
    }
}

workbook.SaveAs("C:\\Temp\\book.xlsx", Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing);

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

Response.WriteFile(C:\\Temp\\book.xlsx");

Don't know exactly if this will work, but it should get you in the right direction

(also: Type.Missing and Missing.Value come from the namespace System.Reflection, just FYI)

+1, you might want to include the fact that you have to have `using Excel = Microsoft.Office.Interop.Excel;` for this to work. I know the OP probably knows this, but just to be completely clear for anyone who sees this in the future =) — Josh Darnell, Oct 10 '11 at 20:34

score 2 · Answer 4 · answered Oct 11 '11 at 14:15

Rather than using some 3rd party library or the Excel automation (with it's added overhead) you can just use ADO.NET.

http://support.microsoft.com/kb/316934#10

You would simply use the T-SQL your used to with OleDbCommand objects.

CREATE TABLE Sheet1 (id INT, name char(255))
CREATE TABLE Sheet2 (id INT, name char(255))
// for inserts use a parameterized command with ?'s
INSERT INTO Sheet1 (id, name) VALUES(?, ?)
INSERT INTO Sheet1 (id, name) VALUES(?, ?)

You'd create your temp excel file using the Path.GetTempFileName and output it, after which you can delete the temp file.

Export GridView to multiple Excel sheet

4 Answers4

Linked