tdf#124953: Use rangelist's combined range top-left address...

as origin address in the conditional format formula on xlsx export.
Excel seems to get confused if anything else is supplied as the
origin in the formula.

For example, before this patch, for a condfmt range over the
range-list [A3:C5 E1:F2], the origin address used in the formula
(for text search type entries) is A3.

<conditionalFormatting sqref="A3:C5 E1:F2">
<cfRule type="containsText" dxfId="0" priority="1" operator="containsText" text="ABC">
<formula>NOT(ISERROR(SEARCH("ABC",A3)))</formula>
</cfRule>
</conditionalFormatting>

In this patch we use the top-left cell address(A1) of the combined range "A1:F5" as
the origin address.

<formula>NOT(ISERROR(SEARCH("ABC",A1)))</formula>

Change-Id: If08a859bc361f925148ff463758d03ebbc41c0ac
Reviewed-on: https://gerrit.libreoffice.org/71312
Tested-by: Jenkins
Reviewed-by: Andras Timar <andras.timar@collabora.com>

4 files changed, 29 insertions, 7 deletions

diff --git a/sc/qa/unit/data/xlsx/conditional_fmt_origin.xlsx b/sc/qa/unit/data/xlsx/conditional_fmt_origin.xlsx
new file mode 100644
index 000000000000..aef60b6cb489
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/conditional_fmt_origin.xlsx
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index de8eb723b884..bc8545ca182e 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -105,6 +105,7 @@ public:
     void testConditionalFormatRangeListXLSX();
     void testConditionalFormatContainsTextXLSX();
     void testConditionalFormatPriorityCheckXLSX();
+    void testConditionalFormatOriginXLSX();
     void testMiscRowHeightExport();
     void testNamedRangeBugfdo62729();
     void testBuiltinRangesXLSX();
@@ -243,6 +244,7 @@ public:
     CPPUNIT_TEST(testConditionalFormatRangeListXLSX);
     CPPUNIT_TEST(testConditionalFormatContainsTextXLSX);
     CPPUNIT_TEST(testConditionalFormatPriorityCheckXLSX);
+    CPPUNIT_TEST(testConditionalFormatOriginXLSX);
     CPPUNIT_TEST(testMiscRowHeightExport);
     CPPUNIT_TEST(testNamedRangeBugfdo62729);
     CPPUNIT_TEST(testBuiltinRangesXLSX);
@@ -3976,6 +3978,19 @@ void ScExportTest::testConditionalFormatPriorityCheckXLSX()
     CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong priorities for A3", bHighPriorityExtensionA3, nA3ExtPriority < nA3NormalPriority);
 }
 
+void ScExportTest::testConditionalFormatOriginXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc("conditional_fmt_origin.", FORMAT_XLSX);
+    CPPUNIT_ASSERT(xDocSh.is());
+
+    xmlDocPtr pDoc = XPathHelper::parseExport2(*this, *xDocSh, m_xSFactory, "xl/worksheets/sheet1.xml", FORMAT_XLSX);
+    CPPUNIT_ASSERT(pDoc);
+
+    // tdf#124953 : The range-list is B3:C6 F1:G2, origin address in the formula should be B1, not B3.
+    OUString aFormula = getXPathContent(pDoc, "//x:conditionalFormatting/x:cfRule/x:formula");
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong origin address in formula", OUString("NOT(ISERROR(SEARCH(\"BAC\",B1)))"), aFormula);
+}
+
 void ScExportTest::testEscapeCharInNumberFormatXLSX()
 {
     ScDocShellRef xDocSh = loadDoc("tdf81939.", FORMAT_XLSX);
diff --git a/sc/source/filter/excel/xecontent.cxx b/sc/source/filter/excel/xecontent.cxx
index f71d48337baf..234c681c59c3 100644
--- a/sc/source/filter/excel/xecontent.cxx
+++ b/sc/source/filter/excel/xecontent.cxx
@@ -579,7 +579,7 @@ void XclExpLabelranges::Save( XclExpStream& rStrm )
 class XclExpCFImpl : protected XclExpRoot
 {
 public:
-    explicit            XclExpCFImpl( const XclExpRoot& rRoot, const ScCondFormatEntry& rFormatEntry, sal_Int32 nPriority );
+    explicit            XclExpCFImpl( const XclExpRoot& rRoot, const ScCondFormatEntry& rFormatEntry, sal_Int32 nPriority, ScAddress aOrigin );
 
     /** Writes the body of the CF record. */
     void                WriteBody( XclExpStream& rStrm );
@@ -587,6 +587,7 @@ public:
 
 private:
     const ScCondFormatEntry& mrFormatEntry; /// Calc conditional format entry.
+    ScAddress           maOrigin;           /// Top left cell of the combined range
     XclFontData         maFontData;         /// Font formatting attributes.
     XclExpCellBorder    maBorder;           /// Border formatting attributes.
     XclExpCellArea      maArea;             /// Pattern formatting attributes.
@@ -608,9 +609,10 @@ private:
     bool                mbFormula2;
 };
 
-XclExpCFImpl::XclExpCFImpl( const XclExpRoot& rRoot, const ScCondFormatEntry& rFormatEntry, sal_Int32 nPriority ) :
+XclExpCFImpl::XclExpCFImpl( const XclExpRoot& rRoot, const ScCondFormatEntry& rFormatEntry, sal_Int32 nPriority, ScAddress aOrigin ) :
     XclExpRoot( rRoot ),
     mrFormatEntry( rFormatEntry ),
+    maOrigin( aOrigin ),
     mnFontColorId( 0 ),
     mnType( EXC_CF_TYPE_CELL ),
     mnOperator( EXC_CF_CMP_NONE ),
@@ -626,6 +628,10 @@ XclExpCFImpl::XclExpCFImpl( const XclExpRoot& rRoot, const ScCondFormatEntry& rF
     mbPattUsed( false ),
     mbFormula2(false)
 {
+    // Set correct tab for maOrigin from GetValidSrcPos() of the format-entry.
+    ScAddress aValidSrcPos = mrFormatEntry.GetValidSrcPos();
+    maOrigin.SetTab(aValidSrcPos.Tab());
+
     /*  Get formatting attributes here, and not in WriteBody(). This is needed to
         correctly insert all colors into the palette. */
 
@@ -1050,7 +1056,7 @@ void XclExpCFImpl::SaveXml( XclExpXmlStream& rStrm )
     if (RequiresFixedFormula(eOperation))
     {
         rWorksheet->startElement(XML_formula);
-        OString aFormula = GetFixedFormula(eOperation, mrFormatEntry.GetValidSrcPos(), aText);
+        OString aFormula = GetFixedFormula(eOperation, maOrigin, aText);
         rWorksheet->writeEscaped(aFormula.getStr());
         rWorksheet->endElement( XML_formula );
     }
@@ -1074,10 +1080,10 @@ void XclExpCFImpl::SaveXml( XclExpXmlStream& rStrm )
     rWorksheet->endElement( XML_cfRule );
 }
 
-XclExpCF::XclExpCF( const XclExpRoot& rRoot, const ScCondFormatEntry& rFormatEntry, sal_Int32 nPriority = 0 ) :
+XclExpCF::XclExpCF( const XclExpRoot& rRoot, const ScCondFormatEntry& rFormatEntry, sal_Int32 nPriority, ScAddress aOrigin ) :
     XclExpRecord( EXC_ID_CF ),
     XclExpRoot( rRoot ),
-    mxImpl( new XclExpCFImpl( rRoot, rFormatEntry, nPriority ) )
+    mxImpl( new XclExpCFImpl( rRoot, rFormatEntry, nPriority, aOrigin ) )
 {
 }
 
@@ -1282,11 +1288,12 @@ XclExpCondfmt::XclExpCondfmt( const XclExpRoot& rRoot, const ScConditionalFormat
     if( !maXclRanges.empty() )
     {
         std::vector<XclExpExtCondFormatData> aExtEntries;
+        ScAddress aOrigin = aScRanges.Combine().aStart;
         for( size_t nIndex = 0, nCount = rCondFormat.size(); nIndex < nCount; ++nIndex )
             if( const ScFormatEntry* pFormatEntry = rCondFormat.GetEntry( nIndex ) )
             {
                 if(pFormatEntry->GetType() == ScFormatEntry::Type::Condition)
-                    maCFList.AppendNewRecord( new XclExpCF( GetRoot(), static_cast<const ScCondFormatEntry&>(*pFormatEntry), ++rIndex ) );
+                    maCFList.AppendNewRecord( new XclExpCF( GetRoot(), static_cast<const ScCondFormatEntry&>(*pFormatEntry), ++rIndex, aOrigin ) );
                 else if(pFormatEntry->GetType() == ScFormatEntry::Type::ExtCondition)
                 {
                     const ScCondFormatEntry& rFormat = static_cast<const ScCondFormatEntry&>(*pFormatEntry);
diff --git a/sc/source/filter/inc/xecontent.hxx b/sc/source/filter/inc/xecontent.hxx
index 0c46c53039ed..66123a29e8a7 100644
--- a/sc/source/filter/inc/xecontent.hxx
+++ b/sc/source/filter/inc/xecontent.hxx
@@ -168,7 +168,7 @@ class XclExpCFImpl;
 class XclExpCF : public XclExpRecord, protected XclExpRoot
 {
 public:
-    explicit            XclExpCF( const XclExpRoot& rRoot, const ScCondFormatEntry& rFormatEntry, sal_Int32 nPriority );
+    explicit            XclExpCF( const XclExpRoot& rRoot, const ScCondFormatEntry& rFormatEntry, sal_Int32 nPriority, ScAddress aOrigin );
     virtual             ~XclExpCF() override;
 
     virtual void        SaveXml( XclExpXmlStream& rStrm ) override;