Bug 967609 - External links from decision table to other XLS files are not handled correctly
External links from decision table to other XLS files are not handled correctly
Status: CLOSED CURRENTRELEASE
Product: JBoss BRMS Platform 6
Classification: JBoss
Component: BRE (Show other bugs)
6.0.0
Unspecified Unspecified
unspecified Severity high
: ER4
: 6.0.0
Assigned To: Mario Fusco
Petr Široký
: Regression, TestBlocker
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-05-27 10:57 EDT by Petr Široký
Modified: 2014-08-06 16:20 EDT (History)
2 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2014-08-06 16:20:21 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
Maven based reproducer (11.99 KB, application/zip)
2013-05-27 11:00 EDT, Petr Široký
no flags Details

  None (edit)
Description Petr Široký 2013-05-27 10:57:15 EDT
Description of problem:
Decision table is not correctly compiled when using links to external XLS files. See attached reproducer. 

I think the external link should not result in error even if it is not available (broken) -> the previous known (cached) value should be used. 

This is the regression from 5.3.1 where both available and not-available (broken) links worked.

Full stacktrace:
java.lang.RuntimeException: Could not resolve external workbook name 'home/psiroky/work/workspace/decisiontables-example-drools6/src/test/resources/definitions.xls'. Workbook environment has not been set up.
	at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:89)
	at org.apache.poi.ss.formula.OperationEvaluationContext.getRef3DEval(OperationEvaluationContext.java:249)
	at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:656)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:527)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:354)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:185)
	at org.drools.decisiontable.parser.xls.ExcelParser.processSheet(ExcelParser.java:145)
	at org.drools.decisiontable.parser.xls.ExcelParser.parseFile(ExcelParser.java:79)
	at org.drools.decisiontable.SpreadsheetCompiler.compile(SpreadsheetCompiler.java:93)
	at org.drools.decisiontable.SpreadsheetCompiler.compile(SpreadsheetCompiler.java:72)
	at org.drools.decisiontable.DecisionTableProviderImpl.compileStream(DecisionTableProviderImpl.java:37)
	at org.drools.decisiontable.DecisionTableProviderImpl.loadFromInputStream(DecisionTableProviderImpl.java:20)
	at org.drools.compiler.compiler.DecisionTableFactory.loadFromInputStream(DecisionTableFactory.java:15)
	at org.drools.compiler.compiler.PackageBuilder.decisionTableToPackageDescr(PackageBuilder.java:461)
	at org.drools.compiler.compiler.CompositeKnowledgeBuilderImpl.buildPackageDescr(CompositeKnowledgeBuilderImpl.java:348)
	at org.drools.compiler.compiler.CompositeKnowledgeBuilderImpl.buildPackages(CompositeKnowledgeBuilderImpl.java:84)
	at org.drools.compiler.compiler.CompositeKnowledgeBuilderImpl.build(CompositeKnowledgeBuilderImpl.java:75)
	at org.drools.compiler.kie.builder.impl.AbstractKieModule.buildKnowledgePackages(AbstractKieModule.java:140)
	at org.drools.compiler.kie.builder.impl.AbstractKieProject.verify(AbstractKieProject.java:39)
	at org.drools.compiler.kie.builder.impl.KieBuilderImpl.buildKieModule(KieBuilderImpl.java:207)
	at org.drools.compiler.kie.builder.impl.KieBuilderImpl.buildAll(KieBuilderImpl.java:160)
	at org.kie.internal.utils.KieHelper.build(KieHelper.java:25)
	at org.jboss.qa.brms.dt.example.CreateKBaseFromDTTest.createKBase(CreateKBaseFromDTTest.java:18)
	at org.jboss.qa.brms.dt.example.CreateKBaseFromDTTest.testCreateKBasefromDT(CreateKBaseFromDTTest.java:11)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:601)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment$WorkbookNotFoundException: Could not resolve external workbook name 'home/psiroky/work/workspace/decisiontables-example-drools6/src/test/resources/definitions.xls'. Workbook environment has not been set up.
	at org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.getWorkbookEvaluator(CollaboratingWorkbooksEnvironment.java:161)
	at org.apache.poi.ss.formula.WorkbookEvaluator.getOtherWorkbookEvaluator(WorkbookEvaluator.java:181)
	at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:87)
	... 48 more


Version-Release number of selected component (if applicable):
6.0.0-SNAPSHOT(2013-05-27)


How reproducible:
Always


Steps to Reproduce:
1. Update the link (cell C7) to definitions.xls in sample.xls. 
2. Run the attached reproducer (mvn test)


Actual results:
Exception when compiling the XLS file.


Expected results:
Successful compilation.
Comment 1 Petr Široký 2013-05-27 11:00:03 EDT
Created attachment 753651 [details]
Maven based reproducer
Comment 4 manstis 2013-05-29 12:04:31 EDT
The problem is that POI doesn't automatically handle cross-workbook references; we, the developer, need to resolve and add all external workbooks. Furthermore things are complicated slightly as POI only supports cross-workbook formula for .XLS files (HSSF \ Excel '97-2007) and not .XLSX (XSSF \ Excel 2007 OOXML) files.

So, we could programmatically parse worksheets for external references, resolve and load the external references. Alternatively we could add a breaking change to 6.0 that requires the developer to provide a list of all external workbooks required to parse the decision table. 

In both scenarios we can only support cross-workbook references for .XLS files and not .XLSX files.
Comment 6 Petr Široký 2013-05-30 04:47:12 EDT
I am no expert in XLS format or POI library, but looking at the spreadsheet with such external references, the values in concrete cells are still present, even if the external spreadsheet is not available. I guess this means that the spreadsheet caches the value from external source and uses it if the external file is not available. Could the decision table compiler use that cached value?
Comment 7 manstis 2013-05-31 04:39:07 EDT
(In reply to Petr Siroky from comment #6)
> I am no expert in XLS format or POI library, but looking at the spreadsheet
> with such external references, the values in concrete cells are still
> present, even if the external spreadsheet is not available. I guess this
> means that the spreadsheet caches the value from external source and uses it
> if the external file is not available. Could the decision table compiler use
> that cached value?

Hello, sure that would (erm, should) be possible. If that's an acceptable solution to this BZ then I can make it work so. Please advise. Thanks, Mike
Comment 8 Edson Tirelli 2013-06-20 21:16:06 EDT
Michael, please go ahead and use the cached values if they are available.
Comment 9 manstis 2013-07-02 07:17:25 EDT
Hello, 

I have a possible fix to use the cached values for linked workbooks however I need the sample.xls workbook opened with Excel, link refreshed and then saved again. I can't find anybody that has Excel. 

Could you please do the following for me:-

1) Open sample.xls
2) Refresh link to external definitions.xls
3) Save
4) Attach revised sample.xls to this ticket.

Thanks,

Mike
Comment 14 Mario Fusco 2013-09-09 11:00:18 EDT
I tried to improve Mike's solution but there are still cases where POI cannot find cached values and there's no much I can do about it.

I also tried to set the ignoreMissingWorkbook flag on the FormulaEvaluator ( http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.html#setIgnoreMissingWorkbooks%28boolean%29 ). The only result I obtained is that POI doesn't thrown any Exception when it meets a missing external reference, but the returned cached value was still wrong. Since this change was not useful and I am unsure of its further possible side-effects I decided to rollback it. 

The only thing I actually did was to log a warning when an external link is missing.
Comment 18 Petr Široký 2013-10-17 12:21:29 EDT
Verified fixed in ER4.

Note You need to log in before you can comment on or make changes to this bug.