XSSFSheet: Sheet index is out of range

时间:2023-01-15 16:41:29

I am tring to write the contents of xlsx file with POI but got an IllegalArgumentException.

我正在尝试用POI写xlsx文件的内容,但是遇到了一个IllegalArgumentException。

Following is my code:

下面是我的代码:

public static void ConvertToCSVOrgtoOrg(String Scenario) throws Exception {

    String IntermediateXLSXPath=GetFileDetailsSIF(Scenario,"ExcelPath");
    String IntermediateCSVPath=GetFileDetailsSIF(Scenario,"CSVPathOrgtoOrg");

    File inputFile = new File(IntermediateXLSXPath);
    File outputFile = new File(IntermediateCSVPath);
    // For storing data into CSV files
    StringBuffer data = new StringBuffer();

    try {
        FileOutputStream fos = new FileOutputStream(outputFile);
        // Get the workbook object for XLSX file
        XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
        XSSFFormulaEvaluator.evaluateAllFormulaCells(wBook);
        // Get first sheet from the workbook
        XSSFSheet sheet = wBook.getSheetAt(18);
        XSSFFormulaEvaluator.evaluateAllFormulaCells(wBook);

        Row row;
        Cell cell;
        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            row = rowIterator.next();
            data.append("\n");

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        data.append(cell.getBooleanCellValue());
                        break;

                    case Cell.CELL_TYPE_NUMERIC:
                        data.append(cell.getNumericCellValue());
                        break;

                    case Cell.CELL_TYPE_STRING:
                        data.append(cell.getStringCellValue());
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        data.append(cell.getStringCellValue());

                    case Cell.CELL_TYPE_BLANK:
                        break;
                    default:
                }
            }
        }

        fos.write(data.toString().getBytes());
        Thread.sleep(1000);
        fos.close();

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }

}

The exception I am getting is:

我得到的例外是:

java.lang.IllegalArgumentException: Sheet index (18) is out of range (0..17)
  at org.apache.poi.xssf.usermodel.XSSFWorkbook.validateSheetIndex(XSSFWorkbook.java:1205)
  at org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheetAt(XSSFWorkbook.java:960)
  at BasicCoreFramework.ObjectUtilities.ConvertToCSVOrgtoOrg(ObjectUtilities.java:1625)
  at TestUtilities.FileManipulation.PostAggregationScenario1Functionality(FileManipulation.java:40)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:85)
  at org.testng.internal.Invoker.invokeMethod(Invoker.java:639)
  at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:816)
  at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1124)
  at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125)
  at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:108)
  at org.testng.TestRunner.privateRun(TestRunner.java:774)
  at org.testng.TestRunner.run(TestRunner.java:624)
  at org.testng.SuiteRunner.runTest(SuiteRunner.java:359)
  at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:354)
  at org.testng.SuiteRunner.privateRun(SuiteRunner.java:312)
  at org.testng.SuiteRunner.run(SuiteRunner.java:261)
  at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
  at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
  at org.testng.TestNG.runSuitesSequentially(TestNG.java:1215)
  at org.testng.TestNG.runSuitesLocally(TestNG.java:1140)
  at org.testng.TestNG.run(TestNG.java:1048)
  at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:126)
  at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:137)
  at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:58)

I do have a 18th sheet. Any help would be greatly appreciated.

我有第18页。如有任何帮助,我们将不胜感激。

1 个解决方案

#1


1  

...
// Get first sheet from the workbook
//XSSFSheet sheet = wBook.getSheetAt(18); Instead,
XSSFSheet sheet = wBook.getSheetAt(17);
...

As you said you have only 18 excel sheets in that file, if you are going to get the 18th sheet in the excel file, you have to minus 1 from 18. Specify 17.

就像你说的,你只有18张excel表格,如果你想要在excel文件中得到第18张,你必须从18开始减去1。指定17。

Index is start from 0 instead of 1.

Index是从0开始,而不是1。

#1


1  

...
// Get first sheet from the workbook
//XSSFSheet sheet = wBook.getSheetAt(18); Instead,
XSSFSheet sheet = wBook.getSheetAt(17);
...

As you said you have only 18 excel sheets in that file, if you are going to get the 18th sheet in the excel file, you have to minus 1 from 18. Specify 17.

就像你说的,你只有18张excel表格,如果你想要在excel文件中得到第18张,你必须从18开始减去1。指定17。

Index is start from 0 instead of 1.

Index是从0开始,而不是1。