tdf#90104 XLSX export: fix commas in data validation list
Comma was exported as list separator instead of
decimal separator, messing up the data validation list
by breaking integer and fractional parts of a number to
two integer numbers, for example the 2-item list 1,5, 2,5
to the bad 4-item list 1, 5, 2, 5.
Note: for testing with numbers, it needs to set decimal
separator to comma in locale settings.
Co-authored-by: Attila Szűcs (NISZ)
Change-Id: I6133c402b47aab8ed9f02b412762404fc520badc
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/104240
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>
diff --git a/sc/qa/unit/data/xlsx/tdf90104.xlsx b/sc/qa/unit/data/xlsx/tdf90104.xlsx
new file mode 100644
index 0000000..ce6ed68
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/tdf90104.xlsx
Binary files differ
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index e928785..bf015d9 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -93,6 +93,7 @@ public:
ScDocShellRef saveAndReloadPassword( ScDocShell*, const OUString&, const OUString&, const OUString&, SfxFilterFlags );
void test();
void testTdf90104();
void testTdf111876();
void testPasswordExportODS();
void testTdf134332();
@@ -276,6 +277,7 @@ public:
CPPUNIT_TEST_SUITE(ScExportTest);
CPPUNIT_TEST(test);
CPPUNIT_TEST(testTdf90104);
CPPUNIT_TEST(testTdf111876);
CPPUNIT_TEST(testPasswordExportODS);
CPPUNIT_TEST(testTdf134332);
@@ -478,6 +480,7 @@ void ScExportTest::registerNamespaces(xmlXPathContextPtr& pXmlXPathCtx)
{ BAD_CAST("ContentType"), BAD_CAST("http://schemas.openxmlformats.org/package/2006/content-types") },
{ BAD_CAST("x14"), BAD_CAST("http://schemas.microsoft.com/office/spreadsheetml/2009/9/main") },
{ BAD_CAST("xm"), BAD_CAST("http://schemas.microsoft.com/office/excel/2006/main") },
{ BAD_CAST("x12ac"), BAD_CAST("http://schemas.microsoft.com/office/spreadsheetml/2011/1/ac") },
};
for(size_t i = 0; i < SAL_N_ELEMENTS(aNamespaces); ++i)
{
@@ -541,6 +544,27 @@ void ScExportTest::test()
xDocSh->DoClose();
}
void ScExportTest::testTdf90104()
{
ScDocShellRef xShell = loadDoc("tdf90104.", 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);
assertXPathContent(pDoc, "/x:worksheet/x:dataValidations/x:dataValidation/mc:AlternateContent"
"/mc:Choice/x12ac:list", "1,\"2,3\",4,\"5,6\"");
assertXPathContent(pDoc, "/x:worksheet/x:dataValidations/x:dataValidation/mc:AlternateContent"
"/mc:Fallback/x:formula1", "\"1,2,3,4,5,6\"");
}
void ScExportTest::testTdf111876()
{
// Document with relative path hyperlink
diff --git a/sc/source/filter/excel/xecontent.cxx b/sc/source/filter/excel/xecontent.cxx
index a04c456f..dbb28ec 100644
--- a/sc/source/filter/excel/xecontent.cxx
+++ b/sc/source/filter/excel/xecontent.cxx
@@ -1738,6 +1738,8 @@ XclExpDV::XclExpDV( const XclExpRoot& rRoot, sal_uLong nScHandle ) :
OUString aString;
if( XclTokenArrayHelper::GetStringList( aString, *xScTokArr, '\n' ) )
{
bool bList = false;
OUStringBuffer sListBuf;
OUStringBuffer sFormulaBuf;
sFormulaBuf.append( '"' );
/* Formula is a list of string tokens -> build the Excel string.
@@ -1750,12 +1752,22 @@ XclExpDV::XclExpDV( const XclExpRoot& rRoot, sal_uLong nScHandle ) :
for(;;)
{
const OUString aToken( aString.getToken( 0, '\n', nStringIx ) );
if (aToken.indexOf(",") != -1)
{
sListBuf.append('"');
sListBuf.append(aToken);
sListBuf.append('"');
bList = true;
}
else
sListBuf.append(aToken);
mxString1->Append( aToken );
sFormulaBuf.append( aToken );
if (nStringIx<0)
break;
mxString1->Append(OUString(u'\0'));
sFormulaBuf.append( ',' );
sListBuf.append( ',' );
}
}
::set_flag( mnFlags, EXC_DV_STRINGLIST );
@@ -1774,6 +1786,10 @@ XclExpDV::XclExpDV( const XclExpRoot& rRoot, sal_uLong nScHandle ) :
sFormulaBuf.append( '"' );
msFormula1 = sFormulaBuf.makeStringAndClear();
if (bList)
msList = sListBuf.makeStringAndClear();
else
sListBuf.remove(0, sListBuf.getLength());
}
else
{
@@ -1869,7 +1885,24 @@ void XclExpDV::SaveXml( XclExpXmlStream& rStrm )
XML_showInputMessage, ToPsz( ::get_flag( mnFlags, EXC_DV_SHOWPROMPT ) ),
XML_sqref, XclXmlUtils::ToOString(rStrm.GetRoot().GetDoc(), maScRanges),
XML_type, lcl_GetValidationType(mnFlags) );
if( !msFormula1.isEmpty() )
if (!msList.isEmpty())
{
rWorksheet->startElement(FSNS(XML_mc, XML_AlternateContent),
FSNS(XML_xmlns, XML_x12ac),rStrm.getNamespaceURL(OOX_NS(x12ac)),
FSNS(XML_xmlns, XML_mc),rStrm.getNamespaceURL(OOX_NS(mce)));
rWorksheet->startElement(FSNS(XML_mc, XML_Choice), XML_Requires, "x12ac");
rWorksheet->startElement(FSNS(XML_x12ac, XML_list));
rWorksheet->writeEscaped(msList);
rWorksheet->endElement(FSNS(XML_x12ac, XML_list));
rWorksheet->endElement(FSNS(XML_mc, XML_Choice));
rWorksheet->startElement(FSNS(XML_mc, XML_Fallback));
rWorksheet->startElement(XML_formula1);
rWorksheet->writeEscaped(msFormula1);
rWorksheet->endElement(XML_formula1);
rWorksheet->endElement(FSNS(XML_mc, XML_Fallback));
rWorksheet->endElement(FSNS(XML_mc, XML_AlternateContent));
}
if (msList.isEmpty() && !msFormula1.isEmpty())
{
rWorksheet->startElement(XML_formula1);
rWorksheet->writeEscaped( msFormula1 );
diff --git a/sc/source/filter/inc/xecontent.hxx b/sc/source/filter/inc/xecontent.hxx
index bc873e8..7632af4 100644
--- a/sc/source/filter/inc/xecontent.hxx
+++ b/sc/source/filter/inc/xecontent.hxx
@@ -343,6 +343,7 @@ private:
XclExpStringRef mxString1; /// String for first condition formula.
XclTokenArrayRef mxTokArr1; /// Formula for first condition.
OUString msFormula1; /// OOXML Formula for first condition.
OUString msList; /// x12ac:list for first condition.
XclTokenArrayRef mxTokArr2; /// Formula for second condition.
OUString msFormula2; /// OOXML Formula for second condition.
sal_uInt32 mnFlags; /// Miscellaneous flags.
