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 45 void lcl_toUpper(OUString& rStr) 46 { 47 rStr = ScGlobal::pCharClass->toUpper(rStr.trim(), 0, static_cast<xub_StrLen>(rStr.getLength())); 48 } 49 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 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 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 255 ScDBRangeBase::ScDBRangeBase(ScDocument* pDoc, RefType eType) : 256 mpDoc(pDoc), meType(eType) 257 { 258 } 259 260 ScDBRangeBase::~ScDBRangeBase() 261 { 262 } 263 264 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 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 283 ScDocument* ScDBRangeBase::getDoc() const 284 { 285 return mpDoc; 286 } 287 288 // ============================================================================ 289 290 ScDBInternalRange::ScDBInternalRange(ScDocument* pDoc, const ScRange& rRange) : 291 ScDBRangeBase(pDoc, INTERNAL), maRange(rRange) 292 { 293 } 294 295 ScDBInternalRange::~ScDBInternalRange() 296 { 297 } 298 299 const ScRange& ScDBInternalRange::getRange() const 300 { 301 return maRange; 302 } 303 304 SCCOL ScDBInternalRange::getColSize() const 305 { 306 return maRange.aEnd.Col() - maRange.aStart.Col() + 1; 307 } 308 309 SCROW ScDBInternalRange::getRowSize() const 310 { 311 return maRange.aEnd.Row() - maRange.aStart.Row() + 1; 312 } 313 314 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 324 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 335 SCCOL ScDBInternalRange::getFirstFieldColumn() const 336 { 337 return getRange().aStart.Col(); 338 } 339 340 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 355 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 388 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 410 bool ScDBInternalRange::isRangeEqual(const ScRange& rRange) const 411 { 412 return maRange == rRange; 413 } 414 415 // ============================================================================ 416 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 426 ScDBExternalRange::~ScDBExternalRange() 427 { 428 } 429 430 SCCOL ScDBExternalRange::getColSize() const 431 { 432 return mnCols; 433 } 434 435 SCROW ScDBExternalRange::getRowSize() const 436 { 437 return mnRows; 438 } 439 440 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 450 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 458 SCCOL ScDBExternalRange::getFirstFieldColumn() const 459 { 460 return 0; 461 } 462 463 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 476 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 493 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 506 bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const 507 { 508 return false; 509 } 510 511