LibreOffice Module sc (master)  1
validat.cxx
Go to the documentation of this file.
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/objsh.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/zforlist.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 <formulacell.hxx>
42 #include <patattr.hxx>
43 #include <globstr.hrc>
44 #include <scresid.hxx>
45 #include <rangenam.hxx>
46 #include <dbdata.hxx>
47 #include <typedstrdata.hxx>
48 #include <editutil.hxx>
49 #include <tokenarray.hxx>
50 #include <scmatrix.hxx>
51 #include <cellvalue.hxx>
52 #include <comphelper/lok.hxx>
53 #include <sfx2/lokhelper.hxx>
54 
55 #include <math.h>
56 #include <memory>
57 
58 using namespace formula;
59 
60 // Entries for validation (with only one condition)
61 
63  const OUString& rExpr1, const OUString& rExpr2,
64  ScDocument* pDocument, const ScAddress& rPos,
65  const OUString& rExprNmsp1, const OUString& rExprNmsp2,
66  FormulaGrammar::Grammar eGrammar1,
67  FormulaGrammar::Grammar eGrammar2 )
68  : ScConditionEntry( eOper, rExpr1, rExpr2, pDocument, 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 
80  const ScTokenArray* pArr1, const ScTokenArray* pArr2,
81  ScDocument* pDocument, const ScAddress& rPos )
82  : ScConditionEntry( eOper, pArr1, pArr2, pDocument, 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 
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 
109  : ScConditionEntry( pDocument, 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 
125 {
126 }
127 
129 {
131  return EqualEntries( aDefault );
132 }
133 
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 &&
146  aErrorTitle == r.aErrorTitle &&
148 }
149 
151 {
152  bShowInput = false;
153 }
154 
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  SfxObjectShell* pDocSh = pDocument->GetDocumentShell();
190  if ( !pDocSh )
191  return false;
192 
193  bool bScriptReturnedFalse = false; // default: do not abort
194 
195  // Set up parameters
196  css::uno::Sequence< css::uno::Any > aParams(2);
197 
198  // 1) entered or calculated value
199  OUString aValStr = rInput;
200  double nValue;
201  bool bIsValue = false;
202  if ( pCell ) // if cell exists, call interpret
203  {
204  bIsValue = pCell->IsValue();
205  if ( bIsValue )
206  nValue = pCell->GetValue();
207  else
208  aValStr = pCell->GetString().getString();
209  }
210  if ( bIsValue )
211  aParams[0] <<= nValue;
212  else
213  aParams[0] <<= aValStr;
214 
215  // 2) Position of the cell
216  OUString aPosStr(rPos.Format(ScRefFlags::VALID | ScRefFlags::TAB_3D, pDocument, pDocument->GetAddressConvention()));
217  aParams[1] <<= aPosStr;
218 
219  // use link-update flag to prevent closing the document
220  // while the macro is running
221  bool bWasInLinkUpdate = pDocument->IsInLinkUpdate();
222  if ( !bWasInLinkUpdate )
223  pDocument->SetInLinkUpdate( true );
224 
225  if ( pCell )
226  pDocument->LockTable( rPos.Tab() );
227 
228  css::uno::Any aRet;
229  css::uno::Sequence< sal_Int16 > aOutArgsIndex;
230  css::uno::Sequence< css::uno::Any > aOutArgs;
231 
232  ErrCode eRet = pDocSh->CallXScript(
233  aErrorTitle, aParams, aRet, aOutArgsIndex, aOutArgs );
234 
235  if ( pCell )
236  pDocument->UnlockTable( rPos.Tab() );
237 
238  if ( !bWasInLinkUpdate )
239  pDocument->SetInLinkUpdate( false );
240 
241  // Check the return value from the script
242  // The contents of the cell get reset if the script returns false
243  bool bTmp = false;
244  if ( eRet == ERRCODE_NONE &&
245  aRet.getValueType() == cppu::UnoType<bool>::get() &&
246  ( aRet >>= bTmp ) &&
247  !bTmp )
248  {
249  bScriptReturnedFalse = true;
250  }
251 
252  if ( eRet == ERRCODE_BASIC_METHOD_NOT_FOUND && !pCell )
253  // Macro not found (only with input)
254  {
255  //TODO: different error message, if found, but not bAllowed ??
256  std::unique_ptr<weld::MessageDialog> xBox(Application::CreateMessageDialog(pParent,
257  VclMessageType::Warning, VclButtonsType::Ok,
258  ScResId(STR_VALID_MACRONOTFOUND)));
259  xBox->run();
260  }
261 
262  return bScriptReturnedFalse;
263 }
264 
265  // true -> abort
266 
267 bool ScValidationData::DoMacro( const ScAddress& rPos, const OUString& rInput,
268  ScFormulaCell* pCell, weld::Window* pParent ) const
269 {
271  {
272  return DoScript( rPos, rInput, pCell, pParent );
273  }
274 
275  ScDocument* pDocument = GetDocument();
276  SfxObjectShell* pDocSh = pDocument->GetDocumentShell();
277  if ( !pDocSh )
278  return false;
279 
280  bool bDone = false;
281  bool bRet = false; // default: do not abort
282 
283  // If the Doc was loaded during a Basic-Calls,
284  // the Sbx-object may not be created (?)
285 // pDocSh->GetSbxObject();
286 
287 #if HAVE_FEATURE_SCRIPTING
288  // no security check ahead (only CheckMacroWarn), that happens in CallBasic
289 
290  // Function search by their simple name,
291  // then assemble aBasicStr, aMacroStr for SfxObjectShell::CallBasic
292 
293  StarBASIC* pRoot = pDocSh->GetBasic();
294  SbxVariable* pVar = pRoot->Find( aErrorTitle, SbxClassType::Method );
295  if (SbMethod* pMethod = dynamic_cast<SbMethod*>(pVar))
296  {
297  SbModule* pModule = pMethod->GetModule();
298  SbxObject* pObject = pModule->GetParent();
299  OUStringBuffer aMacroStr = pObject->GetName();
300  aMacroStr.append('.').append(pModule->GetName()).append('.').append(pMethod->GetName());
301  OUString aBasicStr;
302 
303  // the distinction between document- and app-basic has to be done
304  // by checking the parent (as in ScInterpreter::ScMacro), not by looping
305  // over all open documents, because this may be called from within loading,
306  // when SfxObjectShell::GetFirst/GetNext won't find the document.
307 
308  if ( pObject->GetParent() )
309  aBasicStr = pObject->GetParent()->GetName(); // Basic of document
310  else
311  aBasicStr = SfxGetpApp()->GetName(); // Basic of application
312 
313  // Parameter for Macro
314  SbxArrayRef refPar = new SbxArray;
315 
316  // 1) entered or calculated value
317  OUString aValStr = rInput;
318  double nValue = 0.0;
319  bool bIsValue = false;
320  if ( pCell ) // if cell set, called from interpret
321  {
322  bIsValue = pCell->IsValue();
323  if ( bIsValue )
324  nValue = pCell->GetValue();
325  else
326  aValStr = pCell->GetString().getString();
327  }
328  if ( bIsValue )
329  refPar->Get32(1)->PutDouble( nValue );
330  else
331  refPar->Get32(1)->PutString( aValStr );
332 
333  // 2) Position of the cell
334  OUString aPosStr(rPos.Format(ScRefFlags::VALID | ScRefFlags::TAB_3D, pDocument, pDocument->GetAddressConvention()));
335  refPar->Get32(2)->PutString( aPosStr );
336 
337  // use link-update flag to prevent closing the document
338  // while the macro is running
339  bool bWasInLinkUpdate = pDocument->IsInLinkUpdate();
340  if ( !bWasInLinkUpdate )
341  pDocument->SetInLinkUpdate( true );
342 
343  if ( pCell )
344  pDocument->LockTable( rPos.Tab() );
345  SbxVariableRef refRes = new SbxVariable;
346  ErrCode eRet = pDocSh->CallBasic( aMacroStr.makeStringAndClear(), aBasicStr, refPar.get(), refRes.get() );
347  if ( pCell )
348  pDocument->UnlockTable( rPos.Tab() );
349 
350  if ( !bWasInLinkUpdate )
351  pDocument->SetInLinkUpdate( false );
352 
353  // Interrupt input if Basic macro returns false
354  if ( eRet == ERRCODE_NONE && refRes->GetType() == SbxBOOL && !refRes->GetBool() )
355  bRet = true;
356  bDone = true;
357  }
358 #endif
359  if ( !bDone && !pCell ) // Macro not found (only with input)
360  {
361  //TODO: different error message, if found, but not bAllowed ??
362  std::unique_ptr<weld::MessageDialog> xBox(Application::CreateMessageDialog(pParent,
363  VclMessageType::Warning, VclButtonsType::Ok,
364  ScResId(STR_VALID_MACRONOTFOUND)));
365  xBox->run();
366  }
367 
368  return bRet;
369 }
370 
372 {
373  if ( eErrorStyle == SC_VALERR_MACRO )
374  DoMacro( pCell->aPos, EMPTY_OUSTRING, pCell, nullptr );
375 }
376 
377  // true -> abort
378 
379 bool ScValidationData::DoError(weld::Window* pParent, const OUString& rInput,
380  const ScAddress& rPos) const
381 {
382  if ( eErrorStyle == SC_VALERR_MACRO )
383  return DoMacro(rPos, rInput, nullptr, pParent);
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 
412 
413  std::unique_ptr<weld::MessageDialog> xBox(Application::CreateMessageDialog(pParent, eType,
414  eStyle, aMessage, bIsMobile));
415  xBox->set_title(aTitle);
416 
417  switch (eErrorStyle)
418  {
419  case SC_VALERR_INFO:
420  xBox->set_default_response(RET_OK);
421  break;
422  case SC_VALERR_WARNING:
423  xBox->set_default_response(RET_CANCEL);
424  break;
425  default:
426  break;
427  }
428 
429  short nRet = xBox->run();
430 
431  return ( eErrorStyle == SC_VALERR_STOP || nRet == RET_CANCEL );
432 }
433 
435  const OUString& rTest,
436  const ScPatternAttr& rPattern,
437  const ScAddress& rPos,
438  const CustomValidationPrivateAccess& ) const
439 {
440  OSL_ENSURE(GetDataMode() == SC_VALID_CUSTOM,
441  "ScValidationData::IsDataValidCustom invoked for a non-custom validation");
442 
443  if (rTest.isEmpty()) // check whether empty cells are allowed
444  return IsIgnoreBlank();
445 
446  if (rTest[0] == '=') // formulas do not pass the validity test
447  return false;
448 
449  SvNumberFormatter* pFormatter = GetDocument()->GetFormatTable();
450 
451  // get the value if any
452  sal_uInt32 nFormat = rPattern.GetNumberFormat( pFormatter );
453  double nVal;
454  bool bIsVal = pFormatter->IsNumberFormat( rTest, nFormat, nVal );
455 
456  ScRefCellValue aTmpCell;
457  svl::SharedString aSS;
458  if (bIsVal)
459  {
460  aTmpCell.meType = CELLTYPE_VALUE;
461  aTmpCell.mfValue = nVal;
462  }
463  else
464  {
465  aTmpCell.meType = CELLTYPE_STRING;
466  aSS = mpDoc->GetSharedStringPool().intern(rTest);
467  aTmpCell.mpString = &aSS;
468  }
469 
470  ScCellValue aOriginalCellValue(ScRefCellValue(*GetDocument(), rPos));
471 
472  aTmpCell.commit(*GetDocument(), rPos);
473  bool bRet = IsCellValid(aTmpCell, rPos);
474  aOriginalCellValue.commit(*GetDocument(), rPos);
475 
476  return bRet;
477 }
478 
480  const OUString& rTest, const ScPatternAttr& rPattern, const ScAddress& rPos ) const
481 {
482  if ( eDataMode == SC_VALID_ANY ) // check if any cell content is allowed
483  return true;
484 
485  if (rTest.isEmpty()) // check whether empty cells are allowed
486  return IsIgnoreBlank();
487 
488  if (rTest[0] == '=') // formulas do not pass the validity test
489  return false;
490 
491  SvNumberFormatter* pFormatter = GetDocument()->GetFormatTable();
492 
493  // get the value if any
494  sal_uInt32 nFormat = rPattern.GetNumberFormat( pFormatter );
495  double nVal;
496  bool bIsVal = pFormatter->IsNumberFormat( rTest, nFormat, nVal );
497 
498  bool bRet;
500  {
501  double nLenVal;
502  if (!bIsVal)
503  nLenVal = static_cast<double>(rTest.getLength());
504  else
505  {
506  // For numeric values use the resulting input line string to
507  // determine length, otherwise an once accepted value maybe could
508  // not be edited again, for example abbreviated dates or leading
509  // zeros or trailing zeros after decimal separator change length.
510  OUString aStr;
511  pFormatter->GetInputLineString( nVal, nFormat, aStr);
512  nLenVal = static_cast<double>( aStr.getLength() );
513  }
514  ScRefCellValue aTmpCell(nLenVal);
515  bRet = IsCellValid(aTmpCell, rPos);
516  }
517  else
518  {
519  if (bIsVal)
520  {
521  ScRefCellValue aTmpCell(nVal);
522  bRet = IsDataValid(aTmpCell, rPos);
523  }
524  else
525  {
527  ScRefCellValue aTmpCell(&aSS);
528  bRet = IsDataValid(aTmpCell, rPos);
529  }
530  }
531 
532  return bRet;
533 }
534 
535 bool ScValidationData::IsDataValid( ScRefCellValue& rCell, const ScAddress& rPos ) const
536 {
537  if( eDataMode == SC_VALID_LIST )
538  return IsListValid(rCell, rPos);
539 
540  if ( eDataMode == SC_VALID_CUSTOM )
541  return IsCellValid(rCell, rPos);
542 
543  double nVal = 0.0;
544  OUString aString;
545  bool bIsVal = true;
546 
547  switch (rCell.meType)
548  {
549  case CELLTYPE_VALUE:
550  nVal = rCell.mfValue;
551  break;
552  case CELLTYPE_STRING:
553  aString = rCell.mpString->getString();
554  bIsVal = false;
555  break;
556  case CELLTYPE_EDIT:
557  if (rCell.mpEditText)
558  aString = ScEditUtil::GetString(*rCell.mpEditText, GetDocument());
559  bIsVal = false;
560  break;
561  case CELLTYPE_FORMULA:
562  {
563  ScFormulaCell* pFCell = rCell.mpFormula;
564  bIsVal = pFCell->IsValue();
565  if ( bIsVal )
566  nVal = pFCell->GetValue();
567  else
568  aString = pFCell->GetString().getString();
569  }
570  break;
571  default: // Notes, Broadcaster
572  return IsIgnoreBlank(); // as set
573  }
574 
575  bool bOk = true;
576  switch (eDataMode)
577  {
578  // SC_VALID_ANY already above
579 
580  case SC_VALID_WHOLE:
581  case SC_VALID_DECIMAL:
582  case SC_VALID_DATE: // Date/Time is only formatting
583  case SC_VALID_TIME:
584  bOk = bIsVal;
585  if ( bOk && eDataMode == SC_VALID_WHOLE )
586  bOk = ::rtl::math::approxEqual( nVal, floor(nVal+0.5) ); // integers
587  if ( bOk )
588  bOk = IsCellValid(rCell, rPos);
589  break;
590 
591  case SC_VALID_TEXTLEN:
592  bOk = !bIsVal; // only Text
593  if ( bOk )
594  {
595  double nLenVal = static_cast<double>(aString.getLength());
596  ScRefCellValue aTmpCell(nLenVal);
597  bOk = IsCellValid(aTmpCell, rPos);
598  }
599  break;
600 
601  default:
602  OSL_FAIL("not yet done");
603  break;
604  }
605 
606  return bOk;
607 }
608 
609 namespace {
610 
614 class ScStringTokenIterator
615 {
616 public:
617  explicit ScStringTokenIterator( const ScTokenArray& rTokArr ) :
618  maIter( rTokArr ), mbOk( true ) {}
619 
621  rtl_uString* First();
623  rtl_uString* Next();
624 
626  bool Ok() const { return mbOk; }
627 
628 private:
629  svl::SharedString maCurString;
631  bool mbOk;
632 };
633 
634 rtl_uString* ScStringTokenIterator::First()
635 {
636  maIter.Reset();
637  mbOk = true;
638  return Next();
639 }
640 
641 rtl_uString* ScStringTokenIterator::Next()
642 {
643  if( !mbOk )
644  return nullptr;
645 
646  // seek to next non-separator token
647  const FormulaToken* pToken = maIter.NextNoSpaces();
648  while( pToken && (pToken->GetOpCode() == ocSep) )
649  pToken = maIter.NextNoSpaces();
650 
651  mbOk = !pToken || (pToken->GetType() == formula::svString);
652 
653  maCurString = svl::SharedString(); // start with invalid string.
654  if (mbOk && pToken)
655  maCurString = pToken->GetString();
656 
657  // string found but empty -> get next token; otherwise return it
658  return (maCurString.isValid() && maCurString.isEmpty()) ? Next() : maCurString.getData();
659 }
660 
662 sal_uLong lclGetCellFormat( const ScDocument& rDoc, const ScAddress& rPos )
663 {
664  const ScPatternAttr* pPattern = rDoc.GetPattern( rPos.Col(), rPos.Row(), rPos.Tab() );
665  if( !pPattern )
666  pPattern = rDoc.GetDefPattern();
667  return pPattern->GetNumberFormat( rDoc.GetFormatTable() );
668 }
669 
670 } // namespace
671 
673 {
674  return (eDataMode == SC_VALID_LIST) && (mnListType != css::sheet::TableValidationVisibility::INVISIBLE);
675 }
676 
678  std::vector<ScTypedStrData>* pStrings, ScRefCellValue& rCell, const ScAddress& rPos,
679  const ScTokenArray& rTokArr, int& rMatch) const
680 {
681  bool bOk = true;
682 
683  // pDoc is private in condition, use an accessor and a long winded name.
684  ScDocument* pDocument = GetDocument();
685  if( nullptr == pDocument )
686  return false;
687 
688  ScFormulaCell aValidationSrc(
690 
691  // Make sure the formula gets interpreted and a result is delivered,
692  // regardless of the AutoCalc setting.
693  aValidationSrc.Interpret();
694 
695  ScMatrixRef xMatRef;
696  const ScMatrix *pValues = aValidationSrc.GetMatrix();
697  if (!pValues)
698  {
699  // The somewhat nasty case of either an error occurred, or the
700  // dereferenced value of a single cell reference or an immediate result
701  // is stored as a single value.
702 
703  // Use an interim matrix to create the TypedStrData below.
704  xMatRef = new ScMatrix(1, 1, 0.0);
705 
706  FormulaError nErrCode = aValidationSrc.GetErrCode();
707  if (nErrCode != FormulaError::NONE)
708  {
709  /* TODO : to use later in an alert box?
710  * OUString rStrResult = "...";
711  * rStrResult += ScGlobal::GetLongErrorString(nErrCode);
712  */
713 
714  xMatRef->PutError( nErrCode, 0, 0);
715  bOk = false;
716  }
717  else if (aValidationSrc.IsValue())
718  xMatRef->PutDouble( aValidationSrc.GetValue(), 0);
719  else
720  {
721  svl::SharedString aStr = aValidationSrc.GetString();
722  xMatRef->PutString(aStr, 0);
723  }
724 
725  pValues = xMatRef.get();
726  }
727 
728  // which index matched. We will want it eventually to pre-select that item.
729  rMatch = -1;
730 
731  SvNumberFormatter* pFormatter = GetDocument()->GetFormatTable();
732 
733  SCSIZE nCol, nRow, nCols, nRows, n = 0;
734  pValues->GetDimensions( nCols, nRows );
735 
736  bool bRef = false;
737  ScRange aRange;
738 
739  ScTokenArray* pArr = const_cast<ScTokenArray*>(&rTokArr);
740  if (pArr->GetLen() == 1)
741  {
744  if (t)
745  {
746  OpCode eOpCode = t->GetOpCode();
747  if (eOpCode == ocDBArea || eOpCode == ocTableRef)
748  {
749  if (const ScDBData* pDBData = pDocument->GetDBCollection()->getNamedDBs().findByIndex(t->GetIndex()))
750  {
751  pDBData->GetArea(aRange);
752  bRef = true;
753  }
754  }
755  else if (eOpCode == ocName)
756  {
757  const ScRangeData* pName = pDocument->FindRangeNameBySheetAndIndex( t->GetSheet(), t->GetIndex());
758  if (pName && pName->IsReference(aRange))
759  {
760  bRef = true;
761  }
762  }
763  else if (t->GetType() != svIndex)
764  {
765  if (pArr->IsValidReference(aRange, rPos))
766  {
767  bRef = true;
768  }
769  }
770  }
771  }
772 
773  bool bHaveEmpty = false;
774  svl::SharedStringPool& rSPool = pDocument->GetSharedStringPool();
775 
776  /* XL artificially limits things to a single col or row in the UI but does
777  * not list the constraint in MOOXml. If a defined name or INDIRECT
778  * resulting in 1D is entered in the UI and the definition later modified
779  * to 2D, it is evaluated fine and also stored and loaded. Lets get ahead
780  * of the curve and support 2d. In XL, values are listed row-wise, do the
781  * same. */
782  for( nRow = 0; nRow < nRows ; nRow++ )
783  {
784  for( nCol = 0; nCol < nCols ; nCol++ )
785  {
786  ScTokenArray aCondTokArr(pDocument);
787  std::unique_ptr<ScTypedStrData> pEntry;
788  OUString aValStr;
789  ScMatrixValue nMatVal = pValues->Get( nCol, nRow);
790 
791  // strings and empties
792  if( ScMatrix::IsNonValueType( nMatVal.nType ) )
793  {
794  aValStr = nMatVal.GetString().getString();
795 
796  // Do not add multiple empty strings to the validation list,
797  // especially not if they'd bloat the tail with a million empty
798  // entries for a column range, fdo#61520
799  if (aValStr.isEmpty())
800  {
801  if (bHaveEmpty)
802  continue;
803  bHaveEmpty = true;
804  }
805 
806  if( nullptr != pStrings )
807  pEntry.reset(new ScTypedStrData( aValStr, 0.0, ScTypedStrData::Standard));
808 
809  if (!rCell.isEmpty() && rMatch < 0)
810  aCondTokArr.AddString(rSPool.intern(aValStr));
811  }
812  else
813  {
814  FormulaError nErr = nMatVal.GetError();
815 
816  if( FormulaError::NONE != nErr )
817  {
818  aValStr = ScGlobal::GetErrorString( nErr );
819  }
820  else
821  {
822  // FIXME FIXME FIXME
823  // Feature regression. Date formats are lost passing through the matrix
824  //pFormatter->GetInputLineString( pMatVal->fVal, 0, aValStr );
825  //For external reference and a formula that results in an area or array, date formats are still lost.
826  if ( bRef )
827  {
828  pDocument->GetInputString(static_cast<SCCOL>(nCol+aRange.aStart.Col()),
829  static_cast<SCROW>(nRow+aRange.aStart.Row()), aRange.aStart.Tab() , aValStr);
830  }
831  else
832  {
833  pFormatter->GetInputLineString( nMatVal.fVal, 0, aValStr );
834  }
835  }
836 
837  if (!rCell.isEmpty() && rMatch < 0)
838  {
839  // I am not sure errors will work here, but a user can no
840  // manually enter an error yet so the point is somewhat moot.
841  aCondTokArr.AddDouble( nMatVal.fVal );
842  }
843  if( nullptr != pStrings )
844  pEntry.reset(new ScTypedStrData( aValStr, nMatVal.fVal, ScTypedStrData::Value));
845  }
846 
847  if (rMatch < 0 && !rCell.isEmpty() && IsEqualToTokenArray(rCell, rPos, aCondTokArr))
848  {
849  rMatch = n;
850  // short circuit on the first match if not filling the list
851  if( nullptr == pStrings )
852  return true;
853  }
854 
855  if( pEntry )
856  {
857  assert(pStrings);
858  pStrings->push_back(*pEntry);
859  n++;
860  }
861  }
862  }
863 
864  // In case of no match needed and an error occurred, return that error
865  // entry as valid instead of silently failing.
866  return bOk || rCell.isEmpty();
867 }
868 
869 bool ScValidationData::FillSelectionList(std::vector<ScTypedStrData>& rStrColl, const ScAddress& rPos) const
870 {
871  bool bOk = false;
872 
873  if( HasSelectionList() )
874  {
875  std::unique_ptr<ScTokenArray> pTokArr( CreateFlatCopiedTokenArray(0) );
876 
877  // *** try if formula is a string list ***
878 
879  sal_uInt32 nFormat = lclGetCellFormat( *GetDocument(), rPos );
880  ScStringTokenIterator aIt( *pTokArr );
881  for (rtl_uString* pString = aIt.First(); pString && aIt.Ok(); pString = aIt.Next())
882  {
883  double fValue;
884  OUString aStr(pString);
885  bool bIsValue = GetDocument()->GetFormatTable()->IsNumberFormat(aStr, nFormat, fValue);
886  rStrColl.emplace_back(
887  aStr, fValue, bIsValue ? ScTypedStrData::Value : ScTypedStrData::Standard);
888  }
889  bOk = aIt.Ok();
890 
891  // *** if not a string list, try if formula results in a cell range or
892  // anything else we recognize as valid ***
893 
894  if (!bOk)
895  {
896  int nMatch;
897  ScRefCellValue aEmptyCell;
898  bOk = GetSelectionFromFormula(&rStrColl, aEmptyCell, rPos, *pTokArr, nMatch);
899  }
900  }
901 
902  return bOk;
903 }
904 
905 bool ScValidationData::IsEqualToTokenArray( ScRefCellValue& rCell, const ScAddress& rPos, const ScTokenArray& rTokArr ) const
906 {
907  // create a condition entry that tests on equality and set the passed token array
908  ScConditionEntry aCondEntry( ScConditionMode::Equal, &rTokArr, nullptr, GetDocument(), rPos );
909  return aCondEntry.IsCellValid(rCell, rPos);
910 }
911 
912 bool ScValidationData::IsListValid( ScRefCellValue& rCell, const ScAddress& rPos ) const
913 {
914  bool bIsValid = false;
915 
916  /* Compare input cell with all supported tokens from the formula.
917  Currently a formula may contain:
918  1) A list of strings (at least one string).
919  2) A single cell or range reference.
920  3) A single defined name (must contain a cell/range reference, another
921  name, or DB range, or a formula resulting in a cell/range reference
922  or matrix/array).
923  4) A single database range.
924  5) A formula resulting in a cell/range reference or matrix/array.
925  */
926 
927  std::unique_ptr< ScTokenArray > pTokArr( CreateFlatCopiedTokenArray( 0 ) );
928 
929  // *** try if formula is a string list ***
930 
932  sal_uInt32 nFormat = lclGetCellFormat( *GetDocument(), rPos );
933  ScStringTokenIterator aIt( *pTokArr );
934  for (rtl_uString* pString = aIt.First(); pString && aIt.Ok(); pString = aIt.Next())
935  {
936  /* Do not break the loop, if a valid string has been found.
937  This is to find invalid tokens following in the formula. */
938  if( !bIsValid )
939  {
940  // create a formula containing a single string or number
941  ScTokenArray aCondTokArr(GetDocument());
942  double fValue;
943  OUString aStr(pString);
944  if (GetDocument()->GetFormatTable()->IsNumberFormat(aStr, nFormat, fValue))
945  aCondTokArr.AddDouble( fValue );
946  else
947  aCondTokArr.AddString(rSPool.intern(aStr));
948 
949  bIsValid = IsEqualToTokenArray(rCell, rPos, aCondTokArr);
950  }
951  }
952 
953  if( !aIt.Ok() )
954  bIsValid = false;
955 
956  // *** if not a string list, try if formula results in a cell range or
957  // anything else we recognize as valid ***
958 
959  if (!bIsValid)
960  {
961  int nMatch;
962  bIsValid = GetSelectionFromFormula(nullptr, rCell, rPos, *pTokArr, nMatch);
963  bIsValid = bIsValid && nMatch >= 0;
964  }
965 
966  return bIsValid;
967 }
968 
970 {
971  // for Ref-Undo - real copy with new tokens!
972 
973  for (const auto& rxItem : rList)
974  {
975  InsertNew( std::unique_ptr<ScValidationData>(rxItem->Clone()) );
976  }
977 
978  //TODO: faster insert for sorted entries from rList ???
979 }
980 
982  const ScValidationDataList& rList)
983 {
984  // for new documents - real copy with new tokens!
985 
986  for (const auto& rxItem : rList)
987  {
988  InsertNew( std::unique_ptr<ScValidationData>(rxItem->Clone(pNewDoc)) );
989  }
990 
991  //TODO: faster insert for sorted entries from rList ???
992 }
993 
995 {
996  //TODO: binary search
997 
998  for( iterator it = begin(); it != end(); ++it )
999  if( (*it)->GetKey() == nKey )
1000  return it->get();
1001 
1002  OSL_FAIL("ScValidationDataList: Entry not found");
1003  return nullptr;
1004 }
1005 
1007 {
1008  for( iterator it = begin(); it != end(); ++it )
1009  (*it)->CompileXML();
1010 }
1011 
1013 {
1014  for( iterator it = begin(); it != end(); ++it )
1015  (*it)->UpdateReference(rCxt);
1016 }
1017 
1019 {
1020  for (iterator it = begin(); it != end(); ++it)
1021  (*it)->UpdateInsertTab(rCxt);
1022 }
1023 
1025 {
1026  for (iterator it = begin(); it != end(); ++it)
1027  (*it)->UpdateDeleteTab(rCxt);
1028 }
1029 
1031 {
1032  for (iterator it = begin(); it != end(); ++it)
1033  (*it)->UpdateMoveTab(rCxt);
1034 }
1035 
1037 {
1038  return maData.begin();
1039 }
1040 
1042 {
1043  return maData.begin();
1044 }
1045 
1047 {
1048  return maData.end();
1049 }
1050 
1052 {
1053  return maData.end();
1054 }
1055 
1056 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
static bool IsNonValueType(ScMatValType nType)
String, empty or empty path, but not value nor boolean.
Definition: scmatrix.hxx:173
Matrix data type that can store values of mixed types.
Definition: scmatrix.hxx:113
ErrCode CallBasic(const OUString &rMacro, const OUString &rBasicName, SbxArray *pArgs, SbxValue *pRet=nullptr)
static bool IsXScriptURL(const OUString &rScriptURL)
bool IsDataValid(const OUString &rTest, const ScPatternAttr &rPattern, const ScAddress &rPos) const
Definition: validat.cxx:479
SC_DLLPUBLIC void Format(OStringBuffer &r, ScRefFlags nFlags, const ScDocument *pDocument=nullptr, const Details &rDetails=detailsOOOa1) const
Definition: address.cxx:2111
OUString aInputMessage
Definition: validat.hxx:71
virtual svl::SharedString GetString() const
SC_DLLPUBLIC ScDBCollection * GetDBCollection() const
Definition: document.hxx:816
OUString getString() const
ScAddress aStart
Definition: address.hxx:500
SharedString intern(const OUString &rStr)
bool GetSelectionFromFormula(std::vector< ScTypedStrData > *pStrings, ScRefCellValue &rCell, const ScAddress &rPos, const ScTokenArray &rTokArr, int &rMatch) const
Tries to fill the passed collection with list validation entries.
Definition: validat.cxx:677
ScDBData * findByIndex(sal_uInt16 nIndex)
Definition: dbdata.cxx:1138
SC_DLLPUBLIC svl::SharedStringPool & GetSharedStringPool()
Definition: documen2.cxx:563
SC_DLLPUBLIC void GetInputString(SCCOL nCol, SCROW nRow, SCTAB nTab, OUString &rString)
Definition: document.cxx:3543
bool FillSelectionList(std::vector< ScTypedStrData > &rStrings, const ScAddress &rPos) const
Tries to fill the passed collection with list validation entries.
Definition: validat.cxx:869
#define EMPTY_OUSTRING
Definition: global.hxx:214
bool isLOKMobilePhone() const
const OUString & GetName(SbxNameType=SbxNameType::NONE) const
ocName
SCROW Row() const
Definition: address.hxx:262
OUString aErrorTitle
Definition: validat.hxx:72
VclButtonsType
bool isEmpty() const
Definition: cellvalue.cxx:670
void SetInLinkUpdate(bool bSet)
Definition: documen8.cxx:774
bool IsDataValidCustom(const OUString &rTest, const ScPatternAttr &rPattern, const ScAddress &rPos, const CustomValidationPrivateAccess &) const
Definition: validat.cxx:434
ScValidErrorStyle
Definition: validat.hxx:50
sal_uIntPtr sal_uLong
Context for reference update during shifting, moving or copying of cell ranges.
ScMatrixValue Get(SCSIZE nC, SCSIZE nR) const
: If bString the ScMatrixValue->pS may still be NULL to indicate an empty string! ...
Definition: scmatrix.cxx:3078
sal_Int64 n
ScValidationMode eDataMode
Definition: validat.hxx:65
StarBASIC * GetBasic() const
OpCode GetOpCode() const
This is very similar to ScCellValue, except that it references the original value instead of copying ...
Definition: cellvalue.hxx:104
bool HasSelectionList() const
Returns true, if the validation cell will show a selection list.
Definition: validat.cxx:672
OUString aInputTitle
Definition: validat.hxx:70
bool DoMacro(const ScAddress &rPos, const OUString &rInput, ScFormulaCell *pCell, weld::Window *pParent) const
Definition: validat.cxx:267
EmbeddedObjectRef * pObject
bool IsCellValid(ScRefCellValue &rCell, const ScAddress &rPos) const
Definition: conditio.cxx:1219
RET_CANCEL
SC_DLLPUBLIC formula::FormulaGrammar::AddressConvention GetAddressConvention() const
Definition: documen3.cxx:475
Try NOT to use this struct.
Definition: scmatrix.hxx:53
ScValidationDataListDataType::const_iterator const_iterator
Definition: validat.hxx:207
exports com.sun.star. sheet
virtual sal_Int16 GetSheet() const
SfxApplication * SfxGetpApp()
bool DoScript(const ScAddress &rPos, const OUString &rInput, ScFormulaCell *pCell, weld::Window *pParent) const
Definition: validat.cxx:185
void UnlockTable(SCTAB nTab)
Definition: document.cxx:5320
Store arbitrary cell value of any kind.
Definition: cellvalue.hxx:36
ScValidErrorStyle eErrorStyle
Definition: validat.hxx:68
double fVal
Definition: scmatrix.hxx:55
void UpdateMoveTab(sc::RefUpdateMoveTabContext &rCxt)
Definition: validat.cxx:1030
const EditTextObject * mpEditText
Definition: cellvalue.hxx:110
size_t SCSIZE
size_t typedef to be able to find places where code was changed from USHORT to size_t and is used to ...
Definition: address.hxx:45
const BorderLinePrimitive2D *pCandidateB assert(pCandidateA)
ScValidationData(ScValidationMode eMode, ScConditionMode eOper, const OUString &rExpr1, const OUString &rExpr2, ScDocument *pDocument, const ScAddress &rPos, const OUString &rExprNmsp1=EMPTY_OUSTRING, const OUString &rExprNmsp2=EMPTY_OUSTRING, formula::FormulaGrammar::Grammar eGrammar1=formula::FormulaGrammar::GRAM_DEFAULT, formula::FormulaGrammar::Grammar eGrammar2=formula::FormulaGrammar::GRAM_DEFAULT)
Definition: validat.cxx:62
double GetValue()
FormulaError GetErrCode()
OpCode
SbxBOOL
static SfxViewShell * Current()
void SetInput(const OUString &rTitle, const OUString &rMsg)
Definition: validat.cxx:160
::boost::intrusive_ptr< ScMatrix > ScMatrixRef
Definition: types.hxx:26
ScConditionMode
Definition: conditio.hxx:61
ScFormulaCell * mpFormula
Definition: cellvalue.hxx:111
SCTAB Tab() const
Definition: address.hxx:271
VclMessageType
virtual ~ScValidationData() override
Definition: validat.cxx:124
void commit(ScDocument &rDoc, const ScAddress &rPos) const
Set cell value at specified position in specified document.
Definition: cellvalue.cxx:592
const OUString & GetName() const
ScAddress aPos
SC_DLLPUBLIC const ScPatternAttr * GetPattern(SCCOL nCol, SCROW nRow, SCTAB nTab) const
Definition: document.cxx:4738
ScValidationMode
Definition: validat.hxx:38
ScValidationDataListDataType maData
Definition: validat.hxx:199
sal_uInt16 GetLen() const
ScValidationMode GetDataMode() const
Definition: validat.hxx:114
void DoCalcError(ScFormulaCell *pCell) const
Definition: validat.cxx:371
ocSep
DocumentType eType
bool operator==(const ScFormatEntry &) const
Definition: conditio.cxx:55
FormulaError GetError() const
Only valid if ScMatrix methods indicate that this is no string!
Definition: scmatrix.hxx:63
T * get() const
sal_uInt16 char * pName
Definition: callform.cxx:58
SC_DLLPUBLIC SvNumberFormatter * GetFormatTable() const
Definition: documen2.cxx:438
ScValidationData * GetData(sal_uInt32 nKey)
Definition: validat.cxx:994
void UpdateReference(sc::RefUpdateContext &rCxt)
Definition: validat.cxx:1012
OUString aErrorMessage
Definition: validat.hxx:73
void GetDimensions(SCSIZE &rC, SCSIZE &rR) const
Definition: scmatrix.cxx:2968
const svl::SharedString * mpString
Definition: cellvalue.hxx:109
bool IsEqualToTokenArray(ScRefCellValue &rCell, const ScAddress &rPos, const ScTokenArray &rTokArr) const
Tests, if pCell is equal to what the passed token array represents.
Definition: validat.cxx:905
svIndex
bool Interpret(SCROW nStartOffset=-1, SCROW nEndOffset=-1)
#define ERRCODE_BASIC_METHOD_NOT_FOUND
OUString ScResId(const char *pId)
Definition: scdll.cxx:95
bool IsNumberFormat(const OUString &sString, sal_uInt32 &F_Index, double &fOutNumber, SvNumInputOptions eInputOptions=SvNumInputOptions::NONE)
ErrCode CallXScript(const OUString &rScriptURL, const css::uno::Sequence< css::uno::Any > &aParams, css::uno::Any &aRet, css::uno::Sequence< sal_Int16 > &aOutParamIndex, css::uno::Sequence< css::uno::Any > &aOutParam, bool bRaiseError=true, const css::uno::Any *aCaller=nullptr)
bool EqualEntries(const ScValidationData &r) const
Definition: validat.cxx:134
const SbxObject * GetParent() const
sal_uInt32 GetNumberFormat(SvNumberFormatter *) const
Definition: patattr.cxx:1257
css::uno::Type const & get()
void GetInputLineString(const double &fOutNumber, sal_uInt32 nFIndex, OUString &rOutString)
ScMatValType nType
Definition: scmatrix.hxx:57
virtual sal_uInt16 GetIndex() const
FormulaError
SCCOL Col() const
Definition: address.hxx:267
XPropertyListType t
void LockTable(SCTAB nTab)
Definition: document.cxx:5310
void InsertNew(std::unique_ptr< ScValidationData > pNew)
Definition: validat.hxx:214
bool GetErrMsg(OUString &rTitle, OUString &rMsg, ScValidErrorStyle &rStyle) const
Definition: validat.cxx:176
const svl::SharedString & GetString() const
Only valid if ScMatrix methods indicate so!
Definition: scmatrix.hxx:60
bool GetBool() const
void UpdateDeleteTab(sc::RefUpdateDeleteTabContext &rCxt)
Definition: validat.cxx:1024
CellType meType
Definition: cellvalue.hxx:106
svl::SharedString GetString()
bool IsEmpty() const
Definition: validat.cxx:128
const PropertyValue * pValues
void ResetInput()
Definition: validat.cxx:150
virtual SbxDataType GetType() const override
FormulaToken * AddString(const svl::SharedString &rStr)
#define ERRCODE_NONE
ScValidationDataListDataType::iterator iterator
Definition: validat.hxx:206
bool PutString(const OUString &)
AnnotationVector::iterator maIter
RET_OK
void SetError(const OUString &rTitle, const OUString &rMsg, ScValidErrorStyle eStyle)
Definition: validat.cxx:167
bool PutDouble(double)
const ScMatrix * GetMatrix()
ocTableRef
SC_DLLPUBLIC ScPatternAttr * GetDefPattern() const
Definition: document.cxx:6059
ScDocument * mpDoc
Definition: conditio.hxx:257
NamedDBs & getNamedDBs()
Definition: dbdata.hxx:315
SC_DLLPUBLIC bool IsReference(ScRange &rRef) const
Definition: rangenam.cxx:369
void UpdateInsertTab(sc::RefUpdateInsertTabContext &rCxt)
Definition: validat.cxx:1018
virtual SbxVariable * Find(const OUString &, SbxClassType) override
bool IsValidReference(ScRange &rRange, const ScAddress &rPos) const
Exactly and only one valid range (no #REF!s)
Definition: token.cxx:1860
bool DoError(weld::Window *pParent, const OUString &rInput, const ScAddress &rPos) const
Definition: validat.cxx:379
bool IsInLinkUpdate() const
Definition: documen8.cxx:782
static SC_DLLPUBLIC OUString GetString(const EditTextObject &rEditText, const ScDocument *pDoc)
Retrieves string with paragraphs delimited by new lines (' ').
Definition: editutil.cxx:115
SfxObjectShell * GetDocumentShell() const
Definition: document.hxx:1058
std::unique_ptr< ScTokenArray > CreateFlatCopiedTokenArray(sal_uInt16 nPos) const
Create a flat copy using ScTokenArray copy-ctor with shared tokens.
Definition: conditio.cxx:1281
ScDocument * GetDocument() const
Definition: conditio.hxx:414
bool IsIgnoreBlank() const
Definition: conditio.hxx:372
SbxVariable * Get32(sal_uInt32)
ScRangeData * FindRangeNameBySheetAndIndex(SCTAB nTab, sal_uInt16 nIndex) const
Find a named expression / range name in either global or a local scope.
Definition: documen3.cxx:250
void commit(ScDocument &rDoc, const ScAddress &rPos) const
Set cell value at specified position in specified document.
Definition: cellvalue.cxx:402
StackVar GetType() const
aStr
void ResetError()
Definition: validat.cxx:155
static OUString GetErrorString(FormulaError nErrNumber)
Definition: global.cxx:305
static weld::MessageDialog * CreateMessageDialog(weld::Widget *pParent, VclMessageType eMessageType, VclButtonsType eButtonType, const OUString &rPrimaryMessage, bool bMobile=false)
ocDBArea
sal_Int16 nValue
sal_Int16 mnListType
Definition: validat.hxx:69
FormulaToken * AddDouble(double fVal)
bool IsListValid(ScRefCellValue &rCell, const ScAddress &rPos) const
Tests, if contents of pCell occur in cell range referenced by own formula, or in a string list...
Definition: validat.cxx:912