sc/source/core/data/validat.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: validat.cxx,v $
  10  * $Revision: 1.24.110.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
  35
  36 // INCLUDE ---------------------------------------------------------------
  37
  38 #include "scitems.hxx"
  39 #include <sfx2/app.hxx>
  40 #include <sfx2/docfile.hxx>
  41 #include <sfx2/objsh.hxx>
  42 #include <basic/sbmeth.hxx>
  43 #include <basic/sbmod.hxx>
  44 #include <basic/sbstar.hxx>
  45 #include <basic/basmgr.hxx>
  46
  47 #include <basic/sbx.hxx>
  48 #include <svtools/zforlist.hxx>
  49 #include <vcl/msgbox.hxx>
  50 #include <tools/urlobj.hxx>
  51 #include <rtl/math.hxx>
  52
  53 #include "validat.hxx"
  54 #include "document.hxx"
  55 #include "cell.hxx"
  56 #include "patattr.hxx"
  57 #include "rechead.hxx"
  58 #include "globstr.hrc"
  59 #include "rangenam.hxx"
  60 #include "dbcolect.hxx"
  61
  62 #include <math.h>
  63 #include <memory>
  64
  65 using namespace formula;
  66 //------------------------------------------------------------------------
  67
  68 SV_IMPL_OP_PTRARR_SORT( ScValidationEntries_Impl, ScValidationDataPtr );
  69
  70 //------------------------------------------------------------------------
  71
  72 //
  73 //      Eintrag fuer Gueltigkeit (es gibt nur eine Bedingung)
  74 //
  75
  76 ScValidationData::ScValidationData( ScValidationMode eMode, ScConditionMode eOper,
  77                             const String& rExpr1, const String& rExpr2,
  78                             ScDocument* pDocument, const ScAddress& rPos,
  79                             const String& rExprNmsp1, const String& rExprNmsp2,
  80                             FormulaGrammar::Grammar eGrammar1, FormulaGrammar::Grammar eGrammar2 ) :
  81     ScConditionEntry( eOper, rExpr1, rExpr2, pDocument, rPos, rExprNmsp1, rExprNmsp2, eGrammar1, eGrammar2 ),
  82     nKey( 0 ),
  83     eDataMode( eMode ),
  84     eErrorStyle( SC_VALERR_STOP ),
  85     mnListType( ValidListType::UNSORTED )
  86 {
  87     bShowInput = bShowError = FALSE;
  88 }
  89
  90 ScValidationData::ScValidationData( ScValidationMode eMode, ScConditionMode eOper,
  91                             const ScTokenArray* pArr1, const ScTokenArray* pArr2,
  92                             ScDocument* pDocument, const ScAddress& rPos ) :
  93     ScConditionEntry( eOper, pArr1, pArr2, pDocument, rPos ),
  94     nKey( 0 ),
  95     eDataMode( eMode ),
  96     eErrorStyle( SC_VALERR_STOP ),
  97     mnListType( ValidListType::UNSORTED )
  98 {
  99     bShowInput = bShowError = FALSE;
 100 }
 101
 102 ScValidationData::ScValidationData( const ScValidationData& r ) :
 103     ScConditionEntry( r ),
 104     nKey( r.nKey ),
 105     eDataMode( r.eDataMode ),
 106     bShowInput( r.bShowInput ),
 107     bShowError( r.bShowError ),
 108     eErrorStyle( r.eErrorStyle ),
 109     mnListType( r.mnListType ),
 110     aInputTitle( r.aInputTitle ),
 111     aInputMessage( r.aInputMessage ),
 112     aErrorTitle( r.aErrorTitle ),
 113     aErrorMessage( r.aErrorMessage )
 114 {
 115     //  Formeln per RefCount kopiert
 116 }
 117
 118 ScValidationData::ScValidationData( ScDocument* pDocument, const ScValidationData& r ) :
 119     ScConditionEntry( pDocument, r ),
 120     nKey( r.nKey ),
 121     eDataMode( r.eDataMode ),
 122     bShowInput( r.bShowInput ),
 123     bShowError( r.bShowError ),
 124     eErrorStyle( r.eErrorStyle ),
 125     mnListType( r.mnListType ),
 126     aInputTitle( r.aInputTitle ),
 127     aInputMessage( r.aInputMessage ),
 128     aErrorTitle( r.aErrorTitle ),
 129     aErrorMessage( r.aErrorMessage )
 130 {
 131     //  Formeln wirklich kopiert
 132 }
 133
 134 ScValidationData::~ScValidationData()
 135 {
 136 }
 137
 138 BOOL ScValidationData::IsEmpty() const
 139 {
 140     String aEmpty;
 141     ScValidationData aDefault( SC_VALID_ANY, SC_COND_EQUAL, aEmpty, aEmpty, GetDocument(), ScAddress() );
 142     return EqualEntries( aDefault );
 143 }
 144
 145 BOOL ScValidationData::EqualEntries( const ScValidationData& r ) const
 146 {
 147         //      gleiche Parameter eingestellt (ohne Key)
 148
 149     return ScConditionEntry::operator==(r) &&
 150             eDataMode           == r.eDataMode &&
 151             bShowInput          == r.bShowInput &&
 152             bShowError          == r.bShowError &&
 153             eErrorStyle         == r.eErrorStyle &&
 154             mnListType      == r.mnListType &&
 155             aInputTitle         == r.aInputTitle &&
 156             aInputMessage       == r.aInputMessage &&
 157             aErrorTitle         == r.aErrorTitle &&
 158             aErrorMessage       == r.aErrorMessage;
 159 }
 160
 161 void ScValidationData::ResetInput()
 162 {
 163     bShowInput = FALSE;
 164 }
 165
 166 void ScValidationData::ResetError()
 167 {
 168     bShowError = FALSE;
 169 }
 170
 171 void ScValidationData::SetInput( const String& rTitle, const String& rMsg )
 172 {
 173     bShowInput = TRUE;
 174     aInputTitle = rTitle;
 175     aInputMessage = rMsg;
 176 }
 177
 178 void ScValidationData::SetError( const String& rTitle, const String& rMsg,
 179                                     ScValidErrorStyle eStyle )
 180 {
 181     bShowError = TRUE;
 182     eErrorStyle = eStyle;
 183     aErrorTitle = rTitle;
 184     aErrorMessage = rMsg;
 185 }
 186
 187 BOOL ScValidationData::GetErrMsg( String& rTitle, String& rMsg,
 188                                     ScValidErrorStyle& rStyle ) const
 189 {
 190     rTitle = aErrorTitle;
 191     rMsg   = aErrorMessage;
 192     rStyle = eErrorStyle;
 193     return bShowError;
 194 }
 195
 196 BOOL ScValidationData::DoScript( const ScAddress& rPos, const String& rInput,
 197                                 ScFormulaCell* pCell, Window* pParent ) const
 198 {
 199     ScDocument* pDocument = GetDocument();
 200     SfxObjectShell* pDocSh = pDocument->GetDocumentShell();
 201     if ( !pDocSh || !pDocument->CheckMacroWarn() )
 202         return FALSE;
 203
 204     BOOL bScriptReturnedFalse = FALSE;  // Standard: kein Abbruch
 205
 206     // Set up parameters
 207     ::com::sun::star::uno::Sequence< ::com::sun::star::uno::Any > aParams(2);
 208
 209     //  1) eingegebener / berechneter Wert
 210     String aValStr = rInput;
 211     double nValue;
 212     BOOL bIsValue = FALSE;
 213     if ( pCell )                                // wenn Zelle gesetzt, aus Interpret gerufen
 214     {
 215         bIsValue = pCell->IsValue();
 216         if ( bIsValue )
 217             nValue  = pCell->GetValue();
 218         else
 219             pCell->GetString( aValStr );
 220     }
 221     if ( bIsValue )
 222         aParams[0] = ::com::sun::star::uno::makeAny( nValue );
 223     else
 224         aParams[0] = ::com::sun::star::uno::makeAny( ::rtl::OUString( aValStr ) );
 225
 226     //  2) Position der Zelle
 227     String aPosStr;
 228     rPos.Format( aPosStr, SCA_VALID | SCA_TAB_3D, pDocument, pDocument->GetAddressConvention() );
 229     aParams[1] = ::com::sun::star::uno::makeAny( ::rtl::OUString( aPosStr ) );
 230
 231     //  use link-update flag to prevent closing the document
 232     //  while the macro is running
 233     BOOL bWasInLinkUpdate = pDocument->IsInLinkUpdate();
 234     if ( !bWasInLinkUpdate )
 235         pDocument->SetInLinkUpdate( TRUE );
 236
 237     if ( pCell )
 238         pDocument->LockTable( rPos.Tab() );
 239
 240     ::com::sun::star::uno::Any aRet;
 241     ::com::sun::star::uno::Sequence< sal_Int16 > aOutArgsIndex;
 242     ::com::sun::star::uno::Sequence< ::com::sun::star::uno::Any > aOutArgs;
 243
 244     ErrCode eRet = pDocSh->CallXScript(
 245         aErrorTitle, aParams, aRet, aOutArgsIndex, aOutArgs );
 246
 247     if ( pCell )
 248         pDocument->UnlockTable( rPos.Tab() );
 249
 250     if ( !bWasInLinkUpdate )
 251         pDocument->SetInLinkUpdate( FALSE );
 252
 253     // Check the return value from the script
 254     // The contents of the cell get reset if the script returns false
 255     BOOL bTmp = FALSE;
 256     if ( eRet == ERRCODE_NONE &&
 257              aRet.getValueType() == getCppuBooleanType() &&
 258              sal_True == ( aRet >>= bTmp ) &&
 259              bTmp == FALSE )
 260     {
 261         bScriptReturnedFalse = TRUE;
 262     }
 263
 264     if ( eRet == ERRCODE_BASIC_METHOD_NOT_FOUND && !pCell )
 265     // Makro nicht gefunden (nur bei Eingabe)
 266     {
 267         //!     andere Fehlermeldung, wenn gefunden, aber nicht bAllowed ??
 268
 269         ErrorBox aBox( pParent, WinBits(WB_OK),
 270                         ScGlobal::GetRscString( STR_VALID_MACRONOTFOUND ) );
 271         aBox.Execute();
 272     }
 273
 274     return bScriptReturnedFalse;
 275 }
 276
 277     // TRUE -> Abbruch
 278
 279 BOOL ScValidationData::DoMacro( const ScAddress& rPos, const String& rInput,
 280                                 ScFormulaCell* pCell, Window* pParent ) const
 281 {
 282     if ( SfxApplication::IsXScriptURL( aErrorTitle ) )
 283     {
 284         return DoScript( rPos, rInput, pCell, pParent );
 285     }
 286
 287     ScDocument* pDocument = GetDocument();
 288     SfxObjectShell* pDocSh = pDocument->GetDocumentShell();
 289     if ( !pDocSh || !pDocument->CheckMacroWarn() )
 290         return FALSE;
 291
 292     BOOL bDone = FALSE;
 293     BOOL bRet = FALSE;                                          // Standard: kein Abbruch
 294     SfxApplication* pSfxApp = SFX_APP();
 295     pSfxApp->EnterBasicCall();                          // Dok-Basic anlegen etc.
 296
 297     //  Wenn das Dok waehrend eines Basic-Calls geladen wurde,
 298     //  ist das Sbx-Objekt evtl. nicht angelegt (?)
 299 //      pDocSh->GetSbxObject();
 300
 301     //  keine Sicherheitsabfrage mehr vorneweg (nur CheckMacroWarn), das passiert im CallBasic
 302
 303 #if 0
 304     // Makro-Name liegt in folgender Form vor:
 305     // "Macroname.Modulname.Libname.Dokumentname" oder
 306     // "Macroname.Modulname.Libname.Applikationsname"
 307     String aMacroName = aErrorTitle.GetToken(0, '.');
 308     String aModulName = aErrorTitle.GetToken(1, '.');
 309     String aLibName   = aErrorTitle.GetToken(2, '.');
 310     String aDocName   = aErrorTitle.GetToken(3, '.');
 311 #endif
 312
 313     //  Funktion ueber den einfachen Namen suchen,
 314     //  dann aBasicStr, aMacroStr fuer SfxObjectShell::CallBasic zusammenbauen
 315
 316     StarBASIC* pRoot = pDocSh->GetBasic();
 317     SbxVariable* pVar = pRoot->Find( aErrorTitle, SbxCLASS_METHOD );
 318     if ( pVar && pVar->ISA(SbMethod) )
 319     {
 320         SbMethod* pMethod = (SbMethod*)pVar;
 321         SbModule* pModule = pMethod->GetModule();
 322         SbxObject* pObject = pModule->GetParent();
 323         String aMacroStr = pObject->GetName();
 324         aMacroStr += '.';
 325         aMacroStr += pModule->GetName();
 326         aMacroStr += '.';
 327         aMacroStr += pMethod->GetName();
 328         String aBasicStr;
 329
 330         //      #95867# the distinction between document- and app-basic has to be done
 331         //      by checking the parent (as in ScInterpreter::ScMacro), not by looping
 332         //      over all open documents, because this may be called from within loading,
 333         //      when SfxObjectShell::GetFirst/GetNext won't find the document.
 334
 335         if ( pObject->GetParent() )
 336             aBasicStr = pObject->GetParent()->GetName();        // Dokumentenbasic
 337         else
 338             aBasicStr = SFX_APP()->GetName();                           // Applikationsbasic
 339
 340         //      Parameter fuer Makro
 341         SbxArrayRef refPar = new SbxArray;
 342
 343         //      1) eingegebener / berechneter Wert
 344         String aValStr = rInput;
 345         double nValue = 0.0;
 346         BOOL bIsValue = FALSE;
 347         if ( pCell )                            // wenn Zelle gesetzt, aus Interpret gerufen
 348         {
 349             bIsValue = pCell->IsValue();
 350             if ( bIsValue )
 351                 nValue  = pCell->GetValue();
 352             else
 353                 pCell->GetString( aValStr );
 354         }
 355         if ( bIsValue )
 356             refPar->Get(1)->PutDouble( nValue );
 357         else
 358             refPar->Get(1)->PutString( aValStr );
 359
 360         //      2) Position der Zelle
 361         String aPosStr;
 362         rPos.Format( aPosStr, SCA_VALID | SCA_TAB_3D, pDocument, pDocument->GetAddressConvention() );
 363         refPar->Get(2)->PutString( aPosStr );
 364
 365         //      use link-update flag to prevent closing the document
 366         //      while the macro is running
 367         BOOL bWasInLinkUpdate = pDocument->IsInLinkUpdate();
 368         if ( !bWasInLinkUpdate )
 369             pDocument->SetInLinkUpdate( TRUE );
 370
 371         if ( pCell )
 372             pDocument->LockTable( rPos.Tab() );
 373         SbxVariableRef refRes = new SbxVariable;
 374         ErrCode eRet = pDocSh->CallBasic( aMacroStr, aBasicStr, NULL, refPar, refRes );
 375         if ( pCell )
 376             pDocument->UnlockTable( rPos.Tab() );
 377
 378         if ( !bWasInLinkUpdate )
 379             pDocument->SetInLinkUpdate( FALSE );
 380
 381         //      Eingabe abbrechen, wenn Basic-Makro FALSE zurueckgibt
 382         if ( eRet == ERRCODE_NONE && refRes->GetType() == SbxBOOL && refRes->GetBool() == FALSE )
 383             bRet = TRUE;
 384         bDone = TRUE;
 385     }
 386     pSfxApp->LeaveBasicCall();
 387
 388     if ( !bDone && !pCell )                     // Makro nicht gefunden (nur bei Eingabe)
 389     {
 390         //!     andere Fehlermeldung, wenn gefunden, aber nicht bAllowed ??
 391
 392         ErrorBox aBox( pParent, WinBits(WB_OK),
 393                         ScGlobal::GetRscString( STR_VALID_MACRONOTFOUND ) );
 394         aBox.Execute();
 395     }
 396
 397     return bRet;
 398 }
 399
 400 void ScValidationData::DoCalcError( ScFormulaCell* pCell ) const
 401 {
 402     if ( eErrorStyle == SC_VALERR_MACRO )
 403         DoMacro( pCell->aPos, EMPTY_STRING, pCell, NULL );
 404 }
 405
 406     // TRUE -> Abbruch
 407
 408 BOOL ScValidationData::DoError( Window* pParent, const String& rInput,
 409                                 const ScAddress& rPos ) const
 410 {
 411     if ( eErrorStyle == SC_VALERR_MACRO )
 412         return DoMacro( rPos, rInput, NULL, pParent );
 413
 414     //  Fehlermeldung ausgeben
 415
 416     String aTitle = aErrorTitle;
 417     if (!aTitle.Len())
 418         aTitle = ScGlobal::GetRscString( STR_MSSG_DOSUBTOTALS_0 );      // application title
 419     String aMessage = aErrorMessage;
 420     if (!aMessage.Len())
 421         aMessage = ScGlobal::GetRscString( STR_VALID_DEFERROR );
 422
 423     //! ErrorBox / WarningBox / InfoBox ?
 424     //! (bei InfoBox immer nur OK-Button)
 425
 426     WinBits nStyle = 0;
 427     switch (eErrorStyle)
 428     {
 429         case SC_VALERR_STOP:
 430             nStyle = WB_OK | WB_DEF_OK;
 431             break;
 432         case SC_VALERR_WARNING:
 433             nStyle = WB_OK_CANCEL | WB_DEF_CANCEL;
 434             break;
 435         case SC_VALERR_INFO:
 436             nStyle = WB_OK_CANCEL | WB_DEF_OK;
 437             break;
 438         default:
 439         {
 440             // added to avoid warnings
 441         }
 442     }
 443
 444     MessBox aBox( pParent, WinBits(nStyle), aTitle, aMessage );
 445     USHORT nRet = aBox.Execute();
 446
 447     return ( eErrorStyle == SC_VALERR_STOP || nRet == RET_CANCEL );
 448 }
 449
 450
 451 BOOL ScValidationData::IsDataValid( const String& rTest, const ScPatternAttr& rPattern,
 452                                     const ScAddress& rPos ) const
 453 {
 454     if ( eDataMode == SC_VALID_ANY )
 455         return TRUE;                                            // alles erlaubt
 456
 457     if ( rTest.GetChar(0) == '=' )
 458         return FALSE;                                           // Formeln sind sonst immer ungueltig
 459
 460     if ( !rTest.Len() )
 461         return IsIgnoreBlank();                         // leer: wie eingestellt
 462
 463     SvNumberFormatter* pFormatter = GetDocument()->GetFormatTable();
 464
 465     //  Test, was es denn ist - wie in ScColumn::SetString
 466
 467     sal_uInt32 nFormat = rPattern.GetNumberFormat( pFormatter );
 468
 469     double nVal;
 470     BOOL bIsVal = pFormatter->IsNumberFormat( rTest, nFormat, nVal );
 471     ScBaseCell* pCell;
 472     if (bIsVal)
 473         pCell = new ScValueCell( nVal );
 474     else
 475         pCell = new ScStringCell( rTest );
 476
 477     BOOL bRet = IsDataValid( pCell, rPos );
 478
 479     pCell->Delete();
 480     return bRet;
 481 }
 482
 483 BOOL ScValidationData::IsDataValid( ScBaseCell* pCell, const ScAddress& rPos ) const
 484 {
 485     if( eDataMode == SC_VALID_LIST )
 486         return IsListValid( pCell, rPos );
 487
 488     double nVal = 0.0;
 489     String aString;
 490     BOOL bIsVal = TRUE;
 491
 492     switch (pCell->GetCellType())
 493     {
 494         case CELLTYPE_VALUE:
 495             nVal = ((ScValueCell*)pCell)->GetValue();
 496             break;
 497         case CELLTYPE_STRING:
 498             ((ScStringCell*)pCell)->GetString( aString );
 499             bIsVal = FALSE;
 500             break;
 501         case CELLTYPE_EDIT:
 502             ((ScEditCell*)pCell)->GetString( aString );
 503             bIsVal = FALSE;
 504             break;
 505         case CELLTYPE_FORMULA:
 506             {
 507                 ScFormulaCell* pFCell = (ScFormulaCell*)pCell;
 508                 bIsVal = pFCell->IsValue();
 509                 if ( bIsVal )
 510                     nVal  = pFCell->GetValue();
 511                 else
 512                     pFCell->GetString( aString );
 513             }
 514             break;
 515         default:                                                // Notizen, Broadcaster
 516             return IsIgnoreBlank();             // wie eingestellt
 517     }
 518
 519     BOOL bOk = TRUE;
 520     switch (eDataMode)
 521     {
 522         // SC_VALID_ANY schon oben
 523
 524         case SC_VALID_WHOLE:
 525         case SC_VALID_DECIMAL:
 526         case SC_VALID_DATE:                     // Date/Time ist nur Formatierung
 527         case SC_VALID_TIME:
 528             bOk = bIsVal;
 529             if ( bOk && eDataMode == SC_VALID_WHOLE )
 530                 bOk = ::rtl::math::approxEqual( nVal, floor(nVal+0.5) );                // ganze Zahlen
 531             if ( bOk )
 532                 bOk = IsCellValid( pCell, rPos );
 533             break;
 534
 535         case SC_VALID_CUSTOM:
 536             //  fuer Custom muss eOp == SC_COND_DIRECT sein
 537             //! der Wert muss im Dokument stehen !!!!!!!!!!!!!!!!!!!!
 538             bOk = IsCellValid( pCell, rPos );
 539             break;
 540
 541         case SC_VALID_TEXTLEN:
 542             bOk = !bIsVal;                      // nur Text
 543             if ( bOk )
 544             {
 545                 double nLenVal = (double) aString.Len();
 546                 ScValueCell aTmpCell( nLenVal );
 547                 bOk = IsCellValid( &aTmpCell, rPos );
 548             }
 549             break;
 550
 551         default:
 552             DBG_ERROR("hammanochnich");
 553             break;
 554     }
 555
 556     return bOk;
 557 }
 558
 559 // ----------------------------------------------------------------------------
 560
 561 namespace {
 562
 563 /** Token array helper. Iterates over all string tokens.
 564     @descr  The token array must contain separated string tokens only.
 565     @param bSkipEmpty  true = Ignores string tokens with empty strings. */
 566 class ScStringTokenIterator
 567 {
 568 public:
 569     inline explicit             ScStringTokenIterator( ScTokenArray& rTokArr, bool bSkipEmpty = true ) :
 570                                     mrTokArr( rTokArr ), mbSkipEmpty( bSkipEmpty ), mbOk( true ) {}
 571
 572     /** Returns the string of the first string token or NULL on error or empty token array. */
 573     const String*               First();
 574     /** Returns the string of the next string token or NULL on error or end of token array. */
 575     const String*               Next();
 576
 577     /** Returns false, if a wrong token has been found. Does NOT return false on end of token array. */
 578     inline bool                 Ok() const { return mbOk; }
 579
 580 private:
 581     ScTokenArray&               mrTokArr;       /// The token array for iteration.
 582     bool                        mbSkipEmpty;    /// Ignore empty strings.
 583     bool                        mbOk;           /// true = correct token or end of token array.
 584 };
 585
 586 const String* ScStringTokenIterator::First()
 587 {
 588     mrTokArr.Reset();
 589     mbOk = true;
 590     return Next();
 591 }
 592
 593 const String* ScStringTokenIterator::Next()
 594 {
 595     if( !mbOk )
 596         return NULL;
 597
 598     // seek to next non-separator token
 599     const FormulaToken* pToken = mrTokArr.NextNoSpaces();
 600     while( pToken && (pToken->GetOpCode() == ocSep) )
 601         pToken = mrTokArr.NextNoSpaces();
 602
 603     mbOk = !pToken || (pToken->GetType() == formula::svString);
 604     const String* pString = (mbOk && pToken) ? &pToken->GetString() : NULL;
 605     // string found but empty -> get next token; otherwise return it
 606     return (mbSkipEmpty && pString && !pString->Len()) ? Next() : pString;
 607 }
 608
 609 // ----------------------------------------------------------------------------
 610
 611 /** Returns the number format of the passed cell, or the standard format. */
 612 ULONG lclGetCellFormat( ScDocument& rDoc, const ScAddress& rPos )
 613 {
 614     const ScPatternAttr* pPattern = rDoc.GetPattern( rPos.Col(), rPos.Row(), rPos.Tab() );
 615     if( !pPattern )
 616         pPattern = rDoc.GetDefPattern();
 617     return pPattern->GetNumberFormat( rDoc.GetFormatTable() );
 618 }
 619
 620 /** Inserts the passed string object. Always takes ownership. pData is invalid after this call! */
 621 void lclInsertStringToCollection( TypedScStrCollection& rStrColl, TypedStrData* pData, bool bSorted )
 622 {
 623     if( !(bSorted ? rStrColl.Insert( pData ) : rStrColl.AtInsert( rStrColl.GetCount(), pData )) )
 624         delete pData;
 625 }
 626
 627 } // namespace
 628
 629 // ----------------------------------------------------------------------------
 630
 631 bool ScValidationData::HasSelectionList() const
 632 {
 633     return (eDataMode == SC_VALID_LIST) && (mnListType != ValidListType::INVISIBLE);
 634 }
 635
 636 bool ScValidationData::GetSelectionFromFormula( TypedScStrCollection* pStrings,
 637                                                 ScBaseCell* pCell,
 638                                                 const ScAddress& rPos,
 639                                                 const ScTokenArray& rTokArr,
 640                                                 int& rMatch ) const
 641 {
 642     bool bOk = true;
 643
 644     // pDoc is private in condition, use an accessor and a long winded name.
 645     ScDocument* pDocument = GetDocument();
 646     if( NULL == pDocument )
 647         return false;
 648
 649     ScFormulaCell aValidationSrc( pDocument, rPos, &rTokArr,
 650            formula::FormulaGrammar::GRAM_DEFAULT, MM_FORMULA);
 651
 652     // Make sure the formula gets interpreted and a result is delivered,
 653     // regardless of the AutoCalc setting.
 654     aValidationSrc.Interpret();
 655
 656     ScMatrixRef xMatRef;
 657     const ScMatrix *pValues = aValidationSrc.GetMatrix();
 658     if (!pValues)
 659     {
 660         // The somewhat nasty case of either an error occured, or the
 661         // dereferenced value of a single cell reference or an immediate result
 662         // is stored as a single value.
 663
 664         // Use an interim matrix to create the TypedStrData below.
 665         xMatRef = new ScMatrix(1,1);
 666
 667         USHORT nErrCode = aValidationSrc.GetErrCode();
 668         if (nErrCode)
 669         {
 670             /* TODO : to use later in an alert box?
 671              * String rStrResult = "...";
 672              * rStrResult += ScGlobal::GetLongErrorString(nErrCode);
 673              */
 674
 675             xMatRef->PutError( nErrCode, 0);
 676             bOk = false;
 677         }
 678         else if (aValidationSrc.HasValueData())
 679             xMatRef->PutDouble( aValidationSrc.GetValue(), 0);
 680         else
 681         {
 682             String aStr;
 683             aValidationSrc.GetString( aStr);
 684             xMatRef->PutString( aStr, 0);
 685         }
 686
 687         pValues = xMatRef;
 688     }
 689
 690     // which index matched.  We will want it eventually to pre-select that item.
 691     rMatch = -1;
 692
 693     SvNumberFormatter* pFormatter = GetDocument()->GetFormatTable();
 694
 695     bool    bSortList = (mnListType == ValidListType::SORTEDASCENDING);
 696     SCSIZE  nCol, nRow, nCols, nRows, n = 0;
 697     pValues->GetDimensions( nCols, nRows );
 698
 699     BOOL bRef = FALSE;
 700     ScRange aRange;
 701
 702     ScTokenArray* pArr = (ScTokenArray*) &rTokArr;
 703     pArr->Reset();
 704     ScToken* t = NULL;
 705     if (pArr->GetLen() == 1 && (t = static_cast<ScToken*>(pArr->GetNextReferenceOrName())) != NULL)
 706     {
 707         if (t->GetOpCode() == ocDBArea)
 708         {
 709             if( ScDBData* pDBData = pDocument->GetDBCollection()->FindIndex( t->GetIndex() ) )
 710             {
 711                 pDBData->GetArea(aRange);
 712                 bRef = TRUE;
 713             }
 714         }
 715         else if (t->GetOpCode() == ocName)
 716         {
 717             ScRangeData* pName = pDocument->GetRangeName()->FindIndex( t->GetIndex() );
 718             if (pName && pName->IsReference(aRange))
 719             {
 720                 bRef = TRUE;
 721             }
 722         }
 723         else if (t->GetType() != svIndex)
 724         {
 725             t->CalcAbsIfRel(rPos);
 726             if (pArr->IsValidReference(aRange))
 727             {
 728                 bRef = TRUE;
 729             }
 730         }
 731     }
 732
 733     /* XL artificially limits things to a single col or row in the UI but does
 734      * not list the constraint in MOOXml. If a defined name or INDIRECT
 735      * resulting in 1D is entered in the UI and the definition later modified
 736      * to 2D, it is evaluated fine and also stored and loaded.  Lets get ahead
 737      * of the curve and support 2d. In XL, values are listed row-wise, do the
 738      * same. */
 739     for( nRow = 0; nRow < nRows ; nRow++ )
 740     {
 741         for( nCol = 0; nCol < nCols ; nCol++ )
 742         {
 743             ScTokenArray         aCondTokArr;
 744             TypedStrData*        pEntry = NULL;
 745             ScMatValType         nMatValType;
 746             String               aValStr;
 747             const ScMatrixValue* pMatVal = pValues->Get( nCol, nRow, nMatValType);
 748
 749             // strings and empties
 750             if( NULL == pMatVal || ScMatrix::IsNonValueType( nMatValType ) )
 751             {
 752                 if( NULL != pMatVal )
 753                     aValStr = pMatVal->GetString();
 754
 755                 if( NULL != pStrings )
 756                     pEntry = new TypedStrData( aValStr, 0.0, SC_STRTYPE_STANDARD);
 757
 758                 if( pCell && rMatch < 0 )
 759                     aCondTokArr.AddString( aValStr );
 760             }
 761             else
 762             {
 763                 USHORT nErr = pMatVal->GetError();
 764
 765                 if( 0 != nErr )
 766                 {
 767                     aValStr = ScGlobal::GetErrorString( nErr );
 768                 }
 769                 else
 770                 {
 771                     // FIXME FIXME FIXME
 772                     // Feature regression.  Date formats are lost passing through the matrix
 773                     //pFormatter->GetInputLineString( pMatVal->fVal, 0, aValStr );
 774                     //For external reference and a formula that results in an area or array, date formats are still lost.
 775                     if ( bRef )
 776                     {
 777                         pDocument->GetInputString((SCCOL)(nCol+aRange.aStart.Col()),
 778                             (SCROW)(nRow+aRange.aStart.Row()), aRange.aStart.Tab() , aValStr);
 779                     }
 780                     else
 781                         pFormatter->GetInputLineString( pMatVal->fVal, 0, aValStr );
 782                 }
 783
 784                 if( pCell && rMatch < 0 )
 785                 {
 786                     // I am not sure errors will work here, but a user can no
 787                     // manually enter an error yet so the point is somewhat moot.
 788                     aCondTokArr.AddDouble( pMatVal->fVal );
 789                 }
 790                 if( NULL != pStrings )
 791                     pEntry = new TypedStrData( aValStr, pMatVal->fVal, SC_STRTYPE_VALUE);
 792             }
 793
 794             if( rMatch < 0 && NULL != pCell && IsEqualToTokenArray( pCell, rPos, aCondTokArr ) )
 795             {
 796                 rMatch = n;
 797                 // short circuit on the first match if not filling the list
 798                 if( NULL == pStrings )
 799                     return true;
 800             }
 801
 802             if( NULL != pEntry )
 803             {
 804                 lclInsertStringToCollection( *pStrings, pEntry, bSortList );
 805                 n++;
 806             }
 807         }
 808     }
 809
 810     // In case of no match needed and an error occurred, return that error
 811     // entry as valid instead of silently failing.
 812     return bOk || NULL == pCell;
 813 }
 814
 815 bool ScValidationData::FillSelectionList( TypedScStrCollection& rStrColl, const ScAddress& rPos ) const
 816 {
 817     bool bOk = false;
 818
 819     if( HasSelectionList() )
 820     {
 821         ::std::auto_ptr< ScTokenArray > pTokArr( CreateTokenArry( 0 ) );
 822
 823         // *** try if formula is a string list ***
 824
 825         bool bSortList = (mnListType == ValidListType::SORTEDASCENDING);
 826         UINT32 nFormat = lclGetCellFormat( *GetDocument(), rPos );
 827         ScStringTokenIterator aIt( *pTokArr );
 828         for( const String* pString = aIt.First(); pString && aIt.Ok(); pString = aIt.Next() )
 829         {
 830             double fValue;
 831             bool bIsValue = GetDocument()->GetFormatTable()->IsNumberFormat( *pString, nFormat, fValue );
 832             TypedStrData* pData = new TypedStrData( *pString, fValue, bIsValue ? SC_STRTYPE_VALUE : SC_STRTYPE_STANDARD );
 833             lclInsertStringToCollection( rStrColl, pData, bSortList );
 834         }
 835         bOk = aIt.Ok();
 836
 837         // *** if not a string list, try if formula results in a cell range or
 838         // anything else we recognize as valid ***
 839
 840         if (!bOk)
 841         {
 842             int nMatch;
 843             bOk = GetSelectionFromFormula( &rStrColl, NULL, rPos, *pTokArr, nMatch );
 844         }
 845     }
 846
 847     return bOk;
 848 }
 849
 850 // ----------------------------------------------------------------------------
 851
 852 bool ScValidationData::IsEqualToTokenArray( ScBaseCell* pCell, const ScAddress& rPos, const ScTokenArray& rTokArr ) const
 853 {
 854     // create a condition entry that tests on equality and set the passed token array
 855     ScConditionEntry aCondEntry( SC_COND_EQUAL, &rTokArr, NULL, GetDocument(), rPos );
 856     return aCondEntry.IsCellValid( pCell, rPos );
 857 }
 858
 859 bool ScValidationData::IsListValid( ScBaseCell* pCell, const ScAddress& rPos ) const
 860 {
 861     bool bIsValid = false;
 862
 863     /*  Compare input cell with all supported tokens from the formula.
 864         Currently a formula may contain:
 865         1)  A list of strings (at least one string).
 866         2)  A single cell or range reference.
 867         3)  A single defined name (must contain a cell/range reference, another
 868             name, or DB range, or a formula resulting in a cell/range reference
 869             or matrix/array).
 870         4)  A single database range.
 871         5)  A formula resulting in a cell/range reference or matrix/array.
 872     */
 873
 874     ::std::auto_ptr< ScTokenArray > pTokArr( CreateTokenArry( 0 ) );
 875
 876     // *** try if formula is a string list ***
 877
 878     UINT32 nFormat = lclGetCellFormat( *GetDocument(), rPos );
 879     ScStringTokenIterator aIt( *pTokArr );
 880     for( const String* pString = aIt.First(); pString && aIt.Ok(); pString = aIt.Next() )
 881     {
 882         /*  Do not break the loop, if a valid string has been found.
 883             This is to find invalid tokens following in the formula. */
 884         if( !bIsValid )
 885         {
 886             // create a formula containing a single string or number
 887             ScTokenArray aCondTokArr;
 888             double fValue;
 889             if( GetDocument()->GetFormatTable()->IsNumberFormat( *pString, nFormat, fValue ) )
 890                 aCondTokArr.AddDouble( fValue );
 891             else
 892                 aCondTokArr.AddString( *pString );
 893
 894             bIsValid = IsEqualToTokenArray( pCell, rPos, aCondTokArr );
 895         }
 896     }
 897
 898     if( !aIt.Ok() )
 899         bIsValid = false;
 900
 901     // *** if not a string list, try if formula results in a cell range or
 902     // anything else we recognize as valid ***
 903
 904     if (!bIsValid)
 905     {
 906         int nMatch;
 907         bIsValid = GetSelectionFromFormula( NULL, pCell, rPos, *pTokArr, nMatch );
 908         bIsValid = bIsValid && nMatch >= 0;
 909     }
 910
 911     return bIsValid;
 912 }
 913
 914 // ============================================================================
 915 // ============================================================================
 916
 917 ScValidationDataList::ScValidationDataList(const ScValidationDataList& rList) :
 918     ScValidationEntries_Impl()
 919 {
 920     //  fuer Ref-Undo - echte Kopie mit neuen Tokens!
 921
 922     USHORT nCount = rList.Count();
 923
 924     for (USHORT i=0; i<nCount; i++)
 925         InsertNew( rList[i]->Clone() );
 926
 927     //!         sortierte Eintraege aus rList schneller einfuegen ???
 928 }
 929
 930 ScValidationDataList::ScValidationDataList(ScDocument* pNewDoc,
 931                                             const ScValidationDataList& rList)
 932 {
 933     //  fuer neues Dokument - echte Kopie mit neuen Tokens!
 934
 935     USHORT nCount = rList.Count();
 936
 937     for (USHORT i=0; i<nCount; i++)
 938         InsertNew( rList[i]->Clone(pNewDoc) );
 939
 940     //!         sortierte Eintraege aus rList schneller einfuegen ???
 941 }
 942
 943 ScValidationData* ScValidationDataList::GetData( sal_uInt32 nKey )
 944 {
 945     //! binaer suchen
 946
 947     USHORT nCount = Count();
 948     for (USHORT i=0; i<nCount; i++)
 949         if ((*this)[i]->GetKey() == nKey)
 950             return (*this)[i];
 951
 952     DBG_ERROR("ScValidationDataList: Eintrag nicht gefunden");
 953     return NULL;
 954 }
 955
 956 void ScValidationDataList::CompileXML()
 957 {
 958     USHORT nCount = Count();
 959     for (USHORT i=0; i<nCount; i++)
 960         (*this)[i]->CompileXML();
 961 }
 962
 963 void ScValidationDataList::UpdateReference( UpdateRefMode eUpdateRefMode,
 964                                 const ScRange& rRange, SCsCOL nDx, SCsROW nDy, SCsTAB nDz )
 965 {
 966     USHORT nCount = Count();
 967     for (USHORT i=0; i<nCount; i++)
 968         (*this)[i]->UpdateReference( eUpdateRefMode, rRange, nDx, nDy, nDz);
 969 }
 970
 971 void ScValidationDataList::UpdateMoveTab( SCTAB nOldPos, SCTAB nNewPos )
 972 {
 973     USHORT nCount = Count();
 974     for (USHORT i=0; i<nCount; i++)
 975         (*this)[i]->UpdateMoveTab( nOldPos, nNewPos );
 976 }
 977
 978 bool ScValidationDataList::MarkUsedExternalReferences() const
 979 {
 980     bool bAllMarked = false;
 981     USHORT nCount = Count();
 982     for (USHORT i=0; !bAllMarked && i<nCount; i++)
 983         bAllMarked = (*this)[i]->MarkUsedExternalReferences();
 984     return bAllMarked;
 985 }
 986
 987 BOOL ScValidationDataList::operator==( const ScValidationDataList& r ) const
 988 {
 989     // fuer Ref-Undo - interne Variablen werden nicht verglichen
 990
 991     USHORT nCount = Count();
 992     BOOL bEqual = ( nCount == r.Count() );
 993     for (USHORT i=0; i<nCount && bEqual; i++)                   // Eintraege sind sortiert
 994         if ( !(*this)[i]->EqualEntries(*r[i]) )                 // Eintraege unterschiedlich ?
 995             bEqual = FALSE;
 996
 997     return bEqual;
 998 }
 999