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>
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 0000000..aef60b6
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/conditional_fmt_origin.xlsx
Binary files differ
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index de8eb72..bc8545c 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 f71d483..234c681 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 0c46c53..66123a2 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;
