tdf#89139 Fix PivotCache fields according to OOXML specification

Apply changes to fields:
- XML_containsInteger
- XML_containsBlank
- XML_containsMixedTypes
- XML_containsSemiMixedTypes
According to OOXML specification
https://technet.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.shareditems.aspx

Generally OOXML specification allows listing items for mixed items (example: STRING + NUMBERS).
This patch is fixing that.
Example of mixed types:
<cacheField name="pwdLastSet" numFmtId="0">
<sharedItems containsDate="1" containsBlank="1" containsMixedTypes="1" minDate="2014-07-07T09:30:31" maxDate="2017-03-24T08:38:46" count="4">
<m/>
<d v="2016-06-15T12:18:34"/>
<s v=""/>
<d v="2017-03-21T10:27:39"/>
</sharedItems>
</cacheField>

Change-Id: I02b07c79bea60890e3c995dd70cb5c72901a3d4a
Reviewed-on: https://gerrit.libreoffice.org/40610
Reviewed-by: Bartosz Kosiorek <gang65@poczta.onet.pl>
Tested-by: Bartosz Kosiorek <gang65@poczta.onet.pl>

1 files changed, 63 insertions, 16 deletions

diff --git a/sc/source/filter/excel/xepivotxml.cxx b/sc/source/filter/excel/xepivotxml.cxx
index 9f1da86dcb9b..c5872e24da4a 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -234,6 +234,8 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
 
         std::set<ScDPItemData::Type> aDPTypes;
         double fMin = std::numeric_limits<double>::infinity(), fMax = -std::numeric_limits<double>::infinity();
+        bool isValueInteger = true;
+        double intpart;
         for (; it != itEnd; ++it)
         {
             ScDPItemData::Type eType = it->GetType();
@@ -243,31 +245,76 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr
                 double fVal = it->GetValue();
                 fMin = std::min(fMin, fVal);
                 fMax = std::max(fMax, fVal);
+                // Check if all values are integers
+                if (isValueInteger && (modf(fVal, &intpart) != 0.0))
+                {
+                    isValueInteger = false;
+                }
             }
         }
 
         auto aDPTypeEnd = aDPTypes.cend();
 
         auto pAttList = sax_fastparser::FastSerializerHelper::createAttrList();
-        // tdf#89139: Only create item list for string-only fields.
-        // Using containsXXX attributes in this case makes Excel think the file is corrupted.
-        // OTOH listing items for e.g. number fields also triggers "corrupted" warning in Excel.
-        bool bListItems = aDPTypes.size() == 1 && aDPTypes.find(ScDPItemData::String) != aDPTypeEnd;
-        if (bListItems)
+
+        bool bListItems = true;
+
+        std::set<ScDPItemData::Type> aDPTypesWithoutBlank = aDPTypes;
+        aDPTypesWithoutBlank.erase(ScDPItemData::Empty);
+
+        bool isContainsMoreThanOneType = aDPTypesWithoutBlank.size() > 1;
+        // XML_containsMixedType possible values:
+        // 1 - field contains more than one data type
+        // 0 - (Default) only one data type. The field can still contain blank values (that's why we are using aDPTypesWithoutBlank)
+        if (isContainsMoreThanOneType)
+            pAttList->add(XML_containsMixedTypes, ToPsz10(true));
+
+        bool isContainsString = aDPTypesWithoutBlank.find(ScDPItemData::String) != aDPTypesWithoutBlank.end();
+        // XML_containsSemiMixedTypes possible values:
+        // 1 - (Default) at least one text value, or can also contain a mix of other data types and blank values
+        // 0 - the field does not have a mix of text and other values
+        if (!(isContainsString || (aDPTypes.size() > 1)))
+            pAttList->add(XML_containsSemiMixedTypes, ToPsz10(false));
+
+        // default for containsString field is true, so we are writing only when is false
+        if (!isContainsString)
+            pAttList->add(XML_containsString, ToPsz10(false));
+
+        bool isContainsBlank = aDPTypes.find(ScDPItemData::Empty) != aDPTypeEnd;
+        if (isContainsBlank)
+            pAttList->add(XML_containsBlank, ToPsz10(true));
+
+        bool isContainsNumber = aDPTypesWithoutBlank.find(ScDPItemData::Value) != aDPTypesWithoutBlank.end();
+        if (isContainsNumber)
+            pAttList->add(XML_containsNumber, ToPsz10(true));
+
+        if (isValueInteger && isContainsNumber)
+            pAttList->add(XML_containsInteger, ToPsz10(true));
+
+
+        // Number type fields could be mixed with blank types, and it shouldn't be treated as listed items.
+        // Example:
+        //    <cacheField name="employeeID" numFmtId="0">
+        //        <sharedItems containsString="0" containsBlank="1" containsNumber="1" containsInteger="1" minValue="35" maxValue="89"/>
+        //    </cacheField>
+        if (isContainsNumber)
         {
-            pAttList->add(XML_count, OString::number(static_cast<long>(rFieldItems.size())));
+            pAttList->add(XML_minValue, OString::number(fMin));
+            pAttList->add(XML_maxValue, OString::number(fMax));
+            // If there is only numeric types, then we shouldn't list all items
+            if  (aDPTypesWithoutBlank.size() == 1)
+                bListItems = false;
         }
-        else
+
+        if (isContainsBlank && (aDPTypes.size() == 1))
         {
-            pAttList->add(XML_containsMixedTypes, ToPsz10(aDPTypes.size() > 1));
-            pAttList->add(XML_containsSemiMixedTypes, ToPsz10(aDPTypes.size() > 1));
-            pAttList->add(XML_containsString, ToPsz10(aDPTypes.find(ScDPItemData::String) != aDPTypeEnd));
-            if (aDPTypes.find(ScDPItemData::Value) != aDPTypeEnd)
-            {
-                pAttList->add(XML_containsNumber, ToPsz10(true));
-                pAttList->add(XML_minValue, OString::number(fMin));
-                pAttList->add(XML_maxValue, OString::number(fMax));
-            }
+            // If all items are blank, then we shouldn't list all items
+            bListItems = false;
+        }
+
+        if (bListItems)
+        {
+            pAttList->add(XML_count, OString::number(static_cast<long>(rFieldItems.size())));
         }
         sax_fastparser::XFastAttributeListRef xAttributeList(pAttList);