fix above/below equal average ods import/export

Change-Id: I1c305c67772b46cede6b4da3e79578f2f1a8ee89

11 files changed, 112 insertions, 33 deletions

diff --git a/sc/inc/conditio.hxx b/sc/inc/conditio.hxx
index dba96735c43d..d5a55e3fd29a 100644
--- a/sc/inc/conditio.hxx
+++ b/sc/inc/conditio.hxx
@@ -62,6 +62,8 @@ enum ScConditionMode
     SC_COND_BOTTOM_PERCENT,
     SC_COND_ABOVE_AVERAGE,
     SC_COND_BELOW_AVERAGE,
+    SC_COND_ABOVE_EQUAL_AVERAGE,
+    SC_COND_BELOW_EQUAL_AVERAGE,
     SC_COND_ERROR,
     SC_COND_NOERROR,
     SC_COND_BEGINS_WITH,
@@ -244,8 +246,8 @@ private:
     bool IsTopNPercent( double nArg ) const;
     bool IsBottomNElement( double nArg ) const;
     bool IsBottomNPercent( double nArg ) const;
-    bool IsAboveAverage( double nArg ) const;
-    bool IsBelowAverage( double nArg ) const;
+    bool IsAboveAverage( double nArg, bool bEqual ) const;
+    bool IsBelowAverage( double nArg, bool bEqual ) const;
 
     bool IsError( const ScAddress& rPos ) const;
 
diff --git a/sc/inc/globstr.hrc b/sc/inc/globstr.hrc
index d49b1da5b4ea..9eec9cecc3a8 100644
--- a/sc/inc/globstr.hrc
+++ b/sc/inc/globstr.hrc
@@ -665,7 +665,10 @@
 #define STR_DPFIELD_GROUP_BY_QUARTERS    533
 #define STR_DPFIELD_GROUP_BY_YEARS       534
 
-#define STR_COUNT                   535
+#define STR_COND_ABOVE_EQUAL_AVERAGE 535
+#define STR_COND_BELOW_EQUAL_AVERAGE 536
+
+#define STR_COUNT                   537
 
 #endif
 
diff --git a/sc/source/core/data/conditio.cxx b/sc/source/core/data/conditio.cxx
index cecb844b7f47..772a190b4f95 100644
--- a/sc/source/core/data/conditio.cxx
+++ b/sc/source/core/data/conditio.cxx
@@ -933,7 +933,7 @@ bool ScConditionEntry::IsBottomNPercent( double nArg ) const
     return true;
 }
 
-bool ScConditionEntry::IsBelowAverage( double nArg ) const
+bool ScConditionEntry::IsBelowAverage( double nArg, bool bEqual ) const
 {
     FillCache();
 
@@ -944,13 +944,13 @@ bool ScConditionEntry::IsBelowAverage( double nArg ) const
         nSum += itr->first * itr->second;
     }
 
-    if(nVal1)
+    if(bEqual)
         return (nArg <= nSum/mpCache->nValueItems);
     else
         return (nArg < nSum/mpCache->nValueItems);
 }
 
-bool ScConditionEntry::IsAboveAverage( double nArg ) const
+bool ScConditionEntry::IsAboveAverage( double nArg, bool bEqual ) const
 {
     FillCache();
 
@@ -961,7 +961,7 @@ bool ScConditionEntry::IsAboveAverage( double nArg ) const
         nSum += itr->first * itr->second;
     }
 
-    if(nVal1)
+    if(bEqual)
         return (nArg >= nSum/mpCache->nValueItems);
     else
         return (nArg > nSum/mpCache->nValueItems);
@@ -1084,10 +1084,12 @@ bool ScConditionEntry::IsValid( double nArg, const ScAddress& rPos ) const
             bValid = IsBottomNPercent( nArg );
             break;
         case SC_COND_ABOVE_AVERAGE:
-            bValid = IsAboveAverage( nArg );
+        case SC_COND_ABOVE_EQUAL_AVERAGE:
+            bValid = IsAboveAverage( nArg, eOp == SC_COND_ABOVE_EQUAL_AVERAGE );
             break;
         case SC_COND_BELOW_AVERAGE:
-            bValid = IsBelowAverage( nArg );
+        case SC_COND_BELOW_EQUAL_AVERAGE:
+            bValid = IsBelowAverage( nArg, eOp == SC_COND_BELOW_EQUAL_AVERAGE );
             break;
         case SC_COND_ERROR:
         case SC_COND_NOERROR:
diff --git a/sc/source/filter/excel/xecontent.cxx b/sc/source/filter/excel/xecontent.cxx
index e7069cdae9cb..7ad587475cce 100644
--- a/sc/source/filter/excel/xecontent.cxx
+++ b/sc/source/filter/excel/xecontent.cxx
@@ -833,6 +833,8 @@ const char* GetTypeString(ScConditionMode eMode)
             return "top10";
         case SC_COND_ABOVE_AVERAGE:
         case SC_COND_BELOW_AVERAGE:
+        case SC_COND_ABOVE_EQUAL_AVERAGE:
+        case SC_COND_BELOW_EQUAL_AVERAGE:
             return "aboveAverage";
         case SC_COND_NOTDUPLICATE:
             return "uniqueValues";
@@ -893,7 +895,10 @@ void XclExpCFImpl::SaveXml( XclExpXmlStream& rStrm )
 {
     bool bFmla2 = false;
     ScConditionMode eOperation = mrFormatEntry.GetOperation();
-    sal_Int32 nAboveAverage = eOperation == SC_COND_ABOVE_AVERAGE;
+    sal_Int32 nAboveAverage = eOperation == SC_COND_ABOVE_AVERAGE ||
+                                eOperation == SC_COND_ABOVE_EQUAL_AVERAGE;
+    sal_Int32 nEqualAverage = eOperation == SC_COND_ABOVE_EQUAL_AVERAGE ||
+                                eOperation == SC_COND_BELOW_EQUAL_AVERAGE;
     sal_Int32 nBottom = eOperation == SC_COND_BOTTOM10
         || eOperation == SC_COND_BOTTOM_PERCENT;
     sal_Int32 nPercent = eOperation == SC_COND_TOP_PERCENT ||
@@ -922,6 +927,7 @@ void XclExpCFImpl::SaveXml( XclExpXmlStream& rStrm )
             XML_priority, OString::valueOf( mnPriority + 1 ).getStr(),
             XML_operator, GetOperatorString( mrFormatEntry.GetOperation(), bFmla2 ),
             XML_aboveAverage, OString::valueOf( nAboveAverage ).getStr(),
+            XML_equalAverage, OString::valueOf( nEqualAverage ).getStr(),
             XML_bottom, OString::valueOf( nBottom ).getStr(),
             XML_percent, OString::valueOf( nPercent ).getStr(),
             XML_rank, aRank.getStr(),
diff --git a/sc/source/filter/oox/condformatbuffer.cxx b/sc/source/filter/oox/condformatbuffer.cxx
index aec2f5db0e59..15d193071bfd 100644
--- a/sc/source/filter/oox/condformatbuffer.cxx
+++ b/sc/source/filter/oox/condformatbuffer.cxx
@@ -716,9 +716,19 @@ void CondFormatRule::finalizeImport()
         break;
         case XML_aboveAverage:
             if(maModel.mbAboveAverage)
-                eOperator = SC_COND_ABOVE_AVERAGE;
+            {
+                if(maModel.mbEqualAverage)
+                    eOperator = SC_COND_ABOVE_EQUAL_AVERAGE;
+                else
+                    eOperator = SC_COND_ABOVE_AVERAGE;
+            }
             else
-                eOperator = SC_COND_BELOW_AVERAGE;
+            {
+                if(maModel.mbEqualAverage)
+                    eOperator = SC_COND_BELOW_EQUAL_AVERAGE;
+                else
+                    eOperator = SC_COND_BELOW_AVERAGE;
+            }
         break;
         case XML_colorScale:
         break;
@@ -796,15 +806,15 @@ void CondFormatRule::finalizeImport()
         ScCondFormatEntry* pNewEntry = new ScCondFormatEntry( eOperator, &aTokenArray, NULL, &rDoc, aPos, aStyleName );
         mpFormat->AddEntry(pNewEntry);
     }
-    else if( eOperator == SC_COND_ABOVE_AVERAGE || eOperator == SC_COND_BELOW_AVERAGE )
+    else if( eOperator == SC_COND_ABOVE_AVERAGE || eOperator == SC_COND_BELOW_AVERAGE ||
+            eOperator == SC_COND_ABOVE_EQUAL_AVERAGE || eOperator == SC_COND_BELOW_EQUAL_AVERAGE )
     {
         ScDocument& rDoc = getScDocument();
-        ScTokenArray aTokenArrayEqual;
-        aTokenArrayEqual.AddDouble( maModel.mbEqualAverage );
+        // actually that is still unsupported
         ScTokenArray aTokenArrayDev;
         aTokenArrayDev.AddDouble( maModel.mnStdDev );
         OUString aStyleName = getStyles().createDxfStyle( maModel.mnDxfId );
-        ScCondFormatEntry* pNewEntry = new ScCondFormatEntry( eOperator, &aTokenArrayEqual, &aTokenArrayDev, &rDoc, aPos, aStyleName );
+        ScCondFormatEntry* pNewEntry = new ScCondFormatEntry( eOperator, &aTokenArrayDev, NULL, &rDoc, aPos, aStyleName );
         mpFormat->AddEntry(pNewEntry);
     }
     else if( eOperator == SC_COND_DUPLICATE || eOperator == SC_COND_NOTDUPLICATE )
diff --git a/sc/source/filter/xml/xmlcondformat.cxx b/sc/source/filter/xml/xmlcondformat.cxx
index 5ca2cb733160..ef0468ab2474 100644
--- a/sc/source/filter/xml/xmlcondformat.cxx
+++ b/sc/source/filter/xml/xmlcondformat.cxx
@@ -485,7 +485,7 @@ void GetConditionData(const rtl::OUString& rValue, ScConditionMode& eMode, rtl::
     else if(rValue.indexOf("top-percent") == 0)
     {
         const sal_Unicode* pStr = rValue.getStr();
-        const sal_Unicode* pStart = pStr + 11;
+        const sal_Unicode* pStart = pStr + 12;
         const sal_Unicode* pEnd = pStr + rValue.getLength();
         rExpr1 = ScXMLConditionHelper::getExpression( pStart, pEnd, ')');
         eMode = SC_COND_TOP_PERCENT;
@@ -498,6 +498,22 @@ void GetConditionData(const rtl::OUString& rValue, ScConditionMode& eMode, rtl::
         rExpr1 = ScXMLConditionHelper::getExpression( pStart, pEnd, ')');
         eMode = SC_COND_BOTTOM_PERCENT;
     }
+    else if(rValue.indexOf("above-average") == 0)
+    {
+        eMode = SC_COND_ABOVE_AVERAGE;
+    }
+    else if(rValue.indexOf("below-average") == 0)
+    {
+        eMode = SC_COND_BELOW_AVERAGE;
+    }
+    else if(rValue.indexOf("above-equal-average") == 0)
+    {
+        eMode = SC_COND_ABOVE_EQUAL_AVERAGE;
+    }
+    else if(rValue.indexOf("below-equal-average") == 0)
+    {
+        eMode = SC_COND_BELOW_EQUAL_AVERAGE;
+    }
     else if(rValue.indexOf("is-error") == 0)
     {
         eMode = SC_COND_ERROR;
diff --git a/sc/source/filter/xml/xmlexprt.cxx b/sc/source/filter/xml/xmlexprt.cxx
index 11ba4123d2f9..584219c2f57d 100644
--- a/sc/source/filter/xml/xmlexprt.cxx
+++ b/sc/source/filter/xml/xmlexprt.cxx
@@ -3969,6 +3969,18 @@ void ScXMLExport::ExportConditionalFormat(SCTAB nTab)
                                 aCond.append(pEntry->GetExpression(aPos, 0, 0, formula::FormulaGrammar::GRAM_ODFF));
                                 aCond.append(")");
                                 break;
+                            case SC_COND_ABOVE_AVERAGE:
+                                aCond.append("above-average");
+                                break;
+                            case SC_COND_BELOW_AVERAGE:
+                                aCond.append("below-average");
+                                break;
+                            case SC_COND_ABOVE_EQUAL_AVERAGE:
+                                aCond.append("above-equal-average");
+                                break;
+                            case SC_COND_BELOW_EQUAL_AVERAGE:
+                                aCond.append("below-equal-average");
+                                break;
                             case SC_COND_ERROR:
                                 aCond.append("is-error");
                                 break;
diff --git a/sc/source/ui/condformat/condformatdlgentry.cxx b/sc/source/ui/condformat/condformatdlgentry.cxx
index b6338de1061c..0d17859f42a1 100644
--- a/sc/source/ui/condformat/condformatdlgentry.cxx
+++ b/sc/source/ui/condformat/condformatdlgentry.cxx
@@ -243,26 +243,34 @@ ScConditionFrmtEntry::ScConditionFrmtEntry( Window* pParent, ScDocument* pDoc, c
                 maEdVal1.Hide();
                 maLbCondType.SelectEntryPos(15);
                 break;
-            case SC_COND_ERROR:
+            case SC_COND_ABOVE_EQUAL_AVERAGE:
                 maEdVal1.Hide();
                 maLbCondType.SelectEntryPos(16);
                 break;
-            case SC_COND_NOERROR:
+            case SC_COND_BELOW_EQUAL_AVERAGE:
                 maEdVal1.Hide();
                 maLbCondType.SelectEntryPos(17);
                 break;
-            case SC_COND_BEGINS_WITH:
+            case SC_COND_ERROR:
+                maEdVal1.Hide();
                 maLbCondType.SelectEntryPos(18);
                 break;
-            case SC_COND_ENDS_WITH:
+            case SC_COND_NOERROR:
+                maEdVal1.Hide();
                 maLbCondType.SelectEntryPos(19);
                 break;
-            case SC_COND_CONTAINS_TEXT:
+            case SC_COND_BEGINS_WITH:
                 maLbCondType.SelectEntryPos(20);
                 break;
-            case SC_COND_NOT_CONTAINS_TEXT:
+            case SC_COND_ENDS_WITH:
                 maLbCondType.SelectEntryPos(21);
                 break;
+            case SC_COND_CONTAINS_TEXT:
+                maLbCondType.SelectEntryPos(22);
+                break;
+            case SC_COND_NOT_CONTAINS_TEXT:
+                maLbCondType.SelectEntryPos(23);
+                break;
             case SC_COND_NONE:
                 break;
         }
@@ -356,21 +364,27 @@ ScFormatEntry* ScConditionFrmtEntry::createConditionEntry() const
             eMode = SC_COND_BELOW_AVERAGE;
             break;
         case 16:
-            eMode = SC_COND_ERROR;
+            eMode = SC_COND_ABOVE_EQUAL_AVERAGE;
             break;
         case 17:
-            eMode = SC_COND_NOERROR;
+            eMode = SC_COND_BELOW_EQUAL_AVERAGE;
             break;
         case 18:
-            eMode = SC_COND_BEGINS_WITH;
+            eMode = SC_COND_ERROR;
             break;
         case 19:
-            eMode = SC_COND_ENDS_WITH;
+            eMode = SC_COND_NOERROR;
             break;
         case 20:
-            eMode = SC_COND_CONTAINS_TEXT;
+            eMode = SC_COND_BEGINS_WITH;
             break;
         case 21:
+            eMode = SC_COND_ENDS_WITH;
+            break;
+        case 22:
+            eMode = SC_COND_CONTAINS_TEXT;
+            break;
+        case 23:
             eMode = SC_COND_NOT_CONTAINS_TEXT;
             break;
         default:
diff --git a/sc/source/ui/condformat/condformathelper.cxx b/sc/source/ui/condformat/condformathelper.cxx
index 0c6b77eeb008..6cbc8a2ec5d5 100644
--- a/sc/source/ui/condformat/condformathelper.cxx
+++ b/sc/source/ui/condformat/condformathelper.cxx
@@ -75,16 +75,20 @@ rtl::OUString getExpression(sal_Int32 nIndex)
         case 16:
             return ScGlobal::GetRscString(STR_COND_BELOW_AVERAGE);
         case 17:
-            return ScGlobal::GetRscString(STR_COND_ERROR);
+            return ScGlobal::GetRscString(STR_COND_ABOVE_EQUAL_AVERAGE);
         case 18:
-            return ScGlobal::GetRscString(STR_COND_NOERROR);
+            return ScGlobal::GetRscString(STR_COND_BELOW_EQUAL_AVERAGE);
         case 19:
-            return ScGlobal::GetRscString(STR_COND_BEGINS_WITH);
+            return ScGlobal::GetRscString(STR_COND_ERROR);
         case 20:
-            return ScGlobal::GetRscString(STR_COND_ENDS_WITH);
+            return ScGlobal::GetRscString(STR_COND_NOERROR);
         case 21:
-            return ScGlobal::GetRscString(STR_COND_CONTAINS);
+            return ScGlobal::GetRscString(STR_COND_BEGINS_WITH);
         case 22:
+            return ScGlobal::GetRscString(STR_COND_ENDS_WITH);
+        case 23:
+            return ScGlobal::GetRscString(STR_COND_CONTAINS);
+        case 24:
             return ScGlobal::GetRscString(STR_COND_NOT_CONTAINS);
     }
     return rtl::OUString();
diff --git a/sc/source/ui/src/condformatdlg.src b/sc/source/ui/src/condformatdlg.src
index 53df59c280b7..8e3132b3acd7 100644
--- a/sc/source/ui/src/condformatdlg.src
+++ b/sc/source/ui/src/condformatdlg.src
@@ -150,6 +150,8 @@ Control RID_COND_ENTRY
             "bottom 10 percent";
             "above average";
             "below average";
+            "above or equal average";
+            "below or equal average";
             "Error";
             "No Error";
             "Begins with";
diff --git a/sc/source/ui/src/globstr.src b/sc/source/ui/src/globstr.src
index 1c5f2244bae5..06d1170c0b5d 100644
--- a/sc/source/ui/src/globstr.src
+++ b/sc/source/ui/src/globstr.src
@@ -1927,6 +1927,14 @@ Resource RID_GLOBSTR
     {
         Text [ en-US ] = "Below Average";
     };
+    String STR_COND_ABOVE_EQUAL_AVERAGE
+    {
+        Text [ en-US ] = "Above or equal Average";
+    };
+    String STR_COND_BELOW_EQUAL_AVERAGE
+    {
+        Text [ en-US ] = "Below or equal Average";
+    };
     String STR_COND_ERROR
     {
         Text [ en-US ] = "an Error code";