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.

Reviewed-on: https://gerrit.libreoffice.org/42069
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Tamás Zolnai <tamas.zolnai@collabora.com>
(cherry picked from commit 7c429ddca63c09a2b656715292968000bfe86a1a)

Change-Id: Ie8f731f6c72d7f55f43c318e52d63dc4e993d9c0
Reviewed-on: https://gerrit.libreoffice.org/42137
Reviewed-by: Andras Timar <andras.timar@collabora.com>
Tested-by: Andras Timar <andras.timar@collabora.com>

14 files changed, 501 insertions, 50 deletions

diff --git a/sc/inc/dpobject.hxx b/sc/inc/dpobject.hxx
index 2f144c5c7786..8e4a5f6be3f6 100644
--- a/sc/inc/dpobject.hxx
+++ b/sc/inc/dpobject.hxx
@@ -179,6 +179,8 @@ public:
                                        Rectangle& rPosRect, sal_uInt16& 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/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 91e9a742ce42..2e0ecc0f1cd5 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -199,6 +199,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);
@@ -298,6 +303,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:
@@ -4251,6 +4262,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(static_cast<sal_uInt16>(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(static_cast<sal_uInt16>(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(static_cast<sal_uInt16>(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(static_cast<sal_uInt16>(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 ab220f18651d..3374471d4f36 100644
--- a/sc/source/core/data/dpobject.cxx
+++ b/sc/source/core/data/dpobject.cxx
@@ -1443,6 +1443,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 2203a83ac1cd..34c5bc530e43 100644
--- a/sc/source/filter/excel/xepivot.cxx
+++ b/sc/source/filter/excel/xepivot.cxx
@@ -98,18 +98,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;
 }
 
@@ -120,11 +120,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
@@ -336,20 +336,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 );
         }
     }
@@ -463,7 +463,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;
@@ -471,10 +471,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;
@@ -482,10 +482,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;
@@ -493,7 +493,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 e735414f03db..0d933ffe3596 100644
--- a/sc/source/filter/excel/xipivot.cxx
+++ b/sc/source/filter/excel/xipivot.cxx
@@ -888,15 +888,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());
     }
 }
 
@@ -1080,9 +1115,6 @@ ScDPSaveDimension* XclImpPTField::ConvertRCPField( ScDPSaveData& rSaveData ) con
     // orientation
     rSaveDim.SetOrientation( static_cast< sal_uInt16 >( maFieldInfo.GetApiOrient( EXC_SXVD_AXIS_ROWCOLPAGE ) ) );
 
-    // general field info
-    ConvertFieldInfo( rSaveDim );
-
     // visible name
     if (const OUString* pVisName = maFieldInfo.GetVisName())
         if (!pVisName->isEmpty())
@@ -1128,19 +1160,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 );
 }
@@ -1423,6 +1465,7 @@ void XclImpPivotTable::Convert()
     GetDoc().GetDPCollection()->InsertNewTable(pDPObj);
     mpDPObj = pDPObj;
 
+    ApplyFieldInfo();
     ApplyMergeFlags(aOutRange, aSaveData);
 }
 
@@ -1512,6 +1555,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 3ca6b13442d2..690129ee0827 100644
--- a/sc/source/filter/excel/xlpivot.cxx
+++ b/sc/source/filter/excel/xlpivot.cxx
@@ -59,19 +59,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;
 }
 
@@ -100,11 +98,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;
 }
 
@@ -160,6 +157,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 8d9cd54770c1..ba54d8bfef10 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() );
 
     inline 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 8bf43053b343..28ca9da91991 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 67158c3bfa52..56976aef1bcc 100644
--- a/sc/source/filter/inc/xlpivot.hxx
+++ b/sc/source/filter/inc/xlpivot.hxx
@@ -377,15 +377,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. */
     inline const OUString& ConvertToText() const { return maText; }
@@ -408,6 +408,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.