summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--sc/qa/unit/data/xlsx/pivot.xlsxbin0 -> 12769 bytes
-rw-r--r--sc/qa/unit/subsequent_export-test.cxx69
-rw-r--r--sc/source/filter/excel/xepivotxml.cxx79
3 files changed, 131 insertions, 17 deletions
diff --git a/sc/qa/unit/data/xlsx/pivot.xlsx b/sc/qa/unit/data/xlsx/pivot.xlsx
new file mode 100644
index 000000000000..e6297a91777b
--- /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 8823ece151c1..467e3bb2ff23 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 9f1da86dcb9b..c5872e24da4a 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,31 +245,76 @@ 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;
- if (bListItems)
+
+ 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_count, OString::number(static_cast<long>(rFieldItems.size())));
+ 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;
}
- else
+
+ if (isContainsBlank && (aDPTypes.size() == 1))
{
- 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));
- }
+ // 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())));
}
sax_fastparser::XFastAttributeListRef xAttributeList(pAttList);
generated by cgit (git 2.34.1) at 2025-02-27 13:39:40 +0000