From 06b8f15c8dd23504b8e087451ee5c82eb74edd81 Mon Sep 17 00:00:00 2001 From: Winfried Donkers Date: Mon, 28 Jan 2013 16:19:00 +0100 Subject: 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 Tested-by: Eike Rathke --- formula/inc/formula/opcode.hxx | 2 +- sc/qa/unit/ucalc.cxx | 56 +++++++++++++++++++++ sc/source/core/inc/interpre.hxx | 1 + sc/source/core/tool/interpr1.cxx | 98 ++++++++++++++++++++++++++++++++++++ sc/source/core/tool/interpr4.cxx | 1 + sc/source/filter/oox/formulabase.cxx | 2 +- sc/source/ui/src/scfuncs.src | 16 ++++-- sc/util/hidother.src | 1 + 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; }; -- cgit