Java 如何使用 POI 库获取 Excel 文件中的行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16234486/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get row count in an Excel file using POI library?
提问by Ahmad
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 文件只有 1 行，则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 文件是否有 1 行数据，或者它是否为空。
So how can I detect if an Excel file is empty or not ?
那么如何检测 Excel 文件是否为空？
采纳答案by macias
回答by JavaGeek
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
这将确保您至少有一行包含除标题之外的数据。下面是我的程序，它运行良好。Excel 文件有三列，即。身、姓名、姓氏
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;
}
回答by Abhishek Singh
There are two Things you can do
你可以做两件事
use
用
int noOfColumns = sh.getRow(0).getPhysicalNumberOfCells();
or
或者
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)
Option 2 just gives you the index of last column. Hence done
'getLastCellNum()'
选项 1 给出了实际填充内容的列数（如果 10 列的第 2 列未填充，您将得到 9）
选项 2 只为您提供最后一列的索引。因此完成
'getLastCellNum()'
回答by MatthijsM
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()不计算空行，Sheet.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.
注意：这会将具有一个空行的电子表格视为没有，但对于大多数用途，这可能没问题。
回答by Singh Ss
To find last data row, in case you created table template in excel where it is filled partially or in between rows are empty. Logic:
要查找最后一个数据行，如果您在 excel 中创建了表格模板，其中部分填充或行之间为空。逻辑：
int count = 0;
int emptyrow=0;
int irow=0;
while (rowIterator.hasNext()) {
row = (Row) rowIterator.next();
if (count != 0 && !checkIfRowIsEmpty(row)) { }
else{
if(count!=0 && emptyrow==irow){
emptyrow++;
}else{
emptyrow=0;
irow=0;
}
}
if(emptyrow>0){
irow++;
}
if(emptyrow>3){
break;
}
count++;
}
回答by Shadab Khan
getLastRowNum() return index of last row.
getLastRowNum() 返回最后一行的索引。
So if you wants to know total number of row = getLastRowNum() +1.
因此，如果您想知道总行数 = getLastRowNum() +1。
I hope this will work.
我希望这会奏效。
int rowTotal = sheet.getLastRowNum() +1;
