Get value from next row in Excel sheet with POI

Question

I'm using Apache POI to read an Excel document. I want to get the cell value from the next row if current is empty. For example, get from first row all not empty cell values and for empty cells: move to the next row and get the value. This the code I'm using to read the Excel document

                //every sheet has rows, iterate over them
                Iterator<Row> rowIterator = sheet.iterator();

                if (rowIterator.hasNext())
                    rowIterator.next();

                while (rowIterator.hasNext()) 
                {

                    String libelle="";
                    .....

                    //Get the row object
                    Row row = rowIterator.next();

                    //Every row has columns, get the column iterator and iterate over them
                    Iterator<Cell> cellIterator = row.cellIterator();

                    while (cellIterator.hasNext()) 
                    {
                        //Get the Cell object
                        Cell cell = cellIterator.next();

                        //check the cell type and process accordingly
                        //2nd column

                        switch(cell.getCellType()){
                        case Cell.CELL_TYPE_STRING:

                            ......
                            if (row.getCell(5) == null)
                            {
                               System.out.println("Cell is Empty in Column:" );

                            } else if (row.getCell(5).getCellType() == HSSFCell.CELL_TYPE_STRING)
                            {
                            libelle= row.getCell(5).getStringCellValue().trim();
                            }

...

Did you try reading the Apache POI docs on iterating over rows and cells? — Gagravarr

flo flo · Accepted Answer · 2015-04-09T13:35:44

You can use Sheet.getRow(int rownumber) to get a row with a specific number. You can get the current row's number via Row.getRowNum() and the cell's index via Cell.getColumnIndex().

Therefore, the value of the cell in the next row would be

Row nextRow = sheet.getRow(row.getRowNum() + 1);
if (nextRow != null)
    String value = nextRow.getCell(curCel.getColumnIndex(), Row.CREATE_NULL_AS_BLANK).getStringCellValue();

Get value from next row in Excel sheet with POI

2 Answers