sc/source/core/tool/interpr6.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  *
   9  * This file incorporates work covered by the following license notice:
  10  *
  11  *   Licensed to the Apache Software Foundation (ASF) under one or more
  12  *   contributor license agreements. See the NOTICE file distributed
  13  *   with this work for additional information regarding copyright
  14  *   ownership. The ASF licenses this file to you under the Apache
  15  *   License, Version 2.0 (the "License"); you may not use this file
  16  *   except in compliance with the License. You may obtain a copy of
  17  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  18  */
  19
  20 #include "interpre.hxx"
  21 #include "columnspanset.hxx"
  22 #include "column.hxx"
  23 #include "document.hxx"
  24 #include "cellvalue.hxx"
  25 #include "dociter.hxx"
  26 #include "mtvcellfunc.hxx"
  27 #include "scmatrix.hxx"
  28
  29 #include <formula/token.hxx>
  30
  31 using namespace formula;
  32
  33 double const fHalfMachEps = 0.5 * ::std::numeric_limits<double>::epsilon();
  34
  35 // The idea how this group of gamma functions is calculated, is
  36 // based on the Cephes library
  37 // online http://www.moshier.net/#Cephes [called 2008-02]
  38
  39 /** You must ensure fA>0.0 && fX>0.0
  40     valid results only if fX > fA+1.0
  41     uses continued fraction with odd items */
  42 double ScInterpreter::GetGammaContFraction( double fA, double fX )
  43 {
  44
  45     double const fBigInv = ::std::numeric_limits<double>::epsilon();
  46     double const fBig = 1.0/fBigInv;
  47     double fCount = 0.0;
  48     double fY = 1.0 - fA;
  49     double fDenom = fX + 2.0-fA;
  50     double fPkm1 = fX + 1.0;
  51     double fPkm2 = 1.0;
  52     double fQkm1 = fDenom * fX;
  53     double fQkm2 = fX;
  54     double fApprox = fPkm1/fQkm1;
  55     bool bFinished = false;
  56     do
  57     {
  58         fCount = fCount +1.0;
  59         fY = fY+ 1.0;
  60         const double fNum = fY * fCount;
  61         fDenom = fDenom +2.0;
  62         double fPk = fPkm1 * fDenom  -  fPkm2 * fNum;
  63         const double fQk = fQkm1 * fDenom  -  fQkm2 * fNum;
  64         if (fQk != 0.0)
  65         {
  66             const double fR = fPk/fQk;
  67             bFinished = (fabs( (fApprox - fR)/fR ) <= fHalfMachEps);
  68             fApprox = fR;
  69         }
  70         fPkm2 = fPkm1;
  71         fPkm1 = fPk;
  72         fQkm2 = fQkm1;
  73         fQkm1 = fQk;
  74         if (fabs(fPk) > fBig)
  75         {
  76             // reduce a fraction does not change the value
  77             fPkm2 = fPkm2 * fBigInv;
  78             fPkm1 = fPkm1 * fBigInv;
  79             fQkm2 = fQkm2 * fBigInv;
  80             fQkm1 = fQkm1 * fBigInv;
  81         }
  82     } while (!bFinished && fCount<10000);
  83     // most iterations, if fX==fAlpha+1.0; approx sqrt(fAlpha) iterations then
  84     if (!bFinished)
  85     {
  86         SetError(errNoConvergence);
  87     }
  88     return fApprox;
  89 }
  90
  91 /** You must ensure fA>0.0 && fX>0.0
  92     valid results only if fX <= fA+1.0
  93     uses power series */
  94 double ScInterpreter::GetGammaSeries( double fA, double fX )
  95 {
  96     double fDenomfactor = fA;
  97     double fSummand = 1.0/fA;
  98     double fSum = fSummand;
  99     int nCount=1;
 100     do
 101     {
 102         fDenomfactor = fDenomfactor + 1.0;
 103         fSummand = fSummand * fX/fDenomfactor;
 104         fSum = fSum + fSummand;
 105         nCount = nCount+1;
 106     } while ( fSummand/fSum > fHalfMachEps && nCount<=10000);
 107     // large amount of iterations will be carried out for huge fAlpha, even
 108     // if fX <= fAlpha+1.0
 109     if (nCount>10000)
 110     {
 111         SetError(errNoConvergence);
 112     }
 113     return fSum;
 114 }
 115
 116 /** You must ensure fA>0.0 && fX>0.0) */
 117 double ScInterpreter::GetLowRegIGamma( double fA, double fX )
 118 {
 119     double fLnFactor = fA * log(fX) - fX - GetLogGamma(fA);
 120     double fFactor = exp(fLnFactor);    // Do we need more accuracy than exp(ln()) has?
 121     if (fX>fA+1.0)  // includes fX>1.0; 1-GetUpRegIGamma, continued fraction
 122         return 1.0 - fFactor * GetGammaContFraction(fA,fX);
 123     else            // fX<=1.0 || fX<=fA+1.0, series
 124         return fFactor * GetGammaSeries(fA,fX);
 125 }
 126
 127 /** You must ensure fA>0.0 && fX>0.0) */
 128 double ScInterpreter::GetUpRegIGamma( double fA, double fX )
 129 {
 130
 131     double fLnFactor= fA*log(fX)-fX-GetLogGamma(fA);
 132     double fFactor = exp(fLnFactor); //Do I need more accuracy than exp(ln()) has?;
 133     if (fX>fA+1.0) // includes fX>1.0
 134             return fFactor * GetGammaContFraction(fA,fX);
 135     else //fX<=1 || fX<=fA+1, 1-GetLowRegIGamma, series
 136             return 1.0 -fFactor * GetGammaSeries(fA,fX);
 137 }
 138
 139 /** Gamma distribution, probability density function.
 140     fLambda is "scale" parameter
 141     You must ensure fAlpha>0.0 and fLambda>0.0 */
 142 double ScInterpreter::GetGammaDistPDF( double fX, double fAlpha, double fLambda )
 143 {
 144     if (fX < 0.0)
 145         return 0.0;     // see ODFF
 146     else if (fX == 0)
 147         // in this case 0^0 isn't zero
 148     {
 149         if (fAlpha < 1.0)
 150         {
 151             SetError(errDivisionByZero);  // should be #DIV/0
 152             return HUGE_VAL;
 153         }
 154         else if (fAlpha == 1)
 155         {
 156             return (1.0 / fLambda);
 157         }
 158         else
 159         {
 160             return 0.0;
 161         }
 162     }
 163     else
 164     {
 165         double fXr = fX / fLambda;
 166         // use exp(ln()) only for large arguments because of less accuracy
 167         if (fXr > 1.0)
 168         {
 169             const double fLogDblMax = log( ::std::numeric_limits<double>::max());
 170             if (log(fXr) * (fAlpha-1.0) < fLogDblMax && fAlpha < fMaxGammaArgument)
 171             {
 172                 return pow( fXr, fAlpha-1.0) * exp(-fXr) / fLambda / GetGamma(fAlpha);
 173             }
 174             else
 175             {
 176                 return exp( (fAlpha-1.0) * log(fXr) - fXr - log(fLambda) - GetLogGamma(fAlpha));
 177             }
 178         }
 179         else    // fXr near to zero
 180         {
 181             if (fAlpha<fMaxGammaArgument)
 182             {
 183                 return pow( fXr, fAlpha-1.0) * exp(-fXr) / fLambda / GetGamma(fAlpha);
 184             }
 185             else
 186             {
 187                 return pow( fXr, fAlpha-1.0) * exp(-fXr) / fLambda / exp( GetLogGamma(fAlpha));
 188             }
 189         }
 190     }
 191 }
 192
 193 /** Gamma distribution, cumulative distribution function.
 194     fLambda is "scale" parameter
 195     You must ensure fAlpha>0.0 and fLambda>0.0 */
 196 double ScInterpreter::GetGammaDist( double fX, double fAlpha, double fLambda )
 197 {
 198     if (fX <= 0.0)
 199         return 0.0;
 200     else
 201         return GetLowRegIGamma( fAlpha, fX / fLambda);
 202 }
 203
 204 namespace {
 205
 206 class NumericCellAccumulator
 207 {
 208     double mfSum;
 209     sal_uInt16 mnError;
 210
 211 public:
 212     NumericCellAccumulator() : mfSum(0.0), mnError(0) {}
 213
 214     void operator() (size_t, double fVal)
 215     {
 216         mfSum += fVal;
 217     }
 218
 219     void operator() (size_t, const ScFormulaCell* pCell)
 220     {
 221         if (mnError)
 222             // Skip all the rest if we have an error.
 223             return;
 224
 225         double fVal = 0.0;
 226         sal_uInt16 nErr = 0;
 227         ScFormulaCell& rCell = const_cast<ScFormulaCell&>(*pCell);
 228         if (!rCell.GetErrorOrValue(nErr, fVal))
 229             // The cell has neither error nor value.  Perhaps string result.
 230             return;
 231
 232         if (nErr)
 233         {
 234             // Cell has error.
 235             mnError = nErr;
 236             return;
 237         }
 238
 239         mfSum += fVal;
 240     }
 241
 242     sal_uInt16 getError() const { return mnError; }
 243     double getSum() const { return mfSum; }
 244 };
 245
 246 class NumericCellCounter
 247 {
 248     size_t mnCount;
 249 public:
 250     NumericCellCounter() : mnCount(0) {}
 251
 252     void operator() (const sc::CellStoreType::value_type& rNode, size_t nOffset, size_t nDataSize)
 253     {
 254         switch (rNode.type)
 255         {
 256             case sc::element_type_numeric:
 257                 mnCount += nDataSize;
 258             break;
 259             case sc::element_type_formula:
 260             {
 261                 sc::formula_block::const_iterator it = sc::formula_block::begin(*rNode.data);
 262                 std::advance(it, nOffset);
 263                 sc::formula_block::const_iterator itEnd = it;
 264                 std::advance(itEnd, nDataSize);
 265                 for (; it != itEnd; ++it)
 266                 {
 267                     ScFormulaCell& rCell = const_cast<ScFormulaCell&>(**it);
 268                     if (rCell.IsValueNoError())
 269                         ++mnCount;
 270                 }
 271             }
 272             break;
 273             default:
 274                 ;
 275         }
 276     }
 277
 278     size_t getCount() const { return mnCount; }
 279 };
 280
 281 class FuncCount : public sc::ColumnSpanSet::ColumnAction
 282 {
 283     sc::ColumnBlockConstPosition maPos;
 284     ScColumn* mpCol;
 285     size_t mnCount;
 286     sal_uInt32 mnNumFmt;
 287
 288 public:
 289     FuncCount() : mpCol(0), mnCount(0), mnNumFmt(0) {}
 290
 291     virtual void startColumn(ScColumn* pCol) SAL_OVERRIDE
 292     {
 293         mpCol = pCol;
 294         mpCol->InitBlockPosition(maPos);
 295     }
 296
 297     virtual void execute(SCROW nRow1, SCROW nRow2, bool bVal) SAL_OVERRIDE
 298     {
 299         if (!bVal)
 300             return;
 301
 302         NumericCellCounter aFunc;
 303         maPos.miCellPos = sc::ParseBlock(maPos.miCellPos, mpCol->GetCellStore(), aFunc, nRow1, nRow2);
 304         mnCount += aFunc.getCount();
 305         mnNumFmt = mpCol->GetNumberFormat(nRow2);
 306     };
 307
 308     size_t getCount() const { return mnCount; }
 309     sal_uInt32 getNumberFormat() const { return mnNumFmt; }
 310 };
 311
 312 class FuncSum : public sc::ColumnSpanSet::ColumnAction
 313 {
 314     sc::ColumnBlockConstPosition maPos;
 315     ScColumn* mpCol;
 316     double mfSum;
 317     sal_uInt16 mnError;
 318     sal_uInt32 mnNumFmt;
 319
 320 public:
 321     FuncSum() : mpCol(0), mfSum(0.0), mnError(0), mnNumFmt(0) {}
 322
 323     virtual void startColumn(ScColumn* pCol) SAL_OVERRIDE
 324     {
 325         mpCol = pCol;
 326         mpCol->InitBlockPosition(maPos);
 327     }
 328
 329     virtual void execute(SCROW nRow1, SCROW nRow2, bool bVal) SAL_OVERRIDE
 330     {
 331         if (!bVal)
 332             return;
 333
 334         if (mnError)
 335             return;
 336
 337         NumericCellAccumulator aFunc;
 338         maPos.miCellPos = sc::ParseFormulaNumeric(maPos.miCellPos, mpCol->GetCellStore(), nRow1, nRow2, aFunc);
 339         mnError = aFunc.getError();
 340         if (mnError)
 341             return;
 342
 343         mfSum += aFunc.getSum();
 344         mnNumFmt = mpCol->GetNumberFormat(nRow2);
 345     };
 346
 347     sal_uInt16 getError() const { return mnError; }
 348     double getSum() const { return mfSum; }
 349     sal_uInt32 getNumberFormat() const { return mnNumFmt; }
 350 };
 351
 352 void IterateMatrix(
 353     const ScMatrixRef& pMat, ScIterFunc eFunc, bool bTextAsZero,
 354     sal_uLong& rCount, short& rFuncFmtType, double& fRes, double& fMem, bool& bNull)
 355 {
 356     if (!pMat)
 357         return;
 358
 359     // TODO fdo73148 take mnSubTotalFlags into account
 360     rFuncFmtType = css::util::NumberFormat::NUMBER;
 361     switch (eFunc)
 362     {
 363         case ifAVERAGE:
 364         case ifSUM:
 365         {
 366             ScMatrix::IterateResult aRes = pMat->Sum(bTextAsZero);
 367             if (bNull)
 368             {
 369                 bNull = false;
 370                 fMem = aRes.mfFirst;
 371                 fRes += aRes.mfRest;
 372             }
 373             else
 374                 fRes += aRes.mfFirst + aRes.mfRest;
 375             rCount += aRes.mnCount;
 376         }
 377         break;
 378         case ifCOUNT:
 379             rCount += pMat->Count(bTextAsZero);
 380         break;
 381         case ifCOUNT2:
 382             rCount += pMat->Count(true);
 383         break;
 384         case ifPRODUCT:
 385         {
 386             ScMatrix::IterateResult aRes = pMat->Product(bTextAsZero);
 387             fRes *= aRes.mfFirst;
 388             fRes *= aRes.mfRest;
 389             rCount += aRes.mnCount;
 390         }
 391         break;
 392         case ifSUMSQ:
 393         {
 394             ScMatrix::IterateResult aRes = pMat->SumSquare(bTextAsZero);
 395             fRes += aRes.mfFirst;
 396             fRes += aRes.mfRest;
 397             rCount += aRes.mnCount;
 398         }
 399         break;
 400         default:
 401             ;
 402     }
 403 }
 404
 405 }
 406
 407 double ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
 408 {
 409     short nParamCount = GetByte();
 410     double fRes = ( eFunc == ifPRODUCT ) ? 1.0 : 0.0;
 411     double fVal = 0.0;
 412     double fMem = 0.0; // first numeric value.
 413     bool bNull = true;
 414     sal_uLong nCount = 0;
 415     ScAddress aAdr;
 416     ScRange aRange;
 417     size_t nRefInList = 0;
 418     if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
 419          ( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) ) )
 420         nGlobalError = 0;
 421     while (nParamCount-- > 0)
 422     {
 423         switch (GetStackType())
 424         {
 425             case svString:
 426             {
 427                 if( eFunc == ifCOUNT )
 428                 {
 429                     OUString aStr = PopString().getString();
 430                     if ( bTextAsZero )
 431                         nCount++;
 432                     else
 433                     {
 434                         // Only check if string can be converted to number, no
 435                         // error propagation.
 436                         sal_uInt16 nErr = nGlobalError;
 437                         nGlobalError = 0;
 438                         ConvertStringToValue( aStr );
 439                         if (!nGlobalError)
 440                             ++nCount;
 441                         nGlobalError = nErr;
 442                     }
 443                 }
 444                 else
 445                 {
 446                     switch ( eFunc )
 447                     {
 448                         case ifAVERAGE:
 449                         case ifSUM:
 450                         case ifSUMSQ:
 451                         case ifPRODUCT:
 452                         {
 453                             if ( bTextAsZero )
 454                             {
 455                                 Pop();
 456                                 nCount++;
 457                                 if ( eFunc == ifPRODUCT )
 458                                     fRes = 0.0;
 459                             }
 460                             else
 461                             {
 462                                 while (nParamCount-- > 0)
 463                                     Pop();
 464                                 SetError( errNoValue );
 465                             }
 466                         }
 467                         break;
 468                         default:
 469                             Pop();
 470                             nCount++;
 471                     }
 472                 }
 473             }
 474             break;
 475             case svDouble    :
 476                 fVal = GetDouble();
 477                 nCount++;
 478                 switch( eFunc )
 479                 {
 480                     case ifAVERAGE:
 481                     case ifSUM:
 482                         if ( bNull && fVal != 0.0 )
 483                         {
 484                             bNull = false;
 485                             fMem = fVal;
 486                         }
 487                         else
 488                             fRes += fVal;
 489                         break;
 490                     case ifSUMSQ:   fRes += fVal * fVal; break;
 491                     case ifPRODUCT: fRes *= fVal; break;
 492                     default: ; // nothing
 493                 }
 494                 nFuncFmtType = css::util::NumberFormat::NUMBER;
 495                 break;
 496             case svExternalSingleRef:
 497             {
 498                 ScExternalRefCache::TokenRef pToken;
 499                 ScExternalRefCache::CellFormat aFmt;
 500                 PopExternalSingleRef(pToken, &aFmt);
 501                 if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
 502                      ( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) ) )
 503                 {
 504                     nGlobalError = 0;
 505                     if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) )
 506                         ++nCount;
 507                     break;
 508                 }
 509
 510                 if (!pToken)
 511                     break;
 512
 513                 StackVar eType = pToken->GetType();
 514                 if (eFunc == ifCOUNT2)
 515                 {
 516                     if ( eType != formula::svEmptyCell &&
 517                          ( ( pToken->GetOpCode() != ocSubTotal &&
 518                              pToken->GetOpCode() != ocAggregate ) ||
 519                            ( mnSubTotalFlags & SUBTOTAL_IGN_NESTED_ST_AG ) ) )
 520                         nCount++;
 521                     if (nGlobalError)
 522                         nGlobalError = 0;
 523                 }
 524                 else if (eType == formula::svDouble)
 525                 {
 526                     nCount++;
 527                     fVal = pToken->GetDouble();
 528                     if (aFmt.mbIsSet)
 529                     {
 530                         nFuncFmtType = aFmt.mnType;
 531                         nFuncFmtIndex = aFmt.mnIndex;
 532                     }
 533                     switch( eFunc )
 534                     {
 535                         case ifAVERAGE:
 536                         case ifSUM:
 537                             if ( bNull && fVal != 0.0 )
 538                             {
 539                                 bNull = false;
 540                                 fMem = fVal;
 541                             }
 542                             else
 543                                 fRes += fVal;
 544                             break;
 545                         case ifSUMSQ:   fRes += fVal * fVal; break;
 546                         case ifPRODUCT: fRes *= fVal; break;
 547                         case ifCOUNT:
 548                             if ( nGlobalError )
 549                             {
 550                                 nGlobalError = 0;
 551                                 nCount--;
 552                             }
 553                             break;
 554                         default: ; // nothing
 555                     }
 556                 }
 557                 else if (bTextAsZero && eType == formula::svString)
 558                 {
 559                     nCount++;
 560                     if ( eFunc == ifPRODUCT )
 561                         fRes = 0.0;
 562                 }
 563             }
 564             break;
 565             case svSingleRef :
 566             {
 567                 PopSingleRef( aAdr );
 568                 if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
 569                      ( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) ) )
 570                 {
 571                     nGlobalError = 0;
 572                     if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) )
 573                         ++nCount;
 574                     break;
 575                 }
 576                 if ( ( mnSubTotalFlags & SUBTOTAL_IGN_FILTERED ) &&
 577                      pDok->RowFiltered( aAdr.Row(), aAdr.Tab() ) )
 578                 {
 579                     break;
 580                 }
 581                 ScRefCellValue aCell;
 582                 aCell.assign(*pDok, aAdr);
 583                 if (!aCell.isEmpty())
 584                 {
 585                     if( eFunc == ifCOUNT2 )
 586                     {
 587                         CellType eCellType = aCell.meType;
 588                         if ( eCellType != CELLTYPE_NONE )
 589                             nCount++;
 590                         if ( nGlobalError )
 591                             nGlobalError = 0;
 592                     }
 593                     else if (aCell.hasNumeric())
 594                     {
 595                         nCount++;
 596                         fVal = GetCellValue(aAdr, aCell);
 597                         CurFmtToFuncFmt();
 598                         switch( eFunc )
 599                         {
 600                             case ifAVERAGE:
 601                             case ifSUM:
 602                                 if ( bNull && fVal != 0.0 )
 603                                 {
 604                                     bNull = false;
 605                                     fMem = fVal;
 606                                 }
 607                                 else
 608                                     fRes += fVal;
 609                                 break;
 610                             case ifSUMSQ:   fRes += fVal * fVal; break;
 611                             case ifPRODUCT: fRes *= fVal; break;
 612                             case ifCOUNT:
 613                                 if ( nGlobalError )
 614                                 {
 615                                     nGlobalError = 0;
 616                                     nCount--;
 617                                 }
 618                                 break;
 619                             default: ; // nothing
 620                         }
 621                     }
 622                     else if (bTextAsZero && aCell.hasString())
 623                     {
 624                         nCount++;
 625                         if ( eFunc == ifPRODUCT )
 626                             fRes = 0.0;
 627                     }
 628                 }
 629             }
 630             break;
 631             case svDoubleRef :
 632             case svRefList :
 633             {
 634                 PopDoubleRef( aRange, nParamCount, nRefInList);
 635                 if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
 636                      ( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) ) )
 637                 {
 638                     nGlobalError = 0;
 639                     if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) )
 640                         ++nCount;
 641                     if ( eFunc == ifCOUNT2 || eFunc == ifCOUNT )
 642                         break;
 643                 }
 644                 if( eFunc == ifCOUNT2 )
 645                 {
 646                     ScCellIterator aIter( pDok, aRange, mnSubTotalFlags );
 647                     for (bool bHas = aIter.first(); bHas; bHas = aIter.next())
 648                     {
 649                         if ( !aIter.hasEmptyData() )
 650                         {
 651                             ++nCount;
 652                         }
 653                     }
 654
 655                     if ( nGlobalError )
 656                         nGlobalError = 0;
 657                 }
 658                 else
 659                 {
 660                     ScValueIterator aValIter( pDok, aRange, mnSubTotalFlags, bTextAsZero );
 661                     sal_uInt16 nErr = 0;
 662                     if (aValIter.GetFirst(fVal, nErr))
 663                     {
 664                         // placed the loop on the inside for performance reasons:
 665                         aValIter.GetCurNumFmtInfo( nFuncFmtType, nFuncFmtIndex );
 666                         switch( eFunc )
 667                         {
 668                             case ifAVERAGE:
 669                             case ifSUM:
 670                                     if ( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL )
 671                                     {
 672                                         do
 673                                         {
 674                                             if ( !nErr )
 675                                             {
 676                                                 SetError(nErr);
 677                                                 if ( bNull && fVal != 0.0 )
 678                                                 {
 679                                                     bNull = false;
 680                                                     fMem = fVal;
 681                                                 }
 682                                                 else
 683                                                     fRes += fVal;
 684                                                 nCount++;
 685                                             }
 686                                         }
 687                                         while (aValIter.GetNext(fVal, nErr));
 688                                     }
 689                                     else
 690                                     {
 691                                         do
 692                                         {
 693                                             SetError(nErr);
 694                                             if ( bNull && fVal != 0.0 )
 695                                             {
 696                                                 bNull = false;
 697                                                 fMem = fVal;
 698                                             }
 699                                             else
 700                                                 fRes += fVal;
 701                                             nCount++;
 702                                         }
 703                                         while (aValIter.GetNext(fVal, nErr));
 704                                     }
 705                                     break;
 706                             case ifSUMSQ:
 707                                     if ( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL )
 708                                     {
 709                                         do
 710                                         {
 711                                             if ( !nErr )
 712                                             {
 713                                                 SetError(nErr);
 714                                                 fRes += fVal * fVal;
 715                                                 nCount++;
 716                                             }
 717                                         }
 718                                         while (aValIter.GetNext(fVal, nErr));
 719                                     }
 720                                     else
 721                                     {
 722                                         do
 723                                         {
 724                                             SetError(nErr);
 725                                             fRes += fVal * fVal;
 726                                             nCount++;
 727                                         }
 728                                         while (aValIter.GetNext(fVal, nErr));
 729                                     }
 730                                     break;
 731                             case ifPRODUCT:
 732                                     do
 733                                     {
 734                                         if ( !( nErr && ( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) ) )
 735                                         {
 736                                             SetError(nErr);
 737                                             fRes *= fVal;
 738                                             nCount++;
 739                                         }
 740                                     }
 741                                     while (aValIter.GetNext(fVal, nErr));
 742                                     break;
 743                             case ifCOUNT:
 744                                     do
 745                                     {
 746                                         if ( !nErr )
 747                                             nCount++;
 748                                     }
 749                                     while (aValIter.GetNext(fVal, nErr));
 750                                     break;
 751                             default: ;  // nothing
 752                         }
 753                         SetError( nErr );
 754                     }
 755                 }
 756             }
 757             break;
 758             case svExternalDoubleRef:
 759             {
 760                 ScMatrixRef pMat;
 761                 PopExternalDoubleRef(pMat);
 762                 if ( nGlobalError && !( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) )
 763                     break;
 764
 765                 IterateMatrix(pMat, eFunc, bTextAsZero, nCount, nFuncFmtType, fRes, fMem, bNull);
 766             }
 767             break;
 768             case svMatrix :
 769             {
 770                 ScMatrixRef pMat = PopMatrix();
 771                 IterateMatrix(pMat, eFunc, bTextAsZero, nCount, nFuncFmtType, fRes, fMem, bNull);
 772             }
 773             break;
 774             case svError:
 775             {
 776                 PopError();
 777                 if ( eFunc == ifCOUNT || ( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) )
 778                 {
 779                     nGlobalError = 0;
 780                 }
 781                 else if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SUBTOTAL_IGN_ERR_VAL ) )
 782                 {
 783                     nCount++;
 784                     nGlobalError = 0;
 785                 }
 786             }
 787             break;
 788             default :
 789                 while (nParamCount-- > 0)
 790                     PopError();
 791                 SetError(errIllegalParameter);
 792         }
 793     }
 794     switch( eFunc )
 795     {
 796         case ifSUM:     fRes = ::rtl::math::approxAdd( fRes, fMem ); break;
 797         case ifAVERAGE: fRes = div(::rtl::math::approxAdd( fRes, fMem ), nCount); break;
 798         case ifCOUNT2:
 799         case ifCOUNT:   fRes  = nCount; break;
 800         case ifPRODUCT: if ( !nCount ) fRes = 0.0; break;
 801         default: ; // nothing
 802     }
 803     // Bei Summen etc. macht ein bool-Ergebnis keinen Sinn
 804     // und Anzahl ist immer Number (#38345#)
 805     if( eFunc == ifCOUNT || nFuncFmtType == css::util::NumberFormat::LOGICAL )
 806         nFuncFmtType = css::util::NumberFormat::NUMBER;
 807     return fRes;
 808 }
 809
 810 void ScInterpreter::ScSumSQ()
 811 {
 812     PushDouble( IterateParameters( ifSUMSQ ) );
 813 }
 814
 815 void ScInterpreter::ScSum()
 816 {
 817     if ( mnSubTotalFlags )
 818         PushDouble( IterateParameters( ifSUM ) );
 819     else
 820     {
 821         short nParamCount = GetByte();
 822         double fRes = 0.0;
 823         double fVal = 0.0;
 824         ScAddress aAdr;
 825         ScRange aRange;
 826         size_t nRefInList = 0;
 827         while (nParamCount-- > 0)
 828         {
 829             switch (GetStackType())
 830             {
 831                 case svString:
 832                 {
 833                     while (nParamCount-- > 0)
 834                         Pop();
 835                     SetError( errNoValue );
 836                 }
 837                 break;
 838                 case svDouble    :
 839                     fVal = GetDouble();
 840                     fRes += fVal;
 841                     nFuncFmtType = css::util::NumberFormat::NUMBER;
 842                     break;
 843                 case svExternalSingleRef:
 844                 {
 845                     ScExternalRefCache::TokenRef pToken;
 846                     ScExternalRefCache::CellFormat aFmt;
 847                     PopExternalSingleRef(pToken, &aFmt);
 848
 849                     if (!pToken)
 850                         break;
 851
 852                     StackVar eType = pToken->GetType();
 853                     if (eType == formula::svDouble)
 854                     {
 855                         fVal = pToken->GetDouble();
 856                         if (aFmt.mbIsSet)
 857                         {
 858                             nFuncFmtType = aFmt.mnType;
 859                             nFuncFmtIndex = aFmt.mnIndex;
 860                         }
 861
 862                         fRes += fVal;
 863                     }
 864                 }
 865                 break;
 866                 case svSingleRef :
 867                 {
 868                     PopSingleRef( aAdr );
 869
 870                     ScRefCellValue aCell;
 871                     aCell.assign(*pDok, aAdr);
 872                     if (!aCell.isEmpty())
 873                     {
 874                         if (aCell.hasNumeric())
 875                         {
 876                             fVal = GetCellValue(aAdr, aCell);
 877                             CurFmtToFuncFmt();
 878                             fRes += fVal;
 879                         }
 880                     }
 881                 }
 882                 break;
 883                 case svDoubleRef :
 884                 case svRefList :
 885                 {
 886                     PopDoubleRef( aRange, nParamCount, nRefInList);
 887
 888                     sc::ColumnSpanSet aSet(false);
 889                     aSet.set(aRange, true);
 890
 891                     FuncSum aAction;
 892                     aSet.executeColumnAction(*pDok, aAction);
 893                     sal_uInt16 nErr = aAction.getError();
 894                     if (nErr)
 895                     {
 896                         SetError(nErr);
 897                         return;
 898                     }
 899                     fRes += aAction.getSum();
 900
 901                     // Get the number format of the last iterated cell.
 902                     nFuncFmtIndex = aAction.getNumberFormat();
 903                     nFuncFmtType = pDok->GetFormatTable()->GetType(nFuncFmtIndex);
 904                 }
 905                 break;
 906                 case svExternalDoubleRef:
 907                 {
 908                     ScMatrixRef pMat;
 909                     PopExternalDoubleRef(pMat);
 910                     if (nGlobalError)
 911                         break;
 912
 913                     sal_uLong nCount = 0;
 914                     double fMem = 0.0;
 915                     bool bNull = true;
 916                     IterateMatrix(pMat, ifSUM, false, nCount, nFuncFmtType, fRes, fMem, bNull);
 917                     fRes += fMem;
 918                 }
 919                 break;
 920                 case svMatrix :
 921                 {
 922                     ScMatrixRef pMat = PopMatrix();
 923                     sal_uLong nCount = 0;
 924                     double fMem = 0.0;
 925                     bool bNull = true;
 926                     IterateMatrix(pMat, ifSUM, false, nCount, nFuncFmtType, fRes, fMem, bNull);
 927                     fRes += fMem;
 928                 }
 929                 break;
 930                 case svError:
 931                 {
 932                     PopError();
 933                 }
 934                 break;
 935                 default :
 936                     while (nParamCount-- > 0)
 937                         PopError();
 938                     SetError(errIllegalParameter);
 939             }
 940         }
 941
 942         if (nFuncFmtType == css::util::NumberFormat::LOGICAL)
 943             nFuncFmtType = css::util::NumberFormat::NUMBER;
 944
 945         PushDouble(fRes);
 946     }
 947 }
 948
 949 void ScInterpreter::ScProduct()
 950 {
 951     PushDouble( IterateParameters( ifPRODUCT ) );
 952 }
 953
 954 void ScInterpreter::ScAverage( bool bTextAsZero )
 955 {
 956     PushDouble( IterateParameters( ifAVERAGE, bTextAsZero ) );
 957 }
 958
 959 void ScInterpreter::ScCount()
 960 {
 961     if ( mnSubTotalFlags )
 962         PushDouble( IterateParameters( ifCOUNT ) );
 963     else
 964     {
 965         short nParamCount = GetByte();
 966         sal_uLong nCount = 0;
 967         ScAddress aAdr;
 968         ScRange aRange;
 969         size_t nRefInList = 0;
 970         if (nGlobalError)
 971             nGlobalError = 0;
 972
 973         while (nParamCount-- > 0)
 974         {
 975             switch (GetRawStackType())
 976             {
 977                 case svString:
 978                 {
 979                     OUString aStr = PopString().getString();
 980                     // Only check if string can be converted to number, no
 981                     // error propagation.
 982                     sal_uInt16 nErr = nGlobalError;
 983                     nGlobalError = 0;
 984                     ConvertStringToValue( aStr );
 985                     if (!nGlobalError)
 986                         ++nCount;
 987                     nGlobalError = nErr;
 988                 }
 989                 break;
 990                 case svDouble    :
 991                     GetDouble();
 992                     nCount++;
 993                     nFuncFmtType = css::util::NumberFormat::NUMBER;
 994                     break;
 995                 case svExternalSingleRef:
 996                 {
 997                     ScExternalRefCache::TokenRef pToken;
 998                     ScExternalRefCache::CellFormat aFmt;
 999                     PopExternalSingleRef(pToken, &aFmt);
1000                     if (nGlobalError)
1001                     {
1002                         nGlobalError = 0;
1003                         break;
1004                     }
1005
1006                     if (!pToken)
1007                         break;
1008
1009                     StackVar eType = pToken->GetType();
1010                     if (eType == formula::svDouble)
1011                     {
1012                         nCount++;
1013                         if (aFmt.mbIsSet)
1014                         {
1015                             nFuncFmtType = aFmt.mnType;
1016                             nFuncFmtIndex = aFmt.mnIndex;
1017                         }
1018
1019                         if (nGlobalError)
1020                         {
1021                             nGlobalError = 0;
1022                             nCount--;
1023                         }
1024                     }
1025                 }
1026                 break;
1027                 case svSingleRef :
1028                 {
1029                     PopSingleRef( aAdr );
1030                     if (nGlobalError)
1031                     {
1032                         nGlobalError = 0;
1033                         break;
1034                     }
1035                     ScRefCellValue aCell;
1036                     aCell.assign(*pDok, aAdr);
1037                     if (!aCell.isEmpty())
1038                     {
1039                         if (aCell.hasNumeric())
1040                         {
1041                             nCount++;
1042                             CurFmtToFuncFmt();
1043                             if (nGlobalError)
1044                             {
1045                                 nGlobalError = 0;
1046                                 nCount--;
1047                             }
1048                         }
1049                     }
1050                 }
1051                 break;
1052                 case svDoubleRef :
1053                 case svRefList :
1054                 {
1055                     PopDoubleRef( aRange, nParamCount, nRefInList);
1056                     if (nGlobalError)
1057                     {
1058                         nGlobalError = 0;
1059                         break;
1060                     }
1061
1062                     sc::ColumnSpanSet aSet(false);
1063                     aSet.set(aRange, true);
1064
1065                     FuncCount aAction;
1066                     aSet.executeColumnAction(*pDok, aAction);
1067                     nCount += aAction.getCount();
1068
1069                     // Get the number format of the last iterated cell.
1070                     nFuncFmtIndex = aAction.getNumberFormat();
1071                     nFuncFmtType = pDok->GetFormatTable()->GetType(nFuncFmtIndex);
1072                 }
1073                 break;
1074                 case svExternalDoubleRef:
1075                 {
1076                     ScMatrixRef pMat;
1077                     PopExternalDoubleRef(pMat);
1078                     if (nGlobalError)
1079                         break;
1080
1081                     double fMem = 0.0, fRes = 0.0;
1082                     bool bNull = true;
1083                     IterateMatrix(pMat, ifCOUNT, false, nCount, nFuncFmtType, fRes, fMem, bNull);
1084                 }
1085                 break;
1086                 case svMatrix :
1087                 {
1088                     ScMatrixRef pMat = PopMatrix();
1089                     double fMem = 0.0, fRes = 0.0;
1090                     bool bNull = true;
1091                     IterateMatrix(pMat, ifCOUNT, false, nCount, nFuncFmtType, fRes, fMem, bNull);
1092                 }
1093                 break;
1094                 case svError:
1095                 {
1096                     PopError();
1097                     nGlobalError = 0;
1098                 }
1099                 break;
1100                 default :
1101                     while (nParamCount-- > 0)
1102                         PopError();
1103                     SetError(errIllegalParameter);
1104             }
1105         }
1106
1107         nFuncFmtType = css::util::NumberFormat::NUMBER;
1108
1109         PushDouble(nCount);
1110     }
1111 }
1112
1113 void ScInterpreter::ScCount2()
1114 {
1115     PushDouble( IterateParameters( ifCOUNT2 ) );
1116 }
1117
1118 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */