tdf#139809 sc ooxml export autofiltered date columns
Export XML_dateGroupItem, XML_year, XML_month, XML_day,
XML_dateTimeGrouping based on the OOXML standard.
Follow up commit of: d101a6f8579df8241dcd8100908868cd7d7c3f8a
(tdf#116818 sc OOXML: import autofiltered date columns)
Note: DOCX round-trip is not lossless here, because
instead of exporting the original yearly and monthly
dataGroupItem filtering, the export contains daily level
dataGroupItem elements for each day.
Change-Id: I456b2240213839725e2b416622986ee4a9cf60d5
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/109699
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/dateAutofilter.xlsx b/sc/qa/unit/data/xlsx/dateAutofilter.xlsx
new file mode 100644
index 0000000..1c83651
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/dateAutofilter.xlsx
Binary files differ
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 972004c..2b8738d 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -211,6 +211,7 @@ public:
void testTdf95640_ods_to_xlsx();
void testTdf95640_ods_to_xlsx_with_standard_list();
void testTdf95640_xlsx_to_xlsx();
void testDateAutofilterXLSX();
void testRefStringXLSX();
void testRefStringConfigXLSX();
@@ -390,6 +391,7 @@ public:
CPPUNIT_TEST(testTdf95640_ods_to_xlsx);
CPPUNIT_TEST(testTdf95640_ods_to_xlsx_with_standard_list);
CPPUNIT_TEST(testTdf95640_xlsx_to_xlsx);
CPPUNIT_TEST(testDateAutofilterXLSX);
CPPUNIT_TEST(testRefStringXLSX);
CPPUNIT_TEST(testRefStringConfigXLSX);
@@ -4444,6 +4446,29 @@ void ScExportTest::testTdf95640_xlsx_to_xlsx()
"Low,Medium,High");
}
void ScExportTest::testDateAutofilterXLSX()
{
// XLSX Roundtripping autofilter with date list
ScDocShellRef xDocSh = loadDoc(u"dateAutofilter.", FORMAT_XLSX);
CPPUNIT_ASSERT(xDocSh.is());
xmlDocUniquePtr pDoc = XPathHelper::parseExport2(*this, *xDocSh, m_xSFactory, "xl/worksheets/sheet1.xml", FORMAT_XLSX);
CPPUNIT_ASSERT(pDoc);
assertXPath(pDoc, "//x:autoFilter", "ref", "A1:B4");
assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "day", "02");
assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "month", "03");
assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "year", "2017");
assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "dateTimeGrouping", "day");
assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[2]", "day", "01");
assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[2]", "month", "10");
assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[2]", "year", "2014");
assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[2]", "dateTimeGrouping", "day");
xDocSh->DoClose();
}
void ScExportTest::testTdf88657ODS()
{
ScDocShellRef xDocSh = loadDoc(u"tdf88657.", FORMAT_ODS);
diff --git a/sc/source/filter/excel/excrecds.cxx b/sc/source/filter/excel/excrecds.cxx
index 421c0e8..c31c81d 100644
--- a/sc/source/filter/excel/excrecds.cxx
+++ b/sc/source/filter/excel/excrecds.cxx
@@ -24,6 +24,7 @@
#include <svl/zforlist.hxx>
#include <sal/log.hxx>
#include <sax/fastattribs.hxx>
#include <string.h>
@@ -781,7 +782,7 @@ void XclExpAutofilter::AddMultiValueEntry( const ScQueryEntry& rEntry )
meType = MultiValue;
const ScQueryEntry::QueryItemsType& rItems = rEntry.GetQueryItems();
for (const auto& rItem : rItems)
maMultiValues.push_back(rItem.maString.getString());
maMultiValues.push_back( std::make_pair(rItem.maString.getString(), rItem.meType == ScQueryEntry::ByDate) );
}
void XclExpAutofilter::WriteBody( XclExpStream& rStrm )
@@ -839,9 +840,31 @@ void XclExpAutofilter::SaveXml( XclExpXmlStream& rStrm )
rWorksheet->startElement(XML_filters);
for (const auto& rMultiValue : maMultiValues)
{
OString aStr = OUStringToOString(rMultiValue, RTL_TEXTENCODING_UTF8);
const char* pz = aStr.getStr();
rWorksheet->singleElement(XML_filter, XML_val, pz);
OString aStr = OUStringToOString(rMultiValue.first, RTL_TEXTENCODING_UTF8);
if( !rMultiValue.second )
{
const char* pz = aStr.getStr();
rWorksheet->singleElement(XML_filter, XML_val, pz);
}
else
{
sax_fastparser::FastAttributeList* pAttrList = sax_fastparser::FastSerializerHelper::createAttrList();
sal_Int32 aDateGroup[3] = { XML_year, XML_month, XML_day };
sal_Int32 idx = 0;
for (size_t i = 0; idx >= 0 && i < 3; i++)
{
OString kw = aStr.getToken(0, '-', idx);
kw = kw.trim();
if (!kw.isEmpty())
{
pAttrList->add(aDateGroup[i], kw);
}
}
// TODO: date filter can only handle YYYY-MM-DD date formats, so XML_dateTimeGrouping value
// will be "day" as default, until date filter cannot handle HH:MM:SS.
pAttrList->add(XML_dateTimeGrouping, "day");
rWorksheet->singleElement(XML_dateGroupItem, pAttrList);
}
}
rWorksheet->endElement(XML_filters);
}
diff --git a/sc/source/filter/inc/excrecds.hxx b/sc/source/filter/inc/excrecds.hxx
index 986654fb..0a4c65d 100644
--- a/sc/source/filter/inc/excrecds.hxx
+++ b/sc/source/filter/inc/excrecds.hxx
@@ -366,7 +366,7 @@ private:
sal_uInt16 nCol;
sal_uInt16 nFlags;
ExcFilterCondition aCond[ 2 ];
std::vector<OUString> maMultiValues;
std::vector<std::pair<OUString, bool>> maMultiValues; // first->values, second->bDateFormat
bool AddCondition( ScQueryConnect eConn, sal_uInt8 nType,
sal_uInt8 nOp, double fVal, const OUString* pText,
diff --git a/sc/source/filter/oox/autofilterbuffer.cxx b/sc/source/filter/oox/autofilterbuffer.cxx
index b479aef..757981a 100644
--- a/sc/source/filter/oox/autofilterbuffer.cxx
+++ b/sc/source/filter/oox/autofilterbuffer.cxx
@@ -237,6 +237,8 @@ void DiscreteFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAt
case XLS_TOKEN( dateGroupItem ):
{
OUString aDateValue;
// it is just a fallback, we do not need the XML_day as default value,
// because if the dateGroupItem exists also XML_dateTimeGrouping exists!
sal_uInt16 nToken = rAttribs.getToken(XML_dateTimeGrouping, XML_day);
if( nToken == XML_year || nToken == XML_month || nToken == XML_day )
{
diff --git a/sc/source/ui/unoobj/datauno.cxx b/sc/source/ui/unoobj/datauno.cxx
index 03f5825..7d78766 100644
--- a/sc/source/ui/unoobj/datauno.cxx
+++ b/sc/source/ui/unoobj/datauno.cxx
@@ -1134,6 +1134,7 @@ void fillQueryParam(
aItem.maString = rPool.intern(aStr);
}
// filter all dates starting with the given date filter YYYY or YYYY-MM
if( aItem.meType == ScQueryEntry::ByDate && aItem.maString.getLength() < 10 )
{
ScFilterEntries aFilterEntries;
