Created attachment 21601 [details] the excel file, edited by Open Office Calc, contains boolean values that are considered as functions Reading an excel file, created with Open Office Calc, where the boolean flags are apparently saved as cells of type FORMULA, causes number format exceptions at evaluation time Caused by: java.lang.NumberFormatException: You cannot get a boolean value from a non-boolean cell at org.apache.poi.hssf.usermodel.HSSFCell.getBooleanCellValue(HSSFCell.java:850) at org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:468) at org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:329) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:289)
Created attachment 21605 [details] svn diff of changes to HSSFCell.java
HSSFFormulaEvaluator.evaluateInCell() does an evaluation of the cell formula and then replaces the formula with the evaluation result. Can you tell me why you are using this non-idempotent method? Is it a question of performance? HSSFCell has a method setCellType() which both gets and sets the cell value. The cell value accessor methods are mostly type checked, preventing setting or getting a value of the wrong type. The combination of these causes some convoluted logic. This patch I have submitted only fixes the case where the formula result is a boolean. Here is a test case specifically for this patch: private void testEvaluateBooleanInCell_bug44508() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); wb.setSheetName(0, "Sheet1"); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell((short)0); cell.setCellFormula("1=1"); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); fe.setCurrentRow(row); try { fe.evaluateInCell(cell); } catch (NumberFormatException e) { fail("Identified bug 44508"); } assertEquals(true, cell.getBooleanCellValue()); }
Thanks for the patch and test, applied to svn trunk