poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Woolsey <greg.wool...@gmail.com>
Subject Re: Cell Calculations by reference to other cells in hidden sheets within the same workbook
Date Mon, 08 Apr 2019 17:12:24 GMT
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 <kthotapally@emmes.com>
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(&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