DO NOT REPLY [Bug 49894] New: Reading Excel workbook name and path in vlookup functions

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=49894

           Summary: Reading Excel workbook name and path in vlookup
                    functions
           Product: POI
           Version: 3.5-FINAL
          Platform: PC
        OS/Version: Windows XP
            Status: NEW
          Severity: normal
          Priority: P2
         Component: XSSF
        AssignedTo: dev@poi.apache.org
        ReportedBy: rahuld14@yahoo.com


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)

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

DO NOT REPLY [Bug 49894] Reading Excel workbook name and path in vlookup functions

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=49894

Rahul Kini <ra...@yahoo.com> changed:

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

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org