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)
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.
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);
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 ?
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 ?
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
(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
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
(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);
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 ?
why POI puts in some case a reference or a value to the cell in the MID function ? is there a workaround ?
have you a solution for this problem ?
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!
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 ?