Bug 60355 - SS Formula Parser fails to parse a formula with a free ref function
Summary: SS Formula Parser fails to parse a formula with a free ref function
Status: NEEDINFO
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-11-09 02:45 UTC by kenneth_lau
Modified: 2019-01-12 17:46 UTC (History)
0 users



Attachments
Excel workbook used by the test program (16.06 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-11-09 02:45 UTC, kenneth_lau
Details
HsGetVal Test Case (12.84 KB, application/x-zip-compressed)
2016-11-10 02:50 UTC, kenneth_lau
Details

Note You need to log in before you can comment on or make changes to this bug.
Description kenneth_lau 2016-11-09 02:45:03 UTC
Created attachment 34430 [details]
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();
        }
    }
}
Comment 1 Javen O'Neal 2016-11-09 04:11:02 UTC
(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
Comment 2 kenneth_lau 2016-11-09 22:09:43 UTC
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)
Comment 3 Javen O'Neal 2016-11-10 00:29:46 UTC
(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
Comment 4 Javen O'Neal 2016-11-10 00:54:57 UTC
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
Comment 5 kenneth_lau 2016-11-10 02:50:08 UTC
Created attachment 34432 [details]
HsGetVal Test Case

HsGetValueTest.java - main test program
HsGetValue.java     - standalone java replacement of HsGetValue evaluation
HsGetValue.xlsx     - sample workbook
Comment 6 kenneth_lau 2016-11-10 03:11:08 UTC
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)
Comment 7 Dominik Stadler 2019-01-12 17:46:40 UTC
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?