tdf#139021 XLSX export: fix "contains" conditional formatting

when using "Given text" type with cell reference instead of
fixed string.

Note: fix also "notContainsText", and prepare the fix for
"beginsWith", "endsWith" and "expression" type conditions.

Follow-up of commit 0101975f8eac650bb87c4af81157cb33a6309e0e
(tdf#122102 XLSX import: fix "contains" conditional formatting).

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

Change-Id: I46d80946f2b6cfaa2a9fe2438fae20e8aa9d50f4
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/108035
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>
(cherry picked from commit 583e2bfba2d72ac8afe7261c23f380daf5486889)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/108695
Tested-by: Jenkins
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>

3 files changed, 120 insertions, 5 deletions

diff --git a/sc/qa/unit/data/xlsx/tdf139021.xlsx b/sc/qa/unit/data/xlsx/tdf139021.xlsx
new file mode 100644
index 000000000000..f420b7c150d2
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/tdf139021.xlsx
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 6f3e4d3d36e5..d354de27a7c4 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -89,6 +89,7 @@ public:
     virtual void tearDown() override;
 
     void test();
+    void testExtCondFormatXLSX();
     void testTdf90104();
     void testTdf111876();
     void testPasswordExportODS();
@@ -278,6 +279,7 @@ public:
 
     CPPUNIT_TEST_SUITE(ScExportTest);
     CPPUNIT_TEST(test);
+    CPPUNIT_TEST(testExtCondFormatXLSX);
     CPPUNIT_TEST(testTdf90104);
     CPPUNIT_TEST(testTdf111876);
     CPPUNIT_TEST(testPasswordExportODS);
@@ -515,6 +517,40 @@ void ScExportTest::test()
     xDocSh->DoClose();
 }
 
+void ScExportTest::testExtCondFormatXLSX()
+{
+    ScDocShellRef xShell = loadDoc("tdf139021.", FORMAT_XLSX);
+    CPPUNIT_ASSERT(xShell.is());
+
+    ScDocShellRef xDocSh = saveAndReload(&(*xShell), FORMAT_XLSX);
+    CPPUNIT_ASSERT(xDocSh.is());
+
+    std::shared_ptr<utl::TempFile> pXPathFile = ScBootstrapFixture::exportTo(&(*xDocSh), FORMAT_XLSX);
+    xmlDocUniquePtr pDoc = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/worksheets/sheet1.xml");
+    CPPUNIT_ASSERT(pDoc);
+
+    assertXPath(pDoc,
+                "/x:worksheet/x:extLst/x:ext/x14:conditionalFormattings/x14:conditionalFormatting[1]/"
+                "x14:cfRule", "type", "containsText");
+    assertXPathContent(pDoc,
+                "/x:worksheet/x:extLst/x:ext/x14:conditionalFormattings/x14:conditionalFormatting[1]/"
+                "x14:cfRule/xm:f[1]", "NOT(ISERROR(SEARCH($B$1,A1)))");
+    assertXPathContent(pDoc,
+                "/x:worksheet/x:extLst/x:ext/x14:conditionalFormattings/x14:conditionalFormatting[1]/"
+                "x14:cfRule/xm:f[2]", "$B$1");
+    assertXPath(pDoc,
+                "/x:worksheet/x:extLst/x:ext/x14:conditionalFormattings/x14:conditionalFormatting[2]/"
+                "x14:cfRule", "type", "notContainsText");
+    assertXPathContent(pDoc,
+                "/x:worksheet/x:extLst/x:ext/x14:conditionalFormattings/x14:conditionalFormatting[2]/"
+                "x14:cfRule/xm:f[1]", "ISERROR(SEARCH($B$2,A2))");
+    assertXPathContent(pDoc,
+                "/x:worksheet/x:extLst/x:ext/x14:conditionalFormattings/x14:conditionalFormatting[2]/"
+                "x14:cfRule/xm:f[2]", "$B$2");
+
+    xDocSh->DoClose();
+}
+
 void ScExportTest::testTdf90104()
 {
     ScDocShellRef xShell = loadDoc("tdf90104.", FORMAT_XLSX);
diff --git a/sc/source/filter/excel/xeextlst.cxx b/sc/source/filter/excel/xeextlst.cxx
index cca940350173..a1732197501e 100644
--- a/sc/source/filter/excel/xeextlst.cxx
+++ b/sc/source/filter/excel/xeextlst.cxx
@@ -149,6 +149,48 @@ XclExpExtCF::XclExpExtCF( const XclExpRoot& rRoot, const ScCondFormatEntry& rFor
 {
 }
 
+namespace {
+
+bool RequiresFixedFormula(ScConditionMode eMode)
+{
+    switch (eMode)
+    {
+    case ScConditionMode::BeginsWith:
+    case ScConditionMode::EndsWith:
+    case ScConditionMode::ContainsText:
+    case ScConditionMode::NotContainsText:
+        return true;
+    default:
+        break;
+    }
+
+    return false;
+}
+
+OString GetFixedFormula(ScConditionMode eMode, const ScAddress& rAddress, const OString& rText)
+{
+    OStringBuffer aBuffer;
+    XclXmlUtils::ToOString(aBuffer, rAddress);
+    OString aPos = aBuffer.makeStringAndClear();
+    switch (eMode)
+    {
+    case ScConditionMode::BeginsWith:
+        return OString("LEFT(" + aPos + ",LEN(" + rText + "))=\"" + rText + "\"");
+    case ScConditionMode::EndsWith:
+        return OString("RIGHT(" + aPos + ",LEN(" + rText + "))=\"" + rText + "\"");
+    case ScConditionMode::ContainsText:
+        return OString("NOT(ISERROR(SEARCH(" + rText + "," + aPos + ")))");
+    case ScConditionMode::NotContainsText:
+        return OString("ISERROR(SEARCH(" + rText + "," + aPos + "))");
+    default:
+        break;
+    }
+
+    return "";
+}
+
+}
+
 void XclExpExtCF::SaveXml( XclExpXmlStream& rStrm )
 {
     OUString aStyleName = mrFormat.GetStyle();
@@ -197,10 +239,28 @@ void XclExpExtCF::SaveXml( XclExpXmlStream& rStrm )
 
     sax_fastparser::FSHelperPtr& rWorksheet = rStrm.GetCurrentStream();
 
-    rWorksheet->startElementNS( XML_xm, XML_f );
-    rWorksheet->writeEscaped( aFormula );
-    rWorksheet->endElementNS( XML_xm, XML_f );
-    rDxf.SaveXmlExt( rStrm );
+    ScConditionMode eOperation = mrFormat.GetOperation();
+    if (RequiresFixedFormula(eOperation))
+    {
+        ScAddress aFixedFormulaPos = mrFormat.GetValidSrcPos();
+        OString aFixedFormulaText = aFormula.toUtf8();
+        OString aFixedFormula = GetFixedFormula(eOperation, aFixedFormulaPos, aFixedFormulaText);
+        rWorksheet->startElementNS( XML_xm, XML_f );
+        rWorksheet->writeEscaped(aFixedFormula.getStr());
+        rWorksheet->endElementNS( XML_xm, XML_f );
+
+        rWorksheet->startElementNS( XML_xm, XML_f );
+        rWorksheet->writeEscaped( aFormula );
+        rWorksheet->endElementNS( XML_xm, XML_f );
+        rDxf.SaveXmlExt(rStrm);
+    }
+    else
+    {
+        rWorksheet->startElementNS(XML_xm, XML_f);
+        rWorksheet->writeEscaped(aFormula);
+        rWorksheet->endElementNS(XML_xm, XML_f);
+        rDxf.SaveXmlExt(rStrm);
+    }
 }
 
 XclExpExtDataBar::XclExpExtDataBar( const XclExpRoot& rRoot, const ScDataBarFormat& rFormat, const ScAddress& rPos ):
@@ -293,6 +353,25 @@ const char* GetOperatorString(ScConditionMode eMode)
     return pRet;
 }
 
+const char* GetTypeString(ScConditionMode eMode)
+{
+    switch(eMode)
+    {
+        case ScConditionMode::Direct:
+            return "expression";
+        case ScConditionMode::BeginsWith:
+            return "beginsWith";
+        case ScConditionMode::EndsWith:
+            return "endsWith";
+        case ScConditionMode::ContainsText:
+            return "containsText";
+        case ScConditionMode::NotContainsText:
+            return "notContainsText";
+        default:
+            return "cellIs";
+    }
+}
+
 }
 
 void XclExpExtDataBar::SaveXml( XclExpXmlStream& rStrm )
@@ -381,7 +460,7 @@ XclExpExtCfRule::XclExpExtCfRule( const XclExpRoot& rRoot, const ScFormatEntry&
         {
             const ScCondFormatEntry& rCondFormat = static_cast<const ScCondFormatEntry&>(rFormat);
             mxEntry = new XclExpExtCF(*this, rCondFormat);
-            pType = "cellIs";
+            pType = GetTypeString(rCondFormat.GetOperation());
             mOperator = GetOperatorString( rCondFormat.GetOperation() );
         }
         break;