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.

There is a reason all of POI's classes for Excel are called HSSF... — ig0774, Apr 15 '10 at 13:26

score 20 · Answer 1 · answered Apr 15 '10 at 16:34

20

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

answered Apr 15 '10 at 16:34

trashgod

203,806
29
246
1,045

Actually, after checking again I noticed I was getting the correct result on some of the spreadsheets out of the hundred or so that I was using. The only difference seemed that the ones which didn't work only had one renamed worksheet. I tried testing a new workbook and it did give the right result. However, I wasn't able to reproduce the error by renaming the worksheet and deleting the others, sothe source of the problem is still unknown =/ – FromCanada Apr 15 '10 at 17:33
Any way to cull the errant books for manual attention by checking for a mismatch? – trashgod Apr 16 '10 at 01:04
2

this will work in normal case..but wont work when you run the code for same excel after deleting few last rows...sheet.getLastRowNum will give the previous number of rows only... – suvarna Chavan Sep 21 '16 at 13:15

score 10 · Answer 2 · edited Jun 11 '13 at 13:12

10

you can use the following method to get the original row count.

HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();

edited Jun 11 '13 at 13:12

Avadhani Y

7,566
19
63
90

answered Jun 11 '13 at 12:56

user2318124

101
1
2

2

This doesn't work if there are empty rows at the top or interspersed in the data. – fortyCakes Aug 10 '18 at 11:20
whats the alternative in that case? if the first row is empty – Yougandhara Aug 02 '21 at 10:31

score 8 · Answer 3 · edited Jan 31 '19 at 08:05

I had the same problem before. It could be caused by Excel cells that have been edited and then emptied in Excel. Once they've been touched, they appear as used cells.

I use this trick to delete (not just empty) those cells, and get the right returned row value:

Open Excel file and go the expected sheet.
Select the last row + 1. E.g you have 12 rows with data, then click on row 13.
Select the entire row [Shift]-[Space]
Select all rows to the bottom of the sheet [Ctrl]-[Shift]-[Arrow down]
Delete all selected rows [Ctrl]-[Minus]
Save your workbook
Rerun the code and check returned value.

This is not an issue of POI library.

score 2 · Answer 4 · answered May 29 '15 at 21:30

The only way to know for sure is to test the rows. Here's the solution I'm using for the same problem:

int lastRowIndex = -1;
if( sheet.getPhysicalNumberOfRows() > 0 )
{
    // getLastRowNum() actually returns an index, not a row number
    lastRowIndex = sheet.getLastRowNum();

    // now, start at end of spreadsheet and work our way backwards until we find a row having data
    for( ; lastRowIndex >= 0; lastRowIndex-- ){
        Row row = sheet.getRow( lastRowIndex );
        if( row != null ){
            break;
        }
    }
}

Note: this doesn't check for rows that appear to be empty but aren't, such as cells that have an empty string in them. For that, you need a more complete solution like:

private int determineRowCount()
{
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.formatter = new DataFormatter( true );

    int lastRowIndex = -1;
    if( sheet.getPhysicalNumberOfRows() > 0 )
    {
        // getLastRowNum() actually returns an index, not a row number
        lastRowIndex = sheet.getLastRowNum();

        // now, start at end of spreadsheet and work our way backwards until we find a row having data
        for( ; lastRowIndex >= 0; lastRowIndex-- )
        {
            Row row = sheet.getRow( lastRowIndex );
            if( !isRowEmpty( row ) )
            {
                break;
            }
        }
    }
    return lastRowIndex;
}

/**
 * Determine whether a row is effectively completely empty - i.e. all cells either contain an empty string or nothing.
 */
private boolean isRowEmpty( Row row )
{
    if( row == null ){
        return true;
    }

    int cellCount = row.getLastCellNum() + 1;
    for( int i = 0; i < cellCount; i++ ){
        String cellValue = getCellValue( row, i );
        if( cellValue != null && cellValue.length() > 0 ){
            return false;
        }
    }
    return true;
}

/**
 * Get the effective value of a cell, formatted according to the formatting of the cell.
 * If the cell contains a formula, it is evaluated first, then the result is formatted.
 * 
 * @param row the row
 * @param columnIndex the cell's column index
 * @return the cell's value
 */
private String getCellValue( Row row, int columnIndex )
{
    String cellValue;
    Cell cell = row.getCell( columnIndex );
    if( cell == null ){
        // no data in this cell
        cellValue = null;
    }
    else{
        if( cell.getCellType() != Cell.CELL_TYPE_FORMULA ){
            // cell has a value, so format it into a string
            cellValue = this.formatter.formatCellValue( cell );
        }
        else {
            // cell has a formula, so evaluate it
            cellValue = this.formatter.formatCellValue( cell, this.evaluator );
        }
    }
    return cellValue;
}

The Idea behind this is good (too bad POI makes it that hard to do such a simple thing). Two Annotations: 1. ```int cellCount = row.getLastCellNum() + 1;``` you start at a cell index of maxcellindex+1? and 2nd: ```determineRowCount``` returns the index of the last filled row, so it needs to be either renamed or you have to add 1 to that number to get the actual count. — FrankM, Feb 22 '19 at 09:18

score 1 · Answer 5 · answered Apr 15 '10 at 13:39

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?

At an initial glance I don't see anything similar to the VBA solution. Not too sure about the executing snippets of VBA either. — FromCanada, Apr 15 '10 at 14:24

score 0 · Answer 6 · answered Feb 27 '19 at 18:06

For me nothing worked in any case as it is working for HSSFWorkbook but not in XSSFWorkbook. Finally with the help of workaround I am able to solve this problem. By merging two columns or rows (after your content finishes) at the end of your sheet. Then write below code. sheet.getMergedRegion(0).getLastRow() Here 0 is only one case where I have merged but if you have already merged cells or rows then increment your value accordingly. Hope this will help.

score 0 · Answer 7 · answered Oct 20 '11 at 08:59

You can do this by the following code:

SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();

However, I am trying to do this in Apache POI 3.7 but couldn't find the SVTableModel in the API. This has been removed since 3.2, I guess.

score -1 · Answer 8 · answered Jun 30 '20 at 07:05

using iterator will not return null and unused rows

           Iterator<Row> itr = sheet.iterator();    //iterating over excel file  
            

            while (itr.hasNext())                 
            {  
                Row row = itr.next();
                //your code here 
            }

Finding the last row in an Excel spreadsheet

8 Answers8

Linked