fdo#57180 add calc function NUMBERVALUE as defined in ODFF1.2

Conflicts:

sc/source/filter/excel/xlformula.cxx
sc/source/filter/oox/formulabase.cxx

Change-Id: I6ee01764ae9fc27854fd3bd8a630b9d3560192e5
Reviewed-on: https://gerrit.libreoffice.org/1477
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>

8 files changed, 171 insertions, 6 deletions

diff --git a/formula/inc/formula/opcode.hxx b/formula/inc/formula/opcode.hxx
index e37395ea6e70..007bcd92adf0 100644
--- a/formula/inc/formula/opcode.hxx
+++ b/formula/inc/formula/opcode.hxx
@@ -301,6 +301,7 @@ enum OpCodeEnum
         ocSubstitute        = SC_OPCODE_SUBSTITUTE,
         ocRept              = SC_OPCODE_REPT,
         ocConcat            = SC_OPCODE_CONCAT,
+        ocNumberValue       = SC_OPCODE_NUMBERVALUE,
     // Matrix functions
         ocMatValue          = SC_OPCODE_MAT_VALUE,
         ocMatDet            = SC_OPCODE_MAT_DET,
@@ -395,7 +396,6 @@ enum OpCodeEnum
         ocHyperLink         = SC_OPCODE_HYPERLINK,
         ocGetPivotData      = SC_OPCODE_GET_PIVOT_DATA,
         ocEuroConvert       = SC_OPCODE_EUROCONVERT,
-        ocNumberValue       = SC_OPCODE_NUMBERVALUE,
     // internal stuff
         ocInternalBegin     = SC_OPCODE_INTERNAL_BEGIN,
         ocTTT               = SC_OPCODE_TTT,
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 5558ead68b1a..09ada380d786 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -750,6 +750,60 @@ void testFuncIFERROR(ScDocument* pDoc)
     }
 }
 
+void testFuncNUMBERVALUE( ScDocument* pDoc )
+{
+    // NUMBERVALUE fdo#57180
+
+    // Empty A1:A39 first.
+    clearRange(pDoc, ScRange(0, 0, 0, 0, 40, 0));
+
+    // Raw data (rows 1 through 6)
+    const char* aData[] = {
+        "1ag9a9b9",
+        "1ag34 5g g6  78b9%%",
+        "1 234d56E-2",
+        "d4",
+        "54.4",
+        "1a2b3e1%"
+    };
+
+    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, nRows - 1, 0), "data range for NUMBERVALUE");
+
+    // formulas and results
+    struct {
+        const char* pFormula; const char* pResult;
+    } aChecks[] = {
+        { "=NUMBERVALUE(A1;\"b\";\"ag\")",  "199.9" },
+        { "=NUMBERVALUE(A2;\"b\";\"ag\")",  "134.56789" },
+        { "=NUMBERVALUE(A2;\"b\";\"g\")",   "Err:502" },
+        { "=NUMBERVALUE(A3;\"d\")",         "12.3456" },
+        { "=NUMBERVALUE(A4;\"d\";\"foo\")", "0.4" },
+        { "=NUMBERVALUE(A4;)",              "Err:502" },
+        { "=NUMBERVALUE(A5;)",              "Err:502" },
+        { "=NUMBERVALUE(A6;\"b\";\"a\")",   "1.23" }
+    };
+
+    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)
+    {
+        SCROW nRow = 20 + i;
+        rtl::OUString aResult = pDoc->GetString(0, nRow, 0);
+        CPPUNIT_ASSERT_EQUAL_MESSAGE(
+            aChecks[i].pFormula, OUString::createFromAscii( aChecks[i].pResult), aResult);
+    }
+}
+
 void testFuncVLOOKUP(ScDocument* pDoc)
 {
     // VLOOKUP
@@ -1141,6 +1195,7 @@ void Test::testCellFunctions()
     testFuncN(m_pDoc);
     testFuncCOUNTIF(m_pDoc);
     testFuncIFERROR(m_pDoc);
+    testFuncNUMBERVALUE(m_pDoc);
     testFuncVLOOKUP(m_pDoc);
     testFuncMATCH(m_pDoc);
     testFuncCELL(m_pDoc);
@@ -4571,6 +4626,7 @@ void Test::testFunctionLists()
         "LEN",
         "LOWER",
         "MID",
+        "NUMBERVALUE",
         "PROPER",
         "REPLACE",
         "REPT",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 267e65947d48..cd44780dbc62 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -460,6 +460,7 @@ void ScLower();
 void ScLen();
 void ScT();
 void ScValue();
+void ScNumberValue();
 void ScClean();
 void ScChar();
 void ScJis();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 74294675fef2..53cb552ce2d8 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -3378,6 +3378,104 @@ void ScInterpreter::ScValue()
 }
 
 
+// fdo#57180
+void ScInterpreter::ScNumberValue()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNumberValue" );
+
+    sal_uInt8 nParamCount = GetByte();
+    if ( !MustHaveParamCount( nParamCount, 1, 3 ) )
+        return;
+
+    OUString aInputString;
+    OUString aDecimalSeparator, aGroupSeparator;
+    sal_Unicode cDecimalSeparator = 0;
+
+    if ( nParamCount == 3 )
+        aGroupSeparator = GetString();
+
+    if ( nParamCount >= 2 )
+    {
+        aDecimalSeparator = GetString();
+        if ( aDecimalSeparator.getLength() == 1  )
+            cDecimalSeparator = aDecimalSeparator[ 0 ];
+        else
+        {
+            PushIllegalArgument();  //if given, separator length must be 1
+            return;
+        }
+    }
+
+    if ( cDecimalSeparator && aGroupSeparator.indexOf( cDecimalSeparator ) != -1 )
+    {
+        PushIllegalArgument(); //decimal separator cannot appear in group separator
+        return;
+    }
+
+    switch (GetStackType())
+    {
+        case svDouble:
+        return; // leave on stack
+        default:
+        aInputString = GetString();
+    }
+    if ( nGlobalError )
+    {
+        PushError( nGlobalError );
+        return;
+    }
+    if ( aInputString.isEmpty() )
+    {
+        if ( GetGlobalConfig().mbEmptyStringAsZero )
+            PushDouble( 0.0 );
+        else
+            PushNoValue();
+        return;
+    }
+
+    sal_Int32 nDecSep = aInputString.indexOf( cDecimalSeparator );
+    if ( nDecSep != 0 )
+    {
+        OUString aTemporary( nDecSep >= 0 ? aInputString.copy( 0, nDecSep ) : aInputString );
+        sal_Int32 nIndex = 0;
+        do
+        {
+            sal_uInt32 nChar = aGroupSeparator.iterateCodePoints( &nIndex );
+            aTemporary = aTemporary.replaceAll( OUString( &nChar, 1 ), "" );
+        } while ( nIndex < aGroupSeparator.getLength() );
+        if ( nDecSep >= 0 )
+            aInputString = aTemporary + aInputString.copy( nDecSep );
+        else
+            aInputString = aTemporary;
+    }
+
+    for ( sal_Int32 i = aInputString.getLength(); --i >= 0; )
+    {
+        sal_Unicode c = aInputString[ i ];
+        if ( c == 0x0020 || c == 0x0009 || c == 0x000A || c == 0x000D )
+            aInputString = aInputString.replaceAt( i, 1, "" ); // remove spaces etc.
+    }
+    sal_Int32 nPercentCount = 0;
+    for ( sal_Int32 i = aInputString.getLength() - 1; i >= 0 && aInputString[ i ] == 0x0025; i-- )
+    {
+        aInputString = aInputString.replaceAt( i, 1, "" );  // remove and count trailing '%'
+        nPercentCount++;
+    }
+
+    rtl_math_ConversionStatus eStatus;
+    sal_Int32 nParseEnd;
+    double fVal = ::rtl::math::stringToDouble( aInputString, cDecimalSeparator, 0, &eStatus, &nParseEnd );
+    if ( eStatus == rtl_math_ConversionStatus_Ok && nParseEnd == aInputString.getLength() )
+    {
+        if (nPercentCount)
+            fVal *= pow( 10.0, -(nPercentCount * 2));    // process '%' from input string
+        PushDouble(fVal);
+        return;
+    }
+    PushIllegalArgument();
+}
+
+
 //2do: this should be a proper unicode string method
 static inline bool lcl_ScInterpreter_IsPrintable( sal_Unicode c )
 {
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 2f3b5ac65a49..bc7aa6fbfe14 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -3969,6 +3969,7 @@ StackVar ScInterpreter::Interpret()
                 case ocT                : ScT();                        break;
                 case ocClean            : ScClean();                    break;
                 case ocValue            : ScValue();                    break;
+                case ocNumberValue      : ScNumberValue();              break;
                 case ocChar             : ScChar();                     break;
                 case ocArcTan2          : ScArcTan2();                  break;
                 case ocMod              : ScMod();                      break;
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 3b05290bb456..623b10548b8a 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -801,7 +801,7 @@ static const FunctionData saFuncTable2013[] =
      * import. */
     { 0/*"ISOWEEKNUM"*/,        "ISOWEEKNUM",           NOID,   NOID,   1,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "MUNIT",                  "MUNIT",                NOID,   NOID,   1,  1,  A, { VR }, FUNCFLAG_MACROCALL_NEW },
-    { 0/*"NUMBERVALUE"*/,       "NUMBERVALUE",          NOID,   NOID,   1,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "NUMBERVALUE",            "NUMBERVALUE",          NOID,   NOID,   1,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "PDURATION",              "PDURATION",            NOID,   NOID,   3,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "PERMUTATIONA",           "PERMUTATIONA",         NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "PHI",                    "PHI",                  NOID,   NOID,   1,  1,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index d30c7dd47442..5f4c54024634 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -9396,10 +9396,10 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
         };
         ExtraData =
         {
-            1;  // TODO: implementation and unsuppress
+            0;
             ID_FUNCTION_GRP_TEXT;
             U2S( HID_FUNC_NUMBERVALUE );
-            2;  0;  0;
+            3;  0; 1; 1;
             0;
         };
         String 2 // Name of Parameter 1
@@ -9412,11 +9412,19 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
         };
         String 4 // Name of Parameter 2
         {
-            Text [ en-US ] = "decimal_point" ;
+            Text [ en-US ] = "decimal_separator" ;
         };
         String 5 // Description of Parameter 2
         {
-            Text [ en-US ] = "Defines the character used as the decimal point." ;
+            Text [ en-US ] = "Defines the character used as the decimal separator." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "group_separator" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "Defines the character(s) used as the group separator." ;
         };
     };
 
diff --git a/sc/util/hidother.src b/sc/util/hidother.src
index 718dce297452..be1670729e4c 100644
--- a/sc/util/hidother.src
+++ b/sc/util/hidother.src
@@ -340,6 +340,7 @@ hidspecial HID_FUNC_GROSS2       { HelpID = HID_FUNC_GROSS2; };
 hidspecial HID_FUNC_GROSS        { HelpID = HID_FUNC_GROSS; };
 hidspecial HID_FUNC_KLEIN        { HelpID = HID_FUNC_KLEIN; };
 hidspecial HID_FUNC_WERT         { HelpID = HID_FUNC_WERT; };
+hidspecial HID_FUNC_NUMBERVALUE  { HelpID = HID_FUNC_NUMBERVALUE; };
 hidspecial HID_FUNC_TEXT         { HelpID = HID_FUNC_TEXT; };
 hidspecial HID_FUNC_T        { HelpID = HID_FUNC_T; };
 hidspecial HID_FUNC_ERSETZEN         { HelpID = HID_FUNC_ERSETZEN; };