1

All,

How can I find the last row in an excelsheet with data populated in the cell. i.e.

   | C1  | C2  | C3  | C4 |
---------------------------
R1 |  1      2    3      4  
R2 |  5      6    7      8  
R3 |  
R4 |        9

So in the example above, I wish to retrieve the total count of rows as 4 since Row number 4 has something populated.

Is there any such library function available? Also, what is the best Java library for handling Microsoft Office document operations?

name_masked
  • 9,544
  • 41
  • 118
  • 172

2 Answers2

8

With Apache POI, you can use getLastRowNum().

Something like this example:

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

From this SO thread: Finding the last row in an Excel spreadsheet

Community
  • 1
  • 1
JMax
  • 26,109
  • 12
  • 69
  • 88
  • I get this error "java.io.IOException: Unable to read entire header; 0 bytes read; expected 512 bytes" when I use above snippet. – happy Jul 02 '12 at 09:21
4

The most common Java library for dealing with Excel documents is Apache POI.

http://poi.apache.org/

They will have functions to iterate rows in Excel like what you need. Check out the examples, they are extremely straightforward.

Once you get it working, use your HSSFSheet object and call: http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#getLastRowNum%28%29

One you have the LastRowNum, then iterate through all the rows and then if the row contains a value, you want to say that it is the last row. If the only thing you need to do is fine the last row, you might want to start from the LastRowNum and work your way backwards.

thait84
  • 198
  • 6