From 29c9712b042e410e93e92dead15f353f6ca1e263 Mon Sep 17 00:00:00 2001 From: Winfried Donkers Date: Thu, 17 Apr 2014 21:58:26 +0200 Subject: fdo#73148 (partial patch) Add Excel 2010 function AGGREGATE Change-Id: If8fc8608699a14e573456af2e1180342be942dd1 Reviewed-on: https://gerrit.libreoffice.org/8475 Reviewed-by: Eike Rathke Tested-by: Eike Rathke --- sc/inc/global.hxx | 16 +++++++ sc/inc/helpids.h | 1 + sc/qa/unit/ucalc.cxx | 1 + sc/source/core/inc/interpre.hxx | 1 + sc/source/core/tool/interpr1.cxx | 91 ++++++++++++++++++++++++++++++++++++ sc/source/core/tool/interpr4.cxx | 1 + sc/source/core/tool/parclass.cxx | 1 + sc/source/filter/excel/xlformula.cxx | 1 + sc/source/filter/oox/formulabase.cxx | 1 + sc/source/ui/src/scfuncs.src | 48 +++++++++++++++++++ 10 files changed, 162 insertions(+) (limited to 'sc') 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& rSortArray, ::std::vector* 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 @@ -4210,6 +4210,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 -- cgit