tdf#107711:Pivot table: filtering of non-string fields is not exported to XLSX

Same issue what we have in case of XLS filter. We need to have
the right string representation of pivot field items.

Change-Id: Ifb686ad268c61b03c7dcccc66f65e6f8247eab4f
Reviewed-on: https://gerrit.libreoffice.org/42956
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Tamás Zolnai <tamas.zolnai@collabora.com>

10 files changed, 390 insertions, 8 deletions

diff --git a/sc/qa/unit/data/xlsx/pivottable_bool_field_filter.xlsx b/sc/qa/unit/data/xlsx/pivottable_bool_field_filter.xlsx
new file mode 100755
index 000000000000..8fcdb1bec102
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivottable_bool_field_filter.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivottable_date_field_filter.xlsx b/sc/qa/unit/data/xlsx/pivottable_date_field_filter.xlsx
new file mode 100755
index 000000000000..316f201a5043
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivottable_date_field_filter.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivottable_double_field_filter.xlsx b/sc/qa/unit/data/xlsx/pivottable_double_field_filter.xlsx
new file mode 100755
index 000000000000..f2d3597b273c
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivottable_double_field_filter.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivottable_rowcolpage_field_filter.xlsx b/sc/qa/unit/data/xlsx/pivottable_rowcolpage_field_filter.xlsx
new file mode 100755
index 000000000000..4aaa32794519
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivottable_rowcolpage_field_filter.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivottable_string_field_filter.xlsx b/sc/qa/unit/data/xlsx/pivottable_string_field_filter.xlsx
new file mode 100755
index 000000000000..3bb6a77bcb79
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivottable_string_field_filter.xlsx
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index aad799b90285..544d3623fce1 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -220,6 +220,11 @@ public:
     void testPivotTableEmptyItem();
     void testPivotTablePageFieldFilter();
     void testPivotTableFirstHeaderRowXLSX();
+    void testPivotTableDoubleFieldFilterXLSX();
+    void testPivotTableStringFieldFilterXLSX();
+    void testPivotTableDateFieldFilterXLSX();
+    void testPivotTableBoolFieldFilterXLSX();
+    void testPivotTableRowColPageFieldFilterXLSX();
 
     CPPUNIT_TEST_SUITE(ScExportTest);
     CPPUNIT_TEST(test);
@@ -334,6 +339,11 @@ public:
     CPPUNIT_TEST(testPivotTableEmptyItem);
     CPPUNIT_TEST(testPivotTablePageFieldFilter);
     CPPUNIT_TEST(testPivotTableFirstHeaderRowXLSX);
+    CPPUNIT_TEST(testPivotTableDoubleFieldFilterXLSX);
+    CPPUNIT_TEST(testPivotTableStringFieldFilterXLSX);
+    CPPUNIT_TEST(testPivotTableDateFieldFilterXLSX);
+    CPPUNIT_TEST(testPivotTableBoolFieldFilterXLSX);
+    CPPUNIT_TEST(testPivotTableRowColPageFieldFilterXLSX);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -4878,6 +4888,337 @@ void ScExportTest::testPivotTableFirstHeaderRowXLSX()
     assertXPath(pTable, "/x:pivotTableDefinition/x:location", "firstHeaderRow", "1");
 }
 
+void ScExportTest::testPivotTableDoubleFieldFilterXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_double_field_filter.", FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rDoc = xDocSh->GetDocument();
+    ScDPCollection* pDPs = rDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(3), pDPs->GetCount());
+
+    // Reload and check filtering of row dimensions
+    xDocSh = saveAndReload( &(*xDocSh), FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rLoadedDoc = xDocSh->GetDocument();
+    pDPs = rLoadedDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(3), pDPs->GetCount());
+
+    // Field with general formatting
+    {
+        const ScDPObject* pDPObj = &(*pDPs)[0];
+        CPPUNIT_ASSERT(pDPObj);
+        ScDPSaveData* pSaveData = pDPObj->GetSaveData();
+        CPPUNIT_ASSERT(pSaveData);
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Double field1");
+        CPPUNIT_ASSERT(pSaveDim);
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        auto aIter = rMembers.begin();
+        ScDPSaveMember* pMember = *aIter; // "1"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+        ++aIter;
+        pMember = *aIter; // "2"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        ++aIter;
+        pMember = *aIter; // "3"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+    }
+
+    // Number formatting
+    {
+        const ScDPObject* pDPObj = &(*pDPs)[1];
+        CPPUNIT_ASSERT(pDPObj);
+        ScDPSaveData* pSaveData = pDPObj->GetSaveData();
+        CPPUNIT_ASSERT(pSaveData);
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Double field2");
+        CPPUNIT_ASSERT(pSaveDim);
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        auto aIter = rMembers.begin();
+        ScDPSaveMember* pMember = *aIter; // "1.00"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        ++aIter;
+        pMember = *aIter; // "2.00"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+        ++aIter;
+        pMember = *aIter; // "3.00"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+    }
+
+    // With thousand separator
+    {
+        const ScDPObject* pDPObj = &(*pDPs)[2];
+        CPPUNIT_ASSERT(pDPObj);
+        ScDPSaveData* pSaveData = pDPObj->GetSaveData();
+        CPPUNIT_ASSERT(pSaveData);
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Double field3");
+        CPPUNIT_ASSERT(pSaveDim);
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        auto aIter = rMembers.begin();
+        ScDPSaveMember* pMember = *aIter; // "10,000.00"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        ++aIter;
+        pMember = *aIter; // "20,000.00"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+        ++aIter;
+        pMember = *aIter; // "30,000.00"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+    }
+
+    xDocSh->DoClose();
+}
+
+void ScExportTest::testPivotTableStringFieldFilterXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_string_field_filter.", FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rDoc = xDocSh->GetDocument();
+    ScDPCollection* pDPs = rDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
+
+    // Reload and check filtering of row dimensions
+    xDocSh = saveAndReload( &(*xDocSh), FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rLoadedDoc = xDocSh->GetDocument();
+    pDPs = rLoadedDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
+
+    const ScDPObject* pDPObj = &(*pDPs)[0];
+    CPPUNIT_ASSERT(pDPObj);
+    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
+    CPPUNIT_ASSERT(pSaveData);
+    ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Country");
+    CPPUNIT_ASSERT(pSaveDim);
+
+    const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+    CPPUNIT_ASSERT_EQUAL(size_t(2), rMembers.size());
+    ScDPSaveMember* pMember = pSaveDim->GetExistingMemberByName("United Kingdom");
+    CPPUNIT_ASSERT(pMember);
+    CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+    pMember = pSaveDim->GetExistingMemberByName("United States");
+    CPPUNIT_ASSERT(pMember);
+    CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+
+    xDocSh->DoClose();
+}
+
+
+void ScExportTest::testPivotTableDateFieldFilterXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_date_field_filter.", FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rDoc = xDocSh->GetDocument();
+    ScDPCollection* pDPs = rDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
+
+    // Reload and check filtering of row dimensions
+    xDocSh = saveAndReload( &(*xDocSh), FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rLoadedDoc = xDocSh->GetDocument();
+    pDPs = rLoadedDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
+    const ScDPObject* pDPObj = &(*pDPs)[0];
+    CPPUNIT_ASSERT(pDPObj);
+    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
+    CPPUNIT_ASSERT(pSaveData);
+
+    {
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Date");
+        CPPUNIT_ASSERT(pSaveDim);
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        //CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        auto aIter = rMembers.begin();
+        ScDPSaveMember* pMember = *aIter; // "2016. január 7."
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        ++aIter;
+        pMember = *aIter; // "2016. január 8."
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+    }
+
+    {
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Date2");
+        CPPUNIT_ASSERT(pSaveDim);
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        auto aIter = rMembers.begin();
+        ScDPSaveMember* pMember = *aIter; // "2016-01-07"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        ++aIter;
+        pMember = *aIter; // "2016-01-08"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+    }
+
+    {
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Date3");
+        CPPUNIT_ASSERT(pSaveDim);
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        auto aIter = rMembers.begin();
+        ScDPSaveMember* pMember = *aIter; // "2016. 1. 7. 0:00"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+        ++aIter;
+        pMember = *aIter; // "2016. 1. 8. 0:00"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+    }
+
+    xDocSh->DoClose();
+}
+
+void ScExportTest::testPivotTableBoolFieldFilterXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_bool_field_filter.", FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rDoc = xDocSh->GetDocument();
+    ScDPCollection* pDPs = rDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
+
+    // Reload and check filtering of row dimensions
+    xDocSh = saveAndReload( &(*xDocSh), FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rLoadedDoc = xDocSh->GetDocument();
+    pDPs = rLoadedDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
+    const ScDPObject* pDPObj = &(*pDPs)[0];
+    CPPUNIT_ASSERT(pDPObj);
+    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
+    CPPUNIT_ASSERT(pSaveData);
+
+    ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Bool field");
+    CPPUNIT_ASSERT(pSaveDim);
+
+    const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+    CPPUNIT_ASSERT_EQUAL(size_t(2), rMembers.size());
+    ScDPSaveMember* pMember = pSaveDim->GetExistingMemberByName("0");
+    CPPUNIT_ASSERT(pMember);
+    CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+    pMember = pSaveDim->GetExistingMemberByName("1");
+    CPPUNIT_ASSERT(pMember);
+    CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+
+    xDocSh->DoClose();
+}
+
+void ScExportTest::testPivotTableRowColPageFieldFilterXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_rowcolpage_field_filter.", FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rDoc = xDocSh->GetDocument();
+    ScDPCollection* pDPs = rDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
+
+    // Reload and check filtering of row dimensions
+    xDocSh = saveAndReload( &(*xDocSh), FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load file", xDocSh.is());
+    ScDocument& rLoadedDoc = xDocSh->GetDocument();
+    pDPs = rLoadedDoc.GetDPCollection();
+    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
+    const ScDPObject* pDPObj = &(*pDPs)[0];
+    CPPUNIT_ASSERT(pDPObj);
+    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
+    CPPUNIT_ASSERT(pSaveData);
+
+    // Row field
+    {
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Order ID");
+        CPPUNIT_ASSERT(pSaveDim);
+        CPPUNIT_ASSERT_EQUAL(sheet::DataPilotFieldOrientation_ROW, pSaveDim->GetOrientation());
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        ScDPSaveMember* pMember = pSaveDim->GetExistingMemberByName("1");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        pMember = pSaveDim->GetExistingMemberByName("2");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        pMember = pSaveDim->GetExistingMemberByName("3");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+    }
+
+    // Column field
+    {
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Double2 field");
+        CPPUNIT_ASSERT(pSaveDim);
+        CPPUNIT_ASSERT_EQUAL(sheet::DataPilotFieldOrientation_COLUMN, pSaveDim->GetOrientation());
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        ScDPSaveMember* pMember = pSaveDim->GetExistingMemberByName("2");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        pMember = pSaveDim->GetExistingMemberByName("3");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        pMember = pSaveDim->GetExistingMemberByName("4");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+    }
+
+    // Page field
+    {
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Double3 field");
+        CPPUNIT_ASSERT(pSaveDim);
+        CPPUNIT_ASSERT_EQUAL(sheet::DataPilotFieldOrientation_PAGE, pSaveDim->GetOrientation());
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        ScDPSaveMember* pMember = pSaveDim->GetExistingMemberByName("5");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        pMember = pSaveDim->GetExistingMemberByName("6");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+        pMember = pSaveDim->GetExistingMemberByName("7");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+    }
+
+    // Hidden field
+    /* TODO
+    {
+        ScDPSaveDimension* pSaveDim = pSaveData->GetExistingDimensionByName("Double4 field");
+        CPPUNIT_ASSERT(pSaveDim);
+        CPPUNIT_ASSERT_EQUAL(sheet::DataPilotFieldOrientation_HIDDEN, pSaveDim->GetOrientation());
+
+        const ScDPSaveDimension::MemberList& rMembers = pSaveDim->GetMembers();
+        CPPUNIT_ASSERT_EQUAL(size_t(3), rMembers.size());
+        ScDPSaveMember* pMember = pSaveDim->GetExistingMemberByName("8");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && !pMember->GetIsVisible());
+        pMember = pSaveDim->GetExistingMemberByName("9");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        pMember = pSaveDim->GetExistingMemberByName("10");
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+    }*/
+
+    xDocSh->DoClose();
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScExportTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/filter/excel/xepivotxml.cxx b/sc/source/filter/excel/xepivotxml.cxx
index 4a1011f7c22c..4f15b1bfcb41 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -760,13 +760,24 @@ void XclExpXmlPivotTables::SavePivotTableXml( XclExpXmlStream& rStrm, const ScDP
         std::set<size_t> aUsedCachePositions;
         for (const auto & rMember : aMembers)
         {
-            auto it = std::find_if(iCacheFieldItems_begin, iCacheFieldItems_end,
-                [&rMember](const ScDPItemData& arg) -> bool { return arg.GetString() == rMember.maName; });
-            if (it != iCacheFieldItems_end)
+            for (auto it = iCacheFieldItems_begin; it != iCacheFieldItems_end; ++it)
             {
-                size_t nCachePos = it - iCacheFieldItems_begin;
-                aMemberSequence.emplace_back(nCachePos, !rMember.mbVisible);
-                aUsedCachePositions.insert(nCachePos);
+                OUString sFormattedName;
+                if (it->HasStringData() || it->IsEmpty())
+                {
+                    sFormattedName = it->GetString();
+                }
+                else
+                {
+                    sFormattedName = const_cast<ScDPObject&>(rDPObj).GetFormattedString(pDim->GetName(), it->GetValue());
+                }
+                if (sFormattedName == rMember.maName)
+                {
+                    size_t nCachePos = it - iCacheFieldItems_begin;
+                    aMemberSequence.emplace_back(nCachePos, !rMember.mbVisible);
+                    aUsedCachePositions.insert(nCachePos);
+                    break;
+                }
             }
         }
         // Now add all remaining cache items as hidden
diff --git a/sc/source/filter/inc/pivotcachebuffer.hxx b/sc/source/filter/inc/pivotcachebuffer.hxx
index 48c8fba055e7..05ddb336d54f 100644
--- a/sc/source/filter/inc/pivotcachebuffer.hxx
+++ b/sc/source/filter/inc/pivotcachebuffer.hxx
@@ -31,6 +31,10 @@ namespace com { namespace sun { namespace star {
 
 namespace oox { namespace core { class Relations; } }
 
+class ScDPSaveDimension;
+class ScDPObject;
+class DateTime;
+
 namespace oox {
 namespace xls {
 
@@ -76,6 +80,9 @@ public:
     const css::uno::Any& getValue() const { return maValue; }
     /** Returns the string representation of the item. */
     OUString     getName() const;
+
+    /** Returns the string representation of the item, using the actual formating. */
+    OUString     getFormattedName(const ScDPSaveDimension& rSaveDim, ScDPObject* pObj, const DateTime& rNullDate) const;
     /** Returns true if the item is unused. */
     bool         isUnused() const { return mbUnused; }
 
diff --git a/sc/source/filter/oox/pivotcachebuffer.cxx b/sc/source/filter/oox/pivotcachebuffer.cxx
index 25e9c241f207..1da8b0c1cfed 100644
--- a/sc/source/filter/oox/pivotcachebuffer.cxx
+++ b/sc/source/filter/oox/pivotcachebuffer.cxx
@@ -43,6 +43,9 @@
 #include "tablebuffer.hxx"
 #include "unitconverter.hxx"
 #include "worksheetbuffer.hxx"
+#include "dpobject.hxx"
+#include "dpsave.hxx"
+#include "dpdimsave.hxx"
 
 namespace oox {
 namespace xls {
@@ -230,6 +233,22 @@ OUString PivotCacheItem::getName() const
     return OUString();
 }
 
+OUString PivotCacheItem::getFormattedName(const ScDPSaveDimension& rSaveDim, ScDPObject* pObj, const DateTime& rNullDate) const
+{
+    switch( mnType )
+    {
+        case XML_m: return OUString();
+        case XML_s: return maValue.get< OUString >();
+        case XML_n: return pObj->GetFormattedString(rSaveDim.GetName(), maValue.get<double>());
+        case XML_i: return pObj->GetFormattedString(rSaveDim.GetName(), static_cast<double>(maValue.get< sal_Int32 >()));
+        case XML_b: return pObj->GetFormattedString(rSaveDim.GetName(), static_cast<double>(maValue.get< bool >()));
+        case XML_d: return pObj->GetFormattedString(rSaveDim.GetName(), maValue.get< css::util::DateTime >() - rNullDate);
+        case XML_e: return OUString();                                                              // !TODO
+    }
+    OSL_FAIL( "PivotCacheItem::getFormattedName - invalid data type" );
+    return OUString();
+}
+
 PivotCacheItemList::PivotCacheItemList( const WorkbookHelper& rHelper ) :
     WorkbookHelper( rHelper )
 {
diff --git a/sc/source/filter/oox/pivottablebuffer.cxx b/sc/source/filter/oox/pivottablebuffer.cxx
index cd1025f73abc..e10360a39023 100644
--- a/sc/source/filter/oox/pivottablebuffer.cxx
+++ b/sc/source/filter/oox/pivottablebuffer.cxx
@@ -52,6 +52,7 @@
 #include "dpdimsave.hxx"
 #include "document.hxx"
 #include "documentimport.hxx"
+#include "workbooksettings.hxx"
 
 namespace oox {
 namespace xls {
@@ -547,7 +548,10 @@ void PivotTableField::convertPageField( const PTPageFieldModel& rPageField )
             {
                 if( const PivotCacheItem* pSharedItem = pCacheField->getCacheItem( nCacheItem ) )
                 {
-                    OUString aSelectedPage = pSharedItem->getName();
+                    ScDPObject* pDPObj = mrPivotTable.getDPObject();
+                    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
+                    ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(pCacheField->getName());
+                    OUString aSelectedPage = pSharedItem->getFormattedName(*pDim, pDPObj, DateTime(getWorkbookSettings().getNullDate()));
                     aPropSet.setProperty( PROP_SelectedPage, aSelectedPage );
                 }
             }
@@ -748,7 +752,7 @@ Reference< XDataPilotField > PivotTableField::convertRowColPageField( sal_Int32
 
                         try
                         {
-                            ScDPSaveMember* pMem = pDim->GetMemberByName(pSharedItem->getName());
+                            ScDPSaveMember* pMem = pDim->GetMemberByName(pSharedItem->getFormattedName(*pDim, pDPObj, DateTime(getWorkbookSettings().getNullDate())));
                             pMem->SetShowDetails(aIt->mbShowDetails);
                             pMem->SetIsVisible(!aIt->mbHidden);
                         }