tdf#89139 Fix PivotCache fields according to OOXML specification
Apply changes to fields:
- XML_containsInteger
- XML_containsBlank
- XML_containsMixedTypes
- XML_containsSemiMixedTypes
According to OOXML specification
https://technet.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.shareditems.aspx
Generally OOXML specification allows listing items for mixed items (example: STRING + NUMBERS).
This patch is fixing that.
Example of mixed types:
<cacheField name="pwdLastSet" numFmtId="0">
<sharedItems containsDate="1" containsBlank="1" containsMixedTypes="1" minDate="2014-07-07T09:30:31" maxDate="2017-03-24T08:38:46" count="4">
<m/>
<d v="2016-06-15T12:18:34"/>
<s v=""/>
<d v="2017-03-21T10:27:39"/>
</sharedItems>
</cacheField>
Change-Id: I02b07c79bea60890e3c995dd70cb5c72901a3d4a
Reviewed-on: https://gerrit.libreoffice.org/40610
Reviewed-by: Bartosz Kosiorek <gang65@poczta.onet.pl>
Tested-by: Bartosz Kosiorek <gang65@poczta.onet.pl>
diff --git a/sc/qa/unit/data/xlsx/pivot.xlsx b/sc/qa/unit/data/xlsx/pivot.xlsx
new file mode 100644
index 0000000..e6297a9
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot.xlsx
Binary files differ
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 8823ece..467e3bb 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -112,7 +112,7 @@ public:
void testCellNoteExportXLS();
void testFormatExportODS();
void testPivotExportXLSX();
void testCommentExportXLSX();
#if HAVE_MORE_FONTS
void testCustomColumnWidthExportXLSX();
@@ -223,6 +223,7 @@ public:
CPPUNIT_TEST(testCellNoteExportXLS);
CPPUNIT_TEST(testFormatExportODS);
CPPUNIT_TEST(testPivotExportXLSX);
CPPUNIT_TEST(testCommentExportXLSX);
#if HAVE_MORE_FONTS
CPPUNIT_TEST(testCustomColumnWidthExportXLSX);
@@ -538,6 +539,72 @@ void ScExportTest::testFormatExportODS()
xDocSh->DoClose();
}
void ScExportTest::testPivotExportXLSX()
{
// 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:
// containsMixedTypes, containsSemiMixedTypes, containsInteger, containsBlank
// If it is not properly set, then Excel is not opening spreadsheet properly.
// This test case ensures, that such values are properly set according to documentaion:
// https://technet.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.shareditems.aspx
ScDocShellRef xShell = loadDoc("pivot.", 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);
assertXPath(pSheet, "/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");
// 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
// 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");
// 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
}
void ScExportTest::testCommentExportXLSX()
{
diff --git a/sc/source/filter/excel/xepivotxml.cxx b/sc/source/filter/excel/xepivotxml.cxx
index 9f1da86..c5872e2 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -234,6 +234,8 @@ 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;
double intpart;
for (; it != itEnd; ++it)
{
ScDPItemData::Type eType = it->GetType();
@@ -243,32 +245,77 @@ 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))
{
isValueInteger = false;
}
}
}
auto aDPTypeEnd = aDPTypes.cend();
auto pAttList = sax_fastparser::FastSerializerHelper::createAttrList();
// tdf#89139: Only create item list for string-only fields.
// Using containsXXX attributes in this case makes Excel think the file is corrupted.
// OTOH listing items for e.g. number fields also triggers "corrupted" warning in Excel.
bool bListItems = aDPTypes.size() == 1 && aDPTypes.find(ScDPItemData::String) != aDPTypeEnd;
bool bListItems = true;
std::set<ScDPItemData::Type> aDPTypesWithoutBlank = aDPTypes;
aDPTypesWithoutBlank.erase(ScDPItemData::Empty);
bool isContainsMoreThanOneType = aDPTypesWithoutBlank.size() > 1;
// XML_containsMixedType possible values:
// 1 - field contains more than one data type
// 0 - (Default) only one data type. The field can still contain blank values (that's why we are using aDPTypesWithoutBlank)
if (isContainsMoreThanOneType)
pAttList->add(XML_containsMixedTypes, ToPsz10(true));
bool isContainsString = aDPTypesWithoutBlank.find(ScDPItemData::String) != aDPTypesWithoutBlank.end();
// XML_containsSemiMixedTypes possible values:
// 1 - (Default) at least one text value, or can also contain a mix of other data types and blank values
// 0 - the field does not have a mix of text and other values
if (!(isContainsString || (aDPTypes.size() > 1)))
pAttList->add(XML_containsSemiMixedTypes, ToPsz10(false));
// default for containsString field is true, so we are writing only when is false
if (!isContainsString)
pAttList->add(XML_containsString, ToPsz10(false));
bool isContainsBlank = aDPTypes.find(ScDPItemData::Empty) != aDPTypeEnd;
if (isContainsBlank)
pAttList->add(XML_containsBlank, ToPsz10(true));
bool isContainsNumber = aDPTypesWithoutBlank.find(ScDPItemData::Value) != aDPTypesWithoutBlank.end();
if (isContainsNumber)
pAttList->add(XML_containsNumber, ToPsz10(true));
if (isValueInteger && isContainsNumber)
pAttList->add(XML_containsInteger, ToPsz10(true));
// Number type fields could be mixed with blank types, and it shouldn't be treated as listed items.
// Example:
// <cacheField name="employeeID" numFmtId="0">
// <sharedItems containsString="0" containsBlank="1" containsNumber="1" containsInteger="1" minValue="35" maxValue="89"/>
// </cacheField>
if (isContainsNumber)
{
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 all items are blank, then we shouldn't list all items
bListItems = false;
}
if (bListItems)
{
pAttList->add(XML_count, OString::number(static_cast<long>(rFieldItems.size())));
}
else
{
pAttList->add(XML_containsMixedTypes, ToPsz10(aDPTypes.size() > 1));
pAttList->add(XML_containsSemiMixedTypes, ToPsz10(aDPTypes.size() > 1));
pAttList->add(XML_containsString, ToPsz10(aDPTypes.find(ScDPItemData::String) != aDPTypeEnd));
if (aDPTypes.find(ScDPItemData::Value) != aDPTypeEnd)
{
pAttList->add(XML_containsNumber, ToPsz10(true));
pAttList->add(XML_minValue, OString::number(fMin));
pAttList->add(XML_maxValue, OString::number(fMax));
}
}
sax_fastparser::XFastAttributeListRef xAttributeList(pAttList);
pDefStrm->startElement(XML_sharedItems, xAttributeList);
