55324
2013-07-29 09:23:28 +0000
formula returning #VALUE
2015-08-09 20:08:49 +0000
1
1
1
Unclassified
POI
HSSF
3.10-dev
PC
All
REOPENED
P2
normal
---
55747
1
w.deprez
dev
0
oldest_to_newest
168889
0
w.deprez
2013-07-29 09:23:28 +0000
When you create an excel file and use the following forumula, if condition is true, we see a #VALUE in Excel. However if you click on the cell (or press F2 key) and press enter, the formula gets populated.
=IF(LEN(A1)>4,MID(A1,4,2),A1)
168890
1
apache
2013-07-29 09:39:13 +0000
As detailed in the documentation, and covered in a lot of detail at <http://poi.apache.org/spreadsheet/eval.html>, after making changes to formulas or the cells they depend on you need to re-evaluate the formulas to update the cached value that gets stored with them in the file.
168894
2
w.deprez
2013-07-29 10:22:02 +0000
I have read the documentation. I added this line before saving and closing the workbook but nothing change.
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
evaluator.clearAllCachedResultValues and
evaluator.evaluateAll();
wb.setForceFormulaRecalculation(true);
168965
3
w.deprez
2013-07-31 07:09:55 +0000
After few tests,
* LEN(A1) works perfectly
* MID(A1,4,2) works perfectly
* IF (LEN(A1)>0, A1, A2) works perfectly
* IF (1==1, MID(A1,4,2), A1) works bad
* IF (1==1, A1, MID(A1,4,2)) works bad
all formulas are re-evaluate before closing workbook.
All works perfectly with XSSF but not with HSSF.
are you sure there is no bug when combinating IF instruction and MID ?
168966
4
w.deprez
2013-07-31 07:27:09 +0000
After few tests,
* LEN(A1) works perfectly
* MID(A1,4,2) works perfectly
* IF (LEN(A1)>0, A1, A2) works perfectly
* IF (1=1, MID(A1,4,2), A1) works bad
* IF (1=1, A1, MID(A1,4,2)) works bad
* IF (MID(A1,4,2)="ab", A1, "bad") works fine
all formulas are re-evaluate before closing workbook.
All works perfectly with XSSF but not with HSSF.
are you sure there is no bug when combinating IF instruction and MID ?
168967
5
w.deprez
2013-07-31 07:34:52 +0000
excuse me there is a bug in my comment,
* LEN(A1) works perfectly
* MID(A1,4,2) works perfectly
* IF (LEN(A1)>0, A1, A2) works perfectly
* IF (1=1, MID(A1,4,2), A1) works bad
* IF (MID(A1,4,2)="ab", A1, "bad") works fine
* IF (1=1, A1, MID(A1,4,2)) works fine
* IF (1>2, A1, MID(A1,4,2)) works bad
168994
6
w.deprez
2013-08-01 08:58:57 +0000
(In reply to Nick Burch from comment #1)
> As detailed in the documentation, and covered in a lot of detail at
> <http://poi.apache.org/spreadsheet/eval.html>, after making changes to
> formulas or the cells they depend on you need to re-evaluate the formulas to
> update the cached value that gets stored with them in the file.
are you sure there is no bug ?
=IF(1=1,MID(A1,4,2),A1) works bad
=IF(1=1,MID("abcdefgh",4,2),A1) works perfectly.
thanks for your answer
169004
7
apache
2013-08-01 09:49:52 +0000
There might be a bug, it's always possible...
I'd suggest you try running BiffViewer on the file saved by POI, and then on a file loaded by excel + explicitly evaluated + saved. Find the formula record for the cell, and compare the POI one to the Excel one. Are the ptgs the same? Cached value the same? etc
169016
8
w.deprez
2013-08-01 12:55:51 +0000
(In reply to Nick Burch from comment #7)
> There might be a bug, it's always possible...
>
> I'd suggest you try running BiffViewer on the file saved by POI, and then on
> a file loaded by excel + explicitly evaluated + saved. Find the formula
> record for the cell, and compare the POI one to the Excel one. Are the ptgs
> the same? Cached value the same? etc
it seems that cached value are the same but ptgs are not (one difference on ptg[6])
here are ptgs generate by POI
[FORMULA]
.row = 0x0004
.col = 0x0004
.xfindex= 0x000F
.value = <string> [00, 00, 00, 00, 00, 00]
.options = 0x0002
.alwaysCalc= false
.calcOnLoad= true
.shared = false
.zero = 0x00000000
Ptg[0]=org.apache.poi.ss.formula.ptg.IntPtg [1].
Ptg[1]=org.apache.poi.ss.formula.ptg.IntPtg [2].
Ptg[2]=class org.apache.poi.ss.formula.ptg.GreaterThanPtg.
Ptg[3]=org.apache.poi.ss.formula.ptg.AttrPtg [if dist=9].
Ptg[4]=org.apache.poi.ss.formula.ptg.RefPtg [A5]R
Ptg[5]=org.apache.poi.ss.formula.ptg.AttrPtg [skip dist=21].
Ptg[6]=org.apache.poi.ss.formula.ptg.RefPtg [A5]R
Ptg[7]=org.apache.poi.ss.formula.ptg.IntPtg [4].
Ptg[8]=org.apache.poi.ss.formula.ptg.IntPtg [2].
Ptg[9]=org.apache.poi.ss.formula.ptg.FuncPtg [MID nArgs=3]V
Ptg[10]=org.apache.poi.ss.formula.ptg.AttrPtg [skip dist=3].
Ptg[11]=org.apache.poi.ss.formula.ptg.FuncVarPtg [IF nArgs=3]V
[/FORMULA]
Offset=0x00003801(14337) recno=174 sid=0x0207 size=0x0005(5)
[STRING]
.string = $$
[/STRING]
ptgs generate by Excel
[FORMULA]
.row = 0x0004
.col = 0x0004
.xfindex= 0x000F
.value = <string> [00, 00, 84, 2C, 4F, 06]
.options = 0x0000
.alwaysCalc= false
.calcOnLoad= false
.shared = false
.zero = 0xFE040005
Ptg[0]=org.apache.poi.ss.formula.ptg.IntPtg [1].
Ptg[1]=org.apache.poi.ss.formula.ptg.IntPtg [2].
Ptg[2]=class org.apache.poi.ss.formula.ptg.GreaterThanPtg.
Ptg[3]=org.apache.poi.ss.formula.ptg.AttrPtg [if dist=9].
Ptg[4]=org.apache.poi.ss.formula.ptg.RefPtg [A5]R
Ptg[5]=org.apache.poi.ss.formula.ptg.AttrPtg [skip dist=21].
Ptg[6]=org.apache.poi.ss.formula.ptg.RefPtg [A5]V
Ptg[7]=org.apache.poi.ss.formula.ptg.IntPtg [4].
Ptg[8]=org.apache.poi.ss.formula.ptg.IntPtg [2].
Ptg[9]=org.apache.poi.ss.formula.ptg.FuncPtg [MID nArgs=3]V
Ptg[10]=org.apache.poi.ss.formula.ptg.AttrPtg [skip dist=3].
Ptg[11]=org.apache.poi.ss.formula.ptg.FuncVarPtg [IF nArgs=3]V
[/FORMULA]
Offset=0x00001022(4130) recno=169 sid=0x0207 size=0x0005(5)
[STRING]
.string = $$
[/STRING]
$$ was the formula result value
Is this difference on ptg is important ? are there any solution ?
all Formula where re-evaluate before closing workbook.
if (_evaluator == null)
_evaluator = _workbook.getCreationHelper().createFormulaEvaluator();
_evaluator.clearAllCachedResultValues();
for(int sheetNum = 0; sheetNum < _workbook.getNumberOfSheets(); sheetNum++) {
Sheet sheet = _workbook.getSheetAt(sheetNum);
for(Row r : sheet) {
for(Cell c : r) {
if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
_evaluator.setDebugEvaluationOutputForNextEval(true);
_evaluator.evaluateFormulaCell(c);
}
}
}
}
_workbook.setForceFormulaRecalculation(true);
169032
9
w.deprez
2013-08-02 10:29:59 +0000
it seems that the ptg[6] was a reference in POI but in Excel it was a value.
It think that the bug is in OperandClassTransformer but i don't know how to resolve it.
Can you help me ?
169037
10
w.deprez
2013-08-02 12:48:42 +0000
why POI puts in some case a reference or a value to the cell in the MID function ?
is there a workaround ?
169173
11
w.deprez
2013-08-06 09:23:50 +0000
have you a solution for this problem ?
169174
12
apache
2013-08-06 09:35:48 +0000
You'll need to step through the formula evaluator in a debugger, and try to work out what area of the code is responsible for outputting the Ptg different to Excel. Once that's narrowed down, the fix will hopefully be easier to identify!
169260
13
w.deprez
2013-08-07 15:56:02 +0000
If I change the second line in the resource file functionMetadata.txt by
"1 IF 2 3 R V V V"
all works perfectly.
by default, MID Function return Value class. Or in IF function, the second and third parameter is a reference class. Could it be the problem ?