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);
    }