Bug 44508

Summary: Number format exception when reading boolean formula
Product: POI Reporter: Jan Vanderloock <java>
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows 2000   
Attachments: the excel file, edited by Open Office Calc, contains boolean values that are considered as functions
svn diff of changes to HSSFCell.java

Description Jan Vanderloock 2008-02-29 03:02:37 UTC
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)
Comment 1 Josh Micich 2008-02-29 11:02:47 UTC
Created attachment 21605 [details]
svn diff of changes to HSSFCell.java
Comment 2 Josh Micich 2008-02-29 11:24:41 UTC
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);

        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
        try {
        } catch (NumberFormatException e) {
            fail("Identified bug 44508");
        assertEquals(true, cell.getBooleanCellValue());
Comment 3 Nick Burch 2008-03-03 08:54:49 UTC
Thanks for the patch and test, applied to svn trunk