Related: tdf#140968 avoid duplicated filter values

Group and filter based on the actual cell format,
like MSO does, to simplify filtering of values rounded by
the cell format (e.g. selecting the single AutoFilter condition
"1.0" to filter both 1.01 and 0.99).

Followed up of 4fd1333ba4bb4f2311e9098291154772bd310429
(tdf#140968 tdf#140978 XLSX import: fix lost rounded filters)

tdf#141916: odf export: Indicating the formatted filter values,
by export the XML_DATA_TYPE with XML_TEXT, to avoid bad filtering
of formatted filter values.

tdf#141775: fix regression of 4fd1333ba4bb4f2311e9098291154772bd310429
(tdf#140968 tdf#140978 XLSX import: fix lost rounded filters)

tdf#141915: fix OOXML import of 0.0 filter values of filter conditions
by setting it byValue filtering in datauno.cxx.

Change-Id: I1c840249ee1ef95aff33a4740b8bf2ebc47f2325
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/113314
Tested-by: Jenkins
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>

2 files changed, 52 insertions, 8 deletions

diff --git a/sc/source/filter/xml/XMLExportDatabaseRanges.cxx b/sc/source/filter/xml/XMLExportDatabaseRanges.cxx
index bbcba23903e9..d329bdf2385f 100644
--- a/sc/source/filter/xml/XMLExportDatabaseRanges.cxx
+++ b/sc/source/filter/xml/XMLExportDatabaseRanges.cxx
@@ -415,11 +415,25 @@ private:
     class WriteSetItem
     {
         ScXMLExport& mrExport;
+        const ScDocument* mpDoc;
     public:
-        explicit WriteSetItem(ScXMLExport& r) : mrExport(r) {}
+        explicit WriteSetItem(ScXMLExport& r, const ScDocument* pDoc) : mrExport(r), mpDoc(pDoc) {}
         void operator() (const ScQueryEntry::Item& rItem) const
         {
-            mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
+            if (rItem.meType == ScQueryEntry::ByValue)
+            {
+                OUString aValStr;
+                SvNumberFormatter* pFormatter = mpDoc->GetFormatTable();
+                pFormatter->GetInputLineString(rItem.mfVal, 0, aValStr);
+                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, aValStr);
+            }
+            else
+            {
+                // Indicating the formatted filter values, by export the XML_DATA_TYPE with XML_TEXT
+                if (rItem.meType == ScQueryEntry::ByString && rItem.mbFormattedValue)
+                    mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_TEXT);
+                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
+            }
             SvXMLElementExport aElem(mrExport, XML_NAMESPACE_TABLE, XML_FILTER_SET_ITEM, true, true);
         }
     };
@@ -443,7 +457,12 @@ private:
             // Single item condition.
             const ScQueryEntry::Item& rItem = rItems.front();
             if (rItem.meType == ScQueryEntry::ByString)
+            {
+                // Indicating the formatted filter values, by export the XML_DATA_TYPE with XML_TEXT
+                if (rItem.mbFormattedValue)
+                    mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_TEXT);
                 mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
+            }
             else
             {
                 mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_NUMBER);
@@ -462,11 +481,23 @@ private:
 
             // Store the 1st value for backward compatibility.
             const ScQueryEntry::Item& rItem = rItems.front();
-            mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
+            if (rItem.meType == ScQueryEntry::ByValue)
+            {
+                OUString aValStr;
+                SvNumberFormatter* pFormatter = mpDoc->GetFormatTable();
+                pFormatter->GetInputLineString(rItem.mfVal, 0, aValStr);
+                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, aValStr);
+            }
+            else
+            {
+                if (rItem.mbFormattedValue)
+                    mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_TEXT);
+                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
+            }
             mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_OPERATOR, OUString("="));
             SvXMLElementExport aElemC(mrExport, XML_NAMESPACE_TABLE, XML_FILTER_CONDITION, true, true);
 
-            std::for_each(rItems.begin(), rItems.end(), WriteSetItem(mrExport));
+            std::for_each(rItems.begin(), rItems.end(), WriteSetItem(mrExport, mpDoc));
         }
     }
 
diff --git a/sc/source/filter/xml/xmlfilti.cxx b/sc/source/filter/xml/xmlfilti.cxx
index 5b5708e7a68b..7e22395a90d9 100644
--- a/sc/source/filter/xml/xmlfilti.cxx
+++ b/sc/source/filter/xml/xmlfilti.cxx
@@ -281,7 +281,7 @@ ScXMLConditionContext::ScXMLConditionContext(
     ScXMLImportContext( rImport ),
     mrQueryParam(rParam),
     pFilterContext(pTempFilterContext),
-    sDataType(GetXMLToken(XML_TEXT)),
+    sDataType(OUString()),
     nField(0),
     bIsCaseSensitive(false)
 {
@@ -430,6 +430,8 @@ void SAL_CALL ScXMLConditionContext::endFastElement( sal_Int32 /*nElement*/ )
             svl::SharedStringPool& rPool = GetScImport().GetDocument()->GetSharedStringPool();
             rItem.maString = rPool.intern(sConditionValue);
             rItem.meType = ScQueryEntry::ByString;
+            if (IsXMLToken(sDataType, XML_TEXT))
+                rItem.mbFormattedValue = true;
         }
     }
     else
@@ -444,22 +446,31 @@ ScXMLSetItemContext::ScXMLSetItemContext(
     if ( !rAttrList.is() )
         return;
 
+    ScQueryEntry::Item aItem;
+    bool bAddSetItem = false;
+
     for (auto &aIter : *rAttrList)
     {
         switch (aIter.getToken())
         {
+            case XML_ELEMENT( TABLE, XML_DATA_TYPE ):
+            {
+                aItem.mbFormattedValue = IsXMLToken(aIter.toString(), XML_TEXT);
+            }
+            break;
             case XML_ELEMENT( TABLE, XML_VALUE ):
             {
                 svl::SharedStringPool& rPool = GetScImport().GetDocument()->GetSharedStringPool();
-                ScQueryEntry::Item aItem;
                 aItem.maString = rPool.intern(aIter.toString());
                 aItem.meType = ScQueryEntry::ByString;
                 aItem.mfVal = 0.0;
-                rParent.AddSetItem(aItem);
+                bAddSetItem = true;
             }
             break;
         }
     }
+    if (bAddSetItem)
+        rParent.AddSetItem(aItem);
 }
 
 ScXMLSetItemContext::~ScXMLSetItemContext()
@@ -649,7 +660,7 @@ ScXMLDPConditionContext::ScXMLDPConditionContext( ScXMLImport& rImport,
                                       ScXMLDPFilterContext* pTempFilterContext) :
     ScXMLImportContext( rImport ),
     pFilterContext(pTempFilterContext),
-    sDataType(GetXMLToken(XML_TEXT)),
+    sDataType(OUString()),
     nField(0),
     bIsCaseSensitive(false)
 {
@@ -762,6 +773,8 @@ void SAL_CALL ScXMLDPConditionContext::endFastElement( sal_Int32 /*nElement*/ )
             rItem.maString = rPool.intern(sConditionValue);
             rItem.meType = ScQueryEntry::ByString;
             rItem.mfVal = 0.0;
+            if (IsXMLToken(sDataType, XML_TEXT))
+                rItem.mbFormattedValue = true;
         }
     }
     pFilterContext->AddFilterField(aFilterField);