Hidden sheets aren't external, they are just marked to not be displayed in a UI. POI doesn't care if sheets are hidden or not when calculating formulas, I do this all the time.
How are you looking for the calculated value of the cell? What you are getting back is a formula value, which is just a reference in this case to another cell, it looks like. That's a typical result from Cell.getCellFormula() or similar. What you need is wb.getCreationHelper().createFormulaEvaluator().evaluate(cell); On Fri, Apr 5, 2019 at 1:33 PM Kumar Thotapally <[email protected]> wrote: > 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
