tdf#124772: export data field number format to XLSX

... otherwise Excel would reset data formatting e.g. from currency
to plain numbers upon refresh.

Excel relies on per-field format setting in pivot tables, while Calc
takes fields formatting from source.

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

3 files changed, 61 insertions, 10 deletions

diff --git a/sc/qa/unit/data/ods/pivot-table-num-fmt.ods b/sc/qa/unit/data/ods/pivot-table-num-fmt.ods
new file mode 100644
index 000000000000..e6cfd138c12d
--- /dev/null
+++ b/sc/qa/unit/data/ods/pivot-table-num-fmt.ods
diff --git a/sc/qa/unit/pivottable_filters_test.cxx b/sc/qa/unit/pivottable_filters_test.cxx
index e6ccb4914ef9..f9d10f9f4451 100644
--- a/sc/qa/unit/pivottable_filters_test.cxx
+++ b/sc/qa/unit/pivottable_filters_test.cxx
@@ -88,6 +88,7 @@ public:
     void testTdf123939();
     void testTdf124651();
     void testTdf124736();
+    void tesTtdf124772NumFmt();
 
     CPPUNIT_TEST_SUITE(ScPivotTableFiltersTest);
 
@@ -133,6 +134,7 @@ public:
     CPPUNIT_TEST(testTdf123939);
     CPPUNIT_TEST(testTdf124651);
     CPPUNIT_TEST(testTdf124736);
+    CPPUNIT_TEST(tesTtdf124772NumFmt);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -2538,6 +2540,31 @@ void ScPivotTableFiltersTest::testTdf124736()
                 "t", "default");
 }
 
+void ScPivotTableFiltersTest::tesTtdf124772NumFmt()
+{
+    ScDocShellRef xDocSh = loadDoc("pivot-table-num-fmt.", FORMAT_ODS);
+    CPPUNIT_ASSERT(xDocSh.is());
+
+    std::shared_ptr<utl::TempFile> pXPathFile
+        = ScBootstrapFixture::exportTo(xDocSh.get(), FORMAT_XLSX);
+    xDocSh->DoClose();
+
+    xmlDocPtr pTable
+        = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/pivotTables/pivotTable1.xml");
+    CPPUNIT_ASSERT(pTable);
+
+    // This asserts that numFmtId attribute is present
+    const OUString sXclNumFmt
+        = getXPath(pTable, "/x:pivotTableDefinition/x:dataFields/x:dataField", "numFmtId");
+
+    pTable = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/styles.xml");
+    CPPUNIT_ASSERT(pTable);
+
+    // Check that we refer to correct format
+    assertXPath(pTable, "/x:styleSheet/x:numFmts/x:numFmt[@numFmtId='" + sXclNumFmt.toUtf8() + "']",
+                "formatCode", "\\$#,##0");
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScPivotTableFiltersTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/filter/excel/xepivotxml.cxx b/sc/source/filter/excel/xepivotxml.cxx
index 2161f68d6478..43dc30196160 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -16,6 +16,9 @@
 #include <dputil.hxx>
 #include <document.hxx>
 #include <generalfunction.hxx>
+#include <unonames.hxx>
+#include <xestyle.hxx>
+#include <xeroot.hxx>
 
 #include <o3tl/temporary.hxx>
 #include <oox/export/utils.hxx>
@@ -23,10 +26,12 @@
 #include <sax/tools/converter.hxx>
 #include <sax/fastattribs.hxx>
 
+#include <com/sun/star/beans/XPropertySet.hpp>
 #include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
 #include <com/sun/star/sheet/DataPilotFieldOrientation.hpp>
 #include <com/sun/star/sheet/DataPilotFieldLayoutMode.hpp>
 #include <com/sun/star/sheet/DataPilotOutputRangeType.hpp>
+#include <com/sun/star/sheet/XDimensionsSupplier.hpp>
 
 #include <vector>
 
@@ -1063,6 +1068,10 @@ void XclExpXmlPivotTables::SavePivotTableXml( XclExpXmlStream& rStrm, const ScDP
 
     if (!aDataFields.empty())
     {
+        css::uno::Reference<css::container::XNameAccess> xDimsByName;
+        if (auto xDimSupplier = const_cast<ScDPObject&>(rDPObj).GetSource())
+            xDimsByName = xDimSupplier->getDimensions();
+
         pPivotStrm->startElement(XML_dataFields,
             XML_count, OString::number(static_cast<long>(aDataFields.size())),
             FSEND);
@@ -1077,17 +1086,32 @@ void XclExpXmlPivotTables::SavePivotTableXml( XclExpXmlStream& rStrm, const ScDP
             // Excel (at least 2016) seems to insist on the presence of "name" attribute in
             // dataField element, even if empty
             const OString sName = pName ? pName->toUtf8() : "";
-            pPivotStrm->write("<")->writeId(XML_dataField);
-            rStrm.WriteAttributes(XML_name, sName, FSEND);
-
-            rStrm.WriteAttributes(XML_fld, OString::number(nDimIdx).getStr(), FSEND);
-
-            ScGeneralFunction eFunc = rDim.GetFunction();
-            const char* pSubtotal = toOOXMLSubtotalType(eFunc);
+            auto pItemAttList = sax_fastparser::FastSerializerHelper::createAttrList();
+            pItemAttList->add(XML_name, sName);
+            pItemAttList->add(XML_fld, OString::number(nDimIdx));
+            const char* pSubtotal = toOOXMLSubtotalType(rDim.GetFunction());
             if (pSubtotal)
-                rStrm.WriteAttributes(XML_subtotal, pSubtotal, FSEND);
-
-            pPivotStrm->write("/>");
+                pItemAttList->add(XML_subtotal, pSubtotal);
+            if (xDimsByName)
+            {
+                try
+                {
+                    css::uno::Reference<css::beans::XPropertySet> xDimProps(
+                        xDimsByName->getByName(rDim.GetName()), uno::UNO_QUERY_THROW);
+                    css::uno::Any aVal = xDimProps->getPropertyValue(SC_UNONAME_NUMFMT);
+                    sal_uInt32 nScNumFmt = aVal.get<sal_uInt32>();
+                    sal_uInt16 nXclNumFmt = GetRoot().GetNumFmtBuffer().Insert(nScNumFmt);
+                    pItemAttList->add(XML_numFmtId, OString::number(nXclNumFmt));
+                }
+                catch (uno::Exception&)
+                {
+                    SAL_WARN("sc.filter",
+                             "Couldn't get number format for data field " << rDim.GetName());
+                    // Just skip exporting number format
+                }
+            }
+            sax_fastparser::XFastAttributeListRef xItemAttributeList(pItemAttList);
+            pPivotStrm->singleElement(XML_dataField, xItemAttributeList);
         }
 
         pPivotStrm->endElement(XML_dataFields);