apache-poi - 加速 Apache POI SUMIF

Question

在 xlsx 工作簿中，有一些单元格带有一些无界 SUMIF 公式，如下所示:SUMIF(MySheetname!$B:$B,$E4,MySheetname!$I:$I) .
使用 Apache POI 5.0.0 对一个 SUMIF 函数的评估持续 100 毫秒，而对给定工作簿的评估持续几分钟。
改进执行持续时间的一种方法是将公式绑定(bind)到如下内容:SUMIF(MySheetname!$B1:$B100,$E4,MySheetname!$I1:$I100) .在我的情况下，这不是一个解决方案，因为我不是 xlsx 文件的作者，并且系统从未知的人那里获取未知的 xlsx 文件(所以我不能只是告诉他们限制 SUMIF 范围)。org.apache.poi.ss.formula.functions.Sumif的当前执行情况迭代给定(无界)范围内的所有单元格，因此每次评估都会迭代 1048576 个单元格。
这是方法 sumMatchingCells(AreaEval, I_MatchPredicate, AreaEval) 实现的一部分:

for (int r=0; r<height; r++) {
    for (int c=0; c<width; c++) {
        result += accumulate(aeRange, mp, aeSum, r, c);
    }
}

我想通过检查行或列是否实际存在于总和范围内来提高此方法的性能。也许是这样的(使用不存在的方法 sheetContainsRowIndex ):

for (int r = 0; r < height; r++) {
    if (aeSum.sheetContainsRowIndex(aeSum.getFirstRow() + r)) {
        for (int c = 0; c < width; c++) {
            if (aeSum.sheetContainsColumnIndex(aeSum.getFirstColumn() + c)) {
               [...]

LazyAreaEval包含 SheetRangeEvaluator这包含 SheetRefEvaluator s 并且这些包含 EvaluationSheet这至少知道 getLastRowNum() .不幸的是，这个属性链是私有(private)的。
知道如何实现这一目标吗？或者任何其他想法如何提高 SUMIF 执行的性能？

Answer 1

修补 apache poi公式评估需要深入了解来源，并在评估过程中进行翻找。那不是我会做的。
但解决方法可能是在评估之前将公式中的所有完整列引用替换为从第 1 行到工作表中最后一行的区域引用。
如果您只阅读工作簿，那么这只会影响随机存取内存，而不会影响存储的文件。当然，如果您需要保存更改后的工作簿，那么它会影响存储的文件。那么该解决方法可能无法使用。
当工作表中有多个具有完整列引用的公式时，这对过程持续时间有显着影响，至少使用 *.xlsx ( XSSF ) 并且虽然需要为每个公式执行额外的替换过程。
完整代码示例:

import java.io.FileInputStream;

import org.apache.poi.ss.formula.*;
import org.apache.poi.ss.formula.ptg.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.SpreadsheetVersion;

public class ExcelEvaluateFullColumnFormulas {

 private static String replaceFullColumnReferences(XSSFSheet sheet, String formula) {
  //System.out.println(formula);
     
  XSSFWorkbook workbook = sheet.getWorkbook();
  XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.create(workbook);
  
  Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook)evaluationWorkbook, 
   FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet));
   
  for (int i = 0; i < ptgs.length; i++) {
   if (ptgs[i] instanceof AreaPtgBase) { // the operand Ptg is an area reference
    AreaPtgBase ref = (AreaPtgBase) ptgs[i];
    if (ref.getFirstRow() == 0 && ref.getLastRow() == SpreadsheetVersion.EXCEL2007.getLastRowIndex()) { // only for full column area references
     int lastRowInSheet = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
     if (ref instanceof Area2DPtgBase) { // the area reference is a 2D area reference in same sheet
      lastRowInSheet = sheet.getLastRowNum(); // get last row of this sheet
     } else if (ref instanceof Area3DPxg) { // the area reference is a 3D area reference in another sheet
      Area3DPxg ref3D = (Area3DPxg)ref; 
      String sheetName = ref3D.getSheetName();
      lastRowInSheet = workbook.getSheet(sheetName).getLastRowNum(); // get last row of referenced sheet
     }      
     ref.setLastRow(lastRowInSheet);
     formula = FormulaRenderer.toFormulaString((FormulaRenderingWorkbook)evaluationWorkbook, ptgs);
    }
   }
  }
  //System.out.println(formula);
  return formula;
  
 }

 public static void main(String[] args) throws Exception {

  DataFormatter formatter = new DataFormatter();
  Workbook workbook = WorkbookFactory.create(new FileInputStream("test.xlsx"));  
  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);
  java.time.LocalDateTime startTime = java.time.LocalDateTime.now();
  for (Row row : sheet) {
   for (Cell cell : row) {
    ///*
    if (cell.getCellType() == CellType.FORMULA) {
     if (sheet instanceof XSSFSheet){ // do it for XSSF only, not necessary for HSSF.
      String formula = cell.getCellFormula();
      formula = replaceFullColumnReferences((XSSFSheet)sheet, formula);
      cell.setCellFormula(formula);
     }
    }
    //*/
    String value = formatter.formatCellValue(cell, evaluator);
    System.out.print(value + "\t");
   }
   System.out.println();
  }
  
 java.time.LocalDateTime endTime = java.time.LocalDateTime.now();
 java.time.Duration duration = java.time.Duration.between(startTime, endTime);
 System.out.println("process duration: " + duration);
 
 workbook.close();
 }
}

注释掉部分

...
    /*
    if (cell.getCellType() == CellType.FORMULA) {
     if (sheet instanceof XSSFSheet){ // do it for XSSF only, not necessary for HSSF.
      String formula = cell.getCellFormula();
      formula = replaceFullColumnReferences((XSSFSheet)sheet, formula);
      cell.setCellFormula(formula);
     }
    }
    */
...

看到差异。