sc/source/core/tool/doubleref.cxx

   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, 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     BOOL bValid;
  72     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 = 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 = TRUE;
  95             }
  96             else if ( aCellStr.equals(ScGlobal::GetRscString(STR_TABLE_ODER)) )
  97             {
  98                 rEntry.eConnect = SC_OR;
  99                 bValid = TRUE;
 100             }
 101         }
 102
 103         if ((nIndex < 1) || bValid)
 104         {
 105             // field name in the 2nd column.
 106             bFound = 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 = 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 = 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 //      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 = 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 ScDBRangeBase::RefType ScDBRangeBase::getType() const
 272 {
 273     return meType;
 274 }
 275
 276 bool ScDBRangeBase::fillQueryEntries(ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef) const
 277 {
 278     if (!pDBRef)
 279         return false;
 280
 281     return lcl_fillQueryEntries(pParam, pDBRef, this);
 282 }
 283
 284 void ScDBRangeBase::fillQueryOptions(ScQueryParamBase* pParam)
 285 {
 286     pParam->bHasHeader = true;
 287     pParam->bByRow = true;
 288     pParam->bInplace = true;
 289     pParam->bCaseSens = false;
 290     pParam->bRegExp = false;
 291     pParam->bDuplicate = true;
 292     pParam->bMixedComparison = false;
 293 }
 294
 295 ScDocument* ScDBRangeBase::getDoc() const
 296 {
 297     return mpDoc;
 298 }
 299
 300 // ============================================================================
 301
 302 ScDBInternalRange::ScDBInternalRange(ScDocument* pDoc, const ScRange& rRange) :
 303     ScDBRangeBase(pDoc, INTERNAL), maRange(rRange)
 304 {
 305 }
 306
 307 ScDBInternalRange::~ScDBInternalRange()
 308 {
 309 }
 310
 311 const ScRange& ScDBInternalRange::getRange() const
 312 {
 313     return maRange;
 314 }
 315
 316 SCCOL ScDBInternalRange::getColSize() const
 317 {
 318     return maRange.aEnd.Col() - maRange.aStart.Col() + 1;
 319 }
 320
 321 SCROW ScDBInternalRange::getRowSize() const
 322 {
 323     return maRange.aEnd.Row() - maRange.aStart.Row() + 1;
 324 }
 325
 326 SCSIZE ScDBInternalRange::getVisibleDataCellCount() const
 327 {
 328     SCCOL nCols = getColSize();
 329     SCROW nRows = getRowSize();
 330     if (nRows <= 1)
 331         return 0;
 332
 333     return (nRows-1)*nCols;
 334 }
 335
 336 OUString ScDBInternalRange::getString(SCCOL nCol, SCROW nRow) const
 337 {
 338     String aStr;
 339     const ScAddress& s = maRange.aStart;
 340     getDoc()->GetString(s.Col() + nCol, s.Row() + nRow, maRange.aStart.Tab(), aStr);
 341     return aStr;
 342 }
 343
 344 SCCOL ScDBInternalRange::getFirstFieldColumn() const
 345 {
 346     return getRange().aStart.Col();
 347 }
 348
 349 SCCOL ScDBInternalRange::findFieldColumn(SCCOL nIndex) const
 350 {
 351     const ScRange& rRange = getRange();
 352     const ScAddress& s = rRange.aStart;
 353     const ScAddress& e = rRange.aEnd;
 354
 355     SCCOL nDBCol1 = s.Col();
 356     SCCOL nDBCol2 = e.Col();
 357
 358     if ( nIndex <= 0 || nIndex > (nDBCol2 - nDBCol1 + 1) )
 359         return nDBCol1;
 360
 361     return Min(nDBCol2, static_cast<SCCOL>(nDBCol1 + nIndex - 1));
 362 }
 363
 364 sal_uInt16 ScDBInternalRange::getCellString(OUString& rStr, ScBaseCell* pCell) const
 365 {
 366     sal_uInt16 nErr = 0;
 367     String aStr;
 368     if (pCell)
 369     {
 370         SvNumberFormatter* pFormatter = getDoc()->GetFormatTable();
 371         switch (pCell->GetCellType())
 372         {
 373             case CELLTYPE_STRING:
 374                 ((ScStringCell*) pCell)->GetString(aStr);
 375             break;
 376             case CELLTYPE_EDIT:
 377                 ((ScEditCell*) pCell)->GetString(aStr);
 378             break;
 379             case CELLTYPE_FORMULA:
 380             {
 381                 ScFormulaCell* pFCell = (ScFormulaCell*) pCell;
 382                 nErr = pFCell->GetErrCode();
 383                 if (pFCell->IsValue())
 384                 {
 385                     double fVal = pFCell->GetValue();
 386                     ULONG nIndex = pFormatter->GetStandardFormat(
 387                                         NUMBERFORMAT_NUMBER,
 388                                         ScGlobal::eLnge);
 389                     pFormatter->GetInputLineString(fVal, nIndex, aStr);
 390                 }
 391                 else
 392                     pFCell->GetString(aStr);
 393             }
 394             break;
 395             case CELLTYPE_VALUE:
 396             {
 397                 double fVal = ((ScValueCell*) pCell)->GetValue();
 398                 ULONG nIndex = pFormatter->GetStandardFormat(
 399                                         NUMBERFORMAT_NUMBER,
 400                                         ScGlobal::eLnge);
 401                 pFormatter->GetInputLineString(fVal, nIndex, aStr);
 402             }
 403             break;
 404             default:
 405                 ;
 406         }
 407     }
 408     rStr = aStr;
 409     return nErr;
 410 }
 411
 412 SCCOL ScDBInternalRange::findFieldColumn(const OUString& rStr, sal_uInt16* pErr) const
 413 {
 414     const ScAddress& s = maRange.aStart;
 415     const ScAddress& e = maRange.aEnd;
 416     OUString aUpper = rStr;
 417     lcl_toUpper(aUpper);
 418
 419     SCCOL nDBCol1 = s.Col();
 420     SCROW nDBRow1 = s.Row();
 421     SCTAB nDBTab1 = s.Tab();
 422     SCCOL nDBCol2 = e.Col();
 423
 424     SCCOL   nField = nDBCol1;
 425     BOOL    bFound = TRUE;
 426
 427     bFound = FALSE;
 428     OUString aCellStr;
 429     ScAddress aLook( nDBCol1, nDBRow1, nDBTab1 );
 430     while (!bFound && (aLook.Col() <= nDBCol2))
 431     {
 432         ScBaseCell* pCell = getDoc()->GetCell( aLook );
 433         sal_uInt16 nErr = getCellString( aCellStr, pCell );
 434         if (pErr)
 435             *pErr = nErr;
 436         lcl_toUpper(aCellStr);
 437         bFound = ScGlobal::GetpTransliteration()->isEqual(aCellStr, aUpper);
 438         if (!bFound)
 439             aLook.IncCol();
 440     }
 441     nField = aLook.Col();
 442
 443     return bFound ? nField : -1;
 444 }
 445
 446 ScDBQueryParamBase* ScDBInternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
 447 {
 448     auto_ptr<ScDBQueryParamInternal> pParam(new ScDBQueryParamInternal);
 449
 450     // Set the database range first.
 451     const ScAddress& s = maRange.aStart;
 452     const ScAddress& e = maRange.aEnd;
 453     pParam->nCol1 = s.Col();
 454     pParam->nRow1 = s.Row();
 455     pParam->nCol2 = e.Col();
 456     pParam->nRow2 = e.Row();
 457     pParam->nTab  = s.Tab();
 458
 459     fillQueryOptions(pParam.get());
 460
 461     // Now construct the query entries from the query range.
 462     if (!pQueryRef->fillQueryEntries(pParam.get(), this))
 463         return NULL;
 464
 465     return pParam.release();
 466 }
 467
 468 bool ScDBInternalRange::isRangeEqual(const ScRange& rRange) const
 469 {
 470     return maRange == rRange;
 471 }
 472
 473 // ============================================================================
 474
 475 ScDBExternalRange::ScDBExternalRange(ScDocument* pDoc, const ScMatrixRef& pMat) :
 476     ScDBRangeBase(pDoc, EXTERNAL), mpMatrix(pMat)
 477 {
 478     SCSIZE nC, nR;
 479     mpMatrix->GetDimensions(nC, nR);
 480     mnCols = nC;
 481     mnRows = nR;
 482 }
 483
 484 ScDBExternalRange::~ScDBExternalRange()
 485 {
 486 }
 487
 488 SCCOL ScDBExternalRange::getColSize() const
 489 {
 490     return mnCols;
 491 }
 492
 493 SCROW ScDBExternalRange::getRowSize() const
 494 {
 495     return mnRows;
 496 }
 497
 498 SCSIZE ScDBExternalRange::getVisibleDataCellCount() const
 499 {
 500     SCCOL nCols = getColSize();
 501     SCROW nRows = getRowSize();
 502     if (nRows <= 1)
 503         return 0;
 504
 505     return (nRows-1)*nCols;
 506 }
 507
 508 OUString ScDBExternalRange::getString(SCCOL nCol, SCROW nRow) const
 509 {
 510     if (nCol >= mnCols || nRow >= mnRows)
 511         return OUString();
 512
 513     return mpMatrix->GetString(nCol, nRow);
 514 }
 515
 516 SCCOL ScDBExternalRange::getFirstFieldColumn() const
 517 {
 518     return 0;
 519 }
 520
 521 SCCOL ScDBExternalRange::findFieldColumn(SCCOL nIndex) const
 522 {
 523     if (nIndex < 1)
 524         // 1st field
 525         return 0;
 526
 527     if (nIndex > mnCols)
 528         // last field
 529         return mnCols - 1;
 530
 531     return nIndex - 1;
 532 }
 533
 534 SCCOL ScDBExternalRange::findFieldColumn(const OUString& rStr, sal_uInt16* pErr) const
 535 {
 536     if (pErr)
 537         pErr = 0;
 538
 539     OUString aUpper = rStr;
 540     lcl_toUpper(aUpper);
 541     for (SCCOL i = 0; i < mnCols; ++i)
 542     {
 543         OUString aUpperVal = mpMatrix->GetString(i, 0);
 544         lcl_toUpper(aUpperVal);
 545         if (aUpper.equals(aUpperVal))
 546             return i;
 547     }
 548     return -1;
 549 }
 550
 551 ScDBQueryParamBase* ScDBExternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
 552 {
 553     auto_ptr<ScDBQueryParamMatrix> pParam(new ScDBQueryParamMatrix);
 554     pParam->mpMatrix = mpMatrix;
 555     fillQueryOptions(pParam.get());
 556
 557     // Now construct the query entries from the query range.
 558     if (!pQueryRef->fillQueryEntries(pParam.get(), this))
 559         return NULL;
 560
 561     return pParam.release();
 562 }
 563
 564 bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const
 565 {
 566     return false;
 567 }
 568