在Excel电子表格中查找最后一行
•浏览 1
Finding the last row in an Excel spreadsheet
我正在尝试使用 Apache\\'s POI for Java 在 Excel 电子表格中查找最后一行的索引。
我认为使用 getLastRowNum() 或 getPhysicalNumberOfRows() 应该可以做到这一点，但它们似乎没有给出正确的结果。例如，我有一个单行电子表格，这两个函数返回值 1140。另外两行电子表格的值是 1162。
另一个问题是我不能只查找第一个空行，因为在有效数据行之间可能有空行。
那么有没有办法找到最后一行的索引呢？我想我可以要求数据之间没有空行，但我希望有更好的解决方案。
编辑：对于使用迭代器的记录没有帮助。它只是遍历了 1140/1162 假定的行。
我使用 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());
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
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;
}
}
}
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;
}
Worksheets("Sheet1").UsedRange
Worksheets("Sheet1").UsedRange.Rows
Iterator<Row> itr = sheet.iterator(); //iterating over excel file
while (itr.hasNext())
{
Row row = itr.next();
//your code here
}
SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();输出：4
您可以使用以下方法获取原始行数。
InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
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;
}
}
}
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;
}
Worksheets("Sheet1").UsedRange
Worksheets("Sheet1").UsedRange.Rows
Iterator<Row> itr = sheet.iterator(); //iterating over excel file
while (itr.hasNext())
{
Row row = itr.next();
//your code here
}
SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();我以前也遇到过同样的问题。这可能是由于 Excel 单元格已被编辑然后在 Excel 中清空所致。一旦它们被触摸，它们就会显示为使用过的单元格。
我使用这个技巧来删除(不仅仅是清空)那些单元格，并获得正确的返回行值：
- 打开 Excel 文件并转到预期的工作表。
- 选择最后一行 1。例如，您有 12 行数据，然后单击第 13 行。
- 选择整行 [Shift]-[Space]
- 选择工作表底部的所有行 [Ctrl]-[Shift]-[向下箭头]
- 删除所有选中的行 [Ctrl]-[Minus]
- 保存您的工作簿
- 重新运行代码并检查返回值。
这不是 POI 库的问题。
确定的唯一方法是测试行。这是我用于相同问题的解决方案：
InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
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;
}
}
}
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;
}
Worksheets("Sheet1").UsedRange
Worksheets("Sheet1").UsedRange.Rows
Iterator<Row> itr = sheet.iterator(); //iterating over excel file
while (itr.hasNext())
{
Row row = itr.next();
//your code here
}
SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();注意：这不会检查看似为空但实际上不是空的行，例如其中包含空字符串的单元格。为此，您需要一个更完整的解决方案，例如：
InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
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;
}
}
}
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;
}
Worksheets("Sheet1").UsedRange
Worksheets("Sheet1").UsedRange.Rows
Iterator<Row> itr = sheet.iterator(); //iterating over excel file
while (itr.hasNext())
{
Row row = itr.next();
//your code here
}
SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();我知道如何使用 VBA 解决您的问题，但我不确定如何从 Apache POI 界面获取等效信息。在 VBA 中，要获取工作表"Sheet1"中使用的单元格范围，请使用：
InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
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;
}
}
}
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;
}
Worksheets("Sheet1").UsedRange
Worksheets("Sheet1").UsedRange.Rows
Iterator<Row> itr = sheet.iterator(); //iterating over excel file
while (itr.hasNext())
{
Row row = itr.next();
//your code here
}
SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();这将返回一个 Range 对象，该对象具有提供更多信息的属性。例如，要获取此 Range 中的行数，请使用：
InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
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;
}
}
}
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;
}
Worksheets("Sheet1").UsedRange
Worksheets("Sheet1").UsedRange.Rows
Iterator<Row> itr = sheet.iterator(); //iterating over excel file
while (itr.hasNext())
{
Row row = itr.next();
//your code here
}
SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();同样，我不确定这是否可以通过 POI API 访问，但如果不能，也许它提供了一种执行任意 VBA 片段的方法？
使用迭代器不会返回空行和未使用的行
InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
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;
}
}
}
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;
}
Worksheets("Sheet1").UsedRange
Worksheets("Sheet1").UsedRange.Rows
Iterator<Row> itr = sheet.iterator(); //iterating over excel file
while (itr.hasNext())
{
Row row = itr.next();
//your code here
}
SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();对我来说，在任何情况下都没有任何效果，因为它适用于 HSSFWorkbook，但不适用于 XSSFWorkbook。
最后在解决方法的帮助下，我能够解决这个问题。
通过在工作表末尾合并两列或两行(在您的内容完成后)。
然后写下面的代码。
sheet.getMergedRegion(0).getLastRow()
这里 0 只是我合并的一种情况，但如果您已经合并了单元格或行，则相应地增加您的值。
希望这会有所帮助。
您可以通过以下代码做到这一点：
InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
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;
}
}
}
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;
}
Worksheets("Sheet1").UsedRange
Worksheets("Sheet1").UsedRange.Rows
Iterator<Row> itr = sheet.iterator(); //iterating over excel file
while (itr.hasNext())
{
Row row = itr.next();
//your code here
}
SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();但是，我试图在 Apache POI 3.7 中执行此操作，但在 API 中找不到 SVTableModel。我猜这从 3.2 开始就被删除了。
InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
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;
}
}
}
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;
}
Worksheets("Sheet1").UsedRange
Worksheets("Sheet1").UsedRange.Rows
Iterator<Row> itr = sheet.iterator(); //iterating over excel file
while (itr.hasNext())
{
Row row = itr.next();
//your code here
}
SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();