将数据集导出到一个Excel文件的多个Excel工作表中 [英] Exporting datasets into multiple excel sheets of one excel file

问题描述

我需要在同一工作簿的两个Excel工作表中导出两个数据集的值. 我的查询就像:

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

//数据集一:

        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 ds2 = new DataSet();
        SqlCommand commandOpen = new SqlCommand("storedproc2", conSql);
        commandOpen.CommandType = CommandType.StoredProcedure;
        var adaptOpen = new SqlDataAdapter();
        adaptOpen.SelectCommand = commandOpen;
        adaptOpen.Fill(ds2, "table");

现在创建一个我使用过的Excel工作簿:

Now to create one Excel workbook I have used:

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

但是，除此之外，我想在同一工作簿中添加两张纸；一个用于ds1，另一个用于ds2.怎么做? 谢谢.

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.

推荐答案

可能的重复项:在该问题的答案中，显示了如何将数据集导出到excel文件，但是数据集中的每个表都有其自己的工作表.您可以根据需要修改代码.

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.

另一篇文章中的代码:

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

这篇关于将数据集导出到一个Excel文件的多个Excel工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！