You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2016/11/09 02:45:03 UTC
[Bug 60355] New: SS Formula Parser fails to parse valid formula
string
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
Bug ID: 60355
Summary: SS Formula Parser fails to parse valid formula string
Product: POI
Version: unspecified
Hardware: PC
Status: NEW
Severity: critical
Priority: P2
Component: SS Common
Assignee: dev@poi.apache.org
Reporter: kenneth_lau@yahoo.com
Target Milestone: ---
Created attachment 34430
--> https://bz.apache.org/bugzilla/attachment.cgi?id=34430&action=edit
Excel workbook used by the test program
SS Formula Parser is unable to parse valid Excel formula string.
Here's the stack trace --
org.apache.poi.ss.formula.FormulaParseException: Unused input
[("HSACTIVE","Year#Jan","Measures#Sales","Product#Cola","Market#New
York","Scenario#Actual")] after attempting to parse the formula
[[1]!HsGetValue("HSACTIVE","Year#Jan","Measures#Sales","Product#Cola","Market#New
York","Scenario#Actual")]
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1653)
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:159)
at org.apache.poi.xssf.usermodel.XSSFCell.setFormula(XSSFCell.java:553)
at
org.apache.poi.xssf.usermodel.XSSFCell.setCellFormula(XSSFCell.java:533)
at HsGetValTest.main(HsGetValTest.java:28)
HsGetVal is a valid XLA Add-In formula. Excel is able to read and parse without
problems.
Here's my standalone Java test sample
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.fasterxml.jackson.databind.exc.InvalidFormatException;
public class HsGetValTest {
public static void main( String[] args ) {
String fileName = "HsGetVal.xlsx";
File workbookFile = new File( fileName ) ;
try {
FileInputStream fis = new FileInputStream(workbookFile);
Workbook workbook = WorkbookFactory.create(fis);
Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
Cell formulaCell = sheet.getRow(4).getCell(1);
String cellFormula = formulaCell.getCellFormula();
System.out.println("cell formula:" + cellFormula);
formulaCell.setCellFormula(cellFormula);
} catch( FileNotFoundException e ) {
e.printStackTrace();
} catch( InvalidFormatException e ) {
e.printStackTrace();
} catch( IOException e ) {
e.printStackTrace();
} catch( Exception e) {
e.printStackTrace();
}
}
}
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse valid formula string
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
--- Comment #5 from kenneth_lau@yahoo.com ---
Created attachment 34432
--> https://bz.apache.org/bugzilla/attachment.cgi?id=34432&action=edit
HsGetVal Test Case
HsGetValueTest.java - main test program
HsGetValue.java - standalone java replacement of HsGetValue evaluation
HsGetValue.xlsx - sample workbook
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse valid formula string
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
kenneth_lau@yahoo.com changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |REOPENED
Resolution|INVALID |---
--- Comment #2 from kenneth_lau@yahoo.com ---
Thank you for looking into this bug!
We had extended FreeRefFunction and implemented our own UDF with evaluate()
before we encountered this bug.
The issue we found is the same SS formula parser failure is blocking our UDF
from being called.
Here's the call stack we observed --
org.apache.poi.ss.formula.FormulaParseException: Unused input
[("HSACTIVE","Year#Jan","Measures#Sales","Product#Cola","Market#New
York","Scenario#Actual")] after attempting to parse the formula
[[1]!HsGetValue("HSACTIVE","Year#Jan","Measures#Sales","Product#Cola","Market#New
York","Scenario#Actual")]
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1653)
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:159)
at
org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:53)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:261)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205)
at
org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:189)
at
org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCell(BaseXSSFFormulaEvaluator.java:117)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:346)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:337)
at
org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:105)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse valid formula string
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
--- Comment #4 from Javen O'Neal <on...@apache.org> ---
Could you attach your Java FreeRefFunction implementation of HsGetValue
(preferably as a Java file instead of an inline comment)?
We will also need to mock out C:\Oracle\SmartView\bin\HsTbar.xla or something
to mock an external VBA function defined in a standalone XLA file in order to
write a unit test for this.
HsTbar appears to belong to Oracle Hyperion Smart View for Office[1], but is
behind a OTN License Agreement and account registration wall, so it cannot be
committed to POI svn.
[1]
http://www.oracle.com/technetwork/middleware/epm/downloads/smart-view-1112x-2412371.html
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse a formula with a free
ref function
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #7 from Dominik Stadler <do...@gmx.at> ---
The "[1]!" confuses the formula parser, this happens in
FormulaParser.parseRangeable(), however I know not enough about formula syntax
to know what the [1]! actual means here as part of this formula.
Can you describe what it's effect is in Excel?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse valid formula string
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |INVALID
--- Comment #1 from Javen O'Neal <on...@apache.org> ---
(In reply to kenneth_lau from comment #0)
> HsGetVal is a valid XLA Add-In formula. Excel is able to read and parse
> without problems.
POI does not natively know how to evaluate HsGetValue because it is not an
Excel core function or a function in the official Excel Analysis ToolPak [1].
If you need to evaluate this formula in POI, you can re-implement HsGetValue as
a FreeRefFunction and register the function with a POI formula evaluator [2].
You may also find [3] and [4] helpful to learn about formula evaluation in POI.
For examples of User Defined Functions in POI, see [5] and [6]
[1]
https://support.office.com/en-us/article/Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4
[2] https://poi.apache.org/spreadsheet/user-defined-functions.html
[3] https://poi.apache.org/spreadsheet/eval-devguide.html
[4] https://poi.apache.org/spreadsheet/formula.html
[5]
https://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi/ss/examples/formula/SettingExternalFunction.java?view=markup
[6]
https://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi/ss/examples/formula/UserDefinedFunctionExample.java?view=markup
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse valid formula string
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
kenneth_lau@yahoo.com changed:
What |Removed |Added
----------------------------------------------------------------------------
Version|unspecified |3.15-FINAL
OS| |All
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse valid formula string
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
kenneth_lau@yahoo.com changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #6 from kenneth_lau@yahoo.com ---
Thank you for your feedback!
I've uploaded a standalone test case zip file to illustrate the parsing error.
We would like to replace evaluation of HsGetValue() with Java FreeRefFunction
implementation of HsGetValue. For debugging purposes, I've hard coded
functional values for now.
We would like to remove run-time dependency on
C:\Oracle\SmartView\bin\HsTbar.xla since we are doing the HsGetvalue()
evaluation ourselves in Java.
I have setIgnoreMissingWorkbooks(true) to tell the Formula Evaluator to skip
looking for Oracle HsTbar binaries.
Here's the SS Formula parse exception stack --
cell_4_1
formula:[1]!HsGetValue("HSACTIVE","Year#Jan","Measures#Sales","Product#Cola","Market#New
York","Scenario#Actual")
cell_4_2
formula:[1]!HsGetValue("HSACTIVE","Year#Jan","Measures#Sales","Product#Cola","Market#Utah","Scenario#Actual")
org.apache.poi.ss.formula.FormulaParseException: Unused input
[("HSACTIVE","Year#Jan","Measures#Sales","Product#Cola","Market#New
York","Scenario#Actual")] after attempting to parse the formula
[[1]!HsGetValue("HSACTIVE","Year#Jan","Measures#Sales","Product#Cola","Market#New
York","Scenario#Actual")]
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1653)
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:159)
at
org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:53)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:261)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205)
at
org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:189)
at
org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCell(BaseXSSFFormulaEvaluator.java:117)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:346)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:337)
at
org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:105)
at HsGetValTest.main(HsGetValTest.java:47)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse a formula with a free
ref function
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|SS Formula Parser fails to |SS Formula Parser fails to
|parse valid formula string |parse a formula with a free
| |ref function
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse a formula with a free
ref function
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Severity|critical |normal
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60355] SS Formula Parser fails to parse valid formula string
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|REOPENED |NEEDINFO
--- Comment #3 from Javen O'Neal <on...@apache.org> ---
(In reply to kenneth_lau from comment #2)
> [[1]!HsGetValue("HSACTIVE","Year#Jan","Measures#Sales","Product#Cola",
Looks like HsGetValue is defined in an external workbook, [1]. Did you also add
this external workbook to your evaluator?
https://poi.apache.org/spreadsheet/eval.html#External+%28Cross-Workbook%29+references
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org