tdf#117816, work around MS Excel bug with containsText cond format

This is a combination o the following two master commits:

tdf#117816, work around MS Excel bug with containsText cond format

Excel seems to require the formula that is equal to the containsText
condition.

According to cfRule (18.3.1.10) "Only rules with a type attribute
 value of expression support formula syntax." which contradicts the
MS EXCEL behavior.

Change-Id: Ifa3f9fee58194f70a64b37c62922513435d43bb8
Reviewed-on: https://gerrit.libreoffice.org/57432
Tested-by: Jenkins
Reviewed-by: Markus Mohrhard <markus.mohrhard@googlemail.com>

related tdf#117816, add more work arounds for MS Excel bugs

Another set of cases where MS Excel needs the formula.

Change-Id: I58344a540ad69ff9b8c56aa817730079bd011acd
Reviewed-on: https://gerrit.libreoffice.org/57466
Tested-by: Jenkins
Reviewed-by: Eike Rathke <erack@redhat.com>

1 files changed, 53 insertions, 1 deletions

diff --git a/sc/source/filter/excel/xecontent.cxx b/sc/source/filter/excel/xecontent.cxx
index 8cd7de4b5066..aa92499f135b 100644
--- a/sc/source/filter/excel/xecontent.cxx
+++ b/sc/source/filter/excel/xecontent.cxx
@@ -936,6 +936,50 @@ bool IsTextRule(ScConditionMode eMode)
     return false;
 }
 
+bool RequiresFixedFormula(ScConditionMode eMode)
+{
+    switch(eMode)
+    {
+        case ScConditionMode::NoError:
+        case ScConditionMode::Error:
+        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;
+    OStringBuffer aPosBuffer = XclXmlUtils::ToOString(aBuffer, rAddress);
+    OString aPos = aPosBuffer.makeStringAndClear();
+    switch (eMode)
+    {
+        case ScConditionMode::Error:
+            return "";
+        case ScConditionMode::NoError:
+            return "";
+        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 OString("");
+}
+
 }
 
 void XclExpCFImpl::SaveXml( XclExpXmlStream& rStrm )
@@ -981,7 +1025,15 @@ void XclExpCFImpl::SaveXml( XclExpXmlStream& rStrm )
             XML_text, aText.getStr(),
             XML_dxfId, OString::number( GetDxfs().GetDxfId( mrFormatEntry.GetStyle() ) ).getStr(),
             FSEND );
-    if(!IsTextRule(eOperation) && !IsTopBottomRule(eOperation))
+
+    if (RequiresFixedFormula(eOperation))
+    {
+        rWorksheet->startElement( XML_formula, FSEND );
+        OString aFormula = GetFixedFormula(eOperation, mrFormatEntry.GetValidSrcPos(), aText);
+        rWorksheet->writeEscaped(aFormula.getStr());
+        rWorksheet->endElement( XML_formula );
+    }
+    else if(!IsTextRule(eOperation) && !IsTopBottomRule(eOperation))
     {
         rWorksheet->startElement( XML_formula, FSEND );
         std::unique_ptr<ScTokenArray> pTokenArray(mrFormatEntry.CreateFlatCopiedTokenArray(0));