Java 查找 Excel 电子表格中的最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2645566/
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
Finding the last row in an Excel spreadsheet
提问by FromCanada
I'm trying to find the index of the last row in an excel spreadsheet using Apache's POI for Java.
我正在尝试使用 Apache 的 Java POI 在 Excel 电子表格中查找最后一行的索引。
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.
我认为这应该可以使用getLastRowNum()orgetPhysicalNumberOfRows()但他们似乎没有给出正确的结果。例如，我有一个单行电子表格，这两个函数返回值 1140。另外两行电子表格的值是 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.
编辑：对于使用迭代器的记录没有帮助。它只是迭代了 1140/1162 假定的行。
回答by Dónal
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:
我知道如何使用 VBA 解决您的问题，但我不确定如何从 Apache POI 界面获取等效信息。在 VBA 中，要获取工作表“Sheet1”中使用的单元格范围，请使用：
Worksheets("Sheet1").UsedRange
This returns a Rangeobject which has properties that provide further information. For example, to get the number of rows in this Range, use:
这将返回一个Range对象，该对象具有提供更多信息的属性。例如，要获取 this 中的行数Range，请使用：
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?
同样，我不确定这是否可以通过 POI API 访问，但如果不是，也许它提供了一种执行任意 VBA 片段的方法？
回答by trashgod
I get the expected output using poi-3.6-20091214 and a test.xlshaving two empty rows followed by three occupied rows:
我使用 poi-3.6-20091214 获得预期输出，并且test.xls有两个空行，后跟三个被占用的行：
InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
Output: 4
输出： 4
回答by ParagJ
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 SVTableModelin the API. This has been removed since 3.2, I guess.
但是，我试图在 Apache POI 3.7 中执行此操作，但SVTableModel在 API 中找不到。我猜这已经从 3.2 删除了。
回答by user2318124
you can use the following method to get the original row count.
您可以使用以下方法获取原始行数。
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
回答by GreenGiant
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;
}
回答by zand
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();
回答by realheaven
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.
我以前也遇到过同样的问题。这可能是由 Excel 单元格在 Excel 中编辑然后清空引起的。一旦它们被触摸，它们就会显示为使用过的细胞。
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.
- 打开 Excel 文件并转到预期的工作表。
- 选择最后一行 + 1。例如，您有 12 行数据，然后单击第 13 行。
- 选择整行 [Shift]-[Space]
- 选择工作表底部的所有行 [Ctrl]-[Shift]-[向下箭头]
- 删除所有选中的行 [Ctrl]-[减号]
- 保存您的工作簿
- 重新运行代码并检查返回值。
This is not an issue of POI library.
这不是 POI 库的问题。
回答by Shiv Pratap
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.
对我来说，在任何情况下都没有任何效果，因为它适用于 HSSFWorkbook 但不适用于 XSSFWorkbook。最后在解决方法的帮助下，我能够解决这个问题。通过在工作表末尾合并两列或行（在您的内容完成后）。然后写下面的代码。
sheet.getMergedRegion(0).getLastRow()这里 0 只是我合并的一种情况，但如果您已经合并了单元格或行，则相应地增加您的值。希望这会有所帮助。
