fdo#56124 add functions IFERROR and IFNA to calc as in ODFF1.2

Change-Id: I6403b51ac8c710ad3b8d2625e1482971f50e6b1d

9 files changed, 283 insertions, 4 deletions

diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index 2e2aa4c6c9e3..e273ce415444 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -433,6 +433,8 @@
 #define HID_FUNC_NICHT                                          "SC_HID_FUNC_NICHT"
 #define HID_FUNC_WAHR                                           "SC_HID_FUNC_WAHR"
 #define HID_FUNC_WENN                                           "SC_HID_FUNC_WENN"
+#define HID_FUNC_IFERROR                                        "SC_HID_FUNC_IFERROR"
+#define HID_FUNC_IFNA                                           "SC_HID_FUNC_IFNA"
 #define HID_FUNC_ODER                                           "SC_HID_FUNC_ODER"
 #define HID_FUNC_UND                                            "SC_HID_FUNC_UND"
 #define HID_FUNC_XOR                                            "SC_HID_FUNC_XOR"
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index d9d643fae438..01fe181899b0 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -671,6 +671,71 @@ void testFuncCOUNTIF(ScDocument* pDoc)
     CPPUNIT_ASSERT_MESSAGE("We shouldn't count empty string as valid number.", result == 0.0);
 }
 
+void testFuncIFERROR(ScDocument* pDoc)
+{
+    // IFERROR/IFNA (fdo 56124)
+
+    // Empty A1:A39 first.
+    clearRange(pDoc, ScRange(0, 0, 0, 0, 40, 0));
+
+    // Raw data (rows 1 through 9)
+    const char* aData[] = {
+        "1",
+        "e",
+        "=SQRT(4)",
+        "=SQRT(-2)",
+        "=A4",
+        "=1/0",
+        "bar",
+        "4",
+        "gee"
+    };
+
+    SCROW nRows = SAL_N_ELEMENTS(aData);
+    for (SCROW i = 0; i < nRows; ++i)
+        pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
+
+    printRange(pDoc, ScRange(0, 0, 0, 0, 8, 0), "data range for IFERROR/IFNA");
+
+    // formulas and results
+    struct {
+        const char* pFormula; double fResult;
+    } aChecks[] = {
+        { "=IFERROR(A1;-7)",                       1 },
+        { "{=IFERROR(3*A1:A2;2002)}",              3 },
+        { "{=IFERROR(3*A1:A2;1998)}",           1998 },
+        { "=IFERROR(A3;9)",                        4 },
+        { "=IFERROR(A4;9)",                        9 },
+        { "=IFERROR(A5;-7",                       -7 },
+        { "=IFERROR(A6;-7)",                      -7 },
+        { "=IFERROR(A2;-7)",                      -7 },
+        { "=IFNA(VLOOKUP(\"4\",A7:A9;1;0);-2)",    4 },
+        { "=IFNA(VLOOKUP(\"fop\",A7:A9;1;0);-2)", -2 }
+    };
+
+    nRows = SAL_N_ELEMENTS(aChecks);
+    for (SCROW i = 0; i < nRows; ++i)
+    {
+        SCROW nRow = 20 + i;
+        pDoc->SetString(0, nRow, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
+    }
+    pDoc->CalcAll();
+
+    for (SCROW i = 0; i < nRows; ++i)
+    {
+        double result;
+        SCROW nRow = 20 + i;
+        pDoc->GetValue(0, nRow, 0, result);
+        bool bGood = result == aChecks[i].fResult;
+        if (!bGood)
+        {
+            cerr << "row " << (nRow+1) << ": formula" << aChecks[i].pFormula
+                << "  expected=" << aChecks[i].fResult << "  actual=" << result << endl;
+            CPPUNIT_ASSERT_MESSAGE("Unexpected result for COUNTIF", false);
+        }
+    }
+}
+
 void testFuncVLOOKUP(ScDocument* pDoc)
 {
     // VLOOKUP
@@ -4154,6 +4219,8 @@ void Test::testFunctionLists()
         "CELL",
         "CURRENT",
         "FORMULA",
+        "IFERROR",
+        "IFNA",
         "INFO",
         "ISBLANK",
         "ISERR",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 762b818eadce..267e65947d48 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -368,6 +368,7 @@ bool LookupQueryWithCache( ScAddress & o_rResultPos,
         const ScQueryParam & rParam ) const;
 
 void ScIfJump();
+void ScIfError( bool bNAonly );
 void ScChoseJump();
 
 // Be sure to only call this if pStack[sp-nStackLevel] really contains a
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index ec85a01f7314..0c7817b88450 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -243,6 +243,138 @@ void ScInterpreter::ScIfJump()
 }
 
 
+void ScInterpreter::ScIfError( bool bNAonly )
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIfError" );
+    const short* pJump = pCur->GetJump();
+    if ( !pJump )
+    {
+        PushIllegalParameter();
+        return;
+    }
+    short nJumpCount = pJump[ 0 ];
+    if ( nJumpCount != 2 )
+    {
+        PushIllegalArgument();
+        return;
+    }
+
+    if ( nGlobalError == 0 || ( bNAonly && nGlobalError != NOTAVAILABLE ) )
+    {
+        MatrixDoubleRefToMatrix();
+        switch ( GetStackType() )
+        {
+            case svDoubleRef:
+            case svSingleRef:
+            {
+                ScAddress aAdr;
+                if ( !PopDoubleRefOrSingleRef( aAdr ) )
+                {
+                    PushIllegalArgument();
+                    return;
+                }
+                ScBaseCell* pCell = GetCell( aAdr );
+                sal_uInt16 nErr = GetCellErrCode( pCell );
+                if ( nErr == 0 || ( bNAonly && nErr != NOTAVAILABLE ) )
+                {
+                    // no error, return 1st argument
+                    if ( pCell && pCell->HasValueData() )
+                    {
+                        PushDouble( GetCellValue(aAdr, pCell) );
+                        aCode.Jump( pJump[ nJumpCount  ], pJump[ nJumpCount ] );
+                        return;
+                    }
+                    else
+                    {
+                        String aInputString;
+                        GetCellString( aInputString, pCell );
+                        PushString( aInputString );
+                        aCode.Jump( pJump[ nJumpCount  ], pJump[ nJumpCount ] );
+                        return;
+                    }
+                }
+            }
+            break;
+            case svMatrix:
+            {
+                nFuncFmtType = NUMBERFORMAT_NUMBER;
+                ScMatrixRef pMat = PopMatrix();
+                if ( pMat && ( !nGlobalError || ( bNAonly && nGlobalError != NOTAVAILABLE ) ) )
+                {
+                    FormulaTokenRef xNew;
+                    ScTokenMatrixMap::const_iterator aMapIter;
+                    SCSIZE nCols, nRows;
+                    pMat->SetErrorInterpreter( NULL );
+                    pMat->GetDimensions( nCols, nRows );
+                    if ( nCols > 0 && nRows > 0 )
+                    {
+                        if ( pTokenMatrixMap &&
+                             ( ( aMapIter = pTokenMatrixMap->find( pCur ) ) != pTokenMatrixMap->end() ) )
+                        {
+                            xNew = (*aMapIter).second;
+                        }
+                        else
+                        {
+                            ScJumpMatrix* pJumpMat = new ScJumpMatrix( nCols, nRows );
+                            for ( SCSIZE nC = 0; nC < nCols; ++nC )
+                            {
+                                for ( SCSIZE nR = 0; nR < nRows; ++nR )
+                                {
+                                    double fVal;
+                                    sal_uInt16 nErr;
+                                    bool bIsValue = pMat->IsValue(nC, nR);
+                                    if ( bIsValue )
+                                    {
+                                        fVal = pMat->GetDouble( nC, nR );
+                                        nErr = !( pMat->GetError( nC, nR ) == 0 ||
+                                                  ( bNAonly &&
+                                                    pMat->GetError( nC, nR )!= NOTAVAILABLE ) );
+                                    }
+                                    else
+                                    {
+                                        fVal = 0.0;
+                                        nErr = 1;
+                                    }
+
+                                    if ( nErr == 0 )
+                                    {   // no error, return 1st argument
+                                        pJumpMat->SetJump( nC, nR, fVal,
+                                                pJump[ nJumpCount ], pJump[ nJumpCount ] );
+                                    }
+                                    else
+                                    {
+                                        // error, return 2nd argument
+                                        pJumpMat->SetJump( nC, nR, fVal,
+                                                pJump[ 1 ], pJump[ nJumpCount ] );
+                                    }
+                                }
+                            }
+                            xNew = new ScJumpMatrixToken( pJumpMat );
+                            GetTokenMatrixMap().insert( ScTokenMatrixMap::value_type( pCur, xNew ));
+                        }
+                    }
+                    PushTempToken( xNew.get() );
+                    // set endpoint of path for main code line
+                    aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] );
+                    return;
+                }
+            }
+            break;
+            default:
+            {
+                //other stacktypes, no error, return 1st argument
+                aCode.Jump( pJump[ nJumpCount  ], pJump[ nJumpCount ] );
+                return;
+            }
+        }
+    }
+
+    // error, return 2nd argument
+    nGlobalError = 0;
+    aCode.Jump( pJump[ 1 ], pJump[ nJumpCount ] );
+}
+
+
 void ScInterpreter::ScChoseJump()
 {
     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScChoseJump" );
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 7155445f9a0d..2f3b5ac65a49 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -2712,7 +2712,7 @@ void ScInterpreter::ScExternal()
     }
     else if ( ( aUnoName = ScGlobal::GetAddInCollection()->FindFunction(aFuncName, false) ).Len()  )
     {
-        //  bLocalFirst=false in FindFunction, cFunc should be the stored 
+        //  bLocalFirst=false in FindFunction, cFunc should be the stored
         //  internal name
 
         ScUnoAddInCall aCall( *ScGlobal::GetAddInCollection(), aUnoName, nParamCount );
@@ -3815,7 +3815,8 @@ StackVar ScInterpreter::Interpret()
             // RPN code push without error
             PushWithoutError( (FormulaToken&) *pCur );
         }
-        else if (pTokenMatrixMap && !(eOp == ocIf || eOp == ocChose) &&
+        else if (pTokenMatrixMap &&
+                 !(eOp == ocIf || eOp == ocIfError || eOp == ocIfNA || eOp == ocChose) &&
                 ((aTokenMatrixMapIter = pTokenMatrixMap->find( pCur)) !=
                  pTokenMatrixMap->end()) &&
                 (*aTokenMatrixMapIter).second->GetType() != svJumpMatrix)
@@ -3836,7 +3837,7 @@ StackVar ScInterpreter::Interpret()
             nFuncFmtType = NUMBERFORMAT_NUMBER;
             nFuncFmtIndex = 0;
 
-            if ( eOp == ocIf || eOp == ocChose )
+            if ( eOp == ocIf || eOp == ocChose || eOp == ocIfError || eOp == ocIfNA )
                 nStackBase = sp;        // don't mess around with the jumps
             else
             {
@@ -3863,6 +3864,8 @@ StackVar ScInterpreter::Interpret()
                 case ocDBArea           : ScDBArea();                   break;
                 case ocColRowNameAuto   : ScColRowNameAuto();           break;
                 case ocIf               : ScIfJump();                   break;
+                case ocIfError          : ScIfError( false );           break;
+                case ocIfNA             : ScIfError( true );            break;
                 case ocChose            : ScChoseJump();                break;
                 case ocAdd              : ScAdd();                      break;
                 case ocSub              : ScSub();                      break;
@@ -4258,7 +4261,9 @@ StackVar ScInterpreter::Interpret()
     case ocIsString : \
     case ocIsValue : \
     case ocN : \
-    case ocType :
+    case ocType : \
+    case ocIfError : \
+    case ocIfNA :
 
         switch ( eOp )
         {
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index ebdbb4ad3eb0..68144ddcde24 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -55,6 +55,8 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     // created inside those functions and ConvertMatrixParameters() is not
     // called for them.
     { ocIf,              {{ Array, Reference, Reference                          }, 0 }},
+    { ocIfError,         {{ Array, Reference, Reference                          }, false }},
+    { ocIfNA,            {{ Array, Reference                                     }, false }},
     { ocChose,           {{ Array, Reference                                     }, 1 }},
     // Other specials.
     { ocOpen,            {{ Bounds                                               }, 0 }},
@@ -495,6 +497,8 @@ void ScParameterClassification::GenerateDocumentation()
                     case ocIf:
                         aToken.SetByte(3);
                     break;
+                    case ocIfError:
+                    case ocIfNA:
                     case ocChose:
                         aToken.SetByte(2);
                     break;
diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx
index 539cad0744f9..c498eda2576d 100644
--- a/sc/source/core/tool/token.cxx
+++ b/sc/source/core/tool/token.cxx
@@ -126,6 +126,8 @@ void ScRawToken::SetOpCode( OpCode e )
     switch (eOp)
     {
         case ocIf:
+        case ocIfError:
+        case ocIfNA:
             eType = svJump;
             nJump[ 0 ] = 3; // If, Else, Behind
             break;
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index 1b68b2e51815..6238fbaf0e90 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -2651,6 +2651,70 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
             Text [ en-US ] = "The result of the function if the logical test returns FALSE." ;
         };
     };
+     // -=*# Resource for function IFERROR #*=-
+    Resource SC_OPCODE_IF_ERROR
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Returns value if not an error value, else alternative." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_INFO;
+            U2S( HID_FUNC_IFERROR );
+            2;  0;  1;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "value" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The value to be calculated." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "else value" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The alternative to be returned, should value be an error value." ;
+        };
+    };
+     // -=*# Resource for function IFNA #*=-
+    Resource SC_OPCODE_IF_NA
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Returns value if not an NA, else alternative." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_INFO;
+            U2S( HID_FUNC_IFNA );
+            2;  0;  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 calculated." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "else value" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The alternative to be returned, should value be an NA." ;
+      };
+    };
      // -=*# Resource for function ODER #*=-
     Resource SC_OPCODE_OR
     {
diff --git a/sc/util/hidother.src b/sc/util/hidother.src
index d33922911cc3..718dce297452 100644
--- a/sc/util/hidother.src
+++ b/sc/util/hidother.src
@@ -149,6 +149,8 @@ hidspecial HID_FUNC_FALSCH       { HelpID = HID_FUNC_FALSCH; };
 hidspecial HID_FUNC_NICHT        { HelpID = HID_FUNC_NICHT; };
 hidspecial HID_FUNC_WAHR         { HelpID = HID_FUNC_WAHR; };
 hidspecial HID_FUNC_WENN         { HelpID = HID_FUNC_WENN; };
+hidspecial HID_FUNC_IFERROR      { HelpID = HID_FUNC_IFERROR; };
+hidspecial HID_FUNC_IFNA         { HelpID = HID_FUNC_IFNA; };
 hidspecial HID_FUNC_ODER         { HelpID = HID_FUNC_ODER; };
 hidspecial HID_FUNC_UND      { HelpID = HID_FUNC_UND; };
 hidspecial HID_FUNC_XOR      { HelpID = HID_FUNC_XOR; };