sc/qa/unit/data/ods/tdf123225_pivotTable_no_col_items.ods |binary
sc/qa/unit/pivottable_filters_test.cxx | 76 +++++++++++++-
sc/source/filter/excel/xepivotxml.cxx | 35 +++++-
3 files changed, 104 insertions(+), 7 deletions(-)
New commits:
commit 3b3ba9c329b691ef600ad9ecc419e818b385ce0e
Author: Bayram Çiçek <bayram.ci...@collabora.com>
AuthorDate: Wed Feb 26 18:33:06 2025 +0300
Commit: Caolán McNamara <caolan.mcnam...@collabora.com>
CommitDate: Thu Feb 27 09:53:36 2025 +0100
tdf#123225 - export <i t="grand"> of row/colItems
in xl/pivotTables/pivotTable*.xml.
- we should always export (.ods to .xlsx) <rowItems> and <colItems>, even if
the pivot table does not contain col/row items. Otherwise:
1) Excel will fail to open the xlsx document, you will get an error
message.
2) Excel will open the file without any errors but:
2.1) context menu -by right clicking on the pivot table- will have less
or more items than it should have after "refresh".
2.2) if e.g. trying to sort the items you will get "Cannot determine
which PivotTable field to sort by" warning.
- we should add t="grand" to the last <i> element. Otherwise, Excel will not
have all functions in the context menu of the pivot table. Especially for
the
"Grand Total" column/row cells. we shouldn't need "refresh" to use all
functions/items in the context menu.
- note: it is not completely clear that the last <i> element always gets
t="grand". however, testing on the some docs indicate that t="grand"
should be in the last <i> element.
- added testTdf123225PivotTableNoColItems unit test to check if
<i t="grand"> exported. also, added some useful information in the
comment section for future tests/issues.
Signed-off-by: Bayram Çiçek <bayram.ci...@collabora.com>
Change-Id: I22b42ddb4a27c4b7b88ad8a71376c2c82d5a735a
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/182242
Tested-by: Jenkins
Tested-by: Caolán McNamara <caolan.mcnam...@collabora.com>
Reviewed-by: Caolán McNamara <caolan.mcnam...@collabora.com>
diff --git a/sc/qa/unit/data/ods/tdf123225_pivotTable_no_col_items.ods
b/sc/qa/unit/data/ods/tdf123225_pivotTable_no_col_items.ods
new file mode 100644
index 000000000000..0e2b601efa44
Binary files /dev/null and
b/sc/qa/unit/data/ods/tdf123225_pivotTable_no_col_items.ods differ
diff --git a/sc/qa/unit/pivottable_filters_test.cxx
b/sc/qa/unit/pivottable_filters_test.cxx
index 276dcd324248..fe33c66d447c 100644
--- a/sc/qa/unit/pivottable_filters_test.cxx
+++ b/sc/qa/unit/pivottable_filters_test.cxx
@@ -133,8 +133,24 @@ CPPUNIT_TEST_FIXTURE(ScPivotTableFiltersTest,
testTdf123225PivotTableRowColItems
xmlDocUniquePtr pSheet =
parseExport(u"xl/pivotTables/pivotTable1.xml"_ustr);
CPPUNIT_ASSERT(pSheet);
- // be sure that we have <rowItems> and <colItems> element
- // under <pivotTableDefinition> after export of the .ods to .xlsx
+ /*
+ - be sure that we have <rowItems> and <colItems> element
+ under <pivotTableDefinition> after export of the .ods to .xlsx
+
+ otherwise:
+ 1) Excel will fail to open the xlsx document, you will get an
error message.
+ 2) Excel will open the file without any errors but:
+ 2.1) context menu -by right clicking on the pivot table- will
have
+ less or more items than it should have after "refresh".
+ 2.2) if e.g. trying to sort the items you will get "Cannot
determine
+ which PivotTable field to sort by" warning.
+
+ - after exporting the .ods as .xlsx and opening the .xlsx document in
Excel:
+ the count attribute of rowItems(or colItems) should have the expected
value.
+ otherwise, context menu on the pivot table will have less/more items
+ than it should have after "refresh". we shouldn't need "refresh" to
use all
+ functions/items in the context menu.
+ */
// Row items <rowItems>
@@ -161,6 +177,62 @@ CPPUNIT_TEST_FIXTURE(ScPivotTableFiltersTest,
testTdf123225PivotTableRowColItems
assertXPath(pSheet, "/x:pivotTableDefinition/x:colItems/x:i[1]/x:x", "v",
u"0");
}
+CPPUNIT_TEST_FIXTURE(ScPivotTableFiltersTest,
testTdf123225PivotTableNoColItems)
+{
+ createScDoc("ods/tdf123225_pivotTable_no_col_items.ods");
+ save(u"Calc Office Open XML"_ustr);
+
+ xmlDocUniquePtr pSheet =
parseExport(u"xl/pivotTables/pivotTable1.xml"_ustr);
+ CPPUNIT_ASSERT(pSheet);
+
+ /*
+ - after exporting the .ods to .xlsx, we should have t="grand" in the
+ last <i> element of the <rowItems> and <colItems>. Otherwise, Excel
will not
+ have all functions in the context menu of the pivot table. Especially
for the
+ "Grand Total" column/row cells.
+
+ - additionally, we should always export a single <rowItems> and
<colItems> regardless of
+ whether the document has row/col items. Otherwise, in Excel, context
menu on some
+ cells of the pivot table, will not have the expected context menu
items.
+
+ - this tdf123225_pivotTable_no_col_items.ods document does not have
+ axisCol (DataPilotFieldOrientation_COLUMN) and that means <colItems>
should not exist
+ during export. But in Excel, if pivot table refreshed, there will be
+
+ <colItems count="1">
+ <i/>
+ </colItems>
+
+ in the xl/pivotTables/pivotTable1.xml. So, that means we should export
<colItems>
+ without checking the existence of it.
+ For the sake of completeness, <colItems> exported as:
+
+ <colItems count="1">
+ <i t="grand">
+ <x v="0"/>
+ </i>
+ </colItems>
+ */
+
+ // Row items <rowItems>
+
+ // check if <rowItems count="3">
+ assertXPath(pSheet, "/x:pivotTableDefinition/x:rowItems", "count", u"3");
+ // check if last <i> element, has t="grand"
+ assertXPath(pSheet, "/x:pivotTableDefinition/x:rowItems/x:i[last()]", "t",
u"grand");
+
+ // Column items <colItems>
+
+ // check if <colItems count="1">
+ assertXPath(pSheet, "/x:pivotTableDefinition/x:colItems", "count", u"1");
+ // check if <colItems> has only a single <i> element
+ assertXPath(pSheet, "/x:pivotTableDefinition/x:colItems/x:i", 1);
+ // check if <i> element has t="grand"
+ assertXPath(pSheet, "/x:pivotTableDefinition/x:colItems/x:i", "t",
u"grand");
+ // check if <i> has <x v="0"/>
+ assertXPath(pSheet, "/x:pivotTableDefinition/x:colItems/x:i/x:x", "v",
u"0");
+}
+
CPPUNIT_TEST_FIXTURE(ScPivotTableFiltersTest,
testPivotTableNamedRangeSourceODS)
{
createScDoc("ods/pivot-table-named-range-source.ods");
diff --git a/sc/source/filter/excel/xepivotxml.cxx
b/sc/source/filter/excel/xepivotxml.cxx
index c0c9946fa8b5..65e5d9c61839 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -776,8 +776,11 @@ void XclExpXmlPivotTables::SavePivotTableXml(
XclExpXmlStream& rStrm, const ScDP
std::vector<tools::Long> aPageFields;
std::vector<DataField> aDataFields;
- tools::Long nRowItemsCount = 0; // for <rowItems count="..."> of
pivotTable*.xml
- tools::Long nColItemsCount = 0; // for <colItems count="..."> of
pivotTable*.xml
+ // we should always export <rowItems> and <colItems>, even if the pivot
table
+ // does not contain col/row items. otherwise, in Excel, pivot table will
not
+ // have all context menu items.
+ tools::Long nRowItemsCount = 1; // for <rowItems count="..."> of
pivotTable*.xml
+ tools::Long nColItemsCount = 1; // for <colItems count="..."> of
pivotTable*.xml
tools::Long nDataDimCount = rSaveData.GetDataDimensionCount();
// Use dimensions in the save data to get their correct ordering.
// Dimension order here is significant as they specify the order of
@@ -1064,7 +1067,7 @@ void XclExpXmlPivotTables::SavePivotTableXml(
XclExpXmlStream& rStrm, const ScDP
if (strcmp(toOOXMLAxisType(eOrient), "axisCol") == 0)
nColItemsCount = nItemsCount;
- else
+ else if (strcmp(toOOXMLAxisType(eOrient), "axisRow") == 0)
nRowItemsCount = nItemsCount;
for (const auto & nMember : aMemberSequence)
@@ -1110,7 +1113,18 @@ void XclExpXmlPivotTables::SavePivotTableXml(
XclExpXmlStream& rStrm, const ScDP
// export nRowItemsCount times <i> and <x> elements
for (tools::Long nCount = 0; nCount < nRowItemsCount; ++nCount)
{
- pPivotStrm->startElement(XML_i);
+ /* we should add t="grand" to the last <i> element. Otherwise,
Excel will not
+ have all functions in the context menu of the pivot table.
Especially for the
+ "Grand Total" column/row cells.
+
+ note: it is not completely clear that the last <i> element always
gets t="grand".
+ however, testing on the some docs indicate that t="grand" should be
+ in the last element, so let's try this here. */
+ if (nCount == nRowItemsCount - 1)
+ pPivotStrm->startElement(XML_i, XML_t, "grand");
+ else
+ pPivotStrm->startElement(XML_i);
+
pPivotStrm->singleElement(XML_x, XML_v, OString::number(nCount));
pPivotStrm->endElement(XML_i);
}
@@ -1141,7 +1155,18 @@ void XclExpXmlPivotTables::SavePivotTableXml(
XclExpXmlStream& rStrm, const ScDP
// export nColItemsCount times <i> and <x> elements
for (tools::Long nCount = 0; nCount < nColItemsCount; ++nCount)
{
- pPivotStrm->startElement(XML_i);
+ /* we should add t="grand" to the last <i> element. Otherwise,
Excel will not
+ have all functions in the context menu of the pivot table.
Especially for the
+ "Grand Total" column/row cells.
+
+ note: it is not completely clear that the last <i> element always
gets t="grand".
+ however, testing on the some docs indicate that t="grand" should be
+ in the last element, so let's try this here. */
+ if (nCount == nColItemsCount - 1)
+ pPivotStrm->startElement(XML_i, XML_t, "grand");
+ else
+ pPivotStrm->startElement(XML_i);
+
pPivotStrm->singleElement(XML_x, XML_v, OString::number(nCount));
pPivotStrm->endElement(XML_i);
}
