initial support for percentile in data bars/color scales

Color Scales look correctly but in the databar code seems to be an error
if all values are positive.

Change-Id: I0bfb277df50021bd20a4b13a5da342670102b649

4 files changed, 109 insertions, 19 deletions

diff --git a/sc/inc/colorscale.hxx b/sc/inc/colorscale.hxx
index 997c2a0c6f36..9f4d02fb406a 100644
--- a/sc/inc/colorscale.hxx
+++ b/sc/inc/colorscale.hxx
@@ -32,6 +32,8 @@
 #include <tools/color.hxx>
 #include <rangelst.hxx>
 
+#include <vector>
+
 //TODO: merge this with conditio.hxx
 
 class ScDocument;
@@ -49,6 +51,7 @@ private:
     bool mbMin;
     bool mbMax;
     bool mbPercent;
+    bool mbPercentile;
 public:
     ScColorScaleEntry(double nVal, const Color& rCol);
     ScColorScaleEntry(const ScColorScaleEntry& rEntry);
@@ -65,11 +68,13 @@ public:
     bool GetMin() const;
     bool GetMax() const;
     bool GetPercent() const;
+    bool GetPercentile() const;
     bool HasFormula() const;
     const ScTokenArray* GetFormula() const;
     void SetMin(bool bMin);
     void SetMax(bool bMax);
     void SetPercent(bool bPercent);
+    void SetPercentile(bool bPercentile);
 };
 
 namespace databar
@@ -160,6 +165,8 @@ public:
     virtual ScColorFormatType GetType() const = 0;
 
 protected:
+    void getValues( std::vector<double>& rValues ) const;
+
     ScRangeList maRanges;
     ScDocument* mpDoc;
 };
@@ -175,6 +182,7 @@ private:
 
     void calcMinMax(double& nMin, double& nMax) const;
     bool CheckEntriesForRel(const ScRange& rRange) const;
+    double CalcValue(double nMin, double nMax, ColorScaleEntries::const_iterator& rItr) const;
 public:
     ScColorScaleFormat(ScDocument* pDoc);
     ScColorScaleFormat(ScDocument* pDoc, const ScColorScaleFormat& rFormat);
diff --git a/sc/source/core/data/colorscale.cxx b/sc/source/core/data/colorscale.cxx
index 830610791448..9636365a2b7a 100644
--- a/sc/source/core/data/colorscale.cxx
+++ b/sc/source/core/data/colorscale.cxx
@@ -37,7 +37,8 @@ ScColorScaleEntry::ScColorScaleEntry(double nVal, const Color& rCol):
     mpCell(NULL),
     mbMin(false),
     mbMax(false),
-    mbPercent(false)
+    mbPercent(false),
+    mbPercentile(false)
 {
 }
 
@@ -47,7 +48,8 @@ ScColorScaleEntry::ScColorScaleEntry(const ScColorScaleEntry& rEntry):
     mpCell(),
     mbMin(rEntry.mbMin),
     mbMax(rEntry.mbMax),
-    mbPercent(rEntry.mbPercent)
+    mbPercent(rEntry.mbPercent),
+    mbPercentile(rEntry.mbPercentile)
 {
 }
 
@@ -57,7 +59,8 @@ ScColorScaleEntry::ScColorScaleEntry(ScDocument* pDoc, const ScColorScaleEntry&
     mpCell(),
     mbMin(rEntry.mbMin),
     mbMax(rEntry.mbMax),
-    mbPercent(rEntry.mbPercent)
+    mbPercent(rEntry.mbPercent),
+    mbPercentile(rEntry.mbPercentile)
 {
     if(rEntry.mpCell)
     {
@@ -179,6 +182,11 @@ bool ScColorScaleEntry::GetPercent() const
     return mbPercent;
 }
 
+bool ScColorScaleEntry::GetPercentile() const
+{
+    return mbPercentile;
+}
+
 bool ScColorScaleEntry::HasFormula() const
 {
     return mpCell;
@@ -199,6 +207,11 @@ void ScColorScaleEntry::SetPercent(bool bPercent)
     mbPercent = bPercent;
 }
 
+void ScColorScaleEntry::SetPercentile(bool bPercentile)
+{
+    mbPercentile = bPercentile;
+}
+
 namespace {
 
 double getMinValue(const ScRange& rRange, ScDocument* pDoc)
@@ -336,6 +349,37 @@ const ScRangeList& ScColorFormat::GetRange() const
     return maRanges;
 }
 
+void ScColorFormat::getValues(std::vector<double>& rValues) const
+{
+    size_t n = maRanges.size();
+    for(size_t i = 0; i < n; ++i)
+    {
+        const ScRange* pRange = maRanges[i];
+        SCTAB nTab = pRange->aStart.Tab();
+        for(SCCOL nCol = pRange->aStart.Col(); nCol <= pRange->aEnd.Col(); ++nCol)
+        {
+            for(SCCOL nRow = pRange->aStart.Row(); nRow <= pRange->aEnd.Row(); ++nRow)
+            {
+                ScAddress aAddr(nCol, nRow, nTab);
+                CellType eType = mpDoc->GetCellType(aAddr);
+                if(eType == CELLTYPE_VALUE)
+                {
+                    double aVal = mpDoc->GetValue(nCol, nRow, nTab);
+                    rValues.push_back(aVal);
+                }
+                else if(eType == CELLTYPE_FORMULA)
+                {
+                    if(static_cast<ScFormulaCell*>(mpDoc->GetCell(aAddr))->IsValue())
+                    {
+                        double aVal = mpDoc->GetValue(nCol, nRow, nTab);
+                        rValues.push_back(aVal);
+                    }
+                }
+            }
+        }
+    }
+}
+
 namespace {
 
 sal_uInt8 GetColorValue( double nVal, double nVal1, sal_uInt8 nColVal1, double nVal2, sal_uInt8 nColVal2 )
@@ -359,7 +403,27 @@ Color CalcColor( double nVal, double nVal1, const Color& rCol1, double nVal2, co
     return Color(nColRed, nColGreen, nColBlue);
 }
 
-double CalcValue(double nMin, double nMax, ScColorScaleFormat::const_iterator& itr)
+double GetPercentile( std::vector<double>& rArray, double fPercentile )
+{
+    size_t nSize = rArray.size();
+    size_t nIndex = (size_t)::rtl::math::approxFloor( fPercentile * (nSize-1));
+    double fDiff = fPercentile * (nSize-1) - ::rtl::math::approxFloor( fPercentile * (nSize-1));
+    std::vector<double>::iterator iter = rArray.begin() + nIndex;
+    ::std::nth_element( rArray.begin(), iter, rArray.end());
+    if (fDiff == 0.0)
+        return *iter;
+    else
+    {
+        double fVal = *iter;
+        iter = rArray.begin() + nIndex+1;
+        ::std::nth_element( rArray.begin(), iter, rArray.end());
+        return fVal + fDiff * (*iter - fVal);
+    }
+}
+
+}
+
+double ScColorScaleFormat::CalcValue(double nMin, double nMax, ScColorScaleFormat::const_iterator& itr) const
 {
     if(itr->GetPercent())
     {
@@ -373,12 +437,22 @@ double CalcValue(double nMin, double nMax, ScColorScaleFormat::const_iterator& i
     {
         return nMax;
     }
+    else if(itr->GetPercentile())
+    {
+        std::vector<double> aValues;
+        getValues(aValues);
+        if(aValues.size() == 1)
+            return aValues[0];
+        else
+        {
+            double fPercentile = itr->GetValue()/100.0;
+            return GetPercentile(aValues, fPercentile);
+        }
+    }
 
     return itr->GetValue();
 }
 
-}
-
 Color* ScColorScaleFormat::GetColor( const ScAddress& rAddr ) const
 {
     CellType eCellType = mpDoc->GetCellType(rAddr);
@@ -638,10 +712,14 @@ double ScDataBarFormat::getMin(double nMin, double nMax) const
 {
     if(mpFormatData->mpLowerLimit->GetMin())
         return nMin;
-
-    if(mpFormatData->mpLowerLimit->GetPercent())
-    {
+    else if(mpFormatData->mpLowerLimit->GetPercent())
         return nMin + (nMax-nMin)/100*mpFormatData->mpLowerLimit->GetValue();
+    else if(mpFormatData->mpLowerLimit->GetPercentile())
+    {
+        double fPercentile = mpFormatData->mpLowerLimit->GetValue()/100.0;
+        std::vector<double> aValues;
+        getValues(aValues);
+        return GetPercentile(aValues, fPercentile);
     }
 
     return mpFormatData->mpLowerLimit->GetValue();
@@ -651,10 +729,14 @@ double ScDataBarFormat::getMax(double nMin, double nMax) const
 {
     if(mpFormatData->mpUpperLimit->GetMax())
         return nMax;
-
-    if(mpFormatData->mpUpperLimit->GetPercent())
-    {
+    else if(mpFormatData->mpUpperLimit->GetPercent())
         return nMin + (nMax-nMin)/100*mpFormatData->mpUpperLimit->GetValue();
+    else if(mpFormatData->mpLowerLimit->GetPercentile())
+    {
+        double fPercentile = mpFormatData->mpLowerLimit->GetValue()/100.0;
+        std::vector<double> aValues;
+        getValues(aValues);
+        return GetPercentile(aValues, fPercentile);
     }
 
     return mpFormatData->mpUpperLimit->GetValue();
diff --git a/sc/source/filter/inc/condformatbuffer.hxx b/sc/source/filter/inc/condformatbuffer.hxx
index 60f24d749a39..4dbe61330ac0 100644
--- a/sc/source/filter/inc/condformatbuffer.hxx
+++ b/sc/source/filter/inc/condformatbuffer.hxx
@@ -86,6 +86,7 @@ struct ColorScaleRuleModelEntry
     bool mbMin;
     bool mbMax;
     bool mbPercent;
+    bool mbPercentile;
     rtl::OUString maFormula;
 
     ColorScaleRuleModelEntry():
@@ -93,7 +94,8 @@ struct ColorScaleRuleModelEntry
         mnVal(0),
         mbMin(false),
         mbMax(false),
-        mbPercent(false) {}
+        mbPercent(false),
+        mbPercentile(false) {}
 };
 
 class ColorScaleRule : public WorksheetHelper
diff --git a/sc/source/filter/oox/condformatbuffer.cxx b/sc/source/filter/oox/condformatbuffer.cxx
index 05681b8bd862..3471e11e951f 100644
--- a/sc/source/filter/oox/condformatbuffer.cxx
+++ b/sc/source/filter/oox/condformatbuffer.cxx
@@ -178,9 +178,7 @@ void ColorScaleRule::importCfvo( const AttributeList& rAttribs )
     }
     else if( aType == "percentile" )
     {
-        // this is most likely wrong but I have no idea what the difference
-        // between percent and percentile should be when calculating colors
-        maColorScaleRuleEntries[mnCfvo].mbPercent = true;
+        maColorScaleRuleEntries[mnCfvo].mbPercentile = true;
     }
     else if( aType == "formula" )
     {
@@ -236,6 +234,8 @@ ScColorScaleEntry* ConvertToModel( const ColorScaleRuleModelEntry& rEntry, ScDoc
             pEntry->SetMax(true);
         if(rEntry.mbPercent)
             pEntry->SetPercent(true);
+        if(rEntry.mbPercentile)
+            pEntry->SetPercentile(true);
 
         if(!rEntry.maFormula.isEmpty())
             pEntry->SetFormula(rEntry.maFormula, pDoc, rAddr, formula::FormulaGrammar::GRAM_ENGLISH_XL_A1);
@@ -318,9 +318,7 @@ void DataBarRule::importCfvo( const AttributeList& rAttribs )
     }
     else if( aType == "percentile" )
     {
-        // this is most likely wrong but I have no idea what the difference
-        // between percent and percentile should be when calculating colors
-        pEntry->mbPercent = true;
+        pEntry->mbPercentile = true;
     }
     else if( aType == "formula" )
     {