c# - 尝试将第三张工作表添加到电子表格时，为什么会出现 "Invalid index"？

Question

我有添加第三张工作表的代码:

// contextual code
private Excel.Application _xlApp;
private Excel.Workbook _xlBook;
private Excel.Sheets _xlSheets;
private Excel.Worksheet _xlSheet;
private Excel.Worksheet _xlSheetDelPerf;
private Excel.Worksheet _xlSheetListObjectTest;

// the line that kablooeys (sp?):
_xlSheetListObjectTest = (Excel.Worksheet)_xlSheets.Item[3]; // <= this is line 307, made infamous in the err msg screenshotted below

添加工作表1:

_xlSheet = (Excel.Worksheet)_xlSheets.Item[1];

...和2:

_xlSheetDelPerf = (Excel.Worksheet)_xlSheets.Item[2];

...工作正常，但是当我点击 kablooizes (Item[3]) 的那一行时，我得到:



为什么？我拿了一张传单并改变了这个:

_xlApp.SheetsInNewWorkbook = 1; // prevent the empty "sheet 2" etc.

...对此:

_xlApp.SheetsInNewWorkbook = 3; // prevent the empty "sheet 2" etc.

...如果将“SheetsInNewWorkbook”设置为 1 会阻止我添加更多工作表，但不，它没有任何区别。

那么为什么当“2”很好时“3”是一个坏索引呢？

更新

对于要求更多背景信息的 Yacoub Massad:

_xlBook = _xlApp.Workbooks.Add(Type.Missing);
_xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

_xlApp.ActiveWindow.DisplayGridlines = false;
_xlApp.SheetsInNewWorkbook = 3; // prevent the empty "sheet 2" etc.
_xlSheets = _xlBook.Worksheets;

_xlSheet = (Excel.Worksheet)_xlSheets.Item[1];

如果工作表 3 需要额外的“_xlBook.Worksheets.Add()”，为什么工作表 2 不需要呢？

更新 2

这给了我“Item[0]”行上的“InvalidIndex”，如下所示:

_xlApp.ErrorCheckingOptions.BackgroundChecking = false;
_xlApp.SheetsInNewWorkbook = 3;

_xlBook = _xlApp.Workbooks.Add(Type.Missing);
_xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

_xlApp.ActiveWindow.DisplayGridlines = false;

_xlSheets = _xlBook.Worksheets;

_xlSheet = (Excel.Worksheet)_xlSheets.Item[0]; // changed to 0 from 1
_xlSheetDelPerf = (Excel.Worksheet)_xlSheets.Item[1]; // changed to 1 from 2
_xlSheetListObjectTest = (Excel.Worksheet)_xlSheets.Item[2]; // changed to 2 from 3

更新 3

我将更新 2 中的代码更改为原始索引 1、2 和 3(替换建议的 0、1 和 2)，并且那里不再出现“InvalidIndex”；然而，我现在在这个对 Sort() 的调用中做得更进一步:

fruitList.Range.Sort(
    fruitList.ListColumns[1].Range, Excel.XlSortOrder.xlAscending,
    fruitList.ListColumns[2].Range, Type.Missing, 
        Excel.XlSortOrder.xlAscending,
        Type.Missing, Excel.XlSortOrder.xlAscending,
        Excel.XlYesNoGuess.xlYes, Type.Missing, Type.Missing,
        Excel.XlSortOrientation.xlSortColumns);

在上下文中:

private void WriteListObjectTestSheet()
{
    //_xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); // see if this helps
    //_xlSheetListObjectTest = (Excel.Worksheet)_xlSheets.Item[2]; // changed to 2 from 3
    _xlSheetListObjectTest.Name = ProduceUsageListObjectSortSheetName;

    _xlSheetListObjectTest.Cells[5, 1] = "Apple";
    _xlSheetListObjectTest.Cells[6, 1] = "Strawberry";
    _xlSheetListObjectTest.Cells[7, 1] = "Cashew";
    _xlSheetListObjectTest.Cells[8, 1] = "Kumquat";
    _xlSheetListObjectTest.Cells[9, 1] = "Pomegranate";
    _xlSheetListObjectTest.Cells[10, 1] = "Banana";
    _xlSheetListObjectTest.Cells[11, 1] = "Pineapple";
    _xlSheetListObjectTest.Cells[12, 1] = "Kiwi";
    _xlSheetListObjectTest.Cells[13, 1] = "Huckleberry";
    _xlSheetListObjectTest.Cells[14, 1] = "Gooseberry";

    Excel.ListObject fruitList =
        _xlSheetListObjectTest.
            ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange,
                _xlSheetListObjectTest.Range[
                    _xlSheetListObjectTest.Cells[4, 1],
                    _xlSheetListObjectTest.Cells[4, 1]], //13]], 
                Type.Missing, Excel.XlYesNoGuess.xlNo);

    fruitList.Range.Sort(
        fruitList.ListColumns[1].Range, Excel.XlSortOrder.xlAscending,
        fruitList.ListColumns[2].Range, Type.Missing, 
            Excel.XlSortOrder.xlAscending,
            Type.Missing, Excel.XlSortOrder.xlAscending,
            Excel.XlYesNoGuess.xlYes, Type.Missing, Type.Missing,
            Excel.XlSortOrientation.xlSortColumns);
}

我从 here 中选择了该代码 [a,o] ，并承认我不是很懂；我认为问题出在 ListColumns 1和/或 ListColumns 2 ，但不知道为什么...

Answer 1

移动这一行:

_xlApp.SheetsInNewWorkbook = 3;

在此行之前:

_xlBook = _xlApp.Workbooks.Add(Type.Missing);

顾名思义， SheetsInNewWorkbook为尚未创建的工作簿设置工作表的数量，而不是已经创建的工作簿。