From 298ee50676b849020a8a5042e8612f71379ecf3b Mon Sep 17 00:00:00 2001 From: Tamás Zolnai Date: Sat, 19 Nov 2016 23:59:49 +0100 Subject: PivotMedian: Implement median as a new pivot table function Change-Id: Ife90c8f1c36980254de3cec6e395a6ba94d99fea --- sc/inc/dpglobal.hxx | 19 +++++++++--------- sc/inc/dptabres.hxx | 1 + sc/inc/global.hxx | 3 ++- sc/inc/globstr.hrc | 3 ++- sc/source/core/data/dpoutput.cxx | 5 +++-- sc/source/core/data/dptabres.cxx | 27 ++++++++++++++++++++++++-- sc/source/core/data/dputil.cxx | 14 ++++++++----- sc/source/ui/dbgui/PivotLayoutTreeListData.cxx | 1 + sc/source/ui/dbgui/pvfundlg.cxx | 2 ++ sc/source/ui/dbgui/pvfundlg.src | 1 + sc/source/ui/src/globstr.src | 4 ++++ sc/source/ui/unoobj/dapiuno.cxx | 4 +++- sc/source/ui/unoobj/datauno.cxx | 1 + 13 files changed, 64 insertions(+), 21 deletions(-) diff --git a/sc/inc/dpglobal.hxx b/sc/inc/dpglobal.hxx index 7c82541a06e8..295af86db148 100644 --- a/sc/inc/dpglobal.hxx +++ b/sc/inc/dpglobal.hxx @@ -27,18 +27,19 @@ enum class PivotFunc { Sum = 0x0001, Count = 0x0002, Average = 0x0004, - Max = 0x0008, - Min = 0x0010, - Product = 0x0020, - CountNum = 0x0040, - StdDev = 0x0080, - StdDevP = 0x0100, - StdVar = 0x0200, - StdVarP = 0x0400, + Median = 0x0008, + Max = 0x0010, + Min = 0x0020, + Product = 0x0040, + CountNum = 0x0080, + StdDev = 0x0100, + StdDevP = 0x0200, + StdVar = 0x0400, + StdVarP = 0x0800, Auto = 0x1000 }; namespace o3tl { - template<> struct typed_flags : is_typed_flags {}; + template<> struct typed_flags : is_typed_flags {}; } struct ScDPValue diff --git a/sc/inc/dptabres.hxx b/sc/inc/dptabres.hxx index 05efe99b9c64..65dbf5dbb4d1 100644 --- a/sc/inc/dptabres.hxx +++ b/sc/inc/dptabres.hxx @@ -160,6 +160,7 @@ private: double fAux; long nCount; ScDPAggData* pChild; + std::vector mSortedValues; public: ScDPAggData() : fVal(0.0), fAux(0.0), nCount(SC_DPAGG_EMPTY), pChild(nullptr) {} diff --git a/sc/inc/global.hxx b/sc/inc/global.hxx index a94b855615af..429a867709df 100644 --- a/sc/inc/global.hxx +++ b/sc/inc/global.hxx @@ -851,7 +851,8 @@ enum ScSubTotalFunc SUBTOTAL_FUNC_SUM = 9, SUBTOTAL_FUNC_VAR = 10, SUBTOTAL_FUNC_VARP = 11, - SUBTOTAL_FUNC_SELECTION_COUNT = 12 + SUBTOTAL_FUNC_MED = 12, + SUBTOTAL_FUNC_SELECTION_COUNT = 13 }; enum ScAggregateFunc diff --git a/sc/inc/globstr.hrc b/sc/inc/globstr.hrc index 5833436788c2..e06d9215c6bb 100644 --- a/sc/inc/globstr.hrc +++ b/sc/inc/globstr.hrc @@ -149,6 +149,7 @@ #define STR_FUN_TEXT_SUM 108 #define STR_FUN_TEXT_COUNT 109 #define STR_FUN_TEXT_AVG 110 +#define STR_FUN_TEXT_MEDIAN 544 #define STR_FUN_TEXT_MAX 111 #define STR_FUN_TEXT_MIN 112 #define STR_FUN_TEXT_PRODUCT 113 @@ -709,7 +710,7 @@ #define STR_BOOLEAN_VALUE 542 #define STR_TEXT 543 -#define SC_GLOBSTR_STR_COUNT 544 /**< the count of permanently resident strings */ +#define SC_GLOBSTR_STR_COUNT 545 /**< the count of permanently resident strings */ #endif diff --git a/sc/source/core/data/dpoutput.cxx b/sc/source/core/data/dpoutput.cxx index ff6c2daa7f9a..7124b5170aa5 100644 --- a/sc/source/core/data/dpoutput.cxx +++ b/sc/source/core/data/dpoutput.cxx @@ -1548,6 +1548,7 @@ OUString lcl_GetDataFieldName( const OUString& rSourceName, sheet::GeneralFuncti case sheet::GeneralFunction_COUNT: case sheet::GeneralFunction_COUNTNUMS: nStrId = STR_FUN_TEXT_COUNT; break; case sheet::GeneralFunction_AVERAGE: nStrId = STR_FUN_TEXT_AVG; break; + case sheet::GeneralFunction_MEDIAN: nStrId = STR_FUN_TEXT_MEDIAN; break; case sheet::GeneralFunction_MAX: nStrId = STR_FUN_TEXT_MAX; break; case sheet::GeneralFunction_MIN: nStrId = STR_FUN_TEXT_MIN; break; case sheet::GeneralFunction_PRODUCT: nStrId = STR_FUN_TEXT_PRODUCT; break; @@ -1556,10 +1557,10 @@ OUString lcl_GetDataFieldName( const OUString& rSourceName, sheet::GeneralFuncti case sheet::GeneralFunction_VAR: case sheet::GeneralFunction_VARP: nStrId = STR_FUN_TEXT_VAR; break; case sheet::GeneralFunction_NONE: - case sheet::GeneralFunction_AUTO: + case sheet::GeneralFunction_AUTO: break; default: { - OSL_FAIL("wrong function"); + assert(false); } } if ( !nStrId ) diff --git a/sc/source/core/data/dptabres.cxx b/sc/source/core/data/dptabres.cxx index cba41fdbc19c..233329c854ac 100644 --- a/sc/source/core/data/dptabres.cxx +++ b/sc/source/core/data/dptabres.cxx @@ -57,7 +57,7 @@ using ::com::sun::star::uno::Sequence; namespace { -sal_uInt16 nFuncStrIds[12] = // matching enum ScSubTotalFunc +sal_uInt16 nFuncStrIds[] = // matching enum ScSubTotalFunc { 0, // SUBTOTAL_FUNC_NONE STR_FUN_TEXT_AVG, // SUBTOTAL_FUNC_AVE @@ -70,7 +70,9 @@ sal_uInt16 nFuncStrIds[12] = // matching enum ScSubTotalFunc STR_FUN_TEXT_STDDEV, // SUBTOTAL_FUNC_STDP STR_FUN_TEXT_SUM, // SUBTOTAL_FUNC_SUM STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VAR - STR_FUN_TEXT_VAR // SUBTOTAL_FUNC_VARP + STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VARP + STR_FUN_TEXT_MEDIAN, // SUBTOTAL_FUNC_MED + 0 // SUBTOTAL_FUNC_SELECTION_COUNT - not used for pivot table }; bool lcl_SearchMember( const std::vector & list, SCROW nOrder, SCROW& rIndex) @@ -439,6 +441,15 @@ void ScDPAggData::Update( const ScDPValue& rNext, ScSubTotalFunc eFunc, const Sc nCount = -1; // -1 for error } break; + case SUBTOTAL_FUNC_MED: + { + auto aIter = std::upper_bound(mSortedValues.begin(), mSortedValues.end(), rNext.mfValue); + if (aIter == mSortedValues.end()) + mSortedValues.push_back(rNext.mfValue); + else + mSortedValues.insert(aIter, rNext.mfValue); + } + break; default: OSL_FAIL("invalid function"); } @@ -475,6 +486,7 @@ void ScDPAggData::Calculate( ScSubTotalFunc eFunc, const ScDPSubTotalState& rSub break; case SUBTOTAL_FUNC_AVE: + case SUBTOTAL_FUNC_MED: case SUBTOTAL_FUNC_MAX: case SUBTOTAL_FUNC_MIN: case SUBTOTAL_FUNC_STDP: @@ -534,6 +546,16 @@ void ScDPAggData::Calculate( ScSubTotalFunc eFunc, const ScDPSubTotalState& rSub if ( nCount > 0 ) fResult = (fAux - fVal*fVal/(double)(nCount)) / (double)nCount; break; + case SUBTOTAL_FUNC_MED: + if (mSortedValues.size() > 0) + { + assert(mSortedValues.size() == static_cast(nCount)); + if ((mSortedValues.size() % 2) == 1) + fResult = mSortedValues[mSortedValues.size() / 2]; + else + fResult = (mSortedValues[mSortedValues.size() / 2 - 1] + mSortedValues[mSortedValues.size() / 2]) / 2.0; + } + break; default: OSL_FAIL("invalid function"); } @@ -816,6 +838,7 @@ OUString ScDPResultData::GetMeasureString(long nMeasure, bool bForce, ScSubTotal { // for user-specified subtotal function with all measures, // display only function name + assert(eForceFunc < SAL_N_ELEMENTS(nFuncStrIds)); if ( eForceFunc != SUBTOTAL_FUNC_NONE ) return ScGlobal::GetRscString(nFuncStrIds[eForceFunc]); diff --git a/sc/source/core/data/dputil.cxx b/sc/source/core/data/dputil.cxx index 93d79ed15a58..b0f868fd779d 100644 --- a/sc/source/core/data/dputil.cxx +++ b/sc/source/core/data/dputil.cxx @@ -367,7 +367,7 @@ sal_Int32 ScDPUtil::getDatePartValue( namespace { -sal_uInt16 nFuncStrIds[12] = { +sal_uInt16 nFuncStrIds[] = { 0, // SUBTOTAL_FUNC_NONE STR_FUN_TEXT_AVG, // SUBTOTAL_FUNC_AVE STR_FUN_TEXT_COUNT, // SUBTOTAL_FUNC_CNT @@ -379,7 +379,9 @@ sal_uInt16 nFuncStrIds[12] = { STR_FUN_TEXT_STDDEV, // SUBTOTAL_FUNC_STDP STR_FUN_TEXT_SUM, // SUBTOTAL_FUNC_SUM STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VAR - STR_FUN_TEXT_VAR // SUBTOTAL_FUNC_VARP + STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VARP + STR_FUN_TEXT_MEDIAN, // SUBTOTAL_FUNC_MED + 0 // SUBTOTAL_FUNC_SELECTION_COUNT - not used for pivot table }; } @@ -387,6 +389,7 @@ sal_uInt16 nFuncStrIds[12] = { OUString ScDPUtil::getDisplayedMeasureName(const OUString& rName, ScSubTotalFunc eFunc) { OUStringBuffer aRet; + assert(eFunc < SAL_N_ELEMENTS(nFuncStrIds)); sal_uInt16 nId = nFuncStrIds[eFunc]; if (nId) { @@ -400,13 +403,14 @@ OUString ScDPUtil::getDisplayedMeasureName(const OUString& rName, ScSubTotalFunc ScSubTotalFunc ScDPUtil::toSubTotalFunc(css::sheet::GeneralFunction eGenFunc) { - ScSubTotalFunc eSubTotal; + ScSubTotalFunc eSubTotal = SUBTOTAL_FUNC_NONE; switch (eGenFunc) { case sheet::GeneralFunction_NONE: eSubTotal = SUBTOTAL_FUNC_NONE; break; case sheet::GeneralFunction_SUM: eSubTotal = SUBTOTAL_FUNC_SUM; break; case sheet::GeneralFunction_COUNT: eSubTotal = SUBTOTAL_FUNC_CNT2; break; case sheet::GeneralFunction_AVERAGE: eSubTotal = SUBTOTAL_FUNC_AVE; break; + case sheet::GeneralFunction_MEDIAN: eSubTotal = SUBTOTAL_FUNC_MED; break; case sheet::GeneralFunction_MAX: eSubTotal = SUBTOTAL_FUNC_MAX; break; case sheet::GeneralFunction_MIN: eSubTotal = SUBTOTAL_FUNC_MIN; break; case sheet::GeneralFunction_PRODUCT: eSubTotal = SUBTOTAL_FUNC_PROD; break; @@ -415,9 +419,9 @@ ScSubTotalFunc ScDPUtil::toSubTotalFunc(css::sheet::GeneralFunction eGenFunc) case sheet::GeneralFunction_STDEVP: eSubTotal = SUBTOTAL_FUNC_STDP; break; case sheet::GeneralFunction_VAR: eSubTotal = SUBTOTAL_FUNC_VAR; break; case sheet::GeneralFunction_VARP: eSubTotal = SUBTOTAL_FUNC_VARP; break; - case sheet::GeneralFunction_AUTO: + case sheet::GeneralFunction_AUTO: eSubTotal = SUBTOTAL_FUNC_NONE; break; default: - eSubTotal = SUBTOTAL_FUNC_NONE; + assert(false); } return eSubTotal; } diff --git a/sc/source/ui/dbgui/PivotLayoutTreeListData.cxx b/sc/source/ui/dbgui/PivotLayoutTreeListData.cxx index 401fa81842af..8eae041af1a9 100644 --- a/sc/source/ui/dbgui/PivotLayoutTreeListData.cxx +++ b/sc/source/ui/dbgui/PivotLayoutTreeListData.cxx @@ -35,6 +35,7 @@ OUString lclGetFunctionMaskName(const PivotFunc nFunctionMask) case PivotFunc::Sum: nStrId = STR_FUN_TEXT_SUM; break; case PivotFunc::Count: nStrId = STR_FUN_TEXT_COUNT; break; case PivotFunc::Average: nStrId = STR_FUN_TEXT_AVG; break; + case PivotFunc::Median: nStrId = STR_FUN_TEXT_MEDIAN; break; case PivotFunc::Max: nStrId = STR_FUN_TEXT_MAX; break; case PivotFunc::Min: nStrId = STR_FUN_TEXT_MIN; break; case PivotFunc::Product: nStrId = STR_FUN_TEXT_PRODUCT; break; diff --git a/sc/source/ui/dbgui/pvfundlg.cxx b/sc/source/ui/dbgui/pvfundlg.cxx index d947b51d4ad9..5a9ad2b33621 100644 --- a/sc/source/ui/dbgui/pvfundlg.cxx +++ b/sc/source/ui/dbgui/pvfundlg.cxx @@ -100,6 +100,7 @@ static const PivotFunc spnFunctions[] = PivotFunc::Sum, PivotFunc::Count, PivotFunc::Average, + PivotFunc::Median, PivotFunc::Max, PivotFunc::Min, PivotFunc::Product, @@ -189,6 +190,7 @@ void ScDPFunctionListBox::FillFunctionNames() ResStringArray aArr( ScResId( SCSTR_DPFUNCLISTBOX ) ); for( sal_uInt16 nIndex = 0, nCount = sal::static_int_cast(aArr.Count()); nIndex < nCount; ++nIndex ) InsertEntry( aArr.GetString( nIndex ) ); + assert(GetEntryCount() == SAL_N_ELEMENTS(spnFunctions)); } ScDPFunctionDlg::ScDPFunctionDlg( diff --git a/sc/source/ui/dbgui/pvfundlg.src b/sc/source/ui/dbgui/pvfundlg.src index fe33cf80ebab..0de9461f69e9 100644 --- a/sc/source/ui/dbgui/pvfundlg.src +++ b/sc/source/ui/dbgui/pvfundlg.src @@ -26,6 +26,7 @@ StringArray SCSTR_DPFUNCLISTBOX < "Sum" ; Default ; > ; < "Count" ; Default ; > ; < "Average" ; Default ; > ; + < "Median" ; Default ; > ; < "Max" ; Default ; > ; < "Min" ; Default ; > ; < "Product" ; Default ; > ; diff --git a/sc/source/ui/src/globstr.src b/sc/source/ui/src/globstr.src index 241254b0b1ee..0827a6aee03c 100644 --- a/sc/source/ui/src/globstr.src +++ b/sc/source/ui/src/globstr.src @@ -658,6 +658,10 @@ Resource RID_GLOBSTR { Text [ en-US ] = "Average" ; }; + String STR_FUN_TEXT_MEDIAN + { + Text [ en-US ] = "Median" ; + }; String STR_FUN_TEXT_MAX { Text [ en-US ] = "Max" ; diff --git a/sc/source/ui/unoobj/dapiuno.cxx b/sc/source/ui/unoobj/dapiuno.cxx index a6f7f5846842..fe2590b09c7c 100644 --- a/sc/source/ui/unoobj/dapiuno.cxx +++ b/sc/source/ui/unoobj/dapiuno.cxx @@ -184,6 +184,7 @@ GeneralFunction ScDataPilotConversion::FirstFunc( PivotFunc nBits ) if ( nBits & PivotFunc::Sum ) return GeneralFunction_SUM; if ( nBits & PivotFunc::Count ) return GeneralFunction_COUNT; if ( nBits & PivotFunc::Average ) return GeneralFunction_AVERAGE; + if ( nBits & PivotFunc::Median ) return GeneralFunction_MEDIAN; if ( nBits & PivotFunc::Max ) return GeneralFunction_MAX; if ( nBits & PivotFunc::Min ) return GeneralFunction_MIN; if ( nBits & PivotFunc::Product ) return GeneralFunction_PRODUCT; @@ -204,6 +205,7 @@ PivotFunc ScDataPilotConversion::FunctionBit( GeneralFunction eFunc ) case GeneralFunction_SUM: nRet = PivotFunc::Sum; break; case GeneralFunction_COUNT: nRet = PivotFunc::Count; break; case GeneralFunction_AVERAGE: nRet = PivotFunc::Average; break; + case GeneralFunction_MEDIAN: nRet = PivotFunc::Median; break; case GeneralFunction_MAX: nRet = PivotFunc::Max; break; case GeneralFunction_MIN: nRet = PivotFunc::Min; break; case GeneralFunction_PRODUCT: nRet = PivotFunc::Product; break; @@ -215,7 +217,7 @@ PivotFunc ScDataPilotConversion::FunctionBit( GeneralFunction eFunc ) case GeneralFunction_AUTO: nRet = PivotFunc::Auto; break; default: { - // added to avoid warnings + assert(false); } } return nRet; diff --git a/sc/source/ui/unoobj/datauno.cxx b/sc/source/ui/unoobj/datauno.cxx index 718af054bfcb..403bb6a461b3 100644 --- a/sc/source/ui/unoobj/datauno.cxx +++ b/sc/source/ui/unoobj/datauno.cxx @@ -144,6 +144,7 @@ sheet::GeneralFunction ScDataUnoConversion::SubTotalToGeneral( ScSubTotalFunc e case SUBTOTAL_FUNC_AVE: eGeneral = sheet::GeneralFunction_AVERAGE; break; case SUBTOTAL_FUNC_CNT: eGeneral = sheet::GeneralFunction_COUNTNUMS; break; case SUBTOTAL_FUNC_CNT2: eGeneral = sheet::GeneralFunction_COUNT; break; + case SUBTOTAL_FUNC_MED: eGeneral = sheet::GeneralFunction_MEDIAN; break; case SUBTOTAL_FUNC_MAX: eGeneral = sheet::GeneralFunction_MAX; break; case SUBTOTAL_FUNC_MIN: eGeneral = sheet::GeneralFunction_MIN; break; case SUBTOTAL_FUNC_PROD: eGeneral = sheet::GeneralFunction_PRODUCT; break; -- cgit v1.2.3