无效的指数.当Workbooks.Open时出现HRESULT: 0x8002000B (DISP_E_BADINDEX

本文关键字:0x8002000B DISP HRESULT BADINDEX 指数 Workbooks Open 无效 | 更新日期: 2023-09-27 17:52:48

我正在尝试制作一个应用程序,将打开xlsx文件进行阅读,然后阅读它并使用它做一些事情。当我运行我的应用程序,并点击一个按钮加载文件,我得到这个错误:

无效索引。异常from HRESULT: 0x8002000B (DISP_E_BADINDEX)

在这行代码中:

Excel.Workbook a
   = excelApp.Workbooks.Open("C:''test.xlsx", 0, true, 5, "", "", true,
                             Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                             "'t", false, false, 0, true, 1, 0);

你能指出这里有什么问题吗?

编辑:这里是完整的代码,所以我希望它会更容易告诉什么原因导致错误

 using System;
 using System.Collections.Generic;
 using System.ComponentModel;
 using System.Data;
 using System.Drawing;
 using System.Linq;
 using System.Text;
 using System.Windows.Forms;
 using Excel = Microsoft.Office.Interop.Excel;
 using System.Reflection;

 namespace WindowsFormsApplication2
 {
     public partial class Form1 : Form, IDisposable
     {
         public Form1()
         {
             InitializeComponent();
         }
         private void button1_Click(object sender, EventArgs e)
         {
             Excel.Application excelApp = new Excel.Application();
             excelApp.Visible = true;
             Excel.Workbook a = excelApp.Workbooks.Open("C:/test.xlsx");

        // This selectes the used range of the excel workbook and enters it in
        // a two dimentional array
        try
        {
            // Get a reference to the first sheet of the workbook.
            Excel.Sheets excelSheets = a.Worksheets;
            string currentSheet = "Sheet1";
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
            // write out to console for debugging
            textBox1.Text = "excelWorksheet is " + excelWorksheet;
            // Get a range of data.
            Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A3", Missing.Value);
            // write out to console for debugging
            textBox1.Text = "excelCell is " + excelCell;
            // write out to console for debugging
            textBox1.Text = "Creating string[,] array. . . ";
            // Retrieve the data from the range.
            Object[,] dataArray;
            // write out to console for debugging
            textBox1.Text = "String[,] array created. . . ";
            dataArray = (System.Object[,])excelCell.get_Value(Missing.Value);
            // write out to console for debugging
            textBox1.Text = "Counting rows and columns. . . ";
            // Determine the dimensions of the array.
            int iRows;
            int iCols;
            iRows = dataArray.GetUpperBound(0);
            iCols = dataArray.GetUpperBound(1);
            // write out to console for debugging
            textBox1.Text = "Printing array. . . ";
            // Print the data of the array.
            for (int rowCounter = 1; rowCounter <= iRows; rowCounter++)
            {
                // write out to console for debugging
                textBox1.Text = ("row " + rowCounter);
                for (int colCounter = 1; colCounter <= iCols; colCounter++)
                {
                    // Write the next value to the console.
                    richTextBox1.Text = "col " + colCounter + "= " + dataArray[rowCounter, colCounter].ToString() + ", ";
                }
                // Write in a new line.
                richTextBox1.Text = "'n";
            }
        }
        catch (Exception theException)
        {
            // Create error message
            String errorMessage;
            errorMessage = "Error: ";
            errorMessage = String.Concat(errorMessage, theException.Message);
            errorMessage = String.Concat(errorMessage, " Line: ");
            errorMessage = String.Concat(errorMessage, theException.Source);
            // Display error message
            MessageBox.Show(errorMessage, "Error");
         }
       }
    }
  }

无效的指数.当Workbooks.Open时出现HRESULT: 0x8002000B (DISP_E_BADINDEX

我不知道您是否找到了解决方案,但这是一个变通方法。希望能有所帮助我也面临着同样的问题。问题在这条线上。在我的例子中,工作簿有多个工作表,因此我想循环遍历每个工作表并检索数据。

string currentSheet = "Sheet1";
        Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

您正在尝试打开索引/名称为"Sheet1"的工作表,该工作表不存在……如果您已经知道您所期望的工作表名称,请继续使用名称,否则最好使用工作表索引。示例

Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(1);

获取索引为1的表

这就是我解决我的问题的方法。首先检查表名是否存在

var sheetExists = xlWorkBook.Worksheets.Cast<Excel.Worksheet>().FirstOrDefault(worksheet => worksheet.Name == "SomeSheetName"); // this line returns null if the sheet name or index you intend to open does not exist
                        if (sheetExists != null) // this line thus handles the invalid index error. 
                        { /**you can now open the sheet**/
                          Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
                       }

我相信sheetExists变量已经有工作表了,所以不需要再获取它,所以如果sheetExists不为空,那么就可以使用