Apache POI reliable way to determine last row

Question

I'm using Apache POI to read data from an Excel spreadsheet and sending a POST to a remote service.

While testing my implementation I found out it blew up with an exception when my method tried to parse an empty string into a BigDecimal. I discovered this is due to the fact that, according to the Apache POI documentation, getLastRowNum() may

rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

And lo and behold, it did return a bigger number than I had initially anticipated, forcing me to use the value in the first cell in the row as the exit condition for my loop, something like the following:

String productCode= excelMapper.getValueFromRow(r, "product_code");

while (productCode!= null && !productCode.isEmpty()) {
    MyBeanClass bean = new MyBeanClass ();
    r = sheet.getRow(i);

    productCode = excelMapper.getValueFromRow(r, "cod_prodotto");
    yada yada....
}

This works for my purposes, but I was wondering if there was a better way.

Given that any row could be null, and any cell in there blank or null, it's best to work your way up/down until you find the last row you consider "valid" to count off — Gagravarr
Thank you a lot. If you want to post this comment as an answer I'll gladly mark it as best answer. — Massimo Di Saggio

Gagravarr Gagravarr · Accepted Answer · 2020-04-04T18:46:59

Promoting a comment to an answer...

Excel files are generally stored "sparse" so with gaps for rows with no data in. So, any row in an Excel file you read back with Apache POI could be null. Equally, a row could be styled but have no data in, which means you'd get it back if you asked for it, but without values as you might expect.

So, for your case, you probably want to start at the last row, and work backwards. For each row, check if it is null. If it isn't null, grab a few cells, being aware that those could be null too! Check those, and if enough is present/valid, consider that the "last valid row". If not, go one row higher. Repeat until you find something you consider valid!

Apache POI reliable way to determine last row

1 Answers