Bug 48165 - Multi-workbook formula evaluation: Invalid sheet name '1.2.1_LNE' in bool '1_Structure_Data.xls'.
Summary: Multi-workbook formula evaluation: Invalid sheet name '1.2.1_LNE' in bool '1_...
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.8-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-11-09 22:54 UTC by safinwang
Modified: 2016-07-19 11:17 UTC (History)
1 user (show)



Attachments
The File and the external referneced file (670.23 KB, application/x-zip-compressed)
2012-10-06 20:27 UTC, Divya
Details
Passing unit test (703.08 KB, application/gzip)
2016-07-19 11:02 UTC, Javen O'Neal
Details

Note You need to log in before you can comment on or make changes to this bug.
Description safinwang 2009-11-09 22:54:46 UTC
I want to evaluate a workbook that linked to other workbook using the following method:
String dirName = "c:/somedir/";
String bookNameA = "test.xls";
String bookNameB = "rm0509.xls"; 
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(dirName + bookNameA));
HSSFWorkbook wbB = new HSSFWorkbook(new FileInputStream(dirName + bookNameB));

HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator feB = new HSSFFormulaEvaluator(wbB);

// Set up the workbook environment for evaluation
String[] workbookNames = { bookNameA, bookNameB, };
HSSFFormulaEvaluator[] evaluators = { evaluator, feB, };
HSSFFormulaEvaluator.setupEnvironment(workbookNames, evaluators);

// do an evaluation
HSSFCell cell = wb.getSheetAt(0).getRow(0).getCell(0);
evaluator.evaluateFormulaCell(cell);  

The exception happens when call the evaluator.evaluateFormulaCell(cell) method,the exception message is like this:
14:18:08,437 [http-8080-Processor18] ERROR jsp - java.lang.RuntimeException: Invalid sheet name '1.2.1_LNE' in bool '1_Structure_Data.xls'.
	at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:90)
	at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:480)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:379)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:257)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:524)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:47)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:52)
	at org.apache.poi.ss.formula.WorkbookEvaluator.dereferenceValue(WorkbookEvaluator.java:411)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:392)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:257)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:524)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:47)
	at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:52)
	at org.apache.poi.hssf.record.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:47)
	at org.apache.poi.hssf.record.formula.functions.Hlookup.evaluate(Hlookup.java:58)
	at org.apache.poi.hssf.record.formula.eval.FunctionEval.evaluate(FunctionEval.java:247)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:374)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:257)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:524)
	at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:47)

I don't know why the sheet name is invalid,please help me!
Comment 1 Yegor Kozlov 2011-06-25 12:37:11 UTC
Please attached the problematic file, without it we can't do much to help you.

Yegor
Comment 2 Divya 2012-10-06 20:24:31 UTC
I am facing the same issue. I have attached the file as well the external referenced file for which I am seeing this issue.
My code is similar to the code above and I am seeing the exact same exception. I have copied my stacktrace here.
Anyhelp is greatly appreciated.

java.lang.RuntimeException: Invalid sheet name 'Dates' in bool 'ACCOUNTS_DELIVERY_CLT.xls'.
	at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:93)
	at org.apache.poi.ss.formula.OperationEvaluationContext.getRef3DEval(OperationEvaluationContext.java:249)
	at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:607)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:493)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:229)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:354)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:216)
	at imgrdatamodifier.UpdateExcelFile.updateFile(UpdateExcelFile.java:84)
	at imgrdatamodifier.UpdateExcelFile.main(UpdateExcelFile.java:32)
Comment 3 Divya 2012-10-06 20:27:37 UTC
Created attachment 29456 [details]
The File and the external referneced file

The files are attached.
Please take a look and let me know.
Comment 4 Divya 2012-10-09 10:50:20 UTC
Is there any update to this?
Comment 5 Divya 2012-10-09 15:33:16 UTC
I did some debugging. The exception is thrown by the POI code as it seems to get an index <0 for the particular sheet in the external workbook.
When I call the getSheetIndex function for the external sheet in question separately it returns a valid index (>0) but for some reason poi function ss.formula.OperationEvaluationContext.createExternalSheetRefEvaluator(...) is not able to get the correct index for the sheet when it is called to evaluate the formula.
Please Help.
Comment 6 Javen O'Neal 2016-07-19 11:02:28 UTC
Created attachment 34058 [details]
Passing unit test

I was unable to reproduce the problem in the latest version of POI (3.15 beta 2+) using the worksheets in attachment 29456 [details]. See my attached patch.

If you want to ensure that this stays fixed in the future, please submit a minimal worksheet that ideally contains just one relative external link to a second workbook and nothing else.
Comment 7 Javen O'Neal 2016-07-19 11:03:47 UTC
This was probably fixed in a previous version. Anyways, working now, though no guarantees that it won't break in the future without a unit test...