0
I’m having a hard time evaluating a formula with the Apache Poi API in a cell that contains the formula HLOOKUP(B9,$AK$8:$AS$68,2,FALSE).
The same thing happens to be a file .xls or .xlsx.
The excel sheet has a table template which I fill with certain values that come from elsewhere. The cells in the first and second columns of this table are strings. the following cells are numbers, and the last cell is the formula I mentioned above - HLOOKUP(B9,$AK$8:$AS$68,2,FALSE) - and that’s the one I can’t read the value of.
Once I’ve filled it in, I can do the get and the set of all fields, but when will I evaluate the formula in switch I’ll always stop at Cell.CELL_TYPE_ERROR:
FormulaEvaluator evaluator = my_xls_workbookp.getCreationHelper().createFormulaEvaluator();
CellReference cellReference = new CellReference("R9");
Row row = my_worksheetp.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol());
int cellType = evaluator.evaluateFormulaCell(cell);
switch (cellType) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_ERROR:
break;
// CELL_TYPE_FORMULA will never happen
case Cell.CELL_TYPE_FORMULA:
break;
}
A curiosity: I don’t know if it helps but if after placing the values, open Excel through Office, do nothing in the document, and close again, I get the message from Office saying:
The following features cannot be stored in books without macros permission: The excel 4.0 function stored in the names defined. To save a file with these features, click No, then select a file type with permission for macros in the file type list. To continue saving as a book without permission for macros, click Yes.
In I choose the option "Yes" and if after that I read the file, I can already do the getNumericCellValue() and get the value, but that doesn’t interest me because I have to get the value when I’m writing.
Can’t this API read the $AK$8:$AS$68 directly, I’ll have to do something?
Hello @Oliveira, about the message when closing it just informs that it cannot save if you do not use the extension '.xlsm'. About the function 'cellType' is seen as cell as a whole (all fields and options in Excel xml) so it brings as formula... never used this function, but try to put 'Typeformula' before 'Typeerror', If it doesn’t work, test put in 'Typeerror' to search as 'String' 'Case Error' and see if it brings the number as desired, if yes, we can search for a form of.
– Evert
In any case, @Oliveira, you can do a function before running this function, by pasting values in the desired column and then rotating this function.
– Evert
Next weekend I’m gonna try that thing you told me. but it’s still strange to be able to read the value already if you fill Excel in Java, open the file and write after that message.
– Oliveira
I performed some tests and it worked with me normally. You are reading this formula from a spreadsheet or you are creating the spreadsheet. Provide the worksheet or code that generates the worksheet to facilitate the identification of the problem. In any case, if the formula is solved successfully, the code will enter the Celltype of the result type. You are getting the Celltype of the evaluate return method. If you want to enter the Type_formula case, you would have to use the Cell Celltype.
– Fagner Fonseca
I could not play a case where the execution enters Type_error. In my case, when the formula is incorrect an Exception to type Formulaparseexception is launched. I am using poi in version 3.17.
– Fagner Fonseca