java - 使用 Apache POI 将公式单元格转换为 Excel 中的错误单元格

Question

我正在使用 apache poi 访问 Excel，每当我使用任何公式时，该行就会转换为错误行。

      XSSFWorkbook workbook = new XSSFWorkbook("D://Book.xlsx"); 
      XSSFSheet spreadsheet = workbook.getSheetAt(0);

      XSSFRow row = spreadsheet.createRow(6);
      XSSFCell cell = row.createCell(4);
      cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
      cell.setCellFormula("LOOKUP(2,1/(A:A<>\"\"),ROW(A:A))");
      workbook.setForceFormulaRecalculation(true);
      FormulaEvaluator evaluator =   workbook.getCreationHelper().createFormulaEvaluator();
      CellValue cellValue = evaluator.evaluate(cell);

      switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println("Boolean");
            System.out.println(cellValue.getBooleanValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println("Num");
            System.out.println(cellValue.getNumberValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println("String");
            System.out.println(cellValue.getStringValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            System.out.println("Blank");
            break;
        case Cell.CELL_TYPE_ERROR:
            System.out.println("Error");
            Byte b = cellValue.getErrorValue();
            System.out.println(b.intValue());
            break;

        case Cell.CELL_TYPE_FORMULA: 
            System.out.println("Formula");
            break;
    }   
      workbook.close();
      System.out.println("written successfully");

它给出以下输出 错误 15 写入成功

但我期望执行该公式 block 。但每次执行时都会出现错误情况。我的 Excel 工作表包含简单的行，例如:

a   1
b   2
a   3
b   4
d   5
r   6
g   7
q   8
y   9

请告诉我我缺少哪一部分？

Answer 1

公式=LOOKUP(2,1/(A:A<>""),ROW(A:A))使用 Excels 的未记录功能 LOOKUP 。

部分1/(A:A<>"")计算结果为 {1;1;1;1;#DIV/0!;#DIV/0!;...} 。如果单元格位于 A是 <>"" ，然后1 (1/TRUE)，否则#DIV/0! (1/FALSE)。

但据记录:

Syntax

LOOKUP(lookup_value, lookup_vector, [result_vector])

lookup_vector Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.

这里没有提到错误值!

还有:

Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

是否{1;1;1;1;#DIV/0!;#DIV/0!;...}真的是升序不清楚!

因此，这将按 Excel 中的预期进行计算。如果您要编写工作簿并使用 Excel 打开它，则公式 =LOOKUP(2,1/(A:A<>""),ROW(A:A))将在 E7并将按预期工作。

但与其他Evaluators像apache poi那样严格遵守评估规则的，肯定不行。

在您的数据示例中，我假设值 1 到 9 位于列 B 中,

cell.setCellFormula("LOOKUP(MAX(B:B),B:B,B:B)");

将按预期进行评估。

这当然不是您想要的 - 获取列 A 中最后一个填充的单元格。这也可以通过数组公式来实现。但是，虽然 apache poi 可以将数组公式应用于工作表，但据我所知，它直到现在才评估数组公式。