Bug 55324 - formula returning #VALUE
Summary: formula returning #VALUE
Status: REOPENED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.10-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks: 55747 68258
  Show dependency tree
 
Reported: 2013-07-29 09:23 UTC by w.deprez
Modified: 2024-02-26 12:41 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description w.deprez 2013-07-29 09:23:28 UTC
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)
Comment 1 Nick Burch 2013-07-29 09:39:13 UTC
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.
Comment 2 w.deprez 2013-07-29 10:22:02 UTC
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);
Comment 3 w.deprez 2013-07-31 07:09:55 UTC
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 ?
Comment 4 w.deprez 2013-07-31 07:27:09 UTC
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 ?
Comment 5 w.deprez 2013-07-31 07:34:52 UTC
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
Comment 6 w.deprez 2013-08-01 08:58:57 UTC
(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
Comment 7 Nick Burch 2013-08-01 09:49:52 UTC
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
Comment 8 w.deprez 2013-08-01 12:55:51 UTC
(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);
Comment 9 w.deprez 2013-08-02 10:29:59 UTC
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 ?
Comment 10 w.deprez 2013-08-02 12:48:42 UTC
why POI puts in some case a reference or a value to the cell in the MID function ?
is there a workaround ?
Comment 11 w.deprez 2013-08-06 09:23:50 UTC
have you a solution for this problem ?
Comment 12 Nick Burch 2013-08-06 09:35:48 UTC
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!
Comment 13 w.deprez 2013-08-07 15:56:02 UTC
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 ?