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

i am using apache poi to access excel, when ever i am using any formula that row is getting converted to error row.

我正在使用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");

it is giving following as output Error 15 written successfully

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

but i am expecting that formula block to be executed. but every time it is executing error case. My Excel sheet contains simple row like:

但我期待公式块被执行。但每次执行错误案例。我的Excel工作表包含简单的行:

please tell me what part i am missing?

请告诉我我错过了什么部分?

1 个解决方案

#1

The formula =LOOKUP(2,1/(A:A<>""),ROW(A:A)) uses an not documented feature of Excels LOOKUP.

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

The part 1/(A:A<>"") evaluates to {1;1;1;1;#DIV/0!;#DIV/0!;...}. If the cell in A is <>"", then 1 (1/TRUE), else #DIV/0! (1/FALSE).

第1部分(A:A <>“”)评估为{1; 1; 1; 1; #DIV / 0 !;#DIV / 0!; ...}。如果A中的单元格是<>“”,那么1(1 / TRUE),否则#DIV / 0! (1 / FALSE)。

But as of it is documented:

但据记载:

Syntax

LOOKUP(lookup_value, lookup_vector, [result_vector])

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.

lookup_vector必需。仅包含一行或一列的范围。 lookup_vector中的值可以是文本,数字或逻辑值。

Error values are not mentioned here!

这里没有提到错误值!

And:

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.

重要说明:lookup_vector中的值必须按升序排列:..., - 2,-1,0,1,2,...,A-Z,FALSE,TRUE;否则,LOOKUP可能无法返回正确的值。大写和小写文本是等效的。

Whether {1;1;1;1;#DIV/0!;#DIV/0!;...} really is in ascending order is unclear!

{1; 1; 1; 1; #DIV / 0 !;#DIV / 0!; ...}是否按升序排列尚不清楚!

So this will evaluate as expected in Excel. If you would write the workbook and opening it with Excel, the Formula =LOOKUP(2,1/(A:A<>""),ROW(A:A)) would be in E7 and would work as expected.

因此,这将在Excel中按预期进行评估。如果您要编写工作簿并使用Excel打开它,则Formula = LOOKUP(2,1 /(A:A <>“”),ROW(A:A))将在E7中并按预期工作。

But with other Evaluators like the one from apache poi, which observe the evaluation rules exactly, it must not work.

但是对于像apache poi这样的评估员来说,它们完全遵守评估规则,它一定不行。

With your data example, where I assume the values 1 to 9 are in column B,

使用您的数据示例,我假设值1到9在B列中,

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

will evaluate as expected.

将按预期评估。

Surely it is not what you want - to get last filled cell in column A. This could also be achieved with array formulas. But while apache poi can apply array formulas to the sheet, as far as I know, it will not evaluate array formulas until now.

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

#1