fdo#69552 [part 1] make calc functions CEILING comply with ODF1.2

Also, add support for CEILING.MATH and fix small
deficiencies with CEILING functions, as most are interwoven.

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

7 files changed, 171 insertions, 50 deletions

diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 19b779d2696a..047552480e56 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -624,10 +624,12 @@ void ScAbs();
 void ScInt();
 void ScEven();
 void ScOdd();
-void ScCeil();
+void ScCeil( bool bODFF );
 void ScCeil_MS();
+void ScCeil_Precise();
 void ScFloor();
 void ScFloor_MS();
+void ScFloor_Precise();
 void RoundNumber( rtl_math_RoundingMode eMode );
 void ScRound();
 void ScRoundUp();
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 05e9f9c15705..f2a54d46cfa1 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -2507,12 +2507,21 @@ bool ScCompiler::IsOpCode( const OUString& rName, bool bInArray )
     if (bFound)
     {
         OpCode eOp = iLook->second;
-        if (bInArray)
+        if ( eOp != ocOpen && eOp != ocClose )
         {
-            if (rName.equals(mxSymbols->getSymbol(ocArrayColSep)))
-                eOp = ocArrayColSep;
-            else if (rName.equals(mxSymbols->getSymbol(ocArrayRowSep)))
-                eOp = ocArrayRowSep;
+            if (bInArray)
+            {
+                if (rName.equals(mxSymbols->getSymbol(ocArrayColSep)))
+                    eOp = ocArrayColSep;
+                else if (rName.equals(mxSymbols->getSymbol(ocArrayRowSep)))
+                    eOp = ocArrayRowSep;
+            }
+            else if (mxSymbols->isOOXML())
+            {
+                // OOXML names that need to treated differently on import.
+                if ( rName.equalsIgnoreAsciiCaseAscii( "_XLFN.CEILING.MATH" ) )
+                    eOp = ocCeil_Math;
+            }
         }
         maRawToken.SetOpCode(eOp);
     }
@@ -4082,12 +4091,10 @@ ScTokenArray* ScCompiler::CompileString( const OUString& rFormula )
         }
         if (bOOXML)
         {
-            // Append a parameter for CEILING, FLOOR and WEEKNUM, all 1.0
+            // Append a parameter for FLOOR and WEEKNUM, all 1.0
             // Function is already closed, parameter count is nSep+1
             size_t nFunc = nFunction + 1;
             if (eOp == ocClose && (
-                    (pFunctionStack[ nFunc ].eOp == ocCeil &&   // 3rd Excel mode
-                     pFunctionStack[ nFunc ].nSep == 1) ||
                     (pFunctionStack[ nFunc ].eOp == ocFloor &&  // 3rd Excel mode
                      pFunctionStack[ nFunc ].nSep == 1) ||
                     (pFunctionStack[ nFunc ].eOp == ocWeek &&   // 2nd week start
diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index 8dee4f2b7b04..e3312811698b 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -794,31 +794,74 @@ void ScInterpreter::ScRoundUp()
     RoundNumber( rtl_math_RoundingMode_Up );
 }
 
-void ScInterpreter::ScCeil()
+/** fdo69552 ODFF1.2 function CEILING and Excel function CEILING.MATH
+    In essence, the difference between the two is that ODFF-CEILING needs to
+    have arguments value and significance of the same sign and with
+    CEILING.MATH the sign of argument significance is irrevelevant.
+    This is why ODFF-CEILING is exported to Excel as CEILING.MATH and
+    CEILING.MATH is imported in Calc as CEILING.MATH
+ */
+void ScInterpreter::ScCeil( bool bODFF )
 {
     sal_uInt8 nParamCount = GetByte();
-    if ( MustHaveParamCount( nParamCount, 2, 3 ) )
+    if ( MustHaveParamCount( nParamCount, 1, 3 ) )
     {
-        bool bAbs = nParamCount == 3 && GetBool();
-        double fDec = GetDouble();
-        double fVal = GetDouble();
-        if ( fDec == 0.0 )
-            PushInt(0);
-        else if (fVal*fDec < 0.0)
-            PushIllegalArgument();
+        bool bAbs = ( nParamCount == 3 ? GetBool() : false );
+        double fDec, fVal;
+        if ( nParamCount == 1 )
+        {
+            fVal = GetDouble();
+            fDec = ( fVal < 0 ? -1 : 1 );
+        }
         else
         {
-            if ( !bAbs && fVal < 0.0 )
-                PushDouble(::rtl::math::approxFloor(fVal/fDec) * fDec);
+            bool bArgumentMissing = IsMissing();
+            fDec = GetDouble();
+            fVal = GetDouble();
+            if ( bArgumentMissing )
+                fDec = ( fVal < 0 ? -1 : 1 );
+        }
+        if ( fVal == 0 || fDec == 0.0 )
+            PushInt( 0 );
+        else
+        {
+            if ( bODFF && fVal * fDec < 0 )
+                PushIllegalArgument();
             else
-                PushDouble(::rtl::math::approxCeil(fVal/fDec) * fDec);
+            {
+                if ( fVal * fDec < 0.0 )
+                    fDec = -fDec;
+
+                if ( !bAbs && fVal < 0.0 )
+                    PushDouble(::rtl::math::approxFloor( fVal / fDec ) * fDec );
+                else
+                    PushDouble(::rtl::math::approxCeil( fVal / fDec ) * fDec );
+            }
         }
     }
 }
 
+// fdo69552 Excel function CEILING
 void ScInterpreter::ScCeil_MS()
 {
     sal_uInt8 nParamCount = GetByte();
+    if ( MustHaveParamCount( nParamCount, 2 ) )
+    {
+        double fDec = GetDouble();
+        double fVal = GetDouble();
+        if ( fVal == 0 || fDec == 0.0 )
+            PushInt(0);
+        else if ( fVal > 0.0 && fDec < 0.0 )
+            PushIllegalArgument();
+        else
+            PushDouble(::rtl::math::approxCeil(fVal/fDec) * fDec);
+    }
+}
+
+// fdo69552 Excel functions CEILING.PRECISE and ISO.CEILING
+void ScInterpreter::ScCeil_Precise()
+{
+    sal_uInt8 nParamCount = GetByte();
     if ( MustHaveParamCount( nParamCount, 1, 2 ) )
     {
         double fDec, fVal;
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 386b306d6738..e1de1a6d648c 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -3993,9 +3993,11 @@ StackVar ScInterpreter::Interpret()
                 case ocRoundUp          : ScRoundUp();                  break;
                 case ocTrunc            :
                 case ocRoundDown        : ScRoundDown();                break;
-                case ocCeil             : ScCeil();                     break;
-                case ocCeil_MS          :
-                case ocCeil_ISO         : ScCeil_MS();                  break;
+                case ocCeil             : ScCeil( true );               break;
+                case ocCeil_MS          : ScCeil_MS();                  break;
+                case ocCeil_Precise     :
+                case ocCeil_ISO         : ScCeil_Precise();             break;
+                case ocCeil_Math        : ScCeil( false );              break;
                 case ocFloor            : ScFloor();                    break;
                 case ocFloor_MS         : ScFloor_MS();                 break;
                 case ocSumProduct       : ScSumProduct();               break;
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 568412130bb7..c3ad1b140177 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -278,7 +278,7 @@ static const XclFunctionInfo saFuncTable_4[] =
     { ocFloor,              285,    2,  2,  V, { VR, VR, C }, 0, 0 },
     { ocGammaDist,          286,    4,  4,  V, { VR }, 0, 0 },
     { ocGammaInv,           287,    3,  3,  V, { VR }, 0, 0 },
-    { ocCeil,               288,    2,  2,  V, { VR, VR, C }, 0, 0 },
+    { ocCeil_MS,            288,    2,  2,  V, { VR }, 0, 0 },
     { ocHypGeomDist,        289,    4,  4,  V, { VR }, 0, 0 },
     { ocLogNormDist,        290,    3,  3,  V, { VR }, 0, 0 },
     { ocLogInv,             291,    3,  3,  V, { VR }, 0, 0 },
@@ -487,7 +487,7 @@ static const XclFunctionInfo saFuncTable_2010[] =
     EXC_FUNCENTRY_V_RX( ocModalValue_Multi, 1, MX,  0,  "MODE.MULT" ),
     EXC_FUNCENTRY_V_VR( ocNegBinomDist_MS,  4,  4,  0,  "NEGBINOM.DIST" ),
     EXC_FUNCENTRY_V_VR( ocZTest_MS,         2,  3,  0,  "Z.TEST" ),
-    EXC_FUNCENTRY_V_VR( ocCeil_MS,          2,  2,  0,  "CEILING.PRECISE" ),
+    EXC_FUNCENTRY_V_VR( ocCeil_Precise,     2,  2,  0,  "CEILING.PRECISE" ),
     EXC_FUNCENTRY_V_VR( ocFloor_MS,         2,  2,  0,  "FLOOR.PRECISE" ),
     EXC_FUNCENTRY_V_VR( ocErf_MS,           1,  1,  0,  "ERF.PRECISE" ),
     EXC_FUNCENTRY_V_VR( ocErfc_MS,          1,  1,  0,  "ERFC.PRECISE" ),
@@ -523,10 +523,7 @@ static const XclFunctionInfo saFuncTable_2013[] =
     EXC_FUNCENTRY_V_VR(         ocBitOr,         2,  2,  0,  "BITOR" ),
     EXC_FUNCENTRY_V_VR(         ocBitRshift,     2,  2,  0,  "BITRSHIFT" ),
     EXC_FUNCENTRY_V_VR(         ocBitXor,        2,  2,  0,  "BITXOR" ),
-    /* FIXME: CEILING.MATH is our/ODFF CEILING, but we have special handling
-     * for the weird Excel CEILING behavior, check that and unify or diversify.
-     * */
-    EXC_FUNCENTRY_V_VR(         ocNoName,        1,  3,  0,  "CEILING.MATH" ),
+    EXC_FUNCENTRY_V_VR(         ocCeil_Math,     1,  3,  0,  "CEILING.MATH" ),
     EXC_FUNCENTRY_V_VR(         ocCombinA,       2,  2,  0,  "COMBINA" ),
     EXC_FUNCENTRY_V_VR_IMPORT(  ocCot,           1,  1,  0,  "COT" ),
     EXC_FUNCENTRY_V_VR_IMPORT(  ocCotHyp,        1,  1,  0,  "COTH" ),
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 38f0ef234d3d..408d5e170457 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -507,7 +507,7 @@ static const FunctionData saFuncTableBiff4[] =
     { "FLOOR",                  "FLOOR",                285,    285,    2,  2,  V, { VR, VR, C }, 0 },
     { "GAMMADIST",              "GAMMADIST",            286,    286,    4,  4,  V, { VR }, 0 },
     { "GAMMAINV",               "GAMMAINV",             287,    287,    3,  3,  V, { VR }, 0 },
-    { "CEILING",                "CEILING",              288,    288,    2,  2,  V, { VR, VR, C }, 0 },
+    { "COM.MICROSOFT.CEILING",  "CEILING",              288,    288,    2,  2,  V, { VR, VR, C }, 0 },
     { "HYPGEOMDIST",            "HYPGEOMDIST",          289,    289,    4,  4,  V, { VR }, 0 },
     { "LOGNORMDIST",            "LOGNORMDIST",          290,    290,    3,  3,  V, { VR }, 0 },
     { "LOGINV",                 "LOGINV",               291,    291,    3,  3,  V, { VR }, 0 },
@@ -846,10 +846,8 @@ static const FunctionData saFuncTable2013[] =
     { "BITOR",                  "BITOR",                NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "BITRSHIFT",              "BITRSHIFT",            NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "BITXOR",                 "BITXOR",               NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
-    /* FIXME: CEILING.MATH is our/ODFF CEILING, but we have special handling
-     * for the weird Excel CEILING behavior, check that and unify or diversify.
-     * */
-    { 0/*"CEILING"*/,           "CEILING.MATH",         NOID,   NOID,   1,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.CEILING.MATH", "CEILING.MATH",     NOID,   NOID,   1,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "CEILING",                "CEILING.MATH",         NOID,   NOID,   1,  3,  V, { VR }, FUNCFLAG_EXPORTONLY },
     { "COMBINA",                "COMBINA",              NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "COT",                    "COT",                  NOID,   NOID,   1,  1,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "COTH",                   "COTH",                 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 eed70bce13ff..abeba90be42a 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -4459,8 +4459,8 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
             Text [ en-US ] = "The number to be rounded up." ;
         };
     };
-     // -=*# Resource for function CEILING #*=-
-    Resource SC_OPCODE_CEIL
+     // -=*# Resource for function CEILING.XCL #*=-
+    Resource SC_OPCODE_CEIL_MS
     {
         String 1 // Description
         {
@@ -4470,8 +4470,8 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
         {
             0;
             ID_FUNCTION_GRP_MATH;
-            U2S( HID_FUNC_OBERGRENZE );
-            3;  0;  0;  1;
+            U2S( HID_FUNC_CEIL_MS );
+            2;  0;  0;
             0;
         };
         String 2 // Name of Parameter 1
@@ -4490,17 +4490,9 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
         {
             Text [ en-US ] = "The number to whose multiple the value is rounded." ;
         };
-        String 6 // Name of Parameter 3
-        {
-            Text [ en-US ] = "Mode" ;
-        };
-        String 7 // Description of Parameter 3
-        {
-            Text [ en-US ] = "If given and not equal to zero then rounded up according to amount when a negative number and significance." ;
-        };
     };
      // -=*# Resource for function CEILING.PRECISE #*=-
-    Resource SC_OPCODE_CEIL_MS
+    Resource SC_OPCODE_CEIL_PRECISE
     {
         String 1 // Description
         {
@@ -4510,7 +4502,7 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
         {
             0;
             ID_FUNCTION_GRP_MATH;
-            U2S( HID_FUNC_CEIL_MS );
+            U2S( HID_FUNC_CEIL_PRECISE );
             2;  0;  1;
             0;
         };
@@ -4563,7 +4555,87 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
             Text [ en-US ] = "The number to whose multiple the value is rounded." ;
         };
     };
-     // -=*# Resource for function FLOOR #*=-
+     // -=*# Resource for function CEILING #*=-
+    Resource SC_OPCODE_CEIL
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Rounds a number up to the nearest multiple of significance." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_MATH;
+            U2S( HID_FUNC_OBERGRENZE );
+            3;  0;  1;  1;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "Number" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The number to be rounded up." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "Significance" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "If given the number to whose multiple the value is rounded, else -1 or 1 depending on sign of Number." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "Mode" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "If given and not equal to zero then rounded up according to amount when a negative number and significance." ;
+        };
+    };
+     // -=*# Resource for function CEILING.MATH #*=-
+    Resource SC_OPCODE_CEIL_MATH
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Rounds a number up to the nearest multiple of significance." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_MATH;
+            U2S( HID_FUNC_CEIL_MATH );
+            3;  0;  1;  1;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "Number" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The number to be rounded up." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "Significance" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "If given the number to whose multiple the value is rounded, else 1." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "Mode" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "For negative numbers; if given and not equal to zero then rounds away from zero, else rounds towards zero." ;
+        };
+    };
+     // -=*# Resource for function UNTERGRENZE #*=-
     Resource SC_OPCODE_FLOOR
     {
         String 1 // Description