Bug 44508 - Number format exception when reading boolean formula
Summary: Number format exception when reading boolean formula
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: PC Windows 2000
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-02-29 03:02 UTC by Jan Vanderloock
Modified: 2008-03-03 08:54 UTC (History)
0 users

the excel file, edited by Open Office Calc, contains boolean values that are considered as functions (6.00 KB, application/vnd.ms-excel)
2008-02-29 03:02 UTC, Jan Vanderloock
svn diff of changes to HSSFCell.java (2.15 KB, patch)
2008-02-29 11:02 UTC, Josh Micich
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
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