c# - C# 中的 Excel - 2 张纸有问题

Question

我使用此代码从数据集中制作 Excel:

SQL = "select Bar,Store,Serial from Counter";
            dsView = new DataSet();
            adp = new OleDbDataAdapter(SQL, Conn);
            adp.Fill(dsView, "Counter");
            adp.Dispose();
 Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
        xla.Visible = false  ; 
        Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet);
        Worksheet ws = (Worksheet)xla.ActiveSheet;
        int i = 1;
        foreach (DataRow comp in dsView.Tables[0].Rows)
        {
            ws.Cells[i, 1] = comp[0].ToString();
            ws.Cells[i, 2] = comp[1].ToString();
            ws.Cells[i, 3] = comp[2].ToString();
            i++;
        }

我有两个问题

如何打开新工作表以及如何关闭此过程

(我看到 Excell allwais 在后台运行)

Answer 1

How to open new sheet?

您可以使用 Workbook.Worksheets.Add 添加新工作表方法。

var newWorksheet = 
    (Worksheet)xla.Worksheets.Add(Type.Missing
                                , Type.Missing
                                , Type.Missing
                                , Type.Missing);

How to close this process?

您必须关闭工作簿并处理应用程序实例。

SQL = "select Bar,Store,Serial from Counter";
dsView = new DataSet();
using (adp = new OleDbDataAdapter(SQL, Conn)) 
    using (Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application()) {
        adp.Fill(dsView, "Counter");

        xla.Visible = false  ; 

        try {
            Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet);
            Worksheet ws = (Worksheet)xla.ActiveSheet;

            int i = 1;

            foreach (DataRow comp in dsView.Tables[0].Rows) {
                ws.Cells[i, 1] = comp[0].ToString();
                ws.Cells[i, 2] = comp[1].ToString();
                ws.Cells[i, 3] = comp[2].ToString();
                i++;
            }
        } finally {
            // Notice that the two following lines are totally optional as the use of 
            // using blocks assure that the used resource will necessarily be disposed
            // when getting out of the using blocks scope.
            adp.Dispose();
            xla.Dispose();
        }
}

这里有一些链接可以帮助您熟悉 Microsoft.Office.Interop.Excel COM 程序集。

How to: Add New Worksheets to Workbooks ;

Working with Worksheets ;

Working with Workbooks ;

Working with Ranges ;

Working with Cells .