poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kumar Thotapally <kthotapa...@emmes.com>
Subject Re: Cell Calculations by reference to other cells in hidden sheets within the same workbook
Date Mon, 08 Apr 2019 15:11:00 GMT
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(&quot;C:\\temp\\XLS\\NPCalculatorBlack.xls&quot;);
 HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
 HSSFWorkbook test = new HSSFWorkbook(); 
 HSSFSheet sheet = wb.getSheetAt(0);
 HSSFRow row; 
 HSSFCell cell;
 String examDate = &quot;04/02/2019&quot;;
 String birthDate = &quot;01/01/1980&quot;;
 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
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message