Bug 49894 - Reading Excel workbook name and path in vlookup functions
Summary: Reading Excel workbook name and path in vlookup functions
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2010-09-08 05:36 UTC by Rahul Kini
Modified: 2010-09-08 07:02 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Rahul Kini 2010-09-08 05:36:28 UTC
Hello Experts,
I am a newbie in using POI. I am facing two issues while reading and evluating cell values in an Excel document having vlookup function as cell value (something as "=VLOOKUP(A1,'[Source.XLS]Sheet1'!$A:$B,2,0)").
Below is the detailed information:
=> I am using poi-3.5-FINAL-20090928.jar.
=> I need to only read the contents in the excel file (.xls /.xlsx) and evaluate the value of the formula, if any, during runtime
=> When I execute following code, I get java.lang.RuntimeException: Could not resolve external workbook name 'Source.XLS'. Workbook environment has not been set up:
org.apache.poi.ss.usermodel.FormulaEvaluator formulaEvalutor = wb.getCreationHelper().createFormulaEvaluator();
CellValue cellValue=formulaEvaluator.evaluate(cell);//Error is thrown at this line; Cell is an instance of anorg.apache.poi.ss.usermodel.Cell
=> I checked the threads and found that the evaluator needs to know the name of the external workbook and external sheet. So we need to do something like this:
             HSSFFormulaEvaluator sourceEvaluator = new HSSFFormulaEvaluator(wb);
             HSSFFormulaEvaluator refEvaluator = new HSSFFormulaEvaluator(extWb);
        String[] workbookNames = { wbName, extWbName};//extWbName is not known at compile time
        HSSFFormulaEvaluator[] evaluators = { sourceEvaluator, refEvaluator}; HSSFFormulaEvaluator.setupEnvironment(workbookNames, evaluators);

*** Question 1: I could not find a solution as to how to know the workbook name (I am sure it must be expecting the complete path to the referred workbook than just the name) at runtime from the vlookup function. Is there a way to do it? I could see that there are APIs like HSSFEvaluationWorkbook - getExternalSheet but these are marked for internal use only.

*** Question 2: Is there a way to get the complete formula in a cell as String ("as is") apart from using cell.getCellFormula() as it doesn't work good for something as "=VLOOKUP(A1,'C:\[Source.XLS]Sheet1'!$A:$B,2,0)") - org.apache.poi.hssf.record.SupBookRecord.decodeFileName(String) does not seem to be implemented fully
It gives result as VLOOKUP(A1,'[Source.XLS]Sheet1'!A:B,2,0)