poi中getPhysicalNumberOfRows()和getLastRowNum()区别

http://apache-poi.1045710.n5.nabble.com/Difference-of-getLastRowNum-and-getPhysicalNumberOfRows-td5723176.html

getLastRowNum() retrieves the index of the last row in a spreadsheet. These indexes are 0 based, and if you have no missing rows, row 1104 the last row will have index 1103. getPhysicalNumberOfRows() returns the number of rows actually used in the spreadsheet. Unused rows are just missing from the spreadsheet. So you could have a situation where you only have one row at row 1104. In that case getPhysicalNumberOfRows() would return 1, but getLastRowNum() would return 1103. 

 

In the past I use following code to display first cell string of all rows.

Java

 

1 2 3 4 5 6

for(int i=0; i<sheet.getPhysicalNumberOfRows(); i++){             Row row = sheet.getRow(i);             if(row == null || row.getCell(0) == null){                 System.out.println(row.getCell(0).getStringCellValue());             }         }

But above code will be incorrect if there is empty row.

 

Following is explanation from POI official documentation

Sheet.getPhysicalNumberOfRows()

Returns the number of physically defined rows (NOT the number of rows in the sheet)

This method will ignore empty rows, e.g. if a sheet last row is at row 7, but second row is empty, getPhysicalNumberOfRows()  will return 6.

 

Solution

To get row count of a sheet (no matter the row is empty or not), we should usegetLastRowNum()  method.

So above code can be changed to

Java

 

1 2 3 4 5 6

for(int i=0; i<=sheet.getLastRowNum(); i++){             Row row = sheet.getRow(i);             if(row == null || row.getCell(0) == null){                 System.out.println(row.getCell(0).getStringCellValue());             }         }

Because getLastRowNum()  method returns 0-based row index, so we usei<=sheet.getLastRowNum()  as the loop condition.

 

getLastRowNum  

    如果sheet中一行数据都没有则返回-1，只有第一行有数据则返回0，最后有数据的行是第n行则返回 n-1；

getLastCellNum   

    如果   row中一列数据都没有则返回-1，只有第一列有数据则返回1，最后有数据的列是第n列则返回 n；

getPhysicalNumberOfRows  

    获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m；

getPhysicalNumberOfCells   

    获取有记录的列数，即：最后有数据的列是第n列，前面有m列是空列没数据，则返回n-m；

奇怪的是getLastRowNum和getLastCellNum的逻辑不一致，根据方法命名的话，应该是返回最后一行（或一列）的行（列）数。

如果没有行或列应该返回0，而不应该返回-1。而且getLastRowNum  返回的是最后一行的索引而不是最后一行的行数，getLastCellNum   则是返回的最后一列的列数。

public void test() throws UnsupportedEncodingException{
        String filePath = InfoImport.class.getResource("test.xls").getPath();
        filePath = filePath.substring(1);
        filePath = URLDecoder.decode(filePath, "utf-8");
        File xlsfile = new File(filePath);
         try(FileInputStream is = new FileInputStream(xlsfile)) {  
                //同时支持Excel 2003、2007     
                Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的  
                Sheet sheet = workbook.getSheetAt(0);  
                System.out.println("getPhysicalNumberOfRows:"+sheet.getPhysicalNumberOfRows());
                System.out.println("getLastRowNum:"+sheet.getLastRowNum());
                //int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
                int rowCount = sheet.getLastRowNum() + 1; //获取总行数
                for (int i = 0; i < rowCount; i++) {  
                    Row row = sheet.getRow(i);  
                    int cellCount = 0;
                    if (row != null) {//row  该行所有单元格为空时，row是null值    2017-01-05 pelin
                        //cellCount = row.getPhysicalNumberOfCells(); //获取总列数  
                        cellCount = row.getLastCellNum();//获取最后一个不为空的列是第几个。 
                        System.out.println( i +"索引行getPhysicalNumberOfCells:" + row.getPhysicalNumberOfCells());
                        System.out.println( i +"索引行getLastCellNum:" + cellCount);
                        
                        cellCount = cellCount < 0 ? 0 : cellCount;//getLastCellNum没有单元格时会返回负数
                    }
                }  
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
    }