加速apachepoi-SUMIF

Question

在xlsx工作簿中，有些单元格具有一些无界的SUMIF公式，如下所示：SUMIF（MySheetname！$B:$B，$E4，MySheetname！$I:$I）。使用ApachePOI5.0.0对一个SUMIF函数的评估持续100ms，对给定工作簿的评估持续几分钟。

提高执行持续时间的一种方法是将公式绑定到如下内容：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，其中包含SheetRefEvaluators，其中包含一个EvaluationSheet，这至少知道getLastRowNum（）。不幸的是，这个属性链是私有的。

你知道如何做到这一点吗？或者其他如何提高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);
     }
    }
    */
...

看看有什么不同。