blocks|key|1978866|text|我使用poi-3.6-20091214和一个后跟两个空行和三个占用行的test.xls获得了预期的输出：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1978867|InputStream+myxls+=+new+FileInputStream("test.xls");
Workbook+book+=+new+HSSFWorkbook(myxls);
Sheet+sheet+=+book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());|code-block|syntax|javascript|1978868|输出：4|1978869|entityMap^0|Z|8|0|0|3|1|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

I get the expected output using poi-3.6-20091214 and a <code>test.xls</code> having two empty rows followed by three occupied rows:

<pre><code>InputStream myxls = new FileInputStream("test.xls");
Workbook book = new HSSFWorkbook(myxls);
Sheet sheet = book.getSheetAt(0);
System.out.println(sheet.getLastRowNum());
</code></pre>

Output: <code>4</code>

blocks|key|1978899|text|您可以使用以下方法获取原始行数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1978900|HSSFSheet+worksheet+=+workbook.getSheet("Role_Mapping");
int+rowsNum+=+worksheet.getPhysicalNumberOfRows();|code-block|syntax|javascript|1978901|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can use the following method to get the original row count.

<pre><code>HSSFSheet worksheet = workbook.getSheet("Role_Mapping");
int rowsNum = worksheet.getPhysicalNumberOfRows();
</code></pre>

blocks|key|4089384|text|我以前也遇到过同样的问题。它可能是由在Excel中编辑然后清空的Excel单元格引起的。一旦它们被触摸，它们就会显示为使用过的单元。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4089385|我使用这个技巧删除(而不仅仅是清空)这些单元格，并获得正确的返回行值：|4089386|4089387|打开Excel文件，转到预期的工作表。|ordered-list-item|4089388|选择最后一行%2B+1。例如，您有12行数据，然后单击第13行。|4089389|选择工作表底部的整个行Ctrl-Shift-向下箭头并删除代码的所有选定行，然后检查返回值。|4089390|<|4089391|>G215>|4089392|4089393|这不是POI库的问题。|4089394|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|X|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|G|7|Z|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|10|8|@]|9|@]|A|$]]|$1|N|3|O|5|G|7|11|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

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.

I use this trick to delete (not just empty) those cells, and get the right returned row value:

<ol>
<li>Open Excel file and go the expected sheet.</li>
<li>Select the last row + 1. E.g you have 12 rows with data, then click on row 13.</li>
<li>Select the entire row [Shift]-[Space]</li>
<li>Select all rows to the bottom of the sheet [Ctrl]-[Shift]-[Arrow down]</li>
<li>Delete all selected rows [Ctrl]-[Minus]</li>
<li>Save your workbook</li>
<li>Rerun the code and check returned value.</li>
</ol>

This is not an issue of POI library.

blocks|key|5507129|text|唯一确定的方法是测试这些行。下面是我用来解决同样问题的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5507130|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;
++++++++}
++++}
}|code-block|syntax|javascript|5507131|注意:这不会检查看似为空但不是空的行，例如其中包含空字符串的单元格。为此，您需要一个更完整的解决方案，如：|5507132|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()+%2B+1;
++++for(+int+i+=+0;+i+<+cellCount;+i%2B%2B+){
++++++++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;
}|5507133|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

The only way to know for sure is to test the rows. Here's the solution I'm using for the same problem:

<pre><code>int lastRowIndex = -1;
if( sheet.getPhysicalNumberOfRows() &gt; 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 &gt;= 0; lastRowIndex-- ){
 Row row = sheet.getRow( lastRowIndex );
 if( row != null ){
 break;
 }
 }
}
</code></pre>

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:

<pre><code>private int determineRowCount()
{
 this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
 this.formatter = new DataFormatter( true );

 int lastRowIndex = -1;
 if( sheet.getPhysicalNumberOfRows() &gt; 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 &gt;= 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 &lt; cellCount; i++ ){
 String cellValue = getCellValue( row, i );
 if( cellValue != null &amp;&amp; cellValue.length() &gt; 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;
}
</code></pre>

blocks|key|1978840|text|我知道如何使用VBA解决您的问题，但是我不确定如何从Apache+POI接口获得相同的信息。在VBA中，要获取工作表"Sheet1“中使用的单元格的范围，请使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1978841|Worksheets("Sheet1").UsedRange|code-block|syntax|javascript|1978842|这将返回一个Range对象，该对象具有可提供进一步信息的属性。例如，要获取此Range中的行数，请使用：|offset|length|style|CODE|1978843|Worksheets("Sheet1").UsedRange.Rows|1978844|同样，我不确定这是否可以通过POI+API访问，但如果不能，也许它提供了一种执行VBA的任意代码段的方法？|1978845|entityMap^0|0|0|6|5|12|5|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]|$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

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:

<pre><code>Worksheets("Sheet1").UsedRange
</code></pre>

This returns a <code>Range</code> object which has properties that provide further information. For example, to get the number of rows in this <code>Range</code>, use:

<pre><code>Worksheets("Sheet1").UsedRange.Rows
</code></pre>

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?

blocks|key|4089276|text|您可以通过以下代码来完成此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4089277|SVTableModel+model+=+new+SVTableModel(sheet);
lastRowNum+=+model.getRowCount();|code-block|syntax|javascript|4089278|但是，我正尝试在Apache+POI3.7中执行此操作，但在API中找不到SVTableModel。我想这是从3.2版本开始就被删除了。|offset|length|style|CODE|4089279|entityMap^0|0|0|11|C|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You can do this by the following code:

<pre><code>SVTableModel model = new SVTableModel(sheet);
lastRowNum = model.getRowCount();
</code></pre>

However, I am trying to do this in Apache POI 3.7 but couldn't find the <code>SVTableModel</code> in the API. This has been removed since 3.2, I guess.

blocks|key|5507165|text|对我来说，在任何情况下都不起作用，因为它在HSSFWorkbook上有效，但在XSSFWorkbook中不起作用。最后，在变通方法的帮助下，我能够解决这个问题。通过合并工作表末尾的两列或两行(在内容完成后)。然后编写下面的代码。sheet.getMergedRegion(0).getLastRow()这里0只是我合并的一种情况，但如果你已经合并了单元格或行，那么就相应地递增你的值。希望这能有所帮助。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5507166|entityMap^0|36|11|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

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.
 <code>sheet.getMergedRegion(0).getLastRow()</code>
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.

blocks|key|1979036|text|使用迭代器不会返回null和未使用的行|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1979037|+++++++++++Iterator<Row>+itr+=+sheet.iterator();++++//iterating+over+excel+file++
++++++++++++

++++++++++++while+(itr.hasNext())+++++++++++++++++
++++++++++++{++
++++++++++++++++Row+row+=+itr.next();
++++++++++++++++//your+code+here+
++++++++++++}|code-block|syntax|javascript|1979038|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

using iterator will not return null and unused rows
<pre><code> Iterator&lt;Row&gt; itr = sheet.iterator(); //iterating over excel file 
 

 while (itr.hasNext()) 
 { 
 Row row = itr.next();
 //your code here 
 }
</code></pre>

blocks|key|1978945|text|int+total+=+sheet.getPhysicalNumberOfRows()+-+sheet.getLastRowNum();|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1978946|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>int total = sheet.getPhysicalNumberOfRows() - sheet.getLastRowNum();
</code></pre>

I'm trying to find the index of the last row in an excel spreadsheet using Apache's POI for Java.

I thought this should be possible with <code>getLastRowNum()</code> or <code>getPhysicalNumberOfRows()</code> 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.

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.

Finding the last row in an Excel spreadsheet

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我正在尝试使用Apache的POI for Java查找excel电子表格中最后一行的索引。我认为使用getLastRowNum()或getPhysicalNumberOfRows()应该可以做到这一点，但它们似乎没有给出正确的结果。例如，我有一个一行的电子表格，这两个函数返回值1140。另外两行电子表格的值为1162。另一个问题是，我不能只查找第一个空行，因为在有效数据的行之间可能有空行。那么有

问查找Excel电子表格中的最后一行
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查找Excel电子表格中的最后一行EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查找Excel电子表格中的最后一行
EN