You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Cheong Kok Kik <ko...@streamsolutions.com.sg> on 2009/01/20 19:27:14 UTC
Formula value displayed inconsistently
Hi,
I'm using poi-3.2-FINAL-20081019.jar, poi-contrib-3.2-FINAL-20081019.jar,
poi-scratchpad-3.2-FINAL-20081019.jar.
I've a spreadsheet (quite a complicated spreadsheet) with multiple worksheets.
My objective is to update few cell within worksheet A, and then read out the
content of worksheet Z.
In worksheet Z, it has a cell Z1 some basic formula which has this formula
'=IF(A20="","",D20+M20+N20)',
And N20 is actually pointing to another worksheet with some formula
'=WorksheetX!I23'.
And cell WorksheetX!I23 is having formula of
'=IF(WorksheetX!A23="","",(WorksheetW!F8)*DTABLE!FB8)'
There are more complicated formula and few more level deeper, but POI has been
successfully reading it out except this one.
The problem is cell Z1 returns nothing(through POI), but on Excel spreadsheet it
has correct value displayed. My further investigation found out that N20
basically return nothing, causing the summation of D20 + M20 + N20 to return
nothing. Then i further test on reading WorksheetX!I23, it's returning nothing
(through POI). What puzzle me is the formula in WorksheetX!I23, when copied to
another cell, it does return a correct value and could be read by POI. So,
there's not problem with the formula as it could be read, but why it can't read
it from cell WorksheetX!I23?
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formula value displayed inconsistently
Posted by Cheong Kok Kik <ko...@streamsolutions.com.sg>.
Hi Josh,
Thanks for the reply. I've tried using notifyUpdateCell after the cell update
but it's still giving me the same output.
The first section will update a cell on a worksheet, the the next section.
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
// update
sheet = wb.getSheet(sourceWorkSheetName);
HSSFRow row = sheet.getRow(17);
HSSFCell cell = row.getCell( 2);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(userAge));
evaluator.notifyUpdateCell(cell);
// Displaying output
sheet = wb.getSheet(targetWorkSheetName);
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++)
{
row = sheet.getRow(r);
String value = "";
if (row != null)
{
int cells = row.getPhysicalNumberOfCells();
System.out.println("\r\nROW " + row.getRowNum());
for (int c = 0; c < cells; c++)
{
cell = row.getCell(c);
if (cell != null)
{
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA :
evaluator.evaluateFormulaCell(cell);
HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell);
Double numericCellValue = new Double(cellValue.getNumberValue());
if (numericCellValue.isNaN())
{
value = value + cell.getRichStringCellValue().toString();
}
else
{
value = value + numericCellValue.toString();
}
break;
case HSSFCell.CELL_TYPE_NUMERIC :
value = value + cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING :
value = value + cell.getRichStringCellValue().getString();
break;
}
}
}
System.out.println(value);
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formula value displayed inconsistently
Posted by Cheong Kok Kik <ko...@streamsolutions.com.sg>.
Hi Josh,
I've tried to use 'case HSSFCell.CELL_TYPE_ERROR :' to collect error message and
number on those error cell. The cell that has no value displayed basically show
a) '-60' , or
b) '~CIRCULAR~REF~', or
c) java.lang.RuntimeException: Error Value can only be 0,7,15,23,29,36 or 42. It
cannot be -60
Any idea what does it mean?
Regards,
KK
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formula value displayed inconsistently
Posted by Cheong Kok Kik <ko...@streamsolutions.com.sg>.
Hi Josh,
Thanks for you reply. Yeap, i do agree as POI could do the multiple reference as
i've this workbook with 30 worksheets and it could more complicated formula.
Anyway, I've fixed the problem by doing some workaround (eventhough i couldn't
find a good explanation). I would like to share it here so someone might need it.
(I couldn't upload the spreadsheet as it has customer business detail and
complex formula for their product calculation.)
The Process:
I'm using divide and checking strategy. To 'zoom' into formula to find which
particular cell has problem, and keeping on zooming until i reach a cell (PCell)
with a simple formula having 2 cell multiplying each other. These 2 cells has no
problem being read through POI. So i stopped at here. Then i started to copy
back formula in PCell (problematic Cell) into its parent cell. Then, copy back
its parent cell formula to higher level. This process continue till few more
times (the formula was a bit deep), i found out that POI could read the output
correctly now.
Weird but the circular reference has gone.
Hope this help someone to save some time. (After spending few days and nights).
Keeping to POI spirit moving !
Regards,
Kok Kik
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formula value displayed inconsistently
Posted by Josh Micich <jo...@gmail.com>.
Hello Kok Kik,
There are a few test cases which show that POI *can* handle multiple
references to the same cell within one formula. For example:
TestFormulaEvaluatorBugs.testSlowEvaluate45376(),
TestEvaluationCache.testMediumComplex() and TestFormulasFromSpreadsheet.
There is not enough detail in your description to distinguish your use case
from what these tests do. Please try to reproduce the problem in a
spreadsheet that you can upload (i.e. with no customer data). You can
upload it along with the example code in a new bugzilla entry.
regards,
Josh
Re: Formula value displayed inconsistently
Posted by Cheong Kok Kik <ko...@streamsolutions.com.sg>.
The excel file is complex, but there's no circular reference in the excel
spreadsheet. I've tried to create 2 simple worksheet and having worksheet1 cell
1 depend on worksheet 2 cell2, and worksheet 2 cell2 depends on worksheet 1
cell1, Excel immediately prompt the circular reference issue. The value will be
empty and not updated.
Back to the excel file that having the problem, It looks like POI couldn't
handle cell with formula that have multiple reference back to a single cell.
Once i've use the formula in the single cell and update 1 level upper formula
(which use the single cell), the problem gone for the row.
But the issue is there are 100 rows that need to be updated. And there are few
worksheets having similar issue.
Couldn't send out the spreadsheet as it's customer property.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formula value displayed inconsistently
Posted by Josh Micich <jo...@gmail.com>.
Hello Kok Kik,
When you use POI to 'read' the value of a formula cell, are you using
HSSFCell.getNumericCellValue() or HSSFFormulaEvaluator.evaluate(Cell)?
Calling HSSFCell.get~CellValue() on a formula cell will only return the
cached formula result (as last set by Excel or possibly POI). Excel usually
sets those cached results correctly, but if they were wrong, it could
explain what you are seeing. Furthermore, these values aren't automatically
updated by POI. So if you have changed other cell values that the formula
depends upon, the cached value will probably be wrong. Depending on option
flags, Excel may recalculate formulas when you open the workbook. The
formula cell value you see in Excel may not be the same as what is read in
POI with HSSFCell.get~CellValue(), which can be confusing.
On the other hand, if you are using HSSFFormulaEvaluator.evaluate(Cell) you
won't have those problems (it ignores the cached formula results saved by
Excel). Instead, HSSFFormulaEvaluator does its own caching, so if you need
to change formula input values *during* evaluations you'll also need to call
HSSFFormulaEvaluator.notifyUpdateCell(HSSFCell) to keep things in sync.
If this doesn't help, please open a bugzilla bug (
https://issues.apache.org/bugzilla/enter_bug.cgi), upload the sample file
and some java code to reproduce the problem.
regards,
Josh