CODEWITHSUNDEEP
javaexcelapache-poi
Elizama Melo
Elizama Melo

Reputation: 153

sheet.getRow(rowIndex) returns NULL - APACHE POI

I am using the APACHE POI library to read an excel file in xlsx format.

My problem is that I want to remove the last row from each sheet of this excel, I found a way to find the last row to remove, but it returns an int. The method sheet.removeRow(Row var1); would solve my problem.

Well, having the row number that I want to delete and a method to delete that row. I just need to convert the lastRow (int) in a Row type so I can use the sheet.removeRow method.

To do that I used the following code: Row a = sheet.getRow(lastRow) this method should return a Row with that index. But instead it returns NULL.

Any idea what I'm doing wrong or how to convert the line number I want to remove into a Row type?

Appreciate your help!

Here is an excerpt of the code that read my excel file

    public static List<Measure> excelToMeasures(InputStream is, ProjectMeasureFile projectMeasureFile) throws IOException {

    List<Measure> measures = new ArrayList<>();

    try (Workbook workbook = new XSSFWorkbook(is)) {

        for (int i = 0; i < 3; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            int lastRow = sheet.getPhysicalNumberOfRows() -1;

            removeRow(sheet, lastRow);

            int rowNumber = 0;
            for (Row row : sheet) {

                // skip header
                if (rowNumber == 0) {
                    rowNumber++;
                    continue;
                }

                Iterator<Cell> cellIterator = row.iterator();
                List<Cell> cellObject = new ArrayList<>();

                while (cellIterator.hasNext()) {
                    cellObject.add(cellIterator.next());
                }

                if (cellObject.size() > 0) {
                    Measure measure = new Measure();
                    measure.setArea(sheet.getSheetName());
                    measure.setCode(convertStringCell(cellObject.get(0)));
                    measure.setBehavior(convertStringCell(cellObject.get(1)));

                    measure.setHumanDependencyFactor(convertNumericCell(cellObject.get(2)));
                    Double measureType = convertToPercentage(measure.getHumanDependencyFactor());
                    measure.setHumanDependencyFactor(measureType);

                    measure.setMeasurementResults(convertNumericCell(cellObject.get(3)));
                    Double value = convertToPercentage(measure.getMeasurementResults());
                    measure.setMeasurementResults(value);

                    measure.setProjectMeasureFile(projectMeasureFile);
                    measures.add(measure);
                }

                rowNumber++;
            }
        }

    } catch (Exception e) {
        log.error("An error occurred when trying to parse the file.");
        e.printStackTrace();
    }

    return measures;
}

And here is the method for removing a row:

  public static void removeRow(Sheet sheet, int rowIndex) {
    int lastRowNum = sheet.getPhysicalNumberOfRows() -1;

    if (rowIndex == lastRowNum) {
        Row removingRow = sheet.getRow(rowIndex);
        sheet.getRow(rowIndex);
        Row a = sheet.getRow(rowIndex);
        if (removingRow != null) {

           
            System.out.println(sheet.getRow(lastRowNum).getCell(0).toString());
           
            sheet.removeRow(removingRow); 
        }
    }
}

Upvotes: 2

Views: 9665

Answers (2)

Abbas
Abbas

Reputation: 251

I faced this problem. I used XSSFWorkbook instead of SXSSFWorkbook as a solution.

final XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61945

At first to title of your question: Sheet.getRow will return NULL by design. It returns NULL if the row behind the row index is not stored in sheet. So you always need to check for NULL after Sheet.getRow. Same is for Row.getCell which returns NULL by design for cells which are not stored in the row.

And Sheet.getPhysicalNumberOfRows is not the correct way to get the last row in sheet.

A Excel sheet only physically contains rows having cells stored in them. Rows which are completely empty are not physically stored. So if a sheet only contains data in rows 1, 2, 5, 6 and 7, then Sheet.getPhysicalNumberOfRows will return 5 but last row is 7.

There is Sheet.getLastRowNum to get the last row number (0-based) in sheet. So that would return 6 in the example above and sheet.getRow(6) would get the last row and not NULL.

But there is another problem to consider. In Excel rows might not be totally empty but only contains cells which are blank. Blank cells might be stored because they have cell formats or they had content before. Sheet.getLastRowNum gets the last stored row, even if this row only contains blank cells. So you need to check whether the row behind Sheet.getLastRowNum contains only blank cells by iterating over the cells and check for CellType.BLANK if you need the last filled row.

The following method gets the last filled row in a sheet. It returns NULL if no filled row was found.

 Row getLastFilledRow(Sheet sheet) {
  int lastStoredRowNum = sheet.getLastRowNum();
  for (int r = lastStoredRowNum; r >= 0; r--) {
   Row row = sheet.getRow(r);
   if (row != null) {
    for (Cell cell : row) {
     if (cell.getCellType() != CellType.BLANK) return row;  
    }
   }       
  }
  return null;  
 }

Upvotes: 3

Related Questions