tdf#61313: Allow customization of conditional formatting operators in icon sets

- Replaced the fixed >= operator with a dropdown to select different operators.
- Applied the last valid condition for each cell.
- Fixed an issue where importing an Excel spreadsheet with conditional formatting did not preserve conditions correctly.

Change-Id: Id31522e825e51916db4870fbfb19519745e54660
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/182760
Tested-by: Jenkins
Reviewed-by: Andreas Heinisch <andreas.heinisch@yahoo.de>

11 files changed, 100 insertions, 71 deletions

diff --git a/sc/inc/colorscale.hxx b/sc/inc/colorscale.hxx
index 373c06eda05c..55b1f10cb8a8 100644
--- a/sc/inc/colorscale.hxx
+++ b/sc/inc/colorscale.hxx
@@ -48,12 +48,14 @@ private:
     ScConditionalFormat* mpFormat;
     Color maColor;
     ScColorScaleEntryType meType;
-    bool mbGreaterThanOrEqual;
+    ScConditionMode meMode;
 
     void setListener();
 
 public:
-    SC_DLLPUBLIC ScColorScaleEntry(double nVal, const Color& rCol, ScColorScaleEntryType eType = COLORSCALE_VALUE);
+    SC_DLLPUBLIC ScColorScaleEntry(double nVal, const Color& rCol,
+                                   ScColorScaleEntryType eType = COLORSCALE_VALUE,
+                                   ScConditionMode eMode = ScConditionMode::EqGreater);
     SC_DLLPUBLIC ScColorScaleEntry();
     ScColorScaleEntry(const ScColorScaleEntry& rEntry);
     ScColorScaleEntry(ScDocument* pDoc, const ScColorScaleEntry& rEntry);
@@ -63,8 +65,8 @@ public:
     void SetColor(const Color&);
     SC_DLLPUBLIC double GetValue() const;
     SC_DLLPUBLIC void SetValue(double nValue);
-    SC_DLLPUBLIC bool GetGreaterThanOrEqual() const;
-    SC_DLLPUBLIC void SetGreaterThanOrEqual(bool bGreaterThanOrEqual);
+    SC_DLLPUBLIC ScConditionMode GetMode() const;
+    SC_DLLPUBLIC void SetMode(ScConditionMode eMode);
     SC_DLLPUBLIC void SetFormula(const OUString& rFormula, ScDocument& rDoc, const ScAddress& rAddr,
             formula::FormulaGrammar::Grammar eGrammar = formula::FormulaGrammar::GRAM_DEFAULT);
 
diff --git a/sc/inc/fillinfo.hxx b/sc/inc/fillinfo.hxx
index caecd6a7072b..e9c713373724 100644
--- a/sc/inc/fillinfo.hxx
+++ b/sc/inc/fillinfo.hxx
@@ -92,6 +92,7 @@ struct ScIconSetInfo
 {
     sal_Int32 nIconIndex;
     ScIconSetType eIconSetType;
+    ScConditionMode eConditionMode;
     tools::Long mnHeight = 0;
     bool mbShowValue;
 };
diff --git a/sc/qa/unit/ucalc_condformat.cxx b/sc/qa/unit/ucalc_condformat.cxx
index 56ebcd01e3eb..7b077ba5d1ed 100644
--- a/sc/qa/unit/ucalc_condformat.cxx
+++ b/sc/qa/unit/ucalc_condformat.cxx
@@ -841,9 +841,9 @@ CPPUNIT_TEST_FIXTURE(TestCondformat, testIconSet)
 
     ScIconSetFormat* pEntry = new ScIconSetFormat(m_pDoc);
     ScIconSetFormatData* pData = new ScIconSetFormatData;
-    pData->m_Entries.emplace_back(new ScColorScaleEntry(0, COL_BLUE));
-    pData->m_Entries.emplace_back(new ScColorScaleEntry(1, COL_GREEN));
-    pData->m_Entries.emplace_back(new ScColorScaleEntry(2, COL_RED));
+    pData->m_Entries.emplace_back(new ScColorScaleEntry(0, COL_BLUE, COLORSCALE_VALUE, ScConditionMode::EqLess));
+    pData->m_Entries.emplace_back(new ScColorScaleEntry(1, COL_GREEN, COLORSCALE_VALUE, ScConditionMode::EqGreater));
+    pData->m_Entries.emplace_back(new ScColorScaleEntry(2, COL_RED, COLORSCALE_VALUE, ScConditionMode::Equal));
     pEntry->SetIconSetData(pData);
 
     m_pDoc->AddCondFormatData(pFormat->GetRange(), 0, 1);
@@ -856,7 +856,7 @@ CPPUNIT_TEST_FIXTURE(TestCondformat, testIconSet)
         { 0.0, 0 },
         { 1.0, 1 },
         { 2.0, 2 },
-        { 3.0, 2 }
+        { 3.0, 1 }
     };
     for(size_t i = 0; i < SAL_N_ELEMENTS(aTests); ++i)
     {
diff --git a/sc/source/core/data/colorscale.cxx b/sc/source/core/data/colorscale.cxx
index 9d7df4506c4f..786f1d242a4c 100644
--- a/sc/source/core/data/colorscale.cxx
+++ b/sc/source/core/data/colorscale.cxx
@@ -155,16 +155,17 @@ ScColorScaleEntry::ScColorScaleEntry():
     mnVal(0),
     mpFormat(nullptr),
     meType(COLORSCALE_VALUE),
-    mbGreaterThanOrEqual(true)
+    meMode(ScConditionMode::EqGreater)
 {
 }
 
-ScColorScaleEntry::ScColorScaleEntry(double nVal, const Color& rCol, ScColorScaleEntryType eType):
+ScColorScaleEntry::ScColorScaleEntry(double nVal, const Color& rCol, ScColorScaleEntryType eType,
+                                        ScConditionMode eMode):
     mnVal(nVal),
     mpFormat(nullptr),
     maColor(rCol),
     meType(eType),
-    mbGreaterThanOrEqual(true)
+    meMode(eMode)
 {
 }
 
@@ -173,7 +174,7 @@ ScColorScaleEntry::ScColorScaleEntry(const ScColorScaleEntry& rEntry):
     mpFormat(rEntry.mpFormat),
     maColor(rEntry.maColor),
     meType(rEntry.meType),
-    mbGreaterThanOrEqual(true)
+    meMode(rEntry.meMode)
 {
     setListener();
     if(rEntry.mpCell)
@@ -189,7 +190,7 @@ ScColorScaleEntry::ScColorScaleEntry(ScDocument* pDoc, const ScColorScaleEntry&
     mpFormat(rEntry.mpFormat),
     maColor(rEntry.maColor),
     meType(rEntry.meType),
-    mbGreaterThanOrEqual(true)
+    meMode(rEntry.meMode)
 {
     setListener();
     if(rEntry.mpCell)
@@ -251,14 +252,14 @@ double ScColorScaleEntry::GetValue() const
     return mnVal;
 }
 
-bool ScColorScaleEntry::GetGreaterThanOrEqual() const
+ScConditionMode ScColorScaleEntry::GetMode() const
 {
-    return mbGreaterThanOrEqual;
+    return meMode;
 }
 
-void ScColorScaleEntry::SetGreaterThanOrEqual(bool bGreaterThanOrEqual)
+void ScColorScaleEntry::SetMode(ScConditionMode eMode)
 {
-    mbGreaterThanOrEqual = bGreaterThanOrEqual;
+    meMode = eMode;
 }
 
 void ScColorScaleEntry::SetValue(double nValue)
@@ -1080,6 +1081,28 @@ void ScDataBarFormat::EnsureSize()
     }
 }
 
+static bool Compare(double nVal1, double nVal2, const ScIconSetFormat::const_iterator& itr)
+{
+    switch ((*itr)->GetMode())
+    {
+        case ScConditionMode::Equal:
+            return nVal1 == nVal2;
+        case ScConditionMode::Less:
+            return nVal1 < nVal2;
+        case ScConditionMode::Greater:
+            return nVal1 > nVal2;
+        case ScConditionMode::EqLess:
+            return nVal1 <= nVal2;
+        case ScConditionMode::EqGreater:
+            return nVal1 >= nVal2;
+        case ScConditionMode::NotEqual:
+            return nVal1 != nVal2;
+        default:
+            break;
+    }
+    return false;
+}
+
 ScIconSetFormatData::ScIconSetFormatData(ScIconSetFormatData const& rOther)
     : eIconSetType(rOther.eIconSetType)
     , mbShowValue(rOther.mbShowValue)
@@ -1145,37 +1168,30 @@ std::unique_ptr<ScIconSetInfo> ScIconSetFormat::GetIconSetInfo(const ScAddress&
     // now we have for sure a value
     double nVal = rCell.getValue();
 
-    if (mpFormatData->m_Entries.size() < 2)
+    if (mpFormatData->m_Entries.size() < 3)
         return nullptr;
 
     double nMin = GetMinValue();
     double nMax = GetMaxValue();
 
-    sal_Int32 nIndex = 0;
+    sal_Int32 nIndex = -1;
     const_iterator itr = begin();
-    ++itr;
-    double nValMax = CalcValue(nMin, nMax, itr);
-
-    ++itr;
-    bool bGreaterThanOrEqual = true;
-    while(itr != end() && nVal >= nValMax)
+    ScConditionMode eMode = ScConditionMode::EqGreater;
+    double nValRef = 0;
+    int i = 0;
+    while(itr != end())
     {
-        bGreaterThanOrEqual = (*itr)->GetGreaterThanOrEqual();
-        ++nIndex;
-        nValMax = CalcValue(nMin, nMax, itr);
-        ++itr;
-    }
-
-    if (bGreaterThanOrEqual)
-    {
-        if(nVal >= nValMax)
-            ++nIndex;
-    }
-    else
-    {
-        if(nVal > nValMax)
-            ++nIndex;
+        nValRef = CalcValue(nMin, nMax, itr);
+        if (Compare(nVal, nValRef, itr))
+        {
+            nIndex = i;
+            eMode = (*itr)->GetMode();
+        }
+        itr++;
+        i++;
     }
+    if (nIndex == -1)
+        return nullptr;
 
     std::unique_ptr<ScIconSetInfo> pInfo(new ScIconSetInfo);
 
@@ -1208,6 +1224,7 @@ std::unique_ptr<ScIconSetInfo> ScIconSetFormat::GetIconSetInfo(const ScAddress&
     }
 
     pInfo->mbShowValue = mpFormatData->mbShowValue;
+    pInfo->eConditionMode = eMode;
     return pInfo;
 }
 
diff --git a/sc/source/filter/excel/xecontent.cxx b/sc/source/filter/excel/xecontent.cxx
index 263c55ee5f5d..fdff19415a48 100644
--- a/sc/source/filter/excel/xecontent.cxx
+++ b/sc/source/filter/excel/xecontent.cxx
@@ -1254,7 +1254,7 @@ void XclExpCfvo::SaveXml( XclExpXmlStream& rStrm )
     rWorksheet->startElement( XML_cfvo,
             XML_type, getColorScaleType(mrEntry, mbFirst),
             XML_val, aValue,
-            XML_gte, sax_fastparser::UseIf("0", !mrEntry.GetGreaterThanOrEqual()));
+            XML_gte, sax_fastparser::UseIf("0", mrEntry.GetMode() != ScConditionMode::EqGreater));
 
     rWorksheet->endElement( XML_cfvo );
 }
diff --git a/sc/source/filter/inc/condformatbuffer.hxx b/sc/source/filter/inc/condformatbuffer.hxx
index 47f0080c2464..a1ae1989d3a6 100644
--- a/sc/source/filter/inc/condformatbuffer.hxx
+++ b/sc/source/filter/inc/condformatbuffer.hxx
@@ -80,7 +80,7 @@ struct ColorScaleRuleModelEntry
     bool mbPercentile;
     bool mbNum;
     OUString maFormula;
-    bool mbGreaterThanOrEqual;
+    ScConditionMode meMode;
 
     ColorScaleRuleModelEntry():
         maColor(),
@@ -90,7 +90,7 @@ struct ColorScaleRuleModelEntry
         mbPercent(false),
         mbPercentile(false),
         mbNum(false),
-        mbGreaterThanOrEqual(true) {}
+        meMode(ScConditionMode::EqGreater) {}
 
     bool operator==(const ColorScaleRuleModelEntry &) const = default;
 };
diff --git a/sc/source/filter/oox/condformatbuffer.cxx b/sc/source/filter/oox/condformatbuffer.cxx
index f7ee3a7209a2..73caf1f648e5 100644
--- a/sc/source/filter/oox/condformatbuffer.cxx
+++ b/sc/source/filter/oox/condformatbuffer.cxx
@@ -120,7 +120,7 @@ void SetCfvoData( ColorScaleRuleModelEntry* pEntry, const AttributeList& rAttrib
     if (!aGreaterThanOrEqual.isEmpty())
     {
         if (!aGreaterThanOrEqual.toBoolean())
-            pEntry->mbGreaterThanOrEqual = false;
+            pEntry->meMode = ScConditionMode::Greater;
     }
 
     if (aVal != "\"\"")
@@ -331,7 +331,7 @@ ScColorScaleEntry* ConvertToModel( const ColorScaleRuleModelEntry& rEntry, ScDoc
             pEntry->SetFormula(rEntry.maFormula, *pDoc, rAddr, formula::FormulaGrammar::GRAM_ENGLISH_XL_A1);
         }
 
-        pEntry->SetGreaterThanOrEqual(rEntry.mbGreaterThanOrEqual);
+        pEntry->SetMode(rEntry.meMode);
 
         return pEntry;
 }
diff --git a/sc/source/filter/xml/xmlcondformat.cxx b/sc/source/filter/xml/xmlcondformat.cxx
index 4e2164ae00f8..c0cf2ee5b603 100644
--- a/sc/source/filter/xml/xmlcondformat.cxx
+++ b/sc/source/filter/xml/xmlcondformat.cxx
@@ -909,7 +909,7 @@ ScXMLFormattingEntryContext::ScXMLFormattingEntryContext( ScXMLImport& rImport,
 {
     OUString sVal;
     OUString sType;
-    bool bGreaterThanOrEqual = true;
+    ScConditionMode eMode = ScConditionMode::EqGreater;
 
     if ( rAttrList.is() )
     {
@@ -924,7 +924,7 @@ ScXMLFormattingEntryContext::ScXMLFormattingEntryContext( ScXMLImport& rImport,
                     sVal = aIter.toString();
                 break;
                 case XML_ELEMENT( CALC_EXT, XML_GREATER_EQUAL ):
-                    bGreaterThanOrEqual = aIter.toBoolean();
+                    eMode = aIter.toBoolean() ? eMode : ScConditionMode::Greater;
                 break;
                 default:
                     break;
@@ -938,7 +938,7 @@ ScXMLFormattingEntryContext::ScXMLFormattingEntryContext( ScXMLImport& rImport,
 
     pColorScaleEntry = new ScColorScaleEntry(nVal, Color());
     setColorEntryType(sType, pColorScaleEntry, sVal, GetScImport());
-    pColorScaleEntry->SetGreaterThanOrEqual(bGreaterThanOrEqual);
+    pColorScaleEntry->SetMode(eMode);
 }
 
 namespace {
diff --git a/sc/source/filter/xml/xmlexprt.cxx b/sc/source/filter/xml/xmlexprt.cxx
index aa139c4f6d06..33525f249051 100644
--- a/sc/source/filter/xml/xmlexprt.cxx
+++ b/sc/source/filter/xml/xmlexprt.cxx
@@ -4965,7 +4965,7 @@ void ScXMLExport::ExportConditionalFormat(ScDocument& rDoc, SCTAB nTab)
                     else
                         AddAttribute(XML_NAMESPACE_CALC_EXT, XML_VALUE, OUString::number(it->GetValue()));
 
-                    if (!it->GetGreaterThanOrEqual())
+                    if (it->GetMode() != ScConditionMode::EqGreater)
                         AddAttribute(XML_NAMESPACE_CALC_EXT, XML_GREATER_EQUAL, OUString::boolean(false));
 
                     AddAttribute(XML_NAMESPACE_CALC_EXT, XML_TYPE, getCondFormatEntryType(*it));
diff --git a/sc/source/ui/condformat/condformatdlgentry.cxx b/sc/source/ui/condformat/condformatdlgentry.cxx
index 1be4d4151d44..52c59da89c3d 100644
--- a/sc/source/ui/condformat/condformatdlgentry.cxx
+++ b/sc/source/ui/condformat/condformatdlgentry.cxx
@@ -1337,9 +1337,9 @@ protected:
 private:
     std::unique_ptr<weld::Container> mxGrid;
     std::unique_ptr<weld::Image> mxImgIcon;
-    std::unique_ptr<weld::Label> mxFtEntry;
     std::unique_ptr<weld::Entry> mxEdEntry;
     std::unique_ptr<weld::ComboBox> mxLbEntryType;
+    std::unique_ptr<weld::ComboBox> mxConditionMode;
     weld::Grid* mpParentGrid;
 
 public:
@@ -1355,17 +1355,15 @@ public:
     }
 
     ScColorScaleEntry* CreateEntry(ScDocument& rDoc, const ScAddress& rPos) const;
-
-    void SetFirstEntry();
 };
 
 ScIconSetFrmtDataEntry::ScIconSetFrmtDataEntry(weld::Grid* pParent, ScIconSetType eType, const ScDocument& rDoc, sal_Int32 i, const ScColorScaleEntry* pEntry)
     : mxBuilder(Application::CreateBuilder(pParent, u"modules/scalc/ui/conditionaliconset.ui"_ustr))
     , mxGrid(mxBuilder->weld_container(u"ConditionalIconSet"_ustr))
     , mxImgIcon(mxBuilder->weld_image(u"icon"_ustr))
-    , mxFtEntry(mxBuilder->weld_label("label"))
     , mxEdEntry(mxBuilder->weld_entry(u"entry"_ustr))
     , mxLbEntryType(mxBuilder->weld_combo_box(u"listbox"_ustr))
+    , mxConditionMode(mxBuilder->weld_combo_box("conditionMode"))
     , mpParentGrid(pParent)
 {
     mxEdEntry->set_buildable_name(mxEdEntry->get_buildable_name() + OUString::number(i));
@@ -1374,9 +1372,21 @@ ScIconSetFrmtDataEntry::ScIconSetFrmtDataEntry(weld::Grid* pParent, ScIconSetTyp
     mxImgIcon->set_from_icon_name(ScIconSetFormat::getIconName(eType, i));
     if(pEntry)
     {
-        // tdf#162948: Use ">" instead of ">=". Add some spaces to keep the alignment
-        if (!pEntry->GetGreaterThanOrEqual())
-            mxFtEntry->set_label(" >   ");
+        switch (pEntry->GetMode())
+        {
+            case ScConditionMode::Equal:
+            case ScConditionMode::Less:
+            case ScConditionMode::Greater:
+            case ScConditionMode::EqLess:
+            case ScConditionMode::EqGreater:
+            case ScConditionMode::NotEqual:
+                mxConditionMode->set_active(static_cast<int>(pEntry->GetMode()));
+                break;
+            default:
+                assert(false
+                       && "ScIconSetFrmtDataEntry::ScIconSetFrmtDataEntry: Invalid condition mode");
+        }
+
         switch(pEntry->GetType())
         {
             case COLORSCALE_VALUE:
@@ -1402,6 +1412,7 @@ ScIconSetFrmtDataEntry::ScIconSetFrmtDataEntry(weld::Grid* pParent, ScIconSetTyp
     else
     {
         mxLbEntryType->set_active(1);
+        mxConditionMode->set_active(0);
     }
 }
 
@@ -1412,7 +1423,8 @@ ScIconSetFrmtDataEntry::~ScIconSetFrmtDataEntry()
 
 ScColorScaleEntry* ScIconSetFrmtDataEntry::CreateEntry(ScDocument& rDoc, const ScAddress& rPos) const
 {
-    sal_Int32 nPos = mxLbEntryType->get_active();
+    sal_Int32 nTypePos = mxLbEntryType->get_active();
+    sal_Int32 nModePos = mxConditionMode->get_active();
     OUString aText = mxEdEntry->get_text();
     ScColorScaleEntry* pEntry = new ScColorScaleEntry();
 
@@ -1422,7 +1434,7 @@ ScColorScaleEntry* ScIconSetFrmtDataEntry::CreateEntry(ScDocument& rDoc, const S
     (void)pNumberFormatter->IsNumberFormat(aText, nIndex, nVal);
     pEntry->SetValue(nVal);
 
-    switch(nPos)
+    switch(nTypePos)
     {
         case 0:
             pEntry->SetType(COLORSCALE_VALUE);
@@ -1441,18 +1453,10 @@ ScColorScaleEntry* ScIconSetFrmtDataEntry::CreateEntry(ScDocument& rDoc, const S
             assert(false);
     }
 
+    pEntry->SetMode(static_cast<ScConditionMode>(nModePos));
     return pEntry;
 }
 
-void ScIconSetFrmtDataEntry::SetFirstEntry()
-{
-    mxEdEntry->hide();
-    mxLbEntryType->hide();
-    mxFtEntry->hide();
-    mxEdEntry->set_text("0");
-    mxLbEntryType->set_active(1);
-}
-
 ScIconSetFrmtEntry::ScIconSetFrmtEntry(ScCondFormatList* pParent, ScDocument& rDoc, const ScAddress& rPos, const ScIconSetFormat* pFormat)
     : ScCondFrmtEntry(pParent, rDoc, rPos)
     , mxLbColorFormat(mxBuilder->weld_combo_box(u"colorformat"_ustr))
@@ -1479,7 +1483,6 @@ ScIconSetFrmtEntry::ScIconSetFrmtEntry(ScCondFormatList* pParent, ScDocument& rD
                 mxIconParent.get(), eType, rDoc, i, pIconSetFormatData->m_Entries[i].get()));
             maEntries[i]->set_grid_top_attach(i);
         }
-        maEntries[0]->SetFirstEntry();
     }
     else
         IconSetTypeHdl(*mxLbIconSetType);
@@ -1513,7 +1516,6 @@ IMPL_LINK_NOARG( ScIconSetFrmtEntry, IconSetTypeHdl, weld::ComboBox&, void )
         maEntries[i]->set_grid_top_attach(i);
         maEntries[i]->Show();
     }
-    maEntries[0]->SetFirstEntry();
 }
 
 OUString ScIconSetFrmtEntry::GetExpressionString()
diff --git a/sc/uiconfig/scalc/ui/conditionaliconset.ui b/sc/uiconfig/scalc/ui/conditionaliconset.ui
index abca462dbd7a..57cf2d4992f5 100644
--- a/sc/uiconfig/scalc/ui/conditionaliconset.ui
+++ b/sc/uiconfig/scalc/ui/conditionaliconset.ui
@@ -16,10 +16,17 @@
         <property name="row-spacing">6</property>
         <property name="column-spacing">12</property>
         <child>
-          <object class="GtkLabel" id="label">
+          <object class="GtkComboBoxText" id="conditionMode">
             <property name="visible">True</property>
             <property name="can-focus">False</property>
-            <property name="label" translatable="yes" context="conditionaliconset|label"> &gt;= </property>
+            <items>
+              <item translatable="no" context="conditionaliconset|conditionMode"> = </item>
+              <item translatable="no" context="conditionaliconset|conditionMode"> &lt; </item>
+              <item translatable="no" context="conditionaliconset|conditionMode"> &gt; </item>
+              <item translatable="no" context="conditionaliconset|conditionMode"> &lt;= </item>
+              <item translatable="no" context="conditionaliconset|conditionMode"> &gt;= </item>
+              <item translatable="no" context="conditionaliconset|conditionMode"> &lt;&gt; </item>
+            </items>
           </object>
           <packing>
             <property name="left-attach">1</property>