tdf#124810: Roundtrip pivot table style info from XLSX.

Also provide a default pivot table style for those tables that don't
have a style info. Let's use the style settings that Excel uses.

Change-Id: I8006a33a0aa0e92629f7db0a9c24a6ff52d17945
Reviewed-on: https://gerrit.libreoffice.org/70933
Tested-by: Jenkins
Reviewed-by: Mike Kaganski <mike.kaganski@collabora.com>

8 files changed, 129 insertions, 0 deletions

diff --git a/sc/inc/dpobject.hxx b/sc/inc/dpobject.hxx
index 9be63f26db36..4d0011c17868 100644
--- a/sc/inc/dpobject.hxx
+++ b/sc/inc/dpobject.hxx
@@ -95,6 +95,11 @@ private:
                                             // cached data
     css::uno::Reference<css::sheet::XDimensionsSupplier> xSource;
     std::unique_ptr<ScDPOutput> pOutput;
+
+    // name -> sequence of sequences of css::xml::FastAttribute or css::xml::Attribute
+    // see PivotTable::putToInteropGrabBag in sc/source/filter/oox/pivottablebuffer.cxx for details
+    std::map<OUString, css::uno::Any> maInteropGrabBag;
+
     long                    nHeaderRows;    // page fields plus filter button
     bool                    mbHeaderLayout:1;  // true : grid, false : standard
     bool                    bAllowMove:1;
@@ -253,6 +258,18 @@ public:
 
     static bool         IsOrientationAllowed( css::sheet::DataPilotFieldOrientation nOrient, sal_Int32 nDimFlags );
 
+    void PutInteropGrabBag(std::map<OUString, css::uno::Any>&& val)
+    {
+        maInteropGrabBag = std::move(val);
+    }
+    std::pair<bool, css::uno::Any> GetInteropGrabBagValue(const OUString& sName) const
+    {
+        if (const auto it = maInteropGrabBag.find(sName); it != maInteropGrabBag.end())
+            return { true, it->second };
+
+        return { false, css::uno::Any() };
+    }
+
 #if DUMP_PIVOT_TABLE
     void Dump() const;
     void DumpCache() const;
diff --git a/sc/qa/unit/data/xlsx/pivot_dark1.xlsx b/sc/qa/unit/data/xlsx/pivot_dark1.xlsx
new file mode 100644
index 000000000000..8341e5b09d6d
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot_dark1.xlsx
diff --git a/sc/qa/unit/pivottable_filters_test.cxx b/sc/qa/unit/pivottable_filters_test.cxx
index f9d10f9f4451..11fb1de65a23 100644
--- a/sc/qa/unit/pivottable_filters_test.cxx
+++ b/sc/qa/unit/pivottable_filters_test.cxx
@@ -89,6 +89,7 @@ public:
     void testTdf124651();
     void testTdf124736();
     void tesTtdf124772NumFmt();
+    void testTdf124810();
 
     CPPUNIT_TEST_SUITE(ScPivotTableFiltersTest);
 
@@ -135,6 +136,7 @@ public:
     CPPUNIT_TEST(testTdf124651);
     CPPUNIT_TEST(testTdf124736);
     CPPUNIT_TEST(tesTtdf124772NumFmt);
+    CPPUNIT_TEST(testTdf124810);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -2565,6 +2567,51 @@ void ScPivotTableFiltersTest::tesTtdf124772NumFmt()
                 "formatCode", "\\$#,##0");
 }
 
+void ScPivotTableFiltersTest::testTdf124810()
+{
+    {
+        // First, test that we roundtrip existing pivot table style information from XLSX.
+        ScDocShellRef xDocSh = loadDoc("pivot_dark1.", FORMAT_XLSX);
+        CPPUNIT_ASSERT(xDocSh.is());
+
+        xmlDocPtr pTable = XPathHelper::parseExport2(*this, *xDocSh, m_xSFactory,
+            "xl/pivotTables/pivotTable1.xml", FORMAT_XLSX);
+        xDocSh->DoClose();
+        CPPUNIT_ASSERT(pTable);
+
+        // All attributes must have been roundtripped correctly (testdoc uses some non-default values)
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "name",
+                    "PivotStyleDark1");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showRowHeaders", "1");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showColHeaders", "1");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showRowStripes", "1");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showColStripes", "0");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showLastColumn", "0");
+    }
+
+    {
+        // Now check that we export default style information when there's no such information in
+        // original document. Just use some ODS as source. This might be changed when we start
+        // exporting better pivot table style information.
+        ScDocShellRef xDocSh = loadDoc("tdf124651_simplePivotTable.", FORMAT_ODS);
+        CPPUNIT_ASSERT(xDocSh.is());
+
+        xmlDocPtr pTable = XPathHelper::parseExport2(*this, *xDocSh, m_xSFactory,
+            "xl/pivotTables/pivotTable1.xml", FORMAT_XLSX);
+        xDocSh->DoClose();
+        CPPUNIT_ASSERT(pTable);
+
+        // The default style for pivot tables in Excel 2007 through 2016 is PivotStyleLight16
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "name",
+                    "PivotStyleLight16");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showRowHeaders", "1");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showColHeaders", "1");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showRowStripes", "0");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showColStripes", "0");
+        assertXPath(pTable, "/x:pivotTableDefinition/x:pivotTableStyleInfo", "showLastColumn", "1");
+    }
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScPivotTableFiltersTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/core/data/dpobject.cxx b/sc/source/core/data/dpobject.cxx
index 3089d65e11cf..8357f5961f94 100644
--- a/sc/source/core/data/dpobject.cxx
+++ b/sc/source/core/data/dpobject.cxx
@@ -320,6 +320,7 @@ ScDPObject::ScDPObject(const ScDPObject& r) :
     aTableTag( r.aTableTag ),
     aOutRange( r.aOutRange ),
     mpTableData(static_cast<ScDPTableData*>(nullptr)),
+    maInteropGrabBag(r.maInteropGrabBag),
     nHeaderRows( r.nHeaderRows ),
     mbHeaderLayout( r.mbHeaderLayout ),
     bAllowMove(false),
@@ -352,6 +353,7 @@ ScDPObject& ScDPObject::operator= (const ScDPObject& r)
         aTableName = r.aTableName;
         aTableTag = r.aTableTag;
         aOutRange = r.aOutRange;
+        maInteropGrabBag = r.maInteropGrabBag;
         nHeaderRows = r.nHeaderRows;
         mbHeaderLayout = r.mbHeaderLayout;
         bAllowMove = false;
@@ -792,6 +794,7 @@ void ScDPObject::Clear()
     pImpDesc.reset();
     pServDesc.reset();
     ClearTableData();
+    maInteropGrabBag.clear();
 }
 
 void ScDPObject::ClearTableData()
diff --git a/sc/source/filter/excel/xepivotxml.cxx b/sc/source/filter/excel/xepivotxml.cxx
index 1d0371b25270..ca7faa1bccff 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -682,6 +682,37 @@ sal_Int32 GetSubtotalAttrToken(ScGeneralFunction eFunc)
     return XML_defaultSubtotal;
 }
 
+// An item is expected to contain sequences of css::xml::FastAttribute and css::xml::Attribute
+void WriteGrabBagItemToStream(XclExpXmlStream& rStrm, sal_Int32 tokenId, const css::uno::Any& rItem)
+{
+    if (css::uno::Sequence<css::uno::Any> aSeqs; rItem >>= aSeqs)
+    {
+        auto& pStrm = rStrm.GetCurrentStream();
+        pStrm->write("<")->writeId(tokenId);
+
+        css::uno::Sequence<css::xml::FastAttribute> aFastSeq;
+        css::uno::Sequence<css::xml::Attribute> aUnkSeq;
+        for (const auto& a : aSeqs)
+        {
+            if (a >>= aFastSeq)
+            {
+                for (const auto& rAttr : aFastSeq)
+                    rStrm.WriteAttributes(rAttr.Token, rAttr.Value);
+            }
+            else if (a >>= aUnkSeq)
+            {
+                for (const auto& rAttr : aUnkSeq)
+                    pStrm->write(" ")
+                        ->write(rAttr.Name)
+                        ->write("=\"")
+                        ->writeEscaped(rAttr.Value)
+                        ->write("\"");
+            }
+        }
+
+        pStrm->write("/>");
+    }
+}
 }
 
 void XclExpXmlPivotTables::SavePivotTableXml( XclExpXmlStream& rStrm, const ScDPObject& rDPObj, sal_Int32 nCacheId )
@@ -1083,6 +1114,15 @@ void XclExpXmlPivotTables::SavePivotTableXml( XclExpXmlStream& rStrm, const ScDP
         pPivotStrm->endElement(XML_dataFields);
     }
 
+    // Now add style info (use grab bag, or just a set which is default on Excel 2007 through 2016)
+    if (const auto [bHas, aVal] = rDPObj.GetInteropGrabBagValue("pivotTableStyleInfo"); bHas)
+        WriteGrabBagItemToStream(rStrm, XML_pivotTableStyleInfo, aVal);
+    else
+        pPivotStrm->singleElement(XML_pivotTableStyleInfo, XML_name, "PivotStyleLight16",
+                                  XML_showRowHeaders, "1", XML_showColHeaders, "1",
+                                  XML_showRowStripes, "0", XML_showColStripes, "0",
+                                  XML_showLastColumn, "1");
+
     OUStringBuffer aBuf("../pivotCache/pivotCacheDefinition");
     aBuf.append(nCacheId);
     aBuf.append(".xml");
diff --git a/sc/source/filter/inc/pivottablebuffer.hxx b/sc/source/filter/inc/pivottablebuffer.hxx
index 75f8b78006ef..8b769a68308e 100644
--- a/sc/source/filter/inc/pivottablebuffer.hxx
+++ b/sc/source/filter/inc/pivottablebuffer.hxx
@@ -297,6 +297,8 @@ public:
     void                importPageField( const AttributeList& rAttribs );
     /** Reads the settings of a field located in the data dimension from the dataField element. */
     void                importDataField( const AttributeList& rAttribs );
+    /** Puts the attributes to the named grab bag value. */
+    void putToInteropGrabBag(const OUString& sName, const AttributeList& rAttribs);
 
     /** Reads global pivot table settings from the PTDEFINITION record. */
     void                importPTDefinition( SequenceInputStream& rStrm );
@@ -379,6 +381,7 @@ private:
     PivotCache*           mpPivotCache;       /// The pivot cache this table is based on.
     css::uno::Reference< css::sheet::XDataPilotDescriptor >
                           mxDPDescriptor;     /// Descriptor of the DataPilot object.
+    std::map<OUString, css::uno::Any> maInteropGrabBag;
 
 };
 
diff --git a/sc/source/filter/oox/pivottablebuffer.cxx b/sc/source/filter/oox/pivottablebuffer.cxx
index b52ca269f0c9..772637feb674 100644
--- a/sc/source/filter/oox/pivottablebuffer.cxx
+++ b/sc/source/filter/oox/pivottablebuffer.cxx
@@ -37,6 +37,7 @@
 #include <com/sun/star/sheet/XDataPilotDataLayoutFieldSupplier.hpp>
 #include <com/sun/star/sheet/XDataPilotTablesSupplier.hpp>
 #include <com/sun/star/sheet/XSheetOperation.hpp>
+#include <com/sun/star/xml/sax/XFastAttributeList.hpp>
 #include <osl/diagnose.h>
 #include <sal/log.hxx>
 #include <oox/helper/binaryinputstream.hxx>
@@ -1038,6 +1039,18 @@ void PivotTable::importDataField( const AttributeList& rAttribs )
     maDataFields.push_back( aModel );
 }
 
+void PivotTable::putToInteropGrabBag(const OUString& sName, const AttributeList& rAttribs)
+{
+    if (auto xFastAttributeList = rAttribs.getFastAttributeList())
+    {
+        // Store both known and unknown attribute sequences to the grab bag as is
+        css::uno::Sequence<css::xml::FastAttribute> aFast = xFastAttributeList->getFastAttributes();
+        css::uno::Sequence<css::xml::Attribute> aUnk = xFastAttributeList->getUnknownAttributes();
+        css::uno::Sequence<css::uno::Any> aVal{ css::uno::Any(aFast), css::uno::Any(aUnk) };
+        maInteropGrabBag[sName] <<= aVal;
+    }
+}
+
 void PivotTable::importPTDefinition( SequenceInputStream& rStrm )
 {
     sal_uInt32 nFlags1, nFlags2, nFlags3;
@@ -1283,6 +1296,9 @@ void PivotTable::finalizeImport()
                 if( !maPageFields.empty() )
                     aPos.Row = ::std::max< sal_Int32 >( static_cast< sal_Int32 >( aPos.Row - maPageFields.size() - 1 ), 0 );
 
+                // save interop grab bag
+                mpDPObject->PutInteropGrabBag(std::move(maInteropGrabBag));
+
                 // insert the DataPilot table into the sheet
                 xDPTables->insertNewByName( maDefModel.maName, aPos, mxDPDescriptor );
             }
diff --git a/sc/source/filter/oox/pivottablefragment.cxx b/sc/source/filter/oox/pivottablefragment.cxx
index 59800b026bb4..9ce2bb7eb790 100644
--- a/sc/source/filter/oox/pivottablefragment.cxx
+++ b/sc/source/filter/oox/pivottablefragment.cxx
@@ -181,6 +181,9 @@ ContextHandlerRef PivotTableFragment::onCreateContext( sal_Int32 nElement, const
                 case XLS_TOKEN( pageFields ):   return this;
                 case XLS_TOKEN( dataFields ):   return this;
                 case XLS_TOKEN( filters ):      return this;
+                case XLS_TOKEN(pivotTableStyleInfo):
+                    mrPivotTable.putToInteropGrabBag("pivotTableStyleInfo", rAttribs);
+                    break;
             }
         break;