summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorEike Rathke <erack@redhat.com>2015-03-31 14:01:42 +0200
committerCaolán McNamara <caolanm@redhat.com>2015-04-03 15:35:14 +0000
commitc03db327a2904f5b2cef52be03d70b04cb473a52 (patch)
treef666eb1b0a951ae0367d35df8f15c048de43e9f8
parent09aa700793f3f1b3344558ea8a91b8f4134099de (diff)
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>
-rw-r--r--sc/inc/queryentry.hxx3
-rw-r--r--sc/inc/queryparam.hxx5
-rw-r--r--sc/source/core/data/dociter.cxx20
-rw-r--r--sc/source/core/data/table3.cxx2
-rw-r--r--sc/source/core/tool/doubleref.cxx2
-rw-r--r--sc/source/core/tool/interpr1.cxx18
-rw-r--r--sc/source/core/tool/queryentry.cxx2
-rw-r--r--sc/source/core/tool/queryparam.cxx17
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 92406345caa2..208bd33241b2 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 4ec4b420afaf..f02a4b6c6f02 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 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() :