Bug 44708 - FormulaEvaluator can't handle all functions for XSSF
Summary: FormulaEvaluator can't handle all functions for XSSF
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-03-30 08:31 UTC by Nick Burch
Modified: 2008-04-06 13:57 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Nick Burch 2008-03-30 08:31:27 UTC
In the ooxml branch, I have converted HSSFFormulaEvaluator to work with the new generic interfaces. All of the HSSF formula tests still pass. Many simple formulas for XSSF can be evaluated just fine

Next up, I've taken a copy of the TestFormulasFromSpreadsheet test, and saved the test spreadsheet as .xlsx. This xssf version is in svn as org.apache.poi.xssf.usermodel.TestFormulaEvaluatorOnXSSF

Unfortunately, when running for xssf, lots of the formulas fail to evaluate properly, so I've had to disable the test (it's called DISABLEDtestFunctionsFromTestSpreadsheet)

I suspect the issue is that XSSFWorkbook doesn't have a model.Workbook, which model.FormulaParser expects, as possibly do some of the eval bits. XSSF doesn't have anything equivalent. Probably, we'll need to tweak various bits to not use the low level model.Workbook, but use the higher level stuff instead (since both xssf and hssf have high level stuff). Otherwise, we'll have to try and bodge up a fake model.Workbook for xssf
Comment 1 Josh Micich 2008-04-06 13:57:39 UTC
fixed in svn r645298.  Most of the failures were due to xssf classifying empty cells as type 'numeric'.  XSSF internally uses *numeric* cells with no value to represent 'blank' cells.  However, it is useful to distinguish 'blank' cell type for evaluating formulas.  The rest of POI seems to rely on this.

After the fix, the far majority of function test evaluations work:
"There were 862 successful evaluation(s) and 93 function(s) without error"
So I decided to enable the whole test, but specifically skip the 3 remaining failing evaluation cases.

The first bug is related to parsing of full column references.  This is a bug in trunk too, and should probably be fixed there (I think there is already a bugzilla for this).

The second remaining bug (just one(?!)) is related to the missing Workbook object.  
I have opened the a new bugzilla bug 44762 to track the work for this second bug.

Note - there is a minor TODOs left in XSSFCell.getNumericCellValue() requesting the spec to be settled.