sc/qa/unit/data/xlsx/tdf139021.xlsx |binary
sc/qa/unit/subsequent_export-test.cxx | 36 +++++++++++++
sc/source/filter/excel/xeextlst.cxx | 89 ++++++++++++++++++++++++++++++++--
3 files changed, 120 insertions(+), 5 deletions(-)
New commits:
commit 583e2bfba2d72ac8afe7261c23f380daf5486889
Author: Tibor Nagy <nagy.tib...@nisz.hu>
AuthorDate: Sat Dec 19 22:25:08 2020 +0100
Commit: László Németh <nem...@numbertext.org>
CommitDate: Mon Dec 28 13:48:44 2020 +0100
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 <nem...@numbertext.org>
Reviewed-by: László Németh <nem...@numbertext.org>
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
Binary files /dev/null and b/sc/qa/unit/data/xlsx/tdf139021.xlsx differ
diff --git a/sc/qa/unit/subsequent_export-test.cxx
b/sc/qa/unit/subsequent_export-test.cxx
index e29e7a5ee648..2e9dc8d55259 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -86,6 +86,7 @@ public:
virtual void tearDown() override;
void test();
+ void testExtCondFormatXLSX();
void testTdf90104();
void testTdf111876();
void testPasswordExportODS();
@@ -276,6 +277,7 @@ public:
CPPUNIT_TEST_SUITE(ScExportTest);
CPPUNIT_TEST(test);
+ CPPUNIT_TEST(testExtCondFormatXLSX);
CPPUNIT_TEST(testTdf90104);
CPPUNIT_TEST(testTdf111876);
CPPUNIT_TEST(testPasswordExportODS);
@@ -514,6 +516,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 35afd9570853..1f4055aff5ea 100644
--- a/sc/source/filter/excel/xeextlst.cxx
+++ b/sc/source/filter/excel/xeextlst.cxx
@@ -146,6 +146,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();
@@ -194,10 +236,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 ):
@@ -290,6 +350,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 )
@@ -378,7 +457,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;
_______________________________________________
Libreoffice-commits mailing list
libreoffice-comm...@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits
