CODEWITHSUNDEEP
javaexcelexcel-formulaapache-poixlsx
Stark
Stark

Reputation: 481

Formula Cell converting to Error Cell in excel using Apache POI

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

      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

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:

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

please tell me what part i am missing?

Upvotes: 0

Views: 2148

Answers (1)

Axel Richter
Axel Richter

Reputation: 61975

The formula =LOOKUP(2,1/(A:A<>""),ROW(A:A)) uses an not documented feature of 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).

But as of it is documented:

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.

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.

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

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.

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

With your data example, where I assume the values 1 to 9 are in column 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.

Upvotes: 3

Related Questions