tdf#112733: Pivot table is displayed wrong in Excel after saved in LO Calc

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

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

3 files changed, 24 insertions, 1 deletions

diff --git a/sc/qa/unit/data/xlsx/pivot_table_first_header_row.xlsx b/sc/qa/unit/data/xlsx/pivot_table_first_header_row.xlsx
new file mode 100755
index 000000000000..664637d4bd57
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/pivot_table_first_header_row.xlsx
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 9466fbe8f28d..33c749382d36 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -207,6 +207,7 @@ public:
     void testPivotTableRowColPageFieldFilter();
     void testPivotTableEmptyItem();
     void testPivotTablePageFieldFilter();
+    void testPivotTableFirstHeaderRowXLSX();
 
     CPPUNIT_TEST_SUITE(ScExportTest);
     CPPUNIT_TEST(test);
@@ -314,6 +315,7 @@ public:
     CPPUNIT_TEST(testPivotTableRowColPageFieldFilter);
     CPPUNIT_TEST(testPivotTableEmptyItem);
     CPPUNIT_TEST(testPivotTablePageFieldFilter);
+    CPPUNIT_TEST(testPivotTableFirstHeaderRowXLSX);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -4760,6 +4762,27 @@ void ScExportTest::testPivotTablePageFieldFilter()
     xDocSh->DoClose();
 }
 
+void ScExportTest::testPivotTableFirstHeaderRowXLSX()
+{
+    // tdf#112733: We have different tables here, but have the same value as firstHeaderRow
+    // The documentation is not clear about thit firstHeaderRow actually measn, but MS Excel works on this way
+    ScDocShellRef xShell = loadDoc("pivot_table_first_header_row.", FORMAT_XLSX);
+    CPPUNIT_ASSERT(xShell.Is());
+
+    std::shared_ptr<utl::TempFile> pXPathFile = ScBootstrapFixture::exportTo(&(*xShell), FORMAT_XLSX);
+    xmlDocPtr pTable = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/pivotTables/pivotTable1.xml");
+    CPPUNIT_ASSERT(pTable);
+    assertXPath(pTable, "/x:pivotTableDefinition/x:location", "firstHeaderRow", "1");
+
+    pTable = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/pivotTables/pivotTable2.xml");
+    CPPUNIT_ASSERT(pTable);
+    assertXPath(pTable, "/x:pivotTableDefinition/x:location", "firstHeaderRow", "1");
+
+    pTable = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/pivotTables/pivotTable3.xml");
+    CPPUNIT_ASSERT(pTable);
+    assertXPath(pTable, "/x:pivotTableDefinition/x:location", "firstHeaderRow", "1");
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScExportTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/filter/excel/xepivotxml.cxx b/sc/source/filter/excel/xepivotxml.cxx
index dcbbb0fdb518..b50182e157d8 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -679,7 +679,7 @@ void XclExpXmlPivotTables::SavePivotTableXml( XclExpXmlStream& rStrm, const ScDP
     // NB: Excel's range does not include page field area (if any).
     ScRange aOutRange = rDPObj.GetOutputRangeByType(sheet::DataPilotOutputRangeType::TABLE);
 
-    sal_Int32 nFirstHeaderRow = aColFields.size();
+    sal_Int32 nFirstHeaderRow = rDPObj.GetHeaderLayout() ? 2 : 1;
     sal_Int32 nFirstDataRow = 2;
     sal_Int32 nFirstDataCol = 1;
     ScRange aResRange = rDPObj.GetOutputRangeByType(sheet::DataPilotOutputRangeType::RESULT);