fdo#73148 (partial patch) Add Excel 2010 function AGGREGATE

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

13 files changed, 172 insertions, 1 deletions

diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src
index 90544a41f2b9..1aecc1ae46a2 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -321,6 +321,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
     String SC_OPCODE_MODAL_VALUE_MULTI { Text = "COM.MICROSOFT.MODE.MULT" ; };
     String SC_OPCODE_Z_TEST { Text = "ZTEST" ; };
     String SC_OPCODE_Z_TEST_MS { Text = "COM.MICROSOFT.Z.TEST" ; };
+    String SC_OPCODE_AGGREGATE { Text = "COM.MICROSOFT.AGGREGATE" ; };
     String SC_OPCODE_T_TEST { Text = "TTEST" ; };
     String SC_OPCODE_T_TEST_MS { Text = "COM.MICROSOFT.T.TEST" ; };
     String SC_OPCODE_RANK { Text = "RANK" ; };
@@ -726,6 +727,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML
     String SC_OPCODE_MODAL_VALUE_MULTI { Text = "_xlfn.MODE.MULT" ; };
     String SC_OPCODE_Z_TEST { Text = "ZTEST" ; };
     String SC_OPCODE_Z_TEST_MS { Text = "_xlfn.Z.TEST" ; };
+    String SC_OPCODE_AGGREGATE { Text = "_xlfn.AGGREGATE" ; };
     String SC_OPCODE_T_TEST { Text = "TTEST" ; };
     String SC_OPCODE_T_TEST_MS { Text = "_xlfn.T.TEST" ; };
     String SC_OPCODE_RANK { Text = "RANK" ; };
@@ -1133,6 +1135,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH
     String SC_OPCODE_MODAL_VALUE_MULTI { Text = "MODE.MULT" ; };
     String SC_OPCODE_Z_TEST { Text = "ZTEST" ; };
     String SC_OPCODE_Z_TEST_MS { Text = "Z.TEST" ; };
+    String SC_OPCODE_AGGREGATE { Text = "AGGREGATE" ; };
     String SC_OPCODE_T_TEST { Text = "TTEST" ; };
     String SC_OPCODE_T_TEST_MS { Text = "T.TEST" ; };
     String SC_OPCODE_RANK { Text = "RANK" ; };
@@ -2362,6 +2365,10 @@ Resource RID_STRLIST_FUNCTION_NAMES
     {
         Text [ en-US ] = "Z.TEST" ;
     };
+    String SC_OPCODE_AGGREGATE
+    {
+        Text = "AGGREGATE" ;
+    };
     String SC_OPCODE_T_TEST
     {
         Text [ en-US ] = "TTEST" ;
diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc
index 96ea885d4b8f..2ce723011e5b 100644
--- a/include/formula/compiler.hrc
+++ b/include/formula/compiler.hrc
@@ -467,8 +467,9 @@
 #define SC_OPCODE_FLOOR_MS          467
 #define SC_OPCODE_NETWORKDAYS_MS    468
 #define SC_OPCODE_WORKDAY_MS        469
+#define SC_OPCODE_AGGREGATE         470
 
-#define SC_OPCODE_STOP_2_PAR        470     /* last function with two or more parameters' OpCode + 1 */
+#define SC_OPCODE_STOP_2_PAR        471     /* last function with two or more parameters' OpCode + 1 */
 #define SC_OPCODE_STOP_FUNCTION     SC_OPCODE_STOP_2_PAR            /* last function's OpCode + 1 */
 #define SC_OPCODE_LAST_OPCODE_ID    (SC_OPCODE_STOP_FUNCTION - 1)   /* last OpCode */
 
diff --git a/include/formula/opcode.hxx b/include/formula/opcode.hxx
index f5afa19abca3..0e9fb708adf7 100644
--- a/include/formula/opcode.hxx
+++ b/include/formula/opcode.hxx
@@ -366,6 +366,7 @@ enum OpCodeEnum
         ocModalValue_Multi  = SC_OPCODE_MODAL_VALUE_MULTI,
         ocZTest             = SC_OPCODE_Z_TEST,
         ocZTest_MS          = SC_OPCODE_Z_TEST_MS,
+        ocAggregate         = SC_OPCODE_AGGREGATE,
         ocTTest             = SC_OPCODE_T_TEST,
         ocTTest_MS          = SC_OPCODE_T_TEST_MS,
         ocRank              = SC_OPCODE_RANK,
diff --git a/sc/inc/global.hxx b/sc/inc/global.hxx
index 3944f74ed4dd..0614bce65c65 100644
--- a/sc/inc/global.hxx
+++ b/sc/inc/global.hxx
@@ -234,6 +234,10 @@ const sal_uInt16 IDF_AUTOFILL   = IDF_ALL & ~(IDF_NOTE | IDF_OBJECTS);
 #define SC_SCENARIO_VALUE       32
 #define SC_SCENARIO_PROTECT     64
 
+#define AGGR_IGN_NESTED_ST_AG  0x04
+#define AGGR_IGN_ERR_VAL       0x02
+#define AGGR_IGN_HID_ROW       0x01
+
 /** Default cell clone flags: do not start listening, do not adjust 3D refs to
     old position, clone note captions of cell notes. */
 const int SC_CLONECELL_DEFAULT          = 0x0000;
@@ -726,6 +730,18 @@ enum ScSubTotalFunc
         SUBTOTAL_FUNC_SELECTION_COUNT = 12
     };
 
+enum ScAggregateFunc
+    {
+        AGGREGATE_FUNC_MEDIAN  = 12,
+        AGGREGATE_FUNC_MODSNGL = 13,
+        AGGREGATE_FUNC_LARGE   = 14,
+        AGGREGATE_FUNC_SMALL   = 15,
+        AGGREGATE_FUNC_PERCINC = 16,
+        AGGREGATE_FUNC_QRTINC  = 17,
+        AGGREGATE_FUNC_PERCEXC = 18,
+        AGGREGATE_FUNC_QRTEXC  = 19
+    };
+
 class ScArea;
 
 struct ScConsolidateParam
diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index 2f73f3fbd12c..b5303e221803 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -706,5 +706,6 @@
 #define HID_FUNC_WORKDAY_MS                                     "SC_HID_FUNC_WORKDAY_MS"
 #define HID_FUNC_ERF_MS                                         "SC_HID_FUNC_EFR_MS"
 #define HID_FUNC_ERFC_MS                                        "SC_HID_FUNC_ERFC_MS"
+#define HID_FUNC_AGGREGATE                                      "SC_HID_FUNC_AGGREGATE"
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 6b5585a8785f..48e25b6f79f9 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -2493,6 +2493,7 @@ void Test::testFunctionLists()
         "ACOSH",
         "ACOT",
         "ACOTH",
+//        "AGGREGATE",  // fdo73148 function not yet visble in UI
         "ASIN",
         "ASINH",
         "ATAN",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 40b5206cb3e8..660ecf3c3dd0 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -797,6 +797,7 @@ void QuickSort(::std::vector<double>& rSortArray, ::std::vector<long>* pIndexOrd
 void ScModalValue();
 void ScModalValue_Multi();
 void ScAveDev();
+void ScAggregate();
 void ScDevSq();
 void ScZTest();
 void ScTTest();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 9412c4269d5c..4b6140a1d457 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -6617,6 +6617,97 @@ void ScInterpreter::ScSubTotal()
     }
 }
 
+void ScInterpreter::ScAggregate()
+{
+    sal_uInt8 nParamCount = GetByte();
+    if ( MustHaveParamCountMin( nParamCount, 3 ) )
+    {
+        // fish the 1st parameter from the stack and push it on top.
+        const FormulaToken* p = pStack[ sp - nParamCount ];
+        PushTempToken( *p );
+        int nFunc = ( int ) ::rtl::math::approxFloor( GetDouble() );
+        // fish the 2nd parameter from the stack and push it on top.
+        const FormulaToken* p2 = pStack[ sp - ( nParamCount - 1 ) ];
+        PushTempToken( *p2 );
+        int nOption = ( int ) ::rtl::math::approxFloor( GetDouble() );
+
+        if ( nFunc < 1 || nFunc > 19 )
+            PushIllegalArgument();
+        else
+        {
+            sal_uInt16 nAggrFlags = 0x00;
+            switch ( nOption)
+            {
+                case 0 : // ignore nested SUBTOTAL and AGGREGATE functions
+                    nAggrFlags = AGGR_IGN_NESTED_ST_AG;
+                    break;
+                case 1 : // ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
+                    nAggrFlags = AGGR_IGN_HID_ROW | AGGR_IGN_NESTED_ST_AG;
+                    break;
+                case 2 : // ignore error values, nested SUBTOTAL and AGGREGATE functions
+                    nAggrFlags = AGGR_IGN_ERR_VAL | AGGR_IGN_NESTED_ST_AG;
+                    break;
+                case 3 : // ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
+                    nAggrFlags = AGGR_IGN_HID_ROW | AGGR_IGN_ERR_VAL | AGGR_IGN_NESTED_ST_AG;
+                    break;
+                case 4 : // ignore nothing
+                    break;
+                case 5 : // ignore hidden rows
+                    nAggrFlags = AGGR_IGN_HID_ROW ;
+                    break;
+                case 6 : // ignore error values
+                    nAggrFlags = AGGR_IGN_ERR_VAL ;
+                    break;
+                case 7 : // igniore hidden rows and error values
+                    nAggrFlags = AGGR_IGN_HID_ROW | AGGR_IGN_ERR_VAL ;
+                    break;
+                default :
+                    PushIllegalArgument();
+                    break;
+            }
+            // TODO: implement filter options
+            if ( nAggrFlags != 0x00 )
+            {
+                PushError( errUnknownVariable );
+                return;
+            }
+
+            cPar = nParamCount - 2;
+            glSubTotal = true;
+            switch ( nFunc )
+            {
+                case SUBTOTAL_FUNC_AVE      : ScAverage(); break;
+                case SUBTOTAL_FUNC_CNT      : ScCount();   break;
+                case SUBTOTAL_FUNC_CNT2     : ScCount2();  break;
+                case SUBTOTAL_FUNC_MAX      : ScMax();     break;
+                case SUBTOTAL_FUNC_MIN      : ScMin();     break;
+                case SUBTOTAL_FUNC_PROD     : ScProduct(); break;
+                case SUBTOTAL_FUNC_STD      : ScStDev();   break;
+                case SUBTOTAL_FUNC_STDP     : ScStDevP();  break;
+                case SUBTOTAL_FUNC_SUM      : ScSum();     break;
+                case SUBTOTAL_FUNC_VAR      : ScVar();     break;
+                case SUBTOTAL_FUNC_VARP     : ScVarP();    break;
+                case AGGREGATE_FUNC_MEDIAN  : ScMedian();            break;
+                case AGGREGATE_FUNC_MODSNGL : ScModalValue();        break;
+                case AGGREGATE_FUNC_LARGE   : ScLarge();             break;
+                case AGGREGATE_FUNC_SMALL   : ScSmall();             break;
+                case AGGREGATE_FUNC_PERCINC : ScPercentile( true );  break;
+                case AGGREGATE_FUNC_QRTINC  : ScQuartile( true );    break;
+                case AGGREGATE_FUNC_PERCEXC : ScPercentile( false ); break;
+                case AGGREGATE_FUNC_QRTEXC  : ScQuartile( false );   break;
+                default : PushIllegalArgument();       break;
+            }
+            glSubTotal = false;
+        }
+        double nVal = GetDouble();
+        // Get rid of the 1st and 2nd (fished) parameters.
+        Pop();
+        Pop();
+        if ( !nGlobalError )
+            PushDouble( nVal );
+    }
+}
+
 ScDBQueryParamBase* ScInterpreter::GetDBParams( bool& rMissingField )
 {
     bool bAllowMissingField = false;
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 038c64966752..1a3294d769e6 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4095,6 +4095,7 @@ StackVar ScInterpreter::Interpret()
                 case ocEffektiv         : ScEffektiv();                 break;
                 case ocNominal          : ScNominal();                  break;
                 case ocSubTotal         : ScSubTotal();                 break;
+                case ocAggregate        : ScAggregate();                break;
                 case ocDBSum            : ScDBSum();                    break;
                 case ocDBCount          : ScDBCount();                  break;
                 case ocDBCount2         : ScDBCount2();                 break;
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index b19b361fca9d..040da290a15d 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -210,6 +210,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocZTest_MS,        {{ Reference, Value, Value                              }, 0 }},
     { ocNetWorkdays_MS,  {{ Value, Value, Value, Reference                       }, 0 }},
     { ocWorkday_MS,      {{ Value, Value, Value, Reference                       }, 0 }},
+    { ocAggregate,       {{ Value, Value, Reference                              }, 1 }},
     // Excel doubts:
     // ocN, ocT: Excel says (and handles) Reference, error? This means no
     // position dependent SingleRef if DoubleRef, and no array calculation,
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 449ebb7d4031..ff3d1c714ece 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -493,6 +493,7 @@ static const XclFunctionInfo saFuncTable_2010[] =
     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" ),
+    EXC_FUNCENTRY_V_RX( ocAggregate,        3, MX,  0,  "AGGREGATE" ),
 };
 
 /** Functions new in Excel 2013.
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index bb38d621f2d4..771e3b8310a6 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -803,6 +803,7 @@ static const FunctionData saFuncTable2010[] =
     { "COM.MICROSOFT.FLOOR.PRECISE",          "FLOOR.PRECISE",       NOID,   NOID,    2,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "COM.MICROSOFT.ERF.PRECISE",            "ERF.PRECISE",         NOID,   NOID,    1,  1,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "COM.MICROSOFT.ERFC.PRECISE",           "ERFC.PRECISE",        NOID,   NOID,    1,  1,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.AGGREGATE",              "AGGREGATE",           NOID,   NOID,    3,  MX, V, { VR, RO }, FUNCFLAG_MACROCALL_NEW }
 };
 
 /** Functions new in Excel 2013.
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index a248d72e2836..4fa92d8470de 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -4211,6 +4211,54 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
             Text [ en-US ] = "The cells of the range which are to be taken into account." ;
         };
     };
+     // -=*# Resource for function AGGREGATE #*=-
+    Resource SC_OPCODE_AGGREGATE
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Calculates an aggregate in a spreadsheet." ;
+        };
+        ExtraData =
+        {
+            1;
+            ID_FUNCTION_GRP_MATH;
+            U2S( HID_FUNC_AGGREGATE );
+            VAR_ARGS+3;  0;  0;  0; 1;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "Function" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "Function index. Is an index of the possible functions Total, Max, ..." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "Options" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "Option index. Is an index of the possible ignore options." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "Ref1 or array " ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "The cell(s) of the range which are to be taken into account." ;
+        };
+        String 8 // Name of Parameter 4
+        {
+            Text [ en-US ] = "Ref2..n or k " ;
+        };
+        String 9 // Description of Parameter 4
+        {
+            Text [ en-US ] = "The cells of the range which are to be taken into account or mandatory 2nd argument for certain functions." ;
+        };
+    };
      // -=*# Resource for function GANZZAHL #*=-
     Resource SC_OPCODE_INT
     {