[Bug 60355] New: SS Formula Parser fails to parse valid formula string

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 60355] New: SS Formula Parser fails to parse valid formula string

Bugzilla from bugzilla@apache.org
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: [hidden email]
          Reporter: [hidden email]
  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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse valid formula string

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

[hidden email] 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse valid formula string

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

Javen O'Neal <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |INVALID

--- Comment #1 from Javen O'Neal <[hidden email]> ---
(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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse valid formula string

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |REOPENED
         Resolution|INVALID                     |---

--- Comment #2 from [hidden email] ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse valid formula string

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

Javen O'Neal <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|REOPENED                    |NEEDINFO

--- Comment #3 from Javen O'Neal <[hidden email]> ---
(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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse valid formula string

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

--- Comment #4 from Javen O'Neal <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse valid formula string

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

--- Comment #5 from [hidden email] ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse valid formula string

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--- Comment #6 from [hidden email] ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse a formula with a free ref function

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

Javen O'Neal <[hidden email]> 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse a formula with a free ref function

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

Javen O'Neal <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|critical                    |normal

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60355] SS Formula Parser fails to parse a formula with a free ref function

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60355

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #7 from Dominik Stadler <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]