如何在没有VBA的情况下获得图纸的使用范围

Question

我使用groovy脚本处理excel文件。我使用POI API来处理这些文件。但在文档中，没有任何方法或对象可以帮助我找到获得图纸使用范围的方法我试着用getLastRowNum()或getPhysicalNumberOfRows()这样的方法自己计算，但它们都不起作用，因为当遇到空行时就会停止计数。有时excel文件可以有空行，在这些空行之后可以是填充行，但当这些方法只遇到一个空行时，它们就会停止。所以这些功能不会帮助我达到目标

所以我尝试另一种解决方案。我想使用方法createName()在工作簿中创建一个命名区域，然后使用返回实际工作表的usedrange的公式创建一个名称区域。但我不知道如何制作它，我搜索了很多，发现的都是关于VBA的，我不想使用它，因为在命名范围公式中，我们不能使用VBA。我发现了一个函数调用GET.WORKBOOK，我认为这可能是一个很好的起点来搜索关于我的问题的答案。此函数返回工作簿的工作表名称列表。我的问题和这个结果之间没有联系，但我认为GET对象可能包含更多像GET.WORKSHEET这样的方法——这是非常推测的，但我觉得不仅仅是GET.WORKBOOK。(如果你对此有任何信息，即使它不能解决我的问题，也请把它放在评论中，我真的对这个GET功能感兴趣。(

注意：如果你能找到一种方法来解决我的问题，只使用一个很好的解决方案，我也会很高兴。我不记得这种类型的解决方案，因为我在这个方向上搜索了很多，但没有找到任何帮助。

NB2：我添加java标签是因为groovy和java非常接近。我认为，能够在java中找到解决这个问题的方法的人也可以在groovy中找到同样的方法。

NB3：我想要一个像A1:B2这样的单元格引用来指定使用的范围

NB4：我重新测试了方法getLastRowNum()，它运行得很好，我在代码中犯了一些错误，这就是它运行不好的原因。现在我的新问题是，当我使用此方法时，我无法访问getCell方法为空的单元格。这是我的代码：

import org.apache.poi.ss.usermodel.WorkbookFactory; 
wb = WorkbookFactory.create(new File("./webapps/etlserver/data/files/test_ws.xlsx"));
def getUsedRangeByIndex(file_path,ind_ws){
wb = WorkbookFactory.create(new File(file_path));
max_col = 0;
for(int i = 0 ; i < wb.getSheetAt(ind_ws).getLastRowNum() ; i++){
LOG.info(i.toString())
if(wb.getSheetAt(ind_ws).getRow(i) != null && wb.getSheetAt(ind_ws).getRow(i).getLastCellNum() > max_col){
max_col = wb.getSheetAt(ind_ws).getRow(i).getLastCellNum();
}
}

return "A1:" + wb.getSheetAt(ind_ws).getRow(wb.getSheetAt(ind_ws).getLastRowNum()).getCell(max_col, RETURN_NULL_AND_BLANK).getReference()
}
LOG.info(getUsedRangeByIndex("./webapps/etlserver/data/files/test_ws.xlsx",0))

我知道我必须用一些计算第一个使用的单元格的代码来改进它，但现在我将A1视为第一个使用过的单元格。

Answer 1

如果工作表的使用范围定义如下：。。。

使用范围是从第一个使用的左上角单元格到最后一个使用的右下角单元格的单元格范围。

并且使用的Apache POI版本是当前版本之一(我使用apache poi 5.2.2(，那么获得使用范围的最简单方法是使用以下方法：

Sheet.getFirstRowNum和Sheet.getLastRowNum以获取工作表中第一个使用的行和最后一个使用的列。如果其中一个返回-1，则工作表不包含任何行，因此没有已使用的范围。

然后在第一行和最后一行之间的所有行上循环，得到row.getFirstCellNum和row.getLastCellNum。注意Row.getLastCellNum的API文档：获取此行包含的最后一个单元格的索引PLUS ONE。如果该行中找到的第一列低于之前找到的第一行，则这是新的第一列。如果该行中查找到的最后一列大于查找到的前最后一列，则这是新的最后一行。

之后，我们有第一个使用的行，最后一个使用的列，最左边使用的列和最右边使用的列。这就是当时使用的范围。

完整示例：

import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
class ExcelGetSheetUsedRange {
/**
* Simplest method to get the used range from a sheet.
*
* @param sheet The sheet to get the used range from.
+ @return CellRangeAddress representing the used range or null for an empty sheet.
*/    
static CellRangeAddress getUsedRange(Sheet sheet) {
int firstRow = sheet.getFirstRowNum();
if (firstRow == -1) return null;
int lastRow = sheet.getLastRowNum();
if (lastRow == -1) return null;
int firstCol = Integer.MAX_VALUE;
int lastCol = -1;
for (int r = firstRow; r <= lastRow; r++) {
Row row = sheet.getRow(r);
if (row != null) {
int thisRowFirstCol = row.getFirstCellNum();
int thisRowLastCol = row.getLastCellNum()-1; // see API doc Row.getLastCellNum : Gets the index of the last cell contained in this row PLUS ONE.
if (thisRowFirstCol < firstCol) firstCol = thisRowFirstCol;
if (thisRowLastCol > lastCol) lastCol = thisRowLastCol;
}   
}
if (firstCol == Integer.MAX_VALUE) return null;
if (lastCol == -1) return null;
return new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
}  
public static void main(String[] args) throws Exception {
//Workbook workbook = WorkbookFactory.create(new FileInputStream("./template.xls"));  
Workbook workbook = WorkbookFactory.create(new FileInputStream("./template.xlsx"));   
Sheet sheet = workbook.getSheetAt(0);
CellRangeAddress usedRange = getUsedRange(sheet);
System.out.println(usedRange);  
}
}

如Sheet.getLastRowNum:的API文档中所述

注意：以前有内容但后来设置为空的行可能仍然被Excel和Apache POI计算为行。。。

但这是Excel的一个问题，当通过Worksheet.UsedRange属性获取使用的范围时也可能发生。

Answer 2

Axel Richter的解决方案是完美的。但这里有一个预构建代码，您可以直接插入到jedox作业中，使其正常工作。这是一种从java到groovy的翻译。这是代码：

import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
wb = WorkbookFactory.create(new File("./webapps/etlserver/data/files/test_ws.xlsx")); 
sheet = wb.getSheetAt(0);
def getUsedRange(sheet) {
firstRow = sheet.getFirstRowNum();
if (firstRow == -1) return null;
lastRow = sheet.getLastRowNum();
if (lastRow == -1) return null;
firstCol = Integer.MAX_VALUE;
lastCol = -1;
for (int r = firstRow; r <= lastRow; r++) {
row = sheet.getRow(r);
if (row != null) {
thisRowFirstCol = row.getFirstCellNum();
thisRowLastCol = row.getLastCellNum()-1;
if (thisRowFirstCol < firstCol) firstCol = thisRowFirstCol;
if (thisRowLastCol > lastCol) lastCol = thisRowLastCol;
}   
}
if (firstCol == Integer.MAX_VALUE) return null;
if (lastCol == -1) return null;

return (new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)).formatAsString();
}   
LOG.info(getUsedRange(sheet));