[Libreoffice-commits] core.git: sc/qa sc/source
Bartosz Kosiorek
gang65 at poczta.onet.pl
Thu Aug 31 22:20:51 UTC 2017
dev/null |binary
sc/qa/unit/data/xlsx/pivot-table/with-strings-integers-and-dates.xlsx |binary
sc/qa/unit/subsequent_export-test.cxx | 132 ++++++----
sc/source/filter/excel/xepivotxml.cxx | 64 ++++
4 files changed, 138 insertions(+), 58 deletions(-)
New commits:
commit baed960b8ce55370a040d0c0f49bd3fdc16bdf84
Author: Bartosz Kosiorek <gang65 at poczta.onet.pl>
Date: Tue Aug 15 07:35:35 2017 +0200
tdf#89139 Fix exporting of DateTime to CacheDefinition according to ISO 8601
With this commit, the date is saved in Excel format, like:
"2009-07-06T10:53:02"
We are now exporting attributes:
"minDate"
"maxDate"
"containsDate"
This is necessary to export properly .xlsx file. Without that MS Office 365
is displaying error, and it is not even trying displaying .xslx file content
Change-Id: I1239f5582173afe99bf9178fd4edd1dc5ca28e8e
Reviewed-on: https://gerrit.libreoffice.org/41162
Tested-by: Jenkins <ci at libreoffice.org>
Reviewed-by: Bartosz Kosiorek <gang65 at poczta.onet.pl>
diff --git a/sc/qa/unit/data/xlsx/pivot-table/with-strings-integers-and-dates.xlsx b/sc/qa/unit/data/xlsx/pivot-table/with-strings-integers-and-dates.xlsx
new file mode 100644
index 000000000000..f343b51f98be
Binary files /dev/null and b/sc/qa/unit/data/xlsx/pivot-table/with-strings-integers-and-dates.xlsx differ
diff --git a/sc/qa/unit/data/xlsx/pivot.xlsx b/sc/qa/unit/data/xlsx/pivot.xlsx
deleted file mode 100644
index e6297a91777b..000000000000
Binary files a/sc/qa/unit/data/xlsx/pivot.xlsx and /dev/null differ
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 79221e9c85f4..5e0066365156 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -119,7 +119,8 @@ public:
void testFormatExportODS();
void testPivotTableExportXLSX();
- void testPivotExportXLSX();
+ void testPivotCacheExportXLSX();
+
void testCommentExportXLSX();
#if HAVE_MORE_FONTS
void testCustomColumnWidthExportXLSX();
@@ -234,7 +235,8 @@ public:
CPPUNIT_TEST(testFormatExportODS);
CPPUNIT_TEST(testPivotTableExportXLSX);
- CPPUNIT_TEST(testPivotExportXLSX);
+ CPPUNIT_TEST(testPivotCacheExportXLSX);
+
CPPUNIT_TEST(testCommentExportXLSX);
#if HAVE_MORE_FONTS
CPPUNIT_TEST(testCustomColumnWidthExportXLSX);
@@ -588,7 +590,7 @@ void ScExportTest::testPivotTableExportXLSX()
assertXPath(pTable, "/x:pivotTableDefinition/x:pivotFields/x:pivotField[3]/x:items/x:item[3]", "h", "1");
}
-void ScExportTest::testPivotExportXLSX()
+void ScExportTest::testPivotCacheExportXLSX()
{
// tdf#89139 FILESAVE xlsx pivot table corrupted after save with LO and re-open with MS Office
// MS Excel is very sensitive for proper values of fields:
@@ -597,62 +599,98 @@ void ScExportTest::testPivotExportXLSX()
// This test case ensures, that such values are properly set according to documentation:
// https://technet.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.shareditems.aspx
- ScDocShellRef xShell = loadDoc("pivot.", FORMAT_XLSX);
+ ScDocShellRef xShell = loadDoc("pivot-table/with-strings-integers-and-dates.", FORMAT_XLSX);
CPPUNIT_ASSERT(xShell.is());
std::shared_ptr<utl::TempFile> pXPathFile = ScBootstrapFixture::exportTo(&(*xShell), FORMAT_XLSX);
- xmlDocPtr pSheet = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/pivotCache/pivotCacheDefinition1.xml");
- CPPUNIT_ASSERT(pSheet);
+ xmlDocPtr pCacheDef = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/pivotCache/pivotCacheDefinition1.xml");
+ CPPUNIT_ASSERT(pCacheDef);
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField", 5);
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField", 5);
// Four strings and one empty field
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]", "name", "imieinazwisko");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsBlank", "1");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsMixedTypes");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsSemiMixedTypes");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsString");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsNumber");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsInteger");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "minValue");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "maxValue");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "count", "5");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]", "name", "mixed strings and empty");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsBlank", "1");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsMixedTypes");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsSemiMixedTypes");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsNonDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsString");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "minDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "maxDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsNumber");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "containsInteger");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "minValue");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "maxValue");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems", "count", "5");
// Two integers and one empty field
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]", "name", "wartosc");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsBlank", "1");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsMixedTypes");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsSemiMixedTypes");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsString", "0");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsNumber", "1");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsInteger", "1");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "minValue", "111");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "maxValue", "222");
- // We list items on round-trip and Excel accepts that; so no check for "count" attribute
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]", "name", "mixed empty fileds and integers");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsBlank", "1");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsMixedTypes");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsSemiMixedTypes");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsNonDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsDate");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsString", "0");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "minDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "maxDate");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsNumber", "1");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "containsInteger", "1");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "minValue", "111");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "maxValue", "222");
+ // We list items on round-trip, Excel not, but Excel accepts that; We need list all items, because we are using it as reference in pivotCacheRecords1.xml
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[2]/x:sharedItems", "count", "3");
// Five integers
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]", "name", "druga wartosc");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsBlank");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsMixedTypes");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsSemiMixedTypes", "0");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsString", "0");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsNumber", "1");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsInteger", "1");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "minValue", "1111");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "maxValue", "5555");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "count");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]", "name", "all fields are integers");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsBlank");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsMixedTypes");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsSemiMixedTypes", "0");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsNonDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsDate");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsString", "0");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "minDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "maxDate");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsNumber", "1");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "containsInteger", "1");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "minValue", "1111");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "maxValue", "5555");
+ // We list items on round-trip, Excel not, but Excel accepts that; We need list all items, because we are using it as reference in pivotCacheRecords1.xml
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[3]/x:sharedItems", "count", "5");
// Three integers and one string
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]", "name", "trzecia wartosc");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsBlank");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsMixedTypes", "1");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsSemiMixedTypes");
- assertXPathNoAttribute(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsString");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsNumber", "1");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsInteger", "1");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "minValue", "1234");
- assertXPath(pSheet, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "maxValue", "5678");
- // We list items on round-trip and Excel accepts that; so no check for "count" attribute
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]", "name", "mixed strings and integers");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsBlank");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsMixedTypes", "1");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsSemiMixedTypes");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsNonDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsString");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "minDate");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "maxDate");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsNumber", "1");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "containsInteger", "1");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "minValue", "1234");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "maxValue", "5678");
+ // We list items on round-trip, Excel not, but Excel accepts that; We need list all items, because we are using it as reference in pivotCacheRecords1.xml
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[4]/x:sharedItems", "count", "4");
+
+ // Four dates without blanks
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]", "name", "date and time with duplicated entries");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "containsBlank");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "containsMixedTypes");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "containsSemiMixedTypes","0");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "containsDate", "1");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "containsString", "0");
+ //TODO Date generator in tests are one day higher, than during standard xlsx export. It should be: minDate="1899-12-31T00:00:00" maxDate="2009-07-06T10:53:02"
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "minDate", "1900-01-01T00:00:00");
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "maxDate", "2009-07-07T10:53:02");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "containsNumber");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "containsInteger");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "minValue");
+ assertXPathNoAttribute(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "maxValue");
+ // We list items on round-trip, Excel not, but Excel accepts that; We need list all items, because we are using it as reference in pivotCacheRecords1.xml
+ assertXPath(pCacheDef, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[5]/x:sharedItems", "count", "4");
}
void ScExportTest::testCommentExportXLSX()
diff --git a/sc/source/filter/excel/xepivotxml.cxx b/sc/source/filter/excel/xepivotxml.cxx
index e3283ef87c2d..b72e24bf19c5 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -17,6 +17,7 @@
#include <oox/export/utils.hxx>
#include <oox/token/namespaces.hxx>
+#include <sax/tools/converter.hxx>
#include <com/sun/star/sheet/DataPilotFieldOrientation.hpp>
#include <com/sun/star/sheet/DataPilotOutputRangeType.hpp>
@@ -51,6 +52,9 @@ void savePivotCacheRecordsXml( XclExpXmlStream& rStrm, const ScDPCache& rCache )
const ScDPCache::IndexArrayType* pArray = rCache.GetFieldIndexArray(nField);
assert(pArray);
assert(static_cast<size_t>(i) < pArray->size());
+
+ // We are using XML_x reference (like: <x v="0"/>), instead of values here (eg: <s v="No Discount"/>).
+ // That's why in SavePivotCacheXml method, we need to list all items.
pRecStrm->singleElement(XML_x, XML_v, OString::number((*pArray)[i]), FSEND);
}
pRecStrm->endElement(XML_r);
@@ -171,6 +175,27 @@ const XclExpXmlPivotCaches::Entry* XclExpXmlPivotCaches::GetCache( sal_Int32 nCa
return &maCaches[nPos];
}
+namespace {
+/**
+ * Create combined date and time string according the requirements of Excel.
+ * A single point in time can be represented by concatenating a complete date expression,
+ * the letter T as a delimiter, and a valid time expression. For example, "2007-04-05T14:30".
+ *
+ * fSerialDateTime - a number representing the number of days since 1900-Jan-0 (integer portion of the number),
+ * plus a fractional portion of a 24 hour day (fractional portion of the number).
+ */
+OUString GetExcelFormattedDate( double fSerialDateTime, SvNumberFormatter& rFormatter )
+{
+ //::sax::Converter::convertDateTime(sBuf, (DateTime(rFormatter.GetNullDate()) + fSerialDateTime).GetUNODateTime(), 0, true);
+ css::util::DateTime aUDateTime = (DateTime(rFormatter.GetNullDate()) + fSerialDateTime).GetUNODateTime();
+ // We need to reset nanoseconds, to avoid string like: "1982-02-18T16:04:47.999999849"
+ aUDateTime.NanoSeconds = 0;
+ OUStringBuffer sBuf;
+ ::sax::Converter::convertDateTime(sBuf, aUDateTime, nullptr, true);
+ return sBuf.makeStringAndClear();
+}
+}
+
void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entry& rEntry, sal_Int32 nCounter )
{
assert(rEntry.mpCache);
@@ -235,6 +260,7 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
std::set<ScDPItemData::Type> aDPTypes;
double fMin = std::numeric_limits<double>::infinity(), fMax = -std::numeric_limits<double>::infinity();
bool isValueInteger = true;
+ bool isContainsDate = rCache.IsDateDimension(i);
double intpart;
for (; it != itEnd; ++it)
{
@@ -245,6 +271,7 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
double fVal = it->GetValue();
fMin = std::min(fMin, fVal);
fMax = std::max(fMax, fVal);
+
// Check if all values are integers
if (isValueInteger && (modf(fVal, &intpart) != 0.0))
{
@@ -256,7 +283,8 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
auto aDPTypeEnd = aDPTypes.cend();
auto pAttList = sax_fastparser::FastSerializerHelper::createAttrList();
-
+ // TODO In same cases, disable listing of items, as it is done in MS Excel.
+ // Exporting savePivotCacheRecordsXml method needs to be updated accordingly
bool bListItems = true;
std::set<ScDPItemData::Type> aDPTypesWithoutBlank = aDPTypes;
@@ -276,6 +304,11 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
if (!(isContainsString || (aDPTypes.size() > 1)))
pAttList->add(XML_containsSemiMixedTypes, ToPsz10(false));
+ // OOXTODO: XML_containsNonDate
+
+ if (isContainsDate)
+ pAttList->add(XML_containsDate, ToPsz10(true));
+
// default for containsString field is true, so we are writing only when is false
if (!isContainsString)
pAttList->add(XML_containsString, ToPsz10(false));
@@ -284,7 +317,12 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
if (isContainsBlank)
pAttList->add(XML_containsBlank, ToPsz10(true));
- bool isContainsNumber = aDPTypesWithoutBlank.find(ScDPItemData::Value) != aDPTypesWithoutBlank.end();
+ // If field contain mixed types (Date and Numbers), MS Excel is saving only "minDate" and "maxDate" and not "minValue" and "maxValue"
+ // Example how Excel is saving mixed Date and Numbers:
+ // <sharedItems containsSemiMixedTypes="0" containsDate="1" containsString="0" containsMixedTypes="1" minDate="1900-01-03T22:26:04" maxDate="1900-01-07T14:02:04" />
+ // Example how Excel is saving Dates only:
+ // <sharedItems containsSemiMixedTypes="0" containsNonDate="0" containsDate="1" containsString="0" minDate="1903-08-24T07:40:48" maxDate="2024-05-23T07:12:00"/>
+ bool isContainsNumber = !isContainsDate && aDPTypesWithoutBlank.find(ScDPItemData::Value) != aDPTypesWithoutBlank.end();
if (isContainsNumber)
pAttList->add(XML_containsNumber, ToPsz10(true));
@@ -301,15 +339,12 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
{
pAttList->add(XML_minValue, OString::number(fMin));
pAttList->add(XML_maxValue, OString::number(fMax));
- // If there is only numeric types, then we shouldn't list all items
- if (aDPTypesWithoutBlank.size() == 1)
- bListItems = false;
}
- if (isContainsBlank && (aDPTypes.size() == 1))
+ if (isContainsDate)
{
- // If all items are blank, then we shouldn't list all items
- bListItems = false;
+ pAttList->add(XML_minDate, XclXmlUtils::ToOString(GetExcelFormattedDate(fMin, GetFormatter())));
+ pAttList->add(XML_maxDate, XclXmlUtils::ToOString(GetExcelFormattedDate(fMax, GetFormatter())));
}
if (bListItems)
@@ -334,9 +369,16 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
FSEND);
break;
case ScDPItemData::Value:
- pDefStrm->singleElement(XML_n,
- XML_v, OString::number(rItem.GetValue()),
- FSEND);
+ if (isContainsDate)
+ {
+ pDefStrm->singleElement(XML_d,
+ XML_v, XclXmlUtils::ToOString(GetExcelFormattedDate(rItem.GetValue(), GetFormatter())),
+ FSEND);
+ }
+ else
+ pDefStrm->singleElement(XML_n,
+ XML_v, OString::number(rItem.GetValue()),
+ FSEND);
break;
case ScDPItemData::Empty:
pDefStrm->singleElement(XML_m, FSEND);
More information about the Libreoffice-commits
mailing list