Apache POI XSSFTable.java:294 arrayindex out of bounds

Question

I am trying to edit an xls file through apache POI, but while calling workbook.write(fileOutpuStream), I am getting the following error :

ERROR [2018-01-16 20:33:50,817] java.lang.ArrayIndexOutOfBoundsException: 1
at org.apache.poi.xssf.usermodel.XSSFTable.setCellReferences(XSSFTable.java:294)
at org.apache.poi.xssf.usermodel.XSSFTable.getStartCellReference(XSSFTable.java:266)
at org.apache.poi.xssf.usermodel.XSSFTable.updateHeaders(XSSFTable.java:347)
at org.apache.poi.xssf.usermodel.XSSFTable.writeTo(XSSFTable.java:93)
at org.apache.poi.xssf.usermodel.XSSFTable.commit(XSSFTable.java:104)
at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:414)
at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:419)
at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:419)
at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:242)

Some lines of my code are :

Workbook workbook = new XSSFWorkbook(fileInputStream)
Sheet firstSheet = workbook.getSheetAt(0);
/* iterate through rows and columns, write at the last column (fixed number) */
fileInputSream.close()
FileOutputStream fileOut = new FileOutputStream(localFile);
workbook.write(fileOut);
fileOut.close()

A similar code was working for me earlier, so I have no idea what is wrong with this. I'm literally stuck on this.

EDIT:

I got to part pointed by jontro. It is indeed finding only the start reference of my sheet.

I have an excel like :

id | name   | result
1  | first  |  
2  | second |

I am iterating through the rows, and creating users in my system, if created successfully, I write true (false) in the result column. Since xlsx is created through google spreadsheets, I do not have an end row so if I find empty name, I break out of the row iterator. Then execute the part of my code below the comments, written above.

EDIT 2:

InputStream is = new FileInputStream(localFile);
try (Workbook workbook = new XSSFWorkbook(is)) {
  Sheet firstSheet = workbook.getSheetAt(0);
  Iterator<Row> iterator = firstSheet.iterator();
  while (iterator.hasNext()) {
    Row row = iterator.next();
    Data data = new Data(row.cellIterator());
    //do something with data
    data.getCell().setCellValue("processed");
  }
  FileOutputStream fileOut = new FileOutputStream(localFile);
  workbook.write(fileOut); //fails here
  fileOut.close();
} catch (Exception e) {//print
}



public class Data {
Data (Iterator<Cell> iterator) {
  Cell cell = iterator.next();
  this.var1 = cell.getStringCellValue();
  cell = iterator.next();
  this.var2 = cell.getStringValue();
  ....
  this.cell = iterator.next(); //this is for saying processed
}

I am starting to agree with Axel's comment that this has to do with google spreadsheet creation of Table objects. I have a similar code, where there is a different class Data, and that worked! I double verified everything and nothing was different.

try System.out.println(workbook.getNumberOfSheets()); before Sheet firstSheet = workbook.getSheetAt(0); — Elentriel
The problem is definitely with your data. XSSFTable is only involved if the workbook not only contains simple worksheets but has Microsoft Office Excel table (previously known as an Excel list) in the sheets. So I suspect google spreadsheets does something while creating those Table objects what apache poi does not expect. So to help we would need the xlsx what was created through google spreadsheets. — Axel Richter

jontro jontro · Accepted Answer · 2018-01-16T21:22:46

By looking at the code in the trunk of XSSFTable.java we can see the following code

private void setCellReferences() {
    String ref = ctTable.getRef();
    if (ref != null) {
        String[] boundaries = ref.split(":", 2);
        String from = boundaries[0];
        String to = boundaries[1];
        startCellReference = new CellReference(from);
        endCellReference = new CellReference(to);
    }
}

The ArrayIndexOutOfBoundsException is thrown on the following line String to = boundaries[1];

The cell reference is expected to be in the form A1:B3 or similar. Since you do not post your loop it's hard to say exactly what is causing this error. Check that you are not creating incomplete cell ranges

Apache POI XSSFTable.java:294 arrayindex out of bounds

1 Answers