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
  }
}

Answer 2

Refer below:

        String fileName = "D:\\TestScripts.xls"; // file
        POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(fileName));
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
    //  HSSFSheet sheet = workbook.getSheetAt(0); //Get first Excel Sheet
        HSSFSheet sheet = workbook.getSheet("SheetName"); //Get data as per sheet name
        for (Row row : sheet) { // For each Row.
              Cell cell = row.getCell(0); // Get the Cell at the Index / Column you want.
              if(cell.getStringCellValue().equalsIgnoreCase("test")) {
                  System.out.println(cell.getRow().getLastCellNum());
                  for(int i=0;i<=cell.getRow().getLastCellNum()-1;i++) {
                      System.out.println(cell.getRow().getCell(i));
                  }
              }
           }

Remove below condition if you are not looking for any particular column data

 if(cell.getStringCellValue().equalsIgnoreCase("test"))

Note: you need to change the test from above code with your column name like id, name as per your sheet data header