tdf#112278: Pivot table: filtering of non-string fields is not exported to XLS

It is needed to use the right string representation of field value.
During export this representation is available directly, next to
the actual value. During import we can use the number format attached
to the corresponding pivot field.

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

15 files changed, 501 insertions, 50 deletions

diff --git a/sc/inc/dpobject.hxx b/sc/inc/dpobject.hxx
index 1f1546204e6f..39299c79ab26 100644
--- a/sc/inc/dpobject.hxx
+++ b/sc/inc/dpobject.hxx
@@ -179,6 +179,8 @@ public:
                                        tools::Rectangle& rPosRect, css::sheet::DataPilotFieldOrientation& rOrient, long& rDimPos );
     bool                IsFilterButton( const ScAddress& rPos );
 
+    OUString            GetFormattedString( const OUString& rDimName, const double fValue );
+
     double GetPivotData(
         const OUString& rDataFieldName,
         std::vector<css::sheet::DataPilotFieldFilter>& rFilters);
diff --git a/sc/qa/unit/data/xls/pivottable_bool_field_filter.xls b/sc/qa/unit/data/xls/pivottable_bool_field_filter.xls
new file mode 100755
index 000000000000..27bd838397ad
--- /dev/null
+++ b/sc/qa/unit/data/xls/pivottable_bool_field_filter.xls
diff --git a/sc/qa/unit/data/xls/pivottable_date_field_filter.xls b/sc/qa/unit/data/xls/pivottable_date_field_filter.xls
new file mode 100755
index 000000000000..b579cf875570
--- /dev/null
+++ b/sc/qa/unit/data/xls/pivottable_date_field_filter.xls
diff --git a/sc/qa/unit/data/xls/pivottable_double_field_filter.xls b/sc/qa/unit/data/xls/pivottable_double_field_filter.xls
new file mode 100755
index 000000000000..fe9988562042
--- /dev/null
+++ b/sc/qa/unit/data/xls/pivottable_double_field_filter.xls
diff --git a/sc/qa/unit/data/xls/pivottable_rowcolpage_field_filter.xls b/sc/qa/unit/data/xls/pivottable_rowcolpage_field_filter.xls
new file mode 100755
index 000000000000..771b785c37ed
--- /dev/null
+++ b/sc/qa/unit/data/xls/pivottable_rowcolpage_field_filter.xls
diff --git a/sc/qa/unit/data/xls/pivottable_string_field_filter.xls b/sc/qa/unit/data/xls/pivottable_string_field_filter.xls
new file mode 100755
index 000000000000..a4f730336cdd
--- /dev/null
+++ b/sc/qa/unit/data/xls/pivottable_string_field_filter.xls
diff --git a/sc/qa/unit/data/xls/tdf112278.xls b/sc/qa/unit/data/xls/tdf112278.xls
new file mode 100755
index 000000000000..09cfc9f7b518
--- /dev/null
+++ b/sc/qa/unit/data/xls/tdf112278.xls
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index c5280858928a..05173ca7fa03 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -211,6 +211,11 @@ public:
     void testHiddenRepeatedRowsODS();
     void testHyperlinkTargetFrameODS();
     void testTdf112106();
+    void testPivotTableDoubleFieldFilter();
+    void testPivotTableStringFieldFilter();
+    void testPivotTableDateFieldFilter();
+    void testPivotTableBoolFieldFilter();
+    void testPivotTableRowColPageFieldFilter();
 
     CPPUNIT_TEST_SUITE(ScExportTest);
     CPPUNIT_TEST(test);
@@ -316,6 +321,12 @@ public:
     CPPUNIT_TEST(testHyperlinkTargetFrameODS);
 
     CPPUNIT_TEST(testTdf112106);
+    CPPUNIT_TEST(testPivotTableDoubleFieldFilter);
+    CPPUNIT_TEST(testPivotTableStringFieldFilter);
+    CPPUNIT_TEST(testPivotTableDateFieldFilter);
+    CPPUNIT_TEST(testPivotTableBoolFieldFilter);
+    CPPUNIT_TEST(testPivotTableRowColPageFieldFilter);
+
     CPPUNIT_TEST_SUITE_END();
 
 private:
@@ -4329,6 +4340,348 @@ void ScExportTest::testTdf112106()
     xDocSh->DoClose();
 }
 
+void ScExportTest::testPivotTableDoubleFieldFilter()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_double_field_filter.", FORMAT_XLS);
+    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_XLS);
+    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)[2];
+        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)[0];
+        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::testPivotTableStringFieldFilter()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_string_field_filter.", FORMAT_XLS);
+    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_XLS);
+    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::testPivotTableDateFieldFilter()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_date_field_filter.", FORMAT_XLS);
+    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_XLS);
+    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 6."
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        ++aIter;
+        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-06"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        ++aIter;
+        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. 6. 0:00"
+        CPPUNIT_ASSERT(pMember);
+        CPPUNIT_ASSERT(pMember->HasIsVisible() && pMember->GetIsVisible());
+        ++aIter;
+        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::testPivotTableBoolFieldFilter()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_bool_field_filter.", FORMAT_XLS);
+    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_XLS);
+    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::testPivotTableRowColPageFieldFilter()
+{
+    ScDocShellRef xDocSh = loadDoc("pivottable_rowcolpage_field_filter.", FORMAT_XLS);
+    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_XLS);
+    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
+    {
+        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/core/data/dpobject.cxx b/sc/source/core/data/dpobject.cxx
index 7627a9746cc4..b68be09951e5 100644
--- a/sc/source/core/data/dpobject.cxx
+++ b/sc/source/core/data/dpobject.cxx
@@ -1445,6 +1445,24 @@ void ScDPObject::GetMemberResultNames(ScDPUniqueStringSet& rNames, long nDimensi
     pOutput->GetMemberResultNames(rNames, nDimension);    // used only with table data -> level not needed
 }
 
+OUString ScDPObject::GetFormattedString(const OUString& rDimName, const double fValue)
+{
+    ScDPTableData* pTableData = GetTableData();
+    if(!pTableData)
+        return OUString();
+
+    long nDim;
+    for (nDim = 0; nDim < pTableData->GetColumnCount(); ++nDim)
+    {
+        if(rDimName == pTableData->getDimensionName(nDim))
+            break;
+    }
+    ScDPItemData aItemData;
+    aItemData.SetValue(fValue);
+    return GetTableData()->GetFormattedString(nDim, aItemData, false);
+}
+
+
 namespace {
 
 bool dequote( const OUString& rSource, sal_Int32 nStartPos, sal_Int32& rEndPos, OUString& rResult )
diff --git a/sc/source/filter/excel/xepivot.cxx b/sc/source/filter/excel/xepivot.cxx
index d28348aabf0b..82f7db5646b4 100644
--- a/sc/source/filter/excel/xepivot.cxx
+++ b/sc/source/filter/excel/xepivot.cxx
@@ -99,18 +99,18 @@ XclExpPCItem::XclExpPCItem( const OUString& rText ) :
         SetEmpty();
 }
 
-XclExpPCItem::XclExpPCItem( double fValue ) :
+XclExpPCItem::XclExpPCItem( double fValue, const OUString& rText ) :
     XclExpRecord( EXC_ID_SXDOUBLE, 8 )
 {
-    SetDouble( fValue );
+    SetDouble( fValue, rText );
     mnTypeFlag = (fValue - floor( fValue ) == 0.0) ?
         EXC_PCITEM_DATA_INTEGER : EXC_PCITEM_DATA_DOUBLE;
 }
 
-XclExpPCItem::XclExpPCItem( const DateTime& rDateTime ) :
+XclExpPCItem::XclExpPCItem( const DateTime& rDateTime, const OUString& rText ) :
     XclExpRecord( EXC_ID_SXDATETIME, 8 )
 {
-    SetDateTime( rDateTime );
+    SetDateTime( rDateTime, rText );
     mnTypeFlag = EXC_PCITEM_DATA_DATE;
 }
 
@@ -121,11 +121,11 @@ XclExpPCItem::XclExpPCItem( sal_Int16 nValue ) :
     SetInteger( nValue );
 }
 
-XclExpPCItem::XclExpPCItem( bool bValue ) :
+XclExpPCItem::XclExpPCItem( bool bValue, const OUString& rText ) :
     XclExpRecord( EXC_ID_SXBOOLEAN, 2 ),
     mnTypeFlag( EXC_PCITEM_DATA_STRING )
 {
-    SetBool( bValue );
+    SetBool( bValue, rText );
 }
 
 bool XclExpPCItem::EqualsText( const OUString& rText ) const
@@ -337,20 +337,20 @@ void XclExpPCField::InitStandardField( const ScRange& rRange )
     // loop over all cells, create pivot cache items
     for( aPos.IncRow(); (aPos.Row() <= rRange.aEnd.Row()) && (maOrigItemList.GetSize() < EXC_PC_MAXITEMCOUNT); aPos.IncRow() )
     {
+        OUString aText = rDoc.GetString(aPos.Col(), aPos.Row(), aPos.Tab());
         if( rDoc.HasValueData( aPos.Col(), aPos.Row(), aPos.Tab() ) )
         {
             double fValue = rDoc.GetValue( aPos );
             short nFmtType = rFormatter.GetType( rDoc.GetNumberFormat( aPos ) );
             if( nFmtType == css::util::NumberFormat::LOGICAL )
-                InsertOrigBoolItem( fValue != 0 );
+                InsertOrigBoolItem( fValue != 0, aText );
             else if( nFmtType & css::util::NumberFormat::DATETIME )
-                InsertOrigDateTimeItem( GetDateTimeFromDouble( ::std::max( fValue, 0.0 ) ) );
+                InsertOrigDateTimeItem( GetDateTimeFromDouble( ::std::max( fValue, 0.0 ) ), aText );
             else
-                InsertOrigDoubleItem( fValue );
+                InsertOrigDoubleItem( fValue, aText );
         }
         else
         {
-            OUString aText = rDoc.GetString(aPos.Col(), aPos.Row(), aPos.Tab());
             InsertOrigTextItem( aText );
         }
     }
@@ -464,7 +464,7 @@ void XclExpPCField::InsertOrigTextItem( const OUString& rText )
         InsertOrigItem( new XclExpPCItem( aShortText ) );
 }
 
-void XclExpPCField::InsertOrigDoubleItem( double fValue )
+void XclExpPCField::InsertOrigDoubleItem( double fValue, const OUString& rText )
 {
     size_t nPos = 0;
     bool bFound = false;
@@ -472,10 +472,10 @@ void XclExpPCField::InsertOrigDoubleItem( double fValue )
         if( (bFound = maOrigItemList.GetRecord( nPos )->EqualsDouble( fValue )) )
             InsertItemArrayIndex( nPos );
     if( !bFound )
-        InsertOrigItem( new XclExpPCItem( fValue ) );
+        InsertOrigItem( new XclExpPCItem( fValue, rText ) );
 }
 
-void XclExpPCField::InsertOrigDateTimeItem( const DateTime& rDateTime )
+void XclExpPCField::InsertOrigDateTimeItem( const DateTime& rDateTime, const OUString& rText )
 {
     size_t nPos = 0;
     bool bFound = false;
@@ -483,10 +483,10 @@ void XclExpPCField::InsertOrigDateTimeItem( const DateTime& rDateTime )
         if( (bFound = maOrigItemList.GetRecord( nPos )->EqualsDateTime( rDateTime )) )
             InsertItemArrayIndex( nPos );
     if( !bFound )
-        InsertOrigItem( new XclExpPCItem( rDateTime ) );
+        InsertOrigItem( new XclExpPCItem( rDateTime, rText ) );
 }
 
-void XclExpPCField::InsertOrigBoolItem( bool bValue )
+void XclExpPCField::InsertOrigBoolItem( bool bValue, const OUString& rText )
 {
     size_t nPos = 0;
     bool bFound = false;
@@ -494,7 +494,7 @@ void XclExpPCField::InsertOrigBoolItem( bool bValue )
         if( (bFound = maOrigItemList.GetRecord( nPos )->EqualsBool( bValue )) )
             InsertItemArrayIndex( nPos );
     if( !bFound )
-        InsertOrigItem( new XclExpPCItem( bValue ) );
+        InsertOrigItem( new XclExpPCItem( bValue, rText ) );
 }
 
 sal_uInt16 XclExpPCField::InsertGroupItem( XclExpPCItem* pNewItem )
diff --git a/sc/source/filter/excel/xipivot.cxx b/sc/source/filter/excel/xipivot.cxx
index 89ec062c7089..a232c9bb8444 100644
--- a/sc/source/filter/excel/xipivot.cxx
+++ b/sc/source/filter/excel/xipivot.cxx
@@ -887,15 +887,50 @@ void XclImpPTItem::ReadSxvi( XclImpStream& rStrm )
     rStrm >> maItemInfo;
 }
 
-void XclImpPTItem::ConvertItem( ScDPSaveDimension& rSaveDim ) const
+void XclImpPTItem::ConvertItem( ScDPSaveDimension& rSaveDim, ScDPObject* pObj, const XclImpRoot& rRoot ) const
 {
-    if (const OUString* pItemName = GetItemName())
+    if(!mpCacheField)
+        return;
+
+    const XclImpPCItem* pCacheItem = mpCacheField->GetItem( maItemInfo.mnCacheIdx );
+    if(!pCacheItem)
+        return;
+
+    OUString sItemName;
+    if(pCacheItem->GetType() == EXC_PCITEM_TEXT || pCacheItem->GetType() == EXC_PCITEM_ERROR)
+    {
+        const OUString* pItemName = pCacheItem->GetText();
+        if(!pItemName)
+            return;
+        sItemName = *pItemName;
+    }
+    else if (pCacheItem->GetType() == EXC_PCITEM_DOUBLE)
+    {
+        sItemName = pObj->GetFormattedString(rSaveDim.GetName(), *pCacheItem->GetDouble());
+    }
+    else if (pCacheItem->GetType() == EXC_PCITEM_INTEGER)
+    {
+        sItemName = pObj->GetFormattedString(rSaveDim.GetName(), static_cast<double>(*pCacheItem->GetInteger()));
+    }
+    else if (pCacheItem->GetType() == EXC_PCITEM_BOOL)
+    {
+        sItemName = pObj->GetFormattedString(rSaveDim.GetName(), static_cast<double>(*pCacheItem->GetBool()));
+    }
+    else if (pCacheItem->GetType() == EXC_PCITEM_DATETIME)
+    {
+        sItemName = pObj->GetFormattedString(rSaveDim.GetName(), rRoot.GetDoubleFromDateTime(*pCacheItem->GetDateTime()));
+    }
+    else // EXC_PCITEM_EMPTY || EXC_PCITEM_INVALID
+        return;
+
+    // Find member and set properties
+    ScDPSaveMember* pMember = rSaveDim.GetExistingMemberByName( sItemName );
+    if(pMember)
     {
-        ScDPSaveMember& rMember = *rSaveDim.GetMemberByName( *pItemName );
-        rMember.SetIsVisible( !::get_flag( maItemInfo.mnFlags, EXC_SXVI_HIDDEN ) );
-        rMember.SetShowDetails( !::get_flag( maItemInfo.mnFlags, EXC_SXVI_HIDEDETAIL ) );
+        pMember->SetIsVisible( !::get_flag( maItemInfo.mnFlags, EXC_SXVI_HIDDEN ) );
+        pMember->SetShowDetails( !::get_flag( maItemInfo.mnFlags, EXC_SXVI_HIDEDETAIL ) );
         if (maItemInfo.HasVisName())
-            rMember.SetLayoutName(*maItemInfo.GetVisName());
+            pMember->SetLayoutName(*maItemInfo.GetVisName());
     }
 }
 
@@ -1079,9 +1114,6 @@ ScDPSaveDimension* XclImpPTField::ConvertRCPField( ScDPSaveData& rSaveData ) con
     // orientation
     rSaveDim.SetOrientation( maFieldInfo.GetApiOrient( EXC_SXVD_AXIS_ROWCOLPAGE ) );
 
-    // general field info
-    ConvertFieldInfo( rSaveDim );
-
     // visible name
     if (const OUString* pVisName = maFieldInfo.GetVisName())
         if (!pVisName->isEmpty())
@@ -1127,19 +1159,29 @@ ScDPSaveDimension* XclImpPTField::ConvertRCPField( ScDPSaveData& rSaveData ) con
     return &rSaveDim;
 }
 
-void XclImpPTField::ConvertFieldInfo( ScDPSaveDimension& rSaveDim ) const
+void XclImpPTField::ConvertFieldInfo( const ScDPSaveData& rSaveData, ScDPObject* pObj, const XclImpRoot& rRoot ) const
 {
-    rSaveDim.SetShowEmpty( ::get_flag( maFieldExtInfo.mnFlags, EXC_SXVDEX_SHOWALL ) );
+    const OUString& rFieldName = GetFieldName();
+    if( rFieldName.isEmpty() )
+        return;
+
+    const XclImpPCField* pCacheField = GetCacheField();
+    if( !pCacheField || !pCacheField->IsSupportedField() )
+        return;
+
+    ScDPSaveDimension* pTest = rSaveData.GetExistingDimensionByName(rFieldName);
+    if (!pTest)
+        return;
+
+    pTest->SetShowEmpty( ::get_flag( maFieldExtInfo.mnFlags, EXC_SXVDEX_SHOWALL ) );
     for( XclImpPTItemVec::const_iterator aIt = maItems.begin(), aEnd = maItems.end(); aIt != aEnd; ++aIt )
-        (*aIt)->ConvertItem( rSaveDim );
+        (*aIt)->ConvertItem( *pTest, pObj, rRoot );
 }
 
 void XclImpPTField::ConvertDataField( ScDPSaveDimension& rSaveDim, const XclPTDataFieldInfo& rDataInfo ) const
 {
     // orientation
     rSaveDim.SetOrientation( DataPilotFieldOrientation_DATA );
-    // general field info
-    ConvertFieldInfo( rSaveDim );
     // extended data field info
     ConvertDataFieldInfo( rSaveDim, rDataInfo );
 }
@@ -1422,6 +1464,7 @@ void XclImpPivotTable::Convert()
     GetDoc().GetDPCollection()->InsertNewTable(pDPObj);
     mpDPObj = pDPObj;
 
+    ApplyFieldInfo();
     ApplyMergeFlags(aOutRange, aSaveData);
 }
 
@@ -1511,6 +1554,34 @@ void XclImpPivotTable::ApplyMergeFlags(const ScRange& rOutRange, const ScDPSaveD
     }
 }
 
+
+void XclImpPivotTable::ApplyFieldInfo()
+{
+    mpDPObj->BuildAllDimensionMembers();
+    ScDPSaveData& rSaveData = *mpDPObj->GetSaveData();
+
+    // row fields
+    for( auto aIt = maRowFields.begin(), aEnd = maRowFields.end(); aIt != aEnd; ++aIt )
+        if( const XclImpPTField* pField = GetField( *aIt ) )
+            pField->ConvertFieldInfo( rSaveData, mpDPObj, *this );
+
+    // column fields
+    for( auto aIt = maColFields.begin(), aEnd = maColFields.end(); aIt != aEnd; ++aIt )
+        if( const XclImpPTField* pField = GetField( *aIt ) )
+            pField->ConvertFieldInfo( rSaveData, mpDPObj, *this );
+
+    // page fields
+    for( auto aIt = maPageFields.begin(), aEnd = maPageFields.end(); aIt != aEnd; ++aIt )
+        if( const XclImpPTField* pField = GetField( *aIt ) )
+            pField->ConvertFieldInfo( rSaveData, mpDPObj, *this );
+
+    // hidden fields
+    for( sal_uInt16 nField = 0, nCount = GetFieldCount(); nField < nCount; ++nField )
+        if( const XclImpPTField* pField = GetField( nField ) )
+            if (!pField->GetAxes())
+                pField->ConvertFieldInfo( rSaveData, mpDPObj, *this );
+}
+
 XclImpPivotTableManager::XclImpPivotTableManager( const XclImpRoot& rRoot ) :
     XclImpRoot( rRoot )
 {
diff --git a/sc/source/filter/excel/xlpivot.cxx b/sc/source/filter/excel/xlpivot.cxx
index dbd5822d008c..7cba10f7dd36 100644
--- a/sc/source/filter/excel/xlpivot.cxx
+++ b/sc/source/filter/excel/xlpivot.cxx
@@ -60,19 +60,17 @@ void XclPCItem::SetText( const OUString& rText )
     maText = rText;
 }
 
-void XclPCItem::SetDouble( double fValue )
+void XclPCItem::SetDouble( double fValue, const OUString& rText )
 {
     meType = EXC_PCITEM_DOUBLE;
-    //TODO convert double to string
-    maText.clear();
+    maText = rText;
     mfValue = fValue;
 }
 
-void XclPCItem::SetDateTime( const DateTime& rDateTime )
+void XclPCItem::SetDateTime( const DateTime& rDateTime, const OUString& rText )
 {
     meType = EXC_PCITEM_DATETIME;
-    //TODO convert date to string
-    maText.clear();
+    maText = rText;
     maDateTime = rDateTime;
 }
 
@@ -101,11 +99,10 @@ void XclPCItem::SetError( sal_uInt16 nError )
     }
 }
 
-void XclPCItem::SetBool( bool bValue )
+void XclPCItem::SetBool( bool bValue, const OUString& rText )
 {
     meType = EXC_PCITEM_BOOL;
-    //TODO convert boolean to string
-    maText.clear();
+    maText = rText;
     mbValue = bValue;
 }
 
@@ -161,6 +158,11 @@ const bool* XclPCItem::GetBool() const
     return (meType == EXC_PCITEM_BOOL) ? &mbValue : nullptr;
 }
 
+XclPCItemType XclPCItem::GetType() const
+{
+    return meType;
+}
+
 // Field settings =============================================================
 
 XclPCFieldInfo::XclPCFieldInfo() :
diff --git a/sc/source/filter/inc/xepivot.hxx b/sc/source/filter/inc/xepivot.hxx
index a5e048fa986f..27b38ad3b073 100644
--- a/sc/source/filter/inc/xepivot.hxx
+++ b/sc/source/filter/inc/xepivot.hxx
@@ -40,10 +40,10 @@ class XclExpPCItem : public XclExpRecord, public XclPCItem
 {
 public:
     explicit            XclExpPCItem( const OUString& rText );
-    explicit            XclExpPCItem( double fValue );
-    explicit            XclExpPCItem( const DateTime& rDateTime );
+    explicit            XclExpPCItem( double fValue, const OUString& rText = OUString() );
+    explicit            XclExpPCItem( const DateTime& rDateTime, const OUString& rText = OUString() );
     explicit            XclExpPCItem( sal_Int16 nValue );
-    explicit            XclExpPCItem( bool bValue );
+    explicit            XclExpPCItem( bool bValue, const OUString& rText = OUString() );
 
     sal_uInt16   GetTypeFlag() const { return mnTypeFlag; }
 
@@ -120,11 +120,11 @@ private:
     /** Inserts an original text item, if it is not contained already. */
     void                InsertOrigTextItem( const OUString& rText );
     /** Inserts an original value item, if it is not contained already. */
-    void                InsertOrigDoubleItem( double fValue );
+    void                InsertOrigDoubleItem( double fValue, const OUString& rText );
     /** Inserts an original date/time item, if it is not contained already. */
-    void                InsertOrigDateTimeItem( const DateTime& rDateTime );
+    void                InsertOrigDateTimeItem( const DateTime& rDateTime, const OUString& rText );
     /** Inserts an original boolean item, if it is not contained already. */
-    void                InsertOrigBoolItem( bool bValue );
+    void                InsertOrigBoolItem( bool bValue, const OUString& rText );
 
     /** Inserts an item into the grouping item list. Does not change anything else.
         @return  The list index of the new item. */
diff --git a/sc/source/filter/inc/xipivot.hxx b/sc/source/filter/inc/xipivot.hxx
index f5a9594d4cfd..ef58d20c4f8c 100644
--- a/sc/source/filter/inc/xipivot.hxx
+++ b/sc/source/filter/inc/xipivot.hxx
@@ -207,7 +207,7 @@ public:
     void                ReadSxvi( XclImpStream& rStrm );
 
     /** Inserts this item into the passed ScDPSaveDimension. */
-    void                ConvertItem( ScDPSaveDimension& rSaveDim ) const;
+    void                ConvertItem( ScDPSaveDimension& rSaveDim, ScDPObject* pObj, const XclImpRoot& rRoot ) const;
 
 private:
     XclPTItemInfo       maItemInfo;         /// General data for this item.
@@ -268,9 +268,10 @@ public:
     void                AddDataFieldInfo( const XclPTDataFieldInfo& rDataInfo );
     void                ConvertDataField( ScDPSaveData& rSaveData ) const;
 
+    void                ConvertFieldInfo( const ScDPSaveData& rSaveData, ScDPObject* pObj, const XclImpRoot& rRoot ) const;
+
 private:
     ScDPSaveDimension*  ConvertRCPField( ScDPSaveData& rSaveData ) const;
-    void                ConvertFieldInfo( ScDPSaveDimension& rSaveDim ) const;
 
     void                ConvertDataField( ScDPSaveDimension& rSaveDim, const XclPTDataFieldInfo& rDataInfo ) const;
     void                ConvertDataFieldInfo( ScDPSaveDimension& rSaveDim, const XclPTDataFieldInfo& rDataInfo ) const;
@@ -335,6 +336,7 @@ public:
     void                MaybeRefresh();
 
     void                ApplyMergeFlags(const ScRange& rOutRange, const ScDPSaveData& rSaveData);
+    void                ApplyFieldInfo();
 
 private:
     typedef ::std::vector< XclImpPTFieldRef > XclImpPTFieldVec;
diff --git a/sc/source/filter/inc/xlpivot.hxx b/sc/source/filter/inc/xlpivot.hxx
index 62605f1525ef..e85143c6e0dd 100644
--- a/sc/source/filter/inc/xlpivot.hxx
+++ b/sc/source/filter/inc/xlpivot.hxx
@@ -378,15 +378,15 @@ public:
     /** Sets the item to 'text' type and adds the passed text. */
     void                SetText( const OUString& rText );
     /** Sets the item to 'double' type and adds the passed value. */
-    void                SetDouble( double fValue );
+    void                SetDouble( double fValue, const OUString& rText = OUString() );
     /** Sets the item to 'date/time' type and adds the passed date. */
-    void                SetDateTime( const DateTime& rDateTime );
+    void                SetDateTime( const DateTime& rDateTime, const OUString& rText = OUString() );
     /** Sets the item to 'integer' type and adds the passed value. */
     void                SetInteger( sal_Int16 nValue );
     /** Sets the item to 'error' type and adds the passed Excel error code. */
     void                SetError( sal_uInt16 nError );
     /** Sets the item to 'boolean' type and adds the passed Boolean value. */
-    void                SetBool( bool bValue );
+    void                SetBool( bool bValue, const OUString& rText = OUString() );
 
     /** Returns the text representation of the item. */
     const OUString& ConvertToText() const { return maText; }
@@ -409,6 +409,9 @@ public:
     /** Returns pointer to Boolean value, if the item type is 'boolean', otherwise 0. */
     const bool*         GetBool() const;
 
+    /** Returns the type of the item */
+    XclPCItemType GetType() const;
+
 private:
     XclPCItemType       meType;         /// Type of the item.
     OUString       maText;         /// Text representation of the item.