Finding the last row in an Excel spreadsheet.

Question

I'm trying to find the index of the last row in an excel spreadsheet using Apache's POI for Java.

I thought this should be possible with getLastRowNum() or getPhysicalNumberOfRows() but they don't seem to give the right results. For example, I have a one line spreadsheet and these two functions return a value of 1140. Another two line spreadsheets gets a value of 1162.

The other problem is that I cannot just look for the first empty row, since it may be possible to have empty rows between rows of valid data.

So is there a way to find the index of the last row? I suppose I could make it a requirement to not have empty rows between data, but I was hoping for a better solution.

Edit: For the record using an iterator didn't help. It just iterated over the 1140/1162 supposed rows.

Answer 1

I know how to solve your problem using VBA, but I'm not sure how to get the equivalent information from the Apache POI interface. In VBA, to get the range of used cells in worksheet "Sheet1", use:

Worksheets("Sheet1").UsedRange

This returns a Range object which has properties that provide further information. For example, to get the number of rows in this Range, use:

Worksheets("Sheet1").UsedRange.Rows

Again, I'm not sure whether this is acessible via the POI API, but if not, perhaps it provides a way of executing arbitrary snippets of VBA?

Answer 2

I get the expected output using poi-3.6-20091214 and a test.xls having two empty rows followed by three occupied rows:

InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());

Output: 4