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
