1 /************************************************************************* 2 * 3 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. 4 * 5 * Copyright 2008 by Sun Microsystems, Inc. 6 * 7 * OpenOffice.org - a multi-platform office productivity suite 8 * 9 * $RCSfile: interpre.hxx,v $ 10 * $Revision: 1.35.44.2 $ 11 * 12 * This file is part of OpenOffice.org. 13 * 14 * OpenOffice.org is free software: you can redistribute it and/or modify 15 * it under the terms of the GNU Lesser General Public License version 3 16 * only, as published by the Free Software Foundation. 17 * 18 * OpenOffice.org is distributed in the hope that it will be useful, 19 * but WITHOUT ANY WARRANTY; without even the implied warranty of 20 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 21 * GNU Lesser General Public License version 3 for more details 22 * (a copy is included in the LICENSE file that accompanied this code). 23 * 24 * You should have received a copy of the GNU Lesser General Public License 25 * version 3 along with OpenOffice.org. If not, see 26 * <http://www.openoffice.org/license.html> 27 * for a copy of the LGPLv3 License. 28 * 29 ************************************************************************/ 30 31 // MARKER(update_precomp.py): autogen include statement, do not remove 32 #include "precompiled_sc.hxx" 33 34 // INCLUDE --------------------------------------------------------------- 35 36 #include "doubleref.hxx" 37 #include "cell.hxx" 38 #include "global.hxx" 39 #include "document.hxx" 40 #include "queryparam.hxx" 41 #include "globstr.hrc" 42 43 #include <memory> 44 #include <vector> 45 46 using ::rtl::OUString; 47 using ::std::auto_ptr; 48 using ::std::vector; 49 50 namespace { 51 52 void lcl_toUpper(OUString& rStr) 53 { 54 rStr = ScGlobal::pCharClass->toUpper(rStr.trim(), 0, static_cast<xub_StrLen>(rStr.getLength())); 55 } 56 57 bool lcl_createStarQuery(ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef) 58 { 59 // A valid StarQuery must be at least 4 columns wide. To be precise it 60 // should be exactly 4 columns ... 61 // Additionally, if this wasn't checked, a formula pointing to a valid 1-3 62 // column Excel style query range immediately left to itself would result 63 // in a circular reference when the field name or operator or value (first 64 // to third query range column) is obtained (#i58354#). Furthermore, if the 65 // range wasn't sufficiently specified data changes wouldn't flag formula 66 // cells for recalculation. 67 68 if (pQueryRef->getColSize() < 4) 69 return false; 70 71 sal_Bool bValid; 72 sal_Bool bFound; 73 OUString aCellStr; 74 SCSIZE nIndex = 0; 75 SCROW nRow = 0; 76 SCROW nRows = pDBRef->getRowSize(); 77 SCSIZE nNewEntries = static_cast<SCSIZE>(nRows); 78 pParam->Resize(nNewEntries); 79 80 do 81 { 82 ScQueryEntry& rEntry = pParam->GetEntry(nIndex); 83 84 bValid = sal_False; 85 86 if (nIndex > 0) 87 { 88 // For all entries after the first one, check the and/or connector in the first column. 89 aCellStr = pQueryRef->getString(0, nRow); 90 lcl_toUpper(aCellStr); 91 if ( aCellStr.equals(ScGlobal::GetRscString(STR_TABLE_UND)) ) 92 { 93 rEntry.eConnect = SC_AND; 94 bValid = sal_True; 95 } 96 else if ( aCellStr.equals(ScGlobal::GetRscString(STR_TABLE_ODER)) ) 97 { 98 rEntry.eConnect = SC_OR; 99 bValid = sal_True; 100 } 101 } 102 103 if ((nIndex < 1) || bValid) 104 { 105 // field name in the 2nd column. 106 bFound = sal_False; 107 aCellStr = pQueryRef->getString(1, nRow); 108 SCCOL nField = pDBRef->findFieldColumn(aCellStr); // TODO: must be case insensitive comparison. 109 if (ValidCol(nField)) 110 { 111 rEntry.nField = nField; 112 bValid = true; 113 } 114 else 115 bValid = false; 116 } 117 118 if (bValid) 119 { 120 // equality, non-equality operator in the 3rd column. 121 bFound = sal_False; 122 aCellStr = pQueryRef->getString(2, nRow); 123 lcl_toUpper(aCellStr); 124 const sal_Unicode* p = aCellStr.getStr(); 125 if (p[0] == sal_Unicode('<')) 126 { 127 if (p[1] == sal_Unicode('>')) 128 rEntry.eOp = SC_NOT_EQUAL; 129 else if (p[1] == sal_Unicode('=')) 130 rEntry.eOp = SC_LESS_EQUAL; 131 else 132 rEntry.eOp = SC_LESS; 133 } 134 else if (p[0] == sal_Unicode('>')) 135 { 136 if (p[1] == sal_Unicode('=')) 137 rEntry.eOp = SC_GREATER_EQUAL; 138 else 139 rEntry.eOp = SC_GREATER; 140 } 141 else if (p[0] == sal_Unicode('=')) 142 rEntry.eOp = SC_EQUAL; 143 144 } 145 146 if (bValid) 147 { 148 // Finally, the right-hand-side value in the 4th column. 149 *rEntry.pStr = pQueryRef->getString(3, nRow); 150 rEntry.bDoQuery = sal_True; 151 } 152 nIndex++; 153 nRow++; 154 } 155 while (bValid && (nRow < nRows) /* && (nIndex < MAXQUERY) */ ); 156 return bValid; 157 } 158 159 bool lcl_createExcelQuery( 160 ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef) 161 { 162 bool bValid = true; 163 SCCOL nCols = pQueryRef->getColSize(); 164 SCROW nRows = pQueryRef->getRowSize(); 165 vector<SCCOL> aFields(nCols); 166 SCCOL nCol = 0; 167 while (bValid && (nCol < nCols)) 168 { 169 OUString aQueryStr = pQueryRef->getString(nCol, 0); 170 SCCOL nField = pDBRef->findFieldColumn(aQueryStr); 171 if (ValidCol(nField)) 172 aFields[nCol] = nField; 173 else 174 bValid = false; 175 ++nCol; 176 } 177 178 if (bValid) 179 { 180 // sal_uLong nVisible = 0; 181 // for ( nCol=nCol1; nCol<=nCol2; nCol++ ) 182 // nVisible += aCol[nCol].VisibleCount( nRow1+1, nRow2 ); 183 184 // Count the number of visible cells (excluding the header row). Each 185 // visible cell corresponds with a single query. 186 SCSIZE nVisible = pQueryRef->getVisibleDataCellCount(); 187 if ( nVisible > SCSIZE_MAX / sizeof(void*) ) 188 { 189 DBG_ERROR("zu viele Filterkritierien"); 190 nVisible = 0; 191 } 192 193 SCSIZE nNewEntries = nVisible; 194 pParam->Resize( nNewEntries ); 195 196 SCSIZE nIndex = 0; 197 SCROW nRow = 1; 198 String aCellStr; 199 while (nRow < nRows) 200 { 201 nCol = 0; 202 while (nCol < nCols) 203 { 204 aCellStr = pQueryRef->getString(nCol, nRow); 205 ScGlobal::pCharClass->toUpper( aCellStr ); 206 if (aCellStr.Len() > 0) 207 { 208 if (nIndex < nNewEntries) 209 { 210 pParam->GetEntry(nIndex).nField = aFields[nCol]; 211 pParam->FillInExcelSyntax(aCellStr, nIndex); 212 nIndex++; 213 if (nIndex < nNewEntries) 214 pParam->GetEntry(nIndex).eConnect = SC_AND; 215 } 216 else 217 bValid = sal_False; 218 } 219 nCol++; 220 } 221 nRow++; 222 if (nIndex < nNewEntries) 223 pParam->GetEntry(nIndex).eConnect = SC_OR; 224 } 225 } 226 return bValid; 227 } 228 229 bool lcl_fillQueryEntries( 230 ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef) 231 { 232 SCSIZE nCount = pParam->GetEntryCount(); 233 for (SCSIZE i = 0; i < nCount; ++i) 234 pParam->GetEntry(i).Clear(); 235 236 // Standard QueryTabelle 237 bool bValid = lcl_createStarQuery(pParam, pDBRef, pQueryRef); 238 // Excel QueryTabelle 239 if (!bValid) 240 bValid = lcl_createExcelQuery(pParam, pDBRef, pQueryRef); 241 242 nCount = pParam->GetEntryCount(); 243 if (bValid) 244 { 245 // bQueryByString muss gesetzt sein 246 for (SCSIZE i = 0; i < nCount; ++i) 247 pParam->GetEntry(i).bQueryByString = true; 248 } 249 else 250 { 251 // nix 252 for (SCSIZE i = 0; i < nCount; ++i) 253 pParam->GetEntry(i).Clear(); 254 } 255 return bValid; 256 } 257 258 } 259 260 // ============================================================================ 261 262 ScDBRangeBase::ScDBRangeBase(ScDocument* pDoc, RefType eType) : 263 mpDoc(pDoc), meType(eType) 264 { 265 } 266 267 ScDBRangeBase::~ScDBRangeBase() 268 { 269 } 270 271 bool ScDBRangeBase::fillQueryEntries(ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef) const 272 { 273 if (!pDBRef) 274 return false; 275 276 return lcl_fillQueryEntries(pParam, pDBRef, this); 277 } 278 279 void ScDBRangeBase::fillQueryOptions(ScQueryParamBase* pParam) 280 { 281 pParam->bHasHeader = true; 282 pParam->bByRow = true; 283 pParam->bInplace = true; 284 pParam->bCaseSens = false; 285 pParam->bRegExp = false; 286 pParam->bDuplicate = true; 287 pParam->bMixedComparison = false; 288 } 289 290 ScDocument* ScDBRangeBase::getDoc() const 291 { 292 return mpDoc; 293 } 294 295 // ============================================================================ 296 297 ScDBInternalRange::ScDBInternalRange(ScDocument* pDoc, const ScRange& rRange) : 298 ScDBRangeBase(pDoc, INTERNAL), maRange(rRange) 299 { 300 } 301 302 ScDBInternalRange::~ScDBInternalRange() 303 { 304 } 305 306 const ScRange& ScDBInternalRange::getRange() const 307 { 308 return maRange; 309 } 310 311 SCCOL ScDBInternalRange::getColSize() const 312 { 313 return maRange.aEnd.Col() - maRange.aStart.Col() + 1; 314 } 315 316 SCROW ScDBInternalRange::getRowSize() const 317 { 318 return maRange.aEnd.Row() - maRange.aStart.Row() + 1; 319 } 320 321 SCSIZE ScDBInternalRange::getVisibleDataCellCount() const 322 { 323 SCCOL nCols = getColSize(); 324 SCROW nRows = getRowSize(); 325 if (nRows <= 1) 326 return 0; 327 328 return (nRows-1)*nCols; 329 } 330 331 OUString ScDBInternalRange::getString(SCCOL nCol, SCROW nRow) const 332 { 333 String aStr; 334 const ScAddress& s = maRange.aStart; 335 // #i109200# this is used in formula calculation, use GetInputString, not GetString 336 // (consistent with ScDBInternalRange::getCellString) 337 // GetStringForFormula is not used here, to allow querying for date values. 338 getDoc()->GetInputString(s.Col() + nCol, s.Row() + nRow, maRange.aStart.Tab(), aStr); 339 return aStr; 340 } 341 342 SCCOL ScDBInternalRange::getFirstFieldColumn() const 343 { 344 return getRange().aStart.Col(); 345 } 346 347 SCCOL ScDBInternalRange::findFieldColumn(SCCOL nIndex) const 348 { 349 const ScRange& rRange = getRange(); 350 const ScAddress& s = rRange.aStart; 351 const ScAddress& e = rRange.aEnd; 352 353 SCCOL nDBCol1 = s.Col(); 354 SCCOL nDBCol2 = e.Col(); 355 356 if ( nIndex <= 0 || nIndex > (nDBCol2 - nDBCol1 + 1) ) 357 return nDBCol1; 358 359 return Min(nDBCol2, static_cast<SCCOL>(nDBCol1 + nIndex - 1)); 360 } 361 362 SCCOL ScDBInternalRange::findFieldColumn(const OUString& rStr, sal_uInt16* pErr) const 363 { 364 const ScAddress& s = maRange.aStart; 365 const ScAddress& e = maRange.aEnd; 366 OUString aUpper = rStr; 367 lcl_toUpper(aUpper); 368 369 SCCOL nDBCol1 = s.Col(); 370 SCROW nDBRow1 = s.Row(); 371 SCTAB nDBTab1 = s.Tab(); 372 SCCOL nDBCol2 = e.Col(); 373 374 SCCOL nField = nDBCol1; 375 sal_Bool bFound = sal_True; 376 377 bFound = sal_False; 378 OUString aCellStr; 379 ScAddress aLook( nDBCol1, nDBRow1, nDBTab1 ); 380 while (!bFound && (aLook.Col() <= nDBCol2)) 381 { 382 sal_uInt16 nErr = getDoc()->GetStringForFormula( aLook, aCellStr ); 383 if (pErr) 384 *pErr = nErr; 385 lcl_toUpper(aCellStr); 386 bFound = ScGlobal::GetpTransliteration()->isEqual(aCellStr, aUpper); 387 if (!bFound) 388 aLook.IncCol(); 389 } 390 nField = aLook.Col(); 391 392 return bFound ? nField : -1; 393 } 394 395 ScDBQueryParamBase* ScDBInternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const 396 { 397 auto_ptr<ScDBQueryParamInternal> pParam(new ScDBQueryParamInternal); 398 399 // Set the database range first. 400 const ScAddress& s = maRange.aStart; 401 const ScAddress& e = maRange.aEnd; 402 pParam->nCol1 = s.Col(); 403 pParam->nRow1 = s.Row(); 404 pParam->nCol2 = e.Col(); 405 pParam->nRow2 = e.Row(); 406 pParam->nTab = s.Tab(); 407 408 fillQueryOptions(pParam.get()); 409 410 // Now construct the query entries from the query range. 411 if (!pQueryRef->fillQueryEntries(pParam.get(), this)) 412 return NULL; 413 414 return pParam.release(); 415 } 416 417 bool ScDBInternalRange::isRangeEqual(const ScRange& rRange) const 418 { 419 return maRange == rRange; 420 } 421 422 // ============================================================================ 423 424 ScDBExternalRange::ScDBExternalRange(ScDocument* pDoc, const ScMatrixRef& pMat) : 425 ScDBRangeBase(pDoc, EXTERNAL), mpMatrix(pMat) 426 { 427 SCSIZE nC, nR; 428 mpMatrix->GetDimensions(nC, nR); 429 mnCols = static_cast<SCCOL>(nC); 430 mnRows = static_cast<SCROW>(nR); 431 } 432 433 ScDBExternalRange::~ScDBExternalRange() 434 { 435 } 436 437 SCCOL ScDBExternalRange::getColSize() const 438 { 439 return mnCols; 440 } 441 442 SCROW ScDBExternalRange::getRowSize() const 443 { 444 return mnRows; 445 } 446 447 SCSIZE ScDBExternalRange::getVisibleDataCellCount() const 448 { 449 SCCOL nCols = getColSize(); 450 SCROW nRows = getRowSize(); 451 if (nRows <= 1) 452 return 0; 453 454 return (nRows-1)*nCols; 455 } 456 457 OUString ScDBExternalRange::getString(SCCOL nCol, SCROW nRow) const 458 { 459 if (nCol >= mnCols || nRow >= mnRows) 460 return OUString(); 461 462 return mpMatrix->GetString(nCol, nRow); 463 } 464 465 SCCOL ScDBExternalRange::getFirstFieldColumn() const 466 { 467 return 0; 468 } 469 470 SCCOL ScDBExternalRange::findFieldColumn(SCCOL nIndex) const 471 { 472 if (nIndex < 1) 473 // 1st field 474 return 0; 475 476 if (nIndex > mnCols) 477 // last field 478 return mnCols - 1; 479 480 return nIndex - 1; 481 } 482 483 SCCOL ScDBExternalRange::findFieldColumn(const OUString& rStr, sal_uInt16* pErr) const 484 { 485 if (pErr) 486 pErr = 0; 487 488 OUString aUpper = rStr; 489 lcl_toUpper(aUpper); 490 for (SCCOL i = 0; i < mnCols; ++i) 491 { 492 OUString aUpperVal = mpMatrix->GetString(i, 0); 493 lcl_toUpper(aUpperVal); 494 if (aUpper.equals(aUpperVal)) 495 return i; 496 } 497 return -1; 498 } 499 500 ScDBQueryParamBase* ScDBExternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const 501 { 502 auto_ptr<ScDBQueryParamMatrix> pParam(new ScDBQueryParamMatrix); 503 pParam->mpMatrix = mpMatrix; 504 fillQueryOptions(pParam.get()); 505 506 // Now construct the query entries from the query range. 507 if (!pQueryRef->fillQueryEntries(pParam.get(), this)) 508 return NULL; 509 510 return pParam.release(); 511 } 512 513 bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const 514 { 515 return false; 516 } 517 518