Thanks Nick,
but then I am missing something...
Is there a way to read the cached value directly, without passing through
the "extracted" version?
I mean, I have no need to recalculate them on the fly (as I am interested in
reading what it was saved = I am just interested in getting the value...)
...
...
NOW I GET IT! CACHED VALUES!
And in fact this works fine (no need to use the Evaluator, as I did in the
past).
The code reads content from a named cell, even if it contains a
NotImplementedFunction:
int namedCellIdx = wb.getNameIndex(cellName);
if (namedCellIdx <=0)
throw new NamedCellNotFoundException(cellName);
Name aNamedCell = wb.getNameAt(namedCellIdx);
AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula());
CellReference[] crefs = aref.getAllReferencedCells();
for (int i=0; i<crefs.length; i++) {
Sheet sheet = wb.getSheet(crefs[i].getSheetName());
Row r = sheet.getRow(crefs[i].getRow());
Cell c = r.getCell(crefs[i].getCol());
switch (c.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = c.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(c)) {
cellValue = c.getDateCellValue();
} else {
cellValue = c.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = c.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
switch(c.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_STRING:
cellValue = c.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(c)) {
cellValue = c.getDateCellValue();
} else {
cellValue = c.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = c.getBooleanCellValue();
break;
}
break;
default:
break;
}
I post this for potential future "dummy users" (as I am) <<<
I also found this post now, which is exactly what I needed:
http://stackoverflow.com/questions/7608511/java-poi-how-to-read-excel-cell-value-and-not-the-formula-computing-it
Unfortunately I have no time to implement FREQUENCY from scratch, but I
solved somehow the issue :|
Thank you very much,*****
this saved the day!
Stefano
----- Original Message -----
From: "Nick Burch" <[email protected]>
To: "POI Users List" <[email protected]>
Sent: Wednesday, January 11, 2012 2:38 PM
Subject: Re: POI-3.7 formula support (FREQUENCY)
On Wed, 11 Jan 2012, Stefano Bianchi wrote:
I get the file as a long string where the value calculated with FREQUENCY
formulas ARE included. This way, parsing the string, I found a workaround
to get the calculated value.
These will be the cached formula values, from the last time excel
evaluated them. You can get them yourself from the cell if you want, but
if you make any changes to the file then these values will be incorrect
(that's why you need to run the formula evaluator after changes)
If you're interested in implementing any missing functions (such as
frequency), let us know and we can point you at some guides for doing this
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
