sc/source/core/data/validat.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  *
   9  * This file incorporates work covered by the following license notice:
  10  *
  11  *   Licensed to the Apache Software Foundation (ASF) under one or more
  12  *   contributor license agreements. See the NOTICE file distributed
  13  *   with this work for additional information regarding copyright
  14  *   ownership. The ASF licenses this file to you under the Apache
  15  *   License, Version 2.0 (the "License"); you may not use this file
  16  *   except in compliance with the License. You may obtain a copy of
  17  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  18  */
  19
  20 #include <config_features.h>
  21
  22 #include <validat.hxx>
  23 #include <com/sun/star/sheet/TableValidationVisibility.hpp>
  24
  25 #include <sfx2/app.hxx>
  26 #include <sfx2/viewsh.hxx>
  27 #include <basic/sbmeth.hxx>
  28 #include <basic/sbmod.hxx>
  29 #include <basic/sbstar.hxx>
  30 #include <basic/sberrors.hxx>
  31
  32 #include <basic/sbx.hxx>
  33 #include <svl/numformat.hxx>
  34 #include <svl/sharedstringpool.hxx>
  35 #include <vcl/svapp.hxx>
  36 #include <vcl/weld.hxx>
  37 #include <rtl/math.hxx>
  38 #include <osl/diagnose.h>
  39
  40 #include <document.hxx>
  41 #include <docsh.hxx>
  42 #include <formulacell.hxx>
  43 #include <patattr.hxx>
  44 #include <globstr.hrc>
  45 #include <scresid.hxx>
  46 #include <rangenam.hxx>
  47 #include <dbdata.hxx>
  48 #include <typedstrdata.hxx>
  49 #include <editutil.hxx>
  50 #include <tokenarray.hxx>
  51 #include <scmatrix.hxx>
  52 #include <cellvalue.hxx>
  53 #include <simpleformulacalc.hxx>
  54
  55 #include <math.h>
  56 #include <memory>
  57
  58 using namespace formula;
  59
  60 //  Entries for validation (with only one condition)
  61
  62 ScValidationData::ScValidationData( ScValidationMode eMode, ScConditionMode eOper,
  63                                     const OUString& rExpr1, const OUString& rExpr2,
  64                                     ScDocument& rDocument, const ScAddress& rPos,
  65                                     const OUString& rExprNmsp1, const OUString& rExprNmsp2,
  66                                     FormulaGrammar::Grammar eGrammar1,
  67                                     FormulaGrammar::Grammar eGrammar2 )
  68     : ScConditionEntry( eOper, rExpr1, rExpr2, rDocument, rPos, rExprNmsp1,
  69                         rExprNmsp2, eGrammar1, eGrammar2 )
  70     , nKey( 0 )
  71     , eDataMode( eMode )
  72     , bShowInput(false)
  73     , bShowError(false)
  74     , eErrorStyle( SC_VALERR_STOP )
  75     , mnListType( css::sheet::TableValidationVisibility::UNSORTED )
  76 {
  77 }
  78
  79 ScValidationData::ScValidationData( ScValidationMode eMode, ScConditionMode eOper,
  80                                     const ScTokenArray* pArr1, const ScTokenArray* pArr2,
  81                                     ScDocument& rDocument, const ScAddress& rPos )
  82     : ScConditionEntry( eOper, pArr1, pArr2, rDocument, rPos )
  83     , nKey( 0 )
  84     , eDataMode( eMode )
  85     , bShowInput(false)
  86     , bShowError(false)
  87     , eErrorStyle( SC_VALERR_STOP )
  88     , mnListType( css::sheet::TableValidationVisibility::UNSORTED )
  89 {
  90 }
  91
  92 ScValidationData::ScValidationData( const ScValidationData& r )
  93     : ScConditionEntry( r )
  94     , nKey( r.nKey )
  95     , eDataMode( r.eDataMode )
  96     , bShowInput( r.bShowInput )
  97     , bShowError( r.bShowError )
  98     , eErrorStyle( r.eErrorStyle )
  99     , mnListType( r.mnListType )
 100     , aInputTitle( r.aInputTitle )
 101     , aInputMessage( r.aInputMessage )
 102     , aErrorTitle( r.aErrorTitle )
 103     , aErrorMessage( r.aErrorMessage )
 104 {
 105     //  Formulae copied by RefCount
 106 }
 107
 108 ScValidationData::ScValidationData( ScDocument& rDocument, const ScValidationData& r )
 109     : ScConditionEntry( rDocument, r )
 110     , nKey( r.nKey )
 111     , eDataMode( r.eDataMode )
 112     , bShowInput( r.bShowInput )
 113     , bShowError( r.bShowError )
 114     , eErrorStyle( r.eErrorStyle )
 115     , mnListType( r.mnListType )
 116     , aInputTitle( r.aInputTitle )
 117     , aInputMessage( r.aInputMessage )
 118     , aErrorTitle( r.aErrorTitle )
 119     , aErrorMessage( r.aErrorMessage )
 120 {
 121     //  Formulae really copied
 122 }
 123
 124 ScValidationData::~ScValidationData()
 125 {
 126 }
 127
 128 bool ScValidationData::IsEmpty() const
 129 {
 130     ScValidationData aDefault( SC_VALID_ANY, ScConditionMode::Equal, u""_ustr, u""_ustr, *GetDocument(), ScAddress() );
 131     return EqualEntries( aDefault );
 132 }
 133
 134 bool ScValidationData::EqualEntries( const ScValidationData& r ) const
 135 {
 136         //  test same parameters (excluding Key)
 137
 138     return ScConditionEntry::operator==(r) &&
 139             eDataMode       == r.eDataMode &&
 140             bShowInput      == r.bShowInput &&
 141             bShowError      == r.bShowError &&
 142             eErrorStyle     == r.eErrorStyle &&
 143             mnListType      == r.mnListType &&
 144             aInputTitle     == r.aInputTitle &&
 145             aInputMessage   == r.aInputMessage &&
 146             aErrorTitle     == r.aErrorTitle &&
 147             aErrorMessage   == r.aErrorMessage;
 148 }
 149
 150 void ScValidationData::ResetInput()
 151 {
 152     bShowInput = false;
 153 }
 154
 155 void ScValidationData::ResetError()
 156 {
 157     bShowError = false;
 158 }
 159
 160 void ScValidationData::SetInput( const OUString& rTitle, const OUString& rMsg )
 161 {
 162     bShowInput = true;
 163     aInputTitle = rTitle;
 164     aInputMessage = rMsg;
 165 }
 166
 167 void ScValidationData::SetError( const OUString& rTitle, const OUString& rMsg,
 168                                     ScValidErrorStyle eStyle )
 169 {
 170     bShowError = true;
 171     eErrorStyle = eStyle;
 172     aErrorTitle = rTitle;
 173     aErrorMessage = rMsg;
 174 }
 175
 176 bool ScValidationData::GetErrMsg( OUString& rTitle, OUString& rMsg,
 177                                     ScValidErrorStyle& rStyle ) const
 178 {
 179     rTitle = aErrorTitle;
 180     rMsg   = aErrorMessage;
 181     rStyle = eErrorStyle;
 182     return bShowError;
 183 }
 184
 185 bool ScValidationData::DoScript( const ScAddress& rPos, const OUString& rInput,
 186                                 ScFormulaCell* pCell, weld::Window* pParent ) const
 187 {
 188     ScDocument* pDocument = GetDocument();
 189     ScDocShell* pDocSh = pDocument->GetDocumentShell();
 190     if ( !pDocSh )
 191         return false;
 192
 193     bool bScriptReturnedFalse = false;  // default: do not abort
 194
 195     //  1) entered or calculated value
 196     css::uno::Any aParam0(rInput);
 197     if ( pCell )                // if cell exists, call interpret
 198     {
 199         if ( pCell->IsValue() )
 200             aParam0 <<= pCell->GetValue();
 201         else
 202             aParam0 <<= pCell->GetString().getString();
 203     }
 204
 205     //  2) Position of the cell
 206     OUString aPosStr(rPos.Format(ScRefFlags::VALID | ScRefFlags::TAB_3D, pDocument, pDocument->GetAddressConvention()));
 207
 208     // Set up parameters
 209     css::uno::Sequence< css::uno::Any > aParams{ aParam0, css::uno::Any(aPosStr) };
 210
 211     //  use link-update flag to prevent closing the document
 212     //  while the macro is running
 213     bool bWasInLinkUpdate = pDocument->IsInLinkUpdate();
 214     if ( !bWasInLinkUpdate )
 215         pDocument->SetInLinkUpdate( true );
 216
 217     if ( pCell )
 218         pDocument->LockTable( rPos.Tab() );
 219
 220     css::uno::Any aRet;
 221     css::uno::Sequence< sal_Int16 > aOutArgsIndex;
 222     css::uno::Sequence< css::uno::Any > aOutArgs;
 223
 224     ErrCode eRet = pDocSh->CallXScript(
 225         aErrorTitle, aParams, aRet, aOutArgsIndex, aOutArgs );
 226
 227     if ( pCell )
 228         pDocument->UnlockTable( rPos.Tab() );
 229
 230     if ( !bWasInLinkUpdate )
 231         pDocument->SetInLinkUpdate( false );
 232
 233     // Check the return value from the script
 234     // The contents of the cell get reset if the script returns false
 235     bool bTmp = false;
 236     if ( eRet == ERRCODE_NONE &&
 237              aRet.getValueType() == cppu::UnoType<bool>::get() &&
 238              ( aRet >>= bTmp ) &&
 239              !bTmp )
 240     {
 241         bScriptReturnedFalse =  true;
 242     }
 243
 244     if ( eRet == ERRCODE_BASIC_METHOD_NOT_FOUND && !pCell )
 245     // Macro not found (only with input)
 246     {
 247         //TODO: different error message, if found, but not bAllowed ??
 248         std::unique_ptr<weld::MessageDialog> xBox(Application::CreateMessageDialog(pParent,
 249                                                   VclMessageType::Warning, VclButtonsType::Ok,
 250                                                   ScResId(STR_VALID_MACRONOTFOUND)));
 251         xBox->run();
 252     }
 253
 254     return bScriptReturnedFalse;
 255 }
 256
 257     // true -> abort
 258
 259 bool ScValidationData::DoMacro( const ScAddress& rPos, const OUString& rInput,
 260                                 ScFormulaCell* pCell, weld::Window* pParent ) const
 261 {
 262     if ( SfxApplication::IsXScriptURL( aErrorTitle ) )
 263     {
 264         return DoScript( rPos, rInput, pCell, pParent );
 265     }
 266
 267     ScDocument* pDocument = GetDocument();
 268     ScDocShell* pDocSh = pDocument->GetDocumentShell();
 269     if ( !pDocSh )
 270         return false;
 271
 272     bool bDone = false;
 273     bool bRet = false;                      // default: do not abort
 274
 275     //  If the Doc was loaded during a Basic-Calls,
 276     //  the Sbx-object may not be created (?)
 277 //  pDocSh->GetSbxObject();
 278
 279 #if HAVE_FEATURE_SCRIPTING
 280     //  no security check ahead (only CheckMacroWarn), that happens in CallBasic
 281
 282     //  Function search by their simple name,
 283     //  then assemble aBasicStr, aMacroStr for SfxObjectShell::CallBasic
 284
 285     StarBASIC* pRoot = pDocSh->GetBasic();
 286     SbxVariable* pVar = pRoot->Find( aErrorTitle, SbxClassType::Method );
 287     if (SbMethod* pMethod = dynamic_cast<SbMethod*>(pVar))
 288     {
 289         SbModule* pModule = pMethod->GetModule();
 290         SbxObject* pObject = pModule->GetParent();
 291         OUString aMacroStr(
 292             pObject->GetName() + "." + pModule->GetName() + "." + pMethod->GetName());
 293         OUString aBasicStr;
 294
 295         //  the distinction between document- and app-basic has to be done
 296         //  by checking the parent (as in ScInterpreter::ScMacro), not by looping
 297         //  over all open documents, because this may be called from within loading,
 298         //  when SfxObjectShell::GetFirst/GetNext won't find the document.
 299
 300         if ( pObject->GetParent() )
 301             aBasicStr = pObject->GetParent()->GetName();    // Basic of document
 302         else
 303             aBasicStr = SfxGetpApp()->GetName();            // Basic of application
 304
 305         //  Parameter for Macro
 306         SbxArrayRef refPar = new SbxArray;
 307
 308         //  1) entered or calculated value
 309         OUString aValStr = rInput;
 310         double nValue = 0.0;
 311         bool bIsValue = false;
 312         if ( pCell )                // if cell set, called from interpret
 313         {
 314             bIsValue = pCell->IsValue();
 315             if ( bIsValue )
 316                 nValue  = pCell->GetValue();
 317             else
 318                 aValStr = pCell->GetString().getString();
 319         }
 320         if ( bIsValue )
 321             refPar->Get(1)->PutDouble(nValue);
 322         else
 323             refPar->Get(1)->PutString(aValStr);
 324
 325         //  2) Position of the cell
 326         OUString aPosStr(rPos.Format(ScRefFlags::VALID | ScRefFlags::TAB_3D, pDocument, pDocument->GetAddressConvention()));
 327         refPar->Get(2)->PutString(aPosStr);
 328
 329         //  use link-update flag to prevent closing the document
 330         //  while the macro is running
 331         bool bWasInLinkUpdate = pDocument->IsInLinkUpdate();
 332         if ( !bWasInLinkUpdate )
 333             pDocument->SetInLinkUpdate( true );
 334
 335         if ( pCell )
 336             pDocument->LockTable( rPos.Tab() );
 337         SbxVariableRef refRes = new SbxVariable;
 338         ErrCode eRet = pDocSh->CallBasic( aMacroStr, aBasicStr, refPar.get(), refRes.get() );
 339         if ( pCell )
 340             pDocument->UnlockTable( rPos.Tab() );
 341
 342         if ( !bWasInLinkUpdate )
 343             pDocument->SetInLinkUpdate( false );
 344
 345         //  Interrupt input if Basic macro returns false
 346         if ( eRet == ERRCODE_NONE && refRes->GetType() == SbxBOOL && !refRes->GetBool() )
 347             bRet = true;
 348         bDone = true;
 349     }
 350 #endif
 351     if ( !bDone && !pCell )         // Macro not found (only with input)
 352     {
 353         //TODO: different error message, if found, but not bAllowed ??
 354         std::unique_ptr<weld::MessageDialog> xBox(Application::CreateMessageDialog(pParent,
 355                                                   VclMessageType::Warning, VclButtonsType::Ok,
 356                                                   ScResId(STR_VALID_MACRONOTFOUND)));
 357         xBox->run();
 358     }
 359
 360     return bRet;
 361 }
 362
 363 void ScValidationData::DoCalcError( ScFormulaCell* pCell ) const
 364 {
 365     if ( eErrorStyle == SC_VALERR_MACRO )
 366         DoMacro( pCell->aPos, OUString(), pCell, nullptr );
 367 }
 368
 369 IMPL_STATIC_LINK_NOARG(ScValidationData, InstallLOKNotifierHdl, void*, vcl::ILibreOfficeKitNotifier*)
 370 {
 371     return SfxViewShell::Current();
 372 }
 373
 374     // true -> abort
 375
 376 bool ScValidationData::DoError(weld::Window* pParent, const OUString& rInput,
 377                                const ScAddress& rPos) const
 378 {
 379     if ( eErrorStyle == SC_VALERR_MACRO )
 380         return DoMacro(rPos, rInput, nullptr, pParent);
 381
 382     if (!bShowError)
 383         return true;
 384
 385     //  Output error message
 386
 387     OUString aTitle = aErrorTitle;
 388     if (aTitle.isEmpty())
 389         aTitle = ScResId( STR_MSSG_DOSUBTOTALS_0 );  // application title
 390     OUString aMessage = aErrorMessage;
 391     if (aMessage.isEmpty())
 392         aMessage = ScResId( STR_VALID_DEFERROR );
 393
 394     VclButtonsType eStyle = VclButtonsType::Ok;
 395     VclMessageType eType = VclMessageType::Error;
 396     switch (eErrorStyle)
 397     {
 398         case SC_VALERR_INFO:
 399             eType = VclMessageType::Info;
 400             eStyle = VclButtonsType::OkCancel;
 401             break;
 402         case SC_VALERR_WARNING:
 403             eType = VclMessageType::Warning;
 404             eStyle = VclButtonsType::OkCancel;
 405             break;
 406         default:
 407             break;
 408     }
 409
 410     std::unique_ptr<weld::MessageDialog> xBox(Application::CreateMessageDialog(pParent, eType,
 411                                               eStyle, aMessage, SfxViewShell::Current()));
 412     xBox->set_title(aTitle);
 413     xBox->SetInstallLOKNotifierHdl(LINK(nullptr, ScValidationData, InstallLOKNotifierHdl));
 414
 415     switch (eErrorStyle)
 416     {
 417         case SC_VALERR_INFO:
 418             xBox->set_default_response(RET_OK);
 419             break;
 420         case SC_VALERR_WARNING:
 421             xBox->set_default_response(RET_CANCEL);
 422             break;
 423         default:
 424             break;
 425     }
 426
 427     short nRet = xBox->run();
 428
 429     return ( eErrorStyle == SC_VALERR_STOP || nRet == RET_CANCEL );
 430 }
 431
 432 bool ScValidationData::IsDataValidCustom(
 433         const OUString& rTest,
 434         const ScPatternAttr& rPattern,
 435         const ScAddress& rPos,
 436         const CustomValidationPrivateAccess& ) const
 437 {
 438     OSL_ENSURE(GetDataMode() == SC_VALID_CUSTOM,
 439             "ScValidationData::IsDataValidCustom invoked for a non-custom validation");
 440
 441     if (rTest.isEmpty())              // check whether empty cells are allowed
 442         return IsIgnoreBlank();
 443
 444     SvNumberFormatter* pFormatter = nullptr;
 445     sal_uInt32 nFormat = 0;
 446     double nVal = 0.0;
 447     OUString rStrResult = u""_ustr;
 448     bool bIsVal = false;
 449
 450     if (rTest[0] == '=')
 451     {
 452         if (!isFormulaResultsValidatable(rTest, rPos, pFormatter, rStrResult, nVal, nFormat, bIsVal))
 453             return false;
 454
 455         // check whether empty cells are allowed
 456         if (rStrResult.isEmpty())
 457             return IsIgnoreBlank();
 458     }
 459     else
 460     {
 461         pFormatter = GetDocument()->GetFormatTable();
 462
 463         // get the value if any
 464         nFormat = rPattern.GetNumberFormat(pFormatter);
 465         bIsVal = pFormatter->IsNumberFormat(rTest, nFormat, nVal);
 466         rStrResult = rTest;
 467     }
 468
 469     ScRefCellValue aTmpCell;
 470     svl::SharedString aSS;
 471     if (bIsVal)
 472     {
 473         aTmpCell = ScRefCellValue(nVal);
 474     }
 475     else
 476     {
 477         aSS = mpDoc->GetSharedStringPool().intern(rStrResult);
 478         aTmpCell = ScRefCellValue(&aSS);
 479     }
 480
 481     ScCellValue aOriginalCellValue(ScRefCellValue(*GetDocument(), rPos));
 482
 483     aTmpCell.commit(*GetDocument(), rPos);
 484     bool bRet = IsCellValid(aTmpCell, rPos);
 485     aOriginalCellValue.commit(*GetDocument(), rPos);
 486
 487     return bRet;
 488 }
 489
 490 /** To test numeric data text length in IsDataValidTextLen().
 491
 492     If mpFormatter is not set, it is obtained from the document and the format
 493     key is determined from the cell position's attribute pattern.
 494  */
 495 struct ScValidationDataIsNumeric
 496 {
 497     SvNumberFormatter*  mpFormatter;
 498     double              mfVal;
 499     sal_uInt32          mnFormat;
 500
 501     ScValidationDataIsNumeric( double fVal, SvNumberFormatter* pFormatter = nullptr, sal_uInt32 nFormat = 0 )
 502         : mpFormatter(pFormatter), mfVal(fVal), mnFormat(nFormat)
 503     {
 504     }
 505
 506     void init( const ScDocument& rDoc, const ScAddress& rPos )
 507     {
 508         const ScPatternAttr* pPattern = rDoc.GetPattern( rPos.Col(), rPos.Row(), rPos.Tab());
 509         mpFormatter = rDoc.GetFormatTable();
 510         mnFormat = pPattern->GetNumberFormat( mpFormatter);
 511     }
 512 };
 513
 514 bool ScValidationData::IsDataValidTextLen( std::u16string_view rTest, const ScAddress& rPos,
 515         ScValidationDataIsNumeric* pDataNumeric ) const
 516 {
 517     sal_Int32 nLen;
 518     if (!pDataNumeric)
 519         nLen = rTest.size();
 520     else
 521     {
 522         if (!pDataNumeric->mpFormatter)
 523             pDataNumeric->init( *GetDocument(), rPos);
 524
 525         // For numeric values use the resulting input line string to
 526         // determine length, otherwise an once accepted value maybe could
 527         // not be edited again, for example abbreviated dates or leading
 528         // zeros or trailing zeros after decimal separator change length.
 529         OUString aStr = pDataNumeric->mpFormatter->GetInputLineString( pDataNumeric->mfVal, pDataNumeric->mnFormat);
 530         nLen = aStr.getLength();
 531     }
 532     ScRefCellValue aTmpCell( static_cast<double>(nLen));
 533     return IsCellValid( aTmpCell, rPos);
 534 }
 535
 536 bool ScValidationData::IsDataValid(
 537     const OUString& rTest, const ScPatternAttr& rPattern, const ScAddress& rPos ) const
 538 {
 539     if ( eDataMode == SC_VALID_ANY ) // check if any cell content is allowed
 540         return true;
 541
 542     if (rTest.isEmpty())              // check whether empty cells are allowed
 543         return IsIgnoreBlank();
 544
 545     SvNumberFormatter* pFormatter = nullptr;
 546     sal_uInt32 nFormat = 0;
 547     double nVal = 0.0;
 548     OUString rStrResult = u""_ustr;
 549     bool bIsVal = false;
 550
 551     if (rTest[0] == '=')
 552     {
 553         if (!isFormulaResultsValidatable(rTest, rPos, pFormatter, rStrResult, nVal, nFormat, bIsVal))
 554             return false;
 555
 556         // check whether empty cells are allowed
 557         if (rStrResult.isEmpty())
 558             return IsIgnoreBlank();
 559     }
 560     else
 561     {
 562         pFormatter = GetDocument()->GetFormatTable();
 563
 564         // get the value if any
 565         nFormat = rPattern.GetNumberFormat(pFormatter);
 566         bIsVal = pFormatter->IsNumberFormat(rTest, nFormat, nVal);
 567         rStrResult = rTest;
 568     }
 569
 570     bool bRet;
 571     if (SC_VALID_TEXTLEN == eDataMode)
 572     {
 573         if (!bIsVal)
 574             bRet = IsDataValidTextLen( rStrResult, rPos, nullptr);
 575         else
 576         {
 577             ScValidationDataIsNumeric aDataNumeric( nVal, pFormatter, nFormat);
 578             bRet = IsDataValidTextLen( rStrResult, rPos, &aDataNumeric);
 579         }
 580     }
 581     else
 582     {
 583         if (bIsVal)
 584         {
 585             ScRefCellValue aTmpCell(nVal);
 586             bRet = IsDataValid(aTmpCell, rPos);
 587         }
 588         else
 589         {
 590             svl::SharedString aSS = mpDoc->GetSharedStringPool().intern( rStrResult );
 591             ScRefCellValue aTmpCell(&aSS);
 592             bRet = IsDataValid(aTmpCell, rPos);
 593         }
 594     }
 595
 596     return bRet;
 597 }
 598
 599 bool ScValidationData::IsDataValid( ScRefCellValue& rCell, const ScAddress& rPos ) const
 600 {
 601     if( eDataMode == SC_VALID_LIST )
 602         return IsListValid(rCell, rPos);
 603
 604     if ( eDataMode == SC_VALID_CUSTOM )
 605         return IsCellValid(rCell, rPos);
 606
 607     double nVal = 0.0;
 608     OUString aString;
 609     bool bIsVal = true;
 610
 611     switch (rCell.getType())
 612     {
 613         case CELLTYPE_VALUE:
 614             nVal = rCell.getDouble();
 615         break;
 616         case CELLTYPE_STRING:
 617             aString = rCell.getSharedString()->getString();
 618             bIsVal = false;
 619         break;
 620         case CELLTYPE_EDIT:
 621             if (rCell.getEditText())
 622                 aString = ScEditUtil::GetString(*rCell.getEditText(), GetDocument());
 623             bIsVal = false;
 624         break;
 625         case CELLTYPE_FORMULA:
 626         {
 627             ScFormulaCell* pFCell = rCell.getFormula();
 628             bIsVal = pFCell->IsValue();
 629             if ( bIsVal )
 630                 nVal  = pFCell->GetValue();
 631             else
 632                 aString = pFCell->GetString().getString();
 633         }
 634         break;
 635         default:                        // Notes, Broadcaster
 636             return IsIgnoreBlank();     // as set
 637     }
 638
 639     bool bOk = true;
 640     switch (eDataMode)
 641     {
 642         // SC_VALID_ANY already above
 643
 644         case SC_VALID_WHOLE:
 645         case SC_VALID_DECIMAL:
 646         case SC_VALID_DATE:         // Date/Time is only formatting
 647         case SC_VALID_TIME:
 648             bOk = bIsVal;
 649             if ( bOk && eDataMode == SC_VALID_WHOLE )
 650                 bOk = ::rtl::math::approxEqual( nVal, floor(nVal+0.5) );        // integers
 651             if ( bOk )
 652                 bOk = IsCellValid(rCell, rPos);
 653             break;
 654
 655         case SC_VALID_TEXTLEN:
 656             if (!bIsVal)
 657                 bOk = IsDataValidTextLen( aString, rPos, nullptr);
 658             else
 659             {
 660                 ScValidationDataIsNumeric aDataNumeric( nVal);
 661                 bOk = IsDataValidTextLen( aString, rPos, &aDataNumeric);
 662             }
 663         break;
 664
 665         default:
 666             OSL_FAIL("not yet done");
 667             break;
 668     }
 669
 670     return bOk;
 671 }
 672
 673 bool ScValidationData::isFormulaResultsValidatable(const OUString& rTest, const ScAddress& rPos, SvNumberFormatter* pFormatter,
 674     OUString& rStrResult, double& nVal, sal_uInt32& nFormat, bool& bIsVal) const
 675 {
 676     std::optional<ScSimpleFormulaCalculator> pFCell(std::in_place, *mpDoc, rPos, rTest, true);
 677     pFCell->SetLimitString(true);
 678
 679     bool bColRowName = pFCell->HasColRowName();
 680     if (bColRowName)
 681     {
 682         // ColRowName from RPN-Code?
 683         if (pFCell->GetCode()->GetCodeLen() <= 1)
 684         {   // ==1: area
 685             // ==0: would be an area if...
 686             OUString aBraced = "(" + rTest + ")";
 687             pFCell.emplace(*mpDoc, rPos, aBraced, true);
 688             pFCell->SetLimitString(true);
 689         }
 690         else
 691             bColRowName = false;
 692     }
 693
 694     FormulaError nErrCode = pFCell->GetErrCode();
 695     if (nErrCode == FormulaError::NONE || pFCell->IsMatrix())
 696     {
 697         pFormatter = mpDoc->GetFormatTable();
 698         const Color* pColor;
 699         if (pFCell->IsMatrix())
 700         {
 701             rStrResult = pFCell->GetString().getString();
 702         }
 703         else if (pFCell->IsValue())
 704         {
 705             nVal = pFCell->GetValue();
 706             nFormat = pFormatter->GetStandardFormat(nVal, 0,
 707                 pFCell->GetFormatType(), ScGlobal::eLnge);
 708             pFormatter->GetOutputString(nVal, nFormat, rStrResult, &pColor);
 709             bIsVal = true;
 710         }
 711         else
 712         {
 713             nFormat = pFormatter->GetStandardFormat(
 714                 pFCell->GetFormatType(), ScGlobal::eLnge);
 715             pFormatter->GetOutputString(pFCell->GetString().getString(), nFormat,
 716                 rStrResult, &pColor);
 717             // Indicate it's a string, so a number string doesn't look numeric.
 718             // Escape embedded quotation marks first by doubling them, as
 719             // usual. Actually the result can be copy-pasted from the result
 720             // box as literal into a formula expression.
 721             rStrResult = "\"" + rStrResult.replaceAll("\"", "\"\"") + "\"";
 722         }
 723
 724         ScRange aTestRange;
 725         if (bColRowName || (aTestRange.Parse(rTest, *mpDoc) & ScRefFlags::VALID))
 726             rStrResult += " ...";
 727         // area
 728
 729         return true;
 730     }
 731     else
 732     {
 733         return false;
 734     }
 735 }
 736
 737 namespace {
 738
 739 /** Token array helper. Iterates over all string tokens.
 740     @descr  The token array must contain separated string tokens only.
 741     @param bSkipEmpty  true = Ignores string tokens with empty strings. */
 742 class ScStringTokenIterator
 743 {
 744 public:
 745     explicit             ScStringTokenIterator( const ScTokenArray& rTokArr ) :
 746         maIter( rTokArr ), mbOk( true ) {}
 747
 748     /** Returns the string of the first string token or NULL on error or empty token array. */
 749     rtl_uString* First();
 750     /** Returns the string of the next string token or NULL on error or end of token array. */
 751     rtl_uString* Next();
 752
 753     /** Returns false, if a wrong token has been found. Does NOT return false on end of token array. */
 754     bool                 Ok() const { return mbOk; }
 755
 756 private:
 757     svl::SharedString maCurString; /// Current string.
 758     FormulaTokenArrayPlainIterator maIter;
 759     bool                        mbOk;           /// true = correct token or end of token array.
 760 };
 761
 762 rtl_uString* ScStringTokenIterator::First()
 763 {
 764     maIter.Reset();
 765     mbOk = true;
 766     return Next();
 767 }
 768
 769 rtl_uString* ScStringTokenIterator::Next()
 770 {
 771     if( !mbOk )
 772         return nullptr;
 773
 774     // seek to next non-separator token
 775     const FormulaToken* pToken = maIter.NextNoSpaces();
 776     while( pToken && (pToken->GetOpCode() == ocSep) )
 777         pToken = maIter.NextNoSpaces();
 778
 779     mbOk = !pToken || (pToken->GetType() == formula::svString);
 780
 781     maCurString = svl::SharedString(); // start with invalid string.
 782     if (mbOk && pToken)
 783         maCurString = pToken->GetString();
 784
 785     // string found but empty -> get next token; otherwise return it
 786     return (maCurString.isValid() && maCurString.isEmpty()) ? Next() : maCurString.getData();
 787 }
 788
 789 /** Returns the number format of the passed cell, or the standard format. */
 790 sal_uInt32 lclGetCellFormat( const ScDocument& rDoc, const ScAddress& rPos )
 791 {
 792     const ScPatternAttr* pPattern = rDoc.GetPattern( rPos.Col(), rPos.Row(), rPos.Tab() );
 793     if( !pPattern )
 794         pPattern = &rDoc.getCellAttributeHelper().getDefaultCellAttribute();
 795     return pPattern->GetNumberFormat( rDoc.GetFormatTable() );
 796 }
 797
 798 } // namespace
 799
 800 bool ScValidationData::HasSelectionList() const
 801 {
 802     return (eDataMode == SC_VALID_LIST) && (mnListType != css::sheet::TableValidationVisibility::INVISIBLE);
 803 }
 804
 805 bool ScValidationData::GetSelectionFromFormula(
 806     std::vector<ScTypedStrData>* pStrings, ScRefCellValue& rCell, const ScAddress& rPos,
 807     const ScTokenArray& rTokArr, int& rMatch) const
 808 {
 809     bool bOk = true;
 810
 811     // pDoc is private in condition, use an accessor and a long winded name.
 812     ScDocument* pDocument = GetDocument();
 813     if( nullptr == pDocument )
 814         return false;
 815
 816     ScFormulaCell aValidationSrc(
 817         *pDocument, rPos, rTokArr, formula::FormulaGrammar::GRAM_DEFAULT, ScMatrixMode::Formula);
 818
 819     // Make sure the formula gets interpreted and a result is delivered,
 820     // regardless of the AutoCalc setting.
 821     aValidationSrc.Interpret();
 822
 823     ScMatrixRef xMatRef;
 824     const ScMatrix *pValues = aValidationSrc.GetMatrix();
 825     if (!pValues)
 826     {
 827         // The somewhat nasty case of either an error occurred, or the
 828         // dereferenced value of a single cell reference or an immediate result
 829         // is stored as a single value.
 830
 831         // Use an interim matrix to create the TypedStrData below.
 832         xMatRef = new ScMatrix(1, 1, 0.0);
 833
 834         FormulaError nErrCode = aValidationSrc.GetErrCode();
 835         if (nErrCode != FormulaError::NONE)
 836         {
 837             /* TODO : to use later in an alert box?
 838              * OUString rStrResult = "...";
 839              * rStrResult += ScGlobal::GetLongErrorString(nErrCode);
 840              */
 841
 842             xMatRef->PutError( nErrCode, 0, 0);
 843             bOk = false;
 844         }
 845         else if (aValidationSrc.IsValue())
 846             xMatRef->PutDouble( aValidationSrc.GetValue(), 0);
 847         else
 848         {
 849             svl::SharedString aStr = aValidationSrc.GetString();
 850             xMatRef->PutString(aStr, 0);
 851         }
 852
 853         pValues = xMatRef.get();
 854     }
 855
 856     // which index matched.  We will want it eventually to pre-select that item.
 857     rMatch = -1;
 858
 859     SvNumberFormatter* pFormatter = GetDocument()->GetFormatTable();
 860     sal_uInt32 nDestFormat = pDocument->GetNumberFormat(rPos.Col(), rPos.Row(), rPos.Tab());
 861
 862     SCSIZE  nCol, nRow, nCols, nRows, n = 0;
 863     pValues->GetDimensions( nCols, nRows );
 864
 865     bool bRef = false;
 866     ScRange aRange;
 867
 868     ScTokenArray* pArr = const_cast<ScTokenArray*>(&rTokArr);
 869     if (pArr->GetLen() == 1)
 870     {
 871         formula::FormulaTokenArrayPlainIterator aIter(*pArr);
 872         formula::FormulaToken* t = aIter.GetNextReferenceOrName();
 873         if (t)
 874         {
 875             OpCode eOpCode = t->GetOpCode();
 876             if (eOpCode == ocDBArea || eOpCode == ocTableRef)
 877             {
 878                 if (const ScDBData* pDBData = pDocument->GetDBCollection()->getNamedDBs().findByIndex(t->GetIndex()))
 879                 {
 880                     pDBData->GetArea(aRange);
 881                     bRef = true;
 882                 }
 883             }
 884             else if (eOpCode == ocName)
 885             {
 886                 const ScRangeData* pName = pDocument->FindRangeNameBySheetAndIndex( t->GetSheet(), t->GetIndex());
 887                 if (pName && pName->IsReference(aRange))
 888                 {
 889                     bRef = true;
 890                 }
 891             }
 892             else if (t->GetType() != svIndex)
 893             {
 894                 if (pArr->IsValidReference(aRange, rPos))
 895                 {
 896                     bRef = true;
 897                 }
 898             }
 899         }
 900     }
 901
 902     bool bHaveEmpty = false;
 903     svl::SharedStringPool& rSPool = pDocument->GetSharedStringPool();
 904
 905     /* XL artificially limits things to a single col or row in the UI but does
 906      * not list the constraint in MOOXml. If a defined name or INDIRECT
 907      * resulting in 1D is entered in the UI and the definition later modified
 908      * to 2D, it is evaluated fine and also stored and loaded. Let's get ahead
 909      * of the curve and support 2d. In XL, values are listed row-wise, do the
 910      * same. */
 911     for( nRow = 0; nRow < nRows ; nRow++ )
 912     {
 913         for( nCol = 0; nCol < nCols ; nCol++ )
 914         {
 915             ScTokenArray         aCondTokArr(*pDocument);
 916             std::unique_ptr<ScTypedStrData> pEntry;
 917             OUString               aValStr;
 918             ScMatrixValue nMatVal = pValues->Get( nCol, nRow);
 919
 920             // strings and empties
 921             if( ScMatrix::IsNonValueType( nMatVal.nType ) )
 922             {
 923                 aValStr = nMatVal.GetString().getString();
 924
 925                 // Do not add multiple empty strings to the validation list,
 926                 // especially not if they'd bloat the tail with a million empty
 927                 // entries for a column range, fdo#61520
 928                 if (aValStr.isEmpty())
 929                 {
 930                     if (bHaveEmpty)
 931                         continue;
 932                     bHaveEmpty = true;
 933                 }
 934
 935                 if( nullptr != pStrings )
 936                     pEntry.reset(new ScTypedStrData(aValStr, 0.0, 0.0, ScTypedStrData::Standard));
 937
 938                 if (!rCell.isEmpty() && rMatch < 0)
 939                     aCondTokArr.AddString(rSPool.intern(aValStr));
 940             }
 941             else
 942             {
 943                 FormulaError nErr = nMatVal.GetError();
 944
 945                 if( FormulaError::NONE != nErr )
 946                 {
 947                     aValStr = ScGlobal::GetErrorString( nErr );
 948                 }
 949                 else
 950                 {
 951                     // FIXME FIXME FIXME
 952                     // Feature regression.  Date formats are lost passing through the matrix
 953                     //pFormatter->GetInputLineString( pMatVal->fVal, 0, aValStr );
 954                     //For external reference and a formula that results in an area or array, date formats are still lost.
 955                     if ( bRef )
 956                     {
 957                         aValStr = pDocument->GetInputString(static_cast<SCCOL>(nCol+aRange.aStart.Col()),
 958                             static_cast<SCROW>(nRow+aRange.aStart.Row()), aRange.aStart.Tab());
 959                     }
 960                     else
 961                     {
 962                         aValStr = pFormatter->GetInputLineString( nMatVal.fVal, nDestFormat );
 963                     }
 964                 }
 965
 966                 if (!rCell.isEmpty() && rMatch < 0)
 967                 {
 968                     // I am not sure errors will work here, but a user can no
 969                     // manually enter an error yet so the point is somewhat moot.
 970                     aCondTokArr.AddDouble( nMatVal.fVal );
 971                 }
 972                 if( nullptr != pStrings )
 973                     pEntry.reset(new ScTypedStrData(aValStr, nMatVal.fVal, nMatVal.fVal, ScTypedStrData::Value));
 974             }
 975
 976             if (rMatch < 0 && !rCell.isEmpty() && IsEqualToTokenArray(rCell, rPos, aCondTokArr))
 977             {
 978                 rMatch = n;
 979                 // short circuit on the first match if not filling the list
 980                 if( nullptr == pStrings )
 981                     return true;
 982             }
 983
 984             if( pEntry )
 985             {
 986                 assert(pStrings);
 987                 pStrings->push_back(*pEntry);
 988                 n++;
 989             }
 990         }
 991     }
 992
 993     // In case of no match needed and an error occurred, return that error
 994     // entry as valid instead of silently failing.
 995     return bOk || rCell.isEmpty();
 996 }
 997
 998 bool ScValidationData::FillSelectionList(std::vector<ScTypedStrData>& rStrColl, const ScAddress& rPos) const
 999 {
1000     bool bOk = false;
1001
1002     if( HasSelectionList() )
1003     {
1004         std::unique_ptr<ScTokenArray> pTokArr( CreateFlatCopiedTokenArray(0) );
1005
1006         // *** try if formula is a string list ***
1007
1008         sal_uInt32 nFormat = lclGetCellFormat( *GetDocument(), rPos );
1009         ScStringTokenIterator aIt( *pTokArr );
1010         for (rtl_uString* pString = aIt.First(); pString && aIt.Ok(); pString = aIt.Next())
1011         {
1012             double fValue;
1013             OUString aStr(pString);
1014             bool bIsValue = GetDocument()->GetFormatTable()->IsNumberFormat(aStr, nFormat, fValue);
1015             rStrColl.emplace_back(
1016                     aStr, fValue, fValue, bIsValue ? ScTypedStrData::Value : ScTypedStrData::Standard);
1017         }
1018         bOk = aIt.Ok();
1019
1020         // *** if not a string list, try if formula results in a cell range or
1021         // anything else we recognize as valid ***
1022
1023         if (!bOk)
1024         {
1025             int nMatch;
1026             ScRefCellValue aEmptyCell;
1027             bOk = GetSelectionFromFormula(&rStrColl, aEmptyCell, rPos, *pTokArr, nMatch);
1028         }
1029     }
1030
1031     return bOk;
1032 }
1033
1034 bool ScValidationData::IsEqualToTokenArray( ScRefCellValue& rCell, const ScAddress& rPos, const ScTokenArray& rTokArr ) const
1035 {
1036     // create a condition entry that tests on equality and set the passed token array
1037     ScConditionEntry aCondEntry( ScConditionMode::Equal, &rTokArr, nullptr, *GetDocument(), rPos );
1038     aCondEntry.SetCaseSensitive(IsCaseSensitive());
1039
1040     return aCondEntry.IsCellValid(rCell, rPos);
1041 }
1042
1043 bool ScValidationData::IsListValid( ScRefCellValue& rCell, const ScAddress& rPos ) const
1044 {
1045     bool bIsValid = false;
1046
1047     /*  Compare input cell with all supported tokens from the formula.
1048         Currently a formula may contain:
1049         1)  A list of strings (at least one string).
1050         2)  A single cell or range reference.
1051         3)  A single defined name (must contain a cell/range reference, another
1052             name, or DB range, or a formula resulting in a cell/range reference
1053             or matrix/array).
1054         4)  A single database range.
1055         5)  A formula resulting in a cell/range reference or matrix/array.
1056     */
1057
1058     std::unique_ptr< ScTokenArray > pTokArr( CreateFlatCopiedTokenArray( 0 ) );
1059
1060     // *** try if formula is a string list ***
1061
1062     svl::SharedStringPool& rSPool = GetDocument()->GetSharedStringPool();
1063     sal_uInt32 nFormat = lclGetCellFormat( *GetDocument(), rPos );
1064     ScStringTokenIterator aIt( *pTokArr );
1065     for (rtl_uString* pString = aIt.First(); pString && aIt.Ok(); pString = aIt.Next())
1066     {
1067         /*  Do not break the loop, if a valid string has been found.
1068             This is to find invalid tokens following in the formula. */
1069         if( !bIsValid )
1070         {
1071             // create a formula containing a single string or number
1072             ScTokenArray aCondTokArr(*GetDocument());
1073             double fValue;
1074             OUString aStr(pString);
1075             if (GetDocument()->GetFormatTable()->IsNumberFormat(aStr, nFormat, fValue))
1076                 aCondTokArr.AddDouble( fValue );
1077             else
1078                 aCondTokArr.AddString(rSPool.intern(aStr));
1079
1080             bIsValid = IsEqualToTokenArray(rCell, rPos, aCondTokArr);
1081         }
1082     }
1083
1084     if( !aIt.Ok() )
1085         bIsValid = false;
1086
1087     // *** if not a string list, try if formula results in a cell range or
1088     // anything else we recognize as valid ***
1089
1090     if (!bIsValid)
1091     {
1092         int nMatch;
1093         bIsValid = GetSelectionFromFormula(nullptr, rCell, rPos, *pTokArr, nMatch);
1094         bIsValid = bIsValid && nMatch >= 0;
1095     }
1096
1097     return bIsValid;
1098 }
1099
1100 ScValidationDataList::ScValidationDataList(const ScValidationDataList& rList)
1101 {
1102     //  for Ref-Undo - real copy with new tokens!
1103
1104     for (const auto& rxItem : rList)
1105     {
1106         InsertNew( std::unique_ptr<ScValidationData>(rxItem->Clone()) );
1107     }
1108
1109     //TODO: faster insert for sorted entries from rList ???
1110 }
1111
1112 ScValidationDataList::ScValidationDataList(ScDocument& rNewDoc,
1113                                             const ScValidationDataList& rList)
1114 {
1115     //  for new documents - real copy with new tokens!
1116
1117     for (const auto& rxItem : rList)
1118     {
1119         InsertNew( std::unique_ptr<ScValidationData>(rxItem->Clone(&rNewDoc)) );
1120     }
1121
1122     //TODO: faster insert for sorted entries from rList ???
1123 }
1124
1125 ScValidationData* ScValidationDataList::GetData( sal_uInt32 nKey )
1126 {
1127     //TODO: binary search
1128
1129     for( iterator it = begin(); it != end(); ++it )
1130         if( (*it)->GetKey() == nKey )
1131             return it->get();
1132
1133     OSL_FAIL("ScValidationDataList: Entry not found");
1134     return nullptr;
1135 }
1136
1137 void ScValidationDataList::CompileXML()
1138 {
1139     for( iterator it = begin(); it != end(); ++it )
1140         (*it)->CompileXML();
1141 }
1142
1143 void ScValidationDataList::UpdateReference( sc::RefUpdateContext& rCxt )
1144 {
1145     for( iterator it = begin(); it != end(); ++it )
1146         (*it)->UpdateReference(rCxt);
1147 }
1148
1149 void ScValidationDataList::UpdateInsertTab( sc::RefUpdateInsertTabContext& rCxt )
1150 {
1151     for (iterator it = begin(); it != end(); ++it)
1152         (*it)->UpdateInsertTab(rCxt);
1153 }
1154
1155 void ScValidationDataList::UpdateDeleteTab( sc::RefUpdateDeleteTabContext& rCxt )
1156 {
1157     for (iterator it = begin(); it != end(); ++it)
1158         (*it)->UpdateDeleteTab(rCxt);
1159 }
1160
1161 void ScValidationDataList::UpdateMoveTab( sc::RefUpdateMoveTabContext& rCxt )
1162 {
1163     for (iterator it = begin(); it != end(); ++it)
1164         (*it)->UpdateMoveTab(rCxt);
1165 }
1166
1167 ScValidationDataList::iterator ScValidationDataList::begin()
1168 {
1169     return maData.begin();
1170 }
1171
1172 ScValidationDataList::const_iterator ScValidationDataList::begin() const
1173 {
1174     return maData.begin();
1175 }
1176
1177 ScValidationDataList::iterator ScValidationDataList::end()
1178 {
1179     return maData.end();
1180 }
1181
1182 ScValidationDataList::const_iterator ScValidationDataList::end() const
1183 {
1184     return maData.end();
1185 }
1186
1187 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */