xref: /aoo41x/main/sc/source/core/tool/interpr1.cxx (revision f53782eb)
1 /**************************************************************
2  *
3  * Licensed to the Apache Software Foundation (ASF) under one
4  * or more contributor license agreements.  See the NOTICE file
5  * distributed with this work for additional information
6  * regarding copyright ownership.  The ASF licenses this file
7  * to you under the Apache License, Version 2.0 (the
8  * "License"); you may not use this file except in compliance
9  * with the License.  You may obtain a copy of the License at
10  *
11  *   http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing,
14  * software distributed under the License is distributed on an
15  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
16  * KIND, either express or implied.  See the License for the
17  * specific language governing permissions and limitations
18  * under the License.
19  *
20  *************************************************************/
21 
22 
23 
24 // MARKER(update_precomp.py): autogen include statement, do not remove
25 #include "precompiled_sc.hxx"
26 
27 // INCLUDE ---------------------------------------------------------------
28 
29 #include "scitems.hxx"
30 #include <editeng/langitem.hxx>
31 #include <svx/algitem.hxx>
32 #include <unotools/textsearch.hxx>
33 #include <svl/zforlist.hxx>
34 #include <svl/zformat.hxx>
35 #include <tools/urlobj.hxx>
36 #include <unotools/charclass.hxx>
37 #include <sfx2/docfile.hxx>
38 #include <sfx2/printer.hxx>
39 #include <unotools/collatorwrapper.hxx>
40 #include <unotools/transliterationwrapper.hxx>
41 #include <rtl/ustring.hxx>
42 #include <rtl/logfile.hxx>
43 
44 #include "interpre.hxx"
45 #include "patattr.hxx"
46 #include "global.hxx"
47 #include "document.hxx"
48 #include "dociter.hxx"
49 #include "cell.hxx"
50 #include "scmatrix.hxx"
51 #include "docoptio.hxx"
52 #include "globstr.hrc"
53 #include "attrib.hxx"
54 #include "jumpmatrix.hxx"
55 
56 #ifndef _COMPHELPER_PROCESSFACTORY_HXX_
57 #include <comphelper/processfactory.hxx>
58 #endif
59 
60 #include <stdlib.h>
61 #include <string.h>
62 #include <math.h>
63 #include <vector>
64 #include <memory>
65 #include "cellkeytranslator.hxx"
66 #include "lookupcache.hxx"
67 #include "rangenam.hxx"
68 #include "compiler.hxx"
69 #include "externalrefmgr.hxx"
70 #include "doubleref.hxx"
71 #include "queryparam.hxx"
72 
73 #define SC_DOUBLE_MAXVALUE  1.7e307
74 
75 IMPL_FIXEDMEMPOOL_NEWDEL( ScTokenStack, 8, 4 )
76 IMPL_FIXEDMEMPOOL_NEWDEL( ScInterpreter, 32, 16 )
77 
78 ScTokenStack* ScInterpreter::pGlobalStack = NULL;
79 sal_Bool ScInterpreter::bGlobalStackInUse = sal_False;
80 
81 using namespace formula;
82 using ::std::auto_ptr;
83 
84 //-----------------------------------------------------------------------------
85 // Funktionen
86 //-----------------------------------------------------------------------------
87 
88 
89 void ScInterpreter::ScIfJump()
90 {
91     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIfJump" );
92     const short* pJump = pCur->GetJump();
93     short nJumpCount = pJump[ 0 ];
94     MatrixDoubleRefToMatrix();
95     switch ( GetStackType() )
96     {
97         case svMatrix:
98         {
99             ScMatrixRef pMat = PopMatrix();
100             if ( !pMat )
101                 PushIllegalParameter();
102             else
103             {
104                 FormulaTokenRef xNew;
105                 ScTokenMatrixMap::const_iterator aMapIter;
106                 // DoubleError handled by JumpMatrix
107                 pMat->SetErrorInterpreter( NULL);
108                 SCSIZE nCols, nRows;
109                 pMat->GetDimensions( nCols, nRows );
110                 if ( nCols == 0 || nRows == 0 )
111                     PushIllegalArgument();
112                 else if (pTokenMatrixMap && ((aMapIter = pTokenMatrixMap->find(
113                                     pCur)) != pTokenMatrixMap->end()))
114                     xNew = (*aMapIter).second;
115                 else
116                 {
117                     ScJumpMatrix* pJumpMat = new ScJumpMatrix( nCols, nRows );
118                     for ( SCSIZE nC=0; nC < nCols; ++nC )
119                     {
120                         for ( SCSIZE nR=0; nR < nRows; ++nR )
121                         {
122                             double fVal;
123                             bool bTrue;
124                             ScMatValType nType = 0;
125                             const ScMatrixValue* pMatVal = pMat->Get( nC, nR,
126                                     nType);
127                             bool bIsValue = ScMatrix::IsValueType( nType);
128                             if ( bIsValue )
129                             {
130                                 fVal = pMatVal->fVal;
131                                 bIsValue = ::rtl::math::isFinite( fVal );
132                                 bTrue = bIsValue && (fVal != 0.0);
133                                 if ( bTrue )
134                                     fVal = 1.0;
135                             }
136                             else
137                             {
138                                 // Treat empty and empty path as 0, but string
139                                 // as error.
140                                 bIsValue = !ScMatrix::IsRealStringType( nType);
141                                 bTrue = false;
142                                 fVal = (bIsValue ? 0.0 : CreateDoubleError( errNoValue));
143                             }
144                             if ( bTrue )
145                             {   // TRUE
146                                 if( nJumpCount >= 2 )
147                                 {   // THEN path
148                                     pJumpMat->SetJump( nC, nR, fVal,
149                                             pJump[ 1 ],
150                                             pJump[ nJumpCount ]);
151                                 }
152                                 else
153                                 {   // no parameter given for THEN
154                                     pJumpMat->SetJump( nC, nR, fVal,
155                                             pJump[ nJumpCount ],
156                                             pJump[ nJumpCount ]);
157                                 }
158                             }
159                             else
160                             {   // FALSE
161                                 if( nJumpCount == 3 && bIsValue )
162                                 {   // ELSE path
163                                     pJumpMat->SetJump( nC, nR, fVal,
164                                             pJump[ 2 ],
165                                             pJump[ nJumpCount ]);
166                                 }
167                                 else
168                                 {   // no parameter given for ELSE,
169                                     // or DoubleError
170                                     pJumpMat->SetJump( nC, nR, fVal,
171                                             pJump[ nJumpCount ],
172                                             pJump[ nJumpCount ]);
173                                 }
174                             }
175                         }
176                     }
177                     xNew = new ScJumpMatrixToken( pJumpMat );
178                     GetTokenMatrixMap().insert( ScTokenMatrixMap::value_type(pCur, xNew));
179                 }
180                 PushTempToken( xNew);
181                 // set endpoint of path for main code line
182                 aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] );
183             }
184         }
185         break;
186         default:
187         {
188             if ( GetBool() )
189             {   // TRUE
190                 if( nJumpCount >= 2 )
191                 {   // THEN path
192                     aCode.Jump( pJump[ 1 ], pJump[ nJumpCount ] );
193                 }
194                 else
195                 {   // no parameter given for THEN
196                     nFuncFmtType = NUMBERFORMAT_LOGICAL;
197                     PushInt(1);
198                     aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] );
199                 }
200             }
201             else
202             {   // FALSE
203                 if( nJumpCount == 3 )
204                 {   // ELSE path
205                     aCode.Jump( pJump[ 2 ], pJump[ nJumpCount ] );
206                 }
207                 else
208                 {   // no parameter given for ELSE
209                     nFuncFmtType = NUMBERFORMAT_LOGICAL;
210                     PushInt(0);
211                     aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] );
212                 }
213             }
214         }
215     }
216 }
217 
218 
219 void ScInterpreter::ScChoseJump()
220 {
221     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScChoseJump" );
222     // We have to set a jump, if there was none chosen because of an error set
223     // it to endpoint.
224     bool bHaveJump = false;
225     const short* pJump = pCur->GetJump();
226     short nJumpCount = pJump[ 0 ];
227     MatrixDoubleRefToMatrix();
228     switch ( GetStackType() )
229     {
230         case svMatrix:
231         {
232             ScMatrixRef pMat = PopMatrix();
233             if ( !pMat )
234                 PushIllegalParameter();
235             else
236             {
237                 FormulaTokenRef xNew;
238                 ScTokenMatrixMap::const_iterator aMapIter;
239                 // DoubleError handled by JumpMatrix
240                 pMat->SetErrorInterpreter( NULL);
241                 SCSIZE nCols, nRows;
242                 pMat->GetDimensions( nCols, nRows );
243                 if ( nCols == 0 || nRows == 0 )
244                     PushIllegalParameter();
245                 else if (pTokenMatrixMap && ((aMapIter = pTokenMatrixMap->find(
246                                     pCur)) != pTokenMatrixMap->end()))
247                     xNew = (*aMapIter).second;
248                 else
249                 {
250                     ScJumpMatrix* pJumpMat = new ScJumpMatrix( nCols, nRows );
251                     for ( SCSIZE nC=0; nC < nCols; ++nC )
252                     {
253                         for ( SCSIZE nR=0; nR < nRows; ++nR )
254                         {
255                             double fVal;
256                             ScMatValType nType;
257                             const ScMatrixValue* pMatVal = pMat->Get( nC, nR,
258                                     nType);
259                             bool bIsValue = ScMatrix::IsValueType( nType);
260                             if ( bIsValue )
261                             {
262                                 fVal = pMatVal->fVal;
263                                 bIsValue = ::rtl::math::isFinite( fVal );
264                                 if ( bIsValue )
265                                 {
266                                     fVal = ::rtl::math::approxFloor( fVal);
267                                     if ( (fVal < 1) || (fVal >= nJumpCount))
268                                     {
269                                         bIsValue = sal_False;
270                                         fVal = CreateDoubleError(
271                                                 errIllegalArgument);
272                                     }
273                                 }
274                             }
275                             else
276                             {
277                                 fVal = CreateDoubleError( errNoValue);
278                             }
279                             if ( bIsValue )
280                             {
281                                 pJumpMat->SetJump( nC, nR, fVal,
282                                         pJump[ (short)fVal ],
283                                         pJump[ nJumpCount ]);
284                             }
285                             else
286                             {
287                                 pJumpMat->SetJump( nC, nR, fVal,
288                                         pJump[ nJumpCount ],
289                                         pJump[ nJumpCount ]);
290                             }
291                         }
292                     }
293                     xNew = new ScJumpMatrixToken( pJumpMat );
294                     GetTokenMatrixMap().insert( ScTokenMatrixMap::value_type(
295                                 pCur, xNew));
296                 }
297                 PushTempToken( xNew);
298                 // set endpoint of path for main code line
299                 aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] );
300                 bHaveJump = true;
301             }
302         }
303         break;
304         default:
305         {
306             double nJumpIndex = ::rtl::math::approxFloor( GetDouble() );
307             if (!nGlobalError && (nJumpIndex >= 1) && (nJumpIndex < nJumpCount))
308             {
309                 aCode.Jump( pJump[ (short) nJumpIndex ], pJump[ nJumpCount ] );
310                 bHaveJump = true;
311             }
312             else
313                 PushIllegalArgument();
314         }
315     }
316     if (!bHaveJump)
317         aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] );
318 }
319 
320 void lcl_AdjustJumpMatrix( ScJumpMatrix* pJumpM, ScMatrixRef& pResMat, SCSIZE nParmCols, SCSIZE nParmRows )
321 {
322     SCSIZE nJumpCols, nJumpRows;
323     SCSIZE nResCols, nResRows;
324     SCSIZE nAdjustCols, nAdjustRows;
325     pJumpM->GetDimensions( nJumpCols, nJumpRows );
326     pJumpM->GetResMatDimensions( nResCols, nResRows );
327     if (( nJumpCols == 1 && nParmCols > nResCols ) ||
328         ( nJumpRows == 1 && nParmRows > nResRows ))
329     {
330         if ( nJumpCols == 1 && nJumpRows == 1 )
331         {
332             nAdjustCols = nParmCols > nResCols ? nParmCols : nResCols;
333             nAdjustRows = nParmRows > nResRows ? nParmRows : nResRows;
334         }
335         else if ( nJumpCols == 1 )
336         {
337             nAdjustCols = nParmCols;
338             nAdjustRows = nResRows;
339         }
340         else
341         {
342             nAdjustCols = nResCols;
343             nAdjustRows = nParmRows;
344         }
345         pJumpM->SetNewResMat( nAdjustCols, nAdjustRows );
346         pResMat = pJumpM->GetResultMatrix();
347     }
348 }
349 
350 bool ScInterpreter::JumpMatrix( short nStackLevel )
351 {
352     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::JumpMatrix" );
353     pJumpMatrix = static_cast<ScToken*>(pStack[sp-nStackLevel])->GetJumpMatrix();
354     ScMatrixRef pResMat = pJumpMatrix->GetResultMatrix();
355     SCSIZE nC, nR;
356     if ( nStackLevel == 2 )
357     {
358         if ( aCode.HasStacked() )
359             aCode.Pop();    // pop what Jump() pushed
360         else
361         {
362             DBG_ERRORFILE( "ScInterpreter::JumpMatrix: pop goes the weasel" );
363         }
364 
365         if ( !pResMat )
366         {
367             Pop();
368             SetError( errUnknownStackVariable );
369         }
370         else
371         {
372             pJumpMatrix->GetPos( nC, nR );
373             switch ( GetStackType() )
374             {
375                 case svDouble:
376                 {
377                     double fVal = GetDouble();
378                     if ( nGlobalError )
379                     {
380                         fVal = CreateDoubleError( nGlobalError );
381                         nGlobalError = 0;
382                     }
383                     pResMat->PutDouble( fVal, nC, nR );
384                 }
385                 break;
386                 case svString:
387                 {
388                     const String& rStr = GetString();
389                     if ( nGlobalError )
390                     {
391                         pResMat->PutDouble( CreateDoubleError( nGlobalError),
392                                 nC, nR);
393                         nGlobalError = 0;
394                     }
395                     else
396                         pResMat->PutString( rStr, nC, nR );
397                 }
398                 break;
399                 case svSingleRef:
400                 {
401                     ScAddress aAdr;
402                     PopSingleRef( aAdr );
403                     if ( nGlobalError )
404                     {
405                         pResMat->PutDouble( CreateDoubleError( nGlobalError),
406                                 nC, nR);
407                         nGlobalError = 0;
408                     }
409                     else
410                     {
411                         ScBaseCell* pCell = GetCell( aAdr );
412                         if (HasCellEmptyData( pCell))
413                             pResMat->PutEmpty( nC, nR );
414                         else if (HasCellValueData( pCell))
415                         {
416                             double fVal = GetCellValue( aAdr, pCell);
417                             if ( nGlobalError )
418                             {
419                                 fVal = CreateDoubleError(
420                                         nGlobalError);
421                                 nGlobalError = 0;
422                             }
423                             pResMat->PutDouble( fVal, nC, nR );
424                         }
425                         else
426                         {
427                             String aStr;
428                             GetCellString( aStr, pCell );
429                             if ( nGlobalError )
430                             {
431                                 pResMat->PutDouble( CreateDoubleError(
432                                             nGlobalError), nC, nR);
433                                 nGlobalError = 0;
434                             }
435                             else
436                                 pResMat->PutString( aStr, nC, nR);
437                         }
438                     }
439                 }
440                 break;
441                 case svDoubleRef:
442                 {   // upper left plus offset within matrix
443                     double fVal;
444                     ScRange aRange;
445                     PopDoubleRef( aRange );
446                     if ( nGlobalError )
447                     {
448                         fVal = CreateDoubleError( nGlobalError );
449                         nGlobalError = 0;
450                         pResMat->PutDouble( fVal, nC, nR );
451                     }
452                     else
453                     {
454                         // Do not modify the original range because we use it
455                         // to adjust the size of the result matrix if necessary.
456                         ScAddress aAdr( aRange.aStart);
457                         sal_uLong nCol = (sal_uLong)aAdr.Col() + nC;
458                         sal_uLong nRow = (sal_uLong)aAdr.Row() + nR;
459                         if ((nCol > static_cast<sal_uLong>(aRange.aEnd.Col()) &&
460                                     aRange.aEnd.Col() != aRange.aStart.Col())
461                                 || (nRow > static_cast<sal_uLong>(aRange.aEnd.Row()) &&
462                                     aRange.aEnd.Row() != aRange.aStart.Row()))
463                           {
464                             fVal = CreateDoubleError( NOTAVAILABLE );
465                             pResMat->PutDouble( fVal, nC, nR );
466                           }
467                           else
468                           {
469                             // Replicate column and/or row of a vector if it is
470                             // one. Note that this could be a range reference
471                             // that in fact consists of only one cell, e.g. A1:A1
472                             if (aRange.aEnd.Col() == aRange.aStart.Col())
473                                 nCol = aRange.aStart.Col();
474                             if (aRange.aEnd.Row() == aRange.aStart.Row())
475                                 nRow = aRange.aStart.Row();
476                             aAdr.SetCol( static_cast<SCCOL>(nCol) );
477                             aAdr.SetRow( static_cast<SCROW>(nRow) );
478                             ScBaseCell* pCell = GetCell( aAdr );
479                             if (HasCellEmptyData( pCell))
480                                 pResMat->PutEmpty( nC, nR );
481                             else if (HasCellValueData( pCell))
482                               {
483                                 double fCellVal = GetCellValue( aAdr, pCell);
484                                 if ( nGlobalError )
485                                 {
486                                     fCellVal = CreateDoubleError(
487                                             nGlobalError);
488                                     nGlobalError = 0;
489                                 }
490                                 pResMat->PutDouble( fCellVal, nC, nR );
491                               }
492                               else
493                             {
494                                 String aStr;
495                                 GetCellString( aStr, pCell );
496                                 if ( nGlobalError )
497                                 {
498                                     pResMat->PutDouble( CreateDoubleError(
499                                                 nGlobalError), nC, nR);
500                                     nGlobalError = 0;
501                                 }
502                                 else
503                                     pResMat->PutString( aStr, nC, nR );
504                             }
505                           }
506                         SCSIZE nParmCols = aRange.aEnd.Col() - aRange.aStart.Col() + 1;
507                         SCSIZE nParmRows = aRange.aEnd.Row() - aRange.aStart.Row() + 1;
508                         lcl_AdjustJumpMatrix( pJumpMatrix, pResMat, nParmCols, nParmRows );
509                     }
510                 }
511                 break;
512                 case svMatrix:
513                 {   // match matrix offsets
514                     double fVal;
515                     ScMatrixRef pMat = PopMatrix();
516                     if ( nGlobalError )
517                     {
518                         fVal = CreateDoubleError( nGlobalError );
519                         nGlobalError = 0;
520                         pResMat->PutDouble( fVal, nC, nR );
521                     }
522                     else if ( !pMat )
523                     {
524                         fVal = CreateDoubleError( errUnknownVariable );
525                         pResMat->PutDouble( fVal, nC, nR );
526                     }
527                     else
528                     {
529                         SCSIZE nCols, nRows;
530                         pMat->GetDimensions( nCols, nRows );
531                         if ((nCols <= nC && nCols != 1) ||
532                             (nRows <= nR && nRows != 1))
533                         {
534                             fVal = CreateDoubleError( NOTAVAILABLE );
535                             pResMat->PutDouble( fVal, nC, nR );
536                         }
537                         else
538                         {
539                             if ( pMat->IsValue( nC, nR ) )
540                             {
541                                 fVal = pMat->GetDouble( nC, nR );
542                                 pResMat->PutDouble( fVal, nC, nR );
543                             }
544                             else if ( pMat->IsEmpty( nC, nR ) )
545                                 pResMat->PutEmpty( nC, nR );
546                             else
547                             {
548                                 const String& rStr = pMat->GetString( nC, nR );
549                                 pResMat->PutString( rStr, nC, nR );
550                             }
551                         }
552                         lcl_AdjustJumpMatrix( pJumpMatrix, pResMat, nCols, nRows );
553                     }
554                 }
555                 break;
556                 case svError:
557                 {
558                     PopError();
559                     double fVal = CreateDoubleError( nGlobalError);
560                     nGlobalError = 0;
561                     pResMat->PutDouble( fVal, nC, nR );
562                 }
563                 break;
564                 default:
565                 {
566                     Pop();
567                     double fVal = CreateDoubleError( errIllegalArgument);
568                     pResMat->PutDouble( fVal, nC, nR );
569                 }
570             }
571         }
572     }
573     bool bCont = pJumpMatrix->Next( nC, nR );
574     if ( bCont )
575     {
576         double fBool;
577         short nStart, nNext, nStop;
578         pJumpMatrix->GetJump( nC, nR, fBool, nStart, nNext, nStop );
579         while ( bCont && nStart == nNext )
580         {   // push all results that have no jump path
581             if ( pResMat )
582             {
583                 // a sal_False without path results in an empty path value
584                 if ( fBool == 0.0 )
585                     pResMat->PutEmptyPath( nC, nR );
586                 else
587                     pResMat->PutDouble( fBool, nC, nR );
588             }
589             bCont = pJumpMatrix->Next( nC, nR );
590             if ( bCont )
591                 pJumpMatrix->GetJump( nC, nR, fBool, nStart, nNext, nStop );
592         }
593         if ( bCont && nStart != nNext )
594         {
595             const ScTokenVec* pParams = pJumpMatrix->GetJumpParameters();
596             if ( pParams )
597             {
598                 for ( ScTokenVec::const_iterator i = pParams->begin();
599                         i != pParams->end(); ++i )
600                 {
601                     // This is not the current state of the interpreter, so
602                     // push without error, and elements' errors are coded into
603                     // double.
604                     PushWithoutError( *(*i));
605                 }
606             }
607             aCode.Jump( nStart, nNext, nStop );
608         }
609     }
610     if ( !bCont )
611     {   // we're done with it, throw away jump matrix, keep result
612         pJumpMatrix = NULL;
613         Pop();
614         PushMatrix( pResMat );
615         // Remove jump matrix from map and remember result matrix in case it
616         // could be reused in another path of the same condition.
617         if (pTokenMatrixMap)
618         {
619             pTokenMatrixMap->erase( pCur);
620             pTokenMatrixMap->insert( ScTokenMatrixMap::value_type( pCur,
621                         pStack[sp-1]));
622         }
623         return true;
624     }
625     return false;
626 }
627 
628 
629 ScCompareOptions::ScCompareOptions( ScDocument* pDoc, const ScQueryEntry& rEntry, bool bReg ) :
630     aQueryEntry(rEntry),
631     bRegEx(bReg),
632     bMatchWholeCell(pDoc->GetDocOptions().IsMatchWholeCell()),
633     bIgnoreCase(true)
634 {
635     bRegEx = (bRegEx && (aQueryEntry.eOp == SC_EQUAL || aQueryEntry.eOp == SC_NOT_EQUAL));
636     // Interpreter functions usually are case insensitive, except the simple
637     // comparison operators, for which these options aren't used. Override in
638     // struct if needed.
639 }
640 
641 
642 double ScInterpreter::CompareFunc( const ScCompare& rComp, ScCompareOptions* pOptions )
643 {
644     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::CompareFunc" );
645     // Keep DoubleError if encountered
646     // #i40539# if bEmpty is set, bVal/nVal are uninitialized
647     if ( !rComp.bEmpty[0] && rComp.bVal[0] && !::rtl::math::isFinite( rComp.nVal[0]))
648         return rComp.nVal[0];
649     if ( !rComp.bEmpty[1] && rComp.bVal[1] && !::rtl::math::isFinite( rComp.nVal[1]))
650         return rComp.nVal[1];
651 
652     size_t nStringQuery = 0;    // 0:=no, 1:=0, 2:=1
653     double fRes = 0;
654     if ( rComp.bEmpty[ 0 ] )
655     {
656         if ( rComp.bEmpty[ 1 ] )
657             ;       // empty cell == empty cell, fRes 0
658         else if( rComp.bVal[ 1 ] )
659         {
660             if ( !::rtl::math::approxEqual( rComp.nVal[ 1 ], 0.0 ) )
661             {
662                 if ( rComp.nVal[ 1 ] < 0.0 )
663                     fRes = 1;       // empty cell > -x
664                 else
665                     fRes = -1;      // empty cell < x
666             }
667             // else: empty cell == 0.0
668         }
669         else
670         {
671             if ( rComp.pVal[ 1 ]->Len() )
672                 fRes = -1;      // empty cell < "..."
673             // else: empty cell == ""
674         }
675     }
676     else if ( rComp.bEmpty[ 1 ] )
677     {
678         if( rComp.bVal[ 0 ] )
679         {
680             if ( !::rtl::math::approxEqual( rComp.nVal[ 0 ], 0.0 ) )
681             {
682                 if ( rComp.nVal[ 0 ] < 0.0 )
683                     fRes = -1;      // -x < empty cell
684                 else
685                     fRes = 1;       // x > empty cell
686             }
687             // else: empty cell == 0.0
688         }
689         else
690         {
691             if ( rComp.pVal[ 0 ]->Len() )
692                 fRes = 1;       // "..." > empty cell
693             // else: "" == empty cell
694         }
695     }
696     else if( rComp.bVal[ 0 ] )
697     {
698         if( rComp.bVal[ 1 ] )
699         {
700             if ( !::rtl::math::approxEqual( rComp.nVal[ 0 ], rComp.nVal[ 1 ] ) )
701             {
702                 if( rComp.nVal[ 0 ] - rComp.nVal[ 1 ] < 0 )
703                     fRes = -1;
704                 else
705                     fRes = 1;
706             }
707         }
708         else
709         {
710             fRes = -1;          // number is less than string
711             nStringQuery = 2;   // 1+1
712         }
713     }
714     else if( rComp.bVal[ 1 ] )
715     {
716         fRes = 1;               // string is greater than number
717         nStringQuery = 1;       // 0+1
718     }
719     else
720     {
721         // Both strings.
722         if (pOptions)
723         {
724             // All similar to ScTable::ValidQuery(), *rComp.pVal[1] actually
725             // is/must be identical to *rEntry.pStr, which is essential for
726             // regex to work through GetSearchTextPtr().
727             ScQueryEntry& rEntry = pOptions->aQueryEntry;
728             DBG_ASSERT( *rComp.pVal[1] == *rEntry.pStr, "ScInterpreter::CompareFunc: broken options");
729             if (pOptions->bRegEx)
730             {
731                 xub_StrLen nStart = 0;
732                 xub_StrLen nStop  = rComp.pVal[0]->Len();
733                 bool bMatch = rEntry.GetSearchTextPtr(
734                         !pOptions->bIgnoreCase)->SearchFrwrd( *rComp.pVal[0],
735                             &nStart, &nStop);
736                 if (bMatch && pOptions->bMatchWholeCell && (nStart != 0 || nStop != rComp.pVal[0]->Len()))
737                     bMatch = false;     // RegEx must match entire string.
738                 fRes = (bMatch ? 0 : 1);
739             }
740             else if (rEntry.eOp == SC_EQUAL || rEntry.eOp == SC_NOT_EQUAL)
741             {
742                 ::utl::TransliterationWrapper* pTransliteration =
743                     (pOptions->bIgnoreCase ? ScGlobal::GetpTransliteration() :
744                      ScGlobal::GetCaseTransliteration());
745                 bool bMatch;
746                 if (pOptions->bMatchWholeCell)
747                     bMatch = pTransliteration->isEqual( *rComp.pVal[0], *rComp.pVal[1]);
748                 else
749                 {
750                     String aCell( pTransliteration->transliterate(
751                                 *rComp.pVal[0], ScGlobal::eLnge, 0,
752                                 rComp.pVal[0]->Len(), NULL));
753                     String aQuer( pTransliteration->transliterate(
754                                 *rComp.pVal[1], ScGlobal::eLnge, 0,
755                                 rComp.pVal[1]->Len(), NULL));
756                     bMatch = (aCell.Search( aQuer ) != STRING_NOTFOUND);
757                 }
758                 fRes = (bMatch ? 0 : 1);
759             }
760             else if (pOptions->bIgnoreCase)
761                 fRes = (double) ScGlobal::GetCollator()->compareString(
762                         *rComp.pVal[ 0 ], *rComp.pVal[ 1 ] );
763             else
764                 fRes = (double) ScGlobal::GetCaseCollator()->compareString(
765                         *rComp.pVal[ 0 ], *rComp.pVal[ 1 ] );
766         }
767         else if (pDok->GetDocOptions().IsIgnoreCase())
768             fRes = (double) ScGlobal::GetCollator()->compareString(
769                 *rComp.pVal[ 0 ], *rComp.pVal[ 1 ] );
770         else
771             fRes = (double) ScGlobal::GetCaseCollator()->compareString(
772                 *rComp.pVal[ 0 ], *rComp.pVal[ 1 ] );
773     }
774     if (nStringQuery && pOptions)
775     {
776         const ScQueryEntry& rEntry = pOptions->aQueryEntry;
777         if (!rEntry.bQueryByString && rEntry.pStr->Len() &&
778                 (rEntry.eOp == SC_EQUAL || rEntry.eOp == SC_NOT_EQUAL))
779         {
780             // As in ScTable::ValidQuery() match a numeric string for a
781             // number query that originated from a string, e.g. in SUMIF
782             // and COUNTIF. Transliteration is not needed here.
783             bool bEqual = rComp.pVal[nStringQuery-1]->Equals( *rEntry.pStr);
784             // match => fRes=0, else fRes=1
785             fRes = (rEntry.eOp == SC_NOT_EQUAL) ? bEqual : !bEqual;
786         }
787     }
788     return fRes;
789 }
790 
791 
792 double ScInterpreter::Compare()
793 {
794     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::Compare" );
795     String aVal1, aVal2;
796     ScCompare aComp( &aVal1, &aVal2 );
797     for( short i = 1; i >= 0; i-- )
798     {
799         switch ( GetRawStackType() )
800         {
801             case svEmptyCell:
802                 Pop();
803                 aComp.bEmpty[ i ] = sal_True;
804                 break;
805             case svMissing:
806             case svDouble:
807                 aComp.nVal[ i ] = GetDouble();
808                 aComp.bVal[ i ] = sal_True;
809                 break;
810             case svString:
811                 *aComp.pVal[ i ] = GetString();
812                 aComp.bVal[ i ] = sal_False;
813                 break;
814             case svDoubleRef :
815             case svSingleRef :
816             {
817                 ScAddress aAdr;
818                 if ( !PopDoubleRefOrSingleRef( aAdr ) )
819                     break;
820                 ScBaseCell* pCell = GetCell( aAdr );
821                 if (HasCellEmptyData( pCell))
822                     aComp.bEmpty[ i ] = sal_True;
823                 else if (HasCellStringData( pCell))
824                 {
825                     GetCellString( *aComp.pVal[ i ], pCell);
826                     aComp.bVal[ i ] = sal_False;
827                 }
828                 else
829                 {
830                     aComp.nVal[ i ] = GetCellValue( aAdr, pCell );
831                     aComp.bVal[ i ] = sal_True;
832                 }
833             }
834             break;
835             default:
836                 SetError( errIllegalParameter);
837             break;
838         }
839     }
840     if( nGlobalError )
841         return 0;
842     nCurFmtType = nFuncFmtType = NUMBERFORMAT_LOGICAL;
843     return CompareFunc( aComp );
844 }
845 
846 
847 ScMatrixRef ScInterpreter::CompareMat( ScCompareOptions* pOptions )
848 {
849     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::CompareMat" );
850     String aVal1, aVal2;
851     ScCompare aComp( &aVal1, &aVal2 );
852     ScMatrixRef pMat[2];
853     ScAddress aAdr;
854     for( short i = 1; i >= 0; i-- )
855     {
856         switch (GetRawStackType())
857         {
858             case svEmptyCell:
859                 Pop();
860                 aComp.bEmpty[ i ] = sal_True;
861                 break;
862             case svMissing:
863             case svDouble:
864                 aComp.nVal[ i ] = GetDouble();
865                 aComp.bVal[ i ] = sal_True;
866                 break;
867             case svString:
868                 *aComp.pVal[ i ] = GetString();
869                 aComp.bVal[ i ] = sal_False;
870                 break;
871             case svSingleRef:
872             {
873                 PopSingleRef( aAdr );
874                 ScBaseCell* pCell = GetCell( aAdr );
875                 if (HasCellEmptyData( pCell))
876                     aComp.bEmpty[ i ] = sal_True;
877                 else if (HasCellStringData( pCell))
878                 {
879                     GetCellString( *aComp.pVal[ i ], pCell);
880                     aComp.bVal[ i ] = sal_False;
881                 }
882                 else
883                 {
884                     aComp.nVal[ i ] = GetCellValue( aAdr, pCell );
885                     aComp.bVal[ i ] = sal_True;
886                 }
887             }
888             break;
889             case svDoubleRef:
890             case svMatrix:
891                 pMat[ i ] = GetMatrix();
892                 if ( !pMat[ i ] )
893                     SetError( errIllegalParameter);
894                 else
895                     pMat[i]->SetErrorInterpreter( NULL);
896                     // errors are transported as DoubleError inside matrix
897                 break;
898             default:
899                 SetError( errIllegalParameter);
900             break;
901         }
902     }
903     ScMatrixRef pResMat = NULL;
904     if( !nGlobalError )
905     {
906         if ( pMat[0] && pMat[1] )
907         {
908             SCSIZE nC0, nC1;
909             SCSIZE nR0, nR1;
910             pMat[0]->GetDimensions( nC0, nR0 );
911             pMat[1]->GetDimensions( nC1, nR1 );
912             SCSIZE nC = Max( nC0, nC1 );
913             SCSIZE nR = Max( nR0, nR1 );
914             pResMat = GetNewMat( nC, nR);
915             if ( !pResMat )
916                 return NULL;
917             for ( SCSIZE j=0; j<nC; j++ )
918             {
919                 for ( SCSIZE k=0; k<nR; k++ )
920                 {
921                     SCSIZE nCol = j, nRow = k;
922                     if (    pMat[0]->ValidColRowOrReplicated( nCol, nRow ) &&
923                             pMat[1]->ValidColRowOrReplicated( nCol, nRow ))
924                     {
925                         for ( short i=1; i>=0; i-- )
926                         {
927                             if ( pMat[i]->IsString(j,k) )
928                             {
929                                 aComp.bVal[i] = sal_False;
930                                 *aComp.pVal[i] = pMat[i]->GetString(j,k);
931                                 aComp.bEmpty[i] = pMat[i]->IsEmpty(j,k);
932                             }
933                             else
934                             {
935                                 aComp.bVal[i] = sal_True;
936                                 aComp.nVal[i] = pMat[i]->GetDouble(j,k);
937                                 aComp.bEmpty[i] = sal_False;
938                             }
939                         }
940                         pResMat->PutDouble( CompareFunc( aComp, pOptions ), j,k );
941                     }
942                     else
943                         pResMat->PutString( ScGlobal::GetRscString(STR_NO_VALUE), j,k );
944                 }
945             }
946         }
947         else if ( pMat[0] || pMat[1] )
948         {
949             short i = ( pMat[0] ? 0 : 1);
950             SCSIZE nC, nR;
951             pMat[i]->GetDimensions( nC, nR );
952             pResMat = GetNewMat( nC, nR);
953             if ( !pResMat )
954                 return NULL;
955             SCSIZE n = nC * nR;
956             for ( SCSIZE j=0; j<n; j++ )
957             {
958                 if ( pMat[i]->IsValue(j) )
959                 {
960                     aComp.bVal[i] = sal_True;
961                     aComp.nVal[i] = pMat[i]->GetDouble(j);
962                     aComp.bEmpty[i] = sal_False;
963                 }
964                 else
965                 {
966                     aComp.bVal[i] = sal_False;
967                     *aComp.pVal[i] = pMat[i]->GetString(j);
968                     aComp.bEmpty[i] = pMat[i]->IsEmpty(j);
969                 }
970                 pResMat->PutDouble( CompareFunc( aComp, pOptions ), j );
971             }
972         }
973     }
974     nCurFmtType = nFuncFmtType = NUMBERFORMAT_LOGICAL;
975     return pResMat;
976 }
977 
978 
979 ScMatrixRef ScInterpreter::QueryMat( ScMatrix* pMat, ScCompareOptions& rOptions )
980 {
981     short nSaveCurFmtType = nCurFmtType;
982     short nSaveFuncFmtType = nFuncFmtType;
983     PushMatrix( pMat);
984     if (rOptions.aQueryEntry.bQueryByString)
985         PushString( *rOptions.aQueryEntry.pStr);
986     else
987         PushDouble( rOptions.aQueryEntry.nVal);
988     ScMatrixRef pResultMatrix = CompareMat( &rOptions);
989     nCurFmtType = nSaveCurFmtType;
990     nFuncFmtType = nSaveFuncFmtType;
991     if (nGlobalError || !pResultMatrix)
992     {
993         SetError( errIllegalParameter);
994         return pResultMatrix;
995     }
996 
997     switch (rOptions.aQueryEntry.eOp)
998     {
999         case SC_EQUAL:
1000             pResultMatrix->CompareEqual();
1001             break;
1002         case SC_LESS:
1003             pResultMatrix->CompareLess();
1004             break;
1005         case SC_GREATER:
1006             pResultMatrix->CompareGreater();
1007             break;
1008         case SC_LESS_EQUAL:
1009             pResultMatrix->CompareLessEqual();
1010             break;
1011         case SC_GREATER_EQUAL:
1012             pResultMatrix->CompareGreaterEqual();
1013             break;
1014         case SC_NOT_EQUAL:
1015             pResultMatrix->CompareNotEqual();
1016             break;
1017         default:
1018             SetError( errIllegalArgument);
1019             DBG_ERROR1( "ScInterpreter::QueryMat: unhandled comparison operator: %d", (int)rOptions.aQueryEntry.eOp);
1020     }
1021     return pResultMatrix;
1022 }
1023 
1024 
1025 void ScInterpreter::ScEqual()
1026 {
1027     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScEqual" );
1028     if ( GetStackType(1) == svMatrix || GetStackType(2) == svMatrix )
1029     {
1030         ScMatrixRef pMat = CompareMat();
1031         if ( !pMat )
1032             PushIllegalParameter();
1033         else
1034         {
1035             pMat->CompareEqual();
1036             PushMatrix( pMat );
1037         }
1038     }
1039     else
1040         PushInt( Compare() == 0 );
1041 }
1042 
1043 
1044 void ScInterpreter::ScNotEqual()
1045 {
1046     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNotEqual" );
1047     if ( GetStackType(1) == svMatrix || GetStackType(2) == svMatrix )
1048     {
1049         ScMatrixRef pMat = CompareMat();
1050         if ( !pMat )
1051             PushIllegalParameter();
1052         else
1053         {
1054             pMat->CompareNotEqual();
1055             PushMatrix( pMat );
1056         }
1057     }
1058     else
1059         PushInt( Compare() != 0 );
1060 }
1061 
1062 
1063 void ScInterpreter::ScLess()
1064 {
1065     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLess" );
1066     if ( GetStackType(1) == svMatrix || GetStackType(2) == svMatrix )
1067     {
1068         ScMatrixRef pMat = CompareMat();
1069         if ( !pMat )
1070             PushIllegalParameter();
1071         else
1072         {
1073             pMat->CompareLess();
1074             PushMatrix( pMat );
1075         }
1076     }
1077     else
1078         PushInt( Compare() < 0 );
1079 }
1080 
1081 
1082 void ScInterpreter::ScGreater()
1083 {
1084     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGreater" );
1085     if ( GetStackType(1) == svMatrix || GetStackType(2) == svMatrix )
1086     {
1087         ScMatrixRef pMat = CompareMat();
1088         if ( !pMat )
1089             PushIllegalParameter();
1090         else
1091         {
1092             pMat->CompareGreater();
1093             PushMatrix( pMat );
1094         }
1095     }
1096     else
1097         PushInt( Compare() > 0 );
1098 }
1099 
1100 
1101 void ScInterpreter::ScLessEqual()
1102 {
1103     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLessEqual" );
1104     if ( GetStackType(1) == svMatrix || GetStackType(2) == svMatrix )
1105     {
1106         ScMatrixRef pMat = CompareMat();
1107         if ( !pMat )
1108             PushIllegalParameter();
1109         else
1110         {
1111             pMat->CompareLessEqual();
1112             PushMatrix( pMat );
1113         }
1114     }
1115     else
1116         PushInt( Compare() <= 0 );
1117 }
1118 
1119 
1120 void ScInterpreter::ScGreaterEqual()
1121 {
1122     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGreaterEqual" );
1123     if ( GetStackType(1) == svMatrix || GetStackType(2) == svMatrix )
1124     {
1125         ScMatrixRef pMat = CompareMat();
1126         if ( !pMat )
1127             PushIllegalParameter();
1128         else
1129         {
1130             pMat->CompareGreaterEqual();
1131             PushMatrix( pMat );
1132         }
1133     }
1134     else
1135         PushInt( Compare() >= 0 );
1136 }
1137 
1138 
1139 void ScInterpreter::ScAnd()
1140 {
1141     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScAnd" );
1142     nFuncFmtType = NUMBERFORMAT_LOGICAL;
1143     short nParamCount = GetByte();
1144     if ( MustHaveParamCountMin( nParamCount, 1 ) )
1145     {
1146         sal_Bool bHaveValue = sal_False;
1147         short nRes = sal_True;
1148         size_t nRefInList = 0;
1149         while( nParamCount-- > 0)
1150         {
1151             if ( !nGlobalError )
1152             {
1153                 switch ( GetStackType() )
1154                 {
1155                     case svDouble :
1156                         bHaveValue = sal_True;
1157                         nRes &= ( PopDouble() != 0.0 );
1158                     break;
1159                     case svString :
1160                         Pop();
1161                         SetError( errNoValue );
1162                     break;
1163                     case svSingleRef :
1164                     {
1165                         ScAddress aAdr;
1166                         PopSingleRef( aAdr );
1167                         if ( !nGlobalError )
1168                         {
1169                             ScBaseCell* pCell = GetCell( aAdr );
1170                             if ( HasCellValueData( pCell ) )
1171                             {
1172                                 bHaveValue = sal_True;
1173                                 nRes &= ( GetCellValue( aAdr, pCell ) != 0.0 );
1174                             }
1175                             // else: Xcl setzt hier keinen Fehler
1176                         }
1177                     }
1178                     break;
1179                     case svDoubleRef:
1180                     case svRefList:
1181                     {
1182                         ScRange aRange;
1183                         PopDoubleRef( aRange, nParamCount, nRefInList);
1184                         if ( !nGlobalError )
1185                         {
1186                             double fVal;
1187                             sal_uInt16 nErr = 0;
1188                             ScValueIterator aValIter( pDok, aRange );
1189                             if ( aValIter.GetFirst( fVal, nErr ) )
1190                             {
1191                                 bHaveValue = sal_True;
1192                                 do
1193                                 {
1194                                     nRes &= ( fVal != 0.0 );
1195                                 } while ( (nErr == 0) &&
1196                                     aValIter.GetNext( fVal, nErr ) );
1197                             }
1198                             SetError( nErr );
1199                         }
1200                     }
1201                     break;
1202                     case svMatrix:
1203                     {
1204                         ScMatrixRef pMat = GetMatrix();
1205                         if ( pMat )
1206                         {
1207                             bHaveValue = sal_True;
1208                             double fVal = pMat->And();
1209                             sal_uInt16 nErr = GetDoubleErrorValue( fVal );
1210                             if ( nErr )
1211                             {
1212                                 SetError( nErr );
1213                                 nRes = sal_False;
1214                             }
1215                             else
1216                                 nRes &= (fVal != 0.0);
1217                         }
1218                         // else: GetMatrix did set errIllegalParameter
1219                     }
1220                     break;
1221                     default:
1222                         Pop();
1223                         SetError( errIllegalParameter);
1224                 }
1225             }
1226             else
1227                 Pop();
1228         }
1229         if ( bHaveValue )
1230             PushInt( nRes );
1231         else
1232             PushNoValue();
1233     }
1234 }
1235 
1236 
1237 void ScInterpreter::ScOr()
1238 {
1239     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScOr" );
1240     nFuncFmtType = NUMBERFORMAT_LOGICAL;
1241     short nParamCount = GetByte();
1242     if ( MustHaveParamCountMin( nParamCount, 1 ) )
1243     {
1244         sal_Bool bHaveValue = sal_False;
1245         short nRes = sal_False;
1246         size_t nRefInList = 0;
1247         while( nParamCount-- > 0)
1248         {
1249             if ( !nGlobalError )
1250             {
1251                 switch ( GetStackType() )
1252                 {
1253                     case svDouble :
1254                         bHaveValue = sal_True;
1255                         nRes |= ( PopDouble() != 0.0 );
1256                     break;
1257                     case svString :
1258                         Pop();
1259                         SetError( errNoValue );
1260                     break;
1261                     case svSingleRef :
1262                     {
1263                         ScAddress aAdr;
1264                         PopSingleRef( aAdr );
1265                         if ( !nGlobalError )
1266                         {
1267                             ScBaseCell* pCell = GetCell( aAdr );
1268                             if ( HasCellValueData( pCell ) )
1269                             {
1270                                 bHaveValue = sal_True;
1271                                 nRes |= ( GetCellValue( aAdr, pCell ) != 0.0 );
1272                             }
1273                             // else: Xcl setzt hier keinen Fehler
1274                         }
1275                     }
1276                     break;
1277                     case svDoubleRef:
1278                     case svRefList:
1279                     {
1280                         ScRange aRange;
1281                         PopDoubleRef( aRange, nParamCount, nRefInList);
1282                         if ( !nGlobalError )
1283                         {
1284                             double fVal;
1285                             sal_uInt16 nErr = 0;
1286                             ScValueIterator aValIter( pDok, aRange );
1287                             if ( aValIter.GetFirst( fVal, nErr ) )
1288                             {
1289                                 bHaveValue = sal_True;
1290                                 do
1291                                 {
1292                                     nRes |= ( fVal != 0.0 );
1293                                 } while ( (nErr == 0) &&
1294                                     aValIter.GetNext( fVal, nErr ) );
1295                             }
1296                             SetError( nErr );
1297                         }
1298                     }
1299                     break;
1300                     case svMatrix:
1301                     {
1302                         bHaveValue = sal_True;
1303                         ScMatrixRef pMat = GetMatrix();
1304                         if ( pMat )
1305                         {
1306                             bHaveValue = sal_True;
1307                             double fVal = pMat->Or();
1308                             sal_uInt16 nErr = GetDoubleErrorValue( fVal );
1309                             if ( nErr )
1310                             {
1311                                 SetError( nErr );
1312                                 nRes = sal_False;
1313                             }
1314                             else
1315                                 nRes |= (fVal != 0.0);
1316                         }
1317                         // else: GetMatrix did set errIllegalParameter
1318                     }
1319                     break;
1320                     default:
1321                         Pop();
1322                         SetError( errIllegalParameter);
1323                 }
1324             }
1325             else
1326                 Pop();
1327         }
1328         if ( bHaveValue )
1329             PushInt( nRes );
1330         else
1331             PushNoValue();
1332     }
1333 }
1334 
1335 
1336 void ScInterpreter::ScNeg()
1337 {
1338     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNeg" );
1339     // Simple negation doesn't change current format type to number, keep
1340     // current type.
1341     nFuncFmtType = nCurFmtType;
1342     switch ( GetStackType() )
1343     {
1344         case svMatrix :
1345         {
1346             ScMatrixRef pMat = GetMatrix();
1347             if ( !pMat )
1348                 PushIllegalParameter();
1349             else
1350             {
1351                 SCSIZE nC, nR;
1352                 pMat->GetDimensions( nC, nR );
1353                 ScMatrixRef pResMat = GetNewMat( nC, nR);
1354                 if ( !pResMat )
1355                     PushIllegalArgument();
1356                 else
1357                 {
1358                     SCSIZE nCount = nC * nR;
1359                     for ( SCSIZE j=0; j<nCount; ++j )
1360                     {
1361                         if ( pMat->IsValueOrEmpty(j) )
1362                             pResMat->PutDouble( -pMat->GetDouble(j), j );
1363                         else
1364                             pResMat->PutString(
1365                                 ScGlobal::GetRscString( STR_NO_VALUE ), j );
1366                     }
1367                     PushMatrix( pResMat );
1368                 }
1369             }
1370         }
1371         break;
1372         default:
1373             PushDouble( -GetDouble() );
1374     }
1375 }
1376 
1377 
1378 void ScInterpreter::ScPercentSign()
1379 {
1380     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScPercentSign" );
1381     nFuncFmtType = NUMBERFORMAT_PERCENT;
1382     const FormulaToken* pSaveCur = pCur;
1383     sal_uInt8 nSavePar = cPar;
1384     PushInt( 100 );
1385     cPar = 2;
1386     FormulaByteToken aDivOp( ocDiv, cPar );
1387     pCur = &aDivOp;
1388     ScDiv();
1389     pCur = pSaveCur;
1390     cPar = nSavePar;
1391 }
1392 
1393 
1394 void ScInterpreter::ScNot()
1395 {
1396     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNot" );
1397     nFuncFmtType = NUMBERFORMAT_LOGICAL;
1398     switch ( GetStackType() )
1399     {
1400         case svMatrix :
1401         {
1402             ScMatrixRef pMat = GetMatrix();
1403             if ( !pMat )
1404                 PushIllegalParameter();
1405             else
1406             {
1407                 SCSIZE nC, nR;
1408                 pMat->GetDimensions( nC, nR );
1409                 ScMatrixRef pResMat = GetNewMat( nC, nR);
1410                 if ( !pResMat )
1411                     PushIllegalArgument();
1412                 else
1413                 {
1414                     SCSIZE nCount = nC * nR;
1415                     for ( SCSIZE j=0; j<nCount; ++j )
1416                     {
1417                         if ( pMat->IsValueOrEmpty(j) )
1418                             pResMat->PutDouble( (pMat->GetDouble(j) == 0.0), j );
1419                         else
1420                             pResMat->PutString(
1421                                 ScGlobal::GetRscString( STR_NO_VALUE ), j );
1422                     }
1423                     PushMatrix( pResMat );
1424                 }
1425             }
1426         }
1427         break;
1428         default:
1429             PushInt( GetDouble() == 0.0 );
1430     }
1431 }
1432 
1433 
1434 void ScInterpreter::ScPi()
1435 {
1436     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScPi" );
1437     PushDouble(F_PI);
1438 }
1439 
1440 
1441 void ScInterpreter::ScRandom()
1442 {
1443     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRandom" );
1444     PushDouble((double)rand() / ((double)RAND_MAX+1.0));
1445 }
1446 
1447 
1448 void ScInterpreter::ScTrue()
1449 {
1450     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTrue" );
1451     nFuncFmtType = NUMBERFORMAT_LOGICAL;
1452     PushInt(1);
1453 }
1454 
1455 
1456 void ScInterpreter::ScFalse()
1457 {
1458     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFalse" );
1459     nFuncFmtType = NUMBERFORMAT_LOGICAL;
1460     PushInt(0);
1461 }
1462 
1463 
1464 void ScInterpreter::ScDeg()
1465 {
1466     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDeg" );
1467     PushDouble((GetDouble() / F_PI) * 180.0);
1468 }
1469 
1470 
1471 void ScInterpreter::ScRad()
1472 {
1473     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRad" );
1474     PushDouble(GetDouble() * (F_PI / 180));
1475 }
1476 
1477 
1478 void ScInterpreter::ScSin()
1479 {
1480     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSin" );
1481     PushDouble(::rtl::math::sin(GetDouble()));
1482 }
1483 
1484 
1485 void ScInterpreter::ScCos()
1486 {
1487     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCos" );
1488     PushDouble(::rtl::math::cos(GetDouble()));
1489 }
1490 
1491 
1492 void ScInterpreter::ScTan()
1493 {
1494     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTan" );
1495     PushDouble(::rtl::math::tan(GetDouble()));
1496 }
1497 
1498 
1499 void ScInterpreter::ScCot()
1500 {
1501     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCot" );
1502     PushDouble(1.0 / ::rtl::math::tan(GetDouble()));
1503 }
1504 
1505 
1506 void ScInterpreter::ScArcSin()
1507 {
1508     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArcSin" );
1509     PushDouble(asin(GetDouble()));
1510 }
1511 
1512 
1513 void ScInterpreter::ScArcCos()
1514 {
1515     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArcCos" );
1516     PushDouble(acos(GetDouble()));
1517 }
1518 
1519 
1520 void ScInterpreter::ScArcTan()
1521 {
1522     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArcTan" );
1523     PushDouble(atan(GetDouble()));
1524 }
1525 
1526 
1527 void ScInterpreter::ScArcCot()
1528 {
1529     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArcCot" );
1530     PushDouble((F_PI2) - atan(GetDouble()));
1531 }
1532 
1533 
1534 void ScInterpreter::ScSinHyp()
1535 {
1536     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSinHyp" );
1537     PushDouble(sinh(GetDouble()));
1538 }
1539 
1540 
1541 void ScInterpreter::ScCosHyp()
1542 {
1543     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCosHyp" );
1544     PushDouble(cosh(GetDouble()));
1545 }
1546 
1547 
1548 void ScInterpreter::ScTanHyp()
1549 {
1550     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTanHyp" );
1551     PushDouble(tanh(GetDouble()));
1552 }
1553 
1554 
1555 void ScInterpreter::ScCotHyp()
1556 {
1557     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCotHyp" );
1558     PushDouble(1.0 / tanh(GetDouble()));
1559 }
1560 
1561 
1562 void ScInterpreter::ScArcSinHyp()
1563 {
1564     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArcSinHyp" );
1565     PushDouble( ::rtl::math::asinh( GetDouble()));
1566 }
1567 
1568 void ScInterpreter::ScArcCosHyp()
1569 {
1570     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArcCosHyp" );
1571     double fVal = GetDouble();
1572     if (fVal < 1.0)
1573         PushIllegalArgument();
1574     else
1575         PushDouble( ::rtl::math::acosh( fVal));
1576 }
1577 
1578 void ScInterpreter::ScArcTanHyp()
1579 {
1580     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArcTanHyp" );
1581     double fVal = GetDouble();
1582     if (fabs(fVal) >= 1.0)
1583         PushIllegalArgument();
1584     else
1585         PushDouble( ::rtl::math::atanh( fVal));
1586 }
1587 
1588 
1589 void ScInterpreter::ScArcCotHyp()
1590 {
1591     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArcCotHyp" );
1592     double nVal = GetDouble();
1593     if (fabs(nVal) <= 1.0)
1594         PushIllegalArgument();
1595     else
1596         PushDouble(0.5 * log((nVal + 1.0) / (nVal - 1.0)));
1597 }
1598 
1599 void ScInterpreter::ScCosecant()
1600 {
1601     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "regina", "ScInterpreter::ScCosecant" );
1602     PushDouble(1.0 / ::rtl::math::sin(GetDouble()));
1603 }
1604 
1605 void ScInterpreter::ScSecant()
1606 {
1607     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "regina", "ScInterpreter::ScSecant" );
1608     PushDouble(1.0 / ::rtl::math::cos(GetDouble()));
1609 }
1610 
1611 void ScInterpreter::ScCosecantHyp()
1612 {
1613     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "regina", "ScInterpreter::ScCosecantHyp" );
1614     PushDouble(1.0 / sinh(GetDouble()));
1615 }
1616 
1617 void ScInterpreter::ScSecantHyp()
1618 {
1619     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "regina", "ScInterpreter::ScSecantHyp" );
1620     PushDouble(1.0 / cosh(GetDouble()));
1621 }
1622 
1623 
1624 void ScInterpreter::ScExp()
1625 {
1626     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScExp" );
1627     PushDouble(exp(GetDouble()));
1628 }
1629 
1630 
1631 void ScInterpreter::ScSqrt()
1632 {
1633     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSqrt" );
1634     double fVal = GetDouble();
1635     if (fVal >= 0.0)
1636         PushDouble(sqrt(fVal));
1637     else
1638         PushIllegalArgument();
1639 }
1640 
1641 
1642 void ScInterpreter::ScIsEmpty()
1643 {
1644     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsEmpty" );
1645     short nRes = 0;
1646     nFuncFmtType = NUMBERFORMAT_LOGICAL;
1647     switch ( GetRawStackType() )
1648     {
1649         case svEmptyCell:
1650         {
1651             FormulaTokenRef p = PopToken();
1652             if (!static_cast<const ScEmptyCellToken*>(p.get())->IsInherited())
1653                 nRes = 1;
1654         }
1655         break;
1656         case svDoubleRef :
1657         case svSingleRef :
1658         {
1659             ScAddress aAdr;
1660             if ( !PopDoubleRefOrSingleRef( aAdr ) )
1661                 break;
1662             // NOTE: this could test also on inherited emptiness, but then the
1663             // cell tested wouldn't be empty. Must correspond with
1664             // ScCountEmptyCells().
1665             // if (HasCellEmptyData( GetCell( aAdr)))
1666             CellType eCellType = GetCellType( GetCell( aAdr ) );
1667             if((eCellType == CELLTYPE_NONE) || (eCellType == CELLTYPE_NOTE))
1668                 nRes = 1;
1669         }
1670         break;
1671         case svMatrix:
1672         {
1673             ScMatrixRef pMat = PopMatrix();
1674             if ( !pMat )
1675                 ;   // nothing
1676             else if ( !pJumpMatrix )
1677                 nRes = pMat->IsEmpty( 0 );
1678             else
1679             {
1680                 SCSIZE nCols, nRows, nC, nR;
1681                 pMat->GetDimensions( nCols, nRows);
1682                 pJumpMatrix->GetPos( nC, nR);
1683                 if ( nC < nCols && nR < nRows )
1684                     nRes = pMat->IsEmpty( nC, nR);
1685                 // else: sal_False, not empty (which is what Xcl does)
1686             }
1687         }
1688         break;
1689         default:
1690             Pop();
1691     }
1692     nGlobalError = 0;
1693     PushInt( nRes );
1694 }
1695 
1696 
1697 short ScInterpreter::IsString()
1698 {
1699     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::IsString" );
1700     nFuncFmtType = NUMBERFORMAT_LOGICAL;
1701     short nRes = 0;
1702     switch ( GetRawStackType() )
1703     {
1704         case svString:
1705             Pop();
1706             nRes = 1;
1707         break;
1708         case svDoubleRef :
1709         case svSingleRef :
1710         {
1711             ScAddress aAdr;
1712             if ( !PopDoubleRefOrSingleRef( aAdr ) )
1713                 break;
1714             ScBaseCell* pCell = GetCell( aAdr );
1715             if (GetCellErrCode( pCell ) == 0)
1716             {
1717                 switch ( GetCellType( pCell ) )
1718                 {
1719                     case CELLTYPE_STRING :
1720                     case CELLTYPE_EDIT :
1721                         nRes = 1;
1722                         break;
1723                     case CELLTYPE_FORMULA :
1724                         nRes = !((ScFormulaCell*)pCell)->IsValue() &&
1725                             !((ScFormulaCell*)pCell)->IsEmpty();
1726                         break;
1727                     default:
1728                         ; // nothing
1729                 }
1730             }
1731         }
1732         break;
1733         case svMatrix:
1734         {
1735             ScMatrixRef pMat = PopMatrix();
1736             if ( !pMat )
1737                 ;   // nothing
1738             else if ( !pJumpMatrix )
1739                 nRes = pMat->IsString(0) && !pMat->IsEmpty(0);
1740             else
1741             {
1742                 SCSIZE nCols, nRows, nC, nR;
1743                 pMat->GetDimensions( nCols, nRows);
1744                 pJumpMatrix->GetPos( nC, nR);
1745                 if ( nC < nCols && nR < nRows )
1746                     nRes = pMat->IsString( nC, nR) && !pMat->IsEmpty( nC, nR);
1747             }
1748         }
1749         break;
1750         default:
1751             Pop();
1752     }
1753     nGlobalError = 0;
1754     return nRes;
1755 }
1756 
1757 
1758 void ScInterpreter::ScIsString()
1759 {
1760     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsString" );
1761     PushInt( IsString() );
1762 }
1763 
1764 
1765 void ScInterpreter::ScIsNonString()
1766 {
1767     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsNonString" );
1768     PushInt( !IsString() );
1769 }
1770 
1771 
1772 void ScInterpreter::ScIsLogical()
1773 {
1774     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsLogical" );
1775     short nRes = 0;
1776     switch ( GetStackType() )
1777     {
1778         case svDoubleRef :
1779         case svSingleRef :
1780         {
1781             ScAddress aAdr;
1782             if ( !PopDoubleRefOrSingleRef( aAdr ) )
1783                 break;
1784             ScBaseCell* pCell = GetCell( aAdr );
1785             if (GetCellErrCode( pCell ) == 0)
1786             {
1787                 if (HasCellValueData(pCell))
1788                 {
1789                     sal_uLong nFormat = GetCellNumberFormat( aAdr, pCell );
1790                     nRes = ( pFormatter->GetType(nFormat)
1791                                                  == NUMBERFORMAT_LOGICAL);
1792                 }
1793             }
1794         }
1795         break;
1796         case svMatrix:
1797             // TODO: we don't have type information for arrays except
1798             // numerical/string.
1799         // Fall thru
1800         default:
1801             PopError();
1802             if ( !nGlobalError )
1803                 nRes = ( nCurFmtType == NUMBERFORMAT_LOGICAL );
1804     }
1805     nCurFmtType = nFuncFmtType = NUMBERFORMAT_LOGICAL;
1806     nGlobalError = 0;
1807     PushInt( nRes );
1808 }
1809 
1810 
1811 void ScInterpreter::ScType()
1812 {
1813     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScType" );
1814     short nType = 0;
1815     switch ( GetStackType() )
1816     {
1817         case svDoubleRef :
1818         case svSingleRef :
1819         {
1820             ScAddress aAdr;
1821             if ( !PopDoubleRefOrSingleRef( aAdr ) )
1822                 break;
1823             ScBaseCell* pCell = GetCell( aAdr );
1824             if (GetCellErrCode( pCell ) == 0)
1825             {
1826                 switch ( GetCellType( pCell ) )
1827                 {
1828                     // NOTE: this is Xcl nonsense!
1829                     case CELLTYPE_NOTE :
1830                         nType = 1;      // empty cell is value (0)
1831                         break;
1832                     case CELLTYPE_STRING :
1833                     case CELLTYPE_EDIT :
1834                         nType = 2;
1835                         break;
1836                     case CELLTYPE_VALUE :
1837                         {
1838                             sal_uLong nFormat = GetCellNumberFormat( aAdr, pCell );
1839                             if (pFormatter->GetType(nFormat)
1840                                                      == NUMBERFORMAT_LOGICAL)
1841                                 nType = 4;
1842                             else
1843                                 nType = 1;
1844                         }
1845                         break;
1846                     case CELLTYPE_FORMULA :
1847                         nType = 8;
1848                         break;
1849                     default:
1850                         PushIllegalArgument();
1851                 }
1852             }
1853             else
1854                 nType = 16;
1855         }
1856         break;
1857         case svString:
1858             PopError();
1859             if ( nGlobalError )
1860             {
1861                 nType = 16;
1862                 nGlobalError = 0;
1863             }
1864             else
1865                 nType = 2;
1866         break;
1867         case svMatrix:
1868             PopMatrix();
1869             if ( nGlobalError )
1870             {
1871                 nType = 16;
1872                 nGlobalError = 0;
1873             }
1874             else
1875                 nType = 64;
1876                 // we could return the type of one element if in JumpMatrix or
1877                 // ForceArray mode, but Xcl doesn't ...
1878         break;
1879         default:
1880             PopError();
1881             if ( nGlobalError )
1882             {
1883                 nType = 16;
1884                 nGlobalError = 0;
1885             }
1886             else
1887                 nType = 1;
1888     }
1889     PushInt( nType );
1890 }
1891 
1892 
1893 inline sal_Bool lcl_FormatHasNegColor( const SvNumberformat* pFormat )
1894 {
1895     return pFormat && pFormat->GetColor( 1 );
1896 }
1897 
1898 
1899 inline sal_Bool lcl_FormatHasOpenPar( const SvNumberformat* pFormat )
1900 {
1901     return pFormat && (pFormat->GetFormatstring().Search( '(' ) != STRING_NOTFOUND);
1902 }
1903 
1904 
1905 void ScInterpreter::ScCell()
1906 {   // ATTRIBUTE ; [REF]
1907     sal_uInt8 nParamCount = GetByte();
1908     if( MustHaveParamCount( nParamCount, 1, 2 ) )
1909     {
1910         ScAddress aCellPos( aPos );
1911         sal_Bool bError = sal_False;
1912         if( nParamCount == 2 )
1913             bError = !PopDoubleRefOrSingleRef( aCellPos );
1914         String aInfoType( GetString() );
1915         if( bError || nGlobalError )
1916             PushIllegalParameter();
1917         else
1918         {
1919             String          aFuncResult;
1920             ScBaseCell*     pCell = GetCell( aCellPos );
1921 
1922             ScCellKeywordTranslator::transKeyword(aInfoType, ScGlobal::GetLocale(), ocCell);
1923 
1924 // *** ADDRESS INFO ***
1925             if( aInfoType.EqualsAscii( "COL" ) )
1926             {   // column number (1-based)
1927                 PushInt( aCellPos.Col() + 1 );
1928             }
1929             else if( aInfoType.EqualsAscii( "ROW" ) )
1930             {   // row number (1-based)
1931                 PushInt( aCellPos.Row() + 1 );
1932             }
1933             else if( aInfoType.EqualsAscii( "SHEET" ) )
1934             {   // table number (1-based)
1935                 PushInt( aCellPos.Tab() + 1 );
1936             }
1937             else if( aInfoType.EqualsAscii( "ADDRESS" ) )
1938             {   // address formatted as [['FILENAME'#]$TABLE.]$COL$ROW
1939                 sal_uInt16 nFlags = (aCellPos.Tab() == aPos.Tab()) ? (SCA_ABS) : (SCA_ABS_3D);
1940                 aCellPos.Format( aFuncResult, nFlags, pDok, pDok->GetAddressConvention() );
1941                 PushString( aFuncResult );
1942             }
1943             else if( aInfoType.EqualsAscii( "FILENAME" ) )
1944             {   // file name and table name: 'FILENAME'#$TABLE
1945                 SCTAB nTab = aCellPos.Tab();
1946                 if( nTab < pDok->GetTableCount() )
1947                 {
1948                     if( pDok->GetLinkMode( nTab ) == SC_LINK_VALUE )
1949                         pDok->GetName( nTab, aFuncResult );
1950                     else
1951                     {
1952                         SfxObjectShell* pShell = pDok->GetDocumentShell();
1953                         if( pShell && pShell->GetMedium() )
1954                         {
1955                             aFuncResult = (sal_Unicode) '\'';
1956                             const INetURLObject& rURLObj = pShell->GetMedium()->GetURLObject();
1957                             aFuncResult += String( rURLObj.GetMainURL( INetURLObject::DECODE_UNAMBIGUOUS ) );
1958                             aFuncResult.AppendAscii( "'#$" );
1959                             String aTabName;
1960                             pDok->GetName( nTab, aTabName );
1961                             aFuncResult += aTabName;
1962                         }
1963                     }
1964                 }
1965                 PushString( aFuncResult );
1966             }
1967             else if( aInfoType.EqualsAscii( "COORD" ) )
1968             {   // address, lotus 1-2-3 formatted: $TABLE:$COL$ROW
1969                 // Yes, passing tab as col is intentional!
1970                 ScAddress( static_cast<SCCOL>(aCellPos.Tab()), 0, 0 ).Format(
1971                     aFuncResult, (SCA_COL_ABSOLUTE|SCA_VALID_COL), NULL, pDok->GetAddressConvention() );
1972                 aFuncResult += ':';
1973                 String aCellStr;
1974                 aCellPos.Format( aCellStr, (SCA_COL_ABSOLUTE|SCA_VALID_COL|SCA_ROW_ABSOLUTE|SCA_VALID_ROW),
1975                                  NULL, pDok->GetAddressConvention() );
1976                 aFuncResult += aCellStr;
1977                 PushString( aFuncResult );
1978             }
1979 
1980 // *** CELL PROPERTIES ***
1981             else if( aInfoType.EqualsAscii( "CONTENTS" ) )
1982             {   // contents of the cell, no formatting
1983                 if( pCell && pCell->HasStringData() )
1984                 {
1985                     GetCellString( aFuncResult, pCell );
1986                     PushString( aFuncResult );
1987                 }
1988                 else
1989                     PushDouble( GetCellValue( aCellPos, pCell ) );
1990             }
1991             else if( aInfoType.EqualsAscii( "TYPE" ) )
1992             {   // b = blank; l = string (label); v = otherwise (value)
1993                 if( HasCellStringData( pCell ) )
1994                     aFuncResult = 'l';
1995                 else
1996                     aFuncResult = HasCellValueData( pCell ) ? 'v' : 'b';
1997                 PushString( aFuncResult );
1998             }
1999             else if( aInfoType.EqualsAscii( "WIDTH" ) )
2000             {   // column width (rounded off as count of zero characters in standard font and size)
2001                 Printer*    pPrinter = pDok->GetPrinter();
2002                 MapMode     aOldMode( pPrinter->GetMapMode() );
2003                 Font        aOldFont( pPrinter->GetFont() );
2004                 Font        aDefFont;
2005 
2006                 pPrinter->SetMapMode( MAP_TWIP );
2007                 // font color doesn't matter here
2008                 pDok->GetDefPattern()->GetFont( aDefFont, SC_AUTOCOL_BLACK, pPrinter );
2009                 pPrinter->SetFont( aDefFont );
2010                 long nZeroWidth = pPrinter->GetTextWidth( String( '0' ) );
2011                 pPrinter->SetFont( aOldFont );
2012                 pPrinter->SetMapMode( aOldMode );
2013                 int nZeroCount = (int)(pDok->GetColWidth( aCellPos.Col(), aCellPos.Tab() ) / nZeroWidth);
2014                 PushInt( nZeroCount );
2015             }
2016             else if( aInfoType.EqualsAscii( "PREFIX" ) )
2017             {   // ' = left; " = right; ^ = centered
2018                 if( HasCellStringData( pCell ) )
2019                 {
2020                     const SvxHorJustifyItem* pJustAttr = (const SvxHorJustifyItem*)
2021                         pDok->GetAttr( aCellPos.Col(), aCellPos.Row(), aCellPos.Tab(), ATTR_HOR_JUSTIFY );
2022                     switch( pJustAttr->GetValue() )
2023                     {
2024                         case SVX_HOR_JUSTIFY_STANDARD:
2025                         case SVX_HOR_JUSTIFY_LEFT:
2026                         case SVX_HOR_JUSTIFY_BLOCK:     aFuncResult = '\''; break;
2027                         case SVX_HOR_JUSTIFY_CENTER:    aFuncResult = '^';  break;
2028                         case SVX_HOR_JUSTIFY_RIGHT:     aFuncResult = '"';  break;
2029                         case SVX_HOR_JUSTIFY_REPEAT:    aFuncResult = '\\'; break;
2030                     }
2031                 }
2032                 PushString( aFuncResult );
2033             }
2034             else if( aInfoType.EqualsAscii( "PROTECT" ) )
2035             {   // 1 = cell locked
2036                 const ScProtectionAttr* pProtAttr = (const ScProtectionAttr*)
2037                     pDok->GetAttr( aCellPos.Col(), aCellPos.Row(), aCellPos.Tab(), ATTR_PROTECTION );
2038                 PushInt( pProtAttr->GetProtection() ? 1 : 0 );
2039             }
2040 
2041 // *** FORMATTING ***
2042             else if( aInfoType.EqualsAscii( "FORMAT" ) )
2043             {   // specific format code for standard formats
2044                 sal_uLong   nFormat = pDok->GetNumberFormat( aCellPos );
2045                 sal_Bool    bAppendPrec = sal_True;
2046                 sal_uInt16  nPrec, nLeading;
2047                 sal_Bool    bThousand, bIsRed;
2048                 pFormatter->GetFormatSpecialInfo( nFormat, bThousand, bIsRed, nPrec, nLeading );
2049 
2050                 switch( pFormatter->GetType( nFormat ) )
2051                 {
2052                     case NUMBERFORMAT_NUMBER:       aFuncResult = (bThousand ? ',' : 'F');  break;
2053                     case NUMBERFORMAT_CURRENCY:     aFuncResult = 'C';                      break;
2054                     case NUMBERFORMAT_SCIENTIFIC:   aFuncResult = 'S';                      break;
2055                     case NUMBERFORMAT_PERCENT:      aFuncResult = 'P';                      break;
2056                     default:
2057                     {
2058                         bAppendPrec = sal_False;
2059                         switch( pFormatter->GetIndexTableOffset( nFormat ) )
2060                         {
2061                             case NF_DATE_SYSTEM_SHORT:
2062                             case NF_DATE_SYS_DMMMYY:
2063                             case NF_DATE_SYS_DDMMYY:
2064                             case NF_DATE_SYS_DDMMYYYY:
2065                             case NF_DATE_SYS_DMMMYYYY:
2066                             case NF_DATE_DIN_DMMMYYYY:
2067                             case NF_DATE_SYS_DMMMMYYYY:
2068                             case NF_DATE_DIN_DMMMMYYYY: aFuncResult.AssignAscii( RTL_CONSTASCII_STRINGPARAM( "D1" ) );  break;
2069                             case NF_DATE_SYS_DDMMM:     aFuncResult.AssignAscii( RTL_CONSTASCII_STRINGPARAM( "D2" ) );  break;
2070                             case NF_DATE_SYS_MMYY:      aFuncResult.AssignAscii( RTL_CONSTASCII_STRINGPARAM( "D3" ) );  break;
2071                             case NF_DATETIME_SYSTEM_SHORT_HHMM:
2072                             case NF_DATETIME_SYS_DDMMYYYY_HHMMSS:
2073                                                         aFuncResult.AssignAscii( RTL_CONSTASCII_STRINGPARAM( "D4" ) );  break;
2074                             case NF_DATE_DIN_MMDD:      aFuncResult.AssignAscii( RTL_CONSTASCII_STRINGPARAM( "D5" ) );  break;
2075                             case NF_TIME_HHMMSSAMPM:    aFuncResult.AssignAscii( RTL_CONSTASCII_STRINGPARAM( "D6" ) );  break;
2076                             case NF_TIME_HHMMAMPM:      aFuncResult.AssignAscii( RTL_CONSTASCII_STRINGPARAM( "D7" ) );  break;
2077                             case NF_TIME_HHMMSS:        aFuncResult.AssignAscii( RTL_CONSTASCII_STRINGPARAM( "D8" ) );  break;
2078                             case NF_TIME_HHMM:          aFuncResult.AssignAscii( RTL_CONSTASCII_STRINGPARAM( "D9" ) );  break;
2079                             default:                    aFuncResult = 'G';
2080                         }
2081                     }
2082                 }
2083                 if( bAppendPrec )
2084                     aFuncResult += String::CreateFromInt32( nPrec );
2085                 const SvNumberformat* pFormat = pFormatter->GetEntry( nFormat );
2086                 if( lcl_FormatHasNegColor( pFormat ) )
2087                     aFuncResult += '-';
2088                 if( lcl_FormatHasOpenPar( pFormat ) )
2089                     aFuncResult.AppendAscii( RTL_CONSTASCII_STRINGPARAM( "()" ) );
2090                 PushString( aFuncResult );
2091             }
2092             else if( aInfoType.EqualsAscii( "COLOR" ) )
2093             {   // 1 = negative values are colored, otherwise 0
2094                 const SvNumberformat* pFormat = pFormatter->GetEntry( pDok->GetNumberFormat( aCellPos ) );
2095                 PushInt( lcl_FormatHasNegColor( pFormat ) ? 1 : 0 );
2096             }
2097             else if( aInfoType.EqualsAscii( "PARENTHESES" ) )
2098             {   // 1 = format string contains a '(' character, otherwise 0
2099                 const SvNumberformat* pFormat = pFormatter->GetEntry( pDok->GetNumberFormat( aCellPos ) );
2100                 PushInt( lcl_FormatHasOpenPar( pFormat ) ? 1 : 0 );
2101             }
2102             else
2103                 PushIllegalArgument();
2104         }
2105     }
2106 }
2107 
2108 
2109 void ScInterpreter::ScIsRef()
2110 {
2111     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCell" );
2112     nFuncFmtType = NUMBERFORMAT_LOGICAL;
2113     short nRes = 0;
2114     switch ( GetStackType() )
2115     {
2116         case svSingleRef :
2117         {
2118             ScAddress aAdr;
2119             PopSingleRef( aAdr );
2120             if ( !nGlobalError )
2121                 nRes = 1;
2122         }
2123         break;
2124         case svDoubleRef :
2125         {
2126             ScRange aRange;
2127             PopDoubleRef( aRange );
2128             if ( !nGlobalError )
2129                 nRes = 1;
2130         }
2131         break;
2132         case svRefList :
2133         {
2134             FormulaTokenRef x = PopToken();
2135             if ( !nGlobalError )
2136                 nRes = !static_cast<ScToken*>(x.get())->GetRefList()->empty();
2137         }
2138         break;
2139         default:
2140             Pop();
2141     }
2142     nGlobalError = 0;
2143     PushInt( nRes );
2144 }
2145 
2146 
2147 void ScInterpreter::ScIsValue()
2148 {
2149     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsValue" );
2150     nFuncFmtType = NUMBERFORMAT_LOGICAL;
2151     short nRes = 0;
2152     switch ( GetRawStackType() )
2153     {
2154         case svDouble:
2155             Pop();
2156             nRes = 1;
2157         break;
2158         case svDoubleRef :
2159         case svSingleRef :
2160         {
2161             ScAddress aAdr;
2162             if ( !PopDoubleRefOrSingleRef( aAdr ) )
2163                 break;
2164             ScBaseCell* pCell = GetCell( aAdr );
2165             if (GetCellErrCode( pCell ) == 0)
2166             {
2167                 switch ( GetCellType( pCell ) )
2168                 {
2169                     case CELLTYPE_VALUE :
2170                         nRes = 1;
2171                         break;
2172                     case CELLTYPE_FORMULA :
2173                         nRes = ((ScFormulaCell*)pCell)->IsValue() &&
2174                             !((ScFormulaCell*)pCell)->IsEmpty();
2175                         break;
2176                     default:
2177                         ; // nothing
2178                 }
2179             }
2180         }
2181         break;
2182         case svMatrix:
2183         {
2184             ScMatrixRef pMat = PopMatrix();
2185             if ( !pMat )
2186                 ;   // nothing
2187             else if ( !pJumpMatrix )
2188             {
2189                 if (pMat->GetErrorIfNotString( 0 ) == 0)
2190                     nRes = pMat->IsValue( 0 );
2191             }
2192             else
2193             {
2194                 SCSIZE nCols, nRows, nC, nR;
2195                 pMat->GetDimensions( nCols, nRows);
2196                 pJumpMatrix->GetPos( nC, nR);
2197                 if ( nC < nCols && nR < nRows )
2198                     if (pMat->GetErrorIfNotString( nC, nR) == 0)
2199                         nRes = pMat->IsValue( nC, nR);
2200             }
2201         }
2202         break;
2203         default:
2204             Pop();
2205     }
2206     nGlobalError = 0;
2207     PushInt( nRes );
2208 }
2209 
2210 
2211 void ScInterpreter::ScIsFormula()
2212 {
2213     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsFormula" );
2214     nFuncFmtType = NUMBERFORMAT_LOGICAL;
2215     short nRes = 0;
2216     switch ( GetStackType() )
2217     {
2218         case svDoubleRef :
2219         case svSingleRef :
2220         {
2221             ScAddress aAdr;
2222             if ( !PopDoubleRefOrSingleRef( aAdr ) )
2223                 break;
2224             nRes = (GetCellType( GetCell( aAdr ) ) == CELLTYPE_FORMULA);
2225         }
2226         break;
2227         default:
2228             Pop();
2229     }
2230     nGlobalError = 0;
2231     PushInt( nRes );
2232 }
2233 
2234 
2235 void ScInterpreter::ScFormula()
2236 {
2237     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFormula" );
2238     String aFormula;
2239     switch ( GetStackType() )
2240     {
2241         case svDoubleRef :
2242         case svSingleRef :
2243         {
2244             ScAddress aAdr;
2245             if ( !PopDoubleRefOrSingleRef( aAdr ) )
2246                 break;
2247             ScBaseCell* pCell = GetCell( aAdr );
2248             switch ( GetCellType( pCell ) )
2249             {
2250                 case CELLTYPE_FORMULA :
2251                     ((ScFormulaCell*)pCell)->GetFormula( aFormula );
2252                 break;
2253                 default:
2254                     SetError( NOTAVAILABLE );
2255             }
2256         }
2257         break;
2258         default:
2259             Pop();
2260             SetError( NOTAVAILABLE );
2261     }
2262     PushString( aFormula );
2263 }
2264 
2265 
2266 
2267 void ScInterpreter::ScIsNV()
2268 {
2269     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsNV" );
2270     nFuncFmtType = NUMBERFORMAT_LOGICAL;
2271     short nRes = 0;
2272     switch ( GetStackType() )
2273     {
2274         case svDoubleRef :
2275         case svSingleRef :
2276         {
2277             ScAddress aAdr;
2278             PopDoubleRefOrSingleRef( aAdr );
2279             if ( nGlobalError == NOTAVAILABLE )
2280                 nRes = 1;
2281             else
2282             {
2283                 ScBaseCell* pCell = GetCell( aAdr );
2284                 sal_uInt16 nErr = GetCellErrCode( pCell );
2285                 nRes = (nErr == NOTAVAILABLE);
2286             }
2287         }
2288         break;
2289         case svMatrix:
2290         {
2291             ScMatrixRef pMat = PopMatrix();
2292             if ( !pMat )
2293                 ;   // nothing
2294             else if ( !pJumpMatrix )
2295                 nRes = (pMat->GetErrorIfNotString( 0 ) == NOTAVAILABLE);
2296             else
2297             {
2298                 SCSIZE nCols, nRows, nC, nR;
2299                 pMat->GetDimensions( nCols, nRows);
2300                 pJumpMatrix->GetPos( nC, nR);
2301                 if ( nC < nCols && nR < nRows )
2302                     nRes = (pMat->GetErrorIfNotString( nC, nR) == NOTAVAILABLE);
2303             }
2304         }
2305         break;
2306         default:
2307             PopError();
2308             if ( nGlobalError == NOTAVAILABLE )
2309                 nRes = 1;
2310     }
2311     nGlobalError = 0;
2312     PushInt( nRes );
2313 }
2314 
2315 
2316 void ScInterpreter::ScIsErr()
2317 {
2318     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsErr" );
2319     nFuncFmtType = NUMBERFORMAT_LOGICAL;
2320     short nRes = 0;
2321     switch ( GetStackType() )
2322     {
2323         case svDoubleRef :
2324         case svSingleRef :
2325         {
2326             ScAddress aAdr;
2327             PopDoubleRefOrSingleRef( aAdr );
2328             if ( nGlobalError && nGlobalError != NOTAVAILABLE )
2329                 nRes = 1;
2330             else
2331             {
2332                 ScBaseCell* pCell = GetCell( aAdr );
2333                 sal_uInt16 nErr = GetCellErrCode( pCell );
2334                 nRes = (nErr && nErr != NOTAVAILABLE);
2335             }
2336         }
2337         break;
2338         case svMatrix:
2339         {
2340             ScMatrixRef pMat = PopMatrix();
2341             if ( nGlobalError || !pMat )
2342                 nRes = ((nGlobalError && nGlobalError != NOTAVAILABLE) || !pMat);
2343             else if ( !pJumpMatrix )
2344             {
2345                 sal_uInt16 nErr = pMat->GetErrorIfNotString( 0 );
2346                 nRes = (nErr && nErr != NOTAVAILABLE);
2347             }
2348             else
2349             {
2350                 SCSIZE nCols, nRows, nC, nR;
2351                 pMat->GetDimensions( nCols, nRows);
2352                 pJumpMatrix->GetPos( nC, nR);
2353                 if ( nC < nCols && nR < nRows )
2354                 {
2355                     sal_uInt16 nErr = pMat->GetErrorIfNotString( nC, nR);
2356                     nRes = (nErr && nErr != NOTAVAILABLE);
2357                 }
2358             }
2359         }
2360         break;
2361         default:
2362             PopError();
2363             if ( nGlobalError && nGlobalError != NOTAVAILABLE )
2364                 nRes = 1;
2365     }
2366     nGlobalError = 0;
2367     PushInt( nRes );
2368 }
2369 
2370 
2371 void ScInterpreter::ScIsError()
2372 {
2373     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsError" );
2374     nFuncFmtType = NUMBERFORMAT_LOGICAL;
2375     short nRes = 0;
2376     switch ( GetStackType() )
2377     {
2378         case svDoubleRef :
2379         case svSingleRef :
2380         {
2381             ScAddress aAdr;
2382             if ( !PopDoubleRefOrSingleRef( aAdr ) )
2383             {
2384                 nRes = 1;
2385                 break;
2386             }
2387             if ( nGlobalError )
2388                 nRes = 1;
2389             else
2390             {
2391                 ScBaseCell* pCell = GetCell( aAdr );
2392                 nRes = (GetCellErrCode( pCell ) != 0);
2393             }
2394         }
2395         break;
2396         case svMatrix:
2397         {
2398             ScMatrixRef pMat = PopMatrix();
2399             if ( nGlobalError || !pMat )
2400                 nRes = 1;
2401             else if ( !pJumpMatrix )
2402                 nRes = (pMat->GetErrorIfNotString( 0 ) != 0);
2403             else
2404             {
2405                 SCSIZE nCols, nRows, nC, nR;
2406                 pMat->GetDimensions( nCols, nRows);
2407                 pJumpMatrix->GetPos( nC, nR);
2408                 if ( nC < nCols && nR < nRows )
2409                     nRes = (pMat->GetErrorIfNotString( nC, nR) != 0);
2410             }
2411         }
2412         break;
2413         default:
2414             PopError();
2415             if ( nGlobalError )
2416                 nRes = 1;
2417     }
2418     nGlobalError = 0;
2419     PushInt( nRes );
2420 }
2421 
2422 
2423 short ScInterpreter::IsEven()
2424 {
2425     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::IsEven" );
2426     nFuncFmtType = NUMBERFORMAT_LOGICAL;
2427     short nRes = 0;
2428     double fVal = 0.0;
2429     switch ( GetStackType() )
2430     {
2431         case svDoubleRef :
2432         case svSingleRef :
2433         {
2434             ScAddress aAdr;
2435             if ( !PopDoubleRefOrSingleRef( aAdr ) )
2436                 break;
2437             ScBaseCell* pCell = GetCell( aAdr );
2438             sal_uInt16 nErr = GetCellErrCode( pCell );
2439             if (nErr != 0)
2440                 SetError(nErr);
2441             else
2442             {
2443                 switch ( GetCellType( pCell ) )
2444                 {
2445                     case CELLTYPE_VALUE :
2446                         fVal = GetCellValue( aAdr, pCell );
2447                         nRes = 1;
2448                     break;
2449                     case CELLTYPE_FORMULA :
2450                         if( ((ScFormulaCell*)pCell)->IsValue() )
2451                         {
2452                             fVal = GetCellValue( aAdr, pCell );
2453                             nRes = 1;
2454                         }
2455                     break;
2456                     default:
2457                         ; // nothing
2458                 }
2459             }
2460         }
2461         break;
2462         case svDouble:
2463         {
2464             fVal = PopDouble();
2465             nRes = 1;
2466         }
2467         break;
2468         case svMatrix:
2469         {
2470             ScMatrixRef pMat = PopMatrix();
2471             if ( !pMat )
2472                 ;   // nothing
2473             else if ( !pJumpMatrix )
2474             {
2475                 nRes = pMat->IsValue( 0 );
2476                 if ( nRes )
2477                     fVal = pMat->GetDouble( 0 );
2478             }
2479             else
2480             {
2481                 SCSIZE nCols, nRows, nC, nR;
2482                 pMat->GetDimensions( nCols, nRows);
2483                 pJumpMatrix->GetPos( nC, nR);
2484                 if ( nC < nCols && nR < nRows )
2485                 {
2486                     nRes = pMat->IsValue( nC, nR);
2487                     if ( nRes )
2488                         fVal = pMat->GetDouble( nC, nR);
2489                 }
2490                 else
2491                     SetError( errNoValue);
2492             }
2493         }
2494         break;
2495         default:
2496             ; // nothing
2497     }
2498     if ( !nRes )
2499         SetError( errIllegalParameter);
2500     else
2501         nRes = ( fmod( ::rtl::math::approxFloor( fabs( fVal ) ), 2.0 ) < 0.5 );
2502     return nRes;
2503 }
2504 
2505 
2506 void ScInterpreter::ScIsEven()
2507 {
2508     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsEven" );
2509     PushInt( IsEven() );
2510 }
2511 
2512 
2513 void ScInterpreter::ScIsOdd()
2514 {
2515     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIsOdd" );
2516     PushInt( !IsEven() );
2517 }
2518 
2519 
2520 void ScInterpreter::ScN()
2521 {
2522     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScN" );
2523     sal_uInt16 nErr = nGlobalError;
2524     nGlobalError = 0;
2525     // Temporarily override the ConvertStringToValue() error for
2526     // GetCellValue() / GetCellValueOrZero()
2527     sal_uInt16 nSErr = mnStringNoValueError;
2528     mnStringNoValueError = errCellNoValue;
2529     double fVal = GetDouble();
2530     mnStringNoValueError = nSErr;
2531     if ( nGlobalError == NOTAVAILABLE || nGlobalError == errCellNoValue )
2532         nGlobalError = 0;       // N(#NA) and N("text") are ok
2533     if ( !nGlobalError && nErr != NOTAVAILABLE )
2534         nGlobalError = nErr;
2535     PushDouble( fVal );
2536 }
2537 
2538 
2539 void ScInterpreter::ScTrim()
2540 {   // trimmt nicht nur sondern schnibbelt auch doppelte raus!
2541     String aVal( GetString() );
2542     aVal.EraseLeadingChars();
2543     aVal.EraseTrailingChars();
2544     String aStr;
2545     register const sal_Unicode* p = aVal.GetBuffer();
2546     register const sal_Unicode* const pEnd = p + aVal.Len();
2547     while ( p < pEnd )
2548     {
2549         if ( *p != ' ' || p[-1] != ' ' )    // erster kann kein ' ' sein, -1 ist also ok
2550             aStr += *p;
2551         p++;
2552     }
2553     PushString( aStr );
2554 }
2555 
2556 
2557 void ScInterpreter::ScUpper()
2558 {
2559     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTrim" );
2560     String aString = GetString();
2561     ScGlobal::pCharClass->toUpper(aString);
2562     PushString(aString);
2563 }
2564 
2565 
2566 void ScInterpreter::ScPropper()
2567 {
2568     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScPropper" );
2569 //2do: what to do with I18N-CJK ?!?
2570     String aStr( GetString() );
2571     const xub_StrLen nLen = aStr.Len();
2572     // #i82487# don't try to write to empty string's BufferAccess
2573     // (would crash now that the empty string is const)
2574     if ( nLen > 0 )
2575     {
2576         String aUpr( ScGlobal::pCharClass->upper( aStr ) );
2577         String aLwr( ScGlobal::pCharClass->lower( aStr ) );
2578         register sal_Unicode* pStr = aStr.GetBufferAccess();
2579         const sal_Unicode* pUpr = aUpr.GetBuffer();
2580         const sal_Unicode* pLwr = aLwr.GetBuffer();
2581         *pStr = *pUpr;
2582         String aTmpStr( 'x' );
2583         xub_StrLen nPos = 1;
2584         while( nPos < nLen )
2585         {
2586             aTmpStr.SetChar( 0, pStr[nPos-1] );
2587             if ( !ScGlobal::pCharClass->isLetter( aTmpStr, 0 ) )
2588                 pStr[nPos] = pUpr[nPos];
2589             else
2590                 pStr[nPos] = pLwr[nPos];
2591             nPos++;
2592         }
2593         aStr.ReleaseBufferAccess( nLen );
2594     }
2595     PushString( aStr );
2596 }
2597 
2598 
2599 void ScInterpreter::ScLower()
2600 {
2601     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLower" );
2602     String aString( GetString() );
2603     ScGlobal::pCharClass->toLower(aString);
2604     PushString(aString);
2605 }
2606 
2607 
2608 void ScInterpreter::ScLen()
2609 {
2610     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLen" );
2611     String aStr( GetString() );
2612     PushDouble( aStr.Len() );
2613 }
2614 
2615 
2616 void ScInterpreter::ScT()
2617 {
2618     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScT" );
2619     switch ( GetStackType() )
2620     {
2621         case svDoubleRef :
2622         case svSingleRef :
2623         {
2624             ScAddress aAdr;
2625             if ( !PopDoubleRefOrSingleRef( aAdr ) )
2626             {
2627                 PushInt(0);
2628                 return ;
2629             }
2630             sal_Bool bValue = sal_False;
2631             ScBaseCell* pCell = GetCell( aAdr );
2632             if ( GetCellErrCode( pCell ) == 0 )
2633             {
2634                 switch ( GetCellType( pCell ) )
2635                 {
2636                     case CELLTYPE_VALUE :
2637                         bValue = sal_True;
2638                         break;
2639                     case CELLTYPE_FORMULA :
2640                         bValue = ((ScFormulaCell*)pCell)->IsValue();
2641                         break;
2642                     default:
2643                         ; // nothing
2644                 }
2645             }
2646             if ( bValue )
2647                 PushString( EMPTY_STRING );
2648             else
2649             {
2650                 //  wie GetString()
2651                 GetCellString( aTempStr, pCell );
2652                 PushString( aTempStr );
2653             }
2654         }
2655         break;
2656         case svDouble :
2657         {
2658             PopError();
2659             PushString( EMPTY_STRING );
2660         }
2661         break;
2662         case svString :
2663             ;   // leave on stack
2664         break;
2665         default :
2666             PushError( errUnknownOpCode);
2667     }
2668 }
2669 
2670 
2671 void ScInterpreter::ScValue()
2672 {
2673     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScValue" );
2674     String aInputString;
2675     double fVal;
2676 
2677     switch ( GetRawStackType() )
2678     {
2679         case svMissing:
2680         case svEmptyCell:
2681             Pop();
2682             PushInt(0);
2683             return;
2684         case svDouble:
2685             return;     // leave on stack
2686             //break;
2687 
2688         case svSingleRef:
2689         case svDoubleRef:
2690             {
2691                 ScAddress aAdr;
2692                 if ( !PopDoubleRefOrSingleRef( aAdr ) )
2693                 {
2694                     PushInt(0);
2695                     return;
2696                 }
2697                 ScBaseCell* pCell = GetCell( aAdr );
2698                 if ( pCell && pCell->HasStringData() )
2699                     GetCellString( aInputString, pCell );
2700                 else if ( pCell && pCell->HasValueData() )
2701                 {
2702                     PushDouble( GetCellValue(aAdr, pCell) );
2703                     return;
2704                 }
2705                 else
2706                 {
2707                     PushDouble(0.0);
2708                     return;
2709                 }
2710             }
2711             break;
2712         case svMatrix:
2713             {
2714                 ScMatValType nType = GetDoubleOrStringFromMatrix( fVal,
2715                         aInputString);
2716                 switch (nType)
2717                 {
2718                     case SC_MATVAL_EMPTY:
2719                         fVal = 0.0;
2720                         // fallthru
2721                     case SC_MATVAL_VALUE:
2722                     case SC_MATVAL_BOOLEAN:
2723                         PushDouble( fVal);
2724                         return;
2725                         //break;
2726                     case SC_MATVAL_STRING:
2727                         // evaluated below
2728                         break;
2729                     default:
2730                         PushIllegalArgument();
2731                 }
2732             }
2733             break;
2734         default:
2735             aInputString = GetString();
2736             break;
2737     }
2738 
2739     sal_uInt32 nFIndex = 0;     // 0 for default locale
2740     if (pFormatter->IsNumberFormat(aInputString, nFIndex, fVal))
2741         PushDouble(fVal);
2742     else
2743         PushIllegalArgument();
2744 }
2745 
2746 
2747 //2do: this should be a proper unicode string method
2748 inline sal_Bool lcl_ScInterpreter_IsPrintable( sal_Unicode c )
2749 {
2750     return 0x20 <= c && c != 0x7f;
2751 }
2752 
2753 void ScInterpreter::ScClean()
2754 {
2755     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScClean" );
2756     String aStr( GetString() );
2757     for ( xub_StrLen i = 0; i < aStr.Len(); i++ )
2758     {
2759         if ( !lcl_ScInterpreter_IsPrintable( aStr.GetChar( i ) ) )
2760             aStr.Erase(i,1);
2761     }
2762     PushString(aStr);
2763 }
2764 
2765 
2766 void ScInterpreter::ScCode()
2767 {
2768     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCode" );
2769 //2do: make it full range unicode?
2770     const String& rStr = GetString();
2771     PushInt( (sal_uChar) ByteString::ConvertFromUnicode( rStr.GetChar(0), gsl_getSystemTextEncoding() ) );
2772 }
2773 
2774 
2775 void ScInterpreter::ScChar()
2776 {
2777     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScChar" );
2778 //2do: make it full range unicode?
2779     double fVal = GetDouble();
2780     if (fVal < 0.0 || fVal >= 256.0)
2781         PushIllegalArgument();
2782     else
2783     {
2784         String aStr( '0' );
2785         aStr.SetChar( 0, ByteString::ConvertToUnicode( (sal_Char) fVal, gsl_getSystemTextEncoding() ) );
2786         PushString( aStr );
2787     }
2788 }
2789 
2790 
2791 /* #i70213# fullwidth/halfwidth conversion provided by
2792  * Takashi Nakamoto <bluedwarf@ooo>
2793  * erAck: added Excel compatibility conversions as seen in issue's test case. */
2794 
2795 static ::rtl::OUString lcl_convertIntoHalfWidth( const ::rtl::OUString & rStr )
2796 {
2797     static bool bFirstASCCall = true;
2798     static utl::TransliterationWrapper aTrans( ::comphelper::getProcessServiceFactory(), 0 );
2799 
2800     if( bFirstASCCall )
2801     {
2802         aTrans.loadModuleByImplName( ::rtl::OUString::createFromAscii( "FULLWIDTH_HALFWIDTH_LIKE_ASC" ), LANGUAGE_SYSTEM );
2803         bFirstASCCall = false;
2804     }
2805 
2806     return aTrans.transliterate( rStr, 0, sal_uInt16( rStr.getLength() ), NULL );
2807 }
2808 
2809 
2810 static ::rtl::OUString lcl_convertIntoFullWidth( const ::rtl::OUString & rStr )
2811 {
2812     static bool bFirstJISCall = true;
2813     static utl::TransliterationWrapper aTrans( ::comphelper::getProcessServiceFactory(), 0 );
2814 
2815     if( bFirstJISCall )
2816     {
2817         aTrans.loadModuleByImplName( ::rtl::OUString::createFromAscii( "HALFWIDTH_FULLWIDTH_LIKE_JIS" ), LANGUAGE_SYSTEM );
2818         bFirstJISCall = false;
2819     }
2820 
2821     return aTrans.transliterate( rStr, 0, sal_uInt16( rStr.getLength() ), NULL );
2822 }
2823 
2824 
2825 /* ODFF:
2826  * Summary: Converts half-width to full-width ASCII and katakana characters.
2827  * Semantics: Conversion is done for half-width ASCII and katakana characters,
2828  * other characters are simply copied from T to the result. This is the
2829  * complementary function to ASC.
2830  * For references regarding halfwidth and fullwidth characters see
2831  * http://www.unicode.org/reports/tr11/
2832  * http://www.unicode.org/charts/charindex2.html#H
2833  * http://www.unicode.org/charts/charindex2.html#F
2834  */
2835 void ScInterpreter::ScJis()
2836 {
2837     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScJis" );
2838     if (MustHaveParamCount( GetByte(), 1))
2839         PushString( lcl_convertIntoFullWidth( GetString()));
2840 }
2841 
2842 
2843 /* ODFF:
2844  * Summary: Converts full-width to half-width ASCII and katakana characters.
2845  * Semantics: Conversion is done for full-width ASCII and katakana characters,
2846  * other characters are simply copied from T to the result. This is the
2847  * complementary function to JIS.
2848  */
2849 void ScInterpreter::ScAsc()
2850 {
2851     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScAsc" );
2852     if (MustHaveParamCount( GetByte(), 1))
2853         PushString( lcl_convertIntoHalfWidth( GetString()));
2854 }
2855 
2856 void ScInterpreter::ScUnicode()
2857 {
2858     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScUnicode" );
2859     if ( MustHaveParamCount( GetByte(), 1 ) )
2860     {
2861         const rtl::OUString& rStr = GetString();
2862         if (rStr.getLength() <= 0)
2863             PushIllegalParameter();
2864         else
2865         {
2866             sal_Int32 i = 0;
2867             PushDouble( rStr.iterateCodePoints(&i) );
2868         }
2869     }
2870 }
2871 
2872 void ScInterpreter::ScUnichar()
2873 {
2874     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScUnichar" );
2875     if ( MustHaveParamCount( GetByte(), 1 ) )
2876     {
2877         double dVal = ::rtl::math::approxFloor( GetDouble() );
2878         if ((dVal < 0x000000) || (dVal > 0x10FFFF))
2879             PushIllegalArgument();
2880         else
2881         {
2882             sal_uInt32 nCodePoint = static_cast<sal_uInt32>( dVal );
2883             rtl::OUString aStr( &nCodePoint, 1 );
2884             PushString( aStr );
2885         }
2886     }
2887 }
2888 
2889 
2890 void ScInterpreter::ScMin( sal_Bool bTextAsZero )
2891 {
2892     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScMin" );
2893     short nParamCount = GetByte();
2894     if (!MustHaveParamCountMin( nParamCount, 1))
2895         return;
2896     double nMin = ::std::numeric_limits<double>::max();
2897     double nVal = 0.0;
2898     ScAddress aAdr;
2899     ScRange aRange;
2900     size_t nRefInList = 0;
2901     while (nParamCount-- > 0)
2902     {
2903         switch (GetStackType())
2904         {
2905             case svDouble :
2906             {
2907                 nVal = GetDouble();
2908                 if (nMin > nVal) nMin = nVal;
2909                 nFuncFmtType = NUMBERFORMAT_NUMBER;
2910             }
2911             break;
2912             case svSingleRef :
2913             {
2914                 PopSingleRef( aAdr );
2915                 ScBaseCell* pCell = GetCell( aAdr );
2916                 if (HasCellValueData(pCell))
2917                 {
2918                     nVal = GetCellValue( aAdr, pCell );
2919                     CurFmtToFuncFmt();
2920                     if (nMin > nVal) nMin = nVal;
2921                 }
2922                 else if ( bTextAsZero && HasCellStringData( pCell ) )
2923                 {
2924                     if ( nMin > 0.0 )
2925                         nMin = 0.0;
2926                 }
2927             }
2928             break;
2929             case svDoubleRef :
2930             case svRefList :
2931             {
2932                 sal_uInt16 nErr = 0;
2933                 PopDoubleRef( aRange, nParamCount, nRefInList);
2934                 ScValueIterator aValIter( pDok, aRange, glSubTotal, bTextAsZero );
2935                 if (aValIter.GetFirst(nVal, nErr))
2936                 {
2937                     if (nMin > nVal)
2938                         nMin = nVal;
2939                     aValIter.GetCurNumFmtInfo( nFuncFmtType, nFuncFmtIndex );
2940                     while ((nErr == 0) && aValIter.GetNext(nVal, nErr))
2941                     {
2942                         if (nMin > nVal)
2943                             nMin = nVal;
2944                     }
2945                     SetError(nErr);
2946                 }
2947             }
2948             break;
2949             case svMatrix :
2950             {
2951                 ScMatrixRef pMat = PopMatrix();
2952                 if (pMat)
2953                 {
2954                     SCSIZE nC, nR;
2955                     nFuncFmtType = NUMBERFORMAT_NUMBER;
2956                     pMat->GetDimensions(nC, nR);
2957                     if (pMat->IsNumeric())
2958                     {
2959                         for (SCSIZE nMatCol = 0; nMatCol < nC; nMatCol++)
2960                             for (SCSIZE nMatRow = 0; nMatRow < nR; nMatRow++)
2961                             {
2962                                 nVal = pMat->GetDouble(nMatCol,nMatRow);
2963                                 if (nMin > nVal) nMin = nVal;
2964                             }
2965                     }
2966                     else
2967                     {
2968                         for (SCSIZE nMatCol = 0; nMatCol < nC; nMatCol++)
2969                         {
2970                             for (SCSIZE nMatRow = 0; nMatRow < nR; nMatRow++)
2971                             {
2972                                 if (!pMat->IsString(nMatCol,nMatRow))
2973                                 {
2974                                     nVal = pMat->GetDouble(nMatCol,nMatRow);
2975                                     if (nMin > nVal) nMin = nVal;
2976                                 }
2977                                 else if ( bTextAsZero )
2978                                 {
2979                                     if ( nMin > 0.0 )
2980                                         nMin = 0.0;
2981                                 }
2982                             }
2983                          }
2984                     }
2985                 }
2986             }
2987             break;
2988             case svString :
2989             {
2990                 Pop();
2991                 if ( bTextAsZero )
2992                 {
2993                     if ( nMin > 0.0 )
2994                         nMin = 0.0;
2995                 }
2996                 else
2997                     SetError(errIllegalParameter);
2998             }
2999             break;
3000             default :
3001                 Pop();
3002                 SetError(errIllegalParameter);
3003         }
3004     }
3005     if ( nVal < nMin  )
3006         PushDouble(0.0);
3007     else
3008         PushDouble(nMin);
3009 }
3010 
3011 void ScInterpreter::ScMax( sal_Bool bTextAsZero )
3012 {
3013     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScMax" );
3014     short nParamCount = GetByte();
3015     if (!MustHaveParamCountMin( nParamCount, 1))
3016         return;
3017     double nMax = -(::std::numeric_limits<double>::max());
3018     double nVal = 0.0;
3019     ScAddress aAdr;
3020     ScRange aRange;
3021     size_t nRefInList = 0;
3022     while (nParamCount-- > 0)
3023     {
3024         switch (GetStackType())
3025         {
3026             case svDouble :
3027             {
3028                 nVal = GetDouble();
3029                 if (nMax < nVal) nMax = nVal;
3030                 nFuncFmtType = NUMBERFORMAT_NUMBER;
3031             }
3032             break;
3033             case svSingleRef :
3034             {
3035                 PopSingleRef( aAdr );
3036                 ScBaseCell* pCell = GetCell( aAdr );
3037                 if (HasCellValueData(pCell))
3038                 {
3039                     nVal = GetCellValue( aAdr, pCell );
3040                     CurFmtToFuncFmt();
3041                     if (nMax < nVal) nMax = nVal;
3042                 }
3043                 else if ( bTextAsZero && HasCellStringData( pCell ) )
3044                 {
3045                     if ( nMax < 0.0 )
3046                         nMax = 0.0;
3047                 }
3048             }
3049             break;
3050             case svDoubleRef :
3051             case svRefList :
3052             {
3053                 sal_uInt16 nErr = 0;
3054                 PopDoubleRef( aRange, nParamCount, nRefInList);
3055                 ScValueIterator aValIter( pDok, aRange, glSubTotal, bTextAsZero );
3056                 if (aValIter.GetFirst(nVal, nErr))
3057                 {
3058                     if (nMax < nVal)
3059                         nMax = nVal;
3060                     aValIter.GetCurNumFmtInfo( nFuncFmtType, nFuncFmtIndex );
3061                     while ((nErr == 0) && aValIter.GetNext(nVal, nErr))
3062                     {
3063                         if (nMax < nVal)
3064                             nMax = nVal;
3065                     }
3066                     SetError(nErr);
3067                 }
3068             }
3069             break;
3070             case svMatrix :
3071             {
3072                 ScMatrixRef pMat = PopMatrix();
3073                 if (pMat)
3074                 {
3075                     nFuncFmtType = NUMBERFORMAT_NUMBER;
3076                     SCSIZE nC, nR;
3077                     pMat->GetDimensions(nC, nR);
3078                     if (pMat->IsNumeric())
3079                     {
3080                         for (SCSIZE nMatCol = 0; nMatCol < nC; nMatCol++)
3081                             for (SCSIZE nMatRow = 0; nMatRow < nR; nMatRow++)
3082                             {
3083                                 nVal = pMat->GetDouble(nMatCol,nMatRow);
3084                                 if (nMax < nVal) nMax = nVal;
3085                             }
3086                     }
3087                     else
3088                     {
3089                         for (SCSIZE nMatCol = 0; nMatCol < nC; nMatCol++)
3090                         {
3091                             for (SCSIZE nMatRow = 0; nMatRow < nR; nMatRow++)
3092                             {
3093                                 if (!pMat->IsString(nMatCol,nMatRow))
3094                                 {
3095                                     nVal = pMat->GetDouble(nMatCol,nMatRow);
3096                                     if (nMax < nVal) nMax = nVal;
3097                                 }
3098                                 else if ( bTextAsZero )
3099                                 {
3100                                     if ( nMax < 0.0 )
3101                                         nMax = 0.0;
3102                                 }
3103                             }
3104                         }
3105                     }
3106                 }
3107             }
3108             break;
3109             case svString :
3110             {
3111                 Pop();
3112                 if ( bTextAsZero )
3113                 {
3114                     if ( nMax < 0.0 )
3115                         nMax = 0.0;
3116                 }
3117                 else
3118                     SetError(errIllegalParameter);
3119             }
3120             break;
3121             default :
3122                 Pop();
3123                 SetError(errIllegalParameter);
3124         }
3125     }
3126     if ( nVal > nMax  )
3127         PushDouble(0.0);
3128     else
3129         PushDouble(nMax);
3130 }
3131 
3132 double ScInterpreter::IterateParameters( ScIterFunc eFunc, sal_Bool bTextAsZero )
3133 {
3134     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::IterateParameters" );
3135     short nParamCount = GetByte();
3136     double fRes = ( eFunc == ifPRODUCT ) ? 1.0 : 0.0;
3137     double fVal = 0.0;
3138     double fMem = 0.0;
3139     sal_Bool bNull = sal_True;
3140     sal_uLong nCount = 0;
3141     ScAddress aAdr;
3142     ScRange aRange;
3143     size_t nRefInList = 0;
3144     if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ) )
3145         nGlobalError = 0;
3146     while (nParamCount-- > 0)
3147     {
3148         switch (GetStackType())
3149         {
3150 
3151             case svString:
3152             {
3153                 if( eFunc == ifCOUNT )
3154                 {
3155                     String aStr( PopString() );
3156                     sal_uInt32 nFIndex = 0;                 // damit default Land/Spr.
3157                     if ( bTextAsZero || pFormatter->IsNumberFormat(aStr, nFIndex, fVal))
3158                         nCount++;
3159                 }
3160                 else
3161                 {
3162                     switch ( eFunc )
3163                     {
3164                         case ifAVERAGE:
3165                         case ifSUM:
3166                         case ifSUMSQ:
3167                         case ifPRODUCT:
3168                         {
3169                             if ( bTextAsZero )
3170                             {
3171                                 Pop();
3172                                 nCount++;
3173                                 if ( eFunc == ifPRODUCT )
3174                                     fRes = 0.0;
3175                             }
3176                             else
3177                             {
3178                                 while (nParamCount-- > 0)
3179                                     Pop();
3180                                 SetError( errNoValue );
3181                             }
3182                         }
3183                         break;
3184                         default:
3185                             Pop();
3186                             nCount++;
3187                     }
3188                 }
3189             }
3190             break;
3191             case svDouble    :
3192                 fVal = GetDouble();
3193                 nCount++;
3194                 switch( eFunc )
3195                 {
3196                     case ifAVERAGE:
3197                     case ifSUM:
3198                         if ( bNull && fVal != 0.0 )
3199                         {
3200                             bNull = sal_False;
3201                             fMem = fVal;
3202                         }
3203                         else
3204                             fRes += fVal;
3205                         break;
3206                     case ifSUMSQ:   fRes += fVal * fVal; break;
3207                     case ifPRODUCT: fRes *= fVal; break;
3208                     default: ; // nothing
3209                 }
3210                 nFuncFmtType = NUMBERFORMAT_NUMBER;
3211                 break;
3212             case svSingleRef :
3213             {
3214                 PopSingleRef( aAdr );
3215                 if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ) )
3216                 {
3217                     nGlobalError = 0;
3218                     if ( eFunc == ifCOUNT2 )
3219                         ++nCount;
3220                     break;
3221                 }
3222                 ScBaseCell* pCell = GetCell( aAdr );
3223                 if ( pCell )
3224                 {
3225                     if( eFunc == ifCOUNT2 )
3226                     {
3227                         CellType eCellType = pCell->GetCellType();
3228                         if (eCellType != CELLTYPE_NONE && eCellType != CELLTYPE_NOTE)
3229                             nCount++;
3230                         if ( nGlobalError )
3231                             nGlobalError = 0;
3232                     }
3233                     else if ( pCell->HasValueData() )
3234                     {
3235                         nCount++;
3236                         fVal = GetCellValue( aAdr, pCell );
3237                         CurFmtToFuncFmt();
3238                         switch( eFunc )
3239                         {
3240                             case ifAVERAGE:
3241                             case ifSUM:
3242                                 if ( bNull && fVal != 0.0 )
3243                                 {
3244                                     bNull = sal_False;
3245                                     fMem = fVal;
3246                                 }
3247                                 else
3248                                     fRes += fVal;
3249                                 break;
3250                             case ifSUMSQ:   fRes += fVal * fVal; break;
3251                             case ifPRODUCT: fRes *= fVal; break;
3252                             case ifCOUNT:
3253                                 if ( nGlobalError )
3254                                 {
3255                                     nGlobalError = 0;
3256                                     nCount--;
3257                                 }
3258                                 break;
3259                             default: ; // nothing
3260                         }
3261                     }
3262                     else if ( bTextAsZero && pCell->HasStringData() )
3263                     {
3264                         nCount++;
3265                         if ( eFunc == ifPRODUCT )
3266                             fRes = 0.0;
3267                     }
3268                 }
3269             }
3270             break;
3271             case svDoubleRef :
3272             case svRefList :
3273             {
3274                 sal_uInt16 nErr = 0;
3275                 PopDoubleRef( aRange, nParamCount, nRefInList);
3276                 if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ) )
3277                 {
3278                     nGlobalError = 0;
3279                     if ( eFunc == ifCOUNT2 )
3280                         ++nCount;
3281                     break;
3282                 }
3283                 if( eFunc == ifCOUNT2 )
3284                 {
3285                     ScBaseCell* pCell;
3286                     ScCellIterator aIter( pDok, aRange, glSubTotal );
3287                     if ( (pCell = aIter.GetFirst()) != NULL )
3288                     {
3289                         do
3290                         {
3291                             CellType eType = pCell->GetCellType();
3292                             if( eType != CELLTYPE_NONE && eType != CELLTYPE_NOTE )
3293                                 nCount++;
3294                         }
3295                         while ( (pCell = aIter.GetNext()) != NULL );
3296                     }
3297                     if ( nGlobalError )
3298                         nGlobalError = 0;
3299                 }
3300                 else
3301                 {
3302                     ScValueIterator aValIter( pDok, aRange, glSubTotal, bTextAsZero );
3303                     if (aValIter.GetFirst(fVal, nErr))
3304                     {
3305                         //  Schleife aus Performance-Gruenden nach innen verlegt:
3306                         aValIter.GetCurNumFmtInfo( nFuncFmtType, nFuncFmtIndex );
3307                         switch( eFunc )
3308                         {
3309                             case ifAVERAGE:
3310                             case ifSUM:
3311                                     do
3312                                     {
3313                                         SetError(nErr);
3314                                         if ( bNull && fVal != 0.0 )
3315                                         {
3316                                             bNull = sal_False;
3317                                             fMem = fVal;
3318                                         }
3319                                         else
3320                                             fRes += fVal;
3321                                         nCount++;
3322                                     }
3323                                     while (aValIter.GetNext(fVal, nErr));
3324                                     break;
3325                             case ifSUMSQ:
3326                                     do
3327                                     {
3328                                         SetError(nErr);
3329                                         fRes += fVal * fVal;
3330                                         nCount++;
3331                                     }
3332                                     while (aValIter.GetNext(fVal, nErr));
3333                                     break;
3334                             case ifPRODUCT:
3335                                     do
3336                                     {
3337                                         SetError(nErr);
3338                                         fRes *= fVal;
3339                                         nCount++;
3340                                     }
3341                                     while (aValIter.GetNext(fVal, nErr));
3342                                     break;
3343                             case ifCOUNT:
3344                                     do
3345                                     {
3346                                         if ( !nErr )
3347                                             nCount++;
3348                                     }
3349                                     while (aValIter.GetNext(fVal, nErr));
3350                                     break;
3351                             default: ;  // nothing
3352                         }
3353                         SetError( nErr );
3354                     }
3355                 }
3356             }
3357             break;
3358             case svMatrix :
3359             {
3360                 ScMatrixRef pMat = PopMatrix();
3361                 if (pMat)
3362                 {
3363                     SCSIZE nC, nR;
3364                     nFuncFmtType = NUMBERFORMAT_NUMBER;
3365                     pMat->GetDimensions(nC, nR);
3366                     if( eFunc == ifCOUNT2 )
3367                         nCount += (sal_uLong) nC * nR;
3368                     else
3369                     {
3370                         for (SCSIZE nMatCol = 0; nMatCol < nC; nMatCol++)
3371                         {
3372                             for (SCSIZE nMatRow = 0; nMatRow < nR; nMatRow++)
3373                             {
3374                                 if (!pMat->IsString(nMatCol,nMatRow))
3375                                 {
3376                                     nCount++;
3377                                     fVal = pMat->GetDouble(nMatCol,nMatRow);
3378                                     switch( eFunc )
3379                                     {
3380                                         case ifAVERAGE:
3381                                         case ifSUM:
3382                                             if ( bNull && fVal != 0.0 )
3383                                             {
3384                                                 bNull = sal_False;
3385                                                 fMem = fVal;
3386                                             }
3387                                             else
3388                                                 fRes += fVal;
3389                                             break;
3390                                         case ifSUMSQ:   fRes += fVal * fVal; break;
3391                                         case ifPRODUCT: fRes *= fVal; break;
3392                                         default: ; // nothing
3393                                     }
3394                                 }
3395                                 else if ( bTextAsZero )
3396                                 {
3397                                     nCount++;
3398                                     if ( eFunc == ifPRODUCT )
3399                                         fRes = 0.0;
3400                                 }
3401                             }
3402                         }
3403                     }
3404                 }
3405             }
3406             break;
3407             case svError:
3408             {
3409                 Pop();
3410                 if ( eFunc == ifCOUNT )
3411                 {
3412                     nGlobalError = 0;
3413                 }
3414                 else if ( eFunc == ifCOUNT2 )
3415                 {
3416                     nCount++;
3417                     nGlobalError = 0;
3418                 }
3419             }
3420             break;
3421             default :
3422                 while (nParamCount-- > 0)
3423                     PopError();
3424                 SetError(errIllegalParameter);
3425         }
3426     }
3427     switch( eFunc )
3428     {
3429         case ifSUM:     fRes = ::rtl::math::approxAdd( fRes, fMem ); break;
3430         case ifAVERAGE: fRes = div(::rtl::math::approxAdd( fRes, fMem ), nCount); break;
3431         case ifCOUNT2:
3432         case ifCOUNT:   fRes  = nCount; break;
3433         case ifPRODUCT: if ( !nCount ) fRes = 0.0; break;
3434         default: ; // nothing
3435     }
3436     // Bei Summen etc. macht ein sal_Bool-Ergebnis keinen Sinn
3437     // und Anzahl ist immer Number (#38345#)
3438     if( eFunc == ifCOUNT || nFuncFmtType == NUMBERFORMAT_LOGICAL )
3439         nFuncFmtType = NUMBERFORMAT_NUMBER;
3440     return fRes;
3441 }
3442 
3443 
3444 void ScInterpreter::ScSumSQ()
3445 {
3446     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSumSQ" );
3447     PushDouble( IterateParameters( ifSUMSQ ) );
3448 }
3449 
3450 
3451 void ScInterpreter::ScSum()
3452 {
3453     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSum" );
3454     PushDouble( IterateParameters( ifSUM ) );
3455 }
3456 
3457 
3458 void ScInterpreter::ScProduct()
3459 {
3460     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScProduct" );
3461     PushDouble( IterateParameters( ifPRODUCT ) );
3462 }
3463 
3464 
3465 void ScInterpreter::ScAverage( sal_Bool bTextAsZero )
3466 {
3467     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScAverage" );
3468     PushDouble( IterateParameters( ifAVERAGE, bTextAsZero ) );
3469 }
3470 
3471 
3472 void ScInterpreter::ScCount()
3473 {
3474     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCount" );
3475     PushDouble( IterateParameters( ifCOUNT ) );
3476 }
3477 
3478 
3479 void ScInterpreter::ScCount2()
3480 {
3481     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCount2" );
3482     PushDouble( IterateParameters( ifCOUNT2 ) );
3483 }
3484 
3485 
3486 void ScInterpreter::GetStVarParams( double& rVal, double& rValCount,
3487                 sal_Bool bTextAsZero )
3488 {
3489     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetStVarParams" );
3490     short nParamCount = GetByte();
3491 
3492     std::vector<double> values;
3493     double fSum    = 0.0;
3494     double vSum    = 0.0;
3495     double vMean    = 0.0;
3496     double fVal = 0.0;
3497     rValCount = 0.0;
3498     ScAddress aAdr;
3499     ScRange aRange;
3500     size_t nRefInList = 0;
3501     while (nParamCount-- > 0)
3502     {
3503         switch (GetStackType())
3504         {
3505             case svDouble :
3506             {
3507                 fVal = GetDouble();
3508                 values.push_back(fVal);
3509                 fSum    += fVal;
3510                 rValCount++;
3511             }
3512             break;
3513             case svSingleRef :
3514             {
3515                 PopSingleRef( aAdr );
3516                 ScBaseCell* pCell = GetCell( aAdr );
3517                 if (HasCellValueData(pCell))
3518                 {
3519                     fVal = GetCellValue( aAdr, pCell );
3520                     values.push_back(fVal);
3521                     fSum += fVal;
3522                     rValCount++;
3523                 }
3524                 else if ( bTextAsZero && HasCellStringData( pCell ) )
3525                 {
3526                     values.push_back(0.0);
3527                     rValCount++;
3528                 }
3529             }
3530             break;
3531             case svDoubleRef :
3532             case svRefList :
3533             {
3534                 sal_uInt16 nErr = 0;
3535                 PopDoubleRef( aRange, nParamCount, nRefInList);
3536                 ScValueIterator aValIter( pDok, aRange, glSubTotal, bTextAsZero );
3537                 if (aValIter.GetFirst(fVal, nErr))
3538                 {
3539                     do
3540                     {
3541                         values.push_back(fVal);
3542                         fSum += fVal;
3543                         rValCount++;
3544                     }
3545                     while ((nErr == 0) && aValIter.GetNext(fVal, nErr));
3546                 }
3547             }
3548             break;
3549             case svMatrix :
3550             {
3551                 ScMatrixRef pMat = PopMatrix();
3552                 if (pMat)
3553                 {
3554                     SCSIZE nC, nR;
3555                     pMat->GetDimensions(nC, nR);
3556                     for (SCSIZE nMatCol = 0; nMatCol < nC; nMatCol++)
3557                     {
3558                         for (SCSIZE nMatRow = 0; nMatRow < nR; nMatRow++)
3559                         {
3560                             if (!pMat->IsString(nMatCol,nMatRow))
3561                             {
3562                                 fVal= pMat->GetDouble(nMatCol,nMatRow);
3563                                 values.push_back(fVal);
3564                                 fSum += fVal;
3565                                 rValCount++;
3566                             }
3567                             else if ( bTextAsZero )
3568                             {
3569                                 values.push_back(0.0);
3570                                 rValCount++;
3571                             }
3572                         }
3573                     }
3574                 }
3575             }
3576             break;
3577             case svString :
3578             {
3579                 Pop();
3580                 if ( bTextAsZero )
3581                 {
3582                     values.push_back(0.0);
3583                     rValCount++;
3584                 }
3585                 else
3586                     SetError(errIllegalParameter);
3587             }
3588             break;
3589             default :
3590                 Pop();
3591                 SetError(errIllegalParameter);
3592         }
3593     }
3594 
3595     ::std::vector<double>::size_type n = values.size();
3596     vMean = fSum / n;
3597     for (::std::vector<double>::size_type i = 0; i < n; i++)
3598         vSum += ::rtl::math::approxSub( values[i], vMean) * ::rtl::math::approxSub( values[i], vMean);
3599     rVal = vSum;
3600 }
3601 
3602 
3603 void ScInterpreter::ScVar( sal_Bool bTextAsZero )
3604 {
3605     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScVar" );
3606     double nVal;
3607     double nValCount;
3608     GetStVarParams( nVal, nValCount, bTextAsZero );
3609 
3610     if (nValCount <= 1.0)
3611         PushError( errDivisionByZero );
3612     else
3613         PushDouble( nVal / (nValCount - 1.0));
3614 }
3615 
3616 
3617 void ScInterpreter::ScVarP( sal_Bool bTextAsZero )
3618 {
3619     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScVarP" );
3620     double nVal;
3621     double nValCount;
3622     GetStVarParams( nVal, nValCount, bTextAsZero );
3623 
3624     PushDouble( div( nVal, nValCount));
3625 }
3626 
3627 
3628 void ScInterpreter::ScStDev( sal_Bool bTextAsZero )
3629 {
3630     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScStDev" );
3631     double nVal;
3632     double nValCount;
3633     GetStVarParams( nVal, nValCount, bTextAsZero );
3634     if (nValCount <= 1.0)
3635         PushError( errDivisionByZero );
3636     else
3637         PushDouble( sqrt( nVal / (nValCount - 1.0)));
3638 }
3639 
3640 
3641 void ScInterpreter::ScStDevP( sal_Bool bTextAsZero )
3642 {
3643     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScStDevP" );
3644     double nVal;
3645     double nValCount;
3646     GetStVarParams( nVal, nValCount, bTextAsZero );
3647     if (nValCount == 0.0)
3648         PushError( errDivisionByZero );
3649     else
3650         PushDouble( sqrt( nVal / nValCount));
3651 
3652     /* this was: PushDouble( sqrt( div( nVal, nValCount)));
3653      *
3654      * Besides that the special NAN gets lost in the call through sqrt(),
3655      * unxlngi6.pro then looped back and forth somewhere between div() and
3656      * ::rtl::math::setNan(). Tests showed that
3657      *
3658      *      sqrt( div( 1, 0));
3659      *
3660      * produced a loop, but
3661      *
3662      *      double f1 = div( 1, 0);
3663      *      sqrt( f1 );
3664      *
3665      * was fine. There seems to be some compiler optimization problem. It does
3666      * not occur when compiled with debug=t.
3667      */
3668 }
3669 
3670 
3671 void ScInterpreter::ScColumns()
3672 {
3673     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScColumns" );
3674     sal_uInt8 nParamCount = GetByte();
3675     sal_uLong nVal = 0;
3676     SCCOL nCol1;
3677     SCROW nRow1;
3678     SCTAB nTab1;
3679     SCCOL nCol2;
3680     SCROW nRow2;
3681     SCTAB nTab2;
3682     while (nParamCount-- > 0)
3683     {
3684         switch ( GetStackType() )
3685         {
3686             case svSingleRef:
3687                 PopError();
3688                 nVal++;
3689                 break;
3690             case svDoubleRef:
3691                 PopDoubleRef(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
3692                 nVal += static_cast<sal_uLong>(nTab2 - nTab1 + 1) *
3693                     static_cast<sal_uLong>(nCol2 - nCol1 + 1);
3694                 break;
3695             case svMatrix:
3696             {
3697                 ScMatrixRef pMat = PopMatrix();
3698                 if (pMat)
3699                 {
3700                     SCSIZE nC, nR;
3701                     pMat->GetDimensions(nC, nR);
3702                     nVal += nC;
3703                 }
3704             }
3705             break;
3706             default:
3707                 PopError();
3708                 SetError(errIllegalParameter);
3709         }
3710     }
3711     PushDouble((double)nVal);
3712 }
3713 
3714 
3715 void ScInterpreter::ScRows()
3716 {
3717     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRows" );
3718     sal_uInt8 nParamCount = GetByte();
3719     sal_uLong nVal = 0;
3720     SCCOL nCol1;
3721     SCROW nRow1;
3722     SCTAB nTab1;
3723     SCCOL nCol2;
3724     SCROW nRow2;
3725     SCTAB nTab2;
3726     while (nParamCount-- > 0)
3727     {
3728         switch ( GetStackType() )
3729         {
3730             case svSingleRef:
3731                 PopError();
3732                 nVal++;
3733                 break;
3734             case svDoubleRef:
3735                 PopDoubleRef(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
3736                 nVal += static_cast<sal_uLong>(nTab2 - nTab1 + 1) *
3737                     static_cast<sal_uLong>(nRow2 - nRow1 + 1);
3738                 break;
3739             case svMatrix:
3740             {
3741                 ScMatrixRef pMat = PopMatrix();
3742                 if (pMat)
3743                 {
3744                     SCSIZE nC, nR;
3745                     pMat->GetDimensions(nC, nR);
3746                     nVal += nR;
3747                 }
3748             }
3749             break;
3750             default:
3751                 PopError();
3752                 SetError(errIllegalParameter);
3753         }
3754     }
3755     PushDouble((double)nVal);
3756 }
3757 
3758 void ScInterpreter::ScTables()
3759 {
3760     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTables" );
3761     sal_uInt8 nParamCount = GetByte();
3762     sal_uLong nVal;
3763     if ( nParamCount == 0 )
3764         nVal = pDok->GetTableCount();
3765     else
3766     {
3767         nVal = 0;
3768         SCCOL nCol1;
3769         SCROW nRow1;
3770         SCTAB nTab1;
3771         SCCOL nCol2;
3772         SCROW nRow2;
3773         SCTAB nTab2;
3774         while (nParamCount-- > 0)
3775         {
3776             switch ( GetStackType() )
3777             {
3778                 case svSingleRef:
3779                     PopError();
3780                     nVal++;
3781                 break;
3782                 case svDoubleRef:
3783                     PopDoubleRef(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
3784                     nVal += static_cast<sal_uLong>(nTab2 - nTab1 + 1);
3785                 break;
3786                 case svMatrix:
3787                     PopError();
3788                     nVal++;
3789                 break;
3790                 default:
3791                     PopError();
3792                     SetError( errIllegalParameter );
3793             }
3794         }
3795     }
3796     PushDouble( (double) nVal );
3797 }
3798 
3799 
3800 void ScInterpreter::ScColumn()
3801 {
3802     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScColumn" );
3803     sal_uInt8 nParamCount = GetByte();
3804     if ( MustHaveParamCount( nParamCount, 0, 1 ) )
3805     {
3806         double nVal = 0;
3807         if (nParamCount == 0)
3808         {
3809             nVal = aPos.Col() + 1;
3810             if (bMatrixFormula)
3811             {
3812                 SCCOL nCols;
3813                 SCROW nRows;
3814                 pMyFormulaCell->GetMatColsRows( nCols, nRows);
3815                 ScMatrixRef pResMat = GetNewMat( static_cast<SCSIZE>(nCols), 1);
3816                 if (pResMat)
3817                 {
3818                     for (SCCOL i=0; i < nCols; ++i)
3819                         pResMat->PutDouble( nVal + i, static_cast<SCSIZE>(i), 0);
3820                     PushMatrix( pResMat);
3821                     return;
3822                 }
3823             }
3824         }
3825         else
3826         {
3827             switch ( GetStackType() )
3828             {
3829                 case svSingleRef :
3830                 {
3831                     SCCOL nCol1;
3832                     SCROW nRow1;
3833                     SCTAB nTab1;
3834                     PopSingleRef( nCol1, nRow1, nTab1 );
3835                     nVal = (double) (nCol1 + 1);
3836                 }
3837                 break;
3838                 case svDoubleRef :
3839                 {
3840                     SCCOL nCol1;
3841                     SCROW nRow1;
3842                     SCTAB nTab1;
3843                     SCCOL nCol2;
3844                     SCROW nRow2;
3845                     SCTAB nTab2;
3846                     PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
3847                     if (nCol2 > nCol1)
3848                     {
3849                         ScMatrixRef pResMat = GetNewMat(
3850                                 static_cast<SCSIZE>(nCol2-nCol1+1), 1);
3851                         if (pResMat)
3852                         {
3853                             for (SCCOL i = nCol1; i <= nCol2; i++)
3854                                 pResMat->PutDouble((double)(i+1),
3855                                         static_cast<SCSIZE>(i-nCol1), 0);
3856                             PushMatrix(pResMat);
3857                             return;
3858                         }
3859                         else
3860                             nVal = 0.0;
3861                     }
3862                     else
3863                         nVal = (double) (nCol1 + 1);
3864                 }
3865                 break;
3866                 default:
3867                     SetError( errIllegalParameter );
3868                     nVal = 0.0;
3869             }
3870         }
3871         PushDouble( nVal );
3872     }
3873 }
3874 
3875 
3876 void ScInterpreter::ScRow()
3877 {
3878     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRow" );
3879     sal_uInt8 nParamCount = GetByte();
3880     if ( MustHaveParamCount( nParamCount, 0, 1 ) )
3881     {
3882         double nVal = 0;
3883         if (nParamCount == 0)
3884         {
3885             nVal = aPos.Row() + 1;
3886             if (bMatrixFormula)
3887             {
3888                 SCCOL nCols;
3889                 SCROW nRows;
3890                 pMyFormulaCell->GetMatColsRows( nCols, nRows);
3891                 ScMatrixRef pResMat = GetNewMat( 1, static_cast<SCSIZE>(nRows));
3892                 if (pResMat)
3893                 {
3894                     for (SCROW i=0; i < nRows; i++)
3895                         pResMat->PutDouble( nVal + i, 0, static_cast<SCSIZE>(i));
3896                     PushMatrix( pResMat);
3897                     return;
3898                 }
3899             }
3900         }
3901         else
3902         {
3903             switch ( GetStackType() )
3904             {
3905                 case svSingleRef :
3906                 {
3907                     SCCOL nCol1;
3908                     SCROW nRow1;
3909                     SCTAB nTab1;
3910                     PopSingleRef( nCol1, nRow1, nTab1 );
3911                     nVal = (double) (nRow1 + 1);
3912                 }
3913                 break;
3914                 case svDoubleRef :
3915                 {
3916                     SCCOL nCol1;
3917                     SCROW nRow1;
3918                     SCTAB nTab1;
3919                     SCCOL nCol2;
3920                     SCROW nRow2;
3921                     SCTAB nTab2;
3922                     PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
3923                     if (nRow2 > nRow1)
3924                     {
3925                         ScMatrixRef pResMat = GetNewMat( 1,
3926                                 static_cast<SCSIZE>(nRow2-nRow1+1));
3927                         if (pResMat)
3928                         {
3929                             for (SCROW i = nRow1; i <= nRow2; i++)
3930                                 pResMat->PutDouble((double)(i+1), 0,
3931                                         static_cast<SCSIZE>(i-nRow1));
3932                             PushMatrix(pResMat);
3933                             return;
3934                         }
3935                         else
3936                             nVal = 0.0;
3937                     }
3938                     else
3939                         nVal = (double) (nRow1 + 1);
3940                 }
3941                 break;
3942                 default:
3943                     SetError( errIllegalParameter );
3944                     nVal = 0.0;
3945             }
3946         }
3947         PushDouble( nVal );
3948     }
3949 }
3950 
3951 void ScInterpreter::ScTable()
3952 {
3953     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTable" );
3954     sal_uInt8 nParamCount = GetByte();
3955     if ( MustHaveParamCount( nParamCount, 0, 1 ) )
3956     {
3957         SCTAB nVal = 0;
3958         if ( nParamCount == 0 )
3959             nVal = aPos.Tab() + 1;
3960         else
3961         {
3962             switch ( GetStackType() )
3963             {
3964                 case svString :
3965                 {
3966                     String aStr( PopString() );
3967                     if ( pDok->GetTable( aStr, nVal ) )
3968                         ++nVal;
3969                     else
3970                         SetError( errIllegalArgument );
3971                 }
3972                 break;
3973                 case svSingleRef :
3974                 {
3975                     SCCOL nCol1;
3976                     SCROW nRow1;
3977                     SCTAB nTab1;
3978                     PopSingleRef( nCol1, nRow1, nTab1 );
3979                     nVal = nTab1 + 1;
3980                 }
3981                 break;
3982                 case svDoubleRef :
3983                 {
3984                     SCCOL nCol1;
3985                     SCROW nRow1;
3986                     SCTAB nTab1;
3987                     SCCOL nCol2;
3988                     SCROW nRow2;
3989                     SCTAB nTab2;
3990                     PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
3991                     nVal = nTab1 + 1;
3992                 }
3993                 break;
3994                 default:
3995                     SetError( errIllegalParameter );
3996             }
3997             if ( nGlobalError )
3998                 nVal = 0;
3999         }
4000         PushDouble( (double) nVal );
4001     }
4002 }
4003 
4004 /** returns -1 when the matrix value is smaller than the query value, 0 when
4005     they are equal, and 1 when the matrix value is larger than the query
4006     value. */
4007 static sal_Int32 lcl_CompareMatrix2Query( SCSIZE i, const ScMatrix& rMat,
4008         const ScQueryEntry& rEntry)
4009 {
4010     if (rMat.IsEmpty(i))
4011     {
4012         /* TODO: in case we introduced query for real empty this would have to
4013          * be changed! */
4014         return -1;      // empty always less than anything else
4015     }
4016 
4017     /* FIXME: what is an empty path (result of IF(false;true_path) in
4018      * comparisons? */
4019 
4020     if (rMat.IsValue(i))
4021     {
4022         if (rEntry.bQueryByString)
4023             return -1;  // numeric always less than string
4024 
4025         const double nVal1 = rMat.GetDouble(i);
4026         const double nVal2 = rEntry.nVal;
4027         if (nVal1 == nVal2)
4028             return 0;
4029 
4030         return nVal1 < nVal2 ? -1 : 1;
4031     }
4032 
4033     if (!rEntry.bQueryByString)
4034         return 1;       // string always greater than numeric
4035 
4036     if (!rEntry.pStr)
4037         // this should not happen!
4038         return 1;
4039 
4040     const String& rStr1 = rMat.GetString(i);
4041     const String& rStr2 = *rEntry.pStr;
4042 
4043     return ScGlobal::GetCollator()->compareString( rStr1, rStr2); // case-insensitive
4044 }
4045 
4046 /** returns the last item with the identical value as the original item
4047     value. */
4048 static void lcl_GetLastMatch( SCSIZE& rIndex, const ScMatrix& rMat,
4049         SCSIZE nMatCount, bool bReverse)
4050 {
4051     if (rMat.IsValue(rIndex))
4052     {
4053         double nVal = rMat.GetDouble(rIndex);
4054         if (bReverse)
4055             while (rIndex > 0 && rMat.IsValue(rIndex-1) &&
4056                     nVal == rMat.GetDouble(rIndex-1))
4057                 --rIndex;
4058         else
4059             while (rIndex < nMatCount-1 && rMat.IsValue(rIndex+1) &&
4060                     nVal == rMat.GetDouble(rIndex+1))
4061                 ++rIndex;
4062     }
4063     //! Order of IsEmptyPath, IsEmpty, IsString is significant!
4064     else if (rMat.IsEmptyPath(rIndex))
4065     {
4066         if (bReverse)
4067             while (rIndex > 0 && rMat.IsEmptyPath(rIndex-1))
4068                 --rIndex;
4069         else
4070             while (rIndex < nMatCount-1 && rMat.IsEmptyPath(rIndex+1))
4071                 ++rIndex;
4072     }
4073     else if (rMat.IsEmpty(rIndex))
4074     {
4075         if (bReverse)
4076             while (rIndex > 0 && rMat.IsEmpty(rIndex-1))
4077                 --rIndex;
4078         else
4079             while (rIndex < nMatCount-1 && rMat.IsEmpty(rIndex+1))
4080                 ++rIndex;
4081     }
4082     else if (rMat.IsString(rIndex))
4083     {
4084         String aStr( rMat.GetString(rIndex));
4085         if (bReverse)
4086             while (rIndex > 0 && rMat.IsString(rIndex-1) &&
4087                     aStr == rMat.GetString(rIndex-1))
4088                 --rIndex;
4089         else
4090             while (rIndex < nMatCount-1 && rMat.IsString(rIndex+1) &&
4091                     aStr == rMat.GetString(rIndex+1))
4092                 ++rIndex;
4093     }
4094     else
4095     {
4096         DBG_ERRORFILE("lcl_GetLastMatch: unhandled matrix type");
4097     }
4098 }
4099 
4100 void ScInterpreter::ScMatch()
4101 {
4102     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScMatch" );
4103     ScMatrixRef pMatSrc = NULL;
4104 
4105     sal_uInt8 nParamCount = GetByte();
4106     if ( MustHaveParamCount( nParamCount, 2, 3 ) )
4107     {
4108         double fTyp;
4109         if (nParamCount == 3)
4110             fTyp = GetDouble();
4111         else
4112             fTyp = 1.0;
4113         SCCOL nCol1 = 0;
4114         SCROW nRow1 = 0;
4115         SCTAB nTab1 = 0;
4116         SCCOL nCol2 = 0;
4117         SCROW nRow2 = 0;
4118         SCTAB nTab2 = 0;
4119         if (GetStackType() == svDoubleRef)
4120         {
4121             PopDoubleRef(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
4122             if (nTab1 != nTab2 || (nCol1 != nCol2 && nRow1 != nRow2))
4123             {
4124                 PushIllegalParameter();
4125                 return;
4126             }
4127         }
4128         else if (GetStackType() == svMatrix)
4129         {
4130             pMatSrc = PopMatrix();
4131             if (!pMatSrc)
4132             {
4133                 PushIllegalParameter();
4134                 return;
4135             }
4136         }
4137         else
4138         {
4139             PushIllegalParameter();
4140             return;
4141         }
4142         if (nGlobalError == 0)
4143         {
4144             double fVal;
4145             String sStr;
4146             ScQueryParam rParam;
4147             rParam.nCol1       = nCol1;
4148             rParam.nRow1       = nRow1;
4149             rParam.nCol2       = nCol2;
4150             rParam.nTab        = nTab1;
4151             rParam.bMixedComparison = sal_True;
4152 
4153             ScQueryEntry& rEntry = rParam.GetEntry(0);
4154             rEntry.bDoQuery = sal_True;
4155             if (fTyp < 0.0)
4156                 rEntry.eOp = SC_GREATER_EQUAL;
4157             else if (fTyp > 0.0)
4158                 rEntry.eOp = SC_LESS_EQUAL;
4159             switch ( GetStackType() )
4160             {
4161                 case svDouble:
4162                 {
4163                     fVal = GetDouble();
4164                     rEntry.bQueryByString = sal_False;
4165                     rEntry.nVal = fVal;
4166                 }
4167                 break;
4168                 case svString:
4169                 {
4170                     sStr = GetString();
4171                     rEntry.bQueryByString = sal_True;
4172                     *rEntry.pStr = sStr;
4173                 }
4174                 break;
4175                 case svDoubleRef :
4176                 case svSingleRef :
4177                 {
4178                     ScAddress aAdr;
4179                     if ( !PopDoubleRefOrSingleRef( aAdr ) )
4180                     {
4181                         PushInt(0);
4182                         return ;
4183                     }
4184                     ScBaseCell* pCell = GetCell( aAdr );
4185                     if (HasCellValueData(pCell))
4186                     {
4187                         fVal = GetCellValue( aAdr, pCell );
4188                         rEntry.bQueryByString = sal_False;
4189                         rEntry.nVal = fVal;
4190                     }
4191                     else
4192                     {
4193                         GetCellString(sStr, pCell);
4194                         rEntry.bQueryByString = sal_True;
4195                         *rEntry.pStr = sStr;
4196                     }
4197                 }
4198                 break;
4199                 case svMatrix :
4200                 {
4201                     ScMatValType nType = GetDoubleOrStringFromMatrix(
4202                             rEntry.nVal, *rEntry.pStr);
4203                     rEntry.bQueryByString = ScMatrix::IsNonValueType( nType);
4204                 }
4205                 break;
4206                 default:
4207                 {
4208                     PushIllegalParameter();
4209                     return;
4210                 }
4211             }
4212             if ( rEntry.bQueryByString )
4213                 rParam.bRegExp = MayBeRegExp( *rEntry.pStr, pDok );
4214 
4215             if (pMatSrc) // The source data is matrix array.
4216             {
4217                 SCSIZE nC, nR;
4218                 pMatSrc->GetDimensions( nC, nR);
4219                 if (nC > 1 && nR > 1)
4220                 {
4221                     // The source matrix must be a vector.
4222                     PushIllegalParameter();
4223                     return;
4224                 }
4225                 SCSIZE nMatCount = (nC == 1) ? nR : nC;
4226 
4227                 // simple serial search for equality mode (source data doesn't
4228                 // need to be sorted).
4229 
4230                 if (rEntry.eOp == SC_EQUAL)
4231                 {
4232                     for (SCSIZE i = 0; i < nMatCount; ++i)
4233                     {
4234                         if (lcl_CompareMatrix2Query( i, *pMatSrc, rEntry) == 0)
4235                         {
4236                             PushDouble(i+1); // found !
4237                             return;
4238                         }
4239                     }
4240                     PushNA(); // not found
4241                     return;
4242                 }
4243 
4244                 // binary search for non-equality mode (the source data is
4245                 // assumed to be sorted).
4246 
4247                 bool bAscOrder = (rEntry.eOp == SC_LESS_EQUAL);
4248                 SCSIZE nFirst = 0, nLast = nMatCount-1, nHitIndex = 0;
4249                 for (SCSIZE nLen = nLast-nFirst; nLen > 0; nLen = nLast-nFirst)
4250                 {
4251                     SCSIZE nMid = nFirst + nLen/2;
4252                     sal_Int32 nCmp = lcl_CompareMatrix2Query( nMid, *pMatSrc, rEntry);
4253                     if (nCmp == 0)
4254                     {
4255                         // exact match.  find the last item with the same value.
4256                         lcl_GetLastMatch( nMid, *pMatSrc, nMatCount, !bAscOrder);
4257                         PushDouble( nMid+1);
4258                         return;
4259                     }
4260 
4261                     if (nLen == 1) // first and last items are next to each other.
4262                     {
4263                         if (nCmp < 0)
4264                             nHitIndex = bAscOrder ? nLast : nFirst;
4265                         else
4266                             nHitIndex = bAscOrder ? nFirst : nLast;
4267                         break;
4268                     }
4269 
4270                     if (nCmp < 0)
4271                     {
4272                         if (bAscOrder)
4273                             nFirst = nMid;
4274                         else
4275                             nLast = nMid;
4276                     }
4277                     else
4278                     {
4279                         if (bAscOrder)
4280                             nLast = nMid;
4281                         else
4282                             nFirst = nMid;
4283                     }
4284                 }
4285 
4286                 if (nHitIndex == nMatCount-1) // last item
4287                 {
4288                     sal_Int32 nCmp = lcl_CompareMatrix2Query( nHitIndex, *pMatSrc, rEntry);
4289                     if ((bAscOrder && nCmp <= 0) || (!bAscOrder && nCmp >= 0))
4290                     {
4291                         // either the last item is an exact match or the real
4292                         // hit is beyond the last item.
4293                         PushDouble( nHitIndex+1);
4294                         return;
4295                     }
4296                 }
4297 
4298                 if (nHitIndex > 0) // valid hit must be 2nd item or higher
4299                 {
4300                     PushDouble( nHitIndex); // non-exact match
4301                     return;
4302                 }
4303 
4304                 PushNA();
4305                 return;
4306             }
4307 
4308             SCCOLROW nDelta = 0;
4309             if (nCol1 == nCol2)
4310             {                                           // search row in column
4311                 rParam.nRow2 = nRow2;
4312                 rEntry.nField = nCol1;
4313                 ScAddress aResultPos( nCol1, nRow1, nTab1);
4314                 if (!LookupQueryWithCache( aResultPos, rParam))
4315                 {
4316                     PushNA();
4317                     return;
4318                 }
4319                 nDelta = aResultPos.Row() - nRow1;
4320             }
4321             else
4322             {                                           // search column in row
4323                 SCCOL nC;
4324                 rParam.bByRow = sal_False;
4325                 rParam.nRow2 = nRow1;
4326                 rEntry.nField = nCol1;
4327                 ScQueryCellIterator aCellIter(pDok, nTab1, rParam, sal_False);
4328                 // Advance Entry.nField in Iterator if column changed
4329                 aCellIter.SetAdvanceQueryParamEntryField( sal_True );
4330                 if (fTyp == 0.0)
4331                 {                                       // EQUAL
4332                     if ( aCellIter.GetFirst() )
4333                         nC = aCellIter.GetCol();
4334                     else
4335                     {
4336                         PushNA();
4337                         return;
4338                     }
4339                 }
4340                 else
4341                 {                                       // <= or >=
4342                     SCROW nR;
4343                     if ( !aCellIter.FindEqualOrSortedLastInRange( nC, nR ) )
4344                     {
4345                         PushNA();
4346                         return;
4347                     }
4348                 }
4349                 nDelta = nC - nCol1;
4350             }
4351             PushDouble((double) (nDelta + 1));
4352         }
4353         else
4354             PushIllegalParameter();
4355     }
4356 }
4357 
4358 
4359 void ScInterpreter::ScCountEmptyCells()
4360 {
4361     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCountEmptyCells" );
4362     if ( MustHaveParamCount( GetByte(), 1 ) )
4363     {
4364         sal_uLong nMaxCount = 0, nCount = 0;
4365         CellType eCellType;
4366         switch (GetStackType())
4367         {
4368             case svSingleRef :
4369             {
4370                 nMaxCount = 1;
4371                 ScAddress aAdr;
4372                 PopSingleRef( aAdr );
4373                 eCellType = GetCellType( GetCell( aAdr ) );
4374                 if (eCellType != CELLTYPE_NONE && eCellType != CELLTYPE_NOTE)
4375                     nCount = 1;
4376             }
4377             break;
4378             case svDoubleRef :
4379             case svRefList :
4380             {
4381                 ScRange aRange;
4382                 short nParam = 1;
4383                 size_t nRefInList = 0;
4384                 while (nParam-- > 0)
4385                 {
4386                     PopDoubleRef( aRange, nParam, nRefInList);
4387                     nMaxCount +=
4388                         static_cast<sal_uLong>(aRange.aEnd.Row() - aRange.aStart.Row() + 1) *
4389                         static_cast<sal_uLong>(aRange.aEnd.Col() - aRange.aStart.Col() + 1) *
4390                         static_cast<sal_uLong>(aRange.aEnd.Tab() - aRange.aStart.Tab() + 1);
4391                     ScBaseCell* pCell;
4392                     ScCellIterator aDocIter( pDok, aRange, glSubTotal);
4393                     if ( (pCell = aDocIter.GetFirst()) != NULL )
4394                     {
4395                         do
4396                         {
4397                             if ((eCellType = pCell->GetCellType()) != CELLTYPE_NONE
4398                                     && eCellType != CELLTYPE_NOTE)
4399                                 nCount++;
4400                         } while ( (pCell = aDocIter.GetNext()) != NULL );
4401                     }
4402                 }
4403             }
4404             break;
4405             default : SetError(errIllegalParameter); break;
4406         }
4407         PushDouble(nMaxCount - nCount);
4408     }
4409 }
4410 
4411 
4412 double ScInterpreter::IterateParametersIf( ScIterFuncIf eFunc )
4413 {
4414     sal_uInt8 nParamCount = GetByte();
4415     if ( MustHaveParamCount( nParamCount, 2, 3 ) )
4416     {
4417         SCCOL nCol3 = 0;
4418         SCROW nRow3 = 0;
4419         SCTAB nTab3 = 0;
4420 
4421         ScMatrixRef pSumExtraMatrix;
4422         bool bSumExtraRange = (nParamCount == 3);
4423         if (bSumExtraRange)
4424         {
4425             // Save only the upperleft cell in case of cell range.  The geometry
4426             // of the 3rd parameter is taken from the 1st parameter.
4427 
4428             switch ( GetStackType() )
4429             {
4430                 case svDoubleRef :
4431                     {
4432                         SCCOL nColJunk = 0;
4433                         SCROW nRowJunk = 0;
4434                         SCTAB nTabJunk = 0;
4435                         PopDoubleRef( nCol3, nRow3, nTab3, nColJunk, nRowJunk, nTabJunk );
4436                         if ( nTabJunk != nTab3 )
4437                         {
4438                             SetError( errIllegalParameter);
4439                         }
4440                     }
4441                     break;
4442                 case svSingleRef :
4443                     PopSingleRef( nCol3, nRow3, nTab3 );
4444                     break;
4445                 case svMatrix:
4446                     pSumExtraMatrix = PopMatrix();
4447                     //! nCol3, nRow3, nTab3 remain 0
4448                     break;
4449                 default:
4450                     SetError( errIllegalParameter);
4451             }
4452         }
4453         String rString;
4454         double fVal = 0.0;
4455         bool bIsString = true;
4456         switch ( GetStackType() )
4457         {
4458             case svDoubleRef :
4459             case svSingleRef :
4460                 {
4461                     ScAddress aAdr;
4462                     if ( !PopDoubleRefOrSingleRef( aAdr ) )
4463                         return 0;
4464 
4465                     ScBaseCell* pCell = GetCell( aAdr );
4466                     switch ( GetCellType( pCell ) )
4467                     {
4468                         case CELLTYPE_VALUE :
4469                             fVal = GetCellValue( aAdr, pCell );
4470                             bIsString = false;
4471                             break;
4472                         case CELLTYPE_FORMULA :
4473                             if( ((ScFormulaCell*)pCell)->IsValue() )
4474                             {
4475                                 fVal = GetCellValue( aAdr, pCell );
4476                                 bIsString = false;
4477                             }
4478                             else
4479                                 GetCellString(rString, pCell);
4480                             break;
4481                         case CELLTYPE_STRING :
4482                         case CELLTYPE_EDIT :
4483                             GetCellString(rString, pCell);
4484                             break;
4485                         default:
4486                             fVal = 0.0;
4487                             bIsString = false;
4488                     }
4489                 }
4490                 break;
4491             case svString:
4492                 rString = GetString();
4493                 break;
4494             case svMatrix :
4495                 {
4496                     ScMatValType nType = GetDoubleOrStringFromMatrix( fVal, rString);
4497                     bIsString = ScMatrix::IsNonValueType( nType);
4498                 }
4499                 break;
4500             default:
4501                 {
4502                     fVal = GetDouble();
4503                     bIsString = false;
4504                 }
4505         }
4506 
4507         double fSum = 0.0;
4508         double fMem = 0.0;
4509         double fRes = 0.0;
4510         double fCount = 0.0;
4511         bool bNull = true;
4512         short nParam = 1;
4513         size_t nRefInList = 0;
4514         while (nParam-- > 0)
4515         {
4516             SCCOL nCol1;
4517             SCROW nRow1;
4518             SCTAB nTab1;
4519             SCCOL nCol2;
4520             SCROW nRow2;
4521             SCTAB nTab2;
4522             ScMatrixRef pQueryMatrix;
4523             switch ( GetStackType() )
4524             {
4525                 case svRefList :
4526                     if (bSumExtraRange)
4527                     {
4528                         SetError( errIllegalParameter);
4529                     }
4530                     else
4531                     {
4532                         ScRange aRange;
4533                         PopDoubleRef( aRange, nParam, nRefInList);
4534                         aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
4535                     }
4536                     break;
4537                 case svDoubleRef :
4538                     PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
4539                     break;
4540                 case svSingleRef :
4541                     PopSingleRef( nCol1, nRow1, nTab1 );
4542                     nCol2 = nCol1;
4543                     nRow2 = nRow1;
4544                     nTab2 = nTab1;
4545                     break;
4546                 case svMatrix:
4547                     {
4548                         pQueryMatrix = PopMatrix();
4549                         if (!pQueryMatrix)
4550                         {
4551                             SetError( errIllegalParameter);
4552                         }
4553                         nCol1 = 0;
4554                         nRow1 = 0;
4555                         nTab1 = 0;
4556                         SCSIZE nC, nR;
4557                         pQueryMatrix->GetDimensions( nC, nR);
4558                         nCol2 = static_cast<SCCOL>(nC - 1);
4559                         nRow2 = static_cast<SCROW>(nR - 1);
4560                         nTab2 = 0;
4561                     }
4562                     break;
4563                 default:
4564                     SetError( errIllegalParameter);
4565             }
4566             if ( nTab1 != nTab2 )
4567             {
4568                 SetError( errIllegalParameter);
4569             }
4570 
4571             if (bSumExtraRange)
4572             {
4573                 // Take the range geometry of the 1st parameter and apply it to
4574                 // the 3rd. If parts of the resulting range would point outside
4575                 // the sheet, don't complain but silently ignore and simply cut
4576                 // them away, this is what Xcl does :-/
4577 
4578                 // For the cut-away part we also don't need to determine the
4579                 // criteria match, so shrink the source range accordingly,
4580                 // instead of the result range.
4581                 SCCOL nColDelta = nCol2 - nCol1;
4582                 SCROW nRowDelta = nRow2 - nRow1;
4583                 SCCOL nMaxCol;
4584                 SCROW nMaxRow;
4585                 if (pSumExtraMatrix)
4586                 {
4587                     SCSIZE nC, nR;
4588                     pSumExtraMatrix->GetDimensions( nC, nR);
4589                     nMaxCol = static_cast<SCCOL>(nC - 1);
4590                     nMaxRow = static_cast<SCROW>(nR - 1);
4591                 }
4592                 else
4593                 {
4594                     nMaxCol = MAXCOL;
4595                     nMaxRow = MAXROW;
4596                 }
4597                 if (nCol3 + nColDelta > nMaxCol)
4598                 {
4599                     SCCOL nNewDelta = nMaxCol - nCol3;
4600                     nCol2 = nCol1 + nNewDelta;
4601                 }
4602 
4603                 if (nRow3 + nRowDelta > nMaxRow)
4604                 {
4605                     SCROW nNewDelta = nMaxRow - nRow3;
4606                     nRow2 = nRow1 + nNewDelta;
4607                 }
4608             }
4609             else
4610             {
4611                 nCol3 = nCol1;
4612                 nRow3 = nRow1;
4613                 nTab3 = nTab1;
4614             }
4615 
4616             if (nGlobalError == 0)
4617             {
4618                 ScQueryParam rParam;
4619                 rParam.nRow1       = nRow1;
4620                 rParam.nRow2       = nRow2;
4621 
4622                 ScQueryEntry& rEntry = rParam.GetEntry(0);
4623                 rEntry.bDoQuery = true;
4624                 if (!bIsString)
4625                 {
4626                     rEntry.bQueryByString = false;
4627                     rEntry.nVal = fVal;
4628                     rEntry.eOp = SC_EQUAL;
4629                 }
4630                 else
4631                 {
4632                     rParam.FillInExcelSyntax(rString, 0);
4633                     sal_uInt32 nIndex = 0;
4634                     rEntry.bQueryByString =
4635                         !(pFormatter->IsNumberFormat(
4636                                     *rEntry.pStr, nIndex, rEntry.nVal));
4637                     if ( rEntry.bQueryByString )
4638                         rParam.bRegExp = MayBeRegExp( *rEntry.pStr, pDok );
4639                 }
4640                 ScAddress aAdr;
4641                 aAdr.SetTab( nTab3 );
4642                 rParam.nCol1  = nCol1;
4643                 rParam.nCol2  = nCol2;
4644                 rEntry.nField = nCol1;
4645                 SCsCOL nColDiff = nCol3 - nCol1;
4646                 SCsROW nRowDiff = nRow3 - nRow1;
4647                 if (pQueryMatrix)
4648                 {
4649                     // Never case-sensitive.
4650                     ScCompareOptions aOptions( pDok, rEntry, rParam.bRegExp);
4651                     ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
4652                     if (nGlobalError || !pResultMatrix)
4653                     {
4654                         SetError( errIllegalParameter);
4655                     }
4656 
4657                     if (pSumExtraMatrix)
4658                     {
4659                         for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
4660                         {
4661                             for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
4662                             {
4663                                 if (pResultMatrix->IsValue( nCol, nRow) &&
4664                                         pResultMatrix->GetDouble( nCol, nRow))
4665                                 {
4666                                     SCSIZE nC = nCol + nColDiff;
4667                                     SCSIZE nR = nRow + nRowDiff;
4668                                     if (pSumExtraMatrix->IsValue( nC, nR))
4669                                     {
4670                                         fVal = pSumExtraMatrix->GetDouble( nC, nR);
4671                                         ++fCount;
4672                                         if ( bNull && fVal != 0.0 )
4673                                         {
4674                                             bNull = false;
4675                                             fMem = fVal;
4676                                         }
4677                                         else
4678                                             fSum += fVal;
4679                                     }
4680                                 }
4681                             }
4682                         }
4683                     }
4684                     else
4685                     {
4686                         for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
4687                         {
4688                             for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
4689                             {
4690                                 if (pResultMatrix->GetDouble( nCol, nRow))
4691                                 {
4692                                     aAdr.SetCol( nCol + nColDiff);
4693                                     aAdr.SetRow( nRow + nRowDiff);
4694                                     ScBaseCell* pCell = GetCell( aAdr );
4695                                     if ( HasCellValueData(pCell) )
4696                                     {
4697                                         fVal = GetCellValue( aAdr, pCell );
4698                                         ++fCount;
4699                                         if ( bNull && fVal != 0.0 )
4700                                         {
4701                                             bNull = false;
4702                                             fMem = fVal;
4703                                         }
4704                                         else
4705                                             fSum += fVal;
4706                                     }
4707                                 }
4708                             }
4709                         }
4710                     }
4711                 }
4712                 else
4713                 {
4714                     ScQueryCellIterator aCellIter(pDok, nTab1, rParam, false);
4715                     // Increment Entry.nField in iterator when switching to next column.
4716                     aCellIter.SetAdvanceQueryParamEntryField( true );
4717                     if ( aCellIter.GetFirst() )
4718                     {
4719                         if (pSumExtraMatrix)
4720                         {
4721                             do
4722                             {
4723                                 SCSIZE nC = aCellIter.GetCol() + nColDiff;
4724                                 SCSIZE nR = aCellIter.GetRow() + nRowDiff;
4725                                 if (pSumExtraMatrix->IsValue( nC, nR))
4726                                 {
4727                                     fVal = pSumExtraMatrix->GetDouble( nC, nR);
4728                                     ++fCount;
4729                                     if ( bNull && fVal != 0.0 )
4730                                     {
4731                                         bNull = false;
4732                                         fMem = fVal;
4733                                     }
4734                                     else
4735                                         fSum += fVal;
4736                                 }
4737                             } while ( aCellIter.GetNext() );
4738                         }
4739                         else
4740                         {
4741                             do
4742                             {
4743                                 aAdr.SetCol( aCellIter.GetCol() + nColDiff);
4744                                 aAdr.SetRow( aCellIter.GetRow() + nRowDiff);
4745                                 ScBaseCell* pCell = GetCell( aAdr );
4746                                 if ( HasCellValueData(pCell) )
4747                                 {
4748                                     fVal = GetCellValue( aAdr, pCell );
4749                                     ++fCount;
4750                                     if ( bNull && fVal != 0.0 )
4751                                     {
4752                                         bNull = false;
4753                                         fMem = fVal;
4754                                     }
4755                                     else
4756                                         fSum += fVal;
4757                                 }
4758                             } while ( aCellIter.GetNext() );
4759                         }
4760                     }
4761                 }
4762             }
4763             else
4764             {
4765                 SetError( errIllegalParameter);
4766             }
4767         }
4768 
4769         switch( eFunc )
4770         {
4771             case ifSUMIF:     fRes = ::rtl::math::approxAdd( fSum, fMem ); break;
4772             case ifAVERAGEIF: fRes = div( ::rtl::math::approxAdd( fSum, fMem ), fCount); break;
4773         }
4774         return fRes;
4775     }
4776     return 0;
4777 }
4778 
4779 void ScInterpreter::ScSumIf()
4780 {
4781     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSumIf" );
4782     PushDouble( IterateParametersIf( ifSUMIF));
4783 }
4784 
4785 void ScInterpreter::ScAverageIf()
4786 {
4787     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "makkica", "ScInterpreter::ScAverageIf" );
4788     PushDouble( IterateParametersIf( ifAVERAGEIF));
4789 }
4790 
4791 void ScInterpreter::ScCountIf()
4792 {
4793     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCountIf" );
4794     if ( MustHaveParamCount( GetByte(), 2 ) )
4795     {
4796         String rString;
4797         double fVal = 0.0;
4798         sal_Bool bIsString = sal_True;
4799         switch ( GetStackType() )
4800         {
4801             case svDoubleRef :
4802             case svSingleRef :
4803             {
4804                 ScAddress aAdr;
4805                 if ( !PopDoubleRefOrSingleRef( aAdr ) )
4806                 {
4807                     PushInt(0);
4808                     return ;
4809                 }
4810                 ScBaseCell* pCell = GetCell( aAdr );
4811                 switch ( GetCellType( pCell ) )
4812                 {
4813                     case CELLTYPE_VALUE :
4814                         fVal = GetCellValue( aAdr, pCell );
4815                         bIsString = sal_False;
4816                         break;
4817                     case CELLTYPE_FORMULA :
4818                         if( ((ScFormulaCell*)pCell)->IsValue() )
4819                         {
4820                             fVal = GetCellValue( aAdr, pCell );
4821                             bIsString = sal_False;
4822                         }
4823                         else
4824                             GetCellString(rString, pCell);
4825                         break;
4826                     case CELLTYPE_STRING :
4827                     case CELLTYPE_EDIT :
4828                         GetCellString(rString, pCell);
4829                         break;
4830                     default:
4831                         fVal = 0.0;
4832                         bIsString = sal_False;
4833                 }
4834             }
4835             break;
4836             case svMatrix :
4837             {
4838                 ScMatValType nType = GetDoubleOrStringFromMatrix( fVal, rString);
4839                 bIsString = ScMatrix::IsNonValueType( nType);
4840             }
4841             break;
4842             case svString:
4843                 rString = GetString();
4844             break;
4845             default:
4846             {
4847                 fVal = GetDouble();
4848                 bIsString = sal_False;
4849             }
4850         }
4851         double fCount = 0.0;
4852         short nParam = 1;
4853         size_t nRefInList = 0;
4854         while (nParam-- > 0)
4855         {
4856             SCCOL nCol1;
4857             SCROW nRow1;
4858             SCTAB nTab1;
4859             SCCOL nCol2;
4860             SCROW nRow2;
4861             SCTAB nTab2;
4862             ScMatrixRef pQueryMatrix;
4863             switch ( GetStackType() )
4864             {
4865                 case svDoubleRef :
4866                 case svRefList :
4867                     {
4868                         ScRange aRange;
4869                         PopDoubleRef( aRange, nParam, nRefInList);
4870                         aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
4871                     }
4872                     break;
4873                 case svSingleRef :
4874                     PopSingleRef( nCol1, nRow1, nTab1 );
4875                     nCol2 = nCol1;
4876                     nRow2 = nRow1;
4877                     nTab2 = nTab1;
4878                     break;
4879                 case svMatrix:
4880                     {
4881                         pQueryMatrix = PopMatrix();
4882                         if (!pQueryMatrix)
4883                         {
4884                             PushIllegalParameter();
4885                             return;
4886                         }
4887                         nCol1 = 0;
4888                         nRow1 = 0;
4889                         nTab1 = 0;
4890                         SCSIZE nC, nR;
4891                         pQueryMatrix->GetDimensions( nC, nR);
4892                         nCol2 = static_cast<SCCOL>(nC - 1);
4893                         nRow2 = static_cast<SCROW>(nR - 1);
4894                         nTab2 = 0;
4895                     }
4896                     break;
4897                 default:
4898                     PushIllegalParameter();
4899                     return ;
4900             }
4901             if ( nTab1 != nTab2 )
4902             {
4903                 PushIllegalParameter();
4904                 return;
4905             }
4906             if (nCol1 > nCol2)
4907             {
4908                 PushIllegalParameter();
4909                 return;
4910             }
4911             if (nGlobalError == 0)
4912             {
4913                 ScQueryParam rParam;
4914                 rParam.nRow1       = nRow1;
4915                 rParam.nRow2       = nRow2;
4916 
4917                 ScQueryEntry& rEntry = rParam.GetEntry(0);
4918                 rEntry.bDoQuery = sal_True;
4919                 if (!bIsString)
4920                 {
4921                     rEntry.bQueryByString = sal_False;
4922                     rEntry.nVal = fVal;
4923                     rEntry.eOp = SC_EQUAL;
4924                 }
4925                 else
4926                 {
4927                     rParam.FillInExcelSyntax(rString, 0);
4928                     sal_uInt32 nIndex = 0;
4929                     rEntry.bQueryByString =
4930                         !(pFormatter->IsNumberFormat(
4931                                     *rEntry.pStr, nIndex, rEntry.nVal));
4932                     if ( rEntry.bQueryByString )
4933                         rParam.bRegExp = MayBeRegExp( *rEntry.pStr, pDok );
4934                 }
4935                 rParam.nCol1  = nCol1;
4936                 rParam.nCol2  = nCol2;
4937                 rEntry.nField = nCol1;
4938                 if (pQueryMatrix)
4939                 {
4940                     // Never case-sensitive.
4941                     ScCompareOptions aOptions( pDok, rEntry, rParam.bRegExp);
4942                     ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
4943                     if (nGlobalError || !pResultMatrix)
4944                     {
4945                         PushIllegalParameter();
4946                         return;
4947                     }
4948 
4949                     SCSIZE nSize = pResultMatrix->GetElementCount();
4950                     for (SCSIZE nIndex = 0; nIndex < nSize; ++nIndex)
4951                     {
4952                         if (pResultMatrix->IsValue( nIndex) &&
4953                                 pResultMatrix->GetDouble( nIndex))
4954                             ++fCount;
4955                     }
4956                 }
4957                 else
4958                 {
4959                     ScQueryCellIterator aCellIter(pDok, nTab1, rParam, sal_False);
4960                     // Entry.nField im Iterator bei Spaltenwechsel weiterschalten
4961                     aCellIter.SetAdvanceQueryParamEntryField( sal_True );
4962                     if ( aCellIter.GetFirst() )
4963                     {
4964                         do
4965                         {
4966                             fCount++;
4967                         } while ( aCellIter.GetNext() );
4968                     }
4969                 }
4970             }
4971             else
4972             {
4973                 PushIllegalParameter();
4974                 return;
4975             }
4976         }
4977         PushDouble(fCount);
4978     }
4979 }
4980 
4981 
4982 void ScInterpreter::ScLookup()
4983 {
4984     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLookup" );
4985     sal_uInt8 nParamCount = GetByte();
4986     if ( !MustHaveParamCount( nParamCount, 2, 3 ) )
4987         return ;
4988 
4989     ScMatrixRef pDataMat = NULL, pResMat = NULL;
4990     SCCOL nCol1 = 0, nCol2 = 0, nResCol1 = 0, nResCol2 = 0;
4991     SCROW nRow1 = 0, nRow2 = 0, nResRow1 = 0, nResRow2 = 0;
4992     SCTAB nTab1 = 0, nResTab = 0;
4993     SCSIZE nLenMajor = 0;   // length of major direction
4994     bool bVertical = true;  // whether to lookup vertically or horizontally
4995 
4996     // The third parameter, result array, for double, string and single reference.
4997     double fResVal = 0.0;
4998     String aResStr;
4999     ScAddress aResAdr;
5000     StackVar eResArrayType = svUnknown;
5001 
5002     if (nParamCount == 3)
5003     {
5004         eResArrayType = GetStackType();
5005         switch (eResArrayType)
5006         {
5007             case svDoubleRef:
5008             {
5009                 SCTAB nTabJunk;
5010                 PopDoubleRef(nResCol1, nResRow1, nResTab,
5011                              nResCol2, nResRow2, nTabJunk);
5012                 if (nResTab != nTabJunk ||
5013                     ((nResRow2 - nResRow1) > 0 && (nResCol2 - nResCol1) > 0))
5014                 {
5015                     // The result array must be a vector.
5016                     PushIllegalParameter();
5017                     return;
5018                 }
5019             }
5020             break;
5021             case svMatrix:
5022             {
5023                 pResMat = PopMatrix();
5024                 if (!pResMat)
5025                 {
5026                     PushIllegalParameter();
5027                     return;
5028                 }
5029                 SCSIZE nC, nR;
5030                 pResMat->GetDimensions(nC, nR);
5031                 if (nC != 1 && nR != 1)
5032                 {
5033                     // Result matrix must be a vector.
5034                     PushIllegalParameter();
5035                     return;
5036                 }
5037             }
5038             break;
5039             case svDouble:
5040                 fResVal = GetDouble();
5041             break;
5042             case svString:
5043                 aResStr = GetString();
5044             break;
5045             case svSingleRef:
5046                 PopSingleRef( aResAdr );
5047             break;
5048             default:
5049                 PushIllegalParameter();
5050                 return;
5051         }
5052     }
5053 
5054     // For double, string and single reference.
5055     double fDataVal = 0.0;
5056     String aDataStr;
5057     ScAddress aDataAdr;
5058     bool bValueData = false;
5059 
5060     // Get the data-result range and also determine whether this is vertical
5061     // lookup or horizontal lookup.
5062 
5063     StackVar eDataArrayType = GetStackType();
5064     switch (eDataArrayType)
5065     {
5066         case svDoubleRef:
5067         {
5068             SCTAB nTabJunk;
5069             PopDoubleRef(nCol1, nRow1, nTab1, nCol2, nRow2, nTabJunk);
5070             if (nTab1 != nTabJunk)
5071             {
5072                 PushIllegalParameter();
5073                 return;
5074             }
5075             bVertical = (nRow2 - nRow1) >= (nCol2 - nCol1);
5076             nLenMajor = bVertical ? nRow2 - nRow1 + 1 : nCol2 - nCol1 + 1;
5077         }
5078         break;
5079         case svMatrix:
5080         {
5081             pDataMat = PopMatrix();
5082             if (!pDataMat)
5083             {
5084                 PushIllegalParameter();
5085                 return;
5086             }
5087 
5088             SCSIZE nC, nR;
5089             pDataMat->GetDimensions(nC, nR);
5090             bVertical = (nR >= nC);
5091             nLenMajor = bVertical ? nR : nC;
5092         }
5093         break;
5094         case svDouble:
5095         {
5096             fDataVal = GetDouble();
5097             bValueData = true;
5098         }
5099         break;
5100         case svString:
5101         {
5102             aDataStr = GetString();
5103         }
5104         break;
5105         case svSingleRef:
5106         {
5107             PopSingleRef( aDataAdr );
5108             const ScBaseCell* pDataCell = GetCell( aDataAdr );
5109             if (HasCellEmptyData( pDataCell))
5110             {
5111                 // Empty cells aren't found anywhere, bail out early.
5112                 SetError( NOTAVAILABLE);
5113             }
5114             else if (HasCellValueData( pDataCell))
5115             {
5116                 fDataVal = GetCellValue( aDataAdr, pDataCell );
5117                 bValueData = true;
5118             }
5119             else
5120                 GetCellString( aDataStr, pDataCell );
5121         }
5122         break;
5123         default:
5124             SetError( errIllegalParameter);
5125     }
5126 
5127 
5128     if (nGlobalError)
5129     {
5130         PushError( nGlobalError);
5131         return;
5132     }
5133 
5134     // Get the lookup value.
5135 
5136     ScQueryParam aParam;
5137     ScQueryEntry& rEntry = aParam.GetEntry(0);
5138     if ( !FillEntry(rEntry) )
5139         return;
5140 
5141     if ( eDataArrayType == svDouble || eDataArrayType == svString ||
5142             eDataArrayType == svSingleRef )
5143     {
5144         // Delta position for a single value is always 0.
5145 
5146         // Found if data <= query, but not if query is string and found data is
5147         // numeric or vice versa. This is how Excel does it but doesn't
5148         // document it.
5149 
5150         bool bFound = false;
5151         if ( bValueData )
5152         {
5153             if ( rEntry.bQueryByString )
5154                 bFound = false;
5155             else
5156                 bFound = (fDataVal <= rEntry.nVal);
5157         }
5158         else
5159         {
5160             if ( !rEntry.bQueryByString )
5161                 bFound = false;
5162             else
5163                 bFound = (ScGlobal::GetCollator()->compareString( aDataStr, *rEntry.pStr) <= 0);
5164         }
5165 
5166         if (!bFound)
5167         {
5168             PushNA();
5169             return;
5170         }
5171 
5172         if (pResMat)
5173         {
5174             if (pResMat->IsValue( 0 ))
5175                 PushDouble(pResMat->GetDouble( 0 ));
5176             else
5177                 PushString(pResMat->GetString( 0 ));
5178         }
5179         else if (nParamCount == 3)
5180         {
5181             switch (eResArrayType)
5182             {
5183                 case svDouble:
5184                     PushDouble( fResVal );
5185                     break;
5186                 case svString:
5187                     PushString( aResStr );
5188                     break;
5189                 case svDoubleRef:
5190                     aResAdr.Set( nResCol1, nResRow1, nResTab);
5191                     // fallthru
5192                 case svSingleRef:
5193                     PushCellResultToken( true, aResAdr, NULL, NULL);
5194                     break;
5195                 default:
5196                     DBG_ERRORFILE( "ScInterpreter::ScLookup: unhandled eResArrayType, single value data");
5197             }
5198         }
5199         else
5200         {
5201             switch (eDataArrayType)
5202             {
5203                 case svDouble:
5204                     PushDouble( fDataVal );
5205                     break;
5206                 case svString:
5207                     PushString( aDataStr );
5208                     break;
5209                 case svSingleRef:
5210                     PushCellResultToken( true, aDataAdr, NULL, NULL);
5211                     break;
5212                 default:
5213                     DBG_ERRORFILE( "ScInterpreter::ScLookup: unhandled eDataArrayType, single value data");
5214             }
5215         }
5216         return;
5217     }
5218 
5219     // Now, perform the search to compute the delta position (nDelta).
5220 
5221     if (pDataMat)
5222     {
5223         // Data array is given as a matrix.
5224         rEntry.bDoQuery = true;
5225         rEntry.eOp = SC_LESS_EQUAL;
5226         bool bFound = false;
5227 
5228         SCSIZE nC, nR;
5229         pDataMat->GetDimensions(nC, nR);
5230 
5231         // In case of non-vector matrix, only search the first row or column.
5232         ScMatrixRef pDataMat2;
5233         if (bVertical)
5234         {
5235             ScMatrixRef pTempMat(new ScMatrix(1, nR));
5236             for (SCSIZE i = 0; i < nR; ++i)
5237                 if (pDataMat->IsValue(0, i))
5238                     pTempMat->PutDouble(pDataMat->GetDouble(0, i), 0, i);
5239                 else
5240                     pTempMat->PutString(pDataMat->GetString(0, i), 0, i);
5241             pDataMat2 = pTempMat;
5242         }
5243         else
5244         {
5245             ScMatrixRef pTempMat(new ScMatrix(nC, 1));
5246             for (SCSIZE i = 0; i < nC; ++i)
5247                 if (pDataMat->IsValue(i, 0))
5248                     pTempMat->PutDouble(pDataMat->GetDouble(i, 0), i, 0);
5249                 else
5250                     pTempMat->PutString(pDataMat->GetString(i, 0), i, 0);
5251             pDataMat2 = pTempMat;
5252         }
5253 
5254         // binary search for non-equality mode (the source data is
5255         // assumed to be sorted in ascending order).
5256 
5257         SCCOLROW nDelta = -1;
5258 
5259         SCSIZE nFirst = 0, nLast = nLenMajor-1; //, nHitIndex = 0;
5260         for (SCSIZE nLen = nLast-nFirst; nLen > 0; nLen = nLast-nFirst)
5261         {
5262             SCSIZE nMid = nFirst + nLen/2;
5263             sal_Int32 nCmp = lcl_CompareMatrix2Query( nMid, *pDataMat2, rEntry);
5264             if (nCmp == 0)
5265             {
5266                 // exact match.  find the last item with the same value.
5267                 lcl_GetLastMatch( nMid, *pDataMat2, nLenMajor, false);
5268                 nDelta = nMid;
5269                 bFound = true;
5270                 break;
5271             }
5272 
5273             if (nLen == 1) // first and last items are next to each other.
5274             {
5275                 nDelta = nCmp < 0 ? nLast - 1 : nFirst - 1;
5276                 // If already the 1st item is greater there's nothing found.
5277                 bFound = (nDelta >= 0);
5278                 break;
5279             }
5280 
5281             if (nCmp < 0)
5282                 nFirst = nMid;
5283             else
5284                 nLast = nMid;
5285         }
5286 
5287         if (nDelta == static_cast<SCCOLROW>(nLenMajor-2)) // last item
5288         {
5289             sal_Int32 nCmp = lcl_CompareMatrix2Query(nDelta+1, *pDataMat2, rEntry);
5290             if (nCmp <= 0)
5291             {
5292                 // either the last item is an exact match or the real
5293                 // hit is beyond the last item.
5294                 nDelta += 1;
5295                 bFound = true;
5296             }
5297         }
5298         else if (nDelta > 0) // valid hit must be 2nd item or higher
5299         {
5300             // non-exact match
5301             bFound = true;
5302         }
5303 
5304         // With 0-9 < A-Z, if query is numeric and data found is string, or
5305         // vice versa, the (yet another undocumented) Excel behavior is to
5306         // return #N/A instead.
5307 
5308         if (bFound)
5309         {
5310             SCCOLROW i = nDelta;
5311             SCSIZE n = pDataMat->GetElementCount();
5312             if (static_cast<SCSIZE>(i) >= n)
5313                 i = static_cast<SCCOLROW>(n);
5314             if (bool(rEntry.bQueryByString) == bool(pDataMat->IsValue(i)))
5315                 bFound = false;
5316         }
5317 
5318         if (!bFound)
5319         {
5320             PushNA();
5321             return;
5322         }
5323 
5324         // Now that we've found the delta, push the result back to the cell.
5325 
5326         if (pResMat)
5327         {
5328             // result array is matrix.
5329             if (static_cast<SCSIZE>(nDelta) >= pResMat->GetElementCount())
5330             {
5331                 PushNA();
5332                 return;
5333             }
5334             if (pResMat->IsValue(nDelta))
5335                 PushDouble(pResMat->GetDouble(nDelta));
5336             else
5337                 PushString(pResMat->GetString(nDelta));
5338         }
5339         else if (nParamCount == 3)
5340         {
5341             // result array is cell range.
5342             ScAddress aAdr;
5343             aAdr.SetTab(nResTab);
5344             bool bResVertical = (nResRow2 - nResRow1) > 0;
5345             if (bResVertical)
5346             {
5347                 SCROW nTempRow = static_cast<SCROW>(nResRow1 + nDelta);
5348                 if (nTempRow > MAXROW)
5349                 {
5350                     PushDouble(0);
5351                     return;
5352                 }
5353                 aAdr.SetCol(nResCol1);
5354                 aAdr.SetRow(nTempRow);
5355             }
5356             else
5357             {
5358                 SCCOL nTempCol = static_cast<SCCOL>(nResCol1 + nDelta);
5359                 if (nTempCol > MAXCOL)
5360                 {
5361                     PushDouble(0);
5362                     return;
5363                 }
5364                 aAdr.SetCol(nTempCol);
5365                 aAdr.SetRow(nResRow1);
5366             }
5367             PushCellResultToken(true, aAdr, NULL, NULL);
5368         }
5369         else
5370         {
5371             // no result array.  Use the data array to get the final value from.
5372             if (bVertical)
5373             {
5374                 if (pDataMat->IsValue(nC-1, nDelta))
5375                     PushDouble(pDataMat->GetDouble(nC-1, nDelta));
5376                 else
5377                     PushString(pDataMat->GetString(nC-1, nDelta));
5378             }
5379             else
5380             {
5381                 if (pDataMat->IsValue(nDelta, nR-1))
5382                     PushDouble(pDataMat->GetDouble(nDelta, nR-1));
5383                 else
5384                     PushString(pDataMat->GetString(nDelta, nR-1));
5385             }
5386         }
5387 
5388         return;
5389     }
5390 
5391     // Perform cell range search.
5392 
5393     aParam.nCol1            = nCol1;
5394     aParam.nRow1            = nRow1;
5395     aParam.nCol2            = bVertical ? nCol1 : nCol2;
5396     aParam.nRow2            = bVertical ? nRow2 : nRow1;
5397     aParam.bByRow           = bVertical;
5398     aParam.bMixedComparison = true;
5399 
5400     rEntry.bDoQuery = sal_True;
5401     rEntry.eOp = SC_LESS_EQUAL;
5402     rEntry.nField = nCol1;
5403     if ( rEntry.bQueryByString )
5404         aParam.bRegExp = MayBeRegExp( *rEntry.pStr, pDok );
5405 
5406     ScQueryCellIterator aCellIter(pDok, nTab1, aParam, sal_False);
5407     SCCOL nC;
5408     SCROW nR;
5409     // Advance Entry.nField in iterator upon switching columns if
5410     // lookup in row.
5411     aCellIter.SetAdvanceQueryParamEntryField(!bVertical);
5412     if ( !aCellIter.FindEqualOrSortedLastInRange(nC, nR) )
5413     {
5414         PushNA();
5415         return;
5416     }
5417 
5418     SCCOLROW nDelta = bVertical ? static_cast<SCSIZE>(nR-nRow1) : static_cast<SCSIZE>(nC-nCol1);
5419 
5420     if (pResMat)
5421     {
5422         // Use the matrix result array.
5423         if (pResMat->IsValue(nDelta))
5424             PushDouble(pResMat->GetDouble(nDelta));
5425         else
5426             PushString(pResMat->GetString(nDelta));
5427     }
5428     else if (nParamCount == 3)
5429     {
5430         switch (eResArrayType)
5431         {
5432             case svDoubleRef:
5433             {
5434                 // Use the result array vector.  Note that the result array is assumed
5435                 // to be a vector (i.e. 1-dimensinoal array).
5436 
5437                 ScAddress aAdr;
5438                 aAdr.SetTab(nResTab);
5439                 bool bResVertical = (nResRow2 - nResRow1) > 0;
5440                 if (bResVertical)
5441                 {
5442                     SCROW nTempRow = static_cast<SCROW>(nResRow1 + nDelta);
5443                     if (nTempRow > MAXROW)
5444                     {
5445                         PushDouble(0);
5446                         return;
5447                     }
5448                     aAdr.SetCol(nResCol1);
5449                     aAdr.SetRow(nTempRow);
5450                 }
5451                 else
5452                 {
5453                     SCCOL nTempCol = static_cast<SCCOL>(nResCol1 + nDelta);
5454                     if (nTempCol > MAXCOL)
5455                     {
5456                         PushDouble(0);
5457                         return;
5458                     }
5459                     aAdr.SetCol(nTempCol);
5460                     aAdr.SetRow(nResRow1);
5461                 }
5462                 PushCellResultToken( true, aAdr, NULL, NULL);
5463             }
5464             break;
5465             case svDouble:
5466             case svString:
5467             case svSingleRef:
5468             {
5469                 if (nDelta != 0)
5470                     PushNA();
5471                 else
5472                 {
5473                     switch (eResArrayType)
5474                     {
5475                         case svDouble:
5476                             PushDouble( fResVal );
5477                             break;
5478                         case svString:
5479                             PushString( aResStr );
5480                             break;
5481                         case svSingleRef:
5482                             PushCellResultToken( true, aResAdr, NULL, NULL);
5483                             break;
5484                         default:
5485                             ;   // nothing
5486                     }
5487                 }
5488             }
5489             break;
5490             default:
5491                 DBG_ERRORFILE( "ScInterpreter::ScLookup: unhandled eResArrayType, range search");
5492         }
5493     }
5494     else
5495     {
5496         // Regardless of whether or not the result array exists, the last
5497         // array is always used as the "result" array.
5498 
5499         ScAddress aAdr;
5500         aAdr.SetTab(nTab1);
5501         if (bVertical)
5502         {
5503             SCROW nTempRow = static_cast<SCROW>(nRow1 + nDelta);
5504             if (nTempRow > MAXROW)
5505             {
5506                 PushDouble(0);
5507                 return;
5508             }
5509             aAdr.SetCol(nCol2);
5510             aAdr.SetRow(nTempRow);
5511         }
5512         else
5513         {
5514             SCCOL nTempCol = static_cast<SCCOL>(nCol1 + nDelta);
5515             if (nTempCol > MAXCOL)
5516             {
5517                 PushDouble(0);
5518                 return;
5519             }
5520             aAdr.SetCol(nTempCol);
5521             aAdr.SetRow(nRow2);
5522         }
5523         PushCellResultToken(true, aAdr, NULL, NULL);
5524     }
5525 }
5526 
5527 
5528 void ScInterpreter::ScHLookup()
5529 {
5530     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScHLookup" );
5531     CalculateLookup(sal_True);
5532 }
5533 void ScInterpreter::CalculateLookup(sal_Bool HLookup)
5534 {
5535     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::CalculateLookup" );
5536     sal_uInt8 nParamCount = GetByte();
5537     if ( MustHaveParamCount( nParamCount, 3, 4 ) )
5538     {
5539         sal_Bool bSorted;
5540         if (nParamCount == 4)
5541             bSorted = GetBool();
5542         else
5543             bSorted = sal_True;
5544         double fIndex = ::rtl::math::approxFloor( GetDouble() ) - 1.0;
5545         ScMatrixRef pMat = NULL;
5546         SCSIZE nC = 0, nR = 0;
5547         SCCOL nCol1 = 0;
5548         SCROW nRow1 = 0;
5549         SCTAB nTab1 = 0;
5550         SCCOL nCol2 = 0;
5551         SCROW nRow2 = 0;
5552         SCTAB nTab2;
5553         if (GetStackType() == svDoubleRef)
5554         {
5555             PopDoubleRef(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
5556             if (nTab1 != nTab2)
5557             {
5558                 PushIllegalParameter();
5559                 return;
5560             }
5561         }
5562         else if (GetStackType() == svMatrix)
5563         {
5564             pMat = PopMatrix();
5565             if (pMat)
5566                 pMat->GetDimensions(nC, nR);
5567             else
5568             {
5569                 PushIllegalParameter();
5570                 return;
5571             }
5572         }
5573         else
5574         {
5575             PushIllegalParameter();
5576             return;
5577         }
5578         if ( fIndex < 0.0 || (HLookup ? (pMat ? (fIndex >= nR) : (fIndex+nRow1 > nRow2)) : (pMat ? (fIndex >= nC) : (fIndex+nCol1 > nCol2)) ) )
5579         {
5580             PushIllegalArgument();
5581             return;
5582         }
5583         SCROW nZIndex = static_cast<SCROW>(fIndex);
5584         SCCOL nSpIndex = static_cast<SCCOL>(fIndex);
5585 
5586         if (!pMat)
5587         {
5588             nZIndex += nRow1;                       // Wertzeile
5589             nSpIndex = sal::static_int_cast<SCCOL>( nSpIndex + nCol1 );     // value column
5590         }
5591 
5592         if (nGlobalError == 0)
5593         {
5594             ScQueryParam rParam;
5595             rParam.nCol1       = nCol1;
5596             rParam.nRow1       = nRow1;
5597             if ( HLookup )
5598             {
5599                 rParam.nCol2       = nCol2;
5600                 rParam.nRow2       = nRow1;     // nur in der ersten Zeile suchen
5601                 rParam.bByRow      = sal_False;
5602             } // if ( HLookup )
5603             else
5604             {
5605                 rParam.nCol2       = nCol1;     // nur in der ersten Spalte suchen
5606                 rParam.nRow2       = nRow2;
5607                 rParam.nTab        = nTab1;
5608             }
5609             rParam.bMixedComparison = sal_True;
5610 
5611             ScQueryEntry& rEntry = rParam.GetEntry(0);
5612             rEntry.bDoQuery = sal_True;
5613             if ( bSorted )
5614                 rEntry.eOp = SC_LESS_EQUAL;
5615             if ( !FillEntry(rEntry) )
5616                 return;
5617             if ( rEntry.bQueryByString )
5618                 rParam.bRegExp = MayBeRegExp( *rEntry.pStr, pDok );
5619             if (pMat)
5620             {
5621                 SCSIZE nMatCount = HLookup ? nC : nR;
5622                 SCSIZE nDelta = SCSIZE_MAX;
5623                 if (rEntry.bQueryByString)
5624                 {
5625         //!!!!!!!
5626         //! TODO: enable regex on matrix strings
5627         //!!!!!!!
5628                     String aParamStr = *rEntry.pStr;
5629                     if ( bSorted )
5630                     {
5631                         static CollatorWrapper* pCollator = ScGlobal::GetCollator();
5632                         for (SCSIZE i = 0; i < nMatCount; i++)
5633                         {
5634                             if (HLookup ? pMat->IsString(i, 0) : pMat->IsString(0, i))
5635                             {
5636                                 sal_Int32 nRes =
5637                                     pCollator->compareString( HLookup ? pMat->GetString(i,0) : pMat->GetString(0,i), aParamStr);
5638                                 if (nRes <= 0)
5639                                     nDelta = i;
5640                                 else if (i>0)   // #i2168# ignore first mismatch
5641                                     i = nMatCount+1;
5642                             }
5643                             else
5644                                 nDelta = i;
5645                         }
5646                     }
5647                     else
5648                     {
5649                         for (SCSIZE i = 0; i < nMatCount; i++)
5650                         {
5651                             if (HLookup ? pMat->IsString(i, 0) : pMat->IsString(0, i))
5652                             {
5653                                 if ( ScGlobal::GetpTransliteration()->isEqual(
5654                                     HLookup ? pMat->GetString(i,0) : pMat->GetString(0,i), aParamStr ) )
5655                                 {
5656                                     nDelta = i;
5657                                     i = nMatCount + 1;
5658                                 }
5659                             }
5660                         }
5661                     }
5662                 }
5663                 else
5664                 {
5665                     if ( bSorted )
5666                     {
5667                         // #i2168# ignore strings
5668                         for (SCSIZE i = 0; i < nMatCount; i++)
5669                         {
5670                             if (!(HLookup ? pMat->IsString(i, 0) : pMat->IsString(0, i)))
5671                             {
5672                                 if ((HLookup ? pMat->GetDouble(i,0) : pMat->GetDouble(0,i)) <= rEntry.nVal)
5673                                     nDelta = i;
5674                                 else
5675                                     i = nMatCount+1;
5676                             }
5677                         }
5678                     }
5679                     else
5680                     {
5681                         for (SCSIZE i = 0; i < nMatCount; i++)
5682                         {
5683                             if (!(HLookup ? pMat->IsString(i, 0) : pMat->IsString(0, i)))
5684                             {
5685                                 if ((HLookup ? pMat->GetDouble(i,0) : pMat->GetDouble(0,i)) == rEntry.nVal)
5686                                 {
5687                                     nDelta = i;
5688                                     i = nMatCount + 1;
5689                                 }
5690                             }
5691                         }
5692                     }
5693                 }
5694                 if ( nDelta != SCSIZE_MAX )
5695                 {
5696                     SCSIZE nX = static_cast<SCSIZE>(nSpIndex);
5697                     SCSIZE nY = nDelta;
5698                     if ( HLookup )
5699                     {
5700                         nX = nDelta;
5701                         nY = static_cast<SCSIZE>(nZIndex);
5702                     }
5703                     if ( pMat->IsString( nX, nY) )
5704                         PushString(pMat->GetString( nX,nY));
5705                     else
5706                         PushDouble(pMat->GetDouble( nX,nY));
5707                 }
5708                 else
5709                     PushNA();
5710             }
5711             else
5712             {
5713                 rEntry.nField = nCol1;
5714                 sal_Bool bFound = sal_False;
5715                 SCCOL nCol = 0;
5716                 SCROW nRow = 0;
5717                 if ( bSorted )
5718                     rEntry.eOp = SC_LESS_EQUAL;
5719                 if ( HLookup )
5720                 {
5721                     ScQueryCellIterator aCellIter(pDok, nTab1, rParam, sal_False);
5722                     // advance Entry.nField in Iterator upon switching columns
5723                     aCellIter.SetAdvanceQueryParamEntryField( sal_True );
5724                     if ( bSorted )
5725                     {
5726                         SCROW nRow1_temp;
5727                         bFound = aCellIter.FindEqualOrSortedLastInRange( nCol, nRow1_temp );
5728                     }
5729                     else if ( aCellIter.GetFirst() )
5730                     {
5731                         bFound = sal_True;
5732                         nCol = aCellIter.GetCol();
5733                     }
5734                     nRow = nZIndex;
5735                 } // if ( HLookup )
5736                 else
5737                 {
5738                     ScAddress aResultPos( nCol1, nRow1, nTab1);
5739                     bFound = LookupQueryWithCache( aResultPos, rParam);
5740                     nRow = aResultPos.Row();
5741                     nCol = nSpIndex;
5742                 }
5743                 if ( bFound )
5744                 {
5745                     ScAddress aAdr( nCol, nRow, nTab1 );
5746                     PushCellResultToken( true, aAdr, NULL, NULL);
5747                 }
5748                 else
5749                     PushNA();
5750             }
5751         }
5752         else
5753             PushIllegalParameter();
5754     }
5755 }
5756 
5757 bool ScInterpreter::FillEntry(ScQueryEntry& rEntry)
5758 {
5759     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::FillEntry" );
5760     switch ( GetStackType() )
5761     {
5762         case svDouble:
5763         {
5764             rEntry.bQueryByString = sal_False;
5765             rEntry.nVal = GetDouble();
5766         }
5767         break;
5768         case svString:
5769         {
5770             const String sStr = GetString();
5771             rEntry.bQueryByString = sal_True;
5772             *rEntry.pStr = sStr;
5773         }
5774         break;
5775         case svDoubleRef :
5776         case svSingleRef :
5777         {
5778             ScAddress aAdr;
5779             if ( !PopDoubleRefOrSingleRef( aAdr ) )
5780             {
5781                 PushInt(0);
5782                 return false;
5783             }
5784             ScBaseCell* pCell = GetCell( aAdr );
5785             if (HasCellValueData(pCell))
5786             {
5787                 rEntry.bQueryByString = sal_False;
5788                 rEntry.nVal = GetCellValue( aAdr, pCell );
5789             }
5790             else
5791             {
5792                 if ( GetCellType( pCell ) == CELLTYPE_NOTE )
5793                 {
5794                     rEntry.bQueryByString = sal_False;
5795                     rEntry.nVal = 0.0;
5796                 }
5797                 else
5798                 {
5799                     String sStr;
5800                     GetCellString(sStr, pCell);
5801                     rEntry.bQueryByString = sal_True;
5802                     *rEntry.pStr = sStr;
5803                 }
5804             }
5805         }
5806         break;
5807         case svMatrix :
5808         {
5809             const ScMatValType nType = GetDoubleOrStringFromMatrix(rEntry.nVal, *rEntry.pStr);
5810             rEntry.bQueryByString = ScMatrix::IsNonValueType( nType);
5811         }
5812         break;
5813         default:
5814         {
5815             PushIllegalParameter();
5816             return false;
5817         }
5818     } // switch ( GetStackType() )
5819     return true;
5820 }
5821 void ScInterpreter::ScVLookup()
5822 {
5823     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScVLookup" );
5824     CalculateLookup(sal_False);
5825 }
5826 
5827 void ScInterpreter::ScSubTotal()
5828 {
5829     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSubTotal" );
5830     sal_uInt8 nParamCount = GetByte();
5831     if ( MustHaveParamCountMin( nParamCount, 2 ) )
5832     {
5833         // We must fish the 1st parameter deep from the stack! And push it on top.
5834         const FormulaToken* p = pStack[ sp - nParamCount ];
5835         PushTempToken( *p );
5836         int nFunc = (int) ::rtl::math::approxFloor( GetDouble() );
5837         if( nFunc < 1 || nFunc > 11 )
5838             PushIllegalArgument();  // simulate return on stack, not SetError(...)
5839         else
5840         {
5841             cPar = nParamCount - 1;
5842             glSubTotal = sal_True;
5843             switch( nFunc )
5844             {
5845                 case SUBTOTAL_FUNC_AVE  : ScAverage(); break;
5846                 case SUBTOTAL_FUNC_CNT  : ScCount();   break;
5847                 case SUBTOTAL_FUNC_CNT2 : ScCount2();  break;
5848                 case SUBTOTAL_FUNC_MAX  : ScMax();     break;
5849                 case SUBTOTAL_FUNC_MIN  : ScMin();     break;
5850                 case SUBTOTAL_FUNC_PROD : ScProduct(); break;
5851                 case SUBTOTAL_FUNC_STD  : ScStDev();   break;
5852                 case SUBTOTAL_FUNC_STDP : ScStDevP();  break;
5853                 case SUBTOTAL_FUNC_SUM  : ScSum();     break;
5854                 case SUBTOTAL_FUNC_VAR  : ScVar();     break;
5855                 case SUBTOTAL_FUNC_VARP : ScVarP();    break;
5856                 default : PushIllegalArgument();       break;
5857             }
5858             glSubTotal = sal_False;
5859         }
5860         // Get rid of the 1st (fished) parameter.
5861         double nVal = GetDouble();
5862         Pop();
5863         PushDouble( nVal );
5864     }
5865 }
5866 
5867 ScDBQueryParamBase* ScInterpreter::GetDBParams( sal_Bool& rMissingField )
5868 {
5869     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetDBParams" );
5870     sal_Bool bAllowMissingField = sal_False;
5871     if ( rMissingField )
5872     {
5873         bAllowMissingField = sal_True;
5874         rMissingField = sal_False;
5875     }
5876     if ( GetByte() == 3 )
5877     {
5878         // First, get the query criteria range.
5879         ::std::auto_ptr<ScDBRangeBase> pQueryRef( PopDoubleRef() );
5880         if (!pQueryRef.get())
5881             return NULL;
5882 
5883         sal_Bool    bByVal = sal_True;
5884         double  nVal = 0.0;
5885         String  aStr;
5886         ScRange aMissingRange;
5887         sal_Bool bRangeFake = sal_False;
5888         switch (GetStackType())
5889         {
5890             case svDouble :
5891                 nVal = ::rtl::math::approxFloor( GetDouble() );
5892                 if ( bAllowMissingField && nVal == 0.0 )
5893                     rMissingField = sal_True;   // fake missing parameter
5894                 break;
5895             case svString :
5896                 bByVal = sal_False;
5897                 aStr = GetString();
5898                 break;
5899             case svSingleRef :
5900                 {
5901                     ScAddress aAdr;
5902                     PopSingleRef( aAdr );
5903                     ScBaseCell* pCell = GetCell( aAdr );
5904                     if (HasCellValueData(pCell))
5905                         nVal = GetCellValue( aAdr, pCell );
5906                     else
5907                     {
5908                         bByVal = sal_False;
5909                         GetCellString(aStr, pCell);
5910                     }
5911                 }
5912                 break;
5913             case svDoubleRef :
5914                 if ( bAllowMissingField )
5915                 {   // fake missing parameter for old SO compatibility
5916                     bRangeFake = sal_True;
5917                     PopDoubleRef( aMissingRange );
5918                 }
5919                 else
5920                 {
5921                     PopError();
5922                     SetError( errIllegalParameter );
5923                 }
5924                 break;
5925             case svMissing :
5926                 PopError();
5927                 if ( bAllowMissingField )
5928                     rMissingField = sal_True;
5929                 else
5930                     SetError( errIllegalParameter );
5931                 break;
5932             default:
5933                 PopError();
5934                 SetError( errIllegalParameter );
5935         }
5936 
5937         auto_ptr<ScDBRangeBase> pDBRef( PopDoubleRef() );
5938 
5939         if (nGlobalError || !pDBRef.get())
5940             return NULL;
5941 
5942         if ( bRangeFake )
5943         {
5944             // range parameter must match entire database range
5945             if (pDBRef->isRangeEqual(aMissingRange))
5946                 rMissingField = sal_True;
5947             else
5948                 SetError( errIllegalParameter );
5949         }
5950 
5951         if (nGlobalError)
5952             return NULL;
5953 
5954         SCCOL nField = pDBRef->getFirstFieldColumn();
5955         if (rMissingField)
5956             ; // special case
5957         else if (bByVal)
5958             nField = pDBRef->findFieldColumn(static_cast<SCCOL>(nVal));
5959         else
5960         {
5961             sal_uInt16 nErr = 0;
5962             nField = pDBRef->findFieldColumn(aStr, &nErr);
5963             SetError(nErr);
5964         }
5965 
5966         if (!ValidCol(nField))
5967             return NULL;
5968 
5969         auto_ptr<ScDBQueryParamBase> pParam( pDBRef->createQueryParam(pQueryRef.get()) );
5970 
5971         if (pParam.get())
5972         {
5973             // An allowed missing field parameter sets the result field
5974             // to any of the query fields, just to be able to return
5975             // some cell from the iterator.
5976             if ( rMissingField )
5977                 nField = static_cast<SCCOL>(pParam->GetEntry(0).nField);
5978             pParam->mnField = nField;
5979 
5980             SCSIZE nCount = pParam->GetEntryCount();
5981             for ( SCSIZE i=0; i < nCount; i++ )
5982             {
5983                 ScQueryEntry& rEntry = pParam->GetEntry(i);
5984                 if ( rEntry.bDoQuery )
5985                 {
5986                     sal_uInt32 nIndex = 0;
5987                     rEntry.bQueryByString = !pFormatter->IsNumberFormat(
5988                         *rEntry.pStr, nIndex, rEntry.nVal );
5989                     if ( rEntry.bQueryByString && !pParam->bRegExp )
5990                         pParam->bRegExp = MayBeRegExp( *rEntry.pStr, pDok );
5991                 }
5992                 else
5993                     break;  // for
5994             }
5995             return pParam.release();
5996         }
5997     }
5998     return false;
5999 }
6000 
6001 
6002 void ScInterpreter::DBIterator( ScIterFunc eFunc )
6003 {
6004     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::DBIterator" );
6005     double nErg = 0.0;
6006     double fMem = 0.0;
6007     sal_Bool bNull = sal_True;
6008     sal_uLong nCount = 0;
6009     sal_Bool bMissingField = sal_False;
6010     auto_ptr<ScDBQueryParamBase> pQueryParam( GetDBParams(bMissingField) );
6011     if (pQueryParam.get())
6012     {
6013         ScDBQueryDataIterator aValIter(pDok, pQueryParam.release());
6014         ScDBQueryDataIterator::Value aValue;
6015         if ( aValIter.GetFirst(aValue) && !aValue.mnError )
6016         {
6017             switch( eFunc )
6018             {
6019                 case ifPRODUCT: nErg = 1; break;
6020                 case ifMAX:     nErg = -MAXDOUBLE; break;
6021                 case ifMIN:     nErg = MAXDOUBLE; break;
6022                 default: ; // nothing
6023             }
6024             do
6025             {
6026                 nCount++;
6027                 switch( eFunc )
6028                 {
6029                     case ifAVERAGE:
6030                     case ifSUM:
6031                         if ( bNull && aValue.mfValue != 0.0 )
6032                         {
6033                             bNull = sal_False;
6034                             fMem = aValue.mfValue;
6035                         }
6036                         else
6037                             nErg += aValue.mfValue;
6038                         break;
6039                     case ifSUMSQ:   nErg += aValue.mfValue * aValue.mfValue; break;
6040                     case ifPRODUCT: nErg *= aValue.mfValue; break;
6041                     case ifMAX:     if( aValue.mfValue > nErg ) nErg = aValue.mfValue; break;
6042                     case ifMIN:     if( aValue.mfValue < nErg ) nErg = aValue.mfValue; break;
6043                     default: ; // nothing
6044                 }
6045             }
6046             while ( aValIter.GetNext(aValue) && !aValue.mnError );
6047         }
6048         SetError(aValue.mnError);
6049     }
6050     else
6051         SetError( errIllegalParameter);
6052     switch( eFunc )
6053     {
6054         case ifCOUNT:   nErg = nCount; break;
6055         case ifSUM:     nErg = ::rtl::math::approxAdd( nErg, fMem ); break;
6056         case ifAVERAGE: nErg = ::rtl::math::approxAdd( nErg, fMem ) / nCount; break;
6057         default: ; // nothing
6058     }
6059     PushDouble( nErg );
6060 }
6061 
6062 
6063 void ScInterpreter::ScDBSum()
6064 {
6065     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBSum" );
6066     DBIterator( ifSUM );
6067 }
6068 
6069 
6070 void ScInterpreter::ScDBCount()
6071 {
6072     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBCount" );
6073     sal_Bool bMissingField = sal_True;
6074     auto_ptr<ScDBQueryParamBase> pQueryParam( GetDBParams(bMissingField) );
6075     if (pQueryParam.get())
6076     {
6077         sal_uLong nCount = 0;
6078         if ( bMissingField && pQueryParam->GetType() == ScDBQueryParamBase::INTERNAL )
6079         {   // count all matching records
6080             // TODO: currently the QueryIterators only return cell pointers of
6081             // existing cells, so if a query matches an empty cell there's
6082             // nothing returned, and therefor not counted!
6083             // Since this has ever been the case and this code here only came
6084             // into existance to fix #i6899 and it never worked before we'll
6085             // have to live with it until we reimplement the iterators to also
6086             // return empty cells, which would mean to adapt all callers of
6087             // iterators.
6088             ScDBQueryParamInternal* p = static_cast<ScDBQueryParamInternal*>(pQueryParam.get());
6089             SCTAB nTab = p->nTab;
6090             // ScQueryCellIterator doesn't make use of ScDBQueryParamBase::mnField,
6091             // so the source range has to be restricted, like before the introduction
6092             // of ScDBQueryParamBase.
6093             p->nCol1 = p->nCol2 = p->mnField;
6094             ScQueryCellIterator aCellIter( pDok, nTab, *p);
6095             if ( aCellIter.GetFirst() )
6096             {
6097                 do
6098                 {
6099                     nCount++;
6100                 } while ( aCellIter.GetNext() );
6101             }
6102         }
6103         else
6104         {   // count only matching records with a value in the "result" field
6105             ScDBQueryDataIterator aValIter( pDok, pQueryParam.release());
6106             ScDBQueryDataIterator::Value aValue;
6107             if ( aValIter.GetFirst(aValue) && !aValue.mnError )
6108             {
6109                 do
6110                 {
6111                     nCount++;
6112                 }
6113                 while ( aValIter.GetNext(aValue) && !aValue.mnError );
6114             }
6115             SetError(aValue.mnError);
6116         }
6117         PushDouble( nCount );
6118     }
6119     else
6120         PushIllegalParameter();
6121 }
6122 
6123 
6124 void ScInterpreter::ScDBCount2()
6125 {
6126     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBCount2" );
6127     sal_Bool bMissingField = sal_True;
6128     auto_ptr<ScDBQueryParamBase> pQueryParam( GetDBParams(bMissingField) );
6129     if (pQueryParam.get())
6130     {
6131         sal_uLong nCount = 0;
6132         pQueryParam->mbSkipString = false;
6133         ScDBQueryDataIterator aValIter( pDok, pQueryParam.release());
6134         ScDBQueryDataIterator::Value aValue;
6135         if ( aValIter.GetFirst(aValue) && !aValue.mnError )
6136         {
6137             do
6138             {
6139                 nCount++;
6140             }
6141             while ( aValIter.GetNext(aValue) && !aValue.mnError );
6142         }
6143         SetError(aValue.mnError);
6144         PushDouble( nCount );
6145     }
6146     else
6147         PushIllegalParameter();
6148 }
6149 
6150 
6151 void ScInterpreter::ScDBAverage()
6152 {
6153     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBAverage" );
6154     DBIterator( ifAVERAGE );
6155 }
6156 
6157 
6158 void ScInterpreter::ScDBMax()
6159 {
6160     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBMax" );
6161     DBIterator( ifMAX );
6162 }
6163 
6164 
6165 void ScInterpreter::ScDBMin()
6166 {
6167     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBMin" );
6168     DBIterator( ifMIN );
6169 }
6170 
6171 
6172 void ScInterpreter::ScDBProduct()
6173 {
6174     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBProduct" );
6175     DBIterator( ifPRODUCT );
6176 }
6177 
6178 
6179 void ScInterpreter::GetDBStVarParams( double& rVal, double& rValCount )
6180 {
6181     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetDBStVarParams" );
6182     std::vector<double> values;
6183     double vSum    = 0.0;
6184     double vMean    = 0.0;
6185 
6186     rValCount = 0.0;
6187     double fSum    = 0.0;
6188     sal_Bool bMissingField = sal_False;
6189     auto_ptr<ScDBQueryParamBase> pQueryParam( GetDBParams(bMissingField) );
6190     if (pQueryParam.get())
6191     {
6192         ScDBQueryDataIterator aValIter(pDok, pQueryParam.release());
6193         ScDBQueryDataIterator::Value aValue;
6194         if (aValIter.GetFirst(aValue) && !aValue.mnError)
6195         {
6196             do
6197             {
6198                 rValCount++;
6199                 values.push_back(aValue.mfValue);
6200                 fSum += aValue.mfValue;
6201             }
6202             while ((aValue.mnError == 0) && aValIter.GetNext(aValue));
6203         }
6204         SetError(aValue.mnError);
6205     }
6206     else
6207         SetError( errIllegalParameter);
6208 
6209     vMean = fSum / values.size();
6210 
6211     for (size_t i = 0; i < values.size(); i++)
6212         vSum += (values[i] - vMean) * (values[i] - vMean);
6213 
6214     rVal = vSum;
6215 }
6216 
6217 
6218 void ScInterpreter::ScDBStdDev()
6219 {
6220     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBStdDev" );
6221     double fVal, fCount;
6222     GetDBStVarParams( fVal, fCount );
6223     PushDouble( sqrt(fVal/(fCount-1)));
6224 }
6225 
6226 
6227 void ScInterpreter::ScDBStdDevP()
6228 {
6229     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBStdDevP" );
6230     double fVal, fCount;
6231     GetDBStVarParams( fVal, fCount );
6232     PushDouble( sqrt(fVal/fCount));
6233 }
6234 
6235 
6236 void ScInterpreter::ScDBVar()
6237 {
6238     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBVar" );
6239     double fVal, fCount;
6240     GetDBStVarParams( fVal, fCount );
6241     PushDouble(fVal/(fCount-1));
6242 }
6243 
6244 
6245 void ScInterpreter::ScDBVarP()
6246 {
6247     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDBVarP" );
6248     double fVal, fCount;
6249     GetDBStVarParams( fVal, fCount );
6250     PushDouble(fVal/fCount);
6251 }
6252 
6253 
6254 FormulaSubroutineToken* lcl_CreateExternalRefSubroutine( const ScAddress& rPos, ScDocument* pDoc,
6255         const ScAddress::ExternalInfo& rExtInfo, const ScRefAddress& rRefAd1,
6256         const ScRefAddress* pRefAd2 )
6257 {
6258     ScExternalRefManager* pRefMgr = pDoc->GetExternalRefManager();
6259     size_t nSheets = 1;
6260     const String* pRealTab = pRefMgr->getRealTableName( rExtInfo.mnFileId, rExtInfo.maTabName);
6261     ScTokenArray* pTokenArray = new ScTokenArray;
6262     if (pRefAd2)
6263     {
6264         ScComplexRefData aRef;
6265         aRef.InitRangeRel( ScRange( rRefAd1.GetAddress(), pRefAd2->GetAddress()), rPos);
6266         aRef.Ref1.SetColRel( rRefAd1.IsRelCol());
6267         aRef.Ref1.SetRowRel( rRefAd1.IsRelRow());
6268         aRef.Ref1.SetTabRel( rRefAd1.IsRelTab());
6269         aRef.Ref1.SetFlag3D( true);
6270         aRef.Ref2.SetColRel( pRefAd2->IsRelCol());
6271         aRef.Ref2.SetRowRel( pRefAd2->IsRelRow());
6272         aRef.Ref2.SetTabRel( pRefAd2->IsRelTab());
6273         nSheets = aRef.Ref2.nTab - aRef.Ref1.nTab + 1;
6274         aRef.Ref2.SetFlag3D( nSheets > 1 );
6275         pTokenArray->AddExternalDoubleReference( rExtInfo.mnFileId,
6276                 (pRealTab ? *pRealTab : rExtInfo.maTabName), aRef);
6277     }
6278     else
6279     {
6280         ScSingleRefData aRef;
6281         aRef.InitAddressRel( rRefAd1.GetAddress(), rPos);
6282         aRef.SetColRel( rRefAd1.IsRelCol());
6283         aRef.SetRowRel( rRefAd1.IsRelRow());
6284         aRef.SetTabRel( rRefAd1.IsRelTab());
6285         aRef.SetFlag3D( true);
6286         pTokenArray->AddExternalSingleReference( rExtInfo.mnFileId,
6287                 (pRealTab ? *pRealTab : rExtInfo.maTabName), aRef);
6288     }
6289     // The indirect usage of the external table can't be detected during the
6290     // store-to-file cycle, mark it as permanently referenced so it gets stored
6291     // even if not directly referenced anywhere.
6292     pRefMgr->setCacheTableReferencedPermanently( rExtInfo.mnFileId,
6293             rExtInfo.maTabName, nSheets);
6294     ScCompiler aComp( pDoc, rPos, *pTokenArray);
6295     aComp.CompileTokenArray();
6296     return new FormulaSubroutineToken( pTokenArray);
6297 }
6298 
6299 
6300 void ScInterpreter::ScIndirect()
6301 {
6302     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIndirect" );
6303     sal_uInt8 nParamCount = GetByte();
6304     if ( MustHaveParamCount( nParamCount, 1, 2 )  )
6305     {
6306         bool bTryXlA1 = true;   // whether to try XL_A1 style as well.
6307         FormulaGrammar::AddressConvention eConv = FormulaGrammar::CONV_OOO;
6308         if (nParamCount == 2 && 0.0 == ::rtl::math::approxFloor( GetDouble()))
6309         {
6310             eConv = FormulaGrammar::CONV_XL_R1C1;
6311             bTryXlA1 = false;
6312         }
6313         const ScAddress::Details aDetails( eConv, aPos );
6314         const ScAddress::Details aDetailsXlA1( FormulaGrammar::CONV_XL_A1, aPos );
6315         SCTAB nTab = aPos.Tab();
6316         String sRefStr( GetString() );
6317         ScRefAddress aRefAd, aRefAd2;
6318         ScAddress::ExternalInfo aExtInfo;
6319         if ( ConvertDoubleRef( pDok, sRefStr, nTab, aRefAd, aRefAd2, aDetails, &aExtInfo) ||
6320                 (bTryXlA1 && ConvertDoubleRef( pDok, sRefStr, nTab, aRefAd,
6321                                                aRefAd2, aDetailsXlA1, &aExtInfo)))
6322         {
6323             if (aExtInfo.mbExternal)
6324             {
6325                 // Push a subroutine that resolves the external reference as
6326                 // the next instruction.
6327                 PushTempToken( lcl_CreateExternalRefSubroutine( aPos, pDok,
6328                             aExtInfo, aRefAd, &aRefAd2));
6329             }
6330             else
6331                 PushDoubleRef( aRefAd.Col(), aRefAd.Row(), aRefAd.Tab(),
6332                         aRefAd2.Col(), aRefAd2.Row(), aRefAd2.Tab() );
6333         }
6334         else if ( ConvertSingleRef ( pDok, sRefStr, nTab, aRefAd, aDetails, &aExtInfo) ||
6335                 (bTryXlA1 && ConvertSingleRef ( pDok, sRefStr, nTab, aRefAd,
6336                                                 aDetailsXlA1, &aExtInfo)))
6337         {
6338             if (aExtInfo.mbExternal)
6339             {
6340                 // Push a subroutine that resolves the external reference as
6341                 // the next instruction.
6342                 PushTempToken( lcl_CreateExternalRefSubroutine( aPos, pDok,
6343                             aExtInfo, aRefAd, NULL));
6344             }
6345             else
6346                 PushSingleRef( aRefAd.Col(), aRefAd.Row(), aRefAd.Tab() );
6347         }
6348         else
6349         {
6350             do
6351             {
6352                 ScRangeName* pNames = pDok->GetRangeName();
6353                 if (!pNames)
6354                     break;
6355 
6356                 sal_uInt16 nPos = 0;
6357                 if (!pNames->SearchName( sRefStr, nPos))
6358                     break;
6359 
6360                 ScRangeData* rData = (*pNames)[nPos];
6361                 if (!rData)
6362                     break;
6363 
6364                 // We need this in order to obtain a good range.
6365                 rData->ValidateTabRefs();
6366 
6367                 ScRange aRange;
6368 #if 0
6369                 // This is some really odd Excel behavior and renders named
6370                 // ranges containing relative references totally useless.
6371                 if (!rData->IsReference(aRange, ScAddress( aPos.Tab(), 0, 0)))
6372                     break;
6373 #else
6374                 // This is the usual way to treat named ranges containing
6375                 // relative references.
6376                 if (!rData->IsReference( aRange, aPos))
6377                     break;
6378 #endif
6379 
6380                 if (aRange.aStart == aRange.aEnd)
6381                     PushSingleRef( aRange.aStart.Col(), aRange.aStart.Row(),
6382                             aRange.aStart.Tab());
6383                 else
6384                     PushDoubleRef( aRange.aStart.Col(), aRange.aStart.Row(),
6385                             aRange.aStart.Tab(), aRange.aEnd.Col(),
6386                             aRange.aEnd.Row(), aRange.aEnd.Tab());
6387 
6388                 // success!
6389                 return;
6390             }
6391             while (false);
6392 
6393             PushIllegalArgument();
6394         }
6395     }
6396 }
6397 
6398 
6399 void ScInterpreter::ScAddressFunc()
6400 {
6401     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScAddressFunc" );
6402     String  sTabStr;
6403 
6404     sal_uInt8    nParamCount = GetByte();
6405     if( !MustHaveParamCount( nParamCount, 2, 5 ) )
6406         return;
6407 
6408     if( nParamCount >= 5 )
6409         sTabStr = GetString();
6410 
6411     FormulaGrammar::AddressConvention eConv = FormulaGrammar::CONV_OOO;      // default
6412     if( nParamCount >= 4 && 0.0 == ::rtl::math::approxFloor( GetDoubleWithDefault( 1.0)))
6413         eConv = FormulaGrammar::CONV_XL_R1C1;
6414 
6415     sal_uInt16  nFlags = SCA_COL_ABSOLUTE | SCA_ROW_ABSOLUTE;   // default
6416     if( nParamCount >= 3 )
6417     {
6418         sal_uInt16 n = (sal_uInt16) ::rtl::math::approxFloor( GetDoubleWithDefault( 1.0));
6419         switch ( n )
6420         {
6421             default :
6422                 PushNoValue();
6423                 return;
6424 
6425             case 5:
6426             case 1 : break; // default
6427             case 6:
6428             case 2 : nFlags = SCA_ROW_ABSOLUTE; break;
6429             case 7:
6430             case 3 : nFlags = SCA_COL_ABSOLUTE; break;
6431             case 8:
6432             case 4 : nFlags = 0; break; // both relative
6433         }
6434     }
6435     nFlags |= SCA_VALID | SCA_VALID_ROW | SCA_VALID_COL;
6436 
6437     SCCOL nCol = (SCCOL) ::rtl::math::approxFloor(GetDouble());
6438     SCROW nRow = (SCROW) ::rtl::math::approxFloor(GetDouble());
6439     if( eConv == FormulaGrammar::CONV_XL_R1C1 )
6440     {
6441         // YUCK!  The XL interface actually treats rel R1C1 refs differently
6442         // than A1
6443         if( !(nFlags & SCA_COL_ABSOLUTE) )
6444             nCol += aPos.Col() + 1;
6445         if( !(nFlags & SCA_ROW_ABSOLUTE) )
6446             nRow += aPos.Row() + 1;
6447     }
6448 
6449     --nCol;
6450     --nRow;
6451     if(!ValidCol( nCol) || !ValidRow( nRow))
6452     {
6453         PushIllegalArgument();
6454         return;
6455     }
6456 
6457     String aRefStr;
6458     const ScAddress::Details aDetails( eConv, aPos );
6459     const ScAddress aAdr( nCol, nRow, 0);
6460     aAdr.Format( aRefStr, nFlags, pDok, aDetails );
6461 
6462     if( nParamCount >= 5 && sTabStr.Len() )
6463     {
6464         String aDoc;
6465         if (eConv == FormulaGrammar::CONV_OOO)
6466         {
6467             // Isolate Tab from 'Doc'#Tab
6468             xub_StrLen nPos = ScCompiler::GetDocTabPos( sTabStr);
6469             if (nPos != STRING_NOTFOUND)
6470             {
6471                 if (sTabStr.GetChar(nPos+1) == '$')
6472                     ++nPos;     // also split 'Doc'#$Tab
6473                 aDoc = sTabStr.Copy( 0, nPos+1);
6474                 sTabStr.Erase( 0, nPos+1);
6475             }
6476         }
6477         /* TODO: yet unsupported external reference in CONV_XL_R1C1 syntax may
6478          * need some extra handling to isolate Tab from Doc. */
6479         if (sTabStr.GetChar(0) != '\'' || sTabStr.GetChar(sTabStr.Len()-1) != '\'')
6480             ScCompiler::CheckTabQuotes( sTabStr, eConv);
6481         if (aDoc.Len())
6482             sTabStr.Insert( aDoc, 0);
6483         sTabStr += static_cast<sal_Unicode>(eConv == FormulaGrammar::CONV_XL_R1C1 ? '!' : '.');
6484         sTabStr += aRefStr;
6485         PushString( sTabStr );
6486     }
6487     else
6488         PushString( aRefStr );
6489 }
6490 
6491 
6492 FormulaSubroutineToken* lcl_CreateExternalRefSubroutine( const ScAddress& rPos,
6493         ScDocument* pDoc, const FormulaTokenRef& xExtRef )
6494 {
6495     // The exact usage (which cell range) of the external table can't be
6496     // detected during the store-to-file cycle, mark it as permanently
6497     // referenced so it gets stored even if not directly referenced anywhere.
6498     ScExternalRefManager* pRefMgr = pDoc->GetExternalRefManager();
6499     pRefMgr->setCacheTableReferencedPermanently(
6500             static_cast<const ScToken*>(xExtRef.get())->GetIndex(),
6501             static_cast<const ScToken*>(xExtRef.get())->GetString(), 1);
6502     ScTokenArray* pTokenArray = new ScTokenArray;
6503     pTokenArray->AddToken( *xExtRef);
6504     ScCompiler aComp( pDoc, rPos, *pTokenArray);
6505     aComp.CompileTokenArray();
6506     return new FormulaSubroutineToken( pTokenArray);
6507 }
6508 
6509 
6510 void ScInterpreter::ScOffset()
6511 {
6512     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScOffset" );
6513     sal_uInt8 nParamCount = GetByte();
6514     if ( MustHaveParamCount( nParamCount, 3, 5 ) )
6515     {
6516         long nColNew = -1, nRowNew = -1, nColPlus, nRowPlus;
6517         if (nParamCount == 5)
6518             nColNew = (long) ::rtl::math::approxFloor(GetDouble());
6519         if (nParamCount >= 4)
6520             nRowNew = (long) ::rtl::math::approxFloor(GetDoubleWithDefault( -1.0 ));
6521         nColPlus = (long) ::rtl::math::approxFloor(GetDouble());
6522         nRowPlus = (long) ::rtl::math::approxFloor(GetDouble());
6523         SCCOL nCol1;
6524         SCROW nRow1;
6525         SCTAB nTab1;
6526         SCCOL nCol2;
6527         SCROW nRow2;
6528         SCTAB nTab2;
6529         if (nColNew == 0 || nRowNew == 0)
6530         {
6531             PushIllegalArgument();
6532             return;
6533         }
6534         FormulaTokenRef xExtRef;
6535         switch (GetStackType())
6536         {
6537             case svExternalSingleRef:
6538                 xExtRef = PopToken()->Clone();
6539                 // fallthru
6540             case svSingleRef:
6541                 {
6542                     if (xExtRef)
6543                     {
6544                         ScSingleRefData& rData = static_cast<ScToken*>(xExtRef.get())->GetSingleRef();
6545                         rData.CalcAbsIfRel( aPos);
6546                         nCol1 = rData.nCol;
6547                         nRow1 = rData.nRow;
6548                         nTab1 = rData.nTab;
6549                     }
6550                     else
6551                         PopSingleRef( nCol1, nRow1, nTab1);
6552                     if (nParamCount == 3 || (nColNew < 0 && nRowNew < 0))
6553                     {
6554                         nCol1 = (SCCOL)((long) nCol1 + nColPlus);
6555                         nRow1 = (SCROW)((long) nRow1 + nRowPlus);
6556                         if (!ValidCol(nCol1) || !ValidRow(nRow1))
6557                             PushIllegalArgument();
6558                         else if (xExtRef)
6559                         {
6560                             ScSingleRefData& rData = static_cast<ScToken*>(xExtRef.get())->GetSingleRef();
6561                             rData.nCol = nCol1;
6562                             rData.nRow = nRow1;
6563                             rData.nTab = nTab1;
6564                             rData.CalcRelFromAbs( aPos);
6565                             // Push a subroutine that resolves the external
6566                             // reference as the next instruction.
6567                             PushTempToken( lcl_CreateExternalRefSubroutine( aPos, pDok, xExtRef));
6568                         }
6569                         else
6570                             PushSingleRef(nCol1, nRow1, nTab1);
6571                     }
6572                     else
6573                     {
6574                         if (nColNew < 0)
6575                             nColNew = 1;
6576                         if (nRowNew < 0)
6577                             nRowNew = 1;
6578                         nCol1 = (SCCOL)((long)nCol1+nColPlus);  // ! nCol1 is modified
6579                         nRow1 = (SCROW)((long)nRow1+nRowPlus);
6580                         nCol2 = (SCCOL)((long)nCol1+nColNew-1);
6581                         nRow2 = (SCROW)((long)nRow1+nRowNew-1);
6582                         if (!ValidCol(nCol1) || !ValidRow(nRow1) ||
6583                                 !ValidCol(nCol2) || !ValidRow(nRow2))
6584                             PushIllegalArgument();
6585                         else if (xExtRef)
6586                         {
6587                             // Convert SingleRef to DoubleRef.
6588                             xExtRef = new ScExternalDoubleRefToken(
6589                                     *static_cast<const ScExternalSingleRefToken*>(xExtRef.get()));
6590                             ScComplexRefData& rData = static_cast<ScToken*>(xExtRef.get())->GetDoubleRef();
6591                             rData.Ref1.nCol = nCol1;
6592                             rData.Ref1.nRow = nRow1;
6593                             rData.Ref1.nTab = nTab1;
6594                             rData.Ref2.nCol = nCol2;
6595                             rData.Ref2.nRow = nRow2;
6596                             rData.Ref2.nTab = nTab1;
6597                             rData.CalcRelFromAbs( aPos);
6598                             // Push a subroutine that resolves the external
6599                             // reference as the next instruction.
6600                             PushTempToken( lcl_CreateExternalRefSubroutine( aPos, pDok, xExtRef));
6601                         }
6602                         else
6603                             PushDoubleRef(nCol1, nRow1, nTab1, nCol2, nRow2, nTab1);
6604                     }
6605                 }
6606                 break;
6607             case svExternalDoubleRef:
6608                 xExtRef = PopToken()->Clone();
6609                 // fallthru
6610             case svDoubleRef:
6611                 {
6612                     if (xExtRef)
6613                     {
6614                         ScComplexRefData& rData = static_cast<ScToken*>(xExtRef.get())->GetDoubleRef();
6615                         rData.CalcAbsIfRel( aPos);
6616                         nCol1 = rData.Ref1.nCol;
6617                         nRow1 = rData.Ref1.nRow;
6618                         nTab1 = rData.Ref1.nTab;
6619                         nCol2 = rData.Ref2.nCol;
6620                         nRow2 = rData.Ref2.nRow;
6621                         nTab2 = rData.Ref2.nTab;
6622                     }
6623                     else
6624                         PopDoubleRef(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
6625                     if (nColNew < 0)
6626                         nColNew = nCol2 - nCol1 + 1;
6627                     if (nRowNew < 0)
6628                         nRowNew = nRow2 - nRow1 + 1;
6629                     nCol1 = (SCCOL)((long)nCol1+nColPlus);
6630                     nRow1 = (SCROW)((long)nRow1+nRowPlus);
6631                     nCol2 = (SCCOL)((long)nCol1+nColNew-1);
6632                     nRow2 = (SCROW)((long)nRow1+nRowNew-1);
6633                     if (!ValidCol(nCol1) || !ValidRow(nRow1) ||
6634                             !ValidCol(nCol2) || !ValidRow(nRow2) || nTab1 != nTab2)
6635                         PushIllegalArgument();
6636                     else if (xExtRef)
6637                     {
6638                         ScComplexRefData& rData = static_cast<ScToken*>(xExtRef.get())->GetDoubleRef();
6639                         rData.Ref1.nCol = nCol1;
6640                         rData.Ref1.nRow = nRow1;
6641                         rData.Ref1.nTab = nTab1;
6642                         rData.Ref2.nCol = nCol2;
6643                         rData.Ref2.nRow = nRow2;
6644                         rData.Ref2.nTab = nTab1;
6645                         rData.CalcRelFromAbs( aPos);
6646                         // Push a subroutine that resolves the external
6647                         // reference as the next instruction.
6648                         PushTempToken( lcl_CreateExternalRefSubroutine( aPos, pDok, xExtRef));
6649                     }
6650                     else
6651                         PushDoubleRef(nCol1, nRow1, nTab1, nCol2, nRow2, nTab1);
6652                 }
6653                 break;
6654             default:
6655                 PushIllegalParameter();
6656         }
6657     }
6658 }
6659 
6660 
6661 void ScInterpreter::ScIndex()
6662 {
6663     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIndex" );
6664     sal_uInt8 nParamCount = GetByte();
6665     if ( MustHaveParamCount( nParamCount, 1, 4 ) )
6666     {
6667         long nArea;
6668         size_t nAreaCount;
6669         SCCOL nCol;
6670         SCROW nRow;
6671         if (nParamCount == 4)
6672             nArea = (long) ::rtl::math::approxFloor(GetDouble());
6673         else
6674             nArea = 1;
6675         if (nParamCount >= 3)
6676             nCol = (SCCOL) ::rtl::math::approxFloor(GetDouble());
6677         else
6678             nCol = 0;
6679         if (nParamCount >= 2)
6680             nRow = (SCROW) ::rtl::math::approxFloor(GetDouble());
6681         else
6682             nRow = 0;
6683         if (GetStackType() == svRefList)
6684             nAreaCount = (sp ? static_cast<ScToken*>(pStack[sp-1])->GetRefList()->size() : 0);
6685         else
6686             nAreaCount = 1;     // one reference or array or whatever
6687         if (nAreaCount == 0 || (size_t)nArea > nAreaCount)
6688         {
6689             PushError( errNoRef);
6690             return;
6691         }
6692         else if (nArea < 1 || nCol < 0 || nRow < 0)
6693         {
6694             PushIllegalArgument();
6695             return;
6696         }
6697         switch (GetStackType())
6698         {
6699             case svMatrix:
6700                 {
6701                     if (nArea != 1)
6702                         SetError(errIllegalArgument);
6703                     sal_uInt16 nOldSp = sp;
6704                     ScMatrixRef pMat = GetMatrix();
6705                     if (pMat)
6706                     {
6707                         SCSIZE nC, nR;
6708                         pMat->GetDimensions(nC, nR);
6709                         // Access one element of a vector independent of col/row
6710                         // orientation?
6711                         bool bVector = ((nCol == 0 || nRow == 0) && (nC == 1 || nR == 1));
6712                         SCSIZE nElement = ::std::max( static_cast<SCSIZE>(nCol),
6713                                 static_cast<SCSIZE>(nRow));
6714                         if (nC == 0 || nR == 0 ||
6715                                 (!bVector && (static_cast<SCSIZE>(nCol) > nC ||
6716                                               static_cast<SCSIZE>(nRow) > nR)) ||
6717                                 (bVector && nElement > nC * nR))
6718                             PushIllegalArgument();
6719                         else if (nCol == 0 && nRow == 0)
6720                             sp = nOldSp;
6721                         else if (bVector)
6722                         {
6723                             --nElement;
6724                             if (pMat->IsString( nElement))
6725                                 PushString( pMat->GetString( nElement));
6726                             else
6727                                 PushDouble( pMat->GetDouble( nElement));
6728                         }
6729                         else if (nCol == 0)
6730                         {
6731                             ScMatrixRef pResMat = GetNewMat(nC, 1);
6732                             if (pResMat)
6733                             {
6734                                 SCSIZE nRowMinus1 = static_cast<SCSIZE>(nRow - 1);
6735                                 for (SCSIZE i = 0; i < nC; i++)
6736                                     if (!pMat->IsString(i, nRowMinus1))
6737                                         pResMat->PutDouble(pMat->GetDouble(i,
6738                                                     nRowMinus1), i, 0);
6739                                     else
6740                                         pResMat->PutString(pMat->GetString(i,
6741                                                     nRowMinus1), i, 0);
6742                                 PushMatrix(pResMat);
6743                             }
6744                             else
6745                                 PushIllegalArgument();
6746                         }
6747                         else if (nRow == 0)
6748                         {
6749                             ScMatrixRef pResMat = GetNewMat(1, nR);
6750                             if (pResMat)
6751                             {
6752                                 SCSIZE nColMinus1 = static_cast<SCSIZE>(nCol - 1);
6753                                 for (SCSIZE i = 0; i < nR; i++)
6754                                     if (!pMat->IsString(nColMinus1, i))
6755                                         pResMat->PutDouble(pMat->GetDouble(nColMinus1,
6756                                                     i), i);
6757                                     else
6758                                         pResMat->PutString(pMat->GetString(nColMinus1,
6759                                                     i), i);
6760                                 PushMatrix(pResMat);
6761                             }
6762                             else
6763                                 PushIllegalArgument();
6764                         }
6765                         else
6766                         {
6767                             if (!pMat->IsString( static_cast<SCSIZE>(nCol-1),
6768                                         static_cast<SCSIZE>(nRow-1)))
6769                                 PushDouble( pMat->GetDouble(
6770                                             static_cast<SCSIZE>(nCol-1),
6771                                             static_cast<SCSIZE>(nRow-1)));
6772                             else
6773                                 PushString( pMat->GetString(
6774                                             static_cast<SCSIZE>(nCol-1),
6775                                             static_cast<SCSIZE>(nRow-1)));
6776                         }
6777                     }
6778                 }
6779                 break;
6780             case svSingleRef:
6781                 {
6782                     SCCOL nCol1 = 0;
6783                     SCROW nRow1 = 0;
6784                     SCTAB nTab1 = 0;
6785                     PopSingleRef( nCol1, nRow1, nTab1);
6786                     if (nCol > 1 || nRow > 1)
6787                         PushIllegalArgument();
6788                     else
6789                         PushSingleRef( nCol1, nRow1, nTab1);
6790                 }
6791                 break;
6792             case svDoubleRef:
6793             case svRefList:
6794                 {
6795                     SCCOL nCol1 = 0;
6796                     SCROW nRow1 = 0;
6797                     SCTAB nTab1 = 0;
6798                     SCCOL nCol2 = 0;
6799                     SCROW nRow2 = 0;
6800                     SCTAB nTab2 = 0;
6801                     sal_Bool bRowArray = sal_False;
6802                     if (GetStackType() == svRefList)
6803                     {
6804                         FormulaTokenRef xRef = PopToken();
6805                         if (nGlobalError || !xRef)
6806                         {
6807                             PushIllegalParameter();
6808                             return;
6809                         }
6810                         ScRange aRange( ScAddress::UNINITIALIZED);
6811                         DoubleRefToRange( (*(static_cast<ScToken*>(xRef.get())->GetRefList()))[nArea-1], aRange);
6812                         aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
6813                         if ( nParamCount == 2 && nRow1 == nRow2 )
6814                             bRowArray = sal_True;
6815                     }
6816                     else
6817                     {
6818                         PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
6819                         if ( nParamCount == 2 && nRow1 == nRow2 )
6820                             bRowArray = sal_True;
6821                     }
6822                     if ( nTab1 != nTab2 ||
6823                             (nCol > 0 && nCol1+nCol-1 > nCol2) ||
6824                             (nRow > 0 && nRow1+nRow-1 > nRow2 && !bRowArray ) ||
6825                             ( nRow > nCol2 - nCol1 + 1 && bRowArray ))
6826                         PushIllegalArgument();
6827                     else if (nCol == 0 && nRow == 0)
6828                     {
6829                         if ( nCol1 == nCol2 && nRow1 == nRow2 )
6830                             PushSingleRef( nCol1, nRow1, nTab1 );
6831                         else
6832                             PushDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab1 );
6833                     }
6834                     else if (nRow == 0)
6835                     {
6836                         if ( nRow1 == nRow2 )
6837                             PushSingleRef( nCol1+nCol-1, nRow1, nTab1 );
6838                         else
6839                             PushDoubleRef( nCol1+nCol-1, nRow1, nTab1,
6840                                     nCol1+nCol-1, nRow2, nTab1 );
6841                     }
6842                     else if (nCol == 0)
6843                     {
6844                         if ( nCol1 == nCol2 )
6845                             PushSingleRef( nCol1, nRow1+nRow-1, nTab1 );
6846                         else if ( bRowArray )
6847                         {
6848                             nCol =(SCCOL) nRow;
6849                             nRow = 1;
6850                             PushSingleRef( nCol1+nCol-1, nRow1+nRow-1, nTab1);
6851                         }
6852                         else
6853                             PushDoubleRef( nCol1, nRow1+nRow-1, nTab1,
6854                                     nCol2, nRow1+nRow-1, nTab1);
6855                     }
6856                     else
6857                         PushSingleRef( nCol1+nCol-1, nRow1+nRow-1, nTab1);
6858                 }
6859                 break;
6860             default:
6861                 PushIllegalParameter();
6862         }
6863     }
6864 }
6865 
6866 
6867 void ScInterpreter::ScMultiArea()
6868 {
6869     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScMultiArea" );
6870     // Legacy support, convert to RefList
6871     sal_uInt8 nParamCount = GetByte();
6872     if (MustHaveParamCountMin( nParamCount, 1))
6873     {
6874         while (!nGlobalError && nParamCount-- > 1)
6875         {
6876             ScUnionFunc();
6877         }
6878     }
6879 }
6880 
6881 
6882 void ScInterpreter::ScAreas()
6883 {
6884     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScAreas" );
6885     sal_uInt8 nParamCount = GetByte();
6886     if (MustHaveParamCount( nParamCount, 1))
6887     {
6888         size_t nCount = 0;
6889         switch (GetStackType())
6890         {
6891             case svSingleRef:
6892                 {
6893                     FormulaTokenRef xT = PopToken();
6894                     ValidateRef( static_cast<ScToken*>(xT.get())->GetSingleRef());
6895                     ++nCount;
6896                 }
6897                 break;
6898             case svDoubleRef:
6899                 {
6900                     FormulaTokenRef xT = PopToken();
6901                     ValidateRef( static_cast<ScToken*>(xT.get())->GetDoubleRef());
6902                     ++nCount;
6903                 }
6904                 break;
6905             case svRefList:
6906                 {
6907                     FormulaTokenRef xT = PopToken();
6908                     ValidateRef( *(static_cast<ScToken*>(xT.get())->GetRefList()));
6909                     nCount += static_cast<ScToken*>(xT.get())->GetRefList()->size();
6910                 }
6911                 break;
6912             default:
6913                 SetError( errIllegalParameter);
6914         }
6915         PushDouble( double(nCount));
6916     }
6917 }
6918 
6919 
6920 void ScInterpreter::ScCurrency()
6921 {
6922     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCurrency" );
6923     sal_uInt8 nParamCount = GetByte();
6924     if ( MustHaveParamCount( nParamCount, 1, 2 ) )
6925     {
6926         String aStr;
6927         double fDec;
6928         if (nParamCount == 2)
6929         {
6930             fDec = ::rtl::math::approxFloor(GetDouble());
6931             if (fDec < -15.0 || fDec > 15.0)
6932             {
6933                 PushIllegalArgument();
6934                 return;
6935             }
6936         }
6937         else
6938             fDec = 2.0;
6939         double fVal = GetDouble();
6940         double fFac;
6941         if ( fDec != 0.0 )
6942             fFac = pow( (double)10, fDec );
6943         else
6944             fFac = 1.0;
6945         if (fVal < 0.0)
6946             fVal = ceil(fVal*fFac-0.5)/fFac;
6947         else
6948             fVal = floor(fVal*fFac+0.5)/fFac;
6949         Color* pColor = NULL;
6950         if ( fDec < 0.0 )
6951             fDec = 0.0;
6952         sal_uLong nIndex = pFormatter->GetStandardFormat(
6953                                         NUMBERFORMAT_CURRENCY,
6954                                         ScGlobal::eLnge);
6955         if ( (sal_uInt16) fDec != pFormatter->GetFormatPrecision( nIndex ) )
6956         {
6957             String sFormatString;
6958             pFormatter->GenerateFormat(sFormatString,
6959                                                    nIndex,
6960                                                    ScGlobal::eLnge,
6961                                                    sal_True,        // mit Tausenderpunkt
6962                                                    sal_False,       // nicht rot
6963                                                   (sal_uInt16) fDec,// Nachkommastellen
6964                                                    1);          // 1 Vorkommanull
6965             if (!pFormatter->GetPreviewString(sFormatString,
6966                                                   fVal,
6967                                                   aStr,
6968                                                   &pColor,
6969                                                   ScGlobal::eLnge))
6970                 SetError(errIllegalArgument);
6971         }
6972         else
6973         {
6974             pFormatter->GetOutputString(fVal, nIndex, aStr, &pColor);
6975         }
6976         PushString(aStr);
6977     }
6978 }
6979 
6980 
6981 void ScInterpreter::ScReplace()
6982 {
6983     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScReplace" );
6984     if ( MustHaveParamCount( GetByte(), 4 ) )
6985     {
6986         String aNewStr( GetString() );
6987         double fCount = ::rtl::math::approxFloor( GetDouble());
6988         double fPos   = ::rtl::math::approxFloor( GetDouble());
6989         String aOldStr( GetString() );
6990         if (fPos < 1.0 || fPos > static_cast<double>(STRING_MAXLEN)
6991                 || fCount < 0.0 || fCount > static_cast<double>(STRING_MAXLEN))
6992             PushIllegalArgument();
6993         else
6994         {
6995             xub_StrLen nCount = static_cast<xub_StrLen>(fCount);
6996             xub_StrLen nPos   = static_cast<xub_StrLen>(fPos);
6997             xub_StrLen nLen   = aOldStr.Len();
6998             if (nPos > nLen + 1)
6999                 nPos = nLen + 1;
7000             if (nCount > nLen - nPos + 1)
7001                 nCount = nLen - nPos + 1;
7002             aOldStr.Erase( nPos-1, nCount );
7003             if ( CheckStringResultLen( aOldStr, aNewStr ) )
7004                 aOldStr.Insert( aNewStr, nPos-1 );
7005             PushString( aOldStr );
7006         }
7007     }
7008 }
7009 
7010 
7011 void ScInterpreter::ScFixed()
7012 {
7013     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFixed" );
7014     sal_uInt8 nParamCount = GetByte();
7015     if ( MustHaveParamCount( nParamCount, 1, 3 ) )
7016     {
7017         String aStr;
7018         double fDec;
7019         sal_Bool bThousand;
7020         if (nParamCount == 3)
7021             bThousand = !GetBool();     // Param TRUE: keine Tausenderpunkte
7022         else
7023             bThousand = sal_True;
7024         if (nParamCount >= 2)
7025         {
7026             fDec = ::rtl::math::approxFloor(GetDoubleWithDefault( 2.0 ));
7027             if (fDec < -15.0 || fDec > 15.0)
7028             {
7029                 PushIllegalArgument();
7030                 return;
7031             }
7032         }
7033         else
7034             fDec = 2.0;
7035         double fVal = GetDouble();
7036         double fFac;
7037         if ( fDec != 0.0 )
7038             fFac = pow( (double)10, fDec );
7039         else
7040             fFac = 1.0;
7041         if (fVal < 0.0)
7042             fVal = ceil(fVal*fFac-0.5)/fFac;
7043         else
7044             fVal = floor(fVal*fFac+0.5)/fFac;
7045         Color* pColor = NULL;
7046         String sFormatString;
7047         if (fDec < 0.0)
7048             fDec = 0.0;
7049         sal_uLong nIndex = pFormatter->GetStandardFormat(
7050                                             NUMBERFORMAT_NUMBER,
7051                                             ScGlobal::eLnge);
7052         pFormatter->GenerateFormat(sFormatString,
7053                                                nIndex,
7054                                                ScGlobal::eLnge,
7055                                                bThousand,   // mit Tausenderpunkt
7056                                                sal_False,       // nicht rot
7057                                                (sal_uInt16) fDec,// Nachkommastellen
7058                                                1);          // 1 Vorkommanull
7059         if (!pFormatter->GetPreviewString(sFormatString,
7060                                                   fVal,
7061                                                   aStr,
7062                                                   &pColor,
7063                                                   ScGlobal::eLnge))
7064             PushIllegalArgument();
7065         else
7066             PushString(aStr);
7067     }
7068 }
7069 
7070 
7071 void ScInterpreter::ScFind()
7072 {
7073     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFind" );
7074     sal_uInt8 nParamCount = GetByte();
7075     if ( MustHaveParamCount( nParamCount, 2, 3 ) )
7076     {
7077         double fAnz;
7078         if (nParamCount == 3)
7079             fAnz = GetDouble();
7080         else
7081             fAnz = 1.0;
7082         String sStr = GetString();
7083         if( fAnz < 1.0 || fAnz > (double) sStr.Len() )
7084             PushNoValue();
7085         else
7086         {
7087             xub_StrLen nPos = sStr.Search( GetString(), (xub_StrLen) fAnz - 1 );
7088             if (nPos == STRING_NOTFOUND)
7089                 PushNoValue();
7090             else
7091                 PushDouble((double)(nPos + 1));
7092         }
7093     }
7094 }
7095 
7096 
7097 void ScInterpreter::ScExact()
7098 {
7099     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScExact" );
7100     nFuncFmtType = NUMBERFORMAT_LOGICAL;
7101     if ( MustHaveParamCount( GetByte(), 2 ) )
7102     {
7103         String s1( GetString() );
7104         String s2( GetString() );
7105         PushInt( s1 == s2 );
7106     }
7107 }
7108 
7109 
7110 void ScInterpreter::ScLeft()
7111 {
7112     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLeft" );
7113     sal_uInt8 nParamCount = GetByte();
7114     if ( MustHaveParamCount( nParamCount, 1, 2 ) )
7115     {
7116         xub_StrLen n;
7117         if (nParamCount == 2)
7118         {
7119             double nVal = ::rtl::math::approxFloor(GetDouble());
7120             if ( nVal < 0.0 || nVal > STRING_MAXLEN )
7121             {
7122                 PushIllegalArgument();
7123                 return ;
7124             }
7125             else
7126                 n = (xub_StrLen) nVal;
7127         }
7128         else
7129             n = 1;
7130         String aStr( GetString() );
7131         aStr.Erase( n );
7132         PushString( aStr );
7133     }
7134 }
7135 
7136 
7137 void ScInterpreter::ScRight()
7138 {
7139     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRight" );
7140     sal_uInt8 nParamCount = GetByte();
7141     if ( MustHaveParamCount( nParamCount, 1, 2 ) )
7142     {
7143         xub_StrLen n;
7144         if (nParamCount == 2)
7145         {
7146             double nVal = ::rtl::math::approxFloor(GetDouble());
7147             if ( nVal < 0.0 || nVal > STRING_MAXLEN )
7148             {
7149                 PushIllegalArgument();
7150                 return ;
7151             }
7152             else
7153                 n = (xub_StrLen) nVal;
7154         }
7155         else
7156             n = 1;
7157         String aStr( GetString() );
7158         if( n < aStr.Len() )
7159             aStr.Erase( 0, aStr.Len() - n );
7160         PushString( aStr );
7161     }
7162 }
7163 
7164 
7165 void ScInterpreter::ScSearch()
7166 {
7167     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSearch" );
7168     double fAnz;
7169     sal_uInt8 nParamCount = GetByte();
7170     if ( MustHaveParamCount( nParamCount, 2, 3 ) )
7171     {
7172         if (nParamCount == 3)
7173         {
7174             fAnz = ::rtl::math::approxFloor(GetDouble());
7175             if (fAnz > double(STRING_MAXLEN))
7176             {
7177                 PushIllegalArgument();
7178                 return;
7179             }
7180         }
7181         else
7182             fAnz = 1.0;
7183         String sStr = GetString();
7184         String SearchStr = GetString();
7185         xub_StrLen nPos = (xub_StrLen) fAnz - 1;
7186         xub_StrLen nEndPos = sStr.Len();
7187         if( nPos >= nEndPos )
7188             PushNoValue();
7189         else
7190         {
7191             utl::SearchParam::SearchType eSearchType =
7192                 (MayBeRegExp( SearchStr, pDok ) ?
7193                 utl::SearchParam::SRCH_REGEXP : utl::SearchParam::SRCH_NORMAL);
7194             utl::SearchParam sPar(SearchStr, eSearchType, sal_False, sal_False, sal_False);
7195             utl::TextSearch sT( sPar, *ScGlobal::pCharClass );
7196             int nBool = sT.SearchFrwrd(sStr, &nPos, &nEndPos);
7197             if (!nBool)
7198                 PushNoValue();
7199             else
7200                 PushDouble((double)(nPos) + 1);
7201         }
7202     }
7203 }
7204 
7205 
7206 void ScInterpreter::ScMid()
7207 {
7208     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScMid" );
7209     if ( MustHaveParamCount( GetByte(), 3 ) )
7210     {
7211         double fAnz    = ::rtl::math::approxFloor(GetDouble());
7212         double fAnfang = ::rtl::math::approxFloor(GetDouble());
7213         const String& rStr = GetString();
7214         if (fAnfang < 1.0 || fAnz < 0.0 || fAnfang > double(STRING_MAXLEN) || fAnz > double(STRING_MAXLEN))
7215             PushIllegalArgument();
7216         else
7217             PushString(rStr.Copy( (xub_StrLen) fAnfang - 1, (xub_StrLen) fAnz ));
7218     }
7219 }
7220 
7221 
7222 void ScInterpreter::ScText()
7223 {
7224     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScText" );
7225     if ( MustHaveParamCount( GetByte(), 2 ) )
7226     {
7227         String sFormatString = GetString();
7228         String aStr;
7229         bool bString = false;
7230         double fVal = 0.0;
7231         switch (GetStackType())
7232         {
7233             case svError:
7234                 PopError();
7235                 break;
7236             case svDouble:
7237                 fVal = PopDouble();
7238                 break;
7239             default:
7240                 {
7241                     FormulaTokenRef xTok( PopToken());
7242                     if (!nGlobalError)
7243                     {
7244                         PushTempToken( xTok);
7245                         // Temporarily override the ConvertStringToValue()
7246                         // error for GetCellValue() / GetCellValueOrZero()
7247                         sal_uInt16 nSErr = mnStringNoValueError;
7248                         mnStringNoValueError = errNotNumericString;
7249                         fVal = GetDouble();
7250                         mnStringNoValueError = nSErr;
7251                         if (nGlobalError == errNotNumericString)
7252                         {
7253                             // Not numeric.
7254                             nGlobalError = 0;
7255                             PushTempToken( xTok);
7256                             aStr = GetString();
7257                             bString = true;
7258                         }
7259                     }
7260                 }
7261         }
7262         if (nGlobalError)
7263             PushError( nGlobalError);
7264         else
7265         {
7266             String aResult;
7267             Color* pColor = NULL;
7268             LanguageType eCellLang;
7269             const ScPatternAttr* pPattern = pDok->GetPattern(
7270                     aPos.Col(), aPos.Row(), aPos.Tab() );
7271             if ( pPattern )
7272                 eCellLang = ((const SvxLanguageItem&)
7273                         pPattern->GetItem( ATTR_LANGUAGE_FORMAT )).GetValue();
7274             else
7275                 eCellLang = ScGlobal::eLnge;
7276             if (bString)
7277             {
7278                 if (!pFormatter->GetPreviewString( sFormatString, aStr,
7279                             aResult, &pColor, eCellLang))
7280                     PushIllegalArgument();
7281                 else
7282                     PushString( aResult);
7283             }
7284             else
7285             {
7286                 if (!pFormatter->GetPreviewStringGuess( sFormatString, fVal,
7287                             aResult, &pColor, eCellLang))
7288                     PushIllegalArgument();
7289                 else
7290                     PushString( aResult);
7291             }
7292         }
7293     }
7294 }
7295 
7296 
7297 void ScInterpreter::ScSubstitute()
7298 {
7299     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSubstitute" );
7300     sal_uInt8 nParamCount = GetByte();
7301     if ( MustHaveParamCount( nParamCount, 3, 4 ) )
7302     {
7303         xub_StrLen nAnz;
7304         if (nParamCount == 4)
7305         {
7306             double fAnz = ::rtl::math::approxFloor(GetDouble());
7307             if( fAnz < 1 || fAnz > STRING_MAXLEN )
7308             {
7309                 PushIllegalArgument();
7310                 return;
7311             }
7312             else
7313                 nAnz = (xub_StrLen) fAnz;
7314         }
7315         else
7316             nAnz = 0;
7317         String sNewStr = GetString();
7318         String sOldStr = GetString();
7319         String sStr    = GetString();
7320         xub_StrLen nPos = 0;
7321         xub_StrLen nCount = 0;
7322         xub_StrLen nNewLen = sNewStr.Len();
7323         xub_StrLen nOldLen = sOldStr.Len();
7324         while( sal_True )
7325         {
7326             nPos = sStr.Search( sOldStr, nPos );
7327             if (nPos != STRING_NOTFOUND)
7328             {
7329                 nCount++;
7330                 if( !nAnz || nCount == nAnz )
7331                 {
7332                     sStr.Erase(nPos,nOldLen);
7333                     if ( CheckStringResultLen( sStr, sNewStr ) )
7334                     {
7335                         sStr.Insert(sNewStr,nPos);
7336                         nPos = sal::static_int_cast<xub_StrLen>( nPos + nNewLen );
7337                     }
7338                     else
7339                         break;
7340                 }
7341                 else
7342                     nPos++;
7343             }
7344             else
7345                 break;
7346         }
7347         PushString( sStr );
7348     }
7349 }
7350 
7351 
7352 void ScInterpreter::ScRept()
7353 {
7354     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRept" );
7355     if ( MustHaveParamCount( GetByte(), 2 ) )
7356     {
7357         double fAnz = ::rtl::math::approxFloor(GetDouble());
7358         String aStr( GetString() );
7359         if ( fAnz < 0.0 )
7360             PushIllegalArgument();
7361         else if ( fAnz * aStr.Len() > STRING_MAXLEN )
7362         {
7363             PushError( errStringOverflow );
7364         }
7365         else if ( fAnz == 0.0 )
7366             PushString( EMPTY_STRING );
7367         else
7368         {
7369             xub_StrLen n = (xub_StrLen) fAnz;
7370             const xub_StrLen nLen = aStr.Len();
7371             String aRes;
7372             const sal_Unicode* const pSrc = aStr.GetBuffer();
7373             sal_Unicode* pDst = aRes.AllocBuffer( n * nLen );
7374             while( n-- )
7375             {
7376                 memcpy( pDst, pSrc, nLen * sizeof(sal_Unicode) );
7377                 pDst += nLen;
7378             }
7379             PushString( aRes );
7380         }
7381     }
7382 }
7383 
7384 
7385 void ScInterpreter::ScConcat()
7386 {
7387     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScConcat" );
7388     sal_uInt8 nParamCount = GetByte();
7389     String aRes;
7390     while( nParamCount-- > 0)
7391     {
7392         const String& rStr = GetString();
7393         aRes.Insert( rStr, 0 );
7394     }
7395     PushString( aRes );
7396 }
7397 
7398 
7399 void ScInterpreter::ScErrorType()
7400 {
7401     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScErrorType" );
7402     sal_uInt16 nErr;
7403     sal_uInt16 nOldError = nGlobalError;
7404     nGlobalError = 0;
7405     switch ( GetStackType() )
7406     {
7407         case svRefList :
7408         {
7409             FormulaTokenRef x = PopToken();
7410             if (nGlobalError)
7411                 nErr = nGlobalError;
7412             else
7413             {
7414                 const ScRefList* pRefList = static_cast<ScToken*>(x.get())->GetRefList();
7415                 size_t n = pRefList->size();
7416                 if (!n)
7417                     nErr = errNoRef;
7418                 else if (n > 1)
7419                     nErr = errNoValue;
7420                 else
7421                 {
7422                     ScRange aRange;
7423                     DoubleRefToRange( (*pRefList)[0], aRange);
7424                     if (nGlobalError)
7425                         nErr = nGlobalError;
7426                     else
7427                     {
7428                         ScAddress aAdr;
7429                         if ( DoubleRefToPosSingleRef( aRange, aAdr ) )
7430                             nErr = pDok->GetErrCode( aAdr );
7431                         else
7432                             nErr = nGlobalError;
7433                     }
7434                 }
7435             }
7436         }
7437         break;
7438         case svDoubleRef :
7439         {
7440             ScRange aRange;
7441             PopDoubleRef( aRange );
7442             if ( nGlobalError )
7443                 nErr = nGlobalError;
7444             else
7445             {
7446                 ScAddress aAdr;
7447                 if ( DoubleRefToPosSingleRef( aRange, aAdr ) )
7448                     nErr = pDok->GetErrCode( aAdr );
7449                 else
7450                     nErr = nGlobalError;
7451             }
7452         }
7453         break;
7454         case svSingleRef :
7455         {
7456             ScAddress aAdr;
7457             PopSingleRef( aAdr );
7458             if ( nGlobalError )
7459                 nErr = nGlobalError;
7460             else
7461                 nErr = pDok->GetErrCode( aAdr );
7462         }
7463         break;
7464         default:
7465             PopError();
7466             nErr = nGlobalError;
7467     }
7468     if ( nErr )
7469     {
7470         nGlobalError = 0;
7471         PushDouble( nErr );
7472     }
7473     else
7474     {
7475         nGlobalError = nOldError;
7476         PushNA();
7477     }
7478 }
7479 
7480 
7481 sal_Bool ScInterpreter::MayBeRegExp( const String& rStr, const ScDocument* pDoc  )
7482 {
7483     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::MayBeRegExp" );
7484     if ( pDoc && !pDoc->GetDocOptions().IsFormulaRegexEnabled() )
7485         return sal_False;
7486     if ( !rStr.Len() || (rStr.Len() == 1 && rStr.GetChar(0) != '.') )
7487         return sal_False;   // einzelnes Metazeichen kann keine RegExp sein
7488     static const sal_Unicode cre[] = { '.','*','+','?','[',']','^','$','\\','<','>','(',')','|', 0 };
7489     const sal_Unicode* p1 = rStr.GetBuffer();
7490     sal_Unicode c1;
7491     while ( ( c1 = *p1++ ) != 0 )
7492     {
7493         const sal_Unicode* p2 = cre;
7494         while ( *p2 )
7495         {
7496             if ( c1 == *p2++ )
7497                 return sal_True;
7498         }
7499     }
7500     return sal_False;
7501 }
7502 
7503 static bool lcl_LookupQuery( ScAddress & o_rResultPos, ScDocument * pDoc,
7504         const ScQueryParam & rParam, const ScQueryEntry & rEntry )
7505 {
7506     bool bFound = false;
7507     ScQueryCellIterator aCellIter( pDoc, rParam.nTab, rParam, sal_False);
7508     if (rEntry.eOp != SC_EQUAL)
7509     {
7510         // range lookup <= or >=
7511         SCCOL nCol;
7512         SCROW nRow;
7513         bFound = aCellIter.FindEqualOrSortedLastInRange( nCol, nRow);
7514         if (bFound)
7515         {
7516             o_rResultPos.SetCol( nCol);
7517             o_rResultPos.SetRow( nRow);
7518         }
7519     }
7520     else if (aCellIter.GetFirst())
7521     {
7522         // EQUAL
7523         bFound = true;
7524         o_rResultPos.SetCol( aCellIter.GetCol());
7525         o_rResultPos.SetRow( aCellIter.GetRow());
7526     }
7527     return bFound;
7528 }
7529 
7530 #define erDEBUG_LOOKUPCACHE 0
7531 #if erDEBUG_LOOKUPCACHE
7532 #include <cstdio>
7533 using ::std::fprintf;
7534 using ::std::fflush;
7535 static struct LookupCacheDebugCounter
7536 {
7537     unsigned long nMiss;
7538     unsigned long nHit;
7539     LookupCacheDebugCounter() : nMiss(0), nHit(0) {}
7540     ~LookupCacheDebugCounter()
7541     {
7542         fprintf( stderr, "\nmiss: %lu, hit: %lu, total: %lu, hit/miss: %lu, hit/total %lu%\n",
7543                 nMiss, nHit, nHit+nMiss, (nMiss>0 ? nHit/nMiss : 0),
7544                 ((nHit+nMiss)>0 ? (100*nHit)/(nHit+nMiss) : 0));
7545         fflush( stderr);
7546     }
7547 } aLookupCacheDebugCounter;
7548 #endif
7549 
7550 bool ScInterpreter::LookupQueryWithCache( ScAddress & o_rResultPos,
7551         const ScQueryParam & rParam ) const
7552 {
7553     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::LookupQueryWithCache" );
7554     bool bFound = false;
7555     const ScQueryEntry& rEntry = rParam.GetEntry(0);
7556     bool bColumnsMatch = (rParam.nCol1 == rEntry.nField);
7557     DBG_ASSERT( bColumnsMatch, "ScInterpreter::LookupQueryWithCache: columns don't match");
7558     if (!bColumnsMatch)
7559         bFound = lcl_LookupQuery( o_rResultPos, pDok, rParam, rEntry);
7560     else
7561     {
7562         ScRange aLookupRange( rParam.nCol1, rParam.nRow1, rParam.nTab,
7563                 rParam.nCol2, rParam.nRow2, rParam.nTab);
7564         ScLookupCache& rCache = pDok->GetLookupCache( aLookupRange);
7565         ScLookupCache::QueryCriteria aCriteria( rEntry);
7566         ScLookupCache::Result eCacheResult = rCache.lookup( o_rResultPos,
7567                 aCriteria, aPos);
7568         switch (eCacheResult)
7569         {
7570             case ScLookupCache::NOT_CACHED :
7571             case ScLookupCache::CRITERIA_DIFFERENT :
7572 #if erDEBUG_LOOKUPCACHE
7573                 ++aLookupCacheDebugCounter.nMiss;
7574 #if erDEBUG_LOOKUPCACHE > 1
7575                 fprintf( stderr, "miss %d,%d,%d\n", (int)aPos.Col(), (int)aPos.Row(), (int)aPos.Tab());
7576 #endif
7577 #endif
7578                 bFound = lcl_LookupQuery( o_rResultPos, pDok, rParam, rEntry);
7579                 if (eCacheResult == ScLookupCache::NOT_CACHED)
7580                     rCache.insert( o_rResultPos, aCriteria, aPos, bFound);
7581                 break;
7582             case ScLookupCache::FOUND :
7583 #if erDEBUG_LOOKUPCACHE
7584                 ++aLookupCacheDebugCounter.nHit;
7585 #if erDEBUG_LOOKUPCACHE > 1
7586                 fprintf( stderr, "hit  %d,%d,%d\n", (int)aPos.Col(), (int)aPos.Row(), (int)aPos.Tab());
7587 #endif
7588 #endif
7589                 bFound = true;
7590                 break;
7591             case ScLookupCache::NOT_AVAILABLE :
7592                 ;   // nothing, bFound remains FALSE
7593                 break;
7594         }
7595     }
7596     return bFound;
7597 }
7598