xref: /trunk/main/sc/source/core/tool/doubleref.cxx (revision b3f79822)
1 /**************************************************************
2  *
3  * Licensed to the Apache Software Foundation (ASF) under one
4  * or more contributor license agreements.  See the NOTICE file
5  * distributed with this work for additional information
6  * regarding copyright ownership.  The ASF licenses this file
7  * to you under the Apache License, Version 2.0 (the
8  * "License"); you may not use this file except in compliance
9  * with the License.  You may obtain a copy of the License at
10  *
11  *   http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing,
14  * software distributed under the License is distributed on an
15  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
16  * KIND, either express or implied.  See the License for the
17  * specific language governing permissions and limitations
18  * under the License.
19  *
20  *************************************************************/
21 
22 
23 
24 // MARKER(update_precomp.py): autogen include statement, do not remove
25 #include "precompiled_sc.hxx"
26 
27 // INCLUDE ---------------------------------------------------------------
28 
29 #include "doubleref.hxx"
30 #include "cell.hxx"
31 #include "global.hxx"
32 #include "document.hxx"
33 #include "queryparam.hxx"
34 #include "globstr.hrc"
35 
36 #include <memory>
37 #include <vector>
38 
39 using ::rtl::OUString;
40 using ::std::auto_ptr;
41 using ::std::vector;
42 
43 namespace {
44 
lcl_toUpper(OUString & rStr)45 void lcl_toUpper(OUString& rStr)
46 {
47     rStr = ScGlobal::pCharClass->toUpper(rStr.trim(), 0, static_cast<xub_StrLen>(rStr.getLength()));
48 }
49 
lcl_createStarQuery(ScQueryParamBase * pParam,const ScDBRangeBase * pDBRef,const ScDBRangeBase * pQueryRef)50 bool lcl_createStarQuery(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     sal_Bool bValid;
65     sal_Bool bFound;
66     OUString aCellStr;
67     SCSIZE nIndex = 0;
68     SCROW nRow = 0;
69     SCROW nRows = pDBRef->getRowSize();
70     SCSIZE nNewEntries = static_cast<SCSIZE>(nRows);
71     pParam->Resize(nNewEntries);
72 
73     do
74     {
75         ScQueryEntry& rEntry = pParam->GetEntry(nIndex);
76 
77         bValid = sal_False;
78 
79         if (nIndex > 0)
80         {
81             // For all entries after the first one, check the and/or connector in the first column.
82             aCellStr = pQueryRef->getString(0, nRow);
83             lcl_toUpper(aCellStr);
84             if ( aCellStr.equals(ScGlobal::GetRscString(STR_TABLE_UND)) )
85             {
86                 rEntry.eConnect = SC_AND;
87                 bValid = sal_True;
88             }
89             else if ( aCellStr.equals(ScGlobal::GetRscString(STR_TABLE_ODER)) )
90             {
91                 rEntry.eConnect = SC_OR;
92                 bValid = sal_True;
93             }
94         }
95 
96         if ((nIndex < 1) || bValid)
97         {
98             // field name in the 2nd column.
99             bFound = sal_False;
100             aCellStr = pQueryRef->getString(1, nRow);
101             SCCOL nField = pDBRef->findFieldColumn(aCellStr); // TODO: must be case insensitive comparison.
102             if (ValidCol(nField))
103             {
104                 rEntry.nField = nField;
105                 bValid = true;
106             }
107             else
108                 bValid = false;
109         }
110 
111         if (bValid)
112         {
113             // equality, non-equality operator in the 3rd column.
114             bFound = sal_False;
115             aCellStr = pQueryRef->getString(2, nRow);
116             lcl_toUpper(aCellStr);
117             const sal_Unicode* p = aCellStr.getStr();
118             if (p[0] == sal_Unicode('<'))
119             {
120                 if (p[1] == sal_Unicode('>'))
121                     rEntry.eOp = SC_NOT_EQUAL;
122                 else if (p[1] == sal_Unicode('='))
123                     rEntry.eOp = SC_LESS_EQUAL;
124                 else
125                     rEntry.eOp = SC_LESS;
126             }
127             else if (p[0] == sal_Unicode('>'))
128             {
129                 if (p[1] == sal_Unicode('='))
130                     rEntry.eOp = SC_GREATER_EQUAL;
131                 else
132                     rEntry.eOp = SC_GREATER;
133             }
134             else if (p[0] == sal_Unicode('='))
135                 rEntry.eOp = SC_EQUAL;
136 
137         }
138 
139         if (bValid)
140         {
141             // Finally, the right-hand-side value in the 4th column.
142             *rEntry.pStr = pQueryRef->getString(3, nRow);
143             rEntry.bDoQuery = sal_True;
144         }
145         nIndex++;
146         nRow++;
147     }
148     while (bValid && (nRow < nRows) /* && (nIndex < MAXQUERY) */ );
149     return bValid;
150 }
151 
lcl_createExcelQuery(ScQueryParamBase * pParam,const ScDBRangeBase * pDBRef,const ScDBRangeBase * pQueryRef)152 bool lcl_createExcelQuery(
153     ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
154 {
155     bool bValid = true;
156     SCCOL nCols = pQueryRef->getColSize();
157     SCROW nRows = pQueryRef->getRowSize();
158     vector<SCCOL> aFields(nCols);
159     SCCOL nCol = 0;
160     while (bValid && (nCol < nCols))
161     {
162         OUString aQueryStr = pQueryRef->getString(nCol, 0);
163         SCCOL nField = pDBRef->findFieldColumn(aQueryStr);
164         if (ValidCol(nField))
165             aFields[nCol] = nField;
166         else
167             bValid = false;
168         ++nCol;
169     }
170 
171     if (bValid)
172     {
173 //      sal_uLong nVisible = 0;
174 //      for ( nCol=nCol1; nCol<=nCol2; nCol++ )
175 //          nVisible += aCol[nCol].VisibleCount( nRow1+1, nRow2 );
176 
177         // Count the number of visible cells (excluding the header row).  Each
178         // visible cell corresponds with a single query.
179         SCSIZE nVisible = pQueryRef->getVisibleDataCellCount();
180         if ( nVisible > SCSIZE_MAX / sizeof(void*) )
181         {
182             DBG_ERROR("zu viele Filterkritierien");
183             nVisible = 0;
184         }
185 
186         SCSIZE nNewEntries = nVisible;
187         pParam->Resize( nNewEntries );
188 
189         SCSIZE nIndex = 0;
190         SCROW nRow = 1;
191         String aCellStr;
192         while (nRow < nRows)
193         {
194             nCol = 0;
195             while (nCol < nCols)
196             {
197                 aCellStr = pQueryRef->getString(nCol, nRow);
198                 ScGlobal::pCharClass->toUpper( aCellStr );
199                 if (aCellStr.Len() > 0)
200                 {
201                     if (nIndex < nNewEntries)
202                     {
203                         pParam->GetEntry(nIndex).nField = aFields[nCol];
204                         pParam->FillInExcelSyntax(aCellStr, nIndex);
205                         nIndex++;
206                         if (nIndex < nNewEntries)
207                             pParam->GetEntry(nIndex).eConnect = SC_AND;
208                     }
209                     else
210                         bValid = sal_False;
211                 }
212                 nCol++;
213             }
214             nRow++;
215             if (nIndex < nNewEntries)
216                 pParam->GetEntry(nIndex).eConnect = SC_OR;
217         }
218     }
219     return bValid;
220 }
221 
lcl_fillQueryEntries(ScQueryParamBase * pParam,const ScDBRangeBase * pDBRef,const ScDBRangeBase * pQueryRef)222 bool lcl_fillQueryEntries(
223     ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
224 {
225     SCSIZE nCount = pParam->GetEntryCount();
226     for (SCSIZE i = 0; i < nCount; ++i)
227         pParam->GetEntry(i).Clear();
228 
229     // Standard QueryTabelle
230     bool bValid = lcl_createStarQuery(pParam, pDBRef, pQueryRef);
231     // Excel QueryTabelle
232     if (!bValid)
233         bValid = lcl_createExcelQuery(pParam, pDBRef, pQueryRef);
234 
235     nCount = pParam->GetEntryCount();
236     if (bValid)
237     {
238         //  bQueryByString muss gesetzt sein
239         for (SCSIZE i = 0; i < nCount; ++i)
240             pParam->GetEntry(i).bQueryByString = true;
241     }
242     else
243     {
244         //  nix
245         for (SCSIZE i = 0; i < nCount; ++i)
246             pParam->GetEntry(i).Clear();
247     }
248     return bValid;
249 }
250 
251 }
252 
253 // ============================================================================
254 
ScDBRangeBase(ScDocument * pDoc,RefType eType)255 ScDBRangeBase::ScDBRangeBase(ScDocument* pDoc, RefType eType) :
256     mpDoc(pDoc), meType(eType)
257 {
258 }
259 
~ScDBRangeBase()260 ScDBRangeBase::~ScDBRangeBase()
261 {
262 }
263 
fillQueryEntries(ScQueryParamBase * pParam,const ScDBRangeBase * pDBRef) const264 bool ScDBRangeBase::fillQueryEntries(ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef) const
265 {
266     if (!pDBRef)
267         return false;
268 
269     return lcl_fillQueryEntries(pParam, pDBRef, this);
270 }
271 
fillQueryOptions(ScQueryParamBase * pParam)272 void ScDBRangeBase::fillQueryOptions(ScQueryParamBase* pParam)
273 {
274     pParam->bHasHeader = true;
275     pParam->bByRow = true;
276     pParam->bInplace = true;
277     pParam->bCaseSens = false;
278     pParam->bRegExp = false;
279     pParam->bDuplicate = true;
280     pParam->bMixedComparison = false;
281 }
282 
getDoc() const283 ScDocument* ScDBRangeBase::getDoc() const
284 {
285     return mpDoc;
286 }
287 
288 // ============================================================================
289 
ScDBInternalRange(ScDocument * pDoc,const ScRange & rRange)290 ScDBInternalRange::ScDBInternalRange(ScDocument* pDoc, const ScRange& rRange) :
291     ScDBRangeBase(pDoc, INTERNAL), maRange(rRange)
292 {
293 }
294 
~ScDBInternalRange()295 ScDBInternalRange::~ScDBInternalRange()
296 {
297 }
298 
getRange() const299 const ScRange& ScDBInternalRange::getRange() const
300 {
301     return maRange;
302 }
303 
getColSize() const304 SCCOL ScDBInternalRange::getColSize() const
305 {
306     return maRange.aEnd.Col() - maRange.aStart.Col() + 1;
307 }
308 
getRowSize() const309 SCROW ScDBInternalRange::getRowSize() const
310 {
311     return maRange.aEnd.Row() - maRange.aStart.Row() + 1;
312 }
313 
getVisibleDataCellCount() const314 SCSIZE ScDBInternalRange::getVisibleDataCellCount() const
315 {
316     SCCOL nCols = getColSize();
317     SCROW nRows = getRowSize();
318     if (nRows <= 1)
319         return 0;
320 
321     return (nRows-1)*nCols;
322 }
323 
getString(SCCOL nCol,SCROW nRow) const324 OUString ScDBInternalRange::getString(SCCOL nCol, SCROW nRow) const
325 {
326     String aStr;
327     const ScAddress& s = maRange.aStart;
328     // #i109200# this is used in formula calculation, use GetInputString, not GetString
329     // (consistent with ScDBInternalRange::getCellString)
330     // GetStringForFormula is not used here, to allow querying for date values.
331     getDoc()->GetInputString(s.Col() + nCol, s.Row() + nRow, maRange.aStart.Tab(), aStr);
332     return aStr;
333 }
334 
getFirstFieldColumn() const335 SCCOL ScDBInternalRange::getFirstFieldColumn() const
336 {
337     return getRange().aStart.Col();
338 }
339 
findFieldColumn(SCCOL nIndex) const340 SCCOL ScDBInternalRange::findFieldColumn(SCCOL nIndex) const
341 {
342     const ScRange& rRange = getRange();
343     const ScAddress& s = rRange.aStart;
344     const ScAddress& e = rRange.aEnd;
345 
346     SCCOL nDBCol1 = s.Col();
347     SCCOL nDBCol2 = e.Col();
348 
349     if ( nIndex <= 0 || nIndex > (nDBCol2 - nDBCol1 + 1) )
350         return nDBCol1;
351 
352     return Min(nDBCol2, static_cast<SCCOL>(nDBCol1 + nIndex - 1));
353 }
354 
findFieldColumn(const OUString & rStr,sal_uInt16 * pErr) const355 SCCOL ScDBInternalRange::findFieldColumn(const OUString& rStr, sal_uInt16* pErr) const
356 {
357     const ScAddress& s = maRange.aStart;
358     const ScAddress& e = maRange.aEnd;
359     OUString aUpper = rStr;
360     lcl_toUpper(aUpper);
361 
362     SCCOL nDBCol1 = s.Col();
363     SCROW nDBRow1 = s.Row();
364     SCTAB nDBTab1 = s.Tab();
365     SCCOL nDBCol2 = e.Col();
366 
367     SCCOL   nField = nDBCol1;
368     sal_Bool    bFound = sal_True;
369 
370     bFound = sal_False;
371     OUString aCellStr;
372     ScAddress aLook( nDBCol1, nDBRow1, nDBTab1 );
373     while (!bFound && (aLook.Col() <= nDBCol2))
374     {
375         sal_uInt16 nErr = getDoc()->GetStringForFormula( aLook, aCellStr );
376         if (pErr)
377             *pErr = nErr;
378         lcl_toUpper(aCellStr);
379         bFound = ScGlobal::GetpTransliteration()->isEqual(aCellStr, aUpper);
380         if (!bFound)
381             aLook.IncCol();
382     }
383     nField = aLook.Col();
384 
385     return bFound ? nField : -1;
386 }
387 
createQueryParam(const ScDBRangeBase * pQueryRef) const388 ScDBQueryParamBase* ScDBInternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
389 {
390     auto_ptr<ScDBQueryParamInternal> pParam(new ScDBQueryParamInternal);
391 
392     // Set the database range first.
393     const ScAddress& s = maRange.aStart;
394     const ScAddress& e = maRange.aEnd;
395     pParam->nCol1 = s.Col();
396     pParam->nRow1 = s.Row();
397     pParam->nCol2 = e.Col();
398     pParam->nRow2 = e.Row();
399     pParam->nTab  = s.Tab();
400 
401     fillQueryOptions(pParam.get());
402 
403     // Now construct the query entries from the query range.
404     if (!pQueryRef->fillQueryEntries(pParam.get(), this))
405         return NULL;
406 
407     return pParam.release();
408 }
409 
isRangeEqual(const ScRange & rRange) const410 bool ScDBInternalRange::isRangeEqual(const ScRange& rRange) const
411 {
412     return maRange == rRange;
413 }
414 
415 // ============================================================================
416 
ScDBExternalRange(ScDocument * pDoc,const ScMatrixRef & pMat)417 ScDBExternalRange::ScDBExternalRange(ScDocument* pDoc, const ScMatrixRef& pMat) :
418     ScDBRangeBase(pDoc, EXTERNAL), mpMatrix(pMat)
419 {
420     SCSIZE nC, nR;
421     mpMatrix->GetDimensions(nC, nR);
422     mnCols = static_cast<SCCOL>(nC);
423     mnRows = static_cast<SCROW>(nR);
424 }
425 
~ScDBExternalRange()426 ScDBExternalRange::~ScDBExternalRange()
427 {
428 }
429 
getColSize() const430 SCCOL ScDBExternalRange::getColSize() const
431 {
432     return mnCols;
433 }
434 
getRowSize() const435 SCROW ScDBExternalRange::getRowSize() const
436 {
437     return mnRows;
438 }
439 
getVisibleDataCellCount() const440 SCSIZE ScDBExternalRange::getVisibleDataCellCount() const
441 {
442     SCCOL nCols = getColSize();
443     SCROW nRows = getRowSize();
444     if (nRows <= 1)
445         return 0;
446 
447     return (nRows-1)*nCols;
448 }
449 
getString(SCCOL nCol,SCROW nRow) const450 OUString ScDBExternalRange::getString(SCCOL nCol, SCROW nRow) const
451 {
452     if (nCol >= mnCols || nRow >= mnRows)
453         return OUString();
454 
455     return mpMatrix->GetString(nCol, nRow);
456 }
457 
getFirstFieldColumn() const458 SCCOL ScDBExternalRange::getFirstFieldColumn() const
459 {
460     return 0;
461 }
462 
findFieldColumn(SCCOL nIndex) const463 SCCOL ScDBExternalRange::findFieldColumn(SCCOL nIndex) const
464 {
465     if (nIndex < 1)
466         // 1st field
467         return 0;
468 
469     if (nIndex > mnCols)
470         // last field
471         return mnCols - 1;
472 
473     return nIndex - 1;
474 }
475 
findFieldColumn(const OUString & rStr,sal_uInt16 * pErr) const476 SCCOL ScDBExternalRange::findFieldColumn(const OUString& rStr, sal_uInt16* pErr) const
477 {
478     if (pErr)
479         pErr = 0;
480 
481     OUString aUpper = rStr;
482     lcl_toUpper(aUpper);
483     for (SCCOL i = 0; i < mnCols; ++i)
484     {
485         OUString aUpperVal = mpMatrix->GetString(i, 0);
486         lcl_toUpper(aUpperVal);
487         if (aUpper.equals(aUpperVal))
488             return i;
489     }
490     return -1;
491 }
492 
createQueryParam(const ScDBRangeBase * pQueryRef) const493 ScDBQueryParamBase* ScDBExternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
494 {
495     auto_ptr<ScDBQueryParamMatrix> pParam(new ScDBQueryParamMatrix);
496     pParam->mpMatrix = mpMatrix;
497     fillQueryOptions(pParam.get());
498 
499     // Now construct the query entries from the query range.
500     if (!pQueryRef->fillQueryEntries(pParam.get(), this))
501         return NULL;
502 
503     return pParam.release();
504 }
505 
isRangeEqual(const ScRange &) const506 bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const
507 {
508     return false;
509 }
510 
511