Reading excel files .xlsx via Java

Question

So my excel file is relatively small in size. It contains 8 sheets. Each sheet has "records" of data which i need to read. Each sheet also has the first row reserved for headers which i skip; so my data will begin from the 2nd row (1st index) of each sheet and end on the last record.

So, below is my code to iterate through the sheets and read each row however it fails to read each sheet. And i can't seem to figure out why. Please have look and any suggestions will be appreciated. Thanks!

FileInputStream fis = new FileInputStream(new File(filePath));
XSSFWorkbook wb = new XSSFWorkbook(fis);
DataFormatter formatter = new DataFormatter();

//iterate over sheets
for (int i=0; i<NUM_OF_SHEETS; i++) {
  sheet = wb.getSheetAt(i);
  sheetName = sheet.getSheetName();

  //iterate over rows
  for (int j=1; j<=lastRow; j++) {  //1st row or 0-index of each sheet is reserved for the headings which i do not need.
    row = sheet.getRow(j);
    if (row!=null) {
      data[j-1][0] = sheetName; //1st column or 0th-index of each record in my 2d array is reserved for the sheet's name.

      //iterate over cells
      for (int k=0; k<NUM_OF_COLUMNS; k++) {
        cell = row.getCell(k, XSSFRow.RETURN_BLANK_AS_NULL);
        cellValue = formatter.formatCellValue(cell); //convert cell to type String
        data[j-1][k+1] = cellValue;
      }//end of cell iteration
    }
  }//end of row iteration
}//end of sheet iteration
wb.close();
fis.close();

What does "however it fails to read each sheet" mean? An error occurs? Then please provide the stack trace. Something other does not work? Then please tell us what. — Axel Richter, Mar 19 '16 at 07:12
Have you tried walking through your code using a debugger and/or adding logging so that you can troubleshoot your issue? — mfulton26, Mar 19 '16 at 11:45

score 2 · Accepted Answer · answered Mar 19 '16 at 10:10

At least there is one big logical error. Since you are putting the data of all sheets in one array, this array must be dimensioned like:

String[][] data = new String[lastRow*NUM_OF_SHEETS][NUM_OF_COLUMNS+1];

And then the allocations must be like:

...
      data[(j-1)+(i*lastRow)][0] = sheetName; //1st column or 0th-index of each record in my 2d array is reserved for the sheet's name.
...

and

...
        data[(j-1)+(i*lastRow)][k+1] = cellValue;
...

With your code, the allocations from second sheet will overwrite the ones from the first sheet, since j starts with 1 for every sheet.

I actually have a method to count the total number of rows throughout the sheets i need. HOWEVER, you did shed light into how i was using my 2D array and i've figured out the issue. It was exactly what you said about my second sheet overriding my 1st sheet. — RoRO, Mar 20 '16 at 07:52

Reading excel files .xlsx via Java

1 Answers1