excel 基于范围的Apache poi公式评估

djmepvbi  于 2023-10-21  发布在  Apache
关注(0)|答案(1)|浏览(121)

我们试图在java maven项目中使用poi来计算xlsx公式，但是如果公式有一个范围或数组，输出值是重复的。
下面是预期与POI输出：

预期产出

poi输出

列J和K没有任何公式单元格。列L和M具有依赖于J和K数据的公式单元格。
下面是在J列和K列的7行中定义的公式：

Column J :
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J6)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J6))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J7)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J7))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J8)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J8))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J9)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J9))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J10)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J10))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J11)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J11))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J12)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J12))))

Column K :
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K6)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K6))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K7)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K7))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K8)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K8))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K9)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K9))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K10)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K10))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K11)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K11))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K12)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K12))))

如果我们比较上面的图像（预期与POI输出），那么我们看到POI给出了不正确的结果并重复了J和K中的项目，而MS Excel能够正确计算。
已尝试公式赋值器evaluateAll和evaluateFormulaCell。不会产生正确的结果。
尝试使用evaluator.clearAllCachedResultValues();和evaluator.notifySetFormula(cell);这看起来像poi不支持这些公式。使用XSSFWorkBook。

FileInputStream fis = new FileInputStream(inputFile);
    Workbook workbook = new XSSFWorkbook(fis);
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    // some code in between, loops etc
    if (cell.getCellType() == CellType.FORMULA) {
        evaluator.evaluateFormulaCell(cell);    
    }
<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>            
        <version>5.2.4</version>
    </dependency>       
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.4</version>
    </dependency>
excel

1条答案

按热度按时间
mitkmikd

mitkmikd1#

这里的主要问题是Apache POI没有提供ArrayFunction的所有功能。在这种特殊情况下，Excel function ROW由Java函数org.apache.poi.ss.formula.functions.RowFunc表示，该函数尚未准备好在数组上下文中运行。
这可以通过改变Java函数的代码来改变。该代码需要考虑“如果引用是一个单元格区域，并且如果ROW作为垂直数组输入，则ROW将引用的行号作为垂直数组返回。"。我的函数RowFuncArrayReady就是这么做的。

import org.apache.poi.ss.formula.functions.*;

import org.apache.poi.ss.formula.eval.AreaEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.RefEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.CacheAreaEval;

/**
 * Implementation for the Excel function ROW ready for usage as ArrayFunction
 */
public final class RowFuncArrayReady implements Function, ArrayFunction {
    @Override
    public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
        if (args.length > 1) {
            return ErrorEval.VALUE_INVALID;
        }
        if (args.length == 0) {
            return new NumberEval(srcRowIndex + 1.);
        } else {
            return evaluate(srcRowIndex, srcColumnIndex, args[0]);
        }
    }
        
    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
        int rnum;
        if (arg0 instanceof AreaEval) {
            rnum = ((AreaEval)arg0).getFirstRow();
        } else if (arg0 instanceof RefEval) {
            rnum = ((RefEval)arg0).getRow();
        } else {
            // anything else is not valid argument
            return ErrorEval.VALUE_INVALID;
        }
        return new NumberEval(rnum + 1.);
    }
    
    @Override
    public ValueEval evaluateArray(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
        if (args.length > 1) {
            return ErrorEval.VALUE_INVALID;
        }
        if (args.length == 0) {
            return new NumberEval(srcRowIndex + 1.);
        } else if (args[0] instanceof AreaEval) {
            return evaluateAreaEval((AreaEval)args[0], srcRowIndex, srcColumnIndex);
        } else if (args[0] instanceof RefEval) {
            return evaluate(srcRowIndex, srcColumnIndex, (RefEval)args[0]);
        } else {
            // anything else is not valid argument
            return ErrorEval.VALUE_INVALID;
        }
    }   
    
    private ValueEval evaluateAreaEval(AreaEval ae, int srcRowIndex, int srcColumnIndex) {
        int w1, w2, h1, h2;
        int a1FirstCol = 0, a1FirstRow = 0;
        w1 = ae.getWidth();
        h1 = ae.getHeight();
        a1FirstCol = ae.getFirstColumn();
        a1FirstRow = ae.getFirstRow();
        w2 = 1;
        h2 = 1;
        int width = Math.max(w1, w2);
        int height = Math.max(h1, h2);
        ValueEval[] vals = new ValueEval[height * width];
        int idx = 0;
        for(int i = 0; i < height; i++){
            for(int j = 0; j < width; j++){
                vals[idx++] = evaluate(srcRowIndex, srcColumnIndex, ae.offset(i, i, j, j));
            }
        }
        if (vals.length == 1) {
            return vals[0];
        }
        return new CacheAreaEval(srcRowIndex, srcColumnIndex, srcRowIndex + height - 1, srcColumnIndex + width - 1, vals);
    }
        
}

要使该新函数成为Excel的ROW函数的默认表示形式，它需要位于org.apache.poi.ss.formula.eval.FunctionEval的Function[] functions数组中，索引为8。在下面的代码中，方法prepareFunctionEval将在作为prepareFunctionEval(8, new RowFuncArrayReady());调用时执行此操作。

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;

class ExcelEvaluateROWFormulaAsArray  {
     
 static void prepareFunctionEval(int pos, org.apache.poi.ss.formula.functions.Function function) throws Exception {
  java.lang.reflect.Field _functions = Class.forName("org.apache.poi.ss.formula.eval.FunctionEval").getDeclaredField("functions");
  _functions.setAccessible(true); 
  org.apache.poi.ss.formula.functions.Function[] functions = (org.apache.poi.ss.formula.functions.Function[])_functions.get(null); 
  functions[pos] = function;
 }

 public static void main(String[] args) throws Exception {
     
  prepareFunctionEval(8, new RowFuncArrayReady());
   
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelUsingRowFormulaInArrayContext.xlsx"));
  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); 
  
  evaluator.setDebugEvaluationOutputForNextEval(true);  
 
  Sheet sheet = workbook.getSheetAt(0);
  
  for (Row row : sheet) {
   for (Cell cell : row) {
    if (CellType.FORMULA == cell.getCellType()) {
     System.out.println(cell.getCellFormula());
     CellValue cellValue = evaluator.evaluate(cell);
     System.out.println(cellValue);
    }
   }
  }
  
  workbook.close();
 }
}

我的ExcelUsingRowFormulaInArrayContext.xlsx看起来像这样：

注意，公式{=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J6)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J6))))}是使用CtrlShiftEnter输入的数组公式。Apache POI无法评估Excel 365的新动态数组公式和溢出数组行为。
我的ExcelEvaluateROWFormulaAsArray然后打印：

IF(COUNTIF($J$6:$J$62,"?*")<ROW(J6)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J6))))
org.apache.poi.ss.usermodel.CellValue ["Text 1"]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(K6)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K6))))
org.apache.poi.ss.usermodel.CellValue [1.0]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(J7)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J7))))
org.apache.poi.ss.usermodel.CellValue ["Text 2"]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(K7)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K7))))
org.apache.poi.ss.usermodel.CellValue [2.0]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(J8)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J8))))
org.apache.poi.ss.usermodel.CellValue ["Text 3"]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(K8)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K8))))
org.apache.poi.ss.usermodel.CellValue [3.0]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(J9)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J9))))
org.apache.poi.ss.usermodel.CellValue ["Text 4"]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(K9)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K9))))
org.apache.poi.ss.usermodel.CellValue [4.0]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(J10)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J10))))
org.apache.poi.ss.usermodel.CellValue ["Text 5"]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(K10)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K10))))
org.apache.poi.ss.usermodel.CellValue [5.0]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(J11)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J11))))
org.apache.poi.ss.usermodel.CellValue [""]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(K11)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K11))))
org.apache.poi.ss.usermodel.CellValue [""]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(J12)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J12))))
org.apache.poi.ss.usermodel.CellValue [""]
IF(COUNTIF($J$6:$J$62,"?*")<ROW(K12)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K12))))
org.apache.poi.ss.usermodel.CellValue [""]

这与Excel评估的结果相同。
要检查Excel函数的哪些Apache POI Java函数表示可以在数组上下文中工作，除了查看源代码之外没有其他方法。所有这些代码都在https://svn.apache.org/viewvc/poi/tags/REL_5_2_4/poi/src/main/java/org/apache/poi/ss/formula/和子目录中。命名函数位于/functions或/atp子目录中。
但有时操作数也需要是ArrayFunction s。大多数操作数位于/eval子目录中。例如，UnaryPlusEval是数组上下文就绪的。所以+A1:A10将在数组上下文中工作。但是ConcatEval不是。所以A1:A10&B1:B10不能在数组上下文中工作。
但也有一些函数，其中数组上下文中的函数根本没有记录在某处。例如，对于COUNTIF function，如果在数组上下文中像COUNTIF($B$69:B69, $D$6:$D$62)一样使用，则不清楚会发生什么。如何处理What do you want to look for?中的数组？微软对此没有透露任何信息。因此，我们所能做的就是检查Excel在这种情况下的行为，然后尝试使用Java编程Excel的行为。这是非常具有挑战性和容易出错。我怀疑有人会这样做。我怀疑甚至微软也不知道Excel用户发现了什么有趣的公式解决方案，特别是使用数组上下文。因此，如果有人需要那些有趣的公式解决方案，那么这一个应该使用真实的Excel应用程序。

赞(0）分享  回复(0）举报  2023-10-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com