tdf#113908: Implement exporting pivot tables' groups fields to XSLX
Two tests in sc/qa/unit/pivottable_filters_test.cxx were extended
to also test round-trip of group fields in XLSX.
Change-Id: I70b7c15b09040c64fa1da2f88001af7ba16f2c6f
Reviewed-on: https://gerrit.libreoffice.org/69653
Tested-by: Jenkins
Reviewed-by: Mike Kaganski <mike.kaganski@collabora.com>
diff --git a/sc/inc/dputil.hxx b/sc/inc/dputil.hxx
index b2539a1..735843b 100644
--- a/sc/inc/dputil.hxx
+++ b/sc/inc/dputil.hxx
@@ -33,7 +33,7 @@
SC_DLLPUBLIC static OUString createDuplicateDimensionName(const OUString& rOriginal, size_t nDupCount);
static OUString getDateGroupName(
SC_DLLPUBLIC static OUString getDateGroupName(
sal_Int32 nDatePart, sal_Int32 nValue, SvNumberFormatter* pFormatter,
double fStart, double fEnd);
diff --git a/sc/qa/unit/pivottable_filters_test.cxx b/sc/qa/unit/pivottable_filters_test.cxx
index 719b9bc..cc8bcc0 100644
--- a/sc/qa/unit/pivottable_filters_test.cxx
+++ b/sc/qa/unit/pivottable_filters_test.cxx
@@ -50,8 +50,8 @@
void testPivotTableSharedCacheGroupODS();
void testGetPivotDataXLS();
void testPivotTableSharedGroupXLSX();
void testPivotTableSharedDateGroupXLSX();
void testPivotTableSharedNestedDateGroupXLSX();
void testPivotTableSharedDateGroupXLSX(); // + export
void testPivotTableSharedNestedDateGroupXLSX(); // + export
void testPivotTableSharedNumGroupXLSX();
void testPivotTableNoColumnsLayout();
void testTdf112501();
@@ -527,86 +527,99 @@
void ScPivotTableFiltersTest::testPivotTableSharedDateGroupXLSX()
{
ScDocShellRef xDocSh = loadDoc("pivot-table/shared-dategroup.", FORMAT_XLSX);
CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
ScDocument& rDoc = xDocSh->GetDocument();
auto testThis = [](ScDocShellRef& xDocSh) {
CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
ScDocument& rDoc = xDocSh->GetDocument();
// Check whether right date labels are imported for both tables
// First table
CPPUNIT_ASSERT_EQUAL(OUString("a"), rDoc.GetString(ScAddress(0, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1965"), rDoc.GetString(ScAddress(0, 4, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1989"), rDoc.GetString(ScAddress(0, 5, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2000"), rDoc.GetString(ScAddress(0, 6, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2004"), rDoc.GetString(ScAddress(0, 7, 1)));
// TODO: check why this fails with 2005
// CPPUNIT_ASSERT_EQUAL(OUString("2007"), rDoc.GetString(ScAddress(0,8,1)));
// Check whether right date labels are imported for both tables
// First table
CPPUNIT_ASSERT_EQUAL(OUString("a"), rDoc.GetString(ScAddress(0, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1965"), rDoc.GetString(ScAddress(0, 4, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1989"), rDoc.GetString(ScAddress(0, 5, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2000"), rDoc.GetString(ScAddress(0, 6, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2004"), rDoc.GetString(ScAddress(0, 7, 1)));
// TODO: check why this fails with 2005
// CPPUNIT_ASSERT_EQUAL(OUString("2007"), rDoc.GetString(ScAddress(0,8,1)));
// Second table
CPPUNIT_ASSERT_EQUAL(OUString("a"), rDoc.GetString(ScAddress(5, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1965"), rDoc.GetString(ScAddress(5, 4, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1989"), rDoc.GetString(ScAddress(5, 5, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2000"), rDoc.GetString(ScAddress(5, 6, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2004"), rDoc.GetString(ScAddress(5, 7, 1)));
// TODO: check why this fails with 2005
// CPPUNIT_ASSERT_EQUAL(OUString("2007"), rDoc.GetString(ScAddress(5,8,1)));
// Second table
CPPUNIT_ASSERT_EQUAL(OUString("a"), rDoc.GetString(ScAddress(5, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1965"), rDoc.GetString(ScAddress(5, 4, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1989"), rDoc.GetString(ScAddress(5, 5, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2000"), rDoc.GetString(ScAddress(5, 6, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2004"), rDoc.GetString(ScAddress(5, 7, 1)));
// TODO: check why this fails with 2005
// CPPUNIT_ASSERT_EQUAL(OUString("2007"), rDoc.GetString(ScAddress(5,8,1)));
// There should be exactly 2 pivot tables and 1 cache.
ScDPCollection* pDPs = rDoc.GetDPCollection();
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(2), pDPs->GetCount());
// There should be exactly 2 pivot tables and 1 cache.
ScDPCollection* pDPs = rDoc.GetDPCollection();
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(2), pDPs->GetCount());
ScDPCollection::SheetCaches& rSheetCaches = pDPs->GetSheetCaches();
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), rSheetCaches.size());
ScDPCollection::SheetCaches& rSheetCaches = pDPs->GetSheetCaches();
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), rSheetCaches.size());
const ScDPCache* pCache = rSheetCaches.getExistingCache(ScRange(0, 0, 0, 9, 24, 0));
CPPUNIT_ASSERT_MESSAGE("Pivot cache is expected for A1:J25 on the first sheet.", pCache);
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(10), pCache->GetFieldCount());
xDocSh->DoClose();
const ScDPCache* pCache = rSheetCaches.getExistingCache(ScRange(0, 0, 0, 9, 24, 0));
CPPUNIT_ASSERT_MESSAGE("Pivot cache is expected for A1:J25 on the first sheet.", pCache);
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(10), pCache->GetFieldCount());
};
ScDocShellRef xDocSh1 = loadDoc("pivot-table/shared-dategroup.", FORMAT_XLSX);
testThis(xDocSh1);
// Now test round-trip of group fields
ScDocShellRef xDocSh2 = saveAndReload(xDocSh1.get(), FORMAT_XLSX);
testThis(xDocSh2);
xDocSh2->DoClose();
xDocSh1->DoClose();
}
void ScPivotTableFiltersTest::testPivotTableSharedNestedDateGroupXLSX()
{
ScDocShellRef xDocSh = loadDoc("pivot-table/shared-nested-dategroup.", FORMAT_XLSX);
CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
ScDocument& rDoc = xDocSh->GetDocument();
auto testThis = [](ScDocShellRef& xDocSh) {
CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
ScDocument& rDoc = xDocSh->GetDocument();
// Check whether right date groups are imported for both tables
// First table
CPPUNIT_ASSERT_EQUAL(OUString("Years"), rDoc.GetString(ScAddress(0, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1965"), rDoc.GetString(ScAddress(0, 4, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1989"), rDoc.GetString(ScAddress(0, 11, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2000"), rDoc.GetString(ScAddress(0, 18, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2004"), rDoc.GetString(ScAddress(0, 21, 1)));
// TODO: check why this fails with the empty string
//CPPUNIT_ASSERT_EQUAL(OUString("2007"), rDoc.GetString(ScAddress(0,32,1)));
CPPUNIT_ASSERT_EQUAL(OUString("Quarters"), rDoc.GetString(ScAddress(1, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("a"), rDoc.GetString(ScAddress(2, 3, 1)));
// Check whether right date groups are imported for both tables
// First table
CPPUNIT_ASSERT_EQUAL(OUString("Years"), rDoc.GetString(ScAddress(0, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1965"), rDoc.GetString(ScAddress(0, 4, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1989"), rDoc.GetString(ScAddress(0, 11, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2000"), rDoc.GetString(ScAddress(0, 18, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2004"), rDoc.GetString(ScAddress(0, 21, 1)));
// TODO: check why this fails with the empty string
//CPPUNIT_ASSERT_EQUAL(OUString("2007"), rDoc.GetString(ScAddress(0,32,1)));
CPPUNIT_ASSERT_EQUAL(OUString("Quarters"), rDoc.GetString(ScAddress(1, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("a"), rDoc.GetString(ScAddress(2, 3, 1)));
// Second table
CPPUNIT_ASSERT_EQUAL(OUString("Years"), rDoc.GetString(ScAddress(6, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1965"), rDoc.GetString(ScAddress(6, 4, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1989"), rDoc.GetString(ScAddress(6, 11, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2000"), rDoc.GetString(ScAddress(6, 18, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2004"), rDoc.GetString(ScAddress(6, 21, 1)));
// TODO: check why this fails with the empty string
//CPPUNIT_ASSERT_EQUAL(OUString("2007"), rDoc.GetString(ScAddress(6,31,1)));
CPPUNIT_ASSERT_EQUAL(OUString("Quarters"), rDoc.GetString(ScAddress(7, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("a"), rDoc.GetString(ScAddress(8, 3, 1)));
// Second table
CPPUNIT_ASSERT_EQUAL(OUString("Years"), rDoc.GetString(ScAddress(6, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1965"), rDoc.GetString(ScAddress(6, 4, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("1989"), rDoc.GetString(ScAddress(6, 11, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2000"), rDoc.GetString(ScAddress(6, 18, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("2004"), rDoc.GetString(ScAddress(6, 21, 1)));
// TODO: check why this fails with the empty string
//CPPUNIT_ASSERT_EQUAL(OUString("2007"), rDoc.GetString(ScAddress(6,31,1)));
CPPUNIT_ASSERT_EQUAL(OUString("Quarters"), rDoc.GetString(ScAddress(7, 3, 1)));
CPPUNIT_ASSERT_EQUAL(OUString("a"), rDoc.GetString(ScAddress(8, 3, 1)));
// There should be exactly 2 pivot tables and 1 cache.
ScDPCollection* pDPs = rDoc.GetDPCollection();
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(2), pDPs->GetCount());
// There should be exactly 2 pivot tables and 1 cache.
ScDPCollection* pDPs = rDoc.GetDPCollection();
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(2), pDPs->GetCount());
ScDPCollection::SheetCaches& rSheetCaches = pDPs->GetSheetCaches();
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), rSheetCaches.size());
ScDPCollection::SheetCaches& rSheetCaches = pDPs->GetSheetCaches();
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), rSheetCaches.size());
const ScDPCache* pCache = rSheetCaches.getExistingCache(ScRange(0, 0, 0, 9, 24, 0));
CPPUNIT_ASSERT_MESSAGE("Pivot cache is expected for A1:J25 on the first sheet.", pCache);
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(10), pCache->GetFieldCount());
// Two new group field is created
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(2), pCache->GetGroupFieldCount());
const ScDPCache* pCache = rSheetCaches.getExistingCache(ScRange(0, 0, 0, 9, 24, 0));
CPPUNIT_ASSERT_MESSAGE("Pivot cache is expected for A1:J25 on the first sheet.", pCache);
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(10), pCache->GetFieldCount());
// Two new group field is created
CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(2), pCache->GetGroupFieldCount());
};
xDocSh->DoClose();
ScDocShellRef xDocSh1 = loadDoc("pivot-table/shared-nested-dategroup.", FORMAT_XLSX);
testThis(xDocSh1);
// Now test round-trip of group fields
ScDocShellRef xDocSh2 = saveAndReload(xDocSh1.get(), FORMAT_XLSX);
testThis(xDocSh2);
xDocSh2->DoClose();
xDocSh1->DoClose();
}
void ScPivotTableFiltersTest::testPivotTableSharedNumGroupXLSX()
diff --git a/sc/source/core/data/dpobject.cxx b/sc/source/core/data/dpobject.cxx
index 97644f1b..3089d65 100644
--- a/sc/source/core/data/dpobject.cxx
+++ b/sc/source/core/data/dpobject.cxx
@@ -1231,6 +1231,11 @@
}
}
}
else if (ScDPTableData* pData = GetTableData())
{
aRet = pData->getDimensionName(nDim);
rIsDataLayout = pData->getIsDataLayoutDimension(nDim);
}
return aRet;
}
diff --git a/sc/source/filter/excel/xepivotxml.cxx b/sc/source/filter/excel/xepivotxml.cxx
index 69c98d1..6f98ffd 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -9,6 +9,7 @@
#include <xepivotxml.hxx>
#include <dpcache.hxx>
#include <dpdimsave.hxx>
#include <dpitemdata.hxx>
#include <dpobject.hxx>
#include <dpsave.hxx>
@@ -22,6 +23,7 @@
#include <sax/tools/converter.hxx>
#include <sax/fastattribs.hxx>
#include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
#include <com/sun/star/sheet/DataPilotFieldOrientation.hpp>
#include <com/sun/star/sheet/DataPilotFieldLayoutMode.hpp>
#include <com/sun/star/sheet/DataPilotOutputRangeType.hpp>
@@ -239,10 +241,74 @@
pDefStrm->endElement(XML_cacheSource);
size_t nCount = rCache.GetFieldCount();
const size_t nGroupFieldCount = rCache.GetGroupFieldCount();
pDefStrm->startElement(XML_cacheFields,
XML_count, OString::number(static_cast<long>(nCount)).getStr(),
XML_count, OString::number(static_cast<long>(nCount + nGroupFieldCount)).getStr(),
FSEND);
auto WriteFieldGroup = [this, &rCache, pDefStrm](size_t i, size_t base) {
const sal_Int32 nDatePart = rCache.GetGroupType(i);
if (!nDatePart)
return;
OString sGroupBy;
switch (nDatePart)
{
case sheet::DataPilotFieldGroupBy::SECONDS:
sGroupBy = "seconds";
break;
case sheet::DataPilotFieldGroupBy::MINUTES:
sGroupBy = "minutes";
break;
case sheet::DataPilotFieldGroupBy::HOURS:
sGroupBy = "hours";
break;
case sheet::DataPilotFieldGroupBy::DAYS:
sGroupBy = "days";
break;
case sheet::DataPilotFieldGroupBy::MONTHS:
sGroupBy = "months";
break;
case sheet::DataPilotFieldGroupBy::QUARTERS:
sGroupBy = "quarters";
break;
case sheet::DataPilotFieldGroupBy::YEARS:
sGroupBy = "years";
break;
}
// fieldGroup element
pDefStrm->startElement(XML_fieldGroup, XML_base, OString::number(base), FSEND);
SvNumberFormatter& rFormatter = GetFormatter();
// rangePr element
const ScDPNumGroupInfo* pGI = rCache.GetNumGroupInfo(i);
auto pGroupAttList = sax_fastparser::FastSerializerHelper::createAttrList();
pGroupAttList->add(XML_groupBy, sGroupBy);
// Possible TODO: find out when to write autoStart attribute for years grouping
pGroupAttList->add(XML_startDate, GetExcelFormattedDate(pGI->mfStart, rFormatter).toUtf8());
pGroupAttList->add(XML_endDate, GetExcelFormattedDate(pGI->mfEnd, rFormatter).toUtf8());
if (pGI->mfStep)
pGroupAttList->add(XML_groupInterval, OString::number(pGI->mfStep));
pDefStrm->singleElement(XML_rangePr, pGroupAttList);
// groupItems element
ScfInt32Vec aGIIds;
rCache.GetGroupDimMemberIds(i, aGIIds);
pDefStrm->startElement(XML_groupItems, XML_count, OString::number(aGIIds.size()), FSEND);
for (auto nGIId : aGIIds)
{
const ScDPItemData* pGIData = rCache.GetItemDataById(i, nGIId);
if (pGIData->GetType() == ScDPItemData::GroupValue)
{
OUString sVal = rCache.GetFormattedString(i, *pGIData, false);
pDefStrm->singleElement(XML_s, XML_v, sVal.toUtf8(), FSEND);
}
}
pDefStrm->endElement(XML_groupItems);
pDefStrm->endElement(XML_fieldGroup);
};
for (size_t i = 0; i < nCount; ++i)
{
OUString aName = rCache.GetDimensionName(i);
@@ -387,7 +453,7 @@
XML_v, rItem.GetString().toUtf8(),
FSEND);
break;
case ScDPItemData::GroupValue:
case ScDPItemData::GroupValue: // Should not happen here!
case ScDPItemData::RangeStart:
// TODO : What do we do with these types?
pDefStrm->singleElement(XML_m, FSEND);
@@ -399,6 +465,28 @@
}
pDefStrm->endElement(XML_sharedItems);
WriteFieldGroup(i, i);
pDefStrm->endElement(XML_cacheField);
}
ScDPObject* pDPObject
= rCache.GetAllReferences().empty() ? nullptr : *rCache.GetAllReferences().begin();
for (size_t i = nCount; pDPObject && i < nCount + nGroupFieldCount; ++i)
{
const OUString aName = pDPObject->GetDimName(i, o3tl::temporary(bool()));
ScDPSaveData* pSaveData = pDPObject->GetSaveData();
assert(pSaveData);
const ScDPSaveGroupDimension* pDim = pSaveData->GetDimensionData()->GetNamedGroupDim(aName);
assert(pDim);
const size_t nBase = rCache.GetDimensionIndex(pDim->GetSourceDimName());
pDefStrm->startElement(XML_cacheField, XML_name, aName.toUtf8(), XML_numFmtId,
OString::number(0).getStr(), XML_databaseField, ToPsz10(false),
FSEND);
WriteFieldGroup(i, nBase);
pDefStrm->endElement(XML_cacheField);
}
@@ -589,14 +677,14 @@
const ScDPSaveData& rSaveData = *rDPObj.GetSaveData();
size_t nFieldCount = rCache.GetFieldCount();
size_t nFieldCount = rCache.GetFieldCount() + rCache.GetGroupFieldCount();
std::vector<const ScDPSaveDimension*> aCachedDims;
NameToIdMapType aNameToIdMap;
aCachedDims.reserve(nFieldCount);
for (size_t i = 0; i < nFieldCount; ++i)
{
OUString aName = rCache.GetDimensionName(i);
OUString aName = const_cast<ScDPObject&>(rDPObj).GetDimName(i, o3tl::temporary(bool()));
aNameToIdMap.emplace(aName, aCachedDims.size());
const ScDPSaveDimension* pDim = rSaveData.GetExistingDimensionByName(aName);
aCachedDims.push_back(pDim);
@@ -790,19 +878,32 @@
dpo.GetMembers(i, dpo.GetUsedHierarchy(i), aMembers);
}
const ScDPCache::ScDPItemDataVec& rCacheFieldItems = rCache.GetDimMemberValues(i);
std::vector<const ScDPItemData*> rCacheFieldItems;
if (i < rCache.GetFieldCount() && !rCache.GetGroupType(i))
for (const auto& it : rCache.GetDimMemberValues(i))
rCacheFieldItems.push_back(&it);
else
{
ScfInt32Vec aGIIds;
rCache.GetGroupDimMemberIds(i, aGIIds);
for (const sal_Int32 id : aGIIds)
rCacheFieldItems.push_back(rCache.GetItemDataById(i, id));
}
// The pair contains the member index in cache and if it is hidden
std::vector< std::pair<size_t, bool> > aMemberSequence;
std::set<size_t> aUsedCachePositions;
for (const auto & rMember : aMembers)
{
auto it = std::find_if(rCacheFieldItems.begin(), rCacheFieldItems.end(),
[&rDPObj, &pDim, &rMember](const ScDPItemData& rItem) {
[&rDPObj, &pDim, &rMember, &rCache, i](const ScDPItemData* pItem) {
OUString sFormattedName;
if (rItem.HasStringData() || rItem.IsEmpty())
sFormattedName = rItem.GetString();
if (pItem->GetType() == ScDPItemData::GroupValue)
sFormattedName = rCache.GetFormattedString(i, *pItem, false);
else if (pItem->HasStringData() || pItem->IsEmpty())
sFormattedName = pItem->GetString();
else
sFormattedName = const_cast<ScDPObject&>(rDPObj).GetFormattedString(pDim->GetName(), rItem.GetValue());
sFormattedName = const_cast<ScDPObject&>(rDPObj).GetFormattedString(
pDim->GetName(), pItem->GetValue());
return sFormattedName == rMember.maName;
});
if (it != rCacheFieldItems.end())
