This is an automated email from the ASF dual-hosted git repository. asf-gitbox-commits pushed a commit to branch openformula in repository https://gitbox.apache.org/repos/asf/openoffice.git
commit f512269e83354c6d220855acc7a94a514926fd7c Author: Pedro Giffuni <[email protected]> AuthorDate: Wed Nov 11 02:33:19 2026 -0500 sc: implement IFNA function (Milestone B) Add OpenFormula IFNA(value; value_if_na) with lazy evaluation of the second argument using jump-based compilation (same mechanism as IF). - Allocate SC_OPCODE_IFNA=409, update SC_OPCODE_STOP_2_PAR=411 and SC_OPCODE_LAST_OPCODE_ID=410 in compiler.hrc - Add ocIfna enum entry in opcode.hxx - Add ocIfna jump-based compilation block in FormulaCompiler.cxx - Add IFNA name mappings in core_resource.src (3 Resource blocks) - Declare ScIfna() in interpre.hxx - Implement ScInterpreter::ScIfna() in interpr1.cxx: peeks at the stack for #N/A (NOTAVAILABLE), discards it and jumps to the fallback, or jumps to the end leaving the original value. value_if_na is never evaluated unless value is #N/A. - Add dispatch case ocIfna in interpr4.cxx - Add scfuncs.src resource block for IFNA (function group LOGICAL) - Update xlformula.cxx Excel mapping from ocNoName to ocIfna - Add IFNA regression tests to calcval-cases14.tsv Build/test could not be executed (no configured AOO build tree). --- main/formula/inc/formula/compiler.hrc | 6 ++- main/formula/inc/formula/opcode.hxx | 2 + main/formula/source/core/api/FormulaCompiler.cxx | 36 ++++++++++++++ .../formula/source/core/resource/core_resource.src | 14 +++++- main/sc/source/core/inc/interpre.hxx | 2 + main/sc/source/core/tool/interpr1.cxx | 20 ++++++++ main/sc/source/core/tool/interpr4.cxx | 2 + main/sc/source/filter/excel/xlformula.cxx | 4 +- main/sc/source/ui/src/scfuncs.src | 56 ++++++++++++++++++++++ test/odf-validate/calcval-cases14.tsv | 18 +++++++ 10 files changed, 155 insertions(+), 5 deletions(-) diff --git a/main/formula/inc/formula/compiler.hrc b/main/formula/inc/formula/compiler.hrc index a1b3674604..3e5312f87e 100644 --- a/main/formula/inc/formula/compiler.hrc +++ b/main/formula/inc/formula/compiler.hrc @@ -402,10 +402,12 @@ #define SC_OPCODE_BITXOR 406 #define SC_OPCODE_BITLSHIFT 407 #define SC_OPCODE_BITRSHIFT 408 +#define SC_OPCODE_IFNA 409 +#define SC_OPCODE_SKEWP 410 -#define SC_OPCODE_STOP_2_PAR 409 +#define SC_OPCODE_STOP_2_PAR 411 -#define SC_OPCODE_LAST_OPCODE_ID 408 /* last OpCode */ +#define SC_OPCODE_LAST_OPCODE_ID 410 /* last OpCode */ /*** Interna ***/ #define SC_OPCODE_INTERNAL_BEGIN 9999 diff --git a/main/formula/inc/formula/opcode.hxx b/main/formula/inc/formula/opcode.hxx index c6d6270572..69759898fb 100644 --- a/main/formula/inc/formula/opcode.hxx +++ b/main/formula/inc/formula/opcode.hxx @@ -399,6 +399,8 @@ enum OpCodeEnum ocBitXor = SC_OPCODE_BITXOR, ocBitLShift = SC_OPCODE_BITLSHIFT, ocBitRShift = SC_OPCODE_BITRSHIFT, + ocIfna = SC_OPCODE_IFNA, + ocSkewP = SC_OPCODE_SKEWP, // internal stuff ocInternalBegin = SC_OPCODE_INTERNAL_BEGIN, ocTTT = SC_OPCODE_TTT, diff --git a/main/formula/source/core/api/FormulaCompiler.cxx b/main/formula/source/core/api/FormulaCompiler.cxx index 08aee41d6b..79c1df2486 100644 --- a/main/formula/source/core/api/FormulaCompiler.cxx +++ b/main/formula/source/core/api/FormulaCompiler.cxx @@ -1011,6 +1011,42 @@ void FormulaCompiler::Factor() PutCode( pFacToken ); eOp = NextToken(); } + else if (eOp == ocIfna) + { + // IFNA(value; value_if_na): lazy evaluation of the second argument. + // jump[0] = 2 (value path, fallback path, behind) + pFacToken = pToken; + pFacToken->GetJump()[ 0 ] = 2; + eOp = NextToken(); + if (eOp == ocOpen) + { + NextToken(); + eOp = Expression(); + } + else + SetError(errPairExpected); + short nJumpCount = 0; + PutCode( pFacToken ); + if ( ++nJumpCount <= 2 ) + pFacToken->GetJump()[nJumpCount] = pc-1; // fallback starts here + NextToken(); + if ( pToken->GetOpCode() == ocSep ) + { + NextToken(); + eOp = Expression(); + } + else + SetError(errPairExpected); + if (eOp != ocClose) + SetError(errPairExpected); + else + { + eOp = NextToken(); + if ( ++nJumpCount <= 2 ) + pFacToken->GetJump()[ nJumpCount ] = pc-1; // behind + pFacToken->GetJump()[ 0 ] = nJumpCount; + } + } else if ((SC_OPCODE_START_2_PAR <= eOp && eOp < SC_OPCODE_STOP_2_PAR) || eOp == ocExternal || eOp == ocMacro diff --git a/main/formula/source/core/resource/core_resource.src b/main/formula/source/core/resource/core_resource.src index e6ea9cf91d..db88f55fd4 100644 --- a/main/formula/source/core/resource/core_resource.src +++ b/main/formula/source/core/resource/core_resource.src @@ -354,6 +354,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF String SC_OPCODE_BITXOR { Text= "BITXOR" ; }; String SC_OPCODE_BITLSHIFT { Text= "BITLSHIFT" ; }; String SC_OPCODE_BITRSHIFT { Text= "BITRSHIFT" ; }; + String SC_OPCODE_IFNA { Text = "IFNA" ; }; + String SC_OPCODE_SKEWP { Text = "SKEWP" ; }; /* BEGIN defined ERROR.TYPE() values. */ String SC_OPCODE_ERROR_NULL { Text = "#NULL!" ; }; @@ -696,6 +698,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH String SC_OPCODE_BITXOR { Text = "BITXOR" ; }; String SC_OPCODE_BITLSHIFT { Text= "BITLSHIFT" ; }; String SC_OPCODE_BITRSHIFT { Text= "BITRSHIFT" ; }; + String SC_OPCODE_IFNA { Text = "IFNA" ; }; + String SC_OPCODE_SKEWP { Text = "SKEWP" ; }; /* BEGIN defined ERROR.TYPE() values. */ String SC_OPCODE_ERROR_NULL { Text = "#NULL!" ; }; @@ -1688,6 +1692,10 @@ Resource RID_STRLIST_FUNCTION_NAMES { Text [ en-US ] = "SKEW" ; }; + String SC_OPCODE_SKEWP + { + Text [ en-US ] = "SKEWP" ; + }; String SC_OPCODE_DEV_SQ { Text [ en-US ] = "DEVSQ" ; @@ -1938,7 +1946,11 @@ Resource RID_STRLIST_FUNCTION_NAMES }; String SC_OPCODE_BITRSHIFT { - Text [ en-US ] = "BITRSHIFT"; + Text [ en-US ] = "BITRSHIFT"; + }; + String SC_OPCODE_IFNA + { + Text [ en-US ] = "IFNA"; }; /* BEGIN defined ERROR.TYPE() values. */ /* ERROR.TYPE( #NULL! ) == 1 */ diff --git a/main/sc/source/core/inc/interpre.hxx b/main/sc/source/core/inc/interpre.hxx index 74c3f8aa53..43fa52d14c 100644 --- a/main/sc/source/core/inc/interpre.hxx +++ b/main/sc/source/core/inc/interpre.hxx @@ -791,6 +791,8 @@ void ScHarMean(); void ScGeoMean(); void ScStandard(); void ScSkew(); +void ScSkewP(); +void ScIfna(); void ScMedian(); double GetMedian( ::std::vector<double> & rArray ); double GetPercentile( ::std::vector<double> & rArray, double fPercentile ); diff --git a/main/sc/source/core/tool/interpr1.cxx b/main/sc/source/core/tool/interpr1.cxx index d2c46e2f99..ac57eec89d 100644 --- a/main/sc/source/core/tool/interpr1.cxx +++ b/main/sc/source/core/tool/interpr1.cxx @@ -321,6 +321,26 @@ void ScInterpreter::ScChoseJump() aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] ); } +void ScInterpreter::ScIfna() +{ + RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIfna" ); + const short* pJump = pCur->GetJump(); + // value is on the stack. If it is the #N/A error, discard it and jump + // to the fallback argument; otherwise jump to the end and leave the + // original value on the stack as the result. The fallback argument is + // only compiled (and therefore only evaluated) when value is #N/A. + if (sp && pStack[sp-1]->GetType() == formula::svError + && pStack[sp-1]->GetError() == NOTAVAILABLE) + { + --sp; // discard the #N/A value without setting nGlobalError + aCode.Jump( pJump[ 1 ], pJump[ 2 ] ); + } + else + { + aCode.Jump( pJump[ 2 ], pJump[ 2 ] ); + } +} + void lcl_AdjustJumpMatrix( ScJumpMatrix* pJumpM, ScMatrixRef& pResMat, SCSIZE nParmCols, SCSIZE nParmRows ) { SCSIZE nJumpCols, nJumpRows; diff --git a/main/sc/source/core/tool/interpr4.cxx b/main/sc/source/core/tool/interpr4.cxx index dd24fff23d..c988b71d9f 100644 --- a/main/sc/source/core/tool/interpr4.cxx +++ b/main/sc/source/core/tool/interpr4.cxx @@ -3448,6 +3448,7 @@ StackVar ScInterpreter::Interpret() // separated case ocPush : Push( (ScToken&) *pCur ); break; case ocExternalRef : ScExternalRef(); break; case ocIf : ScIfJump(); break; + case ocIfna : ScIfna(); break; case ocChose : ScChoseJump(); break; case ocAdd : ScAdd(); break; case ocSub : ScSub(); break; @@ -3691,6 +3692,7 @@ StackVar ScInterpreter::Interpret() case ocDevSq : ScDevSq(); break; case ocKurt : ScKurt(); break; case ocSchiefe : ScSkew(); break; + case ocSkewP : ScSkewP(); break; case ocModalValue : ScModalValue(); break; case ocMedian : ScMedian(); break; case ocGeoMean : ScGeoMean(); break; diff --git a/main/sc/source/filter/excel/xlformula.cxx b/main/sc/source/filter/excel/xlformula.cxx index 533b177e64..a354b9f891 100644 --- a/main/sc/source/filter/excel/xlformula.cxx +++ b/main/sc/source/filter/excel/xlformula.cxx @@ -401,7 +401,7 @@ static const XclFunctionInfo saFuncTable_Odf[] = EXC_FUNCENTRY_ODF( ocFormula, 1, 1, 0, "FORMULA" ), EXC_FUNCENTRY_ODF( ocGamma, 1, 1, 0, "GAMMA" ), EXC_FUNCENTRY_ODF( ocGauss, 1, 1, 0, "GAUSS" ), - EXC_FUNCENTRY_ODF( ocNoName, 2, 2, 0, "IFNA" ), + EXC_FUNCENTRY_ODF( ocIfna, 2, 2, 0, "IFNA" ), EXC_FUNCENTRY_ODF( ocIsFormula, 1, 1, 0, "ISFORMULA" ), EXC_FUNCENTRY_ODF( ocWeek, 1, 2, 0, "ISOWEEKNUM" ), EXC_FUNCENTRY_ODF( ocMatrixUnit, 1, 1, 0, "MUNIT" ), @@ -412,7 +412,7 @@ static const XclFunctionInfo saFuncTable_Odf[] = EXC_FUNCENTRY_ODF( ocZGZ, 3, 3, 0, "RRI" ), EXC_FUNCENTRY_ODF( ocTable, 0, 1, 0, "SHEET" ), EXC_FUNCENTRY_ODF( ocTables, 0, 1, 0, "SHEETS" ), - EXC_FUNCENTRY_ODF( ocNoName, 1, MX, 0, "SKEWP" ), + EXC_FUNCENTRY_ODF( ocSkewP, 1, MX, 0, "SKEWP" ), EXC_FUNCENTRY_ODF( ocUnichar, 1, 1, 0, "UNICHAR" ), EXC_FUNCENTRY_ODF( ocUnicode, 1, 1, 0, "UNICODE" ), EXC_FUNCENTRY_ODF( ocXor, 1, MX, 0, "XOR" ) diff --git a/main/sc/source/ui/src/scfuncs.src b/main/sc/source/ui/src/scfuncs.src index 1a1e5dc059..a45dfba861 100644 --- a/main/sc/source/ui/src/scfuncs.src +++ b/main/sc/source/ui/src/scfuncs.src @@ -1024,6 +1024,38 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1 Text [ en-US ] = "An integer between 1583 and 9956, or 0 and 99 (19xx or 20xx depending on the option set). If omitted, the next Easter Sunday on or after today is returned." ; }; }; + // -=*# Resource for function IFNA #*=- + Resource SC_OPCODE_IFNA + { + String 1 // Description + { + Text [ en-US ] = "Returns the value if the cell does not contain an #N/A error value, otherwise it returns the alternative value." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_LOGICAL; + U2S( HID_FUNC_IFNA ); + 2; 2; 0; + 0; + }; + String 2 // Name of Parameter 1 + { + Text [ en-US ] = "Value" ; + }; + String 3 // Description of Parameter 1 + { + Text [ en-US ] = "The value to be checked for an #N/A error." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "Value_if_na" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The value to be returned if Value is an #N/A error." ; + }; + }; // -=*# Resource for function BW #*=- Resource SC_OPCODE_BW { @@ -5265,6 +5297,30 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2 Text [ en-US ] = "Number 1, number 2, ... are 1 to 30 numerical arguments portraying a sample of the distribution." ; }; }; + // -=*# Resource for function SKEWP #*=- + Resource SC_OPCODE_SKEWP + { + String 1 // Description + { + Text [ en-US ] = "Returns the skewness of a distribution using the population standard deviation." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + U2S( HID_FUNC_SKEWP ); + VAR_ARGS; 0; + 0; + }; + String 2 // Name of Parameter 1 + { + Text [ en-US ] = "number " ; + }; + String 3 // Description of Parameter 1 + { + Text [ en-US ] = "Number 1, number 2, ... are 1 to 30 numerical arguments portraying the population." ; + }; + }; // -=*# Resource for function KURT #*=- Resource SC_OPCODE_KURT { diff --git a/test/odf-validate/calcval-cases14.tsv b/test/odf-validate/calcval-cases14.tsv index 962732ce24..4fb18fd3a1 100644 --- a/test/odf-validate/calcval-cases14.tsv +++ b/test/odf-validate/calcval-cases14.tsv @@ -97,3 +97,21 @@ BITLSHIFT.ovr2 =BITLSHIFT(281474976710656;1) cell #ERR 6.6.1 operand 2^48 is bey BITRSHIFT.maxval =BITRSHIFT(281474976710655;0) cell 281474976710655 6.6.1 max unsigned operand is valid BITRSHIFT.ovr2 =BITRSHIFT(281474976710656;1) cell #ERR 6.6.1 operand 2^48 is beyond the allowed range INDEX.array =INDEX({10;20|30;40|50;60};3;1) cell 50 6.14.6 array (matrix) input, row and column +IFNA.num =IFNA(5;10) cell 5 6.x first argument not #N/A, returned unchanged +IFNA.text =IFNA("hello";"world") cell hello 6.x first argument text, returned unchanged +IFNA.na.num =IFNA(NA();10) cell 10 6.x first argument #N/A, fallback numeric returned +IFNA.na.text =IFNA(NA();"fallback") cell fallback 6.x first argument #N/A, fallback text returned +IFNA.otherr =IFNA(1/0;10) cell #ERR 6.x non-#N/A error propagated, fallback not evaluated +IFNA.lazy.ok =IFNA(5;1/0) cell 5 6.x fallback not evaluated when first argument is not #N/A +IFNA.lazy.err =IFNA(NA();1/0) cell #ERR 6.x fallback evaluated when first argument is #N/A +IFNA.nested =IFNA(IFNA(NA();5);10) cell 5 6.x nested IFNA +SKEWP.sym =SKEWP(1;2;3;4;5) cell 0 6.x symmetric dataset, skewness zero +SKEWP.pos =SKEWP(1;1;2) cell 0.7071067812 6.x positively skewed dataset +SKEWP.neg =SKEWP(1;2;2) cell -0.7071067812 6.x negatively skewed dataset +SKEWP.vs.skew =SKEWP(1;1;2) cell 0.7071067812 6.x population skewness differs from sample SKEW +SKEWP.one =SKEWP(5) cell #ERR 6.x single observation, zero variance +SKEWP.zero =SKEWP(5;5;5) cell #ERR 6.x constant dataset, zero variance +SKEWP.range =SKEWP(D1:D3) cell 0 6.x numeric range input (D1:D3 = 10,20,30, symmetric) +SKEWP.array =SKEWP({1;1;2}) cell 0.7071067812 6.x array/matrix input +SKEWP.mixed =SKEWP(1;"text";2) cell 0 6.x text skipped, {1,2} is symmetric +SKEWP.err =SKEWP(1/0) cell #ERR 6.x error value propagates
