Hi Manuel,
You can calculate in the same Workbook from different Worksheets.
You cannot calculate fresh values from another Workbook, but you can use the
cached result saved in your workbook.
Here is an example that grabs all the named cells with numeric data from a
workbook:
Workbook wb = new HSSFWorkbook(new ByteArrayInputStream(bytes));
Sheet sh = wb.getSheetAt(0);
FormulaEvaluator wbeval =
wb.getCreationHelper().createFormulaEvaluator();
DecimalFormat fmt = new DecimalFormat("###.00");
//collect tagged cells into a map
Map<String, String> model = new HashMap<String, String>();
for (int i = 0; i < wb.getNumberOfNames(); i++) {
Name nm = wb.getNameAt(i);
if(nm.isDeleted()) continue;
String key = nm.getNameName();
String nameFmla = nm.getRefersToFormula();
CellReference ref = new CellReference(nameFmla);
Row row = sh.getRow(ref.getRow());
if (row != null) {
Cell cell = row.getCell(ref.getCol());
if (cell != null) {
try {
// try to evaluate the cell
CellValue cv = wbeval.evaluate(cell);
if (cv != null && cv.getCellType() ==
Cell.CELL_TYPE_NUMERIC) {
double dval = cv.getNumberValue();
model.put(key, fmt.format(dval));
}
} catch (RuntimeException e){
// YK: catch any errors thrown by the formula evaluator
// the safe fallback is to retrieve the cached formula
result
if (cell.getCachedFormulaResultType() ==
Cell.CELL_TYPE_NUMERIC){ //ensure that the cell is numeric
double dval = cell.getNumericCellValue();
model.put(key, fmt.format(dval));
}
}
}
}
}
Regards,
Dave
On Oct 22, 2010, at 1:15 PM, Adrian Butnaru wrote:
> Marc.
> Thank you for your advice.
> Do you mean I will not be able to calculate in a cell a formula as the
> following one?
>
> Sheet1!A1 + Sheet2!B5 + Sheet3!C9
>
> I thought this is a basic feature.
>
> Regards,
> Manuel
>
> Op 20-10-2010 17:14, Mark Beardsley schreef:
>> I cannot be certain whether there have been any major changes recently, but I
>> do remember that is was not possible to calculate formulae that spanned
>> multiple worksheets; certainly with HSSF it was not. This may have changed
>> but it is a potential limitation you do need to be aware of.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
