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 <sfx2/viewsh.hxx>
28#include <basic/sbmeth.hxx>
29#include <basic/sbmod.hxx>
30#include <basic/sbstar.hxx>
31#include <basic/sberrors.hxx>
32
33#include <basic/sbx.hxx>
34#include <svl/numformat.hxx>
36#include <vcl/svapp.hxx>
37#include <vcl/weld.hxx>
38#include <rtl/math.hxx>
39#include <osl/diagnose.h>
40
41#include <document.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 <comphelper/lok.hxx>
54
55#include <math.h>
56#include <memory>
57
58using namespace formula;
59
60// Entries for validation (with only one condition)
61
63 const OUString& rExpr1, const OUString& rExpr2,
64 ScDocument& rDocument, const ScAddress& rPos,
65 const OUString& rExprNmsp1, const OUString& rExprNmsp2,
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
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
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( 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
125{
126}
127
129{
131 return EqualEntries( aDefault );
132}
133
135{
136 // test same parameters (excluding Key)
137
139 eDataMode == r.eDataMode &&
140 bShowInput == r.bShowInput &&
141 bShowError == r.bShowError &&
143 mnListType == r.mnListType &&
148}
149
151{
152 bShowInput = false;
153}
154
156{
157 bShowError = false;
158}
159
160void ScValidationData::SetInput( const OUString& rTitle, const OUString& rMsg )
161{
162 bShowInput = true;
163 aInputTitle = rTitle;
164 aInputMessage = rMsg;
165}
166
167void 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
176bool 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
185bool 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 // 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
259bool ScValidationData::DoMacro( const ScAddress& rPos, const OUString& rInput,
260 ScFormulaCell* pCell, weld::Window* pParent ) const
261{
263 {
264 return DoScript( rPos, rInput, pCell, pParent );
265 }
266
267 ScDocument* pDocument = GetDocument();
268 SfxObjectShell* 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
364{
366 DoMacro( pCell->aPos, OUString(), pCell, nullptr );
367}
368
369 // true -> abort
370
371bool ScValidationData::DoError(weld::Window* pParent, const OUString& rInput,
372 const ScAddress& rPos) const
373{
375 return DoMacro(rPos, rInput, nullptr, pParent);
376
377 // Output error message
378
379 OUString aTitle = aErrorTitle;
380 if (aTitle.isEmpty())
381 aTitle = ScResId( STR_MSSG_DOSUBTOTALS_0 ); // application title
382 OUString aMessage = aErrorMessage;
383 if (aMessage.isEmpty())
384 aMessage = ScResId( STR_VALID_DEFERROR );
385
386 VclButtonsType eStyle = VclButtonsType::Ok;
387 VclMessageType eType = VclMessageType::Error;
388 switch (eErrorStyle)
389 {
390 case SC_VALERR_INFO:
391 eType = VclMessageType::Info;
392 eStyle = VclButtonsType::OkCancel;
393 break;
395 eType = VclMessageType::Warning;
396 eStyle = VclButtonsType::OkCancel;
397 break;
398 default:
399 break;
400 }
401
404
405 std::unique_ptr<weld::MessageDialog> xBox(Application::CreateMessageDialog(pParent, eType,
406 eStyle, aMessage, bIsMobile));
407 xBox->set_title(aTitle);
408
409 switch (eErrorStyle)
410 {
411 case SC_VALERR_INFO:
412 xBox->set_default_response(RET_OK);
413 break;
415 xBox->set_default_response(RET_CANCEL);
416 break;
417 default:
418 break;
419 }
420
421 short nRet = xBox->run();
422
423 return ( eErrorStyle == SC_VALERR_STOP || nRet == RET_CANCEL );
424}
425
427 const OUString& rTest,
428 const ScPatternAttr& rPattern,
429 const ScAddress& rPos,
430 const CustomValidationPrivateAccess& ) const
431{
432 OSL_ENSURE(GetDataMode() == SC_VALID_CUSTOM,
433 "ScValidationData::IsDataValidCustom invoked for a non-custom validation");
434
435 if (rTest.isEmpty()) // check whether empty cells are allowed
436 return IsIgnoreBlank();
437
438 if (rTest[0] == '=') // formulas do not pass the validity test
439 return false;
440
442
443 // get the value if any
444 sal_uInt32 nFormat = rPattern.GetNumberFormat( pFormatter );
445 double nVal;
446 bool bIsVal = pFormatter->IsNumberFormat( rTest, nFormat, nVal );
447
448 ScRefCellValue aTmpCell;
450 if (bIsVal)
451 {
452 aTmpCell = ScRefCellValue(nVal);
453 }
454 else
455 {
456 aSS = mpDoc->GetSharedStringPool().intern(rTest);
457 aTmpCell = ScRefCellValue(&aSS);
458 }
459
460 ScCellValue aOriginalCellValue(ScRefCellValue(*GetDocument(), rPos));
461
462 aTmpCell.commit(*GetDocument(), rPos);
463 bool bRet = IsCellValid(aTmpCell, rPos);
464 aOriginalCellValue.commit(*GetDocument(), rPos);
465
466 return bRet;
467}
468
475{
477 double mfVal;
478 sal_uInt32 mnFormat;
479
480 ScValidationDataIsNumeric( double fVal, SvNumberFormatter* pFormatter = nullptr, sal_uInt32 nFormat = 0 )
481 : mpFormatter(pFormatter), mfVal(fVal), mnFormat(nFormat)
482 {
483 }
484
485 void init( const ScDocument& rDoc, const ScAddress& rPos )
486 {
487 const ScPatternAttr* pPattern = rDoc.GetPattern( rPos.Col(), rPos.Row(), rPos.Tab());
490 }
491};
492
493bool ScValidationData::IsDataValidTextLen( const OUString& rTest, const ScAddress& rPos,
494 ScValidationDataIsNumeric* pDataNumeric ) const
495{
496 sal_Int32 nLen;
497 if (!pDataNumeric)
498 nLen = rTest.getLength();
499 else
500 {
501 if (!pDataNumeric->mpFormatter)
502 pDataNumeric->init( *GetDocument(), rPos);
503
504 // For numeric values use the resulting input line string to
505 // determine length, otherwise an once accepted value maybe could
506 // not be edited again, for example abbreviated dates or leading
507 // zeros or trailing zeros after decimal separator change length.
508 OUString aStr;
509 pDataNumeric->mpFormatter->GetInputLineString( pDataNumeric->mfVal, pDataNumeric->mnFormat, aStr);
510 nLen = aStr.getLength();
511 }
512 ScRefCellValue aTmpCell( static_cast<double>(nLen));
513 return IsCellValid( aTmpCell, rPos);
514}
515
517 const OUString& rTest, const ScPatternAttr& rPattern, const ScAddress& rPos ) const
518{
519 if ( eDataMode == SC_VALID_ANY ) // check if any cell content is allowed
520 return true;
521
522 if (rTest.isEmpty()) // check whether empty cells are allowed
523 return IsIgnoreBlank();
524
525 if (rTest[0] == '=') // formulas do not pass the validity test
526 return false;
527
529
530 // get the value if any
531 sal_uInt32 nFormat = rPattern.GetNumberFormat( pFormatter );
532 double nVal;
533 bool bIsVal = pFormatter->IsNumberFormat( rTest, nFormat, nVal );
534
535 bool bRet;
537 {
538 if (!bIsVal)
539 bRet = IsDataValidTextLen( rTest, rPos, nullptr);
540 else
541 {
542 ScValidationDataIsNumeric aDataNumeric( nVal, pFormatter, nFormat);
543 bRet = IsDataValidTextLen( rTest, rPos, &aDataNumeric);
544 }
545 }
546 else
547 {
548 if (bIsVal)
549 {
550 ScRefCellValue aTmpCell(nVal);
551 bRet = IsDataValid(aTmpCell, rPos);
552 }
553 else
554 {
556 ScRefCellValue aTmpCell(&aSS);
557 bRet = IsDataValid(aTmpCell, rPos);
558 }
559 }
560
561 return bRet;
562}
563
565{
566 if( eDataMode == SC_VALID_LIST )
567 return IsListValid(rCell, rPos);
568
569 if ( eDataMode == SC_VALID_CUSTOM )
570 return IsCellValid(rCell, rPos);
571
572 double nVal = 0.0;
573 OUString aString;
574 bool bIsVal = true;
575
576 switch (rCell.getType())
577 {
578 case CELLTYPE_VALUE:
579 nVal = rCell.getDouble();
580 break;
581 case CELLTYPE_STRING:
582 aString = rCell.getSharedString()->getString();
583 bIsVal = false;
584 break;
585 case CELLTYPE_EDIT:
586 if (rCell.getEditText())
587 aString = ScEditUtil::GetString(*rCell.getEditText(), GetDocument());
588 bIsVal = false;
589 break;
590 case CELLTYPE_FORMULA:
591 {
592 ScFormulaCell* pFCell = rCell.getFormula();
593 bIsVal = pFCell->IsValue();
594 if ( bIsVal )
595 nVal = pFCell->GetValue();
596 else
597 aString = pFCell->GetString().getString();
598 }
599 break;
600 default: // Notes, Broadcaster
601 return IsIgnoreBlank(); // as set
602 }
603
604 bool bOk = true;
605 switch (eDataMode)
606 {
607 // SC_VALID_ANY already above
608
609 case SC_VALID_WHOLE:
610 case SC_VALID_DECIMAL:
611 case SC_VALID_DATE: // Date/Time is only formatting
612 case SC_VALID_TIME:
613 bOk = bIsVal;
614 if ( bOk && eDataMode == SC_VALID_WHOLE )
615 bOk = ::rtl::math::approxEqual( nVal, floor(nVal+0.5) ); // integers
616 if ( bOk )
617 bOk = IsCellValid(rCell, rPos);
618 break;
619
620 case SC_VALID_TEXTLEN:
621 if (!bIsVal)
622 bOk = IsDataValidTextLen( aString, rPos, nullptr);
623 else
624 {
625 ScValidationDataIsNumeric aDataNumeric( nVal);
626 bOk = IsDataValidTextLen( aString, rPos, &aDataNumeric);
627 }
628 break;
629
630 default:
631 OSL_FAIL("not yet done");
632 break;
633 }
634
635 return bOk;
636}
637
638namespace {
639
643class ScStringTokenIterator
644{
645public:
646 explicit ScStringTokenIterator( const ScTokenArray& rTokArr ) :
647 maIter( rTokArr ), mbOk( true ) {}
648
650 rtl_uString* First();
652 rtl_uString* Next();
653
655 bool Ok() const { return mbOk; }
656
657private:
658 svl::SharedString maCurString;
660 bool mbOk;
661};
662
663rtl_uString* ScStringTokenIterator::First()
664{
665 maIter.Reset();
666 mbOk = true;
667 return Next();
668}
669
670rtl_uString* ScStringTokenIterator::Next()
671{
672 if( !mbOk )
673 return nullptr;
674
675 // seek to next non-separator token
676 const FormulaToken* pToken = maIter.NextNoSpaces();
677 while( pToken && (pToken->GetOpCode() == ocSep) )
678 pToken = maIter.NextNoSpaces();
679
680 mbOk = !pToken || (pToken->GetType() == formula::svString);
681
682 maCurString = svl::SharedString(); // start with invalid string.
683 if (mbOk && pToken)
684 maCurString = pToken->GetString();
685
686 // string found but empty -> get next token; otherwise return it
687 return (maCurString.isValid() && maCurString.isEmpty()) ? Next() : maCurString.getData();
688}
689
691sal_uLong lclGetCellFormat( const ScDocument& rDoc, const ScAddress& rPos )
692{
693 const ScPatternAttr* pPattern = rDoc.GetPattern( rPos.Col(), rPos.Row(), rPos.Tab() );
694 if( !pPattern )
695 pPattern = rDoc.GetDefPattern();
696 return pPattern->GetNumberFormat( rDoc.GetFormatTable() );
697}
698
699} // namespace
700
702{
703 return (eDataMode == SC_VALID_LIST) && (mnListType != css::sheet::TableValidationVisibility::INVISIBLE);
704}
705
707 std::vector<ScTypedStrData>* pStrings, ScRefCellValue& rCell, const ScAddress& rPos,
708 const ScTokenArray& rTokArr, int& rMatch) const
709{
710 bool bOk = true;
711
712 // pDoc is private in condition, use an accessor and a long winded name.
713 ScDocument* pDocument = GetDocument();
714 if( nullptr == pDocument )
715 return false;
716
717 ScFormulaCell aValidationSrc(
719
720 // Make sure the formula gets interpreted and a result is delivered,
721 // regardless of the AutoCalc setting.
722 aValidationSrc.Interpret();
723
724 ScMatrixRef xMatRef;
725 const ScMatrix *pValues = aValidationSrc.GetMatrix();
726 if (!pValues)
727 {
728 // The somewhat nasty case of either an error occurred, or the
729 // dereferenced value of a single cell reference or an immediate result
730 // is stored as a single value.
731
732 // Use an interim matrix to create the TypedStrData below.
733 xMatRef = new ScMatrix(1, 1, 0.0);
734
735 FormulaError nErrCode = aValidationSrc.GetErrCode();
736 if (nErrCode != FormulaError::NONE)
737 {
738 /* TODO : to use later in an alert box?
739 * OUString rStrResult = "...";
740 * rStrResult += ScGlobal::GetLongErrorString(nErrCode);
741 */
742
743 xMatRef->PutError( nErrCode, 0, 0);
744 bOk = false;
745 }
746 else if (aValidationSrc.IsValue())
747 xMatRef->PutDouble( aValidationSrc.GetValue(), 0);
748 else
749 {
750 svl::SharedString aStr = aValidationSrc.GetString();
751 xMatRef->PutString(aStr, 0);
752 }
753
754 pValues = xMatRef.get();
755 }
756
757 // which index matched. We will want it eventually to pre-select that item.
758 rMatch = -1;
759
761
762 SCSIZE nCol, nRow, nCols, nRows, n = 0;
763 pValues->GetDimensions( nCols, nRows );
764
765 bool bRef = false;
766 ScRange aRange;
767
768 ScTokenArray* pArr = const_cast<ScTokenArray*>(&rTokArr);
769 if (pArr->GetLen() == 1)
770 {
773 if (t)
774 {
775 OpCode eOpCode = t->GetOpCode();
776 if (eOpCode == ocDBArea || eOpCode == ocTableRef)
777 {
778 if (const ScDBData* pDBData = pDocument->GetDBCollection()->getNamedDBs().findByIndex(t->GetIndex()))
779 {
780 pDBData->GetArea(aRange);
781 bRef = true;
782 }
783 }
784 else if (eOpCode == ocName)
785 {
786 const ScRangeData* pName = pDocument->FindRangeNameBySheetAndIndex( t->GetSheet(), t->GetIndex());
787 if (pName && pName->IsReference(aRange))
788 {
789 bRef = true;
790 }
791 }
792 else if (t->GetType() != svIndex)
793 {
794 if (pArr->IsValidReference(aRange, rPos))
795 {
796 bRef = true;
797 }
798 }
799 }
800 }
801
802 bool bHaveEmpty = false;
803 svl::SharedStringPool& rSPool = pDocument->GetSharedStringPool();
804
805 /* XL artificially limits things to a single col or row in the UI but does
806 * not list the constraint in MOOXml. If a defined name or INDIRECT
807 * resulting in 1D is entered in the UI and the definition later modified
808 * to 2D, it is evaluated fine and also stored and loaded. Lets get ahead
809 * of the curve and support 2d. In XL, values are listed row-wise, do the
810 * same. */
811 for( nRow = 0; nRow < nRows ; nRow++ )
812 {
813 for( nCol = 0; nCol < nCols ; nCol++ )
814 {
815 ScTokenArray aCondTokArr(*pDocument);
816 std::unique_ptr<ScTypedStrData> pEntry;
817 OUString aValStr;
818 ScMatrixValue nMatVal = pValues->Get( nCol, nRow);
819
820 // strings and empties
821 if( ScMatrix::IsNonValueType( nMatVal.nType ) )
822 {
823 aValStr = nMatVal.GetString().getString();
824
825 // Do not add multiple empty strings to the validation list,
826 // especially not if they'd bloat the tail with a million empty
827 // entries for a column range, fdo#61520
828 if (aValStr.isEmpty())
829 {
830 if (bHaveEmpty)
831 continue;
832 bHaveEmpty = true;
833 }
834
835 if( nullptr != pStrings )
836 pEntry.reset(new ScTypedStrData(aValStr, 0.0, 0.0, ScTypedStrData::Standard));
837
838 if (!rCell.isEmpty() && rMatch < 0)
839 aCondTokArr.AddString(rSPool.intern(aValStr));
840 }
841 else
842 {
843 FormulaError nErr = nMatVal.GetError();
844
845 if( FormulaError::NONE != nErr )
846 {
847 aValStr = ScGlobal::GetErrorString( nErr );
848 }
849 else
850 {
851 // FIXME FIXME FIXME
852 // Feature regression. Date formats are lost passing through the matrix
853 //pFormatter->GetInputLineString( pMatVal->fVal, 0, aValStr );
854 //For external reference and a formula that results in an area or array, date formats are still lost.
855 if ( bRef )
856 {
857 aValStr = pDocument->GetInputString(static_cast<SCCOL>(nCol+aRange.aStart.Col()),
858 static_cast<SCROW>(nRow+aRange.aStart.Row()), aRange.aStart.Tab());
859 }
860 else
861 {
862 pFormatter->GetInputLineString( nMatVal.fVal, 0, aValStr );
863 }
864 }
865
866 if (!rCell.isEmpty() && rMatch < 0)
867 {
868 // I am not sure errors will work here, but a user can no
869 // manually enter an error yet so the point is somewhat moot.
870 aCondTokArr.AddDouble( nMatVal.fVal );
871 }
872 if( nullptr != pStrings )
873 pEntry.reset(new ScTypedStrData(aValStr, nMatVal.fVal, nMatVal.fVal, ScTypedStrData::Value));
874 }
875
876 if (rMatch < 0 && !rCell.isEmpty() && IsEqualToTokenArray(rCell, rPos, aCondTokArr))
877 {
878 rMatch = n;
879 // short circuit on the first match if not filling the list
880 if( nullptr == pStrings )
881 return true;
882 }
883
884 if( pEntry )
885 {
886 assert(pStrings);
887 pStrings->push_back(*pEntry);
888 n++;
889 }
890 }
891 }
892
893 // In case of no match needed and an error occurred, return that error
894 // entry as valid instead of silently failing.
895 return bOk || rCell.isEmpty();
896}
897
898bool ScValidationData::FillSelectionList(std::vector<ScTypedStrData>& rStrColl, const ScAddress& rPos) const
899{
900 bool bOk = false;
901
902 if( HasSelectionList() )
903 {
904 std::unique_ptr<ScTokenArray> pTokArr( CreateFlatCopiedTokenArray(0) );
905
906 // *** try if formula is a string list ***
907
908 sal_uInt32 nFormat = lclGetCellFormat( *GetDocument(), rPos );
909 ScStringTokenIterator aIt( *pTokArr );
910 for (rtl_uString* pString = aIt.First(); pString && aIt.Ok(); pString = aIt.Next())
911 {
912 double fValue;
913 OUString aStr(pString);
914 bool bIsValue = GetDocument()->GetFormatTable()->IsNumberFormat(aStr, nFormat, fValue);
915 rStrColl.emplace_back(
916 aStr, fValue, fValue, bIsValue ? ScTypedStrData::Value : ScTypedStrData::Standard);
917 }
918 bOk = aIt.Ok();
919
920 // *** if not a string list, try if formula results in a cell range or
921 // anything else we recognize as valid ***
922
923 if (!bOk)
924 {
925 int nMatch;
926 ScRefCellValue aEmptyCell;
927 bOk = GetSelectionFromFormula(&rStrColl, aEmptyCell, rPos, *pTokArr, nMatch);
928 }
929 }
930
931 return bOk;
932}
933
934bool ScValidationData::IsEqualToTokenArray( ScRefCellValue& rCell, const ScAddress& rPos, const ScTokenArray& rTokArr ) const
935{
936 // create a condition entry that tests on equality and set the passed token array
937 ScConditionEntry aCondEntry( ScConditionMode::Equal, &rTokArr, nullptr, *GetDocument(), rPos );
938 return aCondEntry.IsCellValid(rCell, rPos);
939}
940
942{
943 bool bIsValid = false;
944
945 /* Compare input cell with all supported tokens from the formula.
946 Currently a formula may contain:
947 1) A list of strings (at least one string).
948 2) A single cell or range reference.
949 3) A single defined name (must contain a cell/range reference, another
950 name, or DB range, or a formula resulting in a cell/range reference
951 or matrix/array).
952 4) A single database range.
953 5) A formula resulting in a cell/range reference or matrix/array.
954 */
955
956 std::unique_ptr< ScTokenArray > pTokArr( CreateFlatCopiedTokenArray( 0 ) );
957
958 // *** try if formula is a string list ***
959
961 sal_uInt32 nFormat = lclGetCellFormat( *GetDocument(), rPos );
962 ScStringTokenIterator aIt( *pTokArr );
963 for (rtl_uString* pString = aIt.First(); pString && aIt.Ok(); pString = aIt.Next())
964 {
965 /* Do not break the loop, if a valid string has been found.
966 This is to find invalid tokens following in the formula. */
967 if( !bIsValid )
968 {
969 // create a formula containing a single string or number
970 ScTokenArray aCondTokArr(*GetDocument());
971 double fValue;
972 OUString aStr(pString);
973 if (GetDocument()->GetFormatTable()->IsNumberFormat(aStr, nFormat, fValue))
974 aCondTokArr.AddDouble( fValue );
975 else
976 aCondTokArr.AddString(rSPool.intern(aStr));
977
978 bIsValid = IsEqualToTokenArray(rCell, rPos, aCondTokArr);
979 }
980 }
981
982 if( !aIt.Ok() )
983 bIsValid = false;
984
985 // *** if not a string list, try if formula results in a cell range or
986 // anything else we recognize as valid ***
987
988 if (!bIsValid)
989 {
990 int nMatch;
991 bIsValid = GetSelectionFromFormula(nullptr, rCell, rPos, *pTokArr, nMatch);
992 bIsValid = bIsValid && nMatch >= 0;
993 }
994
995 return bIsValid;
996}
997
999{
1000 // for Ref-Undo - real copy with new tokens!
1001
1002 for (const auto& rxItem : rList)
1003 {
1004 InsertNew( std::unique_ptr<ScValidationData>(rxItem->Clone()) );
1005 }
1006
1007 //TODO: faster insert for sorted entries from rList ???
1008}
1009
1011 const ScValidationDataList& rList)
1012{
1013 // for new documents - real copy with new tokens!
1014
1015 for (const auto& rxItem : rList)
1016 {
1017 InsertNew( std::unique_ptr<ScValidationData>(rxItem->Clone(&rNewDoc)) );
1018 }
1019
1020 //TODO: faster insert for sorted entries from rList ???
1021}
1022
1024{
1025 //TODO: binary search
1026
1027 for( iterator it = begin(); it != end(); ++it )
1028 if( (*it)->GetKey() == nKey )
1029 return it->get();
1030
1031 OSL_FAIL("ScValidationDataList: Entry not found");
1032 return nullptr;
1033}
1034
1036{
1037 for( iterator it = begin(); it != end(); ++it )
1038 (*it)->CompileXML();
1039}
1040
1042{
1043 for( iterator it = begin(); it != end(); ++it )
1044 (*it)->UpdateReference(rCxt);
1045}
1046
1048{
1049 for (iterator it = begin(); it != end(); ++it)
1050 (*it)->UpdateInsertTab(rCxt);
1051}
1052
1054{
1055 for (iterator it = begin(); it != end(); ++it)
1056 (*it)->UpdateDeleteTab(rCxt);
1057}
1058
1060{
1061 for (iterator it = begin(); it != end(); ++it)
1062 (*it)->UpdateMoveTab(rCxt);
1063}
1064
1066{
1067 return maData.begin();
1068}
1069
1071{
1072 return maData.begin();
1073}
1074
1076{
1077 return maData.end();
1078}
1079
1081{
1082 return maData.end();
1083}
1084
1085/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
const PropertyValue * pValues
AnnotationVector::iterator maIter
XPropertyListType t
const char * pName
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:44
SfxApplication * SfxGetpApp()
static weld::MessageDialog * CreateMessageDialog(weld::Widget *pParent, VclMessageType eMessageType, VclButtonsType eButtonType, const OUString &rPrimaryMessage, bool bMobile=false)
const SbxObject * GetParent() const
const OUString & GetName(SbxNameType=SbxNameType::NONE) const
SCTAB Tab() const
Definition: address.hxx:283
SC_DLLPUBLIC void Format(OStringBuffer &r, ScRefFlags nFlags, const ScDocument *pDocument=nullptr, const Details &rDetails=detailsOOOa1) const
Definition: address.cxx:2074
SCROW Row() const
Definition: address.hxx:274
SCCOL Col() const
Definition: address.hxx:279
ScDocument * GetDocument() const
Definition: conditio.hxx:412
std::unique_ptr< ScTokenArray > CreateFlatCopiedTokenArray(sal_uInt16 nPos) const
Create a flat copy using ScTokenArray copy-ctor with shared tokens.
Definition: conditio.cxx:1285
bool IsIgnoreBlank() const
Definition: conditio.hxx:370
bool IsCellValid(ScRefCellValue &rCell, const ScAddress &rPos) const
Definition: conditio.cxx:1223
ScDBData * findByIndex(sal_uInt16 nIndex)
Definition: dbdata.cxx:1189
NamedDBs & getNamedDBs()
Definition: dbdata.hxx:316
SC_DLLPUBLIC ScPatternAttr * GetDefPattern() const
Definition: document.cxx:6158
SC_DLLPUBLIC formula::FormulaGrammar::AddressConvention GetAddressConvention() const
Definition: documen3.cxx:494
ScRangeData * FindRangeNameBySheetAndIndex(SCTAB nTab, sal_uInt16 nIndex) const
Find a named expression / range name in either global or a local scope.
Definition: documen3.cxx:269
bool IsInLinkUpdate() const
Definition: documen8.cxx:784
void SetInLinkUpdate(bool bSet)
Definition: documen8.cxx:776
SC_DLLPUBLIC OUString GetInputString(SCCOL nCol, SCROW nRow, SCTAB nTab, bool bForceSystemLocale=false) const
Definition: document.cxx:3590
SC_DLLPUBLIC ScDBCollection * GetDBCollection() const
Definition: document.hxx:826
SfxObjectShell * GetDocumentShell() const
Definition: document.hxx:1081
SC_DLLPUBLIC svl::SharedStringPool & GetSharedStringPool()
Definition: documen2.cxx:585
SC_DLLPUBLIC SvNumberFormatter * GetFormatTable() const
Definition: documen2.cxx:460
void LockTable(SCTAB nTab)
Definition: document.cxx:5409
void UnlockTable(SCTAB nTab)
Definition: document.cxx:5419
SC_DLLPUBLIC const ScPatternAttr * GetPattern(SCCOL nCol, SCROW nRow, SCTAB nTab) const
Definition: document.cxx:4806
static SC_DLLPUBLIC OUString GetString(const EditTextObject &rEditText, const ScDocument *pDoc)
Retrieves string with paragraphs delimited by new lines (' ').
Definition: editutil.cxx:119
ScDocument * mpDoc
Definition: conditio.hxx:255
bool operator==(const ScFormatEntry &) const
Definition: conditio.cxx:55
double GetValue()
const svl::SharedString & GetString()
FormulaError GetErrCode()
const ScMatrix * GetMatrix()
ScAddress aPos
bool Interpret(SCROW nStartOffset=-1, SCROW nEndOffset=-1)
static OUString GetErrorString(FormulaError nErrNumber)
Definition: global.cxx:311
Matrix data type that can store values of mixed types.
Definition: scmatrix.hxx:101
static bool IsNonValueType(ScMatValType nType)
String, empty or empty path, but not value nor boolean.
Definition: scmatrix.hxx:178
sal_uInt32 GetNumberFormat(SvNumberFormatter *) const
Definition: patattr.cxx:1301
ScAddress aStart
Definition: address.hxx:497
bool IsValidReference(ScRange &rRange, const ScAddress &rPos) const
Exactly and only one valid range (no #REF!s)
Definition: token.cxx:1861
ScValidationDataListDataType::iterator iterator
Definition: validat.hxx:215
ScValidationDataListDataType maData
Definition: validat.hxx:208
void UpdateDeleteTab(sc::RefUpdateDeleteTabContext &rCxt)
Definition: validat.cxx:1053
void UpdateReference(sc::RefUpdateContext &rCxt)
Definition: validat.cxx:1041
void UpdateMoveTab(sc::RefUpdateMoveTabContext &rCxt)
Definition: validat.cxx:1059
void InsertNew(std::unique_ptr< ScValidationData > pNew)
Definition: validat.hxx:223
void UpdateInsertTab(sc::RefUpdateInsertTabContext &rCxt)
Definition: validat.cxx:1047
ScValidationData * GetData(sal_uInt32 nKey)
Definition: validat.cxx:1023
ScValidationDataListDataType::const_iterator const_iterator
Definition: validat.hxx:216
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:941
sal_Int16 mnListType
Definition: validat.hxx:69
bool IsEmpty() const
Definition: validat.cxx:128
bool IsDataValidCustom(const OUString &rTest, const ScPatternAttr &rPattern, const ScAddress &rPos, const CustomValidationPrivateAccess &) const
Definition: validat.cxx:426
OUString aErrorTitle
Definition: validat.hxx:72
bool IsDataValidTextLen(const OUString &rTest, const ScAddress &rPos, ScValidationDataIsNumeric *pDataNumeric) const
Tests, if string or numeric data has valid text length.
Definition: validat.cxx:493
OUString aInputTitle
Definition: validat.hxx:70
bool DoScript(const ScAddress &rPos, const OUString &rInput, ScFormulaCell *pCell, weld::Window *pParent) const
Definition: validat.cxx:185
ScValidationData(ScValidationMode eMode, ScConditionMode eOper, const OUString &rExpr1, const OUString &rExpr2, ScDocument &rDocument, const ScAddress &rPos, const OUString &rExprNmsp1=OUString(), const OUString &rExprNmsp2=OUString(), formula::FormulaGrammar::Grammar eGrammar1=formula::FormulaGrammar::GRAM_DEFAULT, formula::FormulaGrammar::Grammar eGrammar2=formula::FormulaGrammar::GRAM_DEFAULT)
Definition: validat.cxx:62
bool IsDataValid(const OUString &rTest, const ScPatternAttr &rPattern, const ScAddress &rPos) const
Definition: validat.cxx:516
ScValidationMode GetDataMode() const
Definition: validat.hxx:114
ScValidationMode eDataMode
Definition: validat.hxx:65
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:706
ScValidErrorStyle eErrorStyle
Definition: validat.hxx:68
bool EqualEntries(const ScValidationData &r) const
Definition: validat.cxx:134
void DoCalcError(ScFormulaCell *pCell) const
Definition: validat.cxx:363
OUString aErrorMessage
Definition: validat.hxx:73
bool FillSelectionList(std::vector< ScTypedStrData > &rStrings, const ScAddress &rPos) const
Tries to fill the passed collection with list validation entries.
Definition: validat.cxx:898
bool GetErrMsg(OUString &rTitle, OUString &rMsg, ScValidErrorStyle &rStyle) const
Definition: validat.cxx:176
void SetInput(const OUString &rTitle, const OUString &rMsg)
Definition: validat.cxx:160
bool HasSelectionList() const
Returns true, if the validation cell will show a selection list.
Definition: validat.cxx:701
void ResetInput()
Definition: validat.cxx:150
virtual ~ScValidationData() override
Definition: validat.cxx:124
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:934
void SetError(const OUString &rTitle, const OUString &rMsg, ScValidErrorStyle eStyle)
Definition: validat.cxx:167
void ResetError()
Definition: validat.cxx:155
bool DoMacro(const ScAddress &rPos, const OUString &rInput, ScFormulaCell *pCell, weld::Window *pParent) const
Definition: validat.cxx:259
OUString aInputMessage
Definition: validat.hxx:71
bool DoError(weld::Window *pParent, const OUString &rInput, const ScAddress &rPos) const
Definition: validat.cxx:371
static bool IsXScriptURL(const OUString &rScriptURL)
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)
StarBASIC * GetBasic() const
ErrCode CallBasic(std::u16string_view rMacro, std::u16string_view rBasicName, SbxArray *pArgs, SbxValue *pRet=nullptr)
const OUString & GetName() const
bool isLOKMobilePhone() const
static SfxViewShell * Current()
virtual SbxVariable * Find(const OUString &, SbxClassType) override
void GetInputLineString(const double &fOutNumber, sal_uInt32 nFIndex, OUString &rOutString, bool bFiltering=false, bool bForceSystemLocale=false)
bool IsNumberFormat(const OUString &sString, sal_uInt32 &F_Index, double &fOutNumber, SvNumInputOptions eInputOptions=SvNumInputOptions::NONE)
sal_uInt16 GetLen() const
FormulaToken * AddDouble(double fVal)
FormulaToken * AddString(const svl::SharedString &rStr)
OpCode GetOpCode() const
StackVar GetType() const
virtual const svl::SharedString & GetString() const
SharedString intern(const OUString &rStr)
OUString getString() const
T * get() const
ScConditionMode
Definition: conditio.hxx:60
EmbeddedObjectRef * pObject
#define ERRCODE_NONE
FormulaError
DocumentType eType
sal_Int16 nValue
@ CELLTYPE_EDIT
Definition: global.hxx:275
@ CELLTYPE_STRING
Definition: global.hxx:273
@ CELLTYPE_FORMULA
Definition: global.hxx:274
@ CELLTYPE_VALUE
Definition: global.hxx:272
Mode eMode
sal_Int64 n
aStr
svIndex
OpCode
ocTableRef
ocDBArea
ocName
#define ERRCODE_BASIC_METHOD_NOT_FOUND
SbxBOOL
OUString ScResId(TranslateId aId)
Definition: scdll.cxx:90
sal_uIntPtr sal_uLong
Store arbitrary cell value of any kind.
Definition: cellvalue.hxx:32
void commit(ScDocument &rDoc, const ScAddress &rPos) const
Set cell value at specified position in specified document.
Definition: cellvalue.cxx:426
Try NOT to use this struct.
Definition: scmatrix.hxx:53
ScMatValType nType
Definition: scmatrix.hxx:56
FormulaError GetError() const
Only valid if ScMatrix methods indicate that this is no string!
Definition: scmatrix.hxx:62
double fVal
Definition: scmatrix.hxx:54
const svl::SharedString & GetString() const
Only valid if ScMatrix methods indicate so!
Definition: scmatrix.hxx:59
This is very similar to ScCellValue, except that it references the original value instead of copying ...
Definition: cellvalue.hxx:108
ScFormulaCell * getFormula() const
Definition: cellvalue.hxx:137
const EditTextObject * getEditText() const
Definition: cellvalue.hxx:136
double getDouble() const
Definition: cellvalue.hxx:134
bool isEmpty() const
Definition: cellvalue.cxx:667
void commit(ScDocument &rDoc, const ScAddress &rPos) const
Set cell value at specified position in specified document.
Definition: cellvalue.cxx:589
const svl::SharedString * getSharedString() const
Definition: cellvalue.hxx:135
CellType getType() const
Definition: cellvalue.hxx:133
To test numeric data text length in IsDataValidTextLen().
Definition: validat.cxx:475
ScValidationDataIsNumeric(double fVal, SvNumberFormatter *pFormatter=nullptr, sal_uInt32 nFormat=0)
Definition: validat.cxx:480
SvNumberFormatter * mpFormatter
Definition: validat.cxx:476
void init(const ScDocument &rDoc, const ScAddress &rPos)
Definition: validat.cxx:485
Context for reference update during shifting, moving or copying of cell ranges.
sal_Int16 SCCOL
Definition: types.hxx:21
::boost::intrusive_ptr< ScMatrix > ScMatrixRef
Definition: types.hxx:25
sal_Int32 SCROW
Definition: types.hxx:17
ScValidationMode
Definition: validat.hxx:39
@ SC_VALID_TEXTLEN
Definition: validat.hxx:45
@ SC_VALID_TIME
Definition: validat.hxx:44
@ SC_VALID_CUSTOM
Definition: validat.hxx:47
@ SC_VALID_ANY
Definition: validat.hxx:40
@ SC_VALID_DECIMAL
Definition: validat.hxx:42
@ SC_VALID_WHOLE
Definition: validat.hxx:41
@ SC_VALID_DATE
Definition: validat.hxx:43
@ SC_VALID_LIST
Definition: validat.hxx:46
ScValidErrorStyle
Definition: validat.hxx:51
@ SC_VALERR_MACRO
Definition: validat.hxx:55
@ SC_VALERR_STOP
Definition: validat.hxx:52
@ SC_VALERR_WARNING
Definition: validat.hxx:53
@ SC_VALERR_INFO
Definition: validat.hxx:54
RET_OK
RET_CANCEL
VclMessageType
VclButtonsType