Read excel file 2010 apache poi ignoring empty cells

Question

I am having an problem, while java application reading Excel file .xlsx extention, The application is working normally, but in the workbook has many sheets, 1, 2 sheets reading correctly , but when reads 3 sheet, there does not read all the cells and while I opened and checked from Excel file, cell is exists with empty values, but when apache poi reading it the cell is just ignored the empty cells. What is a cause? Update

 FileInputStream file = new FileInputStream(file_location);
  //this.workbook = new HSSFWorkbook(file);
  this.workbook = new XSSFWorkbook(file);
XSSFSheet mySheet = workbook.getSheetAt(index);
       Iterator rowIter = mySheet.rowIterator();
        while(rowIter.hasNext()){
          XSSFRow myRow = (XSSFRow) rowIter.next();
          Iterator cellIter = myRow.cellIterator();
          ArrayList colStoreArray=new ArrayList();



          while(cellIter.hasNext()){
              XSSFCell myCell = (XSSFCell) cellIter.next();
              colStoreArray.add(myCell);
                                   }
          rowArrayHolder.add(colStoreArray);

        } }

Above the reading excel file, While i checked the Excel file there in one row 20 cells, but while it reads in some rows only 14 rows. I am using ArrayList, seems here should be problem with the concurrent accessing the ArrayList. What can be cause?

How are you reading the file? Iterating over cells, or fetching by index? (Code would help) — Gagravarr
Iterating each cell, after that each cell is selected with switch statment, the document structure is same for all sheets, but its working for first sheet, but fails in second sheet. so i checked Excel file the row there are 20 cells, including empty cells, but in java application its showing only 14 cells. What can be the cause? — Olzhas

Gagravarr Gagravarr · Accepted Answer · 2012-10-24T14:21:02

The iterators only iterate over cells that are defined in the file. If the cell has never been used in Excel, it probably won't appear in the file (Excel isn't always consistent...), so POI won't see it

If you want to make sure you hit every cell, you should lookup by index instead, and either check for null cells (indicating the cell has never existed in the file), or set a MissingCellPolicy to control how you want null and blank cells to be treated

Read excel file 2010 apache poi ignoring empty cells

1 Answers