Bug 61343 - Sheet.getFirstRowNum() returns first non empty row
Summary: Sheet.getFirstRowNum() returns first non empty row
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.16-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2017-07-26 04:11 UTC by Mayur Jain
Modified: 2017-07-26 05:45 UTC (History)
0 users

Sample spreadsheet (4.50 KB, application/vnd.ms-excel)
2017-07-26 04:11 UTC, Mayur Jain

Note You need to log in before you can comment on or make changes to this bug.
Description Mayur Jain 2017-07-26 04:11:55 UTC
Created attachment 35173 [details]
Sample spreadsheet

I have a spreadsheet with cell (3,6) i.e 3rd row and 6th column being populated with some data. All other rows and columns of the sheet are empty.
While trying to read the Spreadsheet,  
     we use the function:
          sheet.getFirstRowNum() which returns 2 in this case. Expected value is 0 as the sheet start from 0th row.

sheet.getRow() for 0th index returns null, ideally it should return row object with empty value.
Comment 1 Javen O'Neal 2017-07-26 05:40:32 UTC
The OOXML format allows for rows or cells to be missing. This is allows for sheets to be sparse, consuming less memory and tim to load, and less time to iterate through all the non-empty rows and cells.

The function getFirstRowNum() behaves as expected and documented in the Javadocs.

By the way, the same is true for getLastRowNum().
Comment 2 Javen O'Neal 2017-07-26 05:45:04 UTC
Refer to the Spreadsheet Quick Guide and examples for how to handle worksheets that may contain empty rows or cells.

This is what you're looking for to return an empty row from a worksheet that does not contain that row:

static Row getOrCreateRow(Sheet sheet, int rowIndex) {
    Row row = sheet.getRow(2);
    if (row == null) {
        row = sheet.createRow(2);
    return row;

Conveniently, theres a function that is very similar to this in org.apache.poi.ss.util.CellUtil.