Resolves: tdf#35636 implement match on empty cells

This implements search criteria "" and "=" to match empty cells in
spreadsheet functions SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS and
COUNTIFS.

Change-Id: I1b4a4c14bac7b974428bf64afb549707a0d75a90
(cherry picked from commit 01b615687fe0f39c65e0e8290db434db2f1ef8ac)
Reviewed-on: https://gerrit.libreoffice.org/15091
Reviewed-by: Caolán McNamara <caolanm@redhat.com>
Tested-by: Caolán McNamara <caolanm@redhat.com>

4 files changed, 21 insertions, 18 deletions

diff --git a/sc/source/core/tool/doubleref.cxx b/sc/source/core/tool/doubleref.cxx
index 5de3489d0dd6..4f4c523b47b3 100644
--- a/sc/source/core/tool/doubleref.cxx
+++ b/sc/source/core/tool/doubleref.cxx
@@ -191,7 +191,7 @@ bool lcl_createExcelQuery(
                     if (nIndex < nNewEntries)
                     {
                         pParam->GetEntry(nIndex).nField = aFields[nCol];
-                        pParam->FillInExcelSyntax(rPool, aCellStr, nIndex);
+                        pParam->FillInExcelSyntax(rPool, aCellStr, nIndex, NULL);
                         nIndex++;
                         if (nIndex < nNewEntries)
                             pParam->GetEntry(nIndex).eConnect = SC_AND;
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 7715ce586282..d79a138205bb 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -4863,11 +4863,7 @@ double ScInterpreter::IterateParametersIf( ScIterFuncIf eFunc )
             }
             else
             {
-                rParam.FillInExcelSyntax(pDok->GetSharedStringPool(), aString.getString(), 0);
-                sal_uInt32 nIndex = 0;
-                bool bNumber = pFormatter->IsNumberFormat(
-                        rItem.maString.getString(), nIndex, rItem.mfVal);
-                rItem.meType = bNumber ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
+                rParam.FillInExcelSyntax(pDok->GetSharedStringPool(), aString.getString(), 0, pFormatter);
                 if (rItem.meType == ScQueryEntry::ByString)
                     rParam.bRegExp = MayBeRegExp(rItem.maString.getString(), pDok);
             }
@@ -5162,11 +5158,7 @@ void ScInterpreter::ScCountIf()
                 }
                 else
                 {
-                    rParam.FillInExcelSyntax(pDok->GetSharedStringPool(), aString.getString(), 0);
-                    sal_uInt32 nIndex = 0;
-                    bool bNumber = pFormatter->IsNumberFormat(
-                            rItem.maString.getString(), nIndex, rItem.mfVal);
-                    rItem.meType = bNumber ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
+                    rParam.FillInExcelSyntax(pDok->GetSharedStringPool(), aString.getString(), 0, pFormatter);
                     if (rItem.meType == ScQueryEntry::ByString)
                         rParam.bRegExp = MayBeRegExp(rItem.maString.getString(), pDok);
                 }
@@ -5420,11 +5412,7 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc )
             }
             else
             {
-                rParam.FillInExcelSyntax(pDok->GetSharedStringPool(), aString.getString(), 0);
-                sal_uInt32 nIndex = 0;
-                bool bNumber = pFormatter->IsNumberFormat(
-                        rItem.maString.getString(), nIndex, rItem.mfVal);
-                rItem.meType = bNumber ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
+                rParam.FillInExcelSyntax(pDok->GetSharedStringPool(), aString.getString(), 0, pFormatter);
                 if (rItem.meType == ScQueryEntry::ByString)
                     rParam.bRegExp = MayBeRegExp(rItem.maString.getString(), pDok);
             }
diff --git a/sc/source/core/tool/queryentry.cxx b/sc/source/core/tool/queryentry.cxx
index 0b23da5add61..b650576995e6 100644
--- a/sc/source/core/tool/queryentry.cxx
+++ b/sc/source/core/tool/queryentry.cxx
@@ -32,7 +32,7 @@
 
 bool ScQueryEntry::Item::operator== (const Item& r) const
 {
-    return meType == r.meType && mfVal == r.mfVal && maString == r.maString;
+    return meType == r.meType && mfVal == r.mfVal && maString == r.maString && mbMatchEmpty == r.mbMatchEmpty;
 }
 
 ScQueryEntry::ScQueryEntry() :
diff --git a/sc/source/core/tool/queryparam.cxx b/sc/source/core/tool/queryparam.cxx
index 00c7ba333880..099b55d4622e 100644
--- a/sc/source/core/tool/queryparam.cxx
+++ b/sc/source/core/tool/queryparam.cxx
@@ -22,6 +22,7 @@
 #include "scmatrix.hxx"
 
 #include <svl/sharedstringpool.hxx>
+#include <svl/zforlist.hxx>
 #include <osl/diagnose.h>
 
 namespace {
@@ -171,7 +172,7 @@ void ScQueryParamBase::Resize(size_t nNew)
 }
 
 void ScQueryParamBase::FillInExcelSyntax(
-    svl::SharedStringPool& rPool, const OUString& rStr, SCSIZE nIndex)
+    svl::SharedStringPool& rPool, const OUString& rStr, SCSIZE nIndex, SvNumberFormatter* pFormatter )
 {
     const OUString aCellStr = rStr;
     if (nIndex >= maEntries.size())
@@ -226,6 +227,20 @@ void ScQueryParamBase::FillInExcelSyntax(
             rEntry.eOp = SC_EQUAL;
         }
     }
+
+    if (pFormatter)
+    {
+        sal_uInt32 nFormat = 0;
+        bool bNumber = pFormatter->IsNumberFormat( rItem.maString.getString(), nFormat, rItem.mfVal);
+        rItem.meType = bNumber ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
+
+        /* TODO: pFormatter currently is also used as a flag whether matching
+         * empty cells with an empty string is triggered from the interpreter.
+         * This could be handled independently if all queries should support
+         * it, needs to be evaluated if that actually is desired. */
+        if (rItem.meType == ScQueryEntry::ByString)
+            rItem.mbMatchEmpty = (rEntry.eOp == SC_EQUAL && rItem.maString.isEmpty());
+    }
 }
 
 ScQueryParamTable::ScQueryParamTable() :