Reading excel files .xlsx via Java

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();

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.

链接地址: http://www.djcxy.com/p/37984.html

上一篇: 在我的应用程序中没有得到正确的行数或值(Apache poi)

下一篇: 通过Java读取excel文件.xlsx