tdf#123939: string and error are same type for pivot cache in XLSX

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

3 files changed, 34 insertions, 2 deletions

diff --git a/sc/qa/unit/data/ods/pivot-table-str-and-err-in-data.ods b/sc/qa/unit/data/ods/pivot-table-str-and-err-in-data.ods
new file mode 100644
index 000000000000..9c58dc8958ae
--- /dev/null
+++ b/sc/qa/unit/data/ods/pivot-table-str-and-err-in-data.ods
diff --git a/sc/qa/unit/pivottable_filters_test.cxx b/sc/qa/unit/pivottable_filters_test.cxx
index 5e737486d5b3..014c1fdab035 100644
--- a/sc/qa/unit/pivottable_filters_test.cxx
+++ b/sc/qa/unit/pivottable_filters_test.cxx
@@ -82,6 +82,7 @@ public:
     void testPivotTableTabularModeXLSX();
     void testTdf112106();
     void testTdf123923();
+    void testTdf123939();
 
     CPPUNIT_TEST_SUITE(ScPivotTableFiltersTest);
 
@@ -121,6 +122,7 @@ public:
     CPPUNIT_TEST(testPivotTableTabularModeXLSX);
     CPPUNIT_TEST(testTdf112106);
     CPPUNIT_TEST(testTdf123923);
+    CPPUNIT_TEST(testTdf123939);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -2345,6 +2347,33 @@ void ScPivotTableFiltersTest::testTdf123923()
                 "v", "#REF!");
 }
 
+void ScPivotTableFiltersTest::testTdf123939()
+{
+    // tdf#123939: Excel warns on containsMixedTypes="1" if sharedItems has only strings and errors
+
+    ScDocShellRef xShell = loadDoc("pivot-table-str-and-err-in-data.", FORMAT_ODS);
+    CPPUNIT_ASSERT(xShell.is());
+
+    std::shared_ptr<utl::TempFile> pXPathFile
+        = ScBootstrapFixture::exportTo(&(*xShell), FORMAT_XLSX);
+    xmlDocPtr pTable = XPathHelper::parseExport(pXPathFile, m_xSFactory,
+                                                "xl/pivotCache/pivotCacheDefinition1.xml");
+    CPPUNIT_ASSERT(pTable);
+
+    assertXPathNoAttribute(pTable,
+                           "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems",
+                           "containsMixedTypes");
+
+    // But we must emit containsMixedTypes="1" for a mix of errors and non-string types!
+
+    pTable = XPathHelper::parseExport(pXPathFile, m_xSFactory,
+                                      "xl/pivotCache/pivotCacheDefinition2.xml");
+    CPPUNIT_ASSERT(pTable);
+
+    assertXPath(pTable, "/x:pivotCacheDefinition/x:cacheFields/x:cacheField[1]/x:sharedItems",
+                "containsMixedTypes", "1");
+}
+
 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 b7fbd6bc1556..eed70122dfd8 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -261,6 +261,9 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
         for (const auto& rFieldItem : rFieldItems)
         {
             ScDPItemData::Type eType = rFieldItem.GetType();
+            // tdf#123939 : error and string are same for cache; if both are present, keep only one
+            if (eType == ScDPItemData::Error)
+                eType = ScDPItemData::String;
             aDPTypes.insert(eType);
             if (eType == ScDPItemData::Value)
             {
@@ -286,8 +289,8 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
         std::set<ScDPItemData::Type> aDPTypesWithoutBlank = aDPTypes;
         aDPTypesWithoutBlank.erase(ScDPItemData::Empty);
 
-        bool isContainsString = aDPTypesWithoutBlank.find(ScDPItemData::String) != aDPTypesWithoutBlank.end() ||
-                                aDPTypesWithoutBlank.find(ScDPItemData::Error) != aDPTypesWithoutBlank.end();
+        const bool isContainsString
+            = aDPTypesWithoutBlank.find(ScDPItemData::String) != aDPTypesWithoutBlank.end();
         bool isContainsBlank = aDPTypes.find(ScDPItemData::Empty) != aDPTypeEnd;
         bool isContainsNumber = !isContainsDate && aDPTypesWithoutBlank.find(ScDPItemData::Value) != aDPTypesWithoutBlank.end();
         bool isContainsNonDate = !(isContainsDate && aDPTypesWithoutBlank.size() <= 1);