java - How can I read specific rows with Apache POI?

Question

I'm using the Apache POI library, but I have some data that I don't want to be read - So I need the program to start reading the file from a specific row.

I want all the data from the cells and rows which comes after row 10, until the document is empty. I have tried with the following code.

Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet firstSheet = workbook.getSheetAt(0);

    Iterator<Row> iterator = firstSheet.iterator();
    Row getSchool = firstSheet.getRow(10);

    Iterator<Cell> cellIterator = getSchool.cellIterator();

    while (iterator.hasNext())
    {
        while (cellIterator.hasNext())
        {
         ...
        }
    }

But it will only give me all the data from the cells in row 10.

I'll be looking forward to hear from you :-).

Answer 1

You're only getting the data from row 11 here:

Row getSchool = firstSheet.getRow(10);

See the documentation for Sheet.getRow(int rownum)

Returns the logical row (not physical) 0-based. If you ask for a row that is not defined you get a null. This is to say row 4 represents the fifth row on a sheet.

Check the examples in the documentation on how to Iterate over rows and cells.

You can use something like:

Workbook workbook = new XSSFWorkbook(inputStream);
Sheet firstSheet = workbook.getSheetAt(0);

for (Row row : firstSheet) {
  for (Cell cell : row) {
     // Do something here
  }
}

If you want to iterate over all cells in a row check how to Iterate over cells, with control of missing / blank cells.

The CellIterator will only return the cells defined in the file, which is largely those with values or stylings, but it depends on Excel.

You could specify a Row.MissingCellPolicy as:

Row.getCell(int, MissingCellPolicy)

Here's an example:

int lastColumn = Math.max(row.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);

for (int cn = 0; cn < lastColumn; cn++) {
  Cell c = row.getCell(cn, Row.RETURN_BLANK_AS_NULL);
  if (c == null) {
    // The spreadsheet is empty in this cell
  } else {
    // Do something useful with the cell's contents
  }
}