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