summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--sc/inc/queryparam.hxx1
-rw-r--r--sc/source/core/data/dociter.cxx62
-rw-r--r--sc/source/core/data/table3.cxx42
-rw-r--r--sc/source/core/tool/queryparam.cxx5
4 files changed, 107 insertions, 3 deletions
diff --git a/sc/inc/queryparam.hxx b/sc/inc/queryparam.hxx
index ff89d3e62aac..a0e94f8db96f 100644
--- a/sc/inc/queryparam.hxx
+++ b/sc/inc/queryparam.hxx
@@ -42,6 +42,7 @@ struct ScQueryParamBase
bool bCaseSens;
bool bRegExp;
bool bDuplicate;
+ bool mbRangeLookup; ///< for spreadsheet functions like MATCH, LOOKUP, HLOOKUP, VLOOKUP
virtual ~ScQueryParamBase();
diff --git a/sc/source/core/data/dociter.cxx b/sc/source/core/data/dociter.cxx
index 3277d3692433..565565f00a11 100644
--- a/sc/source/core/data/dociter.cxx
+++ b/sc/source/core/data/dociter.cxx
@@ -1245,6 +1245,17 @@ bool ScQueryCellIterator::FindEqualOrSortedLastInRange( SCCOL& nFoundCol,
SCROW& nFoundRow, bool bSearchForEqualAfterMismatch,
bool bIgnoreMismatchOnLeadingStringsP )
{
+ // Set and automatically reset mpParam->mbRangeLookup when returning. We
+ // could use comphelper::FlagRestorationGuard, but really, that one is
+ // overengineered for this simple purpose here.
+ struct BoolResetter
+ {
+ bool& mr;
+ bool mb;
+ BoolResetter( bool& r, bool b ) : mr(r), mb(r) { r = b; }
+ ~BoolResetter() { mr = mb; }
+ } aRangeLookupResetter( mpParam->mbRangeLookup, true);
+
nFoundCol = MAXCOL+1;
nFoundRow = MAXROW+1;
SetStopOnMismatch( true ); // assume sorted keys
@@ -1253,7 +1264,22 @@ bool ScQueryCellIterator::FindEqualOrSortedLastInRange( SCCOL& nFoundCol,
bool bRegExp = mpParam->bRegExp && mpParam->GetEntry(0).GetQueryItem().meType == ScQueryEntry::ByString;
bool bBinary = !bRegExp && mpParam->bByRow && (mpParam->GetEntry(0).eOp ==
SC_LESS_EQUAL || mpParam->GetEntry(0).eOp == SC_GREATER_EQUAL);
- if (bBinary ? (BinarySearch() ? GetThis() : 0) : GetFirst())
+ bool bFound = false;
+ if (bBinary)
+ {
+ if (BinarySearch())
+ {
+ // BinarySearch() already positions correctly and only needs real
+ // query comparisons afterwards, skip the verification check below.
+ mpParam->mbRangeLookup = false;
+ bFound = GetThis();
+ }
+ }
+ else
+ {
+ bFound = GetFirst();
+ }
+ if (bFound)
{
// First equal entry or last smaller than (greater than) entry.
PositionType aPosSave;
@@ -1272,9 +1298,43 @@ bool ScQueryCellIterator::FindEqualOrSortedLastInRange( SCCOL& nFoundCol,
{
// Step back to last in range and adjust position markers for
// GetNumberFormat() or similar.
+ SCCOL nColDiff = nCol - nFoundCol;
nCol = nFoundCol;
nRow = nFoundRow;
maCurPos = aPosSave;
+ if (mpParam->mbRangeLookup)
+ {
+ // Verify that the found entry does not only fulfill the range
+ // lookup but also the real query, i.e. not numeric was found
+ // if query is ByString and vice versa.
+ mpParam->mbRangeLookup = false;
+ // Step back the last field advance if GetNext() did one.
+ if (bAdvanceQuery && nColDiff)
+ {
+ SCSIZE nEntries = mpParam->GetEntryCount();
+ for (SCSIZE j=0; j < nEntries; ++j)
+ {
+ ScQueryEntry& rEntry = mpParam->GetEntry( j );
+ if (rEntry.bDoQuery)
+ {
+ if (rEntry.nField - nColDiff >= 0)
+ rEntry.nField -= nColDiff;
+ else
+ {
+ assert(!"FindEqualOrSortedLastInRange: rEntry.nField -= nColDiff < 0");
+ }
+ }
+ else
+ break; // for
+ }
+ }
+ // Check it.
+ if (!GetThis())
+ {
+ nFoundCol = MAXCOL+1;
+ nFoundRow = MAXROW+1;
+ }
+ }
}
}
if ( IsEqualConditionFulfilled() )
diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index 05c43c3da09f..a92748568d2f 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -1513,6 +1513,41 @@ public:
return std::pair<bool,bool>(bOk, bTestEqual);
}
+
+ // To be called only if both isQueryByValue() and isQueryByString()
+ // returned false and range lookup is wanted! In range lookup comparison
+ // numbers are less than strings. Nothing else is compared.
+ std::pair<bool,bool> compareByRangeLookup(
+ const ScRefCellValue& rCell, SCCOL nCol, SCROW nRow,
+ const ScQueryEntry& rEntry, const ScQueryEntry::Item& rItem)
+ {
+ bool bTestEqual = false;
+
+ if (rItem.meType == ScQueryEntry::ByString && rEntry.eOp != SC_LESS && rEntry.eOp != SC_LESS_EQUAL)
+ return std::pair<bool,bool>(false, bTestEqual);
+
+ if (rItem.meType != ScQueryEntry::ByString && rEntry.eOp != SC_GREATER && rEntry.eOp != SC_GREATER_EQUAL)
+ return std::pair<bool,bool>(false, bTestEqual);
+
+ if (!rCell.isEmpty())
+ {
+ if (rItem.meType == ScQueryEntry::ByString)
+ {
+ if (rCell.meType == CELLTYPE_FORMULA && rCell.mpFormula->GetErrCode())
+ // Error values are compared as string.
+ return std::pair<bool,bool>(false, bTestEqual);
+
+ return std::pair<bool,bool>(rCell.hasNumeric(), bTestEqual);
+ }
+
+ return std::pair<bool,bool>(!rCell.hasNumeric(), bTestEqual);
+ }
+
+ if (rItem.meType == ScQueryEntry::ByString)
+ return std::pair<bool,bool>(mrTab.HasValueData(nCol, nRow), bTestEqual);
+
+ return std::pair<bool,bool>(!mrTab.HasValueData(nCol, nRow), bTestEqual);
+ }
};
}
@@ -1578,6 +1613,13 @@ bool ScTable::ValidQuery(
aRes.first |= aThisRes.first;
aRes.second |= aThisRes.second;
}
+ else if (rParam.mbRangeLookup)
+ {
+ std::pair<bool,bool> aThisRes =
+ aEval.compareByRangeLookup(aCell, nCol, nRow, rEntry, *itr);
+ aRes.first |= aThisRes.first;
+ aRes.second |= aThisRes.second;
+ }
if (aRes.first && aRes.second)
break;
diff --git a/sc/source/core/tool/queryparam.cxx b/sc/source/core/tool/queryparam.cxx
index 071c3457c383..e7f73ca2a83f 100644
--- a/sc/source/core/tool/queryparam.cxx
+++ b/sc/source/core/tool/queryparam.cxx
@@ -64,7 +64,8 @@ ScQueryParamBase::ScQueryParamBase() :
bInplace(true),
bCaseSens(false),
bRegExp(false),
- bDuplicate(false)
+ bDuplicate(false),
+ mbRangeLookup(false)
{
for (size_t i = 0; i < MAXQUERY; ++i)
maEntries.push_back(new ScQueryEntry);
@@ -72,7 +73,7 @@ ScQueryParamBase::ScQueryParamBase() :
ScQueryParamBase::ScQueryParamBase(const ScQueryParamBase& r) :
bHasHeader(r.bHasHeader), bByRow(r.bByRow), bInplace(r.bInplace), bCaseSens(r.bCaseSens),
- bRegExp(r.bRegExp), bDuplicate(r.bDuplicate),
+ bRegExp(r.bRegExp), bDuplicate(r.bDuplicate), mbRangeLookup(r.mbRangeLookup),
maEntries(r.maEntries)
{
}