如何使用C＃从DataSet创建多个工作表的Excel文件 [英] How to create excel file with multiple sheets from DataSet using C#

问题描述

如何使用C＃从DataSet创建多个表格的excel文件。我已经成功创建了一个excel文件与单页。

How to create excel file with multiple sheets from DataSet using C#. I have successfully created an excel file with single sheet. But I am not able to do that for multiple sheets.

推荐答案

方案

这里是一个简单的C＃类，它可以编程创建一个Excel WorkBook，并向其中添加两个工作表，然后填充这两个工作表。最后，它将WorkBook保存到应用程序根目录中的文件，以便您可以检查结果...

Here is a simple C# class that programatically creates an Excel WorkBook and adds two sheets to it, and then populates both sheets. Finally, it saves the WorkBook to a file in the application root directory so that you can inspect the results...

public class Tyburn1
{
    object missing = Type.Missing;
    public Tyburn1()
    {
        Excel.Application oXL = new Excel.Application();
        oXL.Visible = false;
        Excel.Workbook oWB = oXL.Workbooks.Add(missing);
        Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
        oSheet.Name = "The first sheet";
        oSheet.Cells[1, 1] = "Something";
        Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing) 
                        as Excel.Worksheet;
        oSheet2.Name = "The second sheet";
        oSheet2.Cells[1, 1] = "Something completely different";
        string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)        
                                + "\\SoSample.xlsx";
        oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
            missing, missing, missing, missing,
            Excel.XlSaveAsAccessMode.xlNoChange,
            missing, missing, missing, missing, missing);
        oWB.Close(missing, missing, missing);
        oXL.UserControl = true;
        oXL.Quit();
    }
}

为此，您需要添加一个引用到Microsoft.Office.Interop.Excel到您的项目（你可能已经做了，因为你创建一个工作表）。

To do this, you would need to add a reference to Microsoft.Office.Interop.Excel to your project (you may have done this already since you are creating one sheet).

添加第二张工作表的语句是...

The statement that adds the second sheet is...

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

'1'参数指定单个工作表，如果要添加几个

the '1' argument specifies a single sheet, and it can be more if you want to add several sheets at once.

最后注意：语句oXL.Visible = false;告诉Excel以静默模式启动。

Final note: the statement oXL.Visible = false; tells Excel to start in silent mode.

这篇关于如何使用C＃从DataSet创建多个工作表的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！