LibreOffice Module sc (master) 1
doubleref.cxx
Go to the documentation of this file.
1/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2/*
3 * This file is part of the LibreOffice project.
4 *
5 * This Source Code Form is subject to the terms of the Mozilla Public
6 * License, v. 2.0. If a copy of the MPL was not distributed with this
7 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8 *
9 * This file incorporates work covered by the following license notice:
10 *
11 * Licensed to the Apache Software Foundation (ASF) under one or more
12 * contributor license agreements. See the NOTICE file distributed
13 * with this work for additional information regarding copyright
14 * ownership. The ASF licenses this file to you under the Apache
15 * License, Version 2.0 (the "License"); you may not use this file
16 * except in compliance with the License. You may obtain a copy of
17 * the License at http://www.apache.org/licenses/LICENSE-2.0 .
18 */
19
20#include <doubleref.hxx>
21#include <global.hxx>
22#include <document.hxx>
23#include <queryparam.hxx>
24#include <queryentry.hxx>
25#include <globstr.hrc>
26#include <scresid.hxx>
27#include <scmatrix.hxx>
28
30#include <osl/diagnose.h>
33
34#include <memory>
35#include <utility>
36#include <vector>
37
38using ::std::unique_ptr;
39using ::std::vector;
40
41namespace {
42
43void lcl_uppercase(OUString& rStr)
44{
45 rStr = ScGlobal::getCharClass().uppercase(rStr.trim());
46}
47
48bool lcl_createStarQuery(
49 const ScDocument* pDoc,
50 svl::SharedStringPool& rPool, ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
51{
52 // A valid StarQuery must be at least 4 columns wide. To be precise it
53 // should be exactly 4 columns ...
54 // Additionally, if this wasn't checked, a formula pointing to a valid 1-3
55 // column Excel style query range immediately left to itself would result
56 // in a circular reference when the field name or operator or value (first
57 // to third query range column) is obtained (#i58354#). Furthermore, if the
58 // range wasn't sufficiently specified data changes wouldn't flag formula
59 // cells for recalculation.
60
61 if (pQueryRef->getColSize() < 4)
62 return false;
63
64 bool bValid;
65 OUString aCellStr;
66 SCSIZE nIndex = 0;
67 SCROW nRow = 0;
68 SCROW nRows = pDBRef->getRowSize();
69 SCSIZE nNewEntries = static_cast<SCSIZE>(nRows);
70 pParam->Resize(nNewEntries);
71
72 do
73 {
74 ScQueryEntry& rEntry = pParam->GetEntry(nIndex);
75
76 bValid = false;
77
78 if (nIndex > 0)
79 {
80 // For all entries after the first one, check the and/or connector in the first column.
81 aCellStr = pQueryRef->getString(0, nRow);
82 lcl_uppercase(aCellStr);
83 if ( aCellStr == ScResId(STR_TABLE_AND) )
84 {
85 rEntry.eConnect = SC_AND;
86 bValid = true;
87 }
88 else if ( aCellStr == ScResId(STR_TABLE_OR) )
89 {
90 rEntry.eConnect = SC_OR;
91 bValid = true;
92 }
93 }
94
95 if ((nIndex < 1) || bValid)
96 {
97 // field name in the 2nd column.
98 aCellStr = pQueryRef->getString(1, nRow);
99 SCCOL nField = pDBRef->findFieldColumn(aCellStr); // TODO: must be case insensitive comparison.
100 if (pDoc->ValidCol(nField))
101 {
102 rEntry.nField = nField;
103 bValid = true;
104 }
105 else
106 bValid = false;
107 }
108
109 if (bValid)
110 {
111 // equality, non-equality operator in the 3rd column.
112 aCellStr = pQueryRef->getString(2, nRow);
113 lcl_uppercase(aCellStr);
114 const sal_Unicode* p = aCellStr.getStr();
115 if (p[0] == '<')
116 {
117 if (p[1] == '>')
118 rEntry.eOp = SC_NOT_EQUAL;
119 else if (p[1] == '=')
120 rEntry.eOp = SC_LESS_EQUAL;
121 else
122 rEntry.eOp = SC_LESS;
123 }
124 else if (p[0] == '>')
125 {
126 if (p[1] == '=')
127 rEntry.eOp = SC_GREATER_EQUAL;
128 else
129 rEntry.eOp = SC_GREATER;
130 }
131 else if (p[0] == '=')
132 rEntry.eOp = SC_EQUAL;
133
134 }
135
136 if (bValid)
137 {
138 // Finally, the right-hand-side value in the 4th column.
139 rEntry.GetQueryItem().maString =
140 rPool.intern(pQueryRef->getString(3, nRow));
141 rEntry.bDoQuery = true;
142 }
143 nIndex++;
144 nRow++;
145 }
146 while (bValid && (nRow < nRows) /* && (nIndex < MAXQUERY) */ );
147 return bValid;
148}
149
150bool lcl_createExcelQuery(
151 const ScDocument* pDoc,
152 svl::SharedStringPool& rPool, ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
153{
154 bool bValid = true;
155 SCCOL nCols = pQueryRef->getColSize();
156 SCROW nRows = pQueryRef->getRowSize();
157 vector<SCCOL> aFields(nCols);
158 SCCOL nCol = 0;
159 while (bValid && (nCol < nCols))
160 {
161 OUString aQueryStr = pQueryRef->getString(nCol, 0);
162 SCCOL nField = pDBRef->findFieldColumn(aQueryStr);
163 if (pDoc->ValidCol(nField))
164 aFields[nCol] = nField;
165 else
166 bValid = false;
167 ++nCol;
168 }
169
170 if (bValid)
171 {
172 // Count the number of visible cells (excluding the header row). Each
173 // visible cell corresponds with a single query.
174 SCSIZE nVisible = pQueryRef->getVisibleDataCellCount();
175 if ( nVisible > SCSIZE_MAX / sizeof(void*) )
176 {
177 OSL_FAIL("too many filter criteria");
178 nVisible = 0;
179 }
180
181 SCSIZE nNewEntries = nVisible;
182 pParam->Resize( nNewEntries );
183
184 SCSIZE nIndex = 0;
185 SCROW nRow = 1;
186 OUString aCellStr;
187 while (nRow < nRows)
188 {
189 nCol = 0;
190 while (nCol < nCols)
191 {
192 aCellStr = pQueryRef->getString(nCol, nRow);
193 aCellStr = ScGlobal::getCharClass().uppercase( aCellStr );
194 if (!aCellStr.isEmpty())
195 {
196 if (nIndex < nNewEntries)
197 {
198 pParam->GetEntry(nIndex).nField = aFields[nCol];
199 pParam->FillInExcelSyntax(rPool, aCellStr, nIndex, nullptr);
200 nIndex++;
201 if (nIndex < nNewEntries)
202 pParam->GetEntry(nIndex).eConnect = SC_AND;
203 }
204 else
205 bValid = false;
206 }
207 nCol++;
208 }
209 nRow++;
210 if (nIndex < nNewEntries)
211 pParam->GetEntry(nIndex).eConnect = SC_OR;
212 }
213 }
214 return bValid;
215}
216
217bool lcl_fillQueryEntries(
218 const ScDocument* pDoc,
219 svl::SharedStringPool& rPool, ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
220{
221 SCSIZE nCount = pParam->GetEntryCount();
222 for (SCSIZE i = 0; i < nCount; ++i)
223 pParam->GetEntry(i).Clear();
224
225 // Standard QueryTabelle
226 bool bValid = lcl_createStarQuery(pDoc, rPool, pParam, pDBRef, pQueryRef);
227 // Excel QueryTabelle
228 if (!bValid)
229 bValid = lcl_createExcelQuery(pDoc, rPool, pParam, pDBRef, pQueryRef);
230
231 nCount = pParam->GetEntryCount();
232 if (bValid)
233 {
234 // bQueryByString must be set
235 for (SCSIZE i = 0; i < nCount; ++i)
237 }
238 else
239 {
240 // nothing
241 for (SCSIZE i = 0; i < nCount; ++i)
242 pParam->GetEntry(i).Clear();
243 }
244 return bValid;
245}
246
247}
248
250 mpDoc(pDoc)
251{
252}
253
255{
256}
257
259{
260 if (!pDBRef)
261 return false;
262
263 return lcl_fillQueryEntries(getDoc(), getDoc()->GetSharedStringPool(), pParam, pDBRef, this);
264}
265
267{
268 pParam->bHasHeader = true;
269 pParam->bByRow = true;
270 pParam->bInplace = true;
271 pParam->bCaseSens = false;
273 pParam->bDuplicate = true;
274}
275
277 ScDBRangeBase(pDoc), maRange(rRange)
278{
279}
280
282{
283}
284
286{
287 return maRange.aEnd.Col() - maRange.aStart.Col() + 1;
288}
289
291{
292 return maRange.aEnd.Row() - maRange.aStart.Row() + 1;
293}
294
296{
297 SCCOL nCols = getColSize();
298 SCROW nRows = getRowSize();
299 if (nRows <= 1)
300 return 0;
301
302 return (nRows-1)*nCols;
303}
304
305OUString ScDBInternalRange::getString(SCCOL nCol, SCROW nRow) const
306{
307 const ScAddress& s = maRange.aStart;
308 // #i109200# this is used in formula calculation, use GetInputString, not GetString
309 // (consistent with ScDBInternalRange::getCellString)
310 // GetStringForFormula is not used here, to allow querying for date values.
311 return getDoc()->GetInputString(s.Col() + nCol, s.Row() + nRow, maRange.aStart.Tab());
312}
313
315{
316 return getRange().aStart.Col();
317}
318
320{
321 const ScRange& rRange = getRange();
322 const ScAddress& s = rRange.aStart;
323
324 SCCOL nDBCol1 = s.Col();
325
326 // Don't handle out-of-bound condition here. We'll do that later.
327 return nIndex + nDBCol1 - 1;
328}
329
330SCCOL ScDBInternalRange::findFieldColumn(const OUString& rStr, FormulaError* pErr) const
331{
332 const ScAddress& s = maRange.aStart;
333 const ScAddress& e = maRange.aEnd;
334 OUString aUpper = rStr;
335 lcl_uppercase(aUpper);
336
337 SCCOL nDBCol1 = s.Col();
338 SCROW nDBRow1 = s.Row();
339 SCTAB nDBTab1 = s.Tab();
340 SCCOL nDBCol2 = e.Col();
341
342 bool bFound = false;
343
344 OUString aCellStr;
345 ScAddress aLook( nDBCol1, nDBRow1, nDBTab1 );
346 while (!bFound && (aLook.Col() <= nDBCol2))
347 {
348 FormulaError nErr = getDoc()->GetStringForFormula( aLook, aCellStr );
349 if (pErr)
350 *pErr = nErr;
351 lcl_uppercase(aCellStr);
352 bFound = ScGlobal::GetTransliteration().isEqual(aCellStr, aUpper);
353 if (!bFound)
354 aLook.IncCol();
355 }
356 SCCOL nField = aLook.Col();
357
358 return bFound ? nField : -1;
359}
360
361std::unique_ptr<ScDBQueryParamBase> ScDBInternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
362{
363 unique_ptr<ScDBQueryParamInternal> pParam(new ScDBQueryParamInternal);
364
365 // Set the database range first.
366 const ScAddress& s = maRange.aStart;
367 const ScAddress& e = maRange.aEnd;
368 pParam->nCol1 = s.Col();
369 pParam->nRow1 = s.Row();
370 pParam->nCol2 = e.Col();
371 pParam->nRow2 = e.Row();
372 pParam->nTab = s.Tab();
373
374 fillQueryOptions(pParam.get());
375
376 // Now construct the query entries from the query range.
377 if (!pQueryRef->fillQueryEntries(pParam.get(), this))
378 return nullptr;
379
380 return std::unique_ptr<ScDBQueryParamBase>(std::move(pParam));
381}
382
384{
385 return maRange == rRange;
386}
387
389 ScDBRangeBase(pDoc), mpMatrix(std::move(pMat))
390{
391 SCSIZE nC, nR;
392 mpMatrix->GetDimensions(nC, nR);
393 mnCols = static_cast<SCCOL>(nC);
394 mnRows = static_cast<SCROW>(nR);
395}
396
398{
399}
400
402{
403 return mnCols;
404}
405
407{
408 return mnRows;
409}
410
412{
413 SCCOL nCols = getColSize();
414 SCROW nRows = getRowSize();
415 if (nRows <= 1)
416 return 0;
417
418 return (nRows-1)*nCols;
419}
420
421OUString ScDBExternalRange::getString(SCCOL nCol, SCROW nRow) const
422{
423 if (nCol >= mnCols || nRow >= mnRows)
424 return OUString();
425
426 return mpMatrix->GetString(nCol, nRow).getString();
427}
428
430{
431 return 0;
432}
433
435{
436 return nIndex - 1;
437}
438
439SCCOL ScDBExternalRange::findFieldColumn(const OUString& rStr, FormulaError* pErr) const
440{
441 if (pErr)
442 *pErr = FormulaError::NONE;
443
444 OUString aUpper = rStr;
445 lcl_uppercase(aUpper);
446 for (SCCOL i = 0; i < mnCols; ++i)
447 {
448 OUString aUpperVal = mpMatrix->GetString(i, 0).getString();
449 lcl_uppercase(aUpperVal);
450 if (aUpper == aUpperVal)
451 return i;
452 }
453 return -1;
454}
455
456std::unique_ptr<ScDBQueryParamBase> ScDBExternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
457{
458 unique_ptr<ScDBQueryParamMatrix> pParam(new ScDBQueryParamMatrix);
459 pParam->mpMatrix = mpMatrix;
460 fillQueryOptions(pParam.get());
461
462 // Now construct the query entries from the query range.
463 if (!pQueryRef->fillQueryEntries(pParam.get(), this))
464 return nullptr;
465
466 return std::unique_ptr<ScDBQueryParamBase>(std::move(pParam));
467}
468
469bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const
470{
471 return false;
472}
473
474/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
const SCSIZE SCSIZE_MAX
Definition: address.hxx:59
size_t SCSIZE
size_t typedef to be able to find places where code was changed from USHORT to size_t and is used to ...
Definition: address.hxx:44
B2DRange maRange
OUString uppercase(const OUString &rStr, sal_Int32 nPos, sal_Int32 nCount) const
SCTAB Tab() const
Definition: address.hxx:283
void IncCol(SCCOL nDelta=1)
Definition: address.hxx:316
SCROW Row() const
Definition: address.hxx:274
SCCOL Col() const
Definition: address.hxx:279
virtual SCCOL findFieldColumn(SCCOL nIndex) const override
Get a 0-based column index that corresponds with the passed field index.
Definition: doubleref.cxx:434
virtual SCSIZE getVisibleDataCellCount() const override
Definition: doubleref.cxx:411
virtual SCCOL getColSize() const override
Definition: doubleref.cxx:401
virtual std::unique_ptr< ScDBQueryParamBase > createQueryParam(const ScDBRangeBase *pQueryRef) const override
Definition: doubleref.cxx:456
const ScMatrixRef mpMatrix
Definition: doubleref.hxx:173
ScDBExternalRange(ScDocument *pDoc, ScMatrixRef pMat)
Definition: doubleref.cxx:388
virtual bool isRangeEqual(const ScRange &rRange) const override
Definition: doubleref.cxx:469
virtual OUString getString(SCCOL nCol, SCROW nRow) const override
Get a string value of a specified cell position.
Definition: doubleref.cxx:421
virtual SCCOL getFirstFieldColumn() const override
Definition: doubleref.cxx:429
virtual SCROW getRowSize() const override
Definition: doubleref.cxx:406
virtual ~ScDBExternalRange() override
Definition: doubleref.cxx:397
const ScRange & getRange() const
Definition: doubleref.hxx:97
virtual SCCOL findFieldColumn(SCCOL nIndex) const override
Get a 0-based column index that corresponds with the passed field index.
Definition: doubleref.cxx:319
virtual SCROW getRowSize() const override
Definition: doubleref.cxx:290
virtual SCSIZE getVisibleDataCellCount() const override
Definition: doubleref.cxx:295
virtual SCCOL getFirstFieldColumn() const override
Definition: doubleref.cxx:314
virtual SCCOL getColSize() const override
Definition: doubleref.cxx:285
ScDBInternalRange(ScDocument *pDoc, const ScRange &rRange)
Definition: doubleref.cxx:276
virtual OUString getString(SCCOL nCol, SCROW nRow) const override
Get a string value of a specified cell position.
Definition: doubleref.cxx:305
virtual ~ScDBInternalRange() override
Definition: doubleref.cxx:281
virtual bool isRangeEqual(const ScRange &rRange) const override
Definition: doubleref.cxx:383
virtual std::unique_ptr< ScDBQueryParamBase > createQueryParam(const ScDBRangeBase *pQueryRef) const override
Definition: doubleref.cxx:361
Base class for abstracting range data backends for database functions.
Definition: doubleref.hxx:38
virtual OUString getString(SCCOL nCol, SCROW nRow) const =0
Get a string value of a specified cell position.
static void fillQueryOptions(ScQueryParamBase *pParam)
Populate query options that are always the same for all database queries.
Definition: doubleref.cxx:266
bool fillQueryEntries(ScQueryParamBase *pParam, const ScDBRangeBase *pDBRef) const
Definition: doubleref.cxx:258
virtual ~ScDBRangeBase()=0
Definition: doubleref.cxx:254
virtual SCSIZE getVisibleDataCellCount() const =0
virtual SCCOL findFieldColumn(SCCOL nIndex) const =0
Get a 0-based column index that corresponds with the passed field index.
ScDocument * getDoc() const
Definition: doubleref.hxx:79
ScDBRangeBase()=delete
virtual SCROW getRowSize() const =0
virtual SCCOL getColSize() const =0
SC_DLLPUBLIC OUString GetInputString(SCCOL nCol, SCROW nRow, SCTAB nTab, bool bForceSystemLocale=false) const
Definition: document.cxx:3592
bool ValidCol(SCCOL nCol) const
Definition: document.hxx:898
FormulaError GetStringForFormula(const ScAddress &rPos, OUString &rString)
Definition: document.cxx:3600
static SC_DLLPUBLIC ::utl::TransliterationWrapper & GetTransliteration()
Definition: global.cxx:1024
static SC_DLLPUBLIC const CharClass & getCharClass()
Definition: global.cxx:1062
ScAddress aEnd
Definition: address.hxx:498
ScAddress aStart
Definition: address.hxx:497
SharedString intern(const OUString &rStr)
bool isEqual(const OUString &rStr1, const OUString &rStr2) const
int nCount
FormulaError
@ SC_OR
Definition: global.hxx:855
@ SC_AND
Definition: global.hxx:854
@ SC_LESS_EQUAL
Definition: global.hxx:837
@ SC_LESS
Definition: global.hxx:835
@ SC_GREATER_EQUAL
Definition: global.hxx:838
@ SC_GREATER
Definition: global.hxx:836
@ SC_EQUAL
Definition: global.hxx:834
@ SC_NOT_EQUAL
Definition: global.hxx:839
sal_Int32 nIndex
void * p
int i
OUString ScResId(TranslateId aId)
Definition: scdll.cxx:90
svl::SharedString maString
Definition: queryentry.hxx:49
Each instance of this struct represents a single filtering criteria.
Definition: queryentry.hxx:34
SCCOLROW nField
Definition: queryentry.hxx:61
const Item & GetQueryItem() const
Definition: queryentry.hxx:85
ScQueryConnect eConnect
Definition: queryentry.hxx:63
ScQueryOp eOp
Definition: queryentry.hxx:62
void FillInExcelSyntax(svl::SharedStringPool &rPool, const OUString &aCellStr, SCSIZE nIndex, SvNumberFormatter *pFormatter)
Definition: queryparam.cxx:204
SC_DLLPUBLIC const ScQueryEntry & GetEntry(SCSIZE n) const
Definition: queryparam.cxx:116
utl::SearchParam::SearchType eSearchType
Definition: queryparam.hxx:43
void Resize(size_t nNew)
Definition: queryparam.cxx:196
SC_DLLPUBLIC SCSIZE GetEntryCount() const
Definition: queryparam.cxx:111
sal_uInt16 sal_Unicode
sal_Int16 SCTAB
Definition: types.hxx:22
sal_Int16 SCCOL
Definition: types.hxx:21
::boost::intrusive_ptr< ScMatrix > ScMatrixRef
Definition: types.hxx:25
sal_Int32 SCROW
Definition: types.hxx:17