poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Blake Watson <blake.wat...@pnmac.com>
Subject Linking External Workbooks
Date Wed, 05 Oct 2016 23:56:43 GMT
Hey, POI-holloi:

I've got spreadsheets that link to other spreadsheets (created in Excel,
I'm just reading), and I wish to evaluate these spreadsheets in POI. If I
understand this StackExchange exchange correctly:

http://stackoverflow.com/questions/35093505/java-poi-xssf-vlookup-formula/35145903

This should be possible for me because I'm not trying to create the link,
it was already created in Excel. Despite doing what I think this poster has
described, I'm stuck with "Invalid sheetIndex: -1." errors.

So, the first question is: If everything is set up okay in the XSSF Formula
Evaluator, will evaluateFormulaCell actually return the result from the
other workbook? And the second question (if the answer to the first
question is "yes") is: How do I set it up correctly?

Here's what I'm doing now:

1. Open workbook 1.
2. Open workbook 2.
3. Check contents of externally calculated cell:

"VLOOKUP(PropertyState,'[1]Sheet'!$A:$B,2,FALSE)" = 2206.0

4. Create evaluator for workbook 2.
5. Create evaluator for workbook 1.
6. Call setupReferenceWorkbooks for wb1's evaluator, passing in the name of
the current wb attached to wb1's evaluator and the name of wb2 attached to
wb2's evaluator, double-checking that the latter in particular matches the
actual name used in the spreadsheet.
7. Vall evaluatorFormulaCell on the cell checked in step 3 and get:
IllegalArgumentException Invalid sheetIndex: -1.
 org.apache.poi.ss.formula.SheetRefEvaluator.<init>
(SheetRefEvaluator.java:36)

I've also tried putting in "[1]" or "1" in the map rather than my
spreadsheet name. I don't see in all this how the spreadsheet name in Excel
comes out as "[1]" in POI.

Looking at SheetRefEvaluator hasn't been as enlightening as I hoped.

Anyone?
-- 

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.watson@pnmac.com <melanie.petrosyan@pnmac.com>
www.PennyMacUSA.com <http://www.pennymacusa.com/>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message