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

8 files changed, 46 insertions, 23 deletions

diff --git a/sc/inc/queryentry.hxx b/sc/inc/queryentry.hxx
index f024e6de2753..cac117061fe8 100644
--- a/sc/inc/queryentry.hxx
+++ b/sc/inc/queryentry.hxx
@@ -43,8 +43,9 @@ struct SC_DLLPUBLIC ScQueryEntry
         QueryType     meType;
         double        mfVal;
         svl::SharedString maString;
+        bool              mbMatchEmpty;
 
-        Item() : meType(ByValue), mfVal(0.0) {}
+        Item() : meType(ByValue), mfVal(0.0), mbMatchEmpty(false) {}
 
         bool operator== (const Item& r) const;
     };
diff --git a/sc/inc/queryparam.hxx b/sc/inc/queryparam.hxx
index 90105cc71402..867b15d880f8 100644
--- a/sc/inc/queryparam.hxx
+++ b/sc/inc/queryparam.hxx
@@ -25,6 +25,8 @@
 
 #include <boost/ptr_container/ptr_vector.hpp>
 
+class SvNumberFormatter;
+
 struct ScDBQueryParamInternal;
 struct ScQueryEntry;
 
@@ -55,7 +57,8 @@ struct ScQueryParamBase
     ScQueryEntry* FindEntryByField(SCCOLROW nField, bool bNew);
     SC_DLLPUBLIC void RemoveEntryByField(SCCOLROW nField);
     void Resize(size_t nNew);
-    void FillInExcelSyntax(svl::SharedStringPool& rPool, const OUString& aCellStr, SCSIZE nIndex);
+    void FillInExcelSyntax( svl::SharedStringPool& rPool, const OUString& aCellStr, SCSIZE nIndex,
+                            SvNumberFormatter* pFormatter );
 
 protected:
     typedef boost::ptr_vector<ScQueryEntry> EntriesType;
diff --git a/sc/source/core/data/dociter.cxx b/sc/source/core/data/dociter.cxx
index 46690d7f4bf1..8cf40b61a421 100644
--- a/sc/source/core/data/dociter.cxx
+++ b/sc/source/core/data/dociter.cxx
@@ -1117,8 +1117,24 @@ bool ScQueryCellIterator::GetThis()
 
         if (maCurPos.first->type == sc::element_type_empty)
         {
-            IncBlock();
-            continue;
+            if (rItem.mbMatchEmpty && rEntry.GetQueryItems().size() == 1)
+            {
+                // This shortcut, instead of determining if any SC_OR query
+                // exists or this query is SC_AND'ed (which wouldn't make
+                // sense, but..) and evaluating them in ValidQuery(), is
+                // possible only because the interpreter is the only caller
+                // that sets mbMatchEmpty and there is only one item in those
+                // cases.
+                // XXX this would have to be reworked if other filters used it
+                // in different manners and evaluation would have to be done in
+                // ValidQuery().
+                return true;
+            }
+            else
+            {
+                IncBlock();
+                continue;
+            }
         }
 
         ScRefCellValue aCell = sc::toRefCell(maCurPos.first, maCurPos.second);
diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index d344218bcf81..931fc6139b0a 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -3088,7 +3088,7 @@ bool ScTable::CreateExcelQuery(SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow
                     if (nIndex < nNewEntries)
                     {
                         rQueryParam.GetEntry(nIndex).nField = pFields[nCol - nCol1];
-                        rQueryParam.FillInExcelSyntax(rPool, aCellStr, nIndex);
+                        rQueryParam.FillInExcelSyntax(rPool, aCellStr, nIndex, NULL);
                         nIndex++;
                         if (nIndex < nNewEntries)
                             rQueryParam.GetEntry(nIndex).eConnect = SC_AND;
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 e7fd64480c15..260ff93650cf 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -4851,11 +4851,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);
             }
@@ -5150,11 +5146,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);
                 }
@@ -5408,11 +5400,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 36e6638c14ce..dd8fa5463f34 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() :