I am re-posting this as my previous post was not properly formatted. I
apologize.
Hi,
I have a Excel workbook/spreadsheet with 1 visible sheet and 6 hidden
sheets. Formula cells on visible sheet (Sheet 0) are pointing to cells on
hidden sheets within the same workbook.One of the formulas is to calculate
age based on two dates.
When I try to retrieve calculated value of cell containing age, I am getting
a string,
"'Unformatted Values'!AD13"
where 'Unformatted Values' is one of the hidden sheets. I have seen methods
to link external workbooks and to evaluate all formulas using
FormulaEvaluator mainWorkbookEvaluator =
wb.getCreationHelper().createFormulaEvaluator();
Map<String, FormulaEvaluator> workbooks = new HashMap<String,
FormulaEvaluator>();
workbooks.put(wb.getSheetName(0), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(1), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(2), mainWorkbookEvaluator);
etc., when trying to setup referenced workbooks using,
mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
I am getting the following exception about attempting to register same
workbook under different names.
java.lang.IllegalArgumentException: Attempted to register same workbook
under names 'Unformatted Values' and 'NP T-Scores' at
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.(CollaboratingWorkbooksEnvironment.java:114)
at
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setup(CollaboratingWorkbooksEnvironment.java:75)
at
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setupFormulaEvaluator(CollaboratingWorkbooksEnvironment.java:89)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setupReferencedWorkbooks(HSSFFormulaEvaluator.java:103)
at com.emmes.hbb.POICalculator.main(POICalculator.java:61)
How can I create a link/reference between visible sheet and all the other
hidden sheets for evaluating formulas?
Thanks for your help.
--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
