Exporting datasets into multiple excel sheets of one excel file

Question

I need to export two dataset's values in two excel sheets of the same workbook. My Query is like:

//Dataset One:

        DataSet ds1 = new DataSet();
        SqlCommand commandOpen = new SqlCommand("storedproc1", conSql);
        commandOpen.CommandType = CommandType.StoredProcedure;
        var adaptOpen = new SqlDataAdapter();
        adaptOpen.SelectCommand = commandOpen;
        adaptOpen.Fill(ds1, "table");

//Dataset Two:

        DataSet ds2 = new DataSet();
        SqlCommand commandOpen = new SqlCommand("storedproc2", conSql);
        commandOpen.CommandType = CommandType.StoredProcedure;
        var adaptOpen = new SqlDataAdapter();
        adaptOpen.SelectCommand = commandOpen;
        adaptOpen.Fill(ds2, "table");

Now to create one Excel workbook I have used:

        ExcelLibrary.DataSetHelper.CreateWorkbook("C:/New Folder/file1.xls", ds1);

But instead of this, in the same workbook I want to add two sheets; one for ds1 and another for ds2. How to do that? Thanks.

Answer 1

Possible duplicate of: how to add dataset to worksheet using Excellibrary

In the answer to that question it is shown how to export a dataset to an excel file, but each table in dataset will have it's own sheet. You can modify the code for your needs.

Code from the other post:

public static void CreateWorkbook(String filePath, DataSet dataset)
    {
        if (dataset.Tables.Count == 0)
            throw new ArgumentException("DataSet needs to have at least one DataTable", "dataset");

        Workbook workbook = new Workbook();
        foreach (DataTable dt in dataset.Tables)
        {
            Worksheet worksheet = new Worksheet(dt.TableName);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                // Add column header
                worksheet.Cells[0, i] = new Cell(dt.Columns[i].ColumnName);

                // Populate row data
                for (int j = 0; j < dt.Rows.Count; j++)
                    worksheet.Cells[j + 1, i] = new Cell(dt.Rows[j][i]);
            }
            workbook.Worksheets.Add(worksheet);
        }
        workbook.Save(filePath);
    }

Answer 2

Export DataSet to Excel With Multiple Sheets, you can use the following codes. First, you have to install "ClosedXML" NuGet Package in your application.

public DataSet GetDataSetExportToExcel()
    {
        DataSet ds = new DataSet();
        var SPNames = new List<string>() { "storedproc1", "storedproc2" };

        foreach (var SPName in SPNames)
        {
            DataTable dt = new DataTable();
            dt = GetDataTableExportToExcel(SPName);
            ds.Tables.Add(dt);
        }

        return ds;
    }

    private DataTable GetDataTableExportToExcel(string SPName)
    {
        DataTable dt = new DataTable();

        using (SqlConnection con = new SqlConnection(connectionString))
        {
            using (var cmd = new SqlCommand(SPName, con))
            {
                using (var sda = new SqlDataAdapter(cmd))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }

Click on Export button use following code

/// <summary>
    /// Export button click event
    /// </summary>
    /// <returns>Return the excel file with multiple sheets</returns>
    public ActionResult ExportToExcel()
    {
        var sheetNames = new List<string>() { "sheetName1", "sheetName2" };
        string fileName = "Example.xlsx";

        DataSet ds = GetDataSetExportToExcel();

        XLWorkbook wbook = new XLWorkbook();

        for (int k = 0; k < ds.Tables.Count; k++)
        {
            DataTable dt = ds.Tables[k];
            IXLWorksheet Sheet = wbook.Worksheets.Add(sheetNames[k]);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                Sheet.Cell(1, (i + 1)).Value = dt.Columns[i].ColumnName;
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    Sheet.Cell((i + 2), (j + 1)).Value = dt.Rows[i][j].ToString();
                }
            }
        }

        Stream spreadsheetStream = new MemoryStream();
        wbook.SaveAs(spreadsheetStream);
        spreadsheetStream.Position = 0;

        return new FileStreamResult(spreadsheetStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { FileDownloadName = fileName };
    }

I hope it will work.

Answer 3

Here we can export several tables into single excel sheet.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace multiplesheets_export
{
    class Program
    {
    public static void Main(string[] args)
                {
                    object missing = Type.Missing;
                    SqlConnection con = new SqlConnection("Data Source=WINCTRL-KJ8RKFO;Initial Catalog=excel;Integrated Security=True");

                    SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);
                    SqlDataAdapter da1 = new SqlDataAdapter("select * from Department", con);

                    DataTable dt = new DataTable();
                    DataTable dt1 = new DataTable();

                    da.Fill(dt);
                    da1.Fill(dt1);

                    if (dt == null || dt.Columns.Count == 0)
                                                        throw new Exception("ExportToExcel: Null or empty input table!\n");
                    Excel.Application oXL = new Excel.Application();
                    Excel.Workbook oWB = oXL.Workbooks.Add(missing);
                    Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
                    oSheet.Name = "Employee Details";

                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        oSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    }
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        for (var j = 0; j < dt.Columns.Count; j++)
                        {
                            oSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
                        }
                    }

                    // From Here am taking EXCEL SHEET -2

                    Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)as Excel.Worksheet;

                    if (dt1 == null || dt1.Columns.Count == 0)
                        throw new Exception("ExportToExcel: Null or empty input table!\n");
                    oSheet2.Name = "Depatment Details";

                    for (var i = 0; i < dt1.Columns.Count; i++)
                    {
                        oSheet2.Cells[1, i + 1] = dt1.Columns[i].ColumnName;
                    }
                    for (var i = 0; i < dt1.Rows.Count; i++)
                    {
                        for (var j = 0; j < dt1.Columns.Count; j++)
                        {
                            oSheet2.Cells[i + 2, j + 1] = dt1.Rows[i][j];
                        }
                    }
                    oXL.Visible = true;
                }
            }
        }

Answer 4

To put data into excel sheets, you don't need to bring the data into datasets. You can directly pull the data onto excel sheet in many ways. One of this ways is to use QueryTables.Add method. With this approach, when data is modified on source, change is reflected in the excel file. ie: (using sample Northwind data):

void Main()
{
  Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
  var workbook = xl.Workbooks.Add();
  Worksheet sht1, sht2;
  sht1 = ((Worksheet)workbook.Sheets[1]);
  if (workbook.Sheets.Count < 2)
  {
    sht2 = (Worksheet)workbook.Sheets.Add();
  }
  else
  {
    sht2 = ((Worksheet)workbook.Sheets[2]);
  }
  xl.Visible = true;
  sht1.Move(sht2);

  sht1.Name = "Data Sheet 1";
  sht2.Name = "Data Sheet 2";

  string strCon = @"OLEDB;Provider=SQLNCLI11.0;server=.\SQLExpress;Trusted_Connection=yes;Database=Northwind";

  Range target1 = (Range)sht1.Range["A1"];
  sht1.QueryTables.Add(strCon, target1, "Select * from Customers" ).Refresh();

  Range target2 = (Range)sht2.Range["A1"];
  sht2.QueryTables.Add(strCon, target2, "Select * from Orders").Refresh();
}

Another option is to use the Epplus library from Nuget. Using that one you can simply have many sheets where you load the content from collections. ie:

void Main()
{
  ExcelPackage pck = new ExcelPackage();

  var collection1 = from c in db.Customers
                    select new
                    {
                      CustomerName = c.CompanyName,
                      ContactPerson = c.ContactName,
                      FirstOrder = c.Orders.Min(o => o.OrderDate),
                      LastOrder = c.Orders.Max(o => o.OrderDate),
                      OrderTotal = c.Orders.Any() ? c.Orders.Sum(o => o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice)) : 0M,
                      Orders = c.Orders.Count()
                    };
  var collection2 = db.Orders.Select(o => new {
         OrderId = o.OrderID, Customer=o.CustomerID, OrderDate=o.OrderDate});

  var ws1 = pck.Workbook.Worksheets.Add("My Sheet 1");

  //Load the collection1 starting from cell A1 in ws1
  ws1.Cells["A1"].LoadFromCollection(collection1, true, TableStyles.Medium9);
  ws1.Cells[2, 3, collection1.Count() + 1, 3].Style.Numberformat.Format = "MMM dd, yyyy";
  ws1.Cells[2, 4, collection1.Count() + 1, 4].Style.Numberformat.Format = "MMM dd, yyyy";
  ws1.Cells[2, 5, collection1.Count() + 1, 5].Style.Numberformat.Format = "$#,##0.0000";
  ws1.Cells[ws1.Dimension.Address].AutoFitColumns();

  var ws2 = pck.Workbook.Worksheets.Add("My Sheet 2");

  //Load the collection1 starting from cell A1 in ws1
  ws2.Cells["A1"].LoadFromCollection(collection2, true, TableStyles.Medium9);

  //...and save
  var fi = new FileInfo(@"d:\temp\AnonymousCollection.xlsx");
  if (fi.Exists)
  {
    fi.Delete();
  }
  pck.SaveAs(fi);
}

PS: Both QueryTables and Epplus approaches are fast. If you would anyway use datasets and\or setting cell values in a loop, then be sure you have small data.