如何使用POI库获取Excel文件中的行数?

Guys I'm currently using the POI 3.9 library to work with excel files. I know of the getLastRowNum() function, which returns a number of rows in an Excel file.

我现在正在使用POI 3.9库来处理excel文件。我知道getLastRowNum()函数，它返回Excel文件中的许多行。

The only problem is getLastRowNum() returns a number with the count starting from 0.

唯一的问题是getLastRowNum()返回一个数字，计数从0开始。

So if an Excel file uses the first 3 rows, getLastRowNum() returns 2. If an Excel file has just 1 row, getLastRowNum() returns 0.

因此，如果Excel文件使用前3行，getLastRowNum()返回2。如果一个Excel文件只有一行，那么getLastRowNum()返回0。

The problem occurs when the Excel file is completely empty. getLastRowNum() still returns 0, so I cannot determine if the Excel file has 1 row of data, or if its empty.

当Excel文件完全为空时，就会出现问题。getLastRowNum()仍然返回0，因此我无法确定Excel文件是否有一行数据，或者它是否为空。

So how can I detect if an Excel file is empty or not ?

那么如何检测Excel文件是否为空呢?

4 个解决方案

#1

Try Sheet.getPhysicalNumberOfRows()

尝试Sheet.getPhysicalNumberOfRows()

#2

If you do a check

如果你检查一下

if
(getLastRowNum()<1){
 res="Sheet Cannot be empty";
return
}

This will make sure you have at least one row with data except header. Below is my program which works fine. Excel file has three columns ie. ID, NAME , LASTNAME

这将确保除了header外，至少有一行数据。下面是我的程序，运行良好。Excel文件有三列ie。ID、名称、LASTNAME

XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Row header = sheet.getRow(0);
        int n = header.getLastCellNum();
        String header1 = header.getCell(0).getStringCellValue();
        String header2 = header.getCell(1).getStringCellValue();
        String header3 = header.getCell(2).getStringCellValue();
        if (header1.equals("ID") && header2.equals("NAME")
                && header3.equals("LASTNAME")) {
            if(sheet.getLastRowNum()<1){
                System.out.println("Sheet empty");
                         return;
            }   
                        iterate over sheet to get cell values
        }else{
                          SOP("invalid format");
                          return;
                          }

#3

There are two Things you can do

你可以做两件事

use

使用

int noOfColumns = sh.getRow(0).getPhysicalNumberOfCells();

或

int noOfColumns = sh.getRow(0).getLastCellNum();

There is a fine difference between them

他们之间有细微的差别

Option 1 gives the no of columns which are actually filled with contents(If the 2nd column of 10 columns is not filled you will get 9)

选项1给出了实际填充了内容的列的no(如果10列的第二列没有填充，将得到9)
Option 2 just gives you the index of last column. Hence done 'getLastCellNum()'

选项2给出了上一列的索引。因此做getLastCellNum()”

#4

Since Sheet.getPhysicalNumberOfRows() does not count empty rows and Sheet.getLastRowNum() returns 0 both if there is one row or no rows, I use a combination of the two methods to accurately calculate the total number of rows.

由于Sheet.getPhysicalNumberOfRows()不计算空行，并且如果有一行或没有行，getlastrownum()返回0，因此我使用这两种方法的组合来精确计算总行数。

int rowTotal = sheet.getLastRowNum();

if ((rowTotal > 0) || (sheet.getPhysicalNumberOfRows() > 0)) {
    rowTotal++;
}

Note: This will treat a spreadsheet with one empty row as having none but for most purposes this is probably okay.

注意:这将把一个空行作为没有的电子表格处理，但是在大多数情况下这是可以的。

#1