sc/qa/unit/data/xlsx/tdf166413.xlsx |binary
sc/qa/unit/subsequent_export_test4.cxx | 35 +++++++++++++++++++++++++++++++++
sc/source/filter/excel/xecontent.cxx | 12 ++++++-----
3 files changed, 42 insertions(+), 5 deletions(-)
New commits:
commit d1250d073fa978163eb86e4bd8aedcacbdd7895b
Author: Aron Budea <aron.bu...@collabora.com>
AuthorDate: Thu May 1 13:48:42 2025 +0930
Commit: Xisco Fauli <xiscofa...@libreoffice.org>
CommitDate: Fri May 2 10:51:50 2025 +0200
tdf166413 sc: escape quotes in XLSX cond. formatting fixed formula
...during export, otherwise Excel fails to open file
Change-Id: I50bc16ff4064a84ae3601cfa539d7b90f5441607
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/184852
Tested-by: Jenkins
Reviewed-by: Aron Budea <aron.bu...@collabora.com>
Reviewed-by: Mike Kaganski <mike.kagan...@collabora.com>
(cherry picked from commit 55169df2f6e4f8deb9c042b81cc3ff4f01cbf522)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/184855
Reviewed-by: Xisco Fauli <xiscofa...@libreoffice.org>
diff --git a/sc/qa/unit/data/xlsx/tdf166413.xlsx
b/sc/qa/unit/data/xlsx/tdf166413.xlsx
new file mode 100644
index 000000000000..ae6e58a8fa09
Binary files /dev/null and b/sc/qa/unit/data/xlsx/tdf166413.xlsx differ
diff --git a/sc/qa/unit/subsequent_export_test4.cxx
b/sc/qa/unit/subsequent_export_test4.cxx
index af4c770962df..72e07d6f01be 100644
--- a/sc/qa/unit/subsequent_export_test4.cxx
+++ b/sc/qa/unit/subsequent_export_test4.cxx
@@ -2208,6 +2208,41 @@ CPPUNIT_TEST_FIXTURE(ScExportTest4, testTdf165886)
assertXPathContent(pSheet, "/x:worksheet/x:sheetData/x:row[4]/x:c[2]/x:f",
u"OR(D4=0,D4<>’’)");
}
+CPPUNIT_TEST_FIXTURE(ScExportTest4, testTdf166413)
+{
+ createScDoc("xlsx/tdf166413.xlsx");
+
+ save(u"Calc Office Open XML"_ustr);
+
+ xmlDocUniquePtr pSheet = parseExport(u"xl/worksheets/sheet1.xml"_ustr);
+ CPPUNIT_ASSERT(pSheet);
+
+ // Without the accompanying fix in place, this test would have failed with
+ // - Expected: NOT(ISERROR(SEARCH("""ABC""",A1)))
+ // - Actual : NOT(ISERROR(SEARCH(""ABC"",A1)))
+ assertXPathContent(pSheet,
+
"/x:worksheet/x:conditionalFormatting[@sqref=\"A1:C1\"]/x:cfRule/x:formula",
+ u"NOT(ISERROR(SEARCH(\"\"\"ABC\"\"\",A1)))");
+ // Similarly
+ // - Expected: ISERROR(SEARCH("""ABC""",A2))
+ // - Actual : ISERROR(SEARCH(""ABC"",A2))
+ assertXPathContent(pSheet,
+
"/x:worksheet/x:conditionalFormatting[@sqref=\"A2:C2\"]/x:cfRule/x:formula",
+ u"ISERROR(SEARCH(\"\"\"ABC\"\"\",A2))");
+ // Similarly
+ // - Expected: LEFT(A3,LEN("""ABC"""))="""ABC"""
+ // - Actual : LEFT(A3,LEN(""ABC""))=""ABC""
+ assertXPathContent(pSheet,
+
"/x:worksheet/x:conditionalFormatting[@sqref=\"A3:C3\"]/x:cfRule/x:formula",
+ u"LEFT(A3,LEN(\"\"\"ABC\"\"\"))=\"\"\"ABC\"\"\"");
+ // Similarly
+ // - Expected: RIGHT(A4,LEN("""ABC"""))="""ABC"""
+ // - Actual : RIGHT(A4,LEN(""ABC""))=""ABC""
+ assertXPathContent(pSheet,
+
"/x:worksheet/x:conditionalFormatting[@sqref=\"A4:C4\"]/x:cfRule/x:formula",
+ u"RIGHT(A4,LEN(\"\"\"ABC\"\"\"))=\"\"\"ABC\"\"\"");
+}
+
CPPUNIT_PLUGIN_IMPLEMENT();
/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/filter/excel/xecontent.cxx
b/sc/source/filter/excel/xecontent.cxx
index aad8a9cdcfde..11dbf6506f53 100644
--- a/sc/source/filter/excel/xecontent.cxx
+++ b/sc/source/filter/excel/xecontent.cxx
@@ -1001,11 +1001,13 @@ bool RequiresFixedFormula(ScConditionMode eMode)
return false;
}
-OString GetFixedFormula(ScConditionMode eMode, const ScAddress& rAddress,
std::string_view rText)
+OString GetFixedFormula(ScConditionMode eMode, const ScAddress& rAddress,
const OString& rText)
{
OStringBuffer aBuffer;
XclXmlUtils::ToOString(aBuffer, rAddress);
OString aPos = aBuffer.makeStringAndClear();
+ // double quotes in rText need to be escaped
+ const OString aText = rText.replaceAll("\""_ostr, "\"\""_ostr);
switch (eMode)
{
case ScConditionMode::Error:
@@ -1013,13 +1015,13 @@ OString GetFixedFormula(ScConditionMode eMode, const
ScAddress& rAddress, std::s
case ScConditionMode::NoError:
return OString("NOT(ISERROR(" + aPos + "))") ;
case ScConditionMode::BeginsWith:
- return OString("LEFT(" + aPos + ",LEN(\"" + rText + "\"))=\"" +
rText + "\"");
+ return OString("LEFT(" + aPos + ",LEN(\"" + aText + "\"))=\"" +
aText + "\"");
case ScConditionMode::EndsWith:
- return OString("RIGHT(" + aPos +",LEN(\"" + rText + "\"))=\"" +
rText + "\"");
+ return OString("RIGHT(" + aPos +",LEN(\"" + aText + "\"))=\"" +
aText + "\"");
case ScConditionMode::ContainsText:
- return OString(OString::Concat("NOT(ISERROR(SEARCH(\"") + rText +
"\"," + aPos + ")))");
+ return OString(OString::Concat("NOT(ISERROR(SEARCH(\"") + aText +
"\"," + aPos + ")))");
case ScConditionMode::NotContainsText:
- return OString(OString::Concat("ISERROR(SEARCH(\"") + rText +
"\"," + aPos + "))");
+ return OString(OString::Concat("ISERROR(SEARCH(\"") + aText +
"\"," + aPos + "))");
default:
break;
}
