I see your problem. XSSFCell.toString() returns the formula String for a formula cell type, not the cached cell value.
With POI, you need your own method that does a bit more work based on the reported CellType for each cell. Even more if you want the value formatted as it would appear in Excel. For CellType = FORMULA, you need to then look at Cell.getCachedFormulaResultType() and then return the desired representation of the appropriate get*CellValue() method. On Mon, Apr 8, 2019 at 8:18 AM Kumar Thotapally <[email protected]> wrote: > Greg, thank you. > > I have tried evaluate(cell) as well as evaluateAll() and I am still getting > the reference to another cell on a hidden sheet instead of calculated > value. > > Following is my program: > > public class POICalculator { > private DataFormatter formatter; > > public static void main(String[] args) { > // To fill cell values in calculator spreadsheet and > // obtain computed cell value > InputStream ExcelFileToRead; > try { > ExcelFileToRead = new > FileInputStream("C:\\temp\\XLS\\NPCalculatorBlack.xls"); > HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead); > HSSFWorkbook test = new HSSFWorkbook(); > HSSFSheet sheet = wb.getSheetAt(0); > HSSFRow row; > HSSFCell cell; > String examDate = "04/02/2019"; > String birthDate = "01/01/1980"; > FormulaEvaluator mainWorkbookEvaluator = > wb.getCreationHelper().createFormulaEvaluator(); > Map<String, FormulaEvaluator> workbooks = new HashMap<String, > FormulaEvaluator>(); > // Add this workbook > /* workbooks.put(wb.getSheetName(0), mainWorkbookEvaluator); > workbooks.put(wb.getSheetName(1), mainWorkbookEvaluator); > workbooks.put(wb.getSheetName(2), mainWorkbookEvaluator); > workbooks.put(wb.getSheetName(3), mainWorkbookEvaluator); > workbooks.put(wb.getSheetName(4), mainWorkbookEvaluator); > workbooks.put(wb.getSheetName(5), mainWorkbookEvaluator); > workbooks.put(wb.getSheetName(6), mainWorkbookEvaluator); > mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks); > */ > mainWorkbookEvaluator.evaluateAll(); > // input cell value for F3 > sheet.getRow(2).getCell(5).setCellValue(examDate); > // input cell value for H3 > sheet.getRow(2).getCell(7).setCellValue(birthDate); > HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); > *wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); > cell = sheet.getRow(4).getCell(1); > wb.getCreationHelper().createFormulaEvaluator().evaluate(cell);* > > // output computed AGE value from cell B5 - Expected result: 39 > String age = sheet.getRow(4).getCell(1).toString(); > System.out.println(age); > } catch (Exception e) { > e.printStackTrace(); > } > } > } > Output is shown as: > > *'Unformatted Values'!AD13* > Where 'Unformatted Values' is a hidden sheet within the same workbook. > > > > -- > Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
