c# - 尝试将多个工作表添加到 excel

Question

我正在尝试以编程方式将工作表添加到新的 Excel 文档。

我的预期输出是名为“test1-20”的工作表，但我得到的是“Sheet1-19，test20”。

为什么这行不通？

Workbook workbook;
Application objExcel;

objExcel = new Application();
objExcel.Visible = false;
objExcel.DisplayAlerts = false;

for (var i = 0; i < worksheets.Count; i++)
{
    workbook= objExcel.Workbooks.Add(Missing.Value);
    var worksheet = (Worksheet)workbook.Worksheets.get_Item(i + 1);
    worksheet.Name = string.Format("test{0}", i + 1);
}

Answer 1

试试这个:

using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

void MyMethod()
{
    try
    {
        var _excel = new Excel();

        var wb = _excel.Workbooks.Add();
        var collection = new Microsoft.Office.Interop.Excel.Worksheet[20];

        for (var i = 19; i >= 0; i--)
        {
            collection[i] = wb.Worksheets.Add();
            collection[i].Name = String.Format("test{0}", i + 1);
        }

        for (var i = 0; i < 3; i++)
        {
            wb.Worksheets[21].Delete();
        }

        //collection is an array of worksheet objects,
        //the worksheet objects in your workbook.
        //You can access each individual worksheet and
        //work with it in the same way you access any object in an array

        var thisWorksheet = collection[9];
        var thisRange = thisWorksheet.Range["A1"];
        thisRange.Value = "Hello World";

        wb.SaveAs(@"c:\test\whatever.xlsx");
        wb.Close();
    }
    finally
    {
        Marshal.ReleaseComObject(_excel);
    }
}

您的 visible 属性默认设置为 false，因此没有必要明确地执行此操作，上面的代码中没有显示任何警报，因此也没有必要。我已经测试了上面的代码并且可以确认它有效。