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 [email protected] <[email protected]> www.PennyMacUSA.com <http://www.pennymacusa.com/>
