tdf#120749 XLSX: fix "begins/ends with" conditional

formatting when using "Given text" type and cell reference.

Follow-up of commit 583e2bfba2d72ac8afe7261c23f380daf5486889
(tdf#139021 XLSX export: fix "contains" conditional formatting).

Co-authored-by: Attila Szűcs (NISZ)

Change-Id: Idf5d46469765454a11311bd6ec1d85c85b89e26f
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/108058
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>
(cherry picked from commit 879ea123514ce6808212dfbef7e1d226e9d5fd9b)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/108592
Tested-by: Jenkins
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>

4 files changed, 71 insertions, 1 deletions

diff --git a/sc/qa/unit/data/xlsx/tdf120749.xlsx b/sc/qa/unit/data/xlsx/tdf120749.xlsx
new file mode 100644
index 000000000000..de601234d3be
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/tdf120749.xlsx
diff --git a/sc/qa/unit/subsequent_filters-test.cxx b/sc/qa/unit/subsequent_filters-test.cxx
index 28db571e66de..ce65fc6684cf 100644
--- a/sc/qa/unit/subsequent_filters-test.cxx
+++ b/sc/qa/unit/subsequent_filters-test.cxx
@@ -106,6 +106,7 @@ public:
     virtual void tearDown() override;
 
     //ods, xls, xlsx filter tests
+    void testCondFormatBeginsAndEndsWithXLSX();
     void testExtCondFormatXLSX();
     void testUpdateCircleInMergedCellODS();
     void testDeleteCircleInMergedCellODS();
@@ -297,6 +298,7 @@ public:
     void testDeleteCirclesInRowAndCol();
 
     CPPUNIT_TEST_SUITE(ScFiltersTest);
+    CPPUNIT_TEST(testCondFormatBeginsAndEndsWithXLSX);
     CPPUNIT_TEST(testExtCondFormatXLSX);
     CPPUNIT_TEST(testUpdateCircleInMergedCellODS);
     CPPUNIT_TEST(testDeleteCircleInMergedCellODS);
@@ -532,6 +534,42 @@ void testRangeNameImpl(const ScDocument& rDoc)
 
 }
 
+void ScFiltersTest::testCondFormatBeginsAndEndsWithXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc("tdf120749.", FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load tdf120749.xlsx", xDocSh.is());
+
+    ScDocument& rDoc = xDocSh->GetDocument();
+
+    // begins with and ends with conditions
+    ScConditionalFormat* pFormatA1 = rDoc.GetCondFormat(0, 0, 0);
+    CPPUNIT_ASSERT(pFormatA1);
+    ScConditionalFormat* pFormatA2 = rDoc.GetCondFormat(0, 1, 0);
+    CPPUNIT_ASSERT(pFormatA2);
+    ScConditionalFormat* pFormatA3 = rDoc.GetCondFormat(0, 2, 0);
+    CPPUNIT_ASSERT(pFormatA3);
+    ScConditionalFormat* pFormatA4 = rDoc.GetCondFormat(0, 3, 0);
+    CPPUNIT_ASSERT(pFormatA4);
+
+    ScRefCellValue aCellA1(rDoc, ScAddress(0, 0, 0));
+    OUString aCellStyleA1 = pFormatA1->GetCellStyle(aCellA1, ScAddress(0, 0, 0));
+    CPPUNIT_ASSERT(!aCellStyleA1.isEmpty());
+
+    ScRefCellValue aCellA2(rDoc, ScAddress(0, 1, 0));
+    OUString aCellStyleA2 = pFormatA2->GetCellStyle(aCellA2, ScAddress(0, 1, 0));
+    CPPUNIT_ASSERT(!aCellStyleA2.isEmpty());
+
+    ScRefCellValue aCellA3(rDoc, ScAddress(0, 2, 0));
+    OUString aCellStyleA3 = pFormatA3->GetCellStyle(aCellA3, ScAddress(0, 2, 0));
+    CPPUNIT_ASSERT(!aCellStyleA3.isEmpty());
+
+    ScRefCellValue aCellA4(rDoc, ScAddress(0, 3, 0));
+    OUString aCellStyleA4 = pFormatA4->GetCellStyle(aCellA4, ScAddress(0, 3, 0));
+    CPPUNIT_ASSERT(!aCellStyleA4.isEmpty());
+
+    xDocSh->DoClose();
+}
+
 void ScFiltersTest::testExtCondFormatXLSX()
 {
     ScDocShellRef xDocSh = loadDoc("tdf122102.", FORMAT_XLSX);
diff --git a/sc/source/filter/inc/extlstcontext.hxx b/sc/source/filter/inc/extlstcontext.hxx
index 7793437a6c55..0f85d5e6f2d4 100644
--- a/sc/source/filter/inc/extlstcontext.hxx
+++ b/sc/source/filter/inc/extlstcontext.hxx
@@ -51,6 +51,7 @@ public:
     virtual void onEndElement() override;
 
 private:
+    sal_Int32 nFormulaCount;
     OUString aChars; // Characters of between xml elements.
     OUString rStyle; // Style of the corresponding condition
     sal_Int32 nPriority; // Priority of last cfRule element.
diff --git a/sc/source/filter/oox/extlstcontext.cxx b/sc/source/filter/oox/extlstcontext.cxx
index b413e9e58029..53cd7b385100 100644
--- a/sc/source/filter/oox/extlstcontext.cxx
+++ b/sc/source/filter/oox/extlstcontext.cxx
@@ -80,8 +80,26 @@ void ExtCfRuleContext::onStartElement( const AttributeList& rAttribs )
     }
 }
 
+namespace {
+    bool IsSpecificTextCondMode(ScConditionMode eMode)
+    {
+        switch (eMode)
+        {
+        case ScConditionMode::BeginsWith:
+        case ScConditionMode::EndsWith:
+        case ScConditionMode::ContainsText:
+        case ScConditionMode::NotContainsText:
+            return true;
+        default:
+            break;
+        }
+        return false;
+    }
+}
+
 ExtConditionalFormattingContext::ExtConditionalFormattingContext(WorksheetContextBase& rFragment)
     : WorksheetContextBase(rFragment)
+    , nFormulaCount(0)
     , nPriority(-1)
     , eOperator(ScConditionMode::NONE)
     , isPreviousElementF(false)
@@ -145,6 +163,16 @@ ContextHandlerRef ExtConditionalFormattingContext::onCreateContext(sal_Int32 nEl
             eOperator = ScConditionMode::NotContainsText;
             return this;
         }
+        else if(aType == "beginsWith")
+        {
+            eOperator = ScConditionMode::BeginsWith;
+            return this;
+        }
+        else if(aType == "endsWith")
+        {
+            eOperator = ScConditionMode::EndsWith;
+            return this;
+        }
         else
         {
             SAL_WARN("sc", "unhandled XLS14_TOKEN(cfRule) with type: " << aType);
@@ -156,6 +184,8 @@ ContextHandlerRef ExtConditionalFormattingContext::onCreateContext(sal_Int32 nEl
     }
     else if (nElement == XM_TOKEN( sqref ) || nElement == XM_TOKEN( f ))
     {
+        if(nElement == XM_TOKEN( f ))
+           nFormulaCount++;
         return this;
     }
 
@@ -191,13 +221,14 @@ void ExtConditionalFormattingContext::onEndElement()
     {
         case XM_TOKEN(f):
         {
-            if(!aChars.startsWith("ISERROR(SEARCH(") && !aChars.startsWith("NOT(ISERROR(SEARCH("))
+            if(!IsSpecificTextCondMode(eOperator) || nFormulaCount == 2)
                rFormulas.push_back(aChars);
         }
         break;
         case XLS14_TOKEN( cfRule ):
         {
             getStyles().getExtDxfs().forEachMem( &Dxf::finalizeImport );
+            nFormulaCount = 0;
         }
         break;
         case XM_TOKEN(sqref):