pivot: PivotTableFormatOutput to resolve and set PT cell format

This adds the PivotTableFormatOutput, which main responsibility
is to resolve the reference for which the pivot table format is
set to. It first prepares the format data into such a structure
that it is easier to match with the pivot table fields and data.
Then the pivot table data is filled during the output, where we
remember the cell positions of the pivot table output. The last
step is to resolve the pivot table format references with the
filled data, where the cell formats are applied to the output.

PivotTableFormatsImportExport was added to test the correctnes of
the functionality.

Change-Id: Ie67ea15b3aa74739f15937800d03d256b8f68277
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/165992
Tested-by: Jenkins
Reviewed-by: Tomaž Vajngerl <quikee@gmail.com>

20 files changed, 860 insertions, 105 deletions

diff --git a/sc/CppunitTest_sc_pivottable_formats_import_export_test.mk b/sc/CppunitTest_sc_pivottable_formats_import_export_test.mk
new file mode 100644
index 000000000000..827137a7d5f9
--- /dev/null
+++ b/sc/CppunitTest_sc_pivottable_formats_import_export_test.mk
@@ -0,0 +1,86 @@
+# -*- Mode: makefile-gmake; tab-width: 4; indent-tabs-mode: t -*-
+#
+# This file is part of the LibreOffice project.
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+
+$(eval $(call gb_CppunitTest_CppunitTest,sc_pivottable_formats_import_export_test))
+
+$(eval $(call gb_CppunitTest_use_common_precompiled_header,sc_pivottable_formats_import_export_test))
+
+$(eval $(call gb_CppunitTest_add_exception_objects,sc_pivottable_formats_import_export_test, \
+    sc/qa/unit/PivotTableFormatsImportExport \
+))
+
+$(eval $(call gb_CppunitTest_use_externals,sc_pivottable_formats_import_export_test, \
+    boost_headers \
+    mdds_headers \
+    libxml2 \
+))
+
+$(eval $(call gb_CppunitTest_use_libraries,sc_pivottable_formats_import_export_test, \
+    basegfx \
+    comphelper \
+    cppu \
+    cppuhelper \
+    drawinglayer \
+    drawinglayercore \
+    editeng \
+    for \
+    forui \
+    i18nlangtag \
+    msfilter \
+    oox \
+    sal \
+    salhelper \
+    sax \
+    sc \
+    scqahelper \
+    sfx \
+    sot \
+    subsequenttest \
+    svl \
+    svt \
+    svx \
+    svxcore \
+    test \
+    tk \
+    tl \
+    ucbhelper \
+    unotest \
+    utl \
+    vcl \
+    xo \
+))
+
+$(eval $(call gb_CppunitTest_set_include,sc_pivottable_formats_import_export_test,\
+    -I$(SRCDIR)/sc/source/ui/inc \
+    -I$(SRCDIR)/sc/inc \
+    $$(INCLUDE) \
+))
+
+$(eval $(call gb_CppunitTest_use_api,sc_pivottable_formats_import_export_test,\
+    udkapi \
+    offapi \
+    oovbaapi \
+))
+
+$(eval $(call gb_CppunitTest_use_ure,sc_pivottable_formats_import_export_test))
+$(eval $(call gb_CppunitTest_use_vcl,sc_pivottable_formats_import_export_test))
+
+$(eval $(call gb_CppunitTest_use_rdb,sc_pivottable_formats_import_export_test,services))
+
+$(eval $(call gb_CppunitTest_use_custom_headers,sc_pivottable_formats_import_export_test,\
+    officecfg/registry \
+))
+
+$(eval $(call gb_CppunitTest_use_configuration,sc_pivottable_formats_import_export_test))
+
+$(eval $(call gb_CppunitTest_add_arguments,sc_pivottable_formats_import_export_test, \
+    -env:arg-env=$(gb_Helper_LIBRARY_PATH_VAR)"$$$${$(gb_Helper_LIBRARY_PATH_VAR)+=$$$$$(gb_Helper_LIBRARY_PATH_VAR)}" \
+))
+
+# vim: set noet sw=4 ts=4:
diff --git a/sc/Library_sc.mk b/sc/Library_sc.mk
index 273eaab04435..eaa79cf10903 100644
--- a/sc/Library_sc.mk
+++ b/sc/Library_sc.mk
@@ -178,6 +178,7 @@ $(eval $(call gb_Library_add_exception_objects,sc,\
     sc/source/core/data/pagepar \
     sc/source/core/data/patattr \
     sc/source/core/data/pivot2 \
+    sc/source/core/data/PivotTableFormatOutput \
     sc/source/core/data/poolcach \
     sc/source/core/data/poolhelp \
     sc/source/core/data/postit \
diff --git a/sc/Module_sc.mk b/sc/Module_sc.mk
index d966be26e8dc..25b43c6b19df 100644
--- a/sc/Module_sc.mk
+++ b/sc/Module_sc.mk
@@ -85,6 +85,7 @@ $(eval $(call gb_Module_add_slowcheck_targets,sc, \
 	CppunitTest_sc_new_cond_format_api \
 	CppunitTest_sc_pdf_export \
 	CppunitTest_sc_pivottable_filters_test \
+	CppunitTest_sc_pivottable_formats_import_export_test \
 	CppunitTest_sc_sparkline_test \
 	CppunitTest_sc_subsequent_filters_test \
 	CppunitTest_sc_subsequent_filters_test2 \
diff --git a/sc/inc/dpobject.hxx b/sc/inc/dpobject.hxx
index fd1783cda03e..06ffc0085cf2 100644
--- a/sc/inc/dpobject.hxx
+++ b/sc/inc/dpobject.hxx
@@ -108,7 +108,6 @@ private:
     bool mbSettingsChanged : 1;
     bool mbEnableGetPivotData : 1;
 
-    ScDPTableData*    GetTableData();
     void              CreateObjects();
     void              CreateOutput();
     void ClearSource();
@@ -130,6 +129,8 @@ public:
     void                InvalidateData();
     void Clear();
     void ClearTableData();
+    ScDPTableData* GetTableData();
+
     SC_DLLPUBLIC void ReloadGroupTableData();
 
     SC_DLLPUBLIC void   Output( const ScAddress& rPos );
@@ -188,6 +189,7 @@ public:
                                        tools::Rectangle& rPosRect, css::sheet::DataPilotFieldOrientation& rOrient, tools::Long& rDimPos );
     bool                IsFilterButton( const ScAddress& rPos );
 
+    static OUString GetFormattedString(ScDPTableData* pTableData, tools::Long nDimension, const double fValue);
     SC_DLLPUBLIC OUString GetFormattedString( std::u16string_view rDimName, const double fValue );
 
     double GetPivotData(
diff --git a/sc/inc/dpoutput.hxx b/sc/inc/dpoutput.hxx
index 01ae4fb1c600..11251afd4fdf 100644
--- a/sc/inc/dpoutput.hxx
+++ b/sc/inc/dpoutput.hxx
@@ -29,6 +29,7 @@
 
 #include "dptypes.hxx"
 #include "pivot/PivotTableFormats.hxx"
+#include "pivot/PivotTableFormatOutput.hxx"
 
 #include <memory>
 #include <vector>
@@ -44,11 +45,13 @@ namespace tools { class Rectangle; }
 class ScDocument;
 struct ScDPOutLevelData;
 class ScDPOutputImpl;
+class ScDPObject;
 
 class ScDPOutput
 {
 private:
     ScDocument* mpDocument;
+    sc::FormatOutput maFormatOutput;
     css::uno::Reference<css::sheet::XDimensionsSupplier> mxSource;
     ScAddress maStartPos;
     std::vector<ScDPOutLevelData> mpColFields;
@@ -66,8 +69,6 @@ private:
     sal_uInt32 mnSingleNumberFormat;
     size_t mnRowDims; // Including empty ones.
 
-    std::unique_ptr<sc::PivotTableFormats> mpFormats;
-
     // Output geometry related parameters
     sal_Int32 mnColCount;
     sal_Int32 mnRowCount;
@@ -116,10 +117,11 @@ private:
     void outputDataResults(SCTAB nTab);
 
 public:
-                    ScDPOutput( ScDocument* pD,
-                                css::uno::Reference< css::sheet::XDimensionsSupplier> xSrc,
-                                const ScAddress& rPos, bool bFilter, bool bExpandCollapse );
-                    ~ScDPOutput();
+    ScDPOutput(ScDocument* pDocument,
+               css::uno::Reference<css::sheet::XDimensionsSupplier> xSource,
+               const ScAddress& rPosition, bool bFilter, bool bExpandCollapse,
+               ScDPObject& rObject);
+    ~ScDPOutput();
 
     void            SetPosition( const ScAddress& rPos );
 
@@ -147,7 +149,7 @@ public:
 
     void setFormats(sc::PivotTableFormats const& rPivotTableFormats)
     {
-        mpFormats.reset(new sc::PivotTableFormats(rPivotTableFormats));
+        maFormatOutput.setFormats(rPivotTableFormats);
     }
 
     static void GetDataDimensionNames(
diff --git a/sc/inc/pivot/DPOutLevelData.hxx b/sc/inc/pivot/DPOutLevelData.hxx
new file mode 100644
index 000000000000..01f06d990655
--- /dev/null
+++ b/sc/inc/pivot/DPOutLevelData.hxx
@@ -0,0 +1,65 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ *
+ * This file incorporates work covered by the following license notice:
+ *
+ *   Licensed to the Apache Software Foundation (ASF) under one or more
+ *   contributor license agreements. See the NOTICE file distributed
+ *   with this work for additional information regarding copyright
+ *   ownership. The ASF licenses this file to you under the Apache
+ *   License, Version 2.0 (the "License"); you may not use this file
+ *   except in compliance with the License. You may obtain a copy of
+ *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
+ */
+
+#pragma once
+
+#include <tools/long.hxx>
+#include <rtl/ustring.hxx>
+
+#include <com/sun/star/uno/Sequence.hxx>
+#include <com/sun/star/sheet/MemberResult.hpp>
+
+using namespace css;
+
+struct ScDPOutLevelData
+{
+    tools::Long mnDim;
+    tools::Long mnHier;
+    tools::Long mnLevel;
+    tools::Long mnDimPos;
+    sal_uInt32 mnSrcNumFmt; /// Prevailing number format used in the source data.
+    uno::Sequence<sheet::MemberResult> maResult;
+    OUString maName; /// Name is the internal field name.
+    OUString maCaption; /// Caption is the name visible in the output table.
+    bool mbHasHiddenMember : 1;
+    bool mbDataLayout : 1;
+    bool mbPageDim : 1;
+
+    ScDPOutLevelData(tools::Long nDim, tools::Long nHier, tools::Long nLevel, tools::Long nDimPos,
+                     sal_uInt32 nSrcNumFmt, const uno::Sequence<sheet::MemberResult>& aResult,
+                     OUString aName, OUString aCaption, bool bHasHiddenMember, bool bDataLayout,
+                     bool bPageDim)
+        : mnDim(nDim)
+        , mnHier(nHier)
+        , mnLevel(nLevel)
+        , mnDimPos(nDimPos)
+        , mnSrcNumFmt(nSrcNumFmt)
+        , maResult(aResult)
+        , maName(std::move(aName))
+        , maCaption(std::move(aCaption))
+        , mbHasHiddenMember(bHasHiddenMember)
+        , mbDataLayout(bDataLayout)
+        , mbPageDim(bPageDim)
+    {
+    }
+
+    // bug (73840) in uno::Sequence - copy and then assign doesn't work!
+};
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/inc/pivot/PivotTableFormatOutput.hxx b/sc/inc/pivot/PivotTableFormatOutput.hxx
new file mode 100644
index 000000000000..59cad62c013c
--- /dev/null
+++ b/sc/inc/pivot/PivotTableFormatOutput.hxx
@@ -0,0 +1,112 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ */
+
+#pragma once
+
+#include <com/sun/star/uno/Sequence.hxx>
+#include <tools/long.hxx>
+#include <address.hxx>
+#include <dptypes.hxx>
+#include "PivotTableFormats.hxx"
+
+#include <memory>
+#include <vector>
+
+namespace com::sun::star::sheet
+{
+struct MemberResult;
+}
+
+class ScDPObject;
+struct ScDPOutLevelData;
+
+namespace sc
+{
+enum class FormatResultDirection
+{
+    ROW,
+    COLUMN
+};
+
+struct FormatOutputField
+{
+    tools::Long nDimension = -2;
+    OUString aName;
+    sal_Int32 nIndex = -1;
+    bool bSet = false;
+};
+
+struct FormatOutputEntry
+{
+    FormatType eType = FormatType::None;
+    std::optional<SCTAB> onTab = std::nullopt;
+    std::shared_ptr<ScPatternAttr> pPattern;
+
+    std::vector<FormatOutputField> aRowOutputFields;
+    std::vector<FormatOutputField> aColumnOutputFields;
+};
+
+struct FieldData
+{
+    tools::Long mnDimension = -2;
+    OUString aName;
+    tools::Long nIndex;
+
+    bool bIsSet = false;
+    bool bIsMember = false;
+    bool bSubtotal = false;
+    bool bContinue = false;
+};
+
+struct LineData
+{
+    std::optional<SCCOLROW> oLine = std::nullopt;
+    std::optional<SCCOLROW> oPosition = std::nullopt;
+    std::vector<FieldData> maFields;
+};
+
+class NameResolver;
+
+class FormatOutput
+{
+private:
+    ScDPObject& mrObject;
+
+public:
+    FormatOutput(ScDPObject& rObject)
+        : mrObject(rObject)
+    {
+    }
+
+    std::unique_ptr<sc::PivotTableFormats> mpFormats;
+    std::vector<sc::FormatOutputEntry> maFormatOutputEntries;
+
+    std::vector<LineData> maRowLines;
+    std::vector<LineData> maColumnLines;
+
+    void setFormats(sc::PivotTableFormats const& rPivotTableFormats)
+    {
+        mpFormats.reset(new sc::PivotTableFormats(rPivotTableFormats));
+        maFormatOutputEntries.resize(mpFormats->size());
+    }
+
+    void insertFieldMember(size_t nFieldIndex, const ScDPOutLevelData& rField,
+                           tools::Long nMemberIndex, css::sheet::MemberResult const& rMember,
+                           SCCOL nColPos, SCROW nRowPos, FormatResultDirection eResultDirection);
+
+    void insertEmptyDataColumn(SCCOL nColPos, SCROW nRowPos);
+
+    void apply(ScDocument& rDocument);
+    void prepare(SCTAB nTab, std::vector<ScDPOutLevelData> const& rColumnFields,
+                 std::vector<ScDPOutLevelData> const& rRowFields, bool bColumnFieldIsDataOnly);
+};
+
+} // end sc
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/inc/pivot/PivotTableFormats.hxx b/sc/inc/pivot/PivotTableFormats.hxx
index 7b65d0df5820..2665c088625c 100644
--- a/sc/inc/pivot/PivotTableFormats.hxx
+++ b/sc/inc/pivot/PivotTableFormats.hxx
@@ -17,31 +17,38 @@
 
 namespace sc
 {
+/** Type of a pivot table cell format to which a selection can be made. */
+enum class FormatType
+{
+    None,
+    Data,
+    Label
+};
+
+/** Information to make a selection in the pivot table. */
+struct Selection
+{
+    sal_Int32 nField = 0;
+    sal_uInt32 nDataIndex = 0;
+};
+
+/** Holds cell patter attributes and a selection information to which cells in the pivot table
+ *  the pattern should be applied.
+ */
 struct PivotTableFormat
 {
-    sal_Int32 nField;
-    sal_Int32 nDataIndex;
+    FormatType eType = FormatType::None;
+    std::vector<Selection> aSelections;
     std::shared_ptr<ScPatternAttr> pPattern;
-
-    PivotTableFormat(sal_Int32 _nField, sal_Int32 _nDataIndex,
-                     std::shared_ptr<ScPatternAttr> _pPattern)
-        : nField(_nField)
-        , nDataIndex(_nDataIndex)
-        , pPattern(_pPattern)
-    {
-    }
 };
 
+/** A holder for a collection of PivotTableFormat */
 class PivotTableFormats
 {
     std::vector<PivotTableFormat> maFormats;
 
 public:
-    void add(sal_Int32 nField, sal_Int32 nDataIndex,
-             std::shared_ptr<ScPatternAttr> const& rpPattern)
-    {
-        maFormats.emplace_back(nField, nDataIndex, rpPattern);
-    }
+    void add(PivotTableFormat const& rPivotTableFormat) { maFormats.push_back(rPivotTableFormat); }
 
     size_t size() { return maFormats.size(); }
 
diff --git a/sc/qa/unit/PivotTableFormatsImportExport.cxx b/sc/qa/unit/PivotTableFormatsImportExport.cxx
new file mode 100644
index 000000000000..3cffe8cbc510
--- /dev/null
+++ b/sc/qa/unit/PivotTableFormatsImportExport.cxx
@@ -0,0 +1,161 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ */
+
+#include <sal/config.h>
+
+#include "helper/qahelper.hxx"
+
+#include <patattr.hxx>
+#include <scitems.hxx>
+#include <document.hxx>
+#include <generalfunction.hxx>
+#include <dpcache.hxx>
+#include <dpobject.hxx>
+#include <dpsave.hxx>
+#include <dputil.hxx>
+#include <attrib.hxx>
+#include <dpshttab.hxx>
+#include <globstr.hrc>
+#include <scresid.hxx>
+#include <queryentry.hxx>
+#include <queryparam.hxx>
+#include <rtl/string.hxx>
+#include <editeng/brushitem.hxx>
+#include <editeng/colritem.hxx>
+
+using namespace css;
+
+class ScPivotTableFormatsImportExport : public ScModelTestBase
+{
+public:
+    ScPivotTableFormatsImportExport();
+};
+
+ScPivotTableFormatsImportExport::ScPivotTableFormatsImportExport()
+    : ScModelTestBase("sc/qa/unit/data")
+{
+}
+
+namespace
+{
+Color getBackgroundColor(ScDocument& rDoc, OUString const& rAddressString)
+{
+    ScAddress aAddress;
+    aAddress.Parse(rAddressString, rDoc);
+    const ScPatternAttr* pPattern = rDoc.GetPattern(aAddress);
+    const SvxBrushItem& rItem = pPattern->GetItem(ATTR_BACKGROUND);
+    return rItem.GetColor();
+}
+
+Color getFontColor(ScDocument& rDoc, OUString const& rAddressString)
+{
+    ScAddress aAddress;
+    aAddress.Parse(rAddressString, rDoc);
+    const ScPatternAttr* pPattern = rDoc.GetPattern(aAddress);
+    const SvxColorItem& rItem = pPattern->GetItem(ATTR_FONT_COLOR);
+    return rItem.getColor();
+}
+} // end anonymous namespace
+
+CPPUNIT_TEST_FIXTURE(ScPivotTableFormatsImportExport,
+                     testPivotTableCellFormat_1_DataFieldInRow_RowLabelColor)
+{
+    auto assertDocument = [](ScDocument& rDoc) {
+        CPPUNIT_ASSERT_EQUAL(COL_YELLOW, getBackgroundColor(rDoc, u"G6"_ustr));
+        CPPUNIT_ASSERT_EQUAL(COL_LIGHTRED, getFontColor(rDoc, u"G7"_ustr));
+    };
+
+    createScDoc("xlsx/pivot-table/PivotTableCellFormatsTest_1_DataFieldInRow_RowLabelColor.xlsx");
+    assertDocument(*getScDoc());
+}
+
+CPPUNIT_TEST_FIXTURE(ScPivotTableFormatsImportExport,
+                     PivotTableCellFormatsTest_2_DataFieldInRow_ColumnLabelColor)
+{
+    auto assertDocument = [](ScDocument& rDoc) {
+        CPPUNIT_ASSERT_EQUAL(Color(0x00B050), getBackgroundColor(rDoc, u"H5"_ustr));
+    };
+
+    createScDoc(
+        "xlsx/pivot-table/PivotTableCellFormatsTest_2_DataFieldInRow_ColumnLabelColor.xlsx");
+    assertDocument(*getScDoc());
+}
+
+CPPUNIT_TEST_FIXTURE(ScPivotTableFormatsImportExport,
+                     PivotTableCellFormatsTest_3_DataFieldInColumn_ColumnLabelColor)
+{
+    auto assertDocument = [](ScDocument& rDoc) {
+        CPPUNIT_ASSERT_EQUAL(COL_LIGHTRED, getFontColor(rDoc, u"H5"_ustr));
+        CPPUNIT_ASSERT_EQUAL(Color(0x92D050), getBackgroundColor(rDoc, u"I5"_ustr));
+    };
+
+    createScDoc(
+        "xlsx/pivot-table/PivotTableCellFormatsTest_3_DataFieldInColumn_ColumnLabelColor.xlsx");
+    assertDocument(*getScDoc());
+}
+
+CPPUNIT_TEST_FIXTURE(ScPivotTableFormatsImportExport,
+                     PivotTableCellFormatsTest_4_DataFieldInColumn_DataColor)
+{
+    auto assertDocument = [](ScDocument& rDoc) {
+        CPPUNIT_ASSERT_EQUAL(COL_LIGHTRED, getFontColor(rDoc, u"H7"_ustr));
+        CPPUNIT_ASSERT_EQUAL(Color(0x92D050), getBackgroundColor(rDoc, u"I9"_ustr));
+    };
+
+    createScDoc("xlsx/pivot-table/PivotTableCellFormatsTest_4_DataFieldInColumn_DataColor.xlsx");
+    assertDocument(*getScDoc());
+}
+
+CPPUNIT_TEST_FIXTURE(ScPivotTableFormatsImportExport,
+                     PivotTableCellFormatsTest_5_DataFieldInColumnAndTwoRowFields_DataColor)
+{
+    auto assertDocument = [](ScDocument& rDoc) {
+        CPPUNIT_ASSERT_EQUAL(COL_YELLOW, getBackgroundColor(rDoc, u"I8"_ustr));
+        CPPUNIT_ASSERT_EQUAL(COL_LIGHTRED, getBackgroundColor(rDoc, u"I11"_ustr));
+        CPPUNIT_ASSERT_EQUAL(Color(0x0070C0), getBackgroundColor(rDoc, u"J13"_ustr));
+    };
+
+    createScDoc("xlsx/pivot-table//"
+                "PivotTableCellFormatsTest_5_DataFieldInColumnAndTwoRowFields_DataColor.xlsx");
+    assertDocument(*getScDoc());
+}
+
+CPPUNIT_TEST_FIXTURE(ScPivotTableFormatsImportExport,
+                     PivotTableCellFormatsTest_6_SingleDataFieldInColumn_DataColor)
+{
+    auto assertDocument = [](ScDocument& rDoc) {
+        CPPUNIT_ASSERT_EQUAL(COL_YELLOW, getBackgroundColor(rDoc, u"J8"_ustr));
+        CPPUNIT_ASSERT_EQUAL(COL_LIGHTRED, getBackgroundColor(rDoc, u"J12"_ustr));
+    };
+
+    createScDoc(
+        "xlsx/pivot-table//PivotTableCellFormatsTest_6_SingleDataFieldInColumn_DataColor.xlsx");
+    assertDocument(*getScDoc());
+}
+
+CPPUNIT_TEST_FIXTURE(ScPivotTableFormatsImportExport,
+                     PivotTableCellFormatsTest_7_TwoRowTwoColumnFields_DataColor)
+{
+    auto assertDocument = [](ScDocument& rDoc) {
+        CPPUNIT_ASSERT_EQUAL(COL_YELLOW, getBackgroundColor(rDoc, u"I7"_ustr));
+        CPPUNIT_ASSERT_EQUAL(Color(0xFFC000), getBackgroundColor(rDoc, u"J8"_ustr));
+        CPPUNIT_ASSERT_EQUAL(Color(0x0070C0), getBackgroundColor(rDoc, u"J9"_ustr));
+        CPPUNIT_ASSERT_EQUAL(Color(0x00B0F0), getBackgroundColor(rDoc, u"J13"_ustr));
+        CPPUNIT_ASSERT_EQUAL(Color(0x92D050), getBackgroundColor(rDoc, u"K12"_ustr));
+        CPPUNIT_ASSERT_EQUAL(COL_LIGHTRED, getBackgroundColor(rDoc, u"L14"_ustr));
+    };
+
+    createScDoc(
+        "xlsx/pivot-table//PivotTableCellFormatsTest_7_TwoRowTwoColumnFields_DataColor.xlsx");
+    assertDocument(*getScDoc());
+}
+
+CPPUNIT_PLUGIN_IMPLEMENT();
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_1_DataFieldInRow_RowLabelColor.xlsx b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_1_DataFieldInRow_RowLabelColor.xlsx
new file mode 100644
index 000000000000..cd1b66f7cd06
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_1_DataFieldInRow_RowLabelColor.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_2_DataFieldInRow_ColumnLabelColor.xlsx b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_2_DataFieldInRow_ColumnLabelColor.xlsx
new file mode 100644
index 000000000000..e156cad5e041
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_2_DataFieldInRow_ColumnLabelColor.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_3_DataFieldInColumn_ColumnLabelColor.xlsx b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_3_DataFieldInColumn_ColumnLabelColor.xlsx
new file mode 100644
index 000000000000..462f6d1219e6
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_3_DataFieldInColumn_ColumnLabelColor.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_4_DataFieldInColumn_DataColor.xlsx b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_4_DataFieldInColumn_DataColor.xlsx
new file mode 100644
index 000000000000..e3eaf72b3ebf
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_4_DataFieldInColumn_DataColor.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_5_DataFieldInColumnAndTwoRowFields_DataColor.xlsx b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_5_DataFieldInColumnAndTwoRowFields_DataColor.xlsx
new file mode 100644
index 000000000000..2e1d3c62296e
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_5_DataFieldInColumnAndTwoRowFields_DataColor.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_6_SingleDataFieldInColumn_DataColor.xlsx b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_6_SingleDataFieldInColumn_DataColor.xlsx
new file mode 100644
index 000000000000..4e82b47ec1e5
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_6_SingleDataFieldInColumn_DataColor.xlsx
diff --git a/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_7_TwoRowTwoColumnFields_DataColor.xlsx b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_7_TwoRowTwoColumnFields_DataColor.xlsx
new file mode 100644
index 000000000000..798e3bf82772
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot-table/PivotTableCellFormatsTest_7_TwoRowTwoColumnFields_DataColor.xlsx
diff --git a/sc/source/core/data/PivotTableFormatOutput.cxx b/sc/source/core/data/PivotTableFormatOutput.cxx
new file mode 100644
index 000000000000..350aef597ebd
--- /dev/null
+++ b/sc/source/core/data/PivotTableFormatOutput.cxx
@@ -0,0 +1,348 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ */
+
+#include <pivot/PivotTableFormatOutput.hxx>
+#include <pivot/DPOutLevelData.hxx>
+
+#include <dpoutput.hxx>
+#include <dpobject.hxx>
+#include <dptabdat.hxx>
+#include <dpcache.hxx>
+#include <document.hxx>
+
+#include <com/sun/star/sheet/MemberResultFlags.hpp>
+
+namespace sc
+{
+class NameResolver
+{
+private:
+    ScDPTableData& mrTableData;
+    ScDPCache const& mrCache;
+
+    std::unordered_map<sal_Int32, std::vector<OUString>> maNameCache;
+
+    void fillNamesForDimension(std::vector<OUString>& rNames, sal_Int32 nDimension)
+    {
+        for (const auto& rItemData : mrCache.GetDimMemberValues(nDimension))
+        {
+            OUString sFormattedName;
+            if (rItemData.HasStringData() || rItemData.IsEmpty())
+                sFormattedName = rItemData.GetString();
+            else
+                sFormattedName = ScDPObject::GetFormattedString(&mrTableData, nDimension,
+                                                                rItemData.GetValue());
+            rNames.push_back(sFormattedName);
+        }
+    }
+
+public:
+    NameResolver(ScDPTableData& rTableData, ScDPCache const& rCache)
+        : mrTableData(rTableData)
+        , mrCache(rCache)
+    {
+    }
+
+    OUString getNameForIndex(sal_uInt32 nIndex, sal_Int32 nDimension)
+    {
+        auto iterator = maNameCache.find(nDimension);
+        if (iterator == maNameCache.end())
+        {
+            std::vector<OUString> aNames;
+            fillNamesForDimension(aNames, nDimension);
+            maNameCache.emplace(nDimension, aNames);
+            return aNames[nIndex];
+        }
+        else
+        {
+            std::vector<OUString>& rNames = iterator->second;
+            return rNames[nIndex];
+        }
+    }
+};
+
+namespace
+{
+void initLines(std::vector<LineData>& rLines, std::vector<ScDPOutLevelData> const& rFields)
+{
+    for (size_t i = 0; i < rFields.size(); i++)
+    {
+        size_t nFieldLength(rFields[i].maResult.getLength());
+        if (rLines.size() < nFieldLength)
+            rLines.resize(nFieldLength);
+
+        for (LineData& rLineData : rLines)
+        {
+            rLineData.maFields.resize(rFields.size());
+        }
+    }
+}
+
+void initFormatOutputField(std::vector<FormatOutputField>& rOutputFields,
+                           std::vector<ScDPOutLevelData> const& rFields,
+                           PivotTableFormat const& rFormat, NameResolver& rNameResolver)
+{
+    rOutputFields.resize(rFields.size());
+    for (size_t i = 0; i < rOutputFields.size(); i++)
+    {
+        FormatOutputField& rOutputField = rOutputFields[i];
+        if (!rFields[i].mbDataLayout)
+            rOutputField.nDimension = rFields[i].mnDim;
+
+        for (auto const& rSelection : rFormat.aSelections)
+        {
+            if (rSelection.nField == rOutputField.nDimension)
+            {
+                if (rOutputField.nDimension == -2)
+                {
+                    rOutputField.aName = "DATA";
+                    rOutputField.nIndex = rSelection.nDataIndex;
+                    rOutputField.bSet = true;
+                }
+                else
+                {
+                    rOutputField.aName
+                        = rNameResolver.getNameForIndex(rSelection.nDataIndex, rSelection.nField);
+                    rOutputField.nIndex = rSelection.nDataIndex;
+                    rOutputField.bSet = true;
+                }
+            }
+        }
+    }
+}
+
+} // end anonymous namespace
+
+void FormatOutput::prepare(SCTAB nTab, std::vector<ScDPOutLevelData> const& rColumnFields,
+                           std::vector<ScDPOutLevelData> const& rRowFields,
+                           bool bColumnFieldIsDataOnly)
+{
+    if (!mpFormats)
+        return;
+
+    initLines(maRowLines, rRowFields);
+
+    if (rColumnFields.size() == 0 && bColumnFieldIsDataOnly)
+    {
+        maColumnLines.resize(1);
+        maColumnLines[0].maFields.resize(1);
+    }
+    else
+    {
+        initLines(maColumnLines, rColumnFields);
+    }
+
+    auto* pTableData = mrObject.GetTableData();
+    if (!pTableData)
+        return;
+
+    ScDPFilteredCache const& rFilteredCache = pTableData->GetCacheTable();
+    ScDPCache const& rCache = rFilteredCache.getCache();
+
+    NameResolver aNameResolver(*pTableData, rCache);
+
+    size_t nFormatIndex = 0;
+    for (PivotTableFormat const& rFormat : mpFormats->getVector())
+    {
+        sc::FormatOutputEntry& rEntry = maFormatOutputEntries[nFormatIndex];
+        rEntry.pPattern = rFormat.pPattern;
+        rEntry.onTab = nTab;
+        rEntry.eType = rFormat.eType;
+
+        initFormatOutputField(rEntry.aRowOutputFields, rRowFields, rFormat, aNameResolver);
+
+        if (rColumnFields.size() == 0 && bColumnFieldIsDataOnly)
+        {
+            rEntry.aColumnOutputFields.resize(1);
+            FormatOutputField& rOutputField = rEntry.aColumnOutputFields[0];
+
+            for (auto const& rSelection : rFormat.aSelections)
+            {
+                if (rSelection.nField == -2)
+                {
+                    rOutputField.aName = "DATA";
+                    rOutputField.nIndex = rSelection.nDataIndex;
+                    rOutputField.bSet = true;
+                }
+            }
+        }
+        else
+        {
+            initFormatOutputField(rEntry.aColumnOutputFields, rColumnFields, rFormat,
+                                  aNameResolver);
+        }
+        nFormatIndex++;
+    }
+}
+
+void FormatOutput::insertEmptyDataColumn(SCCOL nColPos, SCROW nRowPos)
+{
+    if (!mpFormats)
+        return;
+
+    LineData& rLine = maColumnLines[0];
+    rLine.oLine = nColPos;
+    rLine.oPosition = nRowPos;
+
+    FieldData& rFieldData = rLine.maFields[0];
+    rFieldData.nIndex = 0;
+    rFieldData.bIsSet = true;
+}
+
+namespace
+{
+void fillLineAndFieldData(std::vector<LineData>& rLineDataVector, size_t nFieldIndex,
+                          ScDPOutLevelData const& rField, tools::Long nMemberIndex,
+                          sheet::MemberResult const& rMember, SCCOLROW nLine, SCCOLROW nPosition)
+{
+    LineData& rLine = rLineDataVector[nMemberIndex];
+    rLine.oLine = nLine;
+    rLine.oPosition = nPosition;
+
+    FieldData& rFieldData = rLine.maFields[nFieldIndex];
+    if (!rField.mbDataLayout)
+        rFieldData.mnDimension = rField.mnDim;
+    rFieldData.aName = rMember.Name;
+    rFieldData.nIndex = nMemberIndex;
+    rFieldData.bIsSet = true;
+    rFieldData.bIsMember = rMember.Flags & sheet::MemberResultFlags::HASMEMBER;
+    rFieldData.bSubtotal = rMember.Flags & sheet::MemberResultFlags::SUBTOTAL;
+    rFieldData.bContinue = rMember.Flags & sheet::MemberResultFlags::CONTINUE;
+
+    // Search previous entries for the name / value
+    if (rFieldData.bContinue)
+    {
+        tools::Long nCurrent = nMemberIndex - 1;
+        while (nCurrent >= 0 && rLineDataVector[nCurrent].maFields[nFieldIndex].bContinue)
+            nCurrent--;
+
+        if (nCurrent >= 0)
+        {
+            FieldData& rCurrentFieldData = rLineDataVector[nCurrent].maFields[nFieldIndex];
+            rFieldData.aName = rCurrentFieldData.aName;
+            rFieldData.nIndex = rCurrentFieldData.nIndex;
+        }
+    }
+}
+} // end anonymous namespace
+
+void FormatOutput::insertFieldMember(size_t nFieldIndex, ScDPOutLevelData const& rField,
+                                     tools::Long nMemberIndex, sheet::MemberResult const& rMember,
+                                     SCCOL nColPos, SCROW nRowPos,
+                                     sc::FormatResultDirection eResultDirection)
+{
+    if (!mpFormats)
+        return;
+
+    if (eResultDirection == sc::FormatResultDirection::ROW)
+        fillLineAndFieldData(maRowLines, nFieldIndex, rField, nMemberIndex, rMember, nRowPos,
+                             nColPos);
+    else if (eResultDirection == sc::FormatResultDirection::COLUMN)
+        fillLineAndFieldData(maColumnLines, nFieldIndex, rField, nMemberIndex, rMember, nColPos,
+                             nRowPos);
+}
+
+void FormatOutput::apply(ScDocument& rDocument)
+{
+    if (!mpFormats)
+        return;
+
+    size_t nEntryIndex = 0;
+    for (auto const& rOutputEntry : maFormatOutputEntries)
+    {
+        if (!rOutputEntry.onTab || !rOutputEntry.pPattern)
+            continue;
+
+        std::optional<SCCOLROW> oRow;
+        std::optional<SCCOLROW> oColumn;
+
+        for (LineData const& rLineData : maRowLines)
+        {
+            bool bMatchesAll = true;
+
+            for (size_t nIndex = 0; nIndex < rLineData.maFields.size(); nIndex++)
+            {
+                FieldData const& rFieldData = rLineData.maFields[nIndex];
+                FormatOutputField const& rFormatEntry = rOutputEntry.aRowOutputFields[nIndex];
+
+                tools::Long nDimension = rFieldData.mnDimension;
+                if (nDimension == rFormatEntry.nDimension)
+                {
+                    if (rFormatEntry.bSet)
+                    {
+                        if (nDimension == -2 && rFieldData.nIndex != rFormatEntry.nIndex)
+                            bMatchesAll = false;
+                        else if (nDimension != -2 && rFieldData.aName != rFormatEntry.aName)
+                            bMatchesAll = false;
+                    }
+                }
+                else
+                {
+                    bMatchesAll = false;
+                }
+            }
+
+            if (bMatchesAll)
+            {
+                if (rLineData.oLine && rLineData.oPosition
+                    && rOutputEntry.eType == FormatType::Label)
+                    rDocument.ApplyPattern(*rLineData.oPosition, *rLineData.oLine,
+                                           *rOutputEntry.onTab, *rOutputEntry.pPattern);
+                else if (rOutputEntry.eType == FormatType::Data)
+                    oRow = rLineData.oLine;
+                break;
+            }
+        }
+
+        for (LineData const& rLineData : maColumnLines)
+        {
+            bool bMatchesAll = true;
+            for (size_t nIndex = 0; nIndex < rLineData.maFields.size(); nIndex++)
+            {
+                FieldData const& rFieldData = rLineData.maFields[nIndex];
+                FormatOutputField const& rFormatEntry = rOutputEntry.aColumnOutputFields[nIndex];
+
+                tools::Long nDimension = rFieldData.mnDimension;
+                if (nDimension == rFormatEntry.nDimension)
+                {
+                    if (rFormatEntry.bSet)
+                    {
+                        if (nDimension == -2 && rFieldData.nIndex != rFormatEntry.nIndex)
+                            bMatchesAll = false;
+                        else if (nDimension != -2 && rFieldData.aName != rFormatEntry.aName)
+                            bMatchesAll = false;
+                    }
+                }
+                else
+                {
+                    bMatchesAll = false;
+                }
+            }
+            if (bMatchesAll)
+            {
+                if (rLineData.oLine && rLineData.oPosition
+                    && rOutputEntry.eType == FormatType::Label)
+                    rDocument.ApplyPattern(*rLineData.oLine, *rLineData.oPosition,
+                                           *rOutputEntry.onTab, *rOutputEntry.pPattern);
+                else if (rOutputEntry.eType == FormatType::Data)
+                    oColumn = rLineData.oLine;
+                break;
+            }
+        }
+        if (oColumn && oRow && rOutputEntry.eType == FormatType::Data)
+        {
+            rDocument.ApplyPattern(*oColumn, *oRow, *rOutputEntry.onTab, *rOutputEntry.pPattern);
+        }
+        nEntryIndex++;
+    }
+}
+
+} // end sc
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/core/data/dpobject.cxx b/sc/source/core/data/dpobject.cxx
index 3f98a5fcc40c..d80acccb55c4 100644
--- a/sc/source/core/data/dpobject.cxx
+++ b/sc/source/core/data/dpobject.cxx
@@ -528,7 +528,7 @@ void ScDPObject::CreateOutput()
     bool bFilterButton = IsSheetData() && mpSaveData && mpSaveData->GetFilterButton();
     bool bExpandCollapse = mpSaveData ? mpSaveData->GetExpandCollapse() : false;
 
-    mpOutput.reset(new ScDPOutput(mpDocument, mxSource, maOutputRange.aStart, bFilterButton, bExpandCollapse));
+    mpOutput.reset(new ScDPOutput(mpDocument, mxSource, maOutputRange.aStart, bFilterButton, bExpandCollapse, *this));
     mpOutput->SetHeaderLayout(mbHeaderLayout);
     if (mpSaveData->hasFormats())
         mpOutput->setFormats(mpSaveData->getFormats());
@@ -1442,6 +1442,13 @@ void ScDPObject::GetMemberResultNames(ScDPUniqueStringSet& rNames, tools::Long n
     mpOutput->GetMemberResultNames(rNames, nDimension);    // used only with table data -> level not needed
 }
 
+OUString ScDPObject::GetFormattedString(ScDPTableData* pTableData, tools::Long nDimension, const double fValue)
+{
+    ScDPItemData aItemData;
+    aItemData.SetValue(fValue);
+    return pTableData->GetFormattedString(nDimension, aItemData, false);
+}
+
 OUString ScDPObject::GetFormattedString(std::u16string_view rDimName, const double fValue)
 {
     ScDPTableData* pTableData = GetTableData();
@@ -1454,9 +1461,8 @@ OUString ScDPObject::GetFormattedString(std::u16string_view rDimName, const doub
         if(rDimName == pTableData->getDimensionName(nDim))
             break;
     }
-    ScDPItemData aItemData;
-    aItemData.SetValue(fValue);
-    return GetTableData()->GetFormattedString(nDim, aItemData, false);
+
+    return GetFormattedString(pTableData, nDim, fValue);
 }
 
 
diff --git a/sc/source/core/data/dpoutput.cxx b/sc/source/core/data/dpoutput.cxx
index 8847f857a181..1b20d6fc3a56 100644
--- a/sc/source/core/data/dpoutput.cxx
+++ b/sc/source/core/data/dpoutput.cxx
@@ -29,6 +29,7 @@
 #include <svl/itemset.hxx>
 
 #include <dpoutput.hxx>
+#include <dpobject.hxx>
 #include <document.hxx>
 #include <attrib.hxx>
 #include <formula/errorcodes.hxx>
@@ -41,6 +42,7 @@
 #include <strings.hrc>
 #include <stringutil.hxx>
 #include <dputil.hxx>
+#include <pivot/DPOutLevelData.hxx>
 
 #include <com/sun/star/beans/XPropertySet.hpp>
 #include <com/sun/star/sheet/DataPilotTableHeaderData.hpp>
@@ -82,31 +84,6 @@ using ::com::sun::star::sheet::DataPilotTableResultData;
 
 #define SC_DP_FRAME_COLOR           Color(0,0,0) //( 0x20, 0x40, 0x68 )
 
-struct ScDPOutLevelData
-{
-    tools::Long mnDim;
-    tools::Long mnHier;
-    tools::Long mnLevel;
-    tools::Long mnDimPos;
-    sal_uInt32 mnSrcNumFmt; /// Prevailing number format used in the source data.
-    uno::Sequence<sheet::MemberResult> maResult;
-    OUString maName;     /// Name is the internal field name.
-    OUString maCaption;  /// Caption is the name visible in the output table.
-    bool mbHasHiddenMember:1;
-    bool mbDataLayout:1;
-    bool mbPageDim:1;
-
-    ScDPOutLevelData(tools::Long nDim, tools::Long nHier, tools::Long nLevel, tools::Long nDimPos, sal_uInt32 nSrcNumFmt, const uno::Sequence<sheet::MemberResult>  &aResult,
-                       OUString aName, OUString aCaption, bool bHasHiddenMember, bool bDataLayout, bool bPageDim) :
-        mnDim(nDim), mnHier(nHier), mnLevel(nLevel), mnDimPos(nDimPos), mnSrcNumFmt(nSrcNumFmt), maResult(aResult),
-        maName(std::move(aName)), maCaption(std::move(aCaption)), mbHasHiddenMember(bHasHiddenMember), mbDataLayout(bDataLayout),
-        mbPageDim(bPageDim)
-    {
-    }
-
-    // bug (73840) in uno::Sequence - copy and then assign doesn't work!
-};
-
 namespace
 {
 struct ScDPOutLevelDataComparator
@@ -509,8 +486,9 @@ uno::Sequence<sheet::MemberResult> getVisiblePageMembersAsResults( const uno::Re
 } // end anonymous namespace
 
 ScDPOutput::ScDPOutput(ScDocument* pDocument, uno::Reference<sheet::XDimensionsSupplier> xSource,
-                       const ScAddress& rPosition, bool bFilter, bool bExpandCollapse)
+                       const ScAddress& rPosition, bool bFilter, bool bExpandCollapse, ScDPObject& rObject)
     : mpDocument(pDocument)
+    , maFormatOutput(rObject)
     , mxSource(std::move(xSource))
     , maStartPos(rPosition)
     , mnColFormatCount(0)
@@ -1036,16 +1014,16 @@ void ScDPOutput::outputColumnHeaders(SCTAB nTab, ScDPOutputImpl& rOutputImpl)
         tools::Long nThisColCount = rMemberSequence.getLength();
         OSL_ENSURE(nThisColCount == mnColCount, "count mismatch"); //TODO: ???
 
-        tools::Long nColumnIndex = -1;
         for (tools::Long nColumn = 0; nColumn < nThisColCount; nColumn++)
         {
-            if (!(pMemberArray[nColumn].Flags & sheet::MemberResultFlags::CONTINUE))
-                nColumnIndex++;
+            sheet::MemberResult const& rMember = rMemberSequence[nColumn];
 
             SCCOL nColPos = mnDataStartCol + SCCOL(nColumn); //TODO: check for overflow
-            HeaderCell(nColPos, nRowPos, nTab, pMemberArray[nColumn], true, nField);
-            if ((pMemberArray[nColumn].Flags & sheet::MemberResultFlags::HASMEMBER) &&
-               !(pMemberArray[nColumn].Flags & sheet::MemberResultFlags::SUBTOTAL))
+
+            HeaderCell(nColPos, nRowPos, nTab, rMember, true, nField);
+
+            if ((rMember.Flags & sheet::MemberResultFlags::HASMEMBER) &&
+               !(rMember.Flags & sheet::MemberResultFlags::SUBTOTAL))
             {
                 // Check the number of columns this spreads
                 tools::Long nEnd = nColumn;
@@ -1071,27 +1049,13 @@ void ScDPOutput::outputColumnHeaders(SCTAB nTab, ScDPOutputImpl& rOutputImpl)
                     lcl_SetStyleById(mpDocument, nTab, nColPos, nRowPos, nColPos, mnDataStartRow - 1, STR_PIVOT_STYLENAME_CATEGORY);
                 }
             }
-            else if (pMemberArray[nColumn].Flags & sheet::MemberResultFlags::SUBTOTAL)
+            else if (rMember.Flags & sheet::MemberResultFlags::SUBTOTAL)
             {
                 rOutputImpl.AddCol(nColPos);
             }
 
-            // Apply format
-            if (mpFormats)
-            {
-                auto& rColumnField = mpColFields[nField];
-                tools::Long nDimension = -2;
-                if (!rColumnField.mbDataLayout)
-                    nDimension = rColumnField.mnDim;
-
-                for (auto& aFormat : mpFormats->getVector())
-                {
-                    if (aFormat.nField == nDimension && aFormat.nDataIndex == nColumnIndex)
-                    {
-                        mpDocument->ApplyPattern(nColPos, nRowPos, nTab, *aFormat.pPattern);
-                    }
-                }
-            }
+            // Resolve formats
+            maFormatOutput.insertFieldMember(nField, mpColFields[nField], nColumn, rMember, nColPos, nRowPos, sc::FormatResultDirection::COLUMN);
 
             // Apply the same number format as in data source.
             mpDocument->ApplyAttr(nColPos, nRowPos, nTab, SfxUInt32Item(ATTR_VALUE_FORMAT, mpColFields[nField].mnSrcNumFmt));
@@ -1123,12 +1087,10 @@ void ScDPOutput::outputRowHeader(SCTAB nTab, ScDPOutputImpl& rOutputImpl)
         const sheet::MemberResult* pMemberArray = rMemberSequence.getConstArray();
         sal_Int32 nThisRowCount = rMemberSequence.getLength();
         OSL_ENSURE(nThisRowCount == mnRowCount, "count mismatch");     //TODO: ???
-        tools::Long nRowIndex = -1;
         for (sal_Int32 nRow = 0; nRow < nThisRowCount; nRow++)
         {
-            if (!(pMemberArray[nRow].Flags & sheet::MemberResultFlags::CONTINUE))
-                nRowIndex++;
-            const sheet::MemberResult& rData = pMemberArray[nRow];
+            sheet::MemberResult const& rMember = rMemberSequence[nRow];
+            const sheet::MemberResult& rData = rMember;
             const bool bHasMember = rData.Flags & sheet::MemberResultFlags::HASMEMBER;
             const bool bSubtotal = rData.Flags & sheet::MemberResultFlags::SUBTOTAL;
             SCROW nRowPos = mnDataStartRow + SCROW(nRow); //TODO: check for overflow
@@ -1181,21 +1143,8 @@ void ScDPOutput::outputRowHeader(SCTAB nTab, ScDPOutputImpl& rOutputImpl)
                 rOutputImpl.AddRow(nRowPos);
             }
 
-            // Apply format
-            if (mpFormats)
-            {
-                auto& rRowField = mpRowFields[nField];
-                tools::Long nDimension = -2;
-                if (!rRowField.mbDataLayout)
-                    nDimension = rRowField.mnDim;
-                for (auto& aFormat : mpFormats->getVector())
-                {
-                    if (aFormat.nField == nDimension && aFormat.nDataIndex == nRowIndex)
-                    {
-                        mpDocument->ApplyPattern(nColPos, nRowPos, nTab, *aFormat.pPattern);
-                    }
-                }
-            }
+            // Resolve formats
+            maFormatOutput.insertFieldMember(nField, mpRowFields[nField], nRow, rMember, nColPos, nRowPos, sc::FormatResultDirection::ROW);
 
             // Apply the same number format as in data source.
             mpDocument->ApplyAttr(nColPos, nRowPos, nTab, SfxUInt32Item(ATTR_VALUE_FORMAT, mpRowFields[nField].mnSrcNumFmt));
@@ -1230,6 +1179,8 @@ void ScDPOutput::outputDataResults(SCTAB nTab)
             DataCell(nColPos, nRowPos, nTab, pColAry[nCol]);
         }
     }
+
+    maFormatOutput.apply(*mpDocument);
 }
 
 void ScDPOutput::Output()
@@ -1237,12 +1188,15 @@ void ScDPOutput::Output()
     SCTAB nTab = maStartPos.Tab();
 
     //  calculate output positions and sizes
-
     CalcSizes();
 
     if (mbSizeOverflow || mbResultsError)   // does output area exceed sheet limits?
         return;                             // nothing
 
+    // Prepare format output
+    bool bColumnFieldIsDataOnly = mnColCount == 1 && mnRowCount > 0 && mpColFields.empty();
+    maFormatOutput.prepare(nTab, mpColFields, mpRowFields, bColumnFieldIsDataOnly);
+
     //  clear whole (new) output area
     // when modifying table, clear old area !
     //TODO: include InsertDeleteFlags::OBJECTS ???
@@ -1275,13 +1229,16 @@ void ScDPOutput::Output()
 
     outputRowHeader(nTab, aOutputImpl);
 
-    if (mnColCount == 1 && mnRowCount > 0 && mpColFields.empty())
+    if (bColumnFieldIsDataOnly)
     {
         // the table contains exactly one data field and no column fields.
         // Display data description at top right corner.
         ScSetStringParam aParam;
         aParam.setTextInput();
-        mpDocument->SetString(mnDataStartCol, mnDataStartRow - 1, nTab, maDataDescription, &aParam);
+        SCCOL nCol = mnDataStartCol;
+        SCCOL nRow = mnDataStartRow - 1;
+        mpDocument->SetString(nCol, nRow, nTab, maDataDescription, &aParam);
+        maFormatOutput.insertEmptyDataColumn(nCol, nRow);
     }
 
     outputDataResults(nTab);
diff --git a/sc/source/filter/oox/PivotTableFormat.cxx b/sc/source/filter/oox/PivotTableFormat.cxx
index 436b76af1bdf..9b16087400d2 100644
--- a/sc/source/filter/oox/PivotTableFormat.cxx
+++ b/sc/source/filter/oox/PivotTableFormat.cxx
@@ -88,17 +88,24 @@ void PivotTableFormat::finalizeImport()
         aFormats = pSaveData->getFormats();
 
     // Resolve references - TODO
-    for (auto const& pReference : maReferences)
+
+    sc::PivotTableFormat aFormat;
+    if (mbDataOnly)
+        aFormat.eType = sc::FormatType::Data;
+    else if (mbLabelOnly)
+        aFormat.eType = sc::FormatType::Label;
+
+    aFormat.pPattern = pPattern;
+    for (auto& rReference : maReferences)
     {
-        if (pReference->mnField && pReference->mbSelected)
+        if (rReference->mnField)
         {
-            auto nField = *pReference->mnField;
-            for (auto index : pReference->maFieldItemsIndices)
-            {
-                aFormats.add(nField, index, pPattern);
-            }
+            aFormat.aSelections.push_back(
+                sc::Selection{ .nField = sal_Int32(*rReference->mnField),
+                               .nDataIndex = rReference->maFieldItemsIndices[0] });
         }
     }
+    aFormats.add(aFormat);
 
     pSaveData->setFormats(aFormats);
 }