Bug 53517 - Row iteration on formatted Sheet raise Nullpointer Exception
Summary: Row iteration on formatted Sheet raise Nullpointer Exception
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-07-07 11:21 UTC by Ralf Rublack
Modified: 2012-07-07 13:26 UTC (History)
0 users

formatted Template (49.51 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-07-07 11:21 UTC, Ralf Rublack

Note You need to log in before you can comment on or make changes to this bug.
Description Ralf Rublack 2012-07-07 11:21:17 UTC
Created attachment 29037 [details]
formatted Template

If using a formatted sheet as template and e.g setting row.height at Row 510
raise nullpointer exception when iterate over rows using LastRowNr.

for (int i=0; i <= workbook.getSheet("template").getLastRowNum(); i++)
Row actRow = workbook.getSheet("template").getRow(i);

because it looks like a empty sheet hast normally only 500 rows and formatting 
a row after 500 at this row to the row list. 
Now the row treeMap has 501 entries, with keys 0 to 499 and the additional key 510 
from the formatted row as entry 501 .
Calling workbook.getSheetAt(0).getLastRowNum() get the key 510 and iterate like above raise Nullpointer in workbook.getSheetAt(0).getRow(i) because keys 500 to 509 are not in the treeMap.

I put the template file as attachment
Comment 1 Nick Burch 2012-07-07 11:37:00 UTC
The Excel file format is a sparse format. If rows aren't used, and have never been used, then they'll never be present in the file

If you only want defined rows, then use the RowIterator on a sheet. If you you iterate between the FirstRowNum and LastRowNum, then you need to check that the row isn't null before using it. See the website for more info on all of this
Comment 2 Ralf Rublack 2012-07-07 13:26:56 UTC
Hi Nick,
thx for your hint