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 <memory> 21 #include <interpre.hxx> 22 23 #include <comphelper/string.hxx> 24 #include <sfx2/linkmgr.hxx> 25 #include <sfx2/dispatch.hxx> 26 #include <sfx2/objsh.hxx> 27 #include <svl/stritem.hxx> 28 #include <svl/zforlist.hxx> 29 #include <svl/sharedstringpool.hxx> 30 #include <sal/macros.h> 31 #include <osl/diagnose.h> 32 33 #include <attrib.hxx> 34 #include <sc.hrc> 35 #include <ddelink.hxx> 36 #include <scmatrix.hxx> 37 #include <compiler.hxx> 38 #include <formulacell.hxx> 39 #include <document.hxx> 40 #include <dociter.hxx> 41 #include <docoptio.hxx> 42 #include <unitconv.hxx> 43 #include <hints.hxx> 44 #include <dpobject.hxx> 45 #include <postit.hxx> 46 #include <tokenarray.hxx> 47 #include <globalnames.hxx> 48 #include <stlsheet.hxx> 49 #include <dpcache.hxx> 50 51 #include <com/sun/star/sheet/DataPilotFieldFilter.hpp> 52 53 #include <limits> 54 #include <string.h> 55 #include <math.h> 56 57 using ::std::vector; 58 using namespace com::sun::star; 59 using namespace formula; 60 61 #define SCdEpsilon 1.0E-7 62 63 // Date and Time 64 65 double ScInterpreter::GetDateSerial( sal_Int16 nYear, sal_Int16 nMonth, sal_Int16 nDay, 66 bool bStrict ) 67 { 68 if ( nYear < 100 && !bStrict ) 69 nYear = pFormatter->ExpandTwoDigitYear( nYear ); 70 // Do not use a default Date ctor here because it asks system time with a 71 // performance penalty. 72 sal_Int16 nY, nM, nD; 73 if (bStrict) 74 { 75 nY = nYear; 76 nM = nMonth; 77 nD = nDay; 78 } 79 else 80 { 81 if (nMonth > 0) 82 { 83 nY = nYear + (nMonth-1) / 12; 84 nM = ((nMonth-1) % 12) + 1; 85 } 86 else 87 { 88 nY = nYear + (nMonth-12) / 12; 89 nM = 12 - (-nMonth) % 12; 90 } 91 nD = 1; 92 } 93 Date aDate( nD, nM, nY); 94 if (!bStrict) 95 aDate.AddDays( nDay - 1 ); 96 if (aDate.IsValidAndGregorian()) 97 return static_cast<double>(aDate - pFormatter->GetNullDate()); 98 else 99 { 100 SetError(FormulaError::NoValue); 101 return 0; 102 } 103 } 104 105 void ScInterpreter::ScGetActDate() 106 { 107 nFuncFmtType = SvNumFormatType::DATE; 108 Date aActDate( Date::SYSTEM ); 109 long nDiff = aActDate - pFormatter->GetNullDate(); 110 PushDouble(static_cast<double>(nDiff)); 111 } 112 113 void ScInterpreter::ScGetActTime() 114 { 115 nFuncFmtType = SvNumFormatType::DATETIME; 116 DateTime aActTime( DateTime::SYSTEM ); 117 long nDiff = aActTime - pFormatter->GetNullDate(); 118 double fTime = aActTime.GetHour() / static_cast<double>(::tools::Time::hourPerDay) + 119 aActTime.GetMin() / static_cast<double>(::tools::Time::minutePerDay) + 120 aActTime.GetSec() / static_cast<double>(::tools::Time::secondPerDay) + 121 aActTime.GetNanoSec() / static_cast<double>(::tools::Time::nanoSecPerDay); 122 PushDouble( static_cast<double>(nDiff) + fTime ); 123 } 124 125 void ScInterpreter::ScGetYear() 126 { 127 Date aDate = pFormatter->GetNullDate(); 128 aDate.AddDays( GetInt32()); 129 PushDouble( static_cast<double>(aDate.GetYear()) ); 130 } 131 132 void ScInterpreter::ScGetMonth() 133 { 134 Date aDate = pFormatter->GetNullDate(); 135 aDate.AddDays( GetInt32()); 136 PushDouble( static_cast<double>(aDate.GetMonth()) ); 137 } 138 139 void ScInterpreter::ScGetDay() 140 { 141 Date aDate = pFormatter->GetNullDate(); 142 aDate.AddDays( GetInt32()); 143 PushDouble(static_cast<double>(aDate.GetDay())); 144 } 145 146 void ScInterpreter::ScGetMin() 147 { 148 sal_uInt16 nHour, nMinute, nSecond; 149 double fFractionOfSecond; 150 tools::Time::GetClock( GetDouble(), nHour, nMinute, nSecond, fFractionOfSecond, 0); 151 PushDouble( nMinute); 152 } 153 154 void ScInterpreter::ScGetSec() 155 { 156 sal_uInt16 nHour, nMinute, nSecond; 157 double fFractionOfSecond; 158 tools::Time::GetClock( GetDouble(), nHour, nMinute, nSecond, fFractionOfSecond, 0); 159 if ( fFractionOfSecond >= 0.5 ) 160 nSecond = ( nSecond + 1 ) % 60; 161 PushDouble( nSecond ); 162 163 } 164 165 void ScInterpreter::ScGetHour() 166 { 167 sal_uInt16 nHour, nMinute, nSecond; 168 double fFractionOfSecond; 169 tools::Time::GetClock( GetDouble(), nHour, nMinute, nSecond, fFractionOfSecond, 0); 170 PushDouble( nHour); 171 } 172 173 void ScInterpreter::ScGetDateValue() 174 { 175 OUString aInputString = GetString().getString(); 176 sal_uInt32 nFIndex = 0; // for a default country/language 177 double fVal; 178 if (pFormatter->IsNumberFormat(aInputString, nFIndex, fVal)) 179 { 180 SvNumFormatType eType = pFormatter->GetType(nFIndex); 181 if (eType == SvNumFormatType::DATE || eType == SvNumFormatType::DATETIME) 182 { 183 nFuncFmtType = SvNumFormatType::DATE; 184 PushDouble(::rtl::math::approxFloor(fVal)); 185 } 186 else 187 PushIllegalArgument(); 188 } 189 else 190 PushIllegalArgument(); 191 } 192 193 void ScInterpreter::ScGetDayOfWeek() 194 { 195 sal_uInt8 nParamCount = GetByte(); 196 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 197 { 198 sal_Int16 nFlag; 199 if (nParamCount == 2) 200 nFlag = GetInt16(); 201 else 202 nFlag = 1; 203 204 Date aDate = pFormatter->GetNullDate(); 205 aDate.AddDays( GetInt32()); 206 int nVal = static_cast<int>(aDate.GetDayOfWeek()); // MONDAY = 0 207 switch (nFlag) 208 { 209 case 1: // Sunday = 1 210 if (nVal == 6) 211 nVal = 1; 212 else 213 nVal += 2; 214 break; 215 case 2: // Monday = 1 216 nVal += 1; 217 break; 218 case 3: // Monday = 0 219 ; // nothing 220 break; 221 case 11: // Monday = 1 222 case 12: // Tuesday = 1 223 case 13: // Wednesday = 1 224 case 14: // Thursday = 1 225 case 15: // Friday = 1 226 case 16: // Saturday = 1 227 case 17: // Sunday = 1 228 if (nVal < nFlag - 11) // x = nFlag - 11 = 0,1,2,3,4,5,6 229 nVal += 19 - nFlag; // nVal += (8 - (nFlag - 11) = 8 - x = 8,7,6,5,4,3,2) 230 else 231 nVal -= nFlag - 12; // nVal -= ((nFlag - 11) - 1 = x - 1 = -1,0,1,2,3,4,5) 232 break; 233 default: 234 SetError( FormulaError::IllegalArgument); 235 } 236 PushInt( nVal ); 237 } 238 } 239 240 void ScInterpreter::ScWeeknumOOo() 241 { 242 if ( MustHaveParamCount( GetByte(), 2 ) ) 243 { 244 sal_Int16 nFlag = GetInt16(); 245 246 Date aDate = pFormatter->GetNullDate(); 247 aDate.AddDays( GetInt32()); 248 PushInt( static_cast<int>(aDate.GetWeekOfYear( nFlag == 1 ? SUNDAY : MONDAY ))); 249 } 250 } 251 252 void ScInterpreter::ScGetWeekOfYear() 253 { 254 sal_uInt8 nParamCount = GetByte(); 255 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 256 { 257 sal_Int16 nFlag; 258 if (nParamCount == 1) 259 nFlag = 1; 260 else 261 nFlag = GetInt16(); 262 263 Date aDate = pFormatter->GetNullDate(); 264 aDate.AddDays( GetInt32()); 265 266 sal_Int32 nMinimumNumberOfDaysInWeek; 267 DayOfWeek eFirstDayOfWeek; 268 switch ( nFlag ) 269 { 270 case 1 : 271 eFirstDayOfWeek = SUNDAY; 272 nMinimumNumberOfDaysInWeek = 1; 273 break; 274 case 2 : 275 eFirstDayOfWeek = MONDAY; 276 nMinimumNumberOfDaysInWeek = 1; 277 break; 278 case 11 : 279 case 12 : 280 case 13 : 281 case 14 : 282 case 15 : 283 case 16 : 284 case 17 : 285 eFirstDayOfWeek = static_cast<DayOfWeek>( nFlag - 11 ); // MONDAY := 0 286 nMinimumNumberOfDaysInWeek = 1; //the week containing January 1 is week 1 287 break; 288 case 21 : 289 case 150 : 290 // ISO 8601 291 eFirstDayOfWeek = MONDAY; 292 nMinimumNumberOfDaysInWeek = 4; 293 break; 294 default : 295 PushIllegalArgument(); 296 return; 297 } 298 PushInt( static_cast<int>(aDate.GetWeekOfYear( eFirstDayOfWeek, nMinimumNumberOfDaysInWeek )) ); 299 } 300 } 301 302 void ScInterpreter::ScGetIsoWeekOfYear() 303 { 304 if ( MustHaveParamCount( GetByte(), 1 ) ) 305 { 306 Date aDate = pFormatter->GetNullDate(); 307 aDate.AddDays( GetInt32()); 308 PushInt( static_cast<int>(aDate.GetWeekOfYear()) ); 309 } 310 } 311 312 void ScInterpreter::ScEasterSunday() 313 { 314 nFuncFmtType = SvNumFormatType::DATE; 315 if ( MustHaveParamCount( GetByte(), 1 ) ) 316 { 317 sal_Int16 nDay, nMonth, nYear; 318 nYear = GetInt16(); 319 if (nGlobalError != FormulaError::NONE) 320 { 321 PushError( nGlobalError); 322 return; 323 } 324 if ( nYear < 100 ) 325 nYear = pFormatter->ExpandTwoDigitYear( nYear ); 326 if (nYear < 1583 || nYear > 9956) 327 { 328 // Valid Gregorian and maximum year constraints not met. 329 PushIllegalArgument(); 330 return; 331 } 332 // don't worry, be happy :) 333 int B,C,D,E,F,G,H,I,K,L,M,N,O; 334 N = nYear % 19; 335 B = int(nYear / 100); 336 C = nYear % 100; 337 D = int(B / 4); 338 E = B % 4; 339 F = int((B + 8) / 25); 340 G = int((B - F + 1) / 3); 341 H = (19 * N + B - D - G + 15) % 30; 342 I = int(C / 4); 343 K = C % 4; 344 L = (32 + 2 * E + 2 * I - H - K) % 7; 345 M = int((N + 11 * H + 22 * L) / 451); 346 O = H + L - 7 * M + 114; 347 nDay = sal::static_int_cast<sal_Int16>( O % 31 + 1 ); 348 nMonth = sal::static_int_cast<sal_Int16>( int(O / 31) ); 349 PushDouble( GetDateSerial( nYear, nMonth, nDay, true ) ); 350 } 351 } 352 353 FormulaError ScInterpreter::GetWeekendAndHolidayMasks( 354 const sal_uInt8 nParamCount, const sal_uInt32 nNullDate, vector< double >& rSortArray, 355 bool bWeekendMask[ 7 ] ) 356 { 357 if ( nParamCount == 4 ) 358 { 359 vector< double > nWeekendDays; 360 GetNumberSequenceArray( 1, nWeekendDays, false ); 361 if ( nGlobalError != FormulaError::NONE ) 362 return nGlobalError; 363 else 364 { 365 if ( nWeekendDays.size() != 7 ) 366 return FormulaError::IllegalArgument; 367 368 // Weekend days defined by string, Sunday...Saturday 369 for ( int i = 0; i < 7; i++ ) 370 bWeekendMask[ i ] = static_cast<bool>(nWeekendDays[ ( i == 6 ? 0 : i + 1 ) ]); 371 } 372 } 373 else 374 { 375 for ( int i = 0; i < 7; i++ ) 376 bWeekendMask[ i] = false; 377 378 bWeekendMask[ SATURDAY ] = true; 379 bWeekendMask[ SUNDAY ] = true; 380 } 381 382 if ( nParamCount >= 3 ) 383 { 384 GetSortArray( 1, rSortArray, nullptr, true, true ); 385 size_t nMax = rSortArray.size(); 386 for ( size_t i = 0; i < nMax; i++ ) 387 rSortArray.at( i ) = ::rtl::math::approxFloor( rSortArray.at( i ) ) + nNullDate; 388 } 389 390 return nGlobalError; 391 } 392 393 FormulaError ScInterpreter::GetWeekendAndHolidayMasks_MS( 394 const sal_uInt8 nParamCount, const sal_uInt32 nNullDate, vector< double >& rSortArray, 395 bool bWeekendMask[ 7 ], bool bWorkdayFunction ) 396 { 397 FormulaError nErr = FormulaError::NONE; 398 OUString aWeekendDays; 399 if ( nParamCount == 4 ) 400 { 401 GetSortArray( 1, rSortArray, nullptr, true, true ); 402 size_t nMax = rSortArray.size(); 403 for ( size_t i = 0; i < nMax; i++ ) 404 rSortArray.at( i ) = ::rtl::math::approxFloor( rSortArray.at( i ) ) + nNullDate; 405 } 406 407 if ( nParamCount >= 3 ) 408 { 409 if ( IsMissing() ) 410 Pop(); 411 else 412 { 413 switch ( GetStackType() ) 414 { 415 case svDoubleRef : 416 case svExternalDoubleRef : 417 return FormulaError::NoValue; 418 break; 419 420 default : 421 { 422 double fDouble; 423 svl::SharedString aSharedString; 424 bool bDouble = GetDoubleOrString( fDouble, aSharedString); 425 if ( bDouble ) 426 { 427 if ( fDouble >= 1.0 && fDouble <= 17 ) 428 aWeekendDays = OUString::number( fDouble ); 429 else 430 return FormulaError::NoValue; 431 } 432 else 433 { 434 if ( aSharedString.isEmpty() || aSharedString.getLength() != 7 || 435 ( bWorkdayFunction && aSharedString.getString() == "1111111" ) ) 436 return FormulaError::NoValue; 437 else 438 aWeekendDays = aSharedString.getString(); 439 } 440 } 441 break; 442 } 443 } 444 } 445 446 for ( int i = 0; i < 7; i++ ) 447 bWeekendMask[ i] = false; 448 449 if ( aWeekendDays.isEmpty() ) 450 { 451 bWeekendMask[ SATURDAY ] = true; 452 bWeekendMask[ SUNDAY ] = true; 453 } 454 else 455 { 456 switch ( aWeekendDays.getLength() ) 457 { 458 case 1 : 459 // Weekend days defined by code 460 switch ( aWeekendDays[ 0 ] ) 461 { 462 case '1' : bWeekendMask[ SATURDAY ] = true; bWeekendMask[ SUNDAY ] = true; break; 463 case '2' : bWeekendMask[ SUNDAY ] = true; bWeekendMask[ MONDAY ] = true; break; 464 case '3' : bWeekendMask[ MONDAY ] = true; bWeekendMask[ TUESDAY ] = true; break; 465 case '4' : bWeekendMask[ TUESDAY ] = true; bWeekendMask[ WEDNESDAY ] = true; break; 466 case '5' : bWeekendMask[ WEDNESDAY ] = true; bWeekendMask[ THURSDAY ] = true; break; 467 case '6' : bWeekendMask[ THURSDAY ] = true; bWeekendMask[ FRIDAY ] = true; break; 468 case '7' : bWeekendMask[ FRIDAY ] = true; bWeekendMask[ SATURDAY ] = true; break; 469 default : nErr = FormulaError::IllegalArgument; break; 470 } 471 break; 472 case 2 : 473 // Weekend day defined by code 474 if ( aWeekendDays[ 0 ] == '1' ) 475 { 476 switch ( aWeekendDays[ 1 ] ) 477 { 478 case '1' : bWeekendMask[ SUNDAY ] = true; break; 479 case '2' : bWeekendMask[ MONDAY ] = true; break; 480 case '3' : bWeekendMask[ TUESDAY ] = true; break; 481 case '4' : bWeekendMask[ WEDNESDAY ] = true; break; 482 case '5' : bWeekendMask[ THURSDAY ] = true; break; 483 case '6' : bWeekendMask[ FRIDAY ] = true; break; 484 case '7' : bWeekendMask[ SATURDAY ] = true; break; 485 default : nErr = FormulaError::IllegalArgument; break; 486 } 487 } 488 else 489 nErr = FormulaError::IllegalArgument; 490 break; 491 case 7 : 492 // Weekend days defined by string 493 for ( int i = 0; i < 7 && nErr == FormulaError::NONE; i++ ) 494 { 495 switch ( aWeekendDays[ i ] ) 496 { 497 case '0' : bWeekendMask[ i ] = false; break; 498 case '1' : bWeekendMask[ i ] = true; break; 499 default : nErr = FormulaError::IllegalArgument; break; 500 } 501 } 502 break; 503 default : 504 nErr = FormulaError::IllegalArgument; 505 break; 506 } 507 } 508 return nErr; 509 } 510 511 void ScInterpreter::ScNetWorkdays( bool bOOXML_Version ) 512 { 513 sal_uInt8 nParamCount = GetByte(); 514 if ( MustHaveParamCount( nParamCount, 2, 4 ) ) 515 { 516 vector<double> nSortArray; 517 bool bWeekendMask[ 7 ]; 518 const Date& rNullDate = pFormatter->GetNullDate(); 519 sal_uInt32 nNullDate = Date::DateToDays( rNullDate.GetDay(), rNullDate.GetMonth(), rNullDate.GetYear() ); 520 FormulaError nErr; 521 if ( bOOXML_Version ) 522 { 523 nErr = GetWeekendAndHolidayMasks_MS( nParamCount, nNullDate, 524 nSortArray, bWeekendMask, false ); 525 } 526 else 527 { 528 nErr = GetWeekendAndHolidayMasks( nParamCount, nNullDate, 529 nSortArray, bWeekendMask ); 530 } 531 if ( nErr != FormulaError::NONE ) 532 PushError( nErr ); 533 else 534 { 535 sal_uInt32 nDate2 = GetUInt32(); 536 sal_uInt32 nDate1 = GetUInt32(); 537 if (nGlobalError != FormulaError::NONE || (nDate1 > SAL_MAX_UINT32 - nNullDate) || nDate2 > (SAL_MAX_UINT32 - nNullDate)) 538 { 539 PushIllegalArgument(); 540 return; 541 } 542 nDate2 += nNullDate; 543 nDate1 += nNullDate; 544 545 sal_Int32 nCnt = 0; 546 size_t nRef = 0; 547 bool bReverse = ( nDate1 > nDate2 ); 548 if ( bReverse ) 549 { 550 sal_uInt32 nTemp = nDate1; 551 nDate1 = nDate2; 552 nDate2 = nTemp; 553 } 554 size_t nMax = nSortArray.size(); 555 while ( nDate1 <= nDate2 ) 556 { 557 if ( !bWeekendMask[ GetDayOfWeek( nDate1 ) ] ) 558 { 559 while ( nRef < nMax && nSortArray.at( nRef ) < nDate1 ) 560 nRef++; 561 if ( !( nRef < nMax && nSortArray.at( nRef ) == nDate1 ) ) 562 nCnt++; 563 } 564 ++nDate1; 565 } 566 PushDouble( static_cast<double>( bReverse ? -nCnt : nCnt ) ); 567 } 568 } 569 } 570 571 void ScInterpreter::ScWorkday_MS() 572 { 573 sal_uInt8 nParamCount = GetByte(); 574 if ( MustHaveParamCount( nParamCount, 2, 4 ) ) 575 { 576 nFuncFmtType = SvNumFormatType::DATE; 577 vector<double> nSortArray; 578 bool bWeekendMask[ 7 ]; 579 const Date& rNullDate = pFormatter->GetNullDate(); 580 sal_uInt32 nNullDate = Date::DateToDays( rNullDate.GetDay(), rNullDate.GetMonth(), rNullDate.GetYear() ); 581 FormulaError nErr = GetWeekendAndHolidayMasks_MS( nParamCount, nNullDate, 582 nSortArray, bWeekendMask, true ); 583 if ( nErr != FormulaError::NONE ) 584 PushError( nErr ); 585 else 586 { 587 sal_Int32 nDays = GetInt32(); 588 sal_uInt32 nDate = GetUInt32(); 589 if (nGlobalError != FormulaError::NONE || (nDate > SAL_MAX_UINT32 - nNullDate)) 590 { 591 PushIllegalArgument(); 592 return; 593 } 594 nDate += nNullDate; 595 596 if ( !nDays ) 597 PushDouble( static_cast<double>( nDate - nNullDate ) ); 598 else 599 { 600 size_t nMax = nSortArray.size(); 601 if ( nDays > 0 ) 602 { 603 size_t nRef = 0; 604 while ( nDays ) 605 { 606 do 607 { 608 ++nDate; 609 } 610 while ( bWeekendMask[ GetDayOfWeek( nDate ) ] ); //jump over weekend day(s) 611 612 while ( nRef < nMax && nSortArray.at( nRef ) < nDate ) 613 nRef++; 614 615 if ( !( nRef < nMax && nSortArray.at( nRef ) == nDate ) || nRef >= nMax ) 616 nDays--; 617 } 618 } 619 else 620 { 621 sal_Int16 nRef = nMax - 1; 622 while ( nDays ) 623 { 624 do 625 { 626 --nDate; 627 } 628 while ( bWeekendMask[ GetDayOfWeek( nDate ) ] ); //jump over weekend day(s) 629 630 while ( nRef >= 0 && nSortArray.at( nRef ) > nDate ) 631 nRef--; 632 633 if (nRef < 0 || nSortArray.at(nRef) != nDate) 634 nDays++; 635 } 636 } 637 PushDouble( static_cast<double>( nDate - nNullDate ) ); 638 } 639 } 640 } 641 } 642 643 void ScInterpreter::ScGetDate() 644 { 645 nFuncFmtType = SvNumFormatType::DATE; 646 if ( MustHaveParamCount( GetByte(), 3 ) ) 647 { 648 sal_Int16 nDay = GetInt16(); 649 sal_Int16 nMonth = GetInt16(); 650 if (IsMissing()) 651 SetError( FormulaError::ParameterExpected); // Year must be given. 652 sal_Int16 nYear = GetInt16(); 653 if (nGlobalError != FormulaError::NONE || nYear < 0) 654 PushIllegalArgument(); 655 else 656 { 657 PushDouble(GetDateSerial(nYear, nMonth, nDay, false)); 658 } 659 } 660 } 661 662 void ScInterpreter::ScGetTime() 663 { 664 nFuncFmtType = SvNumFormatType::TIME; 665 if ( MustHaveParamCount( GetByte(), 3 ) ) 666 { 667 double fSec = GetDouble(); 668 double fMin = GetDouble(); 669 double fHour = GetDouble(); 670 double fTime = fmod( (fHour * ::tools::Time::secondPerHour) + (fMin * ::tools::Time::secondPerMinute) + fSec, DATE_TIME_FACTOR) / DATE_TIME_FACTOR; 671 if (fTime < 0) 672 PushIllegalArgument(); 673 else 674 PushDouble( fTime); 675 } 676 } 677 678 void ScInterpreter::ScGetDiffDate() 679 { 680 if ( MustHaveParamCount( GetByte(), 2 ) ) 681 { 682 double fDate2 = GetDouble(); 683 double fDate1 = GetDouble(); 684 PushDouble(fDate1 - fDate2); 685 } 686 } 687 688 void ScInterpreter::ScGetDiffDate360() 689 { 690 /* Implementation follows 691 * http://www.bondmarkets.com/eCommerce/SMD_Fields_030802.pdf 692 * Appendix B: Day-Count Bases, there are 7 different ways to calculate the 693 * 30-days count. That document also claims that Excel implements the "PSA 694 * 30" or "NASD 30" method (funny enough they also state that Excel is the 695 * only tool that does so). 696 * 697 * Note that the definition given in 698 * http://msdn.microsoft.com/library/en-us/office97/html/SEB7C.asp 699 * is _not_ the way how it is actually calculated by Excel (that would not 700 * even match any of the 7 methods mentioned above) and would result in the 701 * following test cases producing wrong results according to that appendix B: 702 * 703 * 28-Feb-95 31-Aug-95 181 instead of 180 704 * 29-Feb-96 31-Aug-96 181 instead of 180 705 * 30-Jan-96 31-Mar-96 61 instead of 60 706 * 31-Jan-96 31-Mar-96 61 instead of 60 707 * 708 * Still, there is a difference between OOoCalc and Excel: 709 * In Excel: 710 * 02-Feb-99 31-Mar-00 results in 419 711 * 31-Mar-00 02-Feb-99 results in -418 712 * In Calc the result is 419 respectively -419. I consider the -418 a bug in Excel. 713 */ 714 715 sal_uInt8 nParamCount = GetByte(); 716 if ( MustHaveParamCount( nParamCount, 2, 3 ) ) 717 { 718 bool bFlag; 719 if (nParamCount == 3) 720 bFlag = GetBool(); 721 else 722 bFlag = false; 723 sal_Int32 nDate2 = GetInt32(); 724 sal_Int32 nDate1 = GetInt32(); 725 if (nGlobalError != FormulaError::NONE) 726 PushError( nGlobalError); 727 else 728 { 729 sal_Int32 nSign; 730 // #i84934# only for non-US European algorithm swap dates. Else 731 // follow Excel's meaningless extrapolation for "interoperability". 732 if (bFlag && (nDate2 < nDate1)) 733 { 734 nSign = nDate1; 735 nDate1 = nDate2; 736 nDate2 = nSign; 737 nSign = -1; 738 } 739 else 740 nSign = 1; 741 Date aDate1 = pFormatter->GetNullDate(); 742 aDate1.AddDays( nDate1); 743 Date aDate2 = pFormatter->GetNullDate(); 744 aDate2.AddDays( nDate2); 745 if (aDate1.GetDay() == 31) 746 aDate1.AddDays( -1); 747 else if (!bFlag) 748 { 749 if (aDate1.GetMonth() == 2) 750 { 751 switch ( aDate1.GetDay() ) 752 { 753 case 28 : 754 if ( !aDate1.IsLeapYear() ) 755 aDate1.SetDay(30); 756 break; 757 case 29 : 758 aDate1.SetDay(30); 759 break; 760 } 761 } 762 } 763 if (aDate2.GetDay() == 31) 764 { 765 if (!bFlag ) 766 { 767 if (aDate1.GetDay() == 30) 768 aDate2.AddDays( -1); 769 } 770 else 771 aDate2.SetDay(30); 772 } 773 PushDouble( static_cast<double>(nSign) * 774 ( static_cast<double>(aDate2.GetDay()) + static_cast<double>(aDate2.GetMonth()) * 30.0 + 775 static_cast<double>(aDate2.GetYear()) * 360.0 776 - static_cast<double>(aDate1.GetDay()) - static_cast<double>(aDate1.GetMonth()) * 30.0 777 - static_cast<double>(aDate1.GetYear()) * 360.0) ); 778 } 779 } 780 } 781 782 // fdo#44456 function DATEDIF as defined in ODF1.2 (Par. 6.10.3) 783 void ScInterpreter::ScGetDateDif() 784 { 785 if ( MustHaveParamCount( GetByte(), 3 ) ) 786 { 787 OUString aInterval = GetString().getString(); 788 sal_Int32 nDate2 = GetInt32(); 789 sal_Int32 nDate1 = GetInt32(); 790 791 if (nGlobalError != FormulaError::NONE) 792 { 793 PushError( nGlobalError); 794 return; 795 } 796 797 // Excel doesn't swap dates or return negative numbers, so don't we. 798 if (nDate1 > nDate2) 799 { 800 PushIllegalArgument(); 801 return; 802 } 803 804 double dd = nDate2 - nDate1; 805 // Zero difference or number of days can be returned immediately. 806 if (dd == 0.0 || aInterval.equalsIgnoreAsciiCase( "d" )) 807 { 808 PushDouble( dd ); 809 return; 810 } 811 812 // split dates in day, month, year for use with formats other than "d" 813 sal_uInt16 d1, m1, d2, m2; 814 sal_Int16 y1, y2; 815 Date aDate1( pFormatter->GetNullDate()); 816 aDate1.AddDays( nDate1); 817 y1 = aDate1.GetYear(); 818 m1 = aDate1.GetMonth(); 819 d1 = aDate1.GetDay(); 820 Date aDate2( pFormatter->GetNullDate()); 821 aDate2.AddDays( nDate2); 822 y2 = aDate2.GetYear(); 823 m2 = aDate2.GetMonth(); 824 d2 = aDate2.GetDay(); 825 826 // Close the year 0 gap to calculate year difference. 827 if (y1 < 0 && y2 > 0) 828 ++y1; 829 else if (y1 > 0 && y2 < 0) 830 ++y2; 831 832 if ( aInterval.equalsIgnoreAsciiCase( "m" ) ) 833 { 834 // Return number of months. 835 int md = m2 - m1 + 12 * (y2 - y1); 836 if (d1 > d2) 837 --md; 838 PushInt( md ); 839 } 840 else if ( aInterval.equalsIgnoreAsciiCase( "y" ) ) 841 { 842 // Return number of years. 843 int yd; 844 if ( y2 > y1 ) 845 { 846 if (m2 > m1 || (m2 == m1 && d2 >= d1)) 847 yd = y2 - y1; // complete years between dates 848 else 849 yd = y2 - y1 - 1; // one incomplete year 850 } 851 else 852 { 853 // Year is equal as we don't allow reversed arguments, no 854 // complete year between dates. 855 yd = 0; 856 } 857 PushInt( yd ); 858 } 859 else if ( aInterval.equalsIgnoreAsciiCase( "md" ) ) 860 { 861 // Return number of days, excluding months and years. 862 // This is actually the remainder of days when subtracting years 863 // and months from the difference of dates. Birthday-like 23 years 864 // and 10 months and 19 days. 865 866 // Algorithm's roll-over behavior extracted from Excel by try and 867 // error.. 868 // If day1 <= day2 then simply day2 - day1. 869 // If day1 > day2 then set month1 to month2-1 and year1 to 870 // year2(-1) and subtract dates, e.g. for 2012-01-28,2012-03-01 set 871 // 2012-02-28 and then (2012-03-01)-(2012-02-28) => 2 days (leap 872 // year). 873 // For 2011-01-29,2011-03-01 the non-existent 2011-02-29 rolls over 874 // to 2011-03-01 so the result is 0. Same for day 31 in months with 875 // only 30 days. 876 877 long nd; 878 if (d1 <= d2) 879 nd = d2 - d1; 880 else 881 { 882 if (m2 == 1) 883 { 884 aDate1.SetYear( y2 == 1 ? -1 : y2 - 1 ); 885 aDate1.SetMonth( 12 ); 886 } 887 else 888 { 889 aDate1.SetYear( y2 ); 890 aDate1.SetMonth( m2 - 1 ); 891 } 892 aDate1.Normalize(); 893 nd = aDate2 - aDate1; 894 } 895 PushDouble( nd ); 896 } 897 else if ( aInterval.equalsIgnoreAsciiCase( "ym" ) ) 898 { 899 // Return number of months, excluding years. 900 int md = m2 - m1 + 12 * (y2 - y1); 901 if (d1 > d2) 902 --md; 903 md %= 12; 904 PushInt( md ); 905 } 906 else if ( aInterval.equalsIgnoreAsciiCase( "yd" ) ) 907 { 908 // Return number of days, excluding years. 909 910 // Condition corresponds with "y". 911 if (m2 > m1 || (m2 == m1 && d2 >= d1)) 912 aDate1.SetYear( y2 ); 913 else 914 aDate1.SetYear( y2 - 1 ); 915 // XXX NOTE: Excel for the case 1988-06-22,2012-05-11 returns 916 // 323, whereas the result here is 324. Don't they use the leap 917 // year of 2012? 918 // http://www.cpearson.com/excel/datedif.aspx "DATEDIF And Leap 919 // Years" is not correct and Excel 2010 correctly returns 0 in 920 // both cases mentioned there. Also using year1 as mentioned 921 // produces incorrect results in other cases and different from 922 // Excel 2010. Apparently they fixed some calculations. 923 aDate1.Normalize(); 924 double fd = aDate2 - aDate1; 925 PushDouble( fd ); 926 } 927 else 928 PushIllegalArgument(); // unsupported format 929 } 930 } 931 932 void ScInterpreter::ScGetTimeValue() 933 { 934 OUString aInputString = GetString().getString(); 935 sal_uInt32 nFIndex = 0; // damit default Land/Spr. 936 double fVal; 937 if (pFormatter->IsNumberFormat(aInputString, nFIndex, fVal)) 938 { 939 SvNumFormatType eType = pFormatter->GetType(nFIndex); 940 if (eType == SvNumFormatType::TIME || eType == SvNumFormatType::DATETIME) 941 { 942 nFuncFmtType = SvNumFormatType::TIME; 943 double fDateVal = rtl::math::approxFloor(fVal); 944 double fTimeVal = fVal - fDateVal; 945 PushDouble(fTimeVal); 946 } 947 else 948 PushIllegalArgument(); 949 } 950 else 951 PushIllegalArgument(); 952 } 953 954 void ScInterpreter::ScPlusMinus() 955 { 956 double fVal = GetDouble(); 957 short n = 0; 958 if (fVal < 0.0) 959 n = -1; 960 else if (fVal > 0.0) 961 n = 1; 962 PushInt( n ); 963 } 964 965 void ScInterpreter::ScAbs() 966 { 967 PushDouble(fabs(GetDouble())); 968 } 969 970 void ScInterpreter::ScInt() 971 { 972 PushDouble(::rtl::math::approxFloor(GetDouble())); 973 } 974 975 void ScInterpreter::RoundNumber( rtl_math_RoundingMode eMode ) 976 { 977 sal_uInt8 nParamCount = GetByte(); 978 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 979 { 980 double fVal = 0.0; 981 if (nParamCount == 1) 982 fVal = ::rtl::math::round( GetDouble(), 0, eMode ); 983 else 984 { 985 sal_Int16 nDec = GetInt16(); 986 double fX = GetDouble(); 987 if ( nGlobalError != FormulaError::NONE || nDec < -20 || nDec > 20 ) 988 PushIllegalArgument(); 989 else 990 { 991 if ( ( eMode == rtl_math_RoundingMode_Down || 992 eMode == rtl_math_RoundingMode_Up ) && 993 nDec < 12 && fmod( fX, 1.0 ) != 0.0 ) 994 { 995 // tdf124286 : round to 12 significant digits before rounding 996 // down or up to avoid unexpected rounding errors 997 // caused by decimal -> binary -> decimal conversion 998 double fRes; 999 RoundSignificant( fX, 12, fRes ); 1000 fVal = ::rtl::math::round( fRes, nDec, eMode ); 1001 } 1002 else 1003 fVal = ::rtl::math::round( fX, nDec, eMode ); 1004 } 1005 } 1006 PushDouble(fVal); 1007 } 1008 } 1009 1010 void ScInterpreter::ScRound() 1011 { 1012 RoundNumber( rtl_math_RoundingMode_Corrected ); 1013 } 1014 1015 void ScInterpreter::ScRoundDown() 1016 { 1017 RoundNumber( rtl_math_RoundingMode_Down ); 1018 } 1019 1020 void ScInterpreter::ScRoundUp() 1021 { 1022 RoundNumber( rtl_math_RoundingMode_Up ); 1023 } 1024 1025 void ScInterpreter::RoundSignificant( double fX, double fDigits, double &fRes ) 1026 { 1027 bool bNegVal = ( fX < 0 ); 1028 if ( bNegVal ) 1029 fX *= -1.0; 1030 double fTemp = ::rtl::math::approxFloor( log10( fX ) ) + 1.0 - fDigits; 1031 fRes = ::rtl::math::round( pow(10.0, -fTemp ) * fX ) * pow( 10.0, fTemp ); 1032 if ( bNegVal ) 1033 fRes *= -1.0; 1034 } 1035 1036 // tdf#106931 1037 void ScInterpreter::ScRoundSignificant() 1038 { 1039 if ( MustHaveParamCount( GetByte(), 2 ) ) 1040 { 1041 double fDigits = ::rtl::math::approxFloor( GetDouble() ); 1042 double fX = GetDouble(); 1043 if ( nGlobalError != FormulaError::NONE || fDigits < 1.0 ) 1044 { 1045 PushIllegalArgument(); 1046 return; 1047 } 1048 1049 if ( fX == 0.0 ) 1050 PushDouble( 0.0 ); 1051 else 1052 { 1053 double fRes; 1054 RoundSignificant( fX, fDigits, fRes ); 1055 PushDouble( fRes ); 1056 } 1057 } 1058 } 1059 1060 /** tdf69552 ODFF1.2 function CEILING and Excel function CEILING.MATH 1061 In essence, the difference between the two is that ODFF-CEILING needs to 1062 have arguments value and significance of the same sign and with 1063 CEILING.MATH the sign of argument significance is irrevelevant. 1064 This is why ODFF-CEILING is exported to Excel as CEILING.MATH and 1065 CEILING.MATH is imported in Calc as CEILING.MATH 1066 */ 1067 void ScInterpreter::ScCeil( bool bODFF ) 1068 { 1069 sal_uInt8 nParamCount = GetByte(); 1070 if ( MustHaveParamCount( nParamCount, 1, 3 ) ) 1071 { 1072 bool bAbs = nParamCount == 3 && GetBool(); 1073 double fDec, fVal; 1074 if ( nParamCount == 1 ) 1075 { 1076 fVal = GetDouble(); 1077 fDec = ( fVal < 0 ? -1 : 1 ); 1078 } 1079 else 1080 { 1081 bool bArgumentMissing = IsMissing(); 1082 fDec = GetDouble(); 1083 fVal = GetDouble(); 1084 if ( bArgumentMissing ) 1085 fDec = ( fVal < 0 ? -1 : 1 ); 1086 } 1087 if ( fVal == 0 || fDec == 0.0 ) 1088 PushInt( 0 ); 1089 else 1090 { 1091 if ( bODFF && fVal * fDec < 0 ) 1092 PushIllegalArgument(); 1093 else 1094 { 1095 if ( fVal * fDec < 0.0 ) 1096 fDec = -fDec; 1097 1098 if ( !bAbs && fVal < 0.0 ) 1099 PushDouble(::rtl::math::approxFloor( fVal / fDec ) * fDec ); 1100 else 1101 PushDouble(::rtl::math::approxCeil( fVal / fDec ) * fDec ); 1102 } 1103 } 1104 } 1105 } 1106 1107 void ScInterpreter::ScCeil_MS() 1108 { 1109 sal_uInt8 nParamCount = GetByte(); 1110 if ( MustHaveParamCount( nParamCount, 2 ) ) 1111 { 1112 double fDec = GetDouble(); 1113 double fVal = GetDouble(); 1114 if ( fVal == 0 || fDec == 0.0 ) 1115 PushInt(0); 1116 else if ( fVal * fDec > 0 ) 1117 PushDouble(::rtl::math::approxCeil( fVal / fDec ) * fDec ); 1118 else if ( fVal < 0.0 ) 1119 PushDouble(::rtl::math::approxFloor( fVal / -fDec ) * -fDec ); 1120 else 1121 PushIllegalArgument(); 1122 } 1123 } 1124 1125 void ScInterpreter::ScCeil_Precise() 1126 { 1127 sal_uInt8 nParamCount = GetByte(); 1128 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 1129 { 1130 double fDec, fVal; 1131 if ( nParamCount == 1 ) 1132 { 1133 fVal = GetDouble(); 1134 fDec = 1.0; 1135 } 1136 else 1137 { 1138 fDec = fabs( GetDoubleWithDefault( 1.0 )); 1139 fVal = GetDouble(); 1140 } 1141 if ( fDec == 0.0 || fVal == 0.0 ) 1142 PushInt( 0 ); 1143 else 1144 PushDouble(::rtl::math::approxCeil( fVal / fDec ) * fDec ); 1145 } 1146 } 1147 1148 /** tdf69552 ODFF1.2 function FLOOR and Excel function FLOOR.MATH 1149 In essence, the difference between the two is that ODFF-FLOOR needs to 1150 have arguments value and significance of the same sign and with 1151 FLOOR.MATH the sign of argument significance is irrevelevant. 1152 This is why ODFF-FLOOR is exported to Excel as FLOOR.MATH and 1153 FLOOR.MATH is imported in Calc as FLOOR.MATH 1154 */ 1155 void ScInterpreter::ScFloor( bool bODFF ) 1156 { 1157 sal_uInt8 nParamCount = GetByte(); 1158 if ( MustHaveParamCount( nParamCount, 1, 3 ) ) 1159 { 1160 bool bAbs = ( nParamCount == 3 && GetBool() ); 1161 double fDec, fVal; 1162 if ( nParamCount == 1 ) 1163 { 1164 fVal = GetDouble(); 1165 fDec = ( fVal < 0 ? -1 : 1 ); 1166 } 1167 else 1168 { 1169 bool bArgumentMissing = IsMissing(); 1170 fDec = GetDouble(); 1171 fVal = GetDouble(); 1172 if ( bArgumentMissing ) 1173 fDec = ( fVal < 0 ? -1 : 1 ); 1174 } 1175 if ( fDec == 0.0 || fVal == 0.0 ) 1176 PushInt( 0 ); 1177 else 1178 { 1179 if ( bODFF && ( fVal * fDec < 0.0 ) ) 1180 PushIllegalArgument(); 1181 else 1182 { 1183 if ( fVal * fDec < 0.0 ) 1184 fDec = -fDec; 1185 1186 if ( !bAbs && fVal < 0.0 ) 1187 PushDouble(::rtl::math::approxCeil( fVal / fDec ) * fDec ); 1188 else 1189 PushDouble(::rtl::math::approxFloor( fVal / fDec ) * fDec ); 1190 } 1191 } 1192 } 1193 } 1194 1195 void ScInterpreter::ScFloor_MS() 1196 { 1197 sal_uInt8 nParamCount = GetByte(); 1198 if ( MustHaveParamCount( nParamCount, 2 ) ) 1199 { 1200 double fDec = GetDouble(); 1201 double fVal = GetDouble(); 1202 1203 if ( fVal == 0 ) 1204 PushInt( 0 ); 1205 else if ( fVal * fDec > 0 ) 1206 PushDouble(::rtl::math::approxFloor( fVal / fDec ) * fDec ); 1207 else if ( fDec == 0 ) 1208 PushIllegalArgument(); 1209 else if ( fVal < 0.0 ) 1210 PushDouble(::rtl::math::approxCeil( fVal / -fDec ) * -fDec ); 1211 else 1212 PushIllegalArgument(); 1213 } 1214 } 1215 1216 void ScInterpreter::ScFloor_Precise() 1217 { 1218 sal_uInt8 nParamCount = GetByte(); 1219 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 1220 { 1221 double fDec, fVal; 1222 if ( nParamCount == 1 ) 1223 { 1224 fVal = GetDouble(); 1225 fDec = 1.0; 1226 } 1227 else 1228 { 1229 fDec = fabs( GetDoubleWithDefault( 1.0 ) ); 1230 fVal = GetDouble(); 1231 } 1232 if ( fDec == 0.0 || fVal == 0.0 ) 1233 PushInt( 0 ); 1234 else 1235 PushDouble(::rtl::math::approxFloor( fVal / fDec ) * fDec ); 1236 } 1237 } 1238 1239 void ScInterpreter::ScEven() 1240 { 1241 double fVal = GetDouble(); 1242 if (fVal < 0.0) 1243 PushDouble(::rtl::math::approxFloor(fVal/2.0) * 2.0); 1244 else 1245 PushDouble(::rtl::math::approxCeil(fVal/2.0) * 2.0); 1246 } 1247 1248 void ScInterpreter::ScOdd() 1249 { 1250 double fVal = GetDouble(); 1251 if (fVal >= 0.0) 1252 { 1253 fVal = ::rtl::math::approxCeil(fVal); 1254 if (fmod(fVal, 2.0) == 0.0) 1255 fVal += 1.0; 1256 } 1257 else 1258 { 1259 fVal = ::rtl::math::approxFloor(fVal); 1260 if (fmod(fVal, 2.0) == 0.0) 1261 fVal -= 1.0; 1262 } 1263 PushDouble(fVal); 1264 } 1265 1266 void ScInterpreter::ScArcTan2() 1267 { 1268 if ( MustHaveParamCount( GetByte(), 2 ) ) 1269 { 1270 double fVal2 = GetDouble(); 1271 double fVal1 = GetDouble(); 1272 PushDouble(atan2(fVal2, fVal1)); 1273 } 1274 } 1275 1276 void ScInterpreter::ScLog() 1277 { 1278 sal_uInt8 nParamCount = GetByte(); 1279 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 1280 { 1281 double fBase; 1282 if (nParamCount == 2) 1283 fBase = GetDouble(); 1284 else 1285 fBase = 10.0; 1286 double fVal = GetDouble(); 1287 if (fVal > 0.0 && fBase > 0.0 && fBase != 1.0) 1288 PushDouble(log(fVal) / log(fBase)); 1289 else 1290 PushIllegalArgument(); 1291 } 1292 } 1293 1294 void ScInterpreter::ScLn() 1295 { 1296 double fVal = GetDouble(); 1297 if (fVal > 0.0) 1298 PushDouble(log(fVal)); 1299 else 1300 PushIllegalArgument(); 1301 } 1302 1303 void ScInterpreter::ScLog10() 1304 { 1305 double fVal = GetDouble(); 1306 if (fVal > 0.0) 1307 PushDouble(log10(fVal)); 1308 else 1309 PushIllegalArgument(); 1310 } 1311 1312 void ScInterpreter::ScNPV() 1313 { 1314 nFuncFmtType = SvNumFormatType::CURRENCY; 1315 short nParamCount = GetByte(); 1316 if ( MustHaveParamCountMin( nParamCount, 2) ) 1317 { 1318 double fVal = 0.0; 1319 // We turn the stack upside down! 1320 ReverseStack( nParamCount); 1321 if (nGlobalError == FormulaError::NONE) 1322 { 1323 double fCount = 1.0; 1324 double fRate = GetDouble(); 1325 --nParamCount; 1326 size_t nRefInList = 0; 1327 ScRange aRange; 1328 while (nParamCount-- > 0) 1329 { 1330 switch (GetStackType()) 1331 { 1332 case svDouble : 1333 { 1334 fVal += (GetDouble() / pow(1.0 + fRate, fCount)); 1335 fCount++; 1336 } 1337 break; 1338 case svSingleRef : 1339 { 1340 ScAddress aAdr; 1341 PopSingleRef( aAdr ); 1342 ScRefCellValue aCell(*pDok, aAdr); 1343 if (!aCell.hasEmptyValue() && aCell.hasNumeric()) 1344 { 1345 double fCellVal = GetCellValue(aAdr, aCell); 1346 fVal += (fCellVal / pow(1.0 + fRate, fCount)); 1347 fCount++; 1348 } 1349 } 1350 break; 1351 case svDoubleRef : 1352 case svRefList : 1353 { 1354 FormulaError nErr = FormulaError::NONE; 1355 double fCellVal; 1356 PopDoubleRef( aRange, nParamCount, nRefInList); 1357 ScHorizontalValueIterator aValIter( pDok, aRange ); 1358 while ((nErr == FormulaError::NONE) && aValIter.GetNext(fCellVal, nErr)) 1359 { 1360 fVal += (fCellVal / pow(1.0 + fRate, fCount)); 1361 fCount++; 1362 } 1363 if ( nErr != FormulaError::NONE ) 1364 SetError(nErr); 1365 } 1366 break; 1367 case svMatrix : 1368 case svExternalSingleRef: 1369 case svExternalDoubleRef: 1370 { 1371 ScMatrixRef pMat = GetMatrix(); 1372 if (pMat) 1373 { 1374 SCSIZE nC, nR; 1375 pMat->GetDimensions(nC, nR); 1376 if (nC == 0 || nR == 0) 1377 { 1378 PushIllegalArgument(); 1379 return; 1380 } 1381 else 1382 { 1383 double fx; 1384 for ( SCSIZE j = 0; j < nC; j++ ) 1385 { 1386 for (SCSIZE k = 0; k < nR; ++k) 1387 { 1388 if (!pMat->IsValue(j,k)) 1389 { 1390 PushIllegalArgument(); 1391 return; 1392 } 1393 fx = pMat->GetDouble(j,k); 1394 fVal += (fx / pow(1.0 + fRate, fCount)); 1395 fCount++; 1396 } 1397 } 1398 } 1399 } 1400 } 1401 break; 1402 default : SetError(FormulaError::IllegalParameter); break; 1403 } 1404 } 1405 } 1406 PushDouble(fVal); 1407 } 1408 } 1409 1410 void ScInterpreter::ScIRR() 1411 { 1412 double fEstimated; 1413 nFuncFmtType = SvNumFormatType::PERCENT; 1414 sal_uInt8 nParamCount = GetByte(); 1415 if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) 1416 return; 1417 if (nParamCount == 2) 1418 fEstimated = GetDouble(); 1419 else 1420 fEstimated = 0.1; 1421 sal_uInt16 sPos = sp; // memorize the position of the stack 1422 double fEps = 1.0; 1423 double x, fValue; 1424 if (fEstimated == -1.0) 1425 x = 0.1; // default result for division by zero 1426 else 1427 x = fEstimated; // startvalue 1428 switch (GetStackType()) 1429 { 1430 case svDoubleRef : 1431 break; 1432 default: 1433 { 1434 PushIllegalParameter(); 1435 return; 1436 } 1437 } 1438 const sal_uInt16 nIterationsMax = 20; 1439 sal_uInt16 nItCount = 0; 1440 ScRange aRange; 1441 while (fEps > SCdEpsilon && nItCount < nIterationsMax) 1442 { // Newtons method: 1443 sp = sPos; // reset stack 1444 double fNom = 0.0; 1445 double fDenom = 0.0; 1446 FormulaError nErr = FormulaError::NONE; 1447 PopDoubleRef( aRange ); 1448 ScValueIterator aValIter(pDok, aRange, mnSubTotalFlags); 1449 if (aValIter.GetFirst(fValue, nErr)) 1450 { 1451 double fCount = 0.0; 1452 fNom += fValue / pow(1.0+x,fCount); 1453 fDenom += -fCount * fValue / pow(1.0+x,fCount+1.0); 1454 fCount++; 1455 while ((nErr == FormulaError::NONE) && aValIter.GetNext(fValue, nErr)) 1456 { 1457 fNom += fValue / pow(1.0+x,fCount); 1458 fDenom += -fCount * fValue / pow(1.0+x,fCount+1.0); 1459 fCount++; 1460 } 1461 SetError(nErr); 1462 } 1463 double xNew = x - fNom / fDenom; // x(i+1) = x(i)-f(x(i))/f'(x(i)) 1464 nItCount++; 1465 fEps = fabs(xNew - x); 1466 x = xNew; 1467 } 1468 if (fEstimated == 0.0 && fabs(x) < SCdEpsilon) 1469 x = 0.0; // adjust to zero 1470 if (fEps < SCdEpsilon) 1471 PushDouble(x); 1472 else 1473 PushError( FormulaError::NoConvergence); 1474 } 1475 1476 void ScInterpreter::ScMIRR() 1477 { // range_of_values ; rate_invest ; rate_reinvest 1478 nFuncFmtType = SvNumFormatType::PERCENT; 1479 if ( MustHaveParamCount( GetByte(), 3 ) ) 1480 { 1481 double fRate1_reinvest = GetDouble() + 1; 1482 double fRate1_invest = GetDouble() + 1; 1483 1484 ScRange aRange; 1485 ScMatrixRef pMat; 1486 SCSIZE nC = 0; 1487 SCSIZE nR = 0; 1488 bool bIsMatrix = false; 1489 switch ( GetStackType() ) 1490 { 1491 case svDoubleRef : 1492 PopDoubleRef( aRange ); 1493 break; 1494 case svMatrix : 1495 case svExternalSingleRef: 1496 case svExternalDoubleRef: 1497 { 1498 pMat = GetMatrix(); 1499 if ( pMat ) 1500 { 1501 pMat->GetDimensions( nC, nR ); 1502 if ( nC == 0 || nR == 0 ) 1503 SetError( FormulaError::IllegalArgument ); 1504 bIsMatrix = true; 1505 } 1506 else 1507 SetError( FormulaError::IllegalArgument ); 1508 } 1509 break; 1510 default : 1511 SetError( FormulaError::IllegalParameter ); 1512 break; 1513 } 1514 1515 if ( nGlobalError != FormulaError::NONE ) 1516 PushError( nGlobalError ); 1517 else 1518 { 1519 double fNPV_reinvest = 0.0; 1520 double fPow_reinvest = 1.0; 1521 double fNPV_invest = 0.0; 1522 double fPow_invest = 1.0; 1523 sal_uLong nCount = 0; 1524 bool bHasPosValue = false; 1525 bool bHasNegValue = false; 1526 1527 if ( bIsMatrix ) 1528 { 1529 double fX; 1530 for ( SCSIZE j = 0; j < nC; j++ ) 1531 { 1532 for ( SCSIZE k = 0; k < nR; ++k ) 1533 { 1534 if ( !pMat->IsValue( j, k ) ) 1535 continue; 1536 fX = pMat->GetDouble( j, k ); 1537 if ( nGlobalError != FormulaError::NONE ) 1538 break; 1539 1540 if ( fX > 0.0 ) 1541 { // reinvestments 1542 bHasPosValue = true; 1543 fNPV_reinvest += fX * fPow_reinvest; 1544 } 1545 else if ( fX < 0.0 ) 1546 { // investments 1547 bHasNegValue = true; 1548 fNPV_invest += fX * fPow_invest; 1549 } 1550 fPow_reinvest /= fRate1_reinvest; 1551 fPow_invest /= fRate1_invest; 1552 nCount++; 1553 } 1554 } 1555 } 1556 else 1557 { 1558 ScValueIterator aValIter( pDok, aRange, mnSubTotalFlags ); 1559 double fCellValue; 1560 FormulaError nIterError = FormulaError::NONE; 1561 1562 bool bLoop = aValIter.GetFirst( fCellValue, nIterError ); 1563 while( bLoop ) 1564 { 1565 if( fCellValue > 0.0 ) // reinvestments 1566 { // reinvestments 1567 bHasPosValue = true; 1568 fNPV_reinvest += fCellValue * fPow_reinvest; 1569 } 1570 else if( fCellValue < 0.0 ) // investments 1571 { // investments 1572 bHasNegValue = true; 1573 fNPV_invest += fCellValue * fPow_invest; 1574 } 1575 fPow_reinvest /= fRate1_reinvest; 1576 fPow_invest /= fRate1_invest; 1577 nCount++; 1578 1579 bLoop = aValIter.GetNext( fCellValue, nIterError ); 1580 } 1581 1582 if ( nIterError != FormulaError::NONE ) 1583 SetError( nIterError ); 1584 } 1585 if ( !( bHasPosValue && bHasNegValue ) ) 1586 SetError( FormulaError::IllegalArgument ); 1587 1588 if ( nGlobalError != FormulaError::NONE ) 1589 PushError( nGlobalError ); 1590 else 1591 { 1592 double fResult = -fNPV_reinvest / fNPV_invest; 1593 fResult *= pow( fRate1_reinvest, static_cast<double>( nCount - 1 ) ); 1594 fResult = pow( fResult, div( 1.0, (nCount - 1)) ); 1595 PushDouble( fResult - 1.0 ); 1596 } 1597 } 1598 } 1599 } 1600 1601 void ScInterpreter::ScISPMT() 1602 { // rate ; period ; total_periods ; invest 1603 if( MustHaveParamCount( GetByte(), 4 ) ) 1604 { 1605 double fInvest = GetDouble(); 1606 double fTotal = GetDouble(); 1607 double fPeriod = GetDouble(); 1608 double fRate = GetDouble(); 1609 1610 if( nGlobalError != FormulaError::NONE ) 1611 PushError( nGlobalError); 1612 else 1613 PushDouble( fInvest * fRate * (fPeriod / fTotal - 1.0) ); 1614 } 1615 } 1616 1617 // financial functions 1618 double ScInterpreter::ScGetPV(double fRate, double fNper, double fPmt, 1619 double fFv, bool bPayInAdvance) 1620 { 1621 double fPv; 1622 if (fRate == 0.0) 1623 fPv = fFv + fPmt * fNper; 1624 else 1625 { 1626 if (bPayInAdvance) 1627 fPv = (fFv * pow(1.0 + fRate, -fNper)) 1628 + (fPmt * (1.0 - pow(1.0 + fRate, -fNper + 1.0)) / fRate) 1629 + fPmt; 1630 else 1631 fPv = (fFv * pow(1.0 + fRate, -fNper)) 1632 + (fPmt * (1.0 - pow(1.0 + fRate, -fNper)) / fRate); 1633 } 1634 return -fPv; 1635 } 1636 1637 void ScInterpreter::ScPV() 1638 { 1639 nFuncFmtType = SvNumFormatType::CURRENCY; 1640 double fPmt, fNper, fRate, fFv = 0; 1641 bool bPayInAdvance = false; 1642 sal_uInt8 nParamCount = GetByte(); 1643 if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) 1644 return; 1645 if (nParamCount == 5) 1646 bPayInAdvance = GetBool(); 1647 if (nParamCount >= 4) 1648 fFv = GetDouble(); 1649 fPmt = GetDouble(); 1650 fNper = GetDouble(); 1651 fRate = GetDouble(); 1652 PushDouble(ScGetPV(fRate, fNper, fPmt, fFv, bPayInAdvance)); 1653 } 1654 1655 void ScInterpreter::ScSYD() 1656 { 1657 nFuncFmtType = SvNumFormatType::CURRENCY; 1658 if ( MustHaveParamCount( GetByte(), 4 ) ) 1659 { 1660 double fPer = GetDouble(); 1661 double fLife = GetDouble(); 1662 double fSalvage = GetDouble(); 1663 double fCost = GetDouble(); 1664 double fSyd = ((fCost - fSalvage) * (fLife - fPer + 1.0)) / 1665 ((fLife * (fLife + 1.0)) / 2.0); 1666 PushDouble(fSyd); 1667 } 1668 } 1669 1670 double ScInterpreter::ScGetDDB(double fCost, double fSalvage, double fLife, 1671 double fPeriod, double fFactor) 1672 { 1673 double fDdb, fRate, fOldValue, fNewValue; 1674 fRate = fFactor / fLife; 1675 if (fRate >= 1.0) 1676 { 1677 fRate = 1.0; 1678 if (fPeriod == 1.0) 1679 fOldValue = fCost; 1680 else 1681 fOldValue = 0.0; 1682 } 1683 else 1684 fOldValue = fCost * pow(1.0 - fRate, fPeriod - 1.0); 1685 fNewValue = fCost * pow(1.0 - fRate, fPeriod); 1686 1687 if (fNewValue < fSalvage) 1688 fDdb = fOldValue - fSalvage; 1689 else 1690 fDdb = fOldValue - fNewValue; 1691 if (fDdb < 0.0) 1692 fDdb = 0.0; 1693 return fDdb; 1694 } 1695 1696 void ScInterpreter::ScDDB() 1697 { 1698 nFuncFmtType = SvNumFormatType::CURRENCY; 1699 sal_uInt8 nParamCount = GetByte(); 1700 if ( MustHaveParamCount( nParamCount, 4, 5 ) ) 1701 { 1702 double fFactor; 1703 if (nParamCount == 5) 1704 fFactor = GetDouble(); 1705 else 1706 fFactor = 2.0; 1707 double fPeriod = GetDouble(); 1708 double fLife = GetDouble(); 1709 double fSalvage = GetDouble(); 1710 double fCost = GetDouble(); 1711 if (fCost < 0.0 || fSalvage < 0.0 || fFactor <= 0.0 || fSalvage > fCost 1712 || fPeriod < 1.0 || fPeriod > fLife) 1713 PushIllegalArgument(); 1714 else 1715 PushDouble(ScGetDDB(fCost, fSalvage, fLife, fPeriod, fFactor)); 1716 } 1717 } 1718 1719 void ScInterpreter::ScDB() 1720 { 1721 nFuncFmtType = SvNumFormatType::CURRENCY; 1722 sal_uInt8 nParamCount = GetByte(); 1723 if ( !MustHaveParamCount( nParamCount, 4, 5 ) ) 1724 return ; 1725 double fMonths; 1726 if (nParamCount == 4) 1727 fMonths = 12.0; 1728 else 1729 fMonths = ::rtl::math::approxFloor(GetDouble()); 1730 double fPeriod = GetDouble(); 1731 double fLife = GetDouble(); 1732 double fSalvage = GetDouble(); 1733 double fCost = GetDouble(); 1734 if (fMonths < 1.0 || fMonths > 12.0 || fLife > 1200.0 || fSalvage < 0.0 || 1735 fPeriod > (fLife + 1.0) || fSalvage > fCost || fCost <= 0.0 || 1736 fLife <= 0 || fPeriod <= 0 ) 1737 { 1738 PushIllegalArgument(); 1739 return; 1740 } 1741 double fOffRate = 1.0 - pow(fSalvage / fCost, 1.0 / fLife); 1742 fOffRate = ::rtl::math::approxFloor((fOffRate * 1000.0) + 0.5) / 1000.0; 1743 double fFirstOffRate = fCost * fOffRate * fMonths / 12.0; 1744 double fDb = 0.0; 1745 if (::rtl::math::approxFloor(fPeriod) == 1) 1746 fDb = fFirstOffRate; 1747 else 1748 { 1749 double fSumOffRate = fFirstOffRate; 1750 double fMin = fLife; 1751 if (fMin > fPeriod) fMin = fPeriod; 1752 sal_uInt16 iMax = static_cast<sal_uInt16>(::rtl::math::approxFloor(fMin)); 1753 for (sal_uInt16 i = 2; i <= iMax; i++) 1754 { 1755 fDb = (fCost - fSumOffRate) * fOffRate; 1756 fSumOffRate += fDb; 1757 } 1758 if (fPeriod > fLife) 1759 fDb = ((fCost - fSumOffRate) * fOffRate * (12.0 - fMonths)) / 12.0; 1760 } 1761 PushDouble(fDb); 1762 } 1763 1764 double ScInterpreter::ScInterVDB(double fCost, double fSalvage, double fLife, 1765 double fLife1, double fPeriod, double fFactor) 1766 { 1767 double fVdb=0; 1768 double fIntEnd = ::rtl::math::approxCeil(fPeriod); 1769 sal_uLong nLoopEnd = static_cast<sal_uLong>(fIntEnd); 1770 1771 double fTerm, fSln; // SLN: Straight-Line Depreciation 1772 double fSalvageValue = fCost - fSalvage; 1773 bool bNowSln = false; 1774 1775 double fDdb; 1776 sal_uLong i; 1777 fSln=0; 1778 for ( i = 1; i <= nLoopEnd; i++) 1779 { 1780 if(!bNowSln) 1781 { 1782 fDdb = ScGetDDB(fCost, fSalvage, fLife, static_cast<double>(i), fFactor); 1783 fSln = fSalvageValue/ (fLife1 - static_cast<double>(i-1)); 1784 1785 if (fSln > fDdb) 1786 { 1787 fTerm = fSln; 1788 bNowSln = true; 1789 } 1790 else 1791 { 1792 fTerm = fDdb; 1793 fSalvageValue -= fDdb; 1794 } 1795 } 1796 else 1797 { 1798 fTerm = fSln; 1799 } 1800 1801 if ( i == nLoopEnd) 1802 fTerm *= ( fPeriod + 1.0 - fIntEnd ); 1803 1804 fVdb += fTerm; 1805 } 1806 return fVdb; 1807 } 1808 1809 void ScInterpreter::ScVDB() 1810 { 1811 nFuncFmtType = SvNumFormatType::CURRENCY; 1812 sal_uInt8 nParamCount = GetByte(); 1813 if ( MustHaveParamCount( nParamCount, 5, 7 ) ) 1814 { 1815 double fCost, fSalvage, fLife, fStart, fEnd, fFactor, fVdb = 0.0; 1816 bool bNoSwitch; 1817 if (nParamCount == 7) 1818 bNoSwitch = GetBool(); 1819 else 1820 bNoSwitch = false; 1821 if (nParamCount >= 6) 1822 fFactor = GetDouble(); 1823 else 1824 fFactor = 2.0; 1825 fEnd = GetDouble(); 1826 fStart = GetDouble(); 1827 fLife = GetDouble(); 1828 fSalvage = GetDouble(); 1829 fCost = GetDouble(); 1830 if (fStart < 0.0 || fEnd < fStart || fEnd > fLife || fCost < 0.0 1831 || fSalvage > fCost || fFactor <= 0.0) 1832 PushIllegalArgument(); 1833 else 1834 { 1835 double fIntStart = ::rtl::math::approxFloor(fStart); 1836 double fIntEnd = ::rtl::math::approxCeil(fEnd); 1837 sal_uLong nLoopStart = static_cast<sal_uLong>(fIntStart); 1838 sal_uLong nLoopEnd = static_cast<sal_uLong>(fIntEnd); 1839 1840 fVdb = 0.0; 1841 if (bNoSwitch) 1842 { 1843 for (sal_uLong i = nLoopStart + 1; i <= nLoopEnd; i++) 1844 { 1845 double fTerm = ScGetDDB(fCost, fSalvage, fLife, static_cast<double>(i), fFactor); 1846 1847 //respect partial period in the Beginning/ End: 1848 if ( i == nLoopStart+1 ) 1849 fTerm *= ( std::min( fEnd, fIntStart + 1.0 ) - fStart ); 1850 else if ( i == nLoopEnd ) 1851 fTerm *= ( fEnd + 1.0 - fIntEnd ); 1852 1853 fVdb += fTerm; 1854 } 1855 } 1856 else 1857 { 1858 double fPart = 0.0; 1859 // respect partial period in the Beginning / End: 1860 if ( !::rtl::math::approxEqual( fStart, fIntStart ) || 1861 !::rtl::math::approxEqual( fEnd, fIntEnd ) ) 1862 { 1863 if ( !::rtl::math::approxEqual( fStart, fIntStart ) ) 1864 { 1865 // part to be subtracted at the beginning 1866 double fTempIntEnd = fIntStart + 1.0; 1867 double fTempValue = fCost - 1868 ScInterVDB( fCost, fSalvage, fLife, fLife, fIntStart, fFactor ); 1869 fPart += ( fStart - fIntStart ) * 1870 ScInterVDB( fTempValue, fSalvage, fLife, fLife - fIntStart, 1871 fTempIntEnd - fIntStart, fFactor); 1872 } 1873 if ( !::rtl::math::approxEqual( fEnd, fIntEnd ) ) 1874 { 1875 // part to be subtracted at the end 1876 double fTempIntStart = fIntEnd - 1.0; 1877 double fTempValue = fCost - 1878 ScInterVDB( fCost, fSalvage, fLife, fLife, fTempIntStart, fFactor ); 1879 fPart += ( fIntEnd - fEnd ) * 1880 ScInterVDB( fTempValue, fSalvage, fLife, fLife - fTempIntStart, 1881 fIntEnd - fTempIntStart, fFactor); 1882 } 1883 } 1884 // calculate depreciation for whole periods 1885 fCost -= ScInterVDB( fCost, fSalvage, fLife, fLife, fIntStart, fFactor ); 1886 fVdb = ScInterVDB( fCost, fSalvage, fLife, fLife - fIntStart, 1887 fIntEnd - fIntStart, fFactor); 1888 fVdb -= fPart; 1889 } 1890 } 1891 PushDouble(fVdb); 1892 } 1893 } 1894 1895 void ScInterpreter::ScPDuration() 1896 { 1897 if ( MustHaveParamCount( GetByte(), 3 ) ) 1898 { 1899 double fFuture = GetDouble(); 1900 double fPresent = GetDouble(); 1901 double fRate = GetDouble(); 1902 if ( fFuture <= 0.0 || fPresent <= 0.0 || fRate <= 0.0 ) 1903 PushIllegalArgument(); 1904 else 1905 PushDouble( log( fFuture / fPresent ) / rtl::math::log1p( fRate ) ); 1906 } 1907 } 1908 1909 void ScInterpreter::ScSLN() 1910 { 1911 nFuncFmtType = SvNumFormatType::CURRENCY; 1912 if ( MustHaveParamCount( GetByte(), 3 ) ) 1913 { 1914 double fLife = GetDouble(); 1915 double fSalvage = GetDouble(); 1916 double fCost = GetDouble(); 1917 PushDouble( div( fCost - fSalvage, fLife ) ); 1918 } 1919 } 1920 1921 double ScInterpreter::ScGetPMT(double fRate, double fNper, double fPv, 1922 double fFv, bool bPayInAdvance) 1923 { 1924 double fPayment; 1925 if (fRate == 0.0) 1926 fPayment = (fPv + fFv) / fNper; 1927 else 1928 { 1929 if (bPayInAdvance) // payment in advance 1930 fPayment = (fFv + fPv * exp( fNper * ::rtl::math::log1p(fRate) ) ) * fRate / 1931 (::rtl::math::expm1( (fNper + 1) * ::rtl::math::log1p(fRate) ) - fRate); 1932 else // payment in arrear 1933 fPayment = (fFv + fPv * exp(fNper * ::rtl::math::log1p(fRate) ) ) * fRate / 1934 ::rtl::math::expm1( fNper * ::rtl::math::log1p(fRate) ); 1935 } 1936 return -fPayment; 1937 } 1938 1939 void ScInterpreter::ScPMT() 1940 { 1941 double fRate, fNper, fPv, fFv = 0; 1942 bool bPayInAdvance = false; 1943 nFuncFmtType = SvNumFormatType::CURRENCY; 1944 sal_uInt8 nParamCount = GetByte(); 1945 if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) 1946 return; 1947 if (nParamCount == 5) 1948 bPayInAdvance = GetBool(); 1949 if (nParamCount >= 4) 1950 fFv = GetDouble(); 1951 fPv = GetDouble(); 1952 fNper = GetDouble(); 1953 fRate = GetDouble(); 1954 PushDouble(ScGetPMT(fRate, fNper, fPv, fFv, bPayInAdvance)); 1955 } 1956 1957 void ScInterpreter::ScRRI() 1958 { 1959 nFuncFmtType = SvNumFormatType::PERCENT; 1960 if ( MustHaveParamCount( GetByte(), 3 ) ) 1961 { 1962 double fFutureValue = GetDouble(); 1963 double fPresentValue = GetDouble(); 1964 double fNrOfPeriods = GetDouble(); 1965 if ( fNrOfPeriods <= 0.0 || fPresentValue == 0.0 ) 1966 PushIllegalArgument(); 1967 else 1968 PushDouble(pow(fFutureValue / fPresentValue, 1.0 / fNrOfPeriods) - 1.0); 1969 } 1970 } 1971 1972 double ScInterpreter::ScGetFV(double fRate, double fNper, double fPmt, 1973 double fPv, bool bPayInAdvance) 1974 { 1975 double fFv; 1976 if (fRate == 0.0) 1977 fFv = fPv + fPmt * fNper; 1978 else 1979 { 1980 double fTerm = pow(1.0 + fRate, fNper); 1981 if (bPayInAdvance) 1982 fFv = fPv * fTerm + fPmt*(1.0 + fRate)*(fTerm - 1.0)/fRate; 1983 else 1984 fFv = fPv * fTerm + fPmt*(fTerm - 1.0)/fRate; 1985 } 1986 return -fFv; 1987 } 1988 1989 void ScInterpreter::ScFV() 1990 { 1991 double fRate, fNper, fPmt, fPv = 0; 1992 bool bPayInAdvance = false; 1993 nFuncFmtType = SvNumFormatType::CURRENCY; 1994 sal_uInt8 nParamCount = GetByte(); 1995 if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) 1996 return; 1997 if (nParamCount == 5) 1998 bPayInAdvance = GetBool(); 1999 if (nParamCount >= 4) 2000 fPv = GetDouble(); 2001 fPmt = GetDouble(); 2002 fNper = GetDouble(); 2003 fRate = GetDouble(); 2004 PushDouble(ScGetFV(fRate, fNper, fPmt, fPv, bPayInAdvance)); 2005 } 2006 2007 void ScInterpreter::ScNper() 2008 { 2009 double fRate, fPmt, fPV, fFV = 0; 2010 bool bPayInAdvance = false; 2011 sal_uInt8 nParamCount = GetByte(); 2012 if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) 2013 return; 2014 if (nParamCount == 5) 2015 bPayInAdvance = GetBool(); 2016 if (nParamCount >= 4) 2017 fFV = GetDouble(); // Future Value 2018 fPV = GetDouble(); // Present Value 2019 fPmt = GetDouble(); // Payment 2020 fRate = GetDouble(); 2021 // Note that due to the function specification in ODFF1.2 (and Excel) the 2022 // amount to be paid to get from fPV to fFV is fFV_+_fPV. 2023 if ( fPV + fFV == 0.0 ) 2024 PushDouble( 0.0 ); 2025 else if (fRate == 0.0) 2026 PushDouble(-(fPV + fFV)/fPmt); 2027 else if (bPayInAdvance) 2028 PushDouble(log(-(fRate*fFV-fPmt*(1.0+fRate))/(fRate*fPV+fPmt*(1.0+fRate))) 2029 / rtl::math::log1p(fRate)); 2030 else 2031 PushDouble(log(-(fRate*fFV-fPmt)/(fRate*fPV+fPmt)) / rtl::math::log1p(fRate)); 2032 } 2033 2034 bool ScInterpreter::RateIteration( double fNper, double fPayment, double fPv, 2035 double fFv, bool bPayType, double & fGuess ) 2036 { 2037 // See also #i15090# 2038 // Newton-Raphson method: x(i+1) = x(i) - f(x(i)) / f'(x(i)) 2039 // This solution handles integer and non-integer values of Nper different. 2040 // If ODFF will constraint Nper to integer, the distinction of cases can be 2041 // removed; only the integer-part is needed then. 2042 bool bValid = true, bFound = false; 2043 double fX, fXnew, fTerm, fTermDerivation; 2044 double fGeoSeries, fGeoSeriesDerivation; 2045 const sal_uInt16 nIterationsMax = 150; 2046 sal_uInt16 nCount = 0; 2047 const double fEpsilonSmall = 1.0E-14; 2048 if ( bPayType ) 2049 { 2050 // payment at beginning of each period 2051 fFv = fFv - fPayment; 2052 fPv = fPv + fPayment; 2053 } 2054 if (fNper == ::rtl::math::round( fNper )) 2055 { // Nper is an integer value 2056 fX = fGuess; 2057 while (!bFound && nCount < nIterationsMax) 2058 { 2059 double fPowN, fPowNminus1; // for (1.0+fX)^Nper and (1.0+fX)^(Nper-1) 2060 fPowNminus1 = pow( 1.0+fX, fNper-1.0); 2061 fPowN = fPowNminus1 * (1.0+fX); 2062 if (fX == 0.0) 2063 { 2064 fGeoSeries = fNper; 2065 fGeoSeriesDerivation = fNper * (fNper-1.0)/2.0; 2066 } 2067 else 2068 { 2069 fGeoSeries = (fPowN-1.0)/fX; 2070 fGeoSeriesDerivation = fNper * fPowNminus1 / fX - fGeoSeries / fX; 2071 } 2072 fTerm = fFv + fPv *fPowN+ fPayment * fGeoSeries; 2073 fTermDerivation = fPv * fNper * fPowNminus1 + fPayment * fGeoSeriesDerivation; 2074 if (fabs(fTerm) < fEpsilonSmall) 2075 bFound = true; // will catch root which is at an extreme 2076 else 2077 { 2078 if (fTermDerivation == 0.0) 2079 fXnew = fX + 1.1 * SCdEpsilon; // move away from zero slope 2080 else 2081 fXnew = fX - fTerm / fTermDerivation; 2082 nCount++; 2083 // more accuracy not possible in oscillating cases 2084 bFound = (fabs(fXnew - fX) < SCdEpsilon); 2085 fX = fXnew; 2086 } 2087 } 2088 // Gnumeric returns roots < -1, Excel gives an error in that cases, 2089 // ODFF says nothing about it. Enable the statement, if you want Excel's 2090 // behavior. 2091 //bValid =(fX >=-1.0); 2092 // Update 2013-06-17: Gnumeric (v1.12.2) doesn't return roots <= -1 2093 // anymore. 2094 bValid = (fX > -1.0); 2095 } 2096 else 2097 { // Nper is not an integer value. 2098 fX = (fGuess < -1.0) ? -1.0 : fGuess; // start with a valid fX 2099 while (bValid && !bFound && nCount < nIterationsMax) 2100 { 2101 if (fX == 0.0) 2102 { 2103 fGeoSeries = fNper; 2104 fGeoSeriesDerivation = fNper * (fNper-1.0)/2.0; 2105 } 2106 else 2107 { 2108 fGeoSeries = (pow( 1.0+fX, fNper) - 1.0) / fX; 2109 fGeoSeriesDerivation = fNper * pow( 1.0+fX, fNper-1.0) / fX - fGeoSeries / fX; 2110 } 2111 fTerm = fFv + fPv *pow(1.0 + fX,fNper)+ fPayment * fGeoSeries; 2112 fTermDerivation = fPv * fNper * pow( 1.0+fX, fNper-1.0) + fPayment * fGeoSeriesDerivation; 2113 if (fabs(fTerm) < fEpsilonSmall) 2114 bFound = true; // will catch root which is at an extreme 2115 else 2116 { 2117 if (fTermDerivation == 0.0) 2118 fXnew = fX + 1.1 * SCdEpsilon; // move away from zero slope 2119 else 2120 fXnew = fX - fTerm / fTermDerivation; 2121 nCount++; 2122 // more accuracy not possible in oscillating cases 2123 bFound = (fabs(fXnew - fX) < SCdEpsilon); 2124 fX = fXnew; 2125 bValid = (fX >= -1.0); // otherwise pow(1.0+fX,fNper) will fail 2126 } 2127 } 2128 } 2129 fGuess = fX; // return approximate root 2130 return bValid && bFound; 2131 } 2132 2133 // In Calc UI it is the function RATE(Nper;Pmt;Pv;Fv;Type;Guess) 2134 void ScInterpreter::ScRate() 2135 { 2136 double fPv, fPayment, fNper; 2137 // defaults for missing arguments, see ODFF spec 2138 double fFv = 0, fGuess = 0.1, fOrigGuess = 0.1; 2139 bool bPayType = false, bValid = true; 2140 bool bDefaultGuess = true; 2141 nFuncFmtType = SvNumFormatType::PERCENT; 2142 sal_uInt8 nParamCount = GetByte(); 2143 if ( !MustHaveParamCount( nParamCount, 3, 6 ) ) 2144 return; 2145 if (nParamCount == 6) 2146 { 2147 fOrigGuess = fGuess = GetDouble(); 2148 bDefaultGuess = false; 2149 } 2150 if (nParamCount >= 5) 2151 bPayType = GetBool(); 2152 if (nParamCount >= 4) 2153 fFv = GetDouble(); 2154 fPv = GetDouble(); 2155 fPayment = GetDouble(); 2156 fNper = GetDouble(); 2157 if (fNper <= 0.0) // constraint from ODFF spec 2158 { 2159 PushIllegalArgument(); 2160 return; 2161 } 2162 bValid = RateIteration(fNper, fPayment, fPv, fFv, bPayType, fGuess); 2163 if (!bValid) 2164 { 2165 /* TODO: try also for specified guess values, not only default? As is, 2166 * a specified 0.1 guess may be error result but a default 0.1 guess 2167 * may succeed. On the other hand, using a different guess value than 2168 * the specified one may not be desired, even if that didn't match. */ 2169 if (bDefaultGuess) 2170 { 2171 /* TODO: this is rather ugly, instead of looping over different 2172 * guess values and doing a Newton goal seek for each we could 2173 * first insert the values into the RATE equation to obtain a set 2174 * of y values and then do a bisecting goal seek, possibly using 2175 * different algorithms. */ 2176 double fX = fOrigGuess; 2177 for (int nStep = 2; nStep <= 10 && !bValid; ++nStep) 2178 { 2179 fGuess = fX * nStep; 2180 bValid = RateIteration( fNper, fPayment, fPv, fFv, bPayType, fGuess); 2181 if (!bValid) 2182 { 2183 fGuess = fX / nStep; 2184 bValid = RateIteration( fNper, fPayment, fPv, fFv, bPayType, fGuess); 2185 } 2186 } 2187 } 2188 if (!bValid) 2189 SetError(FormulaError::NoConvergence); 2190 } 2191 PushDouble(fGuess); 2192 } 2193 2194 double ScInterpreter::ScGetIpmt(double fRate, double fPer, double fNper, double fPv, 2195 double fFv, bool bPayInAdvance, double& fPmt) 2196 { 2197 fPmt = ScGetPMT(fRate, fNper, fPv, fFv, bPayInAdvance); // for PPMT also if fPer == 1 2198 double fIpmt; 2199 nFuncFmtType = SvNumFormatType::CURRENCY; 2200 if (fPer == 1.0) 2201 { 2202 if (bPayInAdvance) 2203 fIpmt = 0.0; 2204 else 2205 fIpmt = -fPv; 2206 } 2207 else 2208 { 2209 if (bPayInAdvance) 2210 fIpmt = ScGetFV(fRate, fPer-2.0, fPmt, fPv, true) - fPmt; 2211 else 2212 fIpmt = ScGetFV(fRate, fPer-1.0, fPmt, fPv, false); 2213 } 2214 return fIpmt * fRate; 2215 } 2216 2217 void ScInterpreter::ScIpmt() 2218 { 2219 double fRate, fPer, fNper, fPv, fFv = 0; 2220 bool bPayInAdvance = false; 2221 nFuncFmtType = SvNumFormatType::CURRENCY; 2222 sal_uInt8 nParamCount = GetByte(); 2223 if ( !MustHaveParamCount( nParamCount, 4, 6 ) ) 2224 return; 2225 if (nParamCount == 6) 2226 bPayInAdvance = GetBool(); 2227 if (nParamCount >= 5) 2228 fFv = GetDouble(); 2229 fPv = GetDouble(); 2230 fNper = GetDouble(); 2231 fPer = GetDouble(); 2232 fRate = GetDouble(); 2233 if (fPer < 1.0 || fPer > fNper) 2234 PushIllegalArgument(); 2235 else 2236 { 2237 double fPmt; 2238 PushDouble(ScGetIpmt(fRate, fPer, fNper, fPv, fFv, bPayInAdvance, fPmt)); 2239 } 2240 } 2241 2242 void ScInterpreter::ScPpmt() 2243 { 2244 double fRate, fPer, fNper, fPv, fFv = 0; 2245 bool bPayInAdvance = false; 2246 nFuncFmtType = SvNumFormatType::CURRENCY; 2247 sal_uInt8 nParamCount = GetByte(); 2248 if ( !MustHaveParamCount( nParamCount, 4, 6 ) ) 2249 return; 2250 if (nParamCount == 6) 2251 bPayInAdvance = GetBool(); 2252 if (nParamCount >= 5) 2253 fFv = GetDouble(); 2254 fPv = GetDouble(); 2255 fNper = GetDouble(); 2256 fPer = GetDouble(); 2257 fRate = GetDouble(); 2258 if (fPer < 1.0 || fPer > fNper) 2259 PushIllegalArgument(); 2260 else 2261 { 2262 double fPmt; 2263 double fInterestPer = ScGetIpmt(fRate, fPer, fNper, fPv, fFv, bPayInAdvance, fPmt); 2264 PushDouble(fPmt - fInterestPer); 2265 } 2266 } 2267 2268 void ScInterpreter::ScCumIpmt() 2269 { 2270 nFuncFmtType = SvNumFormatType::CURRENCY; 2271 if ( MustHaveParamCount( GetByte(), 6 ) ) 2272 { 2273 double fRate, fNper, fPv, fStart, fEnd; 2274 double fFlag = GetDoubleWithDefault( -1.0 ); 2275 fEnd = ::rtl::math::approxFloor(GetDouble()); 2276 fStart = ::rtl::math::approxFloor(GetDouble()); 2277 fPv = GetDouble(); 2278 fNper = GetDouble(); 2279 fRate = GetDouble(); 2280 if (fStart < 1.0 || fEnd < fStart || fRate <= 0.0 || 2281 fEnd > fNper || fNper <= 0.0 || fPv <= 0.0 || 2282 ( fFlag != 0.0 && fFlag != 1.0 )) 2283 PushIllegalArgument(); 2284 else 2285 { 2286 bool bPayInAdvance = static_cast<bool>(fFlag); 2287 sal_uLong nStart = static_cast<sal_uLong>(fStart); 2288 sal_uLong nEnd = static_cast<sal_uLong>(fEnd) ; 2289 double fPmt = ScGetPMT(fRate, fNper, fPv, 0.0, bPayInAdvance); 2290 double fIpmt = 0.0; 2291 if (nStart == 1) 2292 { 2293 if (!bPayInAdvance) 2294 fIpmt = -fPv; 2295 nStart++; 2296 } 2297 for (sal_uLong i = nStart; i <= nEnd; i++) 2298 { 2299 if (bPayInAdvance) 2300 fIpmt += ScGetFV(fRate, static_cast<double>(i-2), fPmt, fPv, true) - fPmt; 2301 else 2302 fIpmt += ScGetFV(fRate, static_cast<double>(i-1), fPmt, fPv, false); 2303 } 2304 fIpmt *= fRate; 2305 PushDouble(fIpmt); 2306 } 2307 } 2308 } 2309 2310 void ScInterpreter::ScCumPrinc() 2311 { 2312 nFuncFmtType = SvNumFormatType::CURRENCY; 2313 if ( MustHaveParamCount( GetByte(), 6 ) ) 2314 { 2315 double fRate, fNper, fPv, fStart, fEnd; 2316 double fFlag = GetDoubleWithDefault( -1.0 ); 2317 fEnd = ::rtl::math::approxFloor(GetDouble()); 2318 fStart = ::rtl::math::approxFloor(GetDouble()); 2319 fPv = GetDouble(); 2320 fNper = GetDouble(); 2321 fRate = GetDouble(); 2322 if (fStart < 1.0 || fEnd < fStart || fRate <= 0.0 || 2323 fEnd > fNper || fNper <= 0.0 || fPv <= 0.0 || 2324 ( fFlag != 0.0 && fFlag != 1.0 )) 2325 PushIllegalArgument(); 2326 else 2327 { 2328 bool bPayInAdvance = static_cast<bool>(fFlag); 2329 double fPmt = ScGetPMT(fRate, fNper, fPv, 0.0, bPayInAdvance); 2330 double fPpmt = 0.0; 2331 sal_uLong nStart = static_cast<sal_uLong>(fStart); 2332 sal_uLong nEnd = static_cast<sal_uLong>(fEnd); 2333 if (nStart == 1) 2334 { 2335 if (bPayInAdvance) 2336 fPpmt = fPmt; 2337 else 2338 fPpmt = fPmt + fPv * fRate; 2339 nStart++; 2340 } 2341 for (sal_uLong i = nStart; i <= nEnd; i++) 2342 { 2343 if (bPayInAdvance) 2344 fPpmt += fPmt - (ScGetFV(fRate, static_cast<double>(i-2), fPmt, fPv, true) - fPmt) * fRate; 2345 else 2346 fPpmt += fPmt - ScGetFV(fRate, static_cast<double>(i-1), fPmt, fPv, false) * fRate; 2347 } 2348 PushDouble(fPpmt); 2349 } 2350 } 2351 } 2352 2353 void ScInterpreter::ScEffect() 2354 { 2355 nFuncFmtType = SvNumFormatType::PERCENT; 2356 if ( MustHaveParamCount( GetByte(), 2 ) ) 2357 { 2358 double fPeriods = GetDouble(); 2359 double fNominal = GetDouble(); 2360 if (fPeriods < 1.0 || fNominal < 0.0) 2361 PushIllegalArgument(); 2362 else if ( fNominal == 0.0 ) 2363 PushDouble( 0.0 ); 2364 else 2365 { 2366 fPeriods = ::rtl::math::approxFloor(fPeriods); 2367 PushDouble(pow(1.0 + fNominal/fPeriods, fPeriods) - 1.0); 2368 } 2369 } 2370 } 2371 2372 void ScInterpreter::ScNominal() 2373 { 2374 nFuncFmtType = SvNumFormatType::PERCENT; 2375 if ( MustHaveParamCount( GetByte(), 2 ) ) 2376 { 2377 double fPeriods = GetDouble(); 2378 double fEffective = GetDouble(); 2379 if (fPeriods < 1.0 || fEffective <= 0.0) 2380 PushIllegalArgument(); 2381 else 2382 { 2383 fPeriods = ::rtl::math::approxFloor(fPeriods); 2384 PushDouble( (pow(fEffective + 1.0, 1.0 / fPeriods) - 1.0) * fPeriods ); 2385 } 2386 } 2387 } 2388 2389 void ScInterpreter::ScMod() 2390 { 2391 if ( MustHaveParamCount( GetByte(), 2 ) ) 2392 { 2393 double fDenom = GetDouble(); // Denominator 2394 if ( fDenom == 0.0 ) 2395 { 2396 PushError(FormulaError::DivisionByZero); 2397 return; 2398 } 2399 double fNum = GetDouble(); // Numerator 2400 double fRes = ::rtl::math::approxSub( fNum, 2401 ::rtl::math::approxFloor( fNum / fDenom ) * fDenom ); 2402 if ( ( fDenom > 0 && fRes >= 0 && fRes < fDenom ) || 2403 ( fDenom < 0 && fRes <= 0 && fRes > fDenom ) ) 2404 PushDouble( fRes ); 2405 else 2406 PushError( FormulaError::NoValue ); 2407 } 2408 } 2409 2410 void ScInterpreter::ScIntersect() 2411 { 2412 formula::FormulaConstTokenRef p2nd = PopToken(); 2413 formula::FormulaConstTokenRef p1st = PopToken(); 2414 2415 if (nGlobalError != FormulaError::NONE || !p2nd || !p1st) 2416 { 2417 PushIllegalArgument(); 2418 return; 2419 } 2420 2421 StackVar sv1 = p1st->GetType(); 2422 StackVar sv2 = p2nd->GetType(); 2423 if ((sv1 != svSingleRef && sv1 != svDoubleRef && sv1 != svRefList) || 2424 (sv2 != svSingleRef && sv2 != svDoubleRef && sv2 != svRefList)) 2425 { 2426 PushIllegalArgument(); 2427 return; 2428 } 2429 2430 const formula::FormulaToken* x1 = p1st.get(); 2431 const formula::FormulaToken* x2 = p2nd.get(); 2432 if (sv1 == svRefList || sv2 == svRefList) 2433 { 2434 // Now this is a bit nasty but it simplifies things, and having 2435 // intersections with lists isn't too common, if at all.. 2436 // Convert a reference to list. 2437 const formula::FormulaToken* xt[2] = { x1, x2 }; 2438 StackVar sv[2] = { sv1, sv2 }; 2439 // There may only be one reference; the other is necessarily a list 2440 // Ensure converted list proper destruction 2441 std::unique_ptr<formula::FormulaToken> p; 2442 for (size_t i=0; i<2; ++i) 2443 { 2444 if (sv[i] == svSingleRef) 2445 { 2446 ScComplexRefData aRef; 2447 aRef.Ref1 = aRef.Ref2 = *xt[i]->GetSingleRef(); 2448 p.reset(new ScRefListToken); 2449 p->GetRefList()->push_back( aRef); 2450 xt[i] = p.get(); 2451 } 2452 else if (sv[i] == svDoubleRef) 2453 { 2454 ScComplexRefData aRef = *xt[i]->GetDoubleRef(); 2455 p.reset(new ScRefListToken); 2456 p->GetRefList()->push_back( aRef); 2457 xt[i] = p.get(); 2458 } 2459 } 2460 x1 = xt[0]; 2461 x2 = xt[1]; 2462 2463 ScTokenRef xRes = new ScRefListToken; 2464 ScRefList* pRefList = xRes->GetRefList(); 2465 for (const auto& rRef1 : *x1->GetRefList()) 2466 { 2467 const ScAddress& r11 = rRef1.Ref1.toAbs(aPos); 2468 const ScAddress& r12 = rRef1.Ref2.toAbs(aPos); 2469 for (const auto& rRef2 : *x2->GetRefList()) 2470 { 2471 const ScAddress& r21 = rRef2.Ref1.toAbs(aPos); 2472 const ScAddress& r22 = rRef2.Ref2.toAbs(aPos); 2473 SCCOL nCol1 = ::std::max( r11.Col(), r21.Col()); 2474 SCROW nRow1 = ::std::max( r11.Row(), r21.Row()); 2475 SCTAB nTab1 = ::std::max( r11.Tab(), r21.Tab()); 2476 SCCOL nCol2 = ::std::min( r12.Col(), r22.Col()); 2477 SCROW nRow2 = ::std::min( r12.Row(), r22.Row()); 2478 SCTAB nTab2 = ::std::min( r12.Tab(), r22.Tab()); 2479 if (nCol2 < nCol1 || nRow2 < nRow1 || nTab2 < nTab1) 2480 ; // nothing 2481 else 2482 { 2483 ScComplexRefData aRef; 2484 aRef.InitRange( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2); 2485 pRefList->push_back( aRef); 2486 } 2487 } 2488 } 2489 size_t n = pRefList->size(); 2490 if (!n) 2491 PushError( FormulaError::NoRef); 2492 else if (n == 1) 2493 { 2494 const ScComplexRefData& rRef = (*pRefList)[0]; 2495 if (rRef.Ref1 == rRef.Ref2) 2496 PushTempToken( new ScSingleRefToken( rRef.Ref1)); 2497 else 2498 PushTempToken( new ScDoubleRefToken( rRef)); 2499 } 2500 else 2501 PushTokenRef( xRes); 2502 } 2503 else 2504 { 2505 const formula::FormulaToken* pt[2] = { x1, x2 }; 2506 StackVar sv[2] = { sv1, sv2 }; 2507 SCCOL nC1[2], nC2[2]; 2508 SCROW nR1[2], nR2[2]; 2509 SCTAB nT1[2], nT2[2]; 2510 for (size_t i=0; i<2; ++i) 2511 { 2512 switch (sv[i]) 2513 { 2514 case svSingleRef: 2515 case svDoubleRef: 2516 { 2517 { 2518 const ScAddress& r = pt[i]->GetSingleRef()->toAbs(aPos); 2519 nC1[i] = r.Col(); 2520 nR1[i] = r.Row(); 2521 nT1[i] = r.Tab(); 2522 } 2523 if (sv[i] == svDoubleRef) 2524 { 2525 const ScAddress& r = pt[i]->GetSingleRef2()->toAbs(aPos); 2526 nC2[i] = r.Col(); 2527 nR2[i] = r.Row(); 2528 nT2[i] = r.Tab(); 2529 } 2530 else 2531 { 2532 nC2[i] = nC1[i]; 2533 nR2[i] = nR1[i]; 2534 nT2[i] = nT1[i]; 2535 } 2536 } 2537 break; 2538 default: 2539 ; // nothing, prevent compiler warning 2540 } 2541 } 2542 SCCOL nCol1 = ::std::max( nC1[0], nC1[1]); 2543 SCROW nRow1 = ::std::max( nR1[0], nR1[1]); 2544 SCTAB nTab1 = ::std::max( nT1[0], nT1[1]); 2545 SCCOL nCol2 = ::std::min( nC2[0], nC2[1]); 2546 SCROW nRow2 = ::std::min( nR2[0], nR2[1]); 2547 SCTAB nTab2 = ::std::min( nT2[0], nT2[1]); 2548 if (nCol2 < nCol1 || nRow2 < nRow1 || nTab2 < nTab1) 2549 PushError( FormulaError::NoRef); 2550 else if (nCol2 == nCol1 && nRow2 == nRow1 && nTab2 == nTab1) 2551 PushSingleRef( nCol1, nRow1, nTab1); 2552 else 2553 PushDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2); 2554 } 2555 } 2556 2557 void ScInterpreter::ScRangeFunc() 2558 { 2559 formula::FormulaConstTokenRef x2 = PopToken(); 2560 formula::FormulaConstTokenRef x1 = PopToken(); 2561 2562 if (nGlobalError != FormulaError::NONE || !x2 || !x1) 2563 { 2564 PushIllegalArgument(); 2565 return; 2566 } 2567 // We explicitly tell extendRangeReference() to not reuse the token, 2568 // casting const away spares two clones. 2569 FormulaTokenRef xRes = extendRangeReference( 2570 const_cast<FormulaToken&>(*x1), const_cast<FormulaToken&>(*x2), aPos, false); 2571 if (!xRes) 2572 PushIllegalArgument(); 2573 else 2574 PushTokenRef( xRes); 2575 } 2576 2577 void ScInterpreter::ScUnionFunc() 2578 { 2579 formula::FormulaConstTokenRef p2nd = PopToken(); 2580 formula::FormulaConstTokenRef p1st = PopToken(); 2581 2582 if (nGlobalError != FormulaError::NONE || !p2nd || !p1st) 2583 { 2584 PushIllegalArgument(); 2585 return; 2586 } 2587 2588 StackVar sv1 = p1st->GetType(); 2589 StackVar sv2 = p2nd->GetType(); 2590 if ((sv1 != svSingleRef && sv1 != svDoubleRef && sv1 != svRefList) || 2591 (sv2 != svSingleRef && sv2 != svDoubleRef && sv2 != svRefList)) 2592 { 2593 PushIllegalArgument(); 2594 return; 2595 } 2596 2597 const formula::FormulaToken* x1 = p1st.get(); 2598 const formula::FormulaToken* x2 = p2nd.get(); 2599 2600 ScTokenRef xRes; 2601 // Append to an existing RefList if there is one. 2602 if (sv1 == svRefList) 2603 { 2604 xRes = x1->Clone(); 2605 sv1 = svUnknown; // mark as handled 2606 } 2607 else if (sv2 == svRefList) 2608 { 2609 xRes = x2->Clone(); 2610 sv2 = svUnknown; // mark as handled 2611 } 2612 else 2613 xRes = new ScRefListToken; 2614 ScRefList* pRes = xRes->GetRefList(); 2615 const formula::FormulaToken* pt[2] = { x1, x2 }; 2616 StackVar sv[2] = { sv1, sv2 }; 2617 for (size_t i=0; i<2; ++i) 2618 { 2619 if (pt[i] == xRes) 2620 continue; 2621 switch (sv[i]) 2622 { 2623 case svSingleRef: 2624 { 2625 ScComplexRefData aRef; 2626 aRef.Ref1 = aRef.Ref2 = *pt[i]->GetSingleRef(); 2627 pRes->push_back( aRef); 2628 } 2629 break; 2630 case svDoubleRef: 2631 pRes->push_back( *pt[i]->GetDoubleRef()); 2632 break; 2633 case svRefList: 2634 { 2635 const ScRefList* p = pt[i]->GetRefList(); 2636 for (const auto& rRef : *p) 2637 { 2638 pRes->push_back( rRef); 2639 } 2640 } 2641 break; 2642 default: 2643 ; // nothing, prevent compiler warning 2644 } 2645 } 2646 ValidateRef( *pRes); // set #REF! if needed 2647 PushTokenRef( xRes); 2648 } 2649 2650 void ScInterpreter::ScCurrent() 2651 { 2652 FormulaConstTokenRef xTok( PopToken()); 2653 if (xTok) 2654 { 2655 PushTokenRef( xTok); 2656 PushTokenRef( xTok); 2657 } 2658 else 2659 PushError( FormulaError::UnknownStackVariable); 2660 } 2661 2662 void ScInterpreter::ScStyle() 2663 { 2664 sal_uInt8 nParamCount = GetByte(); 2665 if (nParamCount >= 1 && nParamCount <= 3) 2666 { 2667 OUString aStyle2; // Template after timer 2668 if (nParamCount >= 3) 2669 aStyle2 = GetString().getString(); 2670 long nTimeOut = 0; // timeout 2671 if (nParamCount >= 2) 2672 nTimeOut = static_cast<long>(GetDouble()*1000.0); 2673 OUString aStyle1 = GetString().getString(); // Template for immediate 2674 2675 if (nTimeOut < 0) 2676 nTimeOut = 0; 2677 2678 // Execute request to apply template 2679 if ( !pDok->IsClipOrUndo() ) 2680 { 2681 SfxObjectShell* pShell = pDok->GetDocumentShell(); 2682 if (pShell) 2683 { 2684 // notify object shell directly! 2685 bool bNotify = true; 2686 if (aStyle2.isEmpty()) 2687 { 2688 const ScStyleSheet* pStyle = pDok->GetStyle(aPos.Col(), aPos.Row(), aPos.Tab()); 2689 2690 if (pStyle && pStyle->GetName() == aStyle1) 2691 bNotify = false; 2692 } 2693 2694 if (bNotify) 2695 { 2696 ScRange aRange(aPos); 2697 ScAutoStyleHint aHint( aRange, aStyle1, nTimeOut, aStyle2 ); 2698 pShell->Broadcast( aHint ); 2699 } 2700 } 2701 } 2702 2703 PushDouble(0.0); 2704 } 2705 else 2706 PushIllegalParameter(); 2707 } 2708 2709 static ScDdeLink* lcl_GetDdeLink( const sfx2::LinkManager* pLinkMgr, 2710 const OUString& rA, const OUString& rT, const OUString& rI, sal_uInt8 nM ) 2711 { 2712 size_t nCount = pLinkMgr->GetLinks().size(); 2713 for (size_t i=0; i<nCount; i++ ) 2714 { 2715 ::sfx2::SvBaseLink* pBase = pLinkMgr->GetLinks()[i].get(); 2716 if (ScDdeLink* pLink = dynamic_cast<ScDdeLink*>(pBase)) 2717 { 2718 if ( pLink->GetAppl() == rA && 2719 pLink->GetTopic() == rT && 2720 pLink->GetItem() == rI && 2721 pLink->GetMode() == nM ) 2722 return pLink; 2723 } 2724 } 2725 2726 return nullptr; 2727 } 2728 2729 void ScInterpreter::ScDde() 2730 { 2731 // application, file, scope 2732 // application, Topic, Item 2733 2734 sal_uInt8 nParamCount = GetByte(); 2735 if ( MustHaveParamCount( nParamCount, 3, 4 ) ) 2736 { 2737 sal_uInt8 nMode = SC_DDE_DEFAULT; 2738 if (nParamCount == 4) 2739 { 2740 sal_uInt32 nTmp = GetUInt32(); 2741 if (nGlobalError != FormulaError::NONE || nTmp > SAL_MAX_UINT8) 2742 { 2743 PushIllegalArgument(); 2744 return; 2745 } 2746 nMode = static_cast<sal_uInt8>(nTmp); 2747 } 2748 OUString aItem = GetString().getString(); 2749 OUString aTopic = GetString().getString(); 2750 OUString aAppl = GetString().getString(); 2751 2752 if (nMode > SC_DDE_TEXT) 2753 nMode = SC_DDE_DEFAULT; 2754 2755 // temporary documents (ScFunctionAccess) have no DocShell 2756 // and no LinkManager -> abort 2757 2758 //sfx2::LinkManager* pLinkMgr = pDok->GetLinkManager(); 2759 if (!mpLinkManager) 2760 { 2761 PushNoValue(); 2762 return; 2763 } 2764 2765 // Need to reinterpret after loading (build links) 2766 rArr.AddRecalcMode( ScRecalcMode::ONLOAD_LENIENT ); 2767 2768 // while the link is not evaluated, idle must be disabled (to avoid circular references) 2769 2770 bool bOldEnabled = pDok->IsIdleEnabled(); 2771 pDok->EnableIdle(false); 2772 2773 // Get/ Create link object 2774 2775 ScDdeLink* pLink = lcl_GetDdeLink( mpLinkManager, aAppl, aTopic, aItem, nMode ); 2776 2777 //TODO: Save Dde-links (in addition) more efficient at document !!!!! 2778 // ScDdeLink* pLink = pDok->GetDdeLink( aAppl, aTopic, aItem ); 2779 2780 bool bWasError = ( pMyFormulaCell && pMyFormulaCell->GetRawError() != FormulaError::NONE ); 2781 2782 if (!pLink) 2783 { 2784 pLink = new ScDdeLink( pDok, aAppl, aTopic, aItem, nMode ); 2785 mpLinkManager->InsertDDELink( pLink, aAppl, aTopic, aItem ); 2786 if ( mpLinkManager->GetLinks().size() == 1 ) // the first one? 2787 { 2788 SfxBindings* pBindings = pDok->GetViewBindings(); 2789 if (pBindings) 2790 pBindings->Invalidate( SID_LINKS ); // Link-Manager enabled 2791 } 2792 2793 //if the document was just loaded, but the ScDdeLink entry was missing, then 2794 //don't update this link until the links are updated in response to the users 2795 //decision 2796 if (!pDok->HasLinkFormulaNeedingCheck()) 2797 { 2798 //TODO: evaluate asynchron ??? 2799 pLink->TryUpdate(); // TryUpdate doesn't call Update multiple times 2800 } 2801 2802 if (pMyFormulaCell) 2803 { 2804 // StartListening after the Update to avoid circular references 2805 pMyFormulaCell->StartListening( *pLink ); 2806 } 2807 } 2808 else 2809 { 2810 if (pMyFormulaCell) 2811 pMyFormulaCell->StartListening( *pLink ); 2812 } 2813 2814 // If an new Error from Reschedule appears when the link is executed then reset the errorflag 2815 2816 2817 if ( pMyFormulaCell && pMyFormulaCell->GetRawError() != FormulaError::NONE && !bWasError ) 2818 pMyFormulaCell->SetErrCode(FormulaError::NONE); 2819 2820 // check the value 2821 2822 const ScMatrix* pLinkMat = pLink->GetResult(); 2823 if (pLinkMat) 2824 { 2825 SCSIZE nC, nR; 2826 pLinkMat->GetDimensions(nC, nR); 2827 ScMatrixRef pNewMat = GetNewMat( nC, nR); 2828 if (pNewMat) 2829 { 2830 pLinkMat->MatCopy(*pNewMat); // copy 2831 PushMatrix( pNewMat ); 2832 } 2833 else 2834 PushIllegalArgument(); 2835 } 2836 else 2837 PushNA(); 2838 2839 pDok->EnableIdle(bOldEnabled); 2840 mpLinkManager->CloseCachedComps(); 2841 } 2842 } 2843 2844 void ScInterpreter::ScBase() 2845 { // Value, Base [, MinLen] 2846 sal_uInt8 nParamCount = GetByte(); 2847 if ( MustHaveParamCount( nParamCount, 2, 3 ) ) 2848 { 2849 static const sal_Unicode pDigits[] = { 2850 '0','1','2','3','4','5','6','7','8','9', 2851 'A','B','C','D','E','F','G','H','I','J','K','L','M', 2852 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z', 2853 0 2854 }; 2855 static const int nDigits = SAL_N_ELEMENTS(pDigits) - 1; 2856 sal_Int32 nMinLen; 2857 if ( nParamCount == 3 ) 2858 { 2859 double fLen = ::rtl::math::approxFloor( GetDouble() ); 2860 if ( 1.0 <= fLen && fLen < SAL_MAX_UINT16 ) 2861 nMinLen = static_cast<sal_Int32>(fLen); 2862 else if ( fLen == 0.0 ) 2863 nMinLen = 1; 2864 else 2865 nMinLen = 0; // Error 2866 } 2867 else 2868 nMinLen = 1; 2869 double fBase = ::rtl::math::approxFloor( GetDouble() ); 2870 double fVal = ::rtl::math::approxFloor( GetDouble() ); 2871 double fChars = ((fVal > 0.0 && fBase > 0.0) ? 2872 (ceil( log( fVal ) / log( fBase ) ) + 2.0) : 2873 2.0); 2874 if ( fChars >= SAL_MAX_UINT16 ) 2875 nMinLen = 0; // Error 2876 2877 if ( nGlobalError == FormulaError::NONE && nMinLen && 2 <= fBase && fBase <= nDigits && 0 <= fVal ) 2878 { 2879 const sal_Int32 nConstBuf = 128; 2880 sal_Unicode aBuf[nConstBuf]; 2881 sal_Int32 nBuf = std::max<sal_Int32>( fChars, nMinLen + 1 ); 2882 sal_Unicode* pBuf = (nBuf <= nConstBuf ? aBuf : new sal_Unicode[nBuf]); 2883 for ( sal_Int32 j = 0; j < nBuf; ++j ) 2884 { 2885 pBuf[j] = '0'; 2886 } 2887 sal_Unicode* p = pBuf + nBuf - 1; 2888 *p = 0; 2889 if ( fVal <= sal_uLong(~0) ) 2890 { 2891 sal_uLong nVal = static_cast<sal_uLong>(fVal); 2892 sal_uLong nBase = static_cast<sal_uLong>(fBase); 2893 while ( nVal && p > pBuf ) 2894 { 2895 *--p = pDigits[ nVal % nBase ]; 2896 nVal /= nBase; 2897 } 2898 fVal = static_cast<double>(nVal); 2899 } 2900 else 2901 { 2902 bool bDirt = false; 2903 while ( fVal && p > pBuf ) 2904 { 2905 //TODO: roundoff error starting with numbers greater than 2**48 2906 // double fDig = ::rtl::math::approxFloor( fmod( fVal, fBase ) ); 2907 // a little bit better: 2908 double fInt = ::rtl::math::approxFloor( fVal / fBase ); 2909 double fMult = fInt * fBase; 2910 #if 0 2911 // =BASIS(1e308;36) => GPF with 2912 // nDig = (size_t) ::rtl::math::approxFloor( fVal - fMult ); 2913 // in spite off previous test if fVal >= fMult 2914 double fDebug1 = fVal - fMult; 2915 // fVal := 7,5975311883090e+290 2916 // fMult := 7,5975311883090e+290 2917 // fDebug1 := 1,3848924157003e+275 <- RoundOff-Error 2918 // fVal != fMult, aber: ::rtl::math::approxEqual( fVal, fMult ) == TRUE 2919 double fDebug2 = ::rtl::math::approxSub( fVal, fMult ); 2920 // and ::rtl::math::approxSub( fVal, fMult ) == 0 2921 double fDebug3 = ( fInt ? fVal / fInt : 0.0 ); 2922 2923 // Actual after strange fDebug1 and fVal < fMult is fDebug2 == fBase, but 2924 // anyway it can't be compared, then bDirt is executed an everything is good... 2925 2926 // prevent compiler warnings 2927 (void)fDebug1; (void)fDebug2; (void)fDebug3; 2928 #endif 2929 size_t nDig; 2930 if ( fVal < fMult ) 2931 { // something is wrong there 2932 bDirt = true; 2933 nDig = 0; 2934 } 2935 else 2936 { 2937 double fDig = ::rtl::math::approxFloor( ::rtl::math::approxSub( fVal, fMult ) ); 2938 if ( bDirt ) 2939 { 2940 bDirt = false; 2941 --fDig; 2942 } 2943 if ( fDig <= 0.0 ) 2944 nDig = 0; 2945 else if ( fDig >= fBase ) 2946 nDig = static_cast<size_t>(fBase) - 1; 2947 else 2948 nDig = static_cast<size_t>(fDig); 2949 } 2950 *--p = pDigits[ nDig ]; 2951 fVal = fInt; 2952 } 2953 } 2954 if ( fVal ) 2955 PushError( FormulaError::StringOverflow ); 2956 else 2957 { 2958 if ( nBuf - (p - pBuf) <= nMinLen ) 2959 p = pBuf + nBuf - 1 - nMinLen; 2960 PushStringBuffer( p ); 2961 } 2962 if ( pBuf != aBuf ) 2963 delete [] pBuf; 2964 } 2965 else 2966 PushIllegalArgument(); 2967 } 2968 } 2969 2970 void ScInterpreter::ScDecimal() 2971 { // Text, Base 2972 if ( MustHaveParamCount( GetByte(), 2 ) ) 2973 { 2974 double fBase = ::rtl::math::approxFloor( GetDouble() ); 2975 OUString aStr = GetString().getString(); 2976 if ( nGlobalError == FormulaError::NONE && 2 <= fBase && fBase <= 36 ) 2977 { 2978 double fVal = 0.0; 2979 int nBase = static_cast<int>(fBase); 2980 const sal_Unicode* p = aStr.getStr(); 2981 while ( *p == ' ' || *p == '\t' ) 2982 p++; // strip leading white space 2983 if ( nBase == 16 ) 2984 { // evtl. hex-prefix strippen 2985 if ( *p == 'x' || *p == 'X' ) 2986 p++; 2987 else if ( *p == '0' && (*(p+1) == 'x' || *(p+1) == 'X') ) 2988 p += 2; 2989 } 2990 while ( *p ) 2991 { 2992 int n; 2993 if ( '0' <= *p && *p <= '9' ) 2994 n = *p - '0'; 2995 else if ( 'A' <= *p && *p <= 'Z' ) 2996 n = 10 + (*p - 'A'); 2997 else if ( 'a' <= *p && *p <= 'z' ) 2998 n = 10 + (*p - 'a'); 2999 else 3000 n = nBase; 3001 if ( nBase <= n ) 3002 { 3003 if ( *(p+1) == 0 && 3004 ( (nBase == 2 && (*p == 'b' || *p == 'B')) 3005 ||(nBase == 16 && (*p == 'h' || *p == 'H')) ) 3006 ) 3007 ; // 101b and F00Dh are ok 3008 else 3009 { 3010 PushIllegalArgument(); 3011 return ; 3012 } 3013 } 3014 else 3015 fVal = fVal * fBase + n; 3016 p++; 3017 3018 } 3019 PushDouble( fVal ); 3020 } 3021 else 3022 PushIllegalArgument(); 3023 } 3024 } 3025 3026 void ScInterpreter::ScConvertOOo() 3027 { // Value, FromUnit, ToUnit 3028 if ( MustHaveParamCount( GetByte(), 3 ) ) 3029 { 3030 OUString aToUnit = GetString().getString(); 3031 OUString aFromUnit = GetString().getString(); 3032 double fVal = GetDouble(); 3033 if ( nGlobalError != FormulaError::NONE ) 3034 PushError( nGlobalError); 3035 else 3036 { 3037 // first of all search for the given order; if it can't be found then search for the inverse 3038 double fConv; 3039 if ( ScGlobal::GetUnitConverter()->GetValue( fConv, aFromUnit, aToUnit ) ) 3040 PushDouble( fVal * fConv ); 3041 else if ( ScGlobal::GetUnitConverter()->GetValue( fConv, aToUnit, aFromUnit ) ) 3042 PushDouble( fVal / fConv ); 3043 else 3044 PushNA(); 3045 } 3046 } 3047 } 3048 3049 void ScInterpreter::ScRoman() 3050 { // Value [Mode] 3051 sal_uInt8 nParamCount = GetByte(); 3052 if( MustHaveParamCount( nParamCount, 1, 2 ) ) 3053 { 3054 double fMode = (nParamCount == 2) ? ::rtl::math::approxFloor( GetDouble() ) : 0.0; 3055 double fVal = ::rtl::math::approxFloor( GetDouble() ); 3056 if( nGlobalError != FormulaError::NONE ) 3057 PushError( nGlobalError); 3058 else if( (fMode >= 0.0) && (fMode < 5.0) && (fVal >= 0.0) && (fVal < 4000.0) ) 3059 { 3060 static const sal_Unicode pChars[] = { 'M', 'D', 'C', 'L', 'X', 'V', 'I' }; 3061 static const sal_uInt16 pValues[] = { 1000, 500, 100, 50, 10, 5, 1 }; 3062 static const sal_uInt16 nMaxIndex = sal_uInt16(SAL_N_ELEMENTS(pValues) - 1); 3063 3064 OUStringBuffer aRoman; 3065 sal_uInt16 nVal = static_cast<sal_uInt16>(fVal); 3066 sal_uInt16 nMode = static_cast<sal_uInt16>(fMode); 3067 3068 for( sal_uInt16 i = 0; i <= nMaxIndex / 2; i++ ) 3069 { 3070 sal_uInt16 nIndex = 2 * i; 3071 sal_uInt16 nDigit = nVal / pValues[ nIndex ]; 3072 3073 if( (nDigit % 5) == 4 ) 3074 { 3075 // assert can't happen with nVal<4000 precondition 3076 assert( ((nDigit == 4) ? (nIndex >= 1) : (nIndex >= 2))); 3077 3078 sal_uInt16 nIndex2 = (nDigit == 4) ? nIndex - 1 : nIndex - 2; 3079 sal_uInt16 nSteps = 0; 3080 while( (nSteps < nMode) && (nIndex < nMaxIndex) ) 3081 { 3082 nSteps++; 3083 if( pValues[ nIndex2 ] - pValues[ nIndex + 1 ] <= nVal ) 3084 nIndex++; 3085 else 3086 nSteps = nMode; 3087 } 3088 aRoman.append( pChars[ nIndex ] ).append( pChars[ nIndex2 ] ); 3089 nVal = sal::static_int_cast<sal_uInt16>( nVal + pValues[ nIndex ] ); 3090 nVal = sal::static_int_cast<sal_uInt16>( nVal - pValues[ nIndex2 ] ); 3091 } 3092 else 3093 { 3094 if( nDigit > 4 ) 3095 { 3096 // assert can't happen with nVal<4000 precondition 3097 assert( nIndex >= 1 ); 3098 aRoman.append( pChars[ nIndex - 1 ] ); 3099 } 3100 sal_Int32 nPad = nDigit % 5; 3101 if (nPad) 3102 { 3103 OUStringBuffer aBuf(aRoman); 3104 comphelper::string::padToLength(aBuf, aBuf.getLength() + nPad, 3105 pChars[nIndex]); 3106 aRoman = aBuf.makeStringAndClear(); 3107 } 3108 nVal %= pValues[ nIndex ]; 3109 } 3110 } 3111 3112 PushString( aRoman.makeStringAndClear() ); 3113 } 3114 else 3115 PushIllegalArgument(); 3116 } 3117 } 3118 3119 static bool lcl_GetArabicValue( sal_Unicode cChar, sal_uInt16& rnValue, bool& rbIsDec ) 3120 { 3121 switch( cChar ) 3122 { 3123 case 'M': rnValue = 1000; rbIsDec = true; break; 3124 case 'D': rnValue = 500; rbIsDec = false; break; 3125 case 'C': rnValue = 100; rbIsDec = true; break; 3126 case 'L': rnValue = 50; rbIsDec = false; break; 3127 case 'X': rnValue = 10; rbIsDec = true; break; 3128 case 'V': rnValue = 5; rbIsDec = false; break; 3129 case 'I': rnValue = 1; rbIsDec = true; break; 3130 default: return false; 3131 } 3132 return true; 3133 } 3134 3135 void ScInterpreter::ScArabic() 3136 { 3137 OUString aRoman = GetString().getString(); 3138 if( nGlobalError != FormulaError::NONE ) 3139 PushError( nGlobalError); 3140 else 3141 { 3142 aRoman = aRoman.toAsciiUpperCase(); 3143 3144 sal_uInt16 nValue = 0; 3145 sal_uInt16 nValidRest = 3999; 3146 sal_Int32 nCharIndex = 0; 3147 sal_Int32 nCharCount = aRoman.getLength(); 3148 bool bValid = true; 3149 3150 while( bValid && (nCharIndex < nCharCount) ) 3151 { 3152 sal_uInt16 nDigit1 = 0; 3153 sal_uInt16 nDigit2 = 0; 3154 bool bIsDec1 = false; 3155 bValid = lcl_GetArabicValue( aRoman[nCharIndex], nDigit1, bIsDec1 ); 3156 if( bValid && (nCharIndex + 1 < nCharCount) ) 3157 { 3158 bool bIsDec2 = false; 3159 bValid = lcl_GetArabicValue( aRoman[nCharIndex + 1], nDigit2, bIsDec2 ); 3160 } 3161 if( bValid ) 3162 { 3163 if( nDigit1 >= nDigit2 ) 3164 { 3165 nValue = sal::static_int_cast<sal_uInt16>( nValue + nDigit1 ); 3166 nValidRest %= (nDigit1 * (bIsDec1 ? 5 : 2)); 3167 bValid = (nValidRest >= nDigit1); 3168 if( bValid ) 3169 nValidRest = sal::static_int_cast<sal_uInt16>( nValidRest - nDigit1 ); 3170 nCharIndex++; 3171 } 3172 else if( nDigit1 * 2 != nDigit2 ) 3173 { 3174 sal_uInt16 nDiff = nDigit2 - nDigit1; 3175 nValue = sal::static_int_cast<sal_uInt16>( nValue + nDiff ); 3176 bValid = (nValidRest >= nDiff); 3177 if( bValid ) 3178 nValidRest = nDigit1 - 1; 3179 nCharIndex += 2; 3180 } 3181 else 3182 bValid = false; 3183 } 3184 } 3185 if( bValid ) 3186 PushInt( nValue ); 3187 else 3188 PushIllegalArgument(); 3189 } 3190 } 3191 3192 void ScInterpreter::ScHyperLink() 3193 { 3194 sal_uInt8 nParamCount = GetByte(); 3195 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 3196 { 3197 double fVal = 0.0; 3198 svl::SharedString aStr; 3199 ScMatValType nResultType = ScMatValType::String; 3200 3201 if ( nParamCount == 2 ) 3202 { 3203 switch ( GetStackType() ) 3204 { 3205 case svDouble: 3206 fVal = GetDouble(); 3207 nResultType = ScMatValType::Value; 3208 break; 3209 case svString: 3210 aStr = GetString(); 3211 break; 3212 case svSingleRef: 3213 case svDoubleRef: 3214 { 3215 ScAddress aAdr; 3216 if ( !PopDoubleRefOrSingleRef( aAdr ) ) 3217 break; 3218 3219 ScRefCellValue aCell(*pDok, aAdr); 3220 if (aCell.hasEmptyValue()) 3221 nResultType = ScMatValType::Empty; 3222 else 3223 { 3224 FormulaError nErr = GetCellErrCode(aCell); 3225 if (nErr != FormulaError::NONE) 3226 SetError( nErr); 3227 else if (aCell.hasNumeric()) 3228 { 3229 fVal = GetCellValue(aAdr, aCell); 3230 nResultType = ScMatValType::Value; 3231 } 3232 else 3233 GetCellString(aStr, aCell); 3234 } 3235 } 3236 break; 3237 case svMatrix: 3238 nResultType = GetDoubleOrStringFromMatrix( fVal, aStr); 3239 break; 3240 case svMissing: 3241 case svEmptyCell: 3242 Pop(); 3243 // mimic xcl 3244 fVal = 0.0; 3245 nResultType = ScMatValType::Value; 3246 break; 3247 default: 3248 PopError(); 3249 SetError( FormulaError::IllegalArgument); 3250 } 3251 } 3252 svl::SharedString aUrl = GetString(); 3253 ScMatrixRef pResMat = GetNewMat( 1, 2); 3254 if (nGlobalError != FormulaError::NONE) 3255 { 3256 fVal = CreateDoubleError( nGlobalError); 3257 nResultType = ScMatValType::Value; 3258 } 3259 if (nParamCount == 2 || nGlobalError != FormulaError::NONE) 3260 { 3261 if (ScMatrix::IsValueType( nResultType)) 3262 pResMat->PutDouble( fVal, 0); 3263 else if (ScMatrix::IsRealStringType( nResultType)) 3264 pResMat->PutString(aStr, 0); 3265 else // EmptyType, EmptyPathType, mimic xcl 3266 pResMat->PutDouble( 0.0, 0 ); 3267 } 3268 else 3269 pResMat->PutString(aUrl, 0); 3270 pResMat->PutString(aUrl, 1); 3271 bMatrixFormula = true; 3272 PushMatrix(pResMat); 3273 } 3274 } 3275 3276 /** Resources at the website of the European Commission: 3277 http://ec.europa.eu/economy_finance/euro/adoption/conversion/ 3278 http://ec.europa.eu/economy_finance/euro/countries/ 3279 */ 3280 static bool lclConvertMoney( const OUString& aSearchUnit, double& rfRate, int& rnDec ) 3281 { 3282 struct ConvertInfo 3283 { 3284 const sal_Char* pCurrText; 3285 double fRate; 3286 int nDec; 3287 }; 3288 static const ConvertInfo aConvertTable[] = { 3289 { "EUR", 1.0, 2 }, 3290 { "ATS", 13.7603, 2 }, 3291 { "BEF", 40.3399, 0 }, 3292 { "DEM", 1.95583, 2 }, 3293 { "ESP", 166.386, 0 }, 3294 { "FIM", 5.94573, 2 }, 3295 { "FRF", 6.55957, 2 }, 3296 { "IEP", 0.787564, 2 }, 3297 { "ITL", 1936.27, 0 }, 3298 { "LUF", 40.3399, 0 }, 3299 { "NLG", 2.20371, 2 }, 3300 { "PTE", 200.482, 2 }, 3301 { "GRD", 340.750, 2 }, 3302 { "SIT", 239.640, 2 }, 3303 { "MTL", 0.429300, 2 }, 3304 { "CYP", 0.585274, 2 }, 3305 { "SKK", 30.1260, 2 }, 3306 { "EEK", 15.6466, 2 }, 3307 { "LVL", 0.702804, 2 }, 3308 { "LTL", 3.45280, 2 } 3309 }; 3310 3311 for (const auto & i : aConvertTable) 3312 if ( aSearchUnit.equalsIgnoreAsciiCaseAscii( i.pCurrText ) ) 3313 { 3314 rfRate = i.fRate; 3315 rnDec = i.nDec; 3316 return true; 3317 } 3318 return false; 3319 } 3320 3321 void ScInterpreter::ScEuroConvert() 3322 { //Value, FromUnit, ToUnit[, FullPrecision, [TriangulationPrecision]] 3323 sal_uInt8 nParamCount = GetByte(); 3324 if ( MustHaveParamCount( nParamCount, 3, 5 ) ) 3325 { 3326 double fPrecision = 0.0; 3327 if ( nParamCount == 5 ) 3328 { 3329 fPrecision = ::rtl::math::approxFloor(GetDouble()); 3330 if ( fPrecision < 3 ) 3331 { 3332 PushIllegalArgument(); 3333 return; 3334 } 3335 } 3336 bool bFullPrecision = false; 3337 if ( nParamCount >= 4 ) 3338 bFullPrecision = GetBool(); 3339 OUString aToUnit = GetString().getString(); 3340 OUString aFromUnit = GetString().getString(); 3341 double fVal = GetDouble(); 3342 if ( nGlobalError != FormulaError::NONE ) 3343 PushError( nGlobalError); 3344 else 3345 { 3346 double fFromRate; 3347 double fToRate; 3348 int nFromDec; 3349 int nToDec; 3350 if ( lclConvertMoney( aFromUnit, fFromRate, nFromDec ) 3351 && lclConvertMoney( aToUnit, fToRate, nToDec ) ) 3352 { 3353 double fRes; 3354 if ( aFromUnit.equalsIgnoreAsciiCase( aToUnit ) ) 3355 fRes = fVal; 3356 else 3357 { 3358 if ( aFromUnit.equalsIgnoreAsciiCase( "EUR" ) ) 3359 fRes = fVal * fToRate; 3360 else 3361 { 3362 double fIntermediate = fVal / fFromRate; 3363 if ( fPrecision ) 3364 fIntermediate = ::rtl::math::round( fIntermediate, 3365 static_cast<int>(fPrecision) ); 3366 fRes = fIntermediate * fToRate; 3367 } 3368 if ( !bFullPrecision ) 3369 fRes = ::rtl::math::round( fRes, nToDec ); 3370 } 3371 PushDouble( fRes ); 3372 } 3373 else 3374 PushIllegalArgument(); 3375 } 3376 } 3377 } 3378 3379 // BAHTTEXT 3380 #define UTF8_TH_0 "\340\270\250\340\270\271\340\270\231\340\270\242\340\271\214" 3381 #define UTF8_TH_1 "\340\270\253\340\270\231\340\270\266\340\271\210\340\270\207" 3382 #define UTF8_TH_2 "\340\270\252\340\270\255\340\270\207" 3383 #define UTF8_TH_3 "\340\270\252\340\270\262\340\270\241" 3384 #define UTF8_TH_4 "\340\270\252\340\270\265\340\271\210" 3385 #define UTF8_TH_5 "\340\270\253\340\271\211\340\270\262" 3386 #define UTF8_TH_6 "\340\270\253\340\270\201" 3387 #define UTF8_TH_7 "\340\271\200\340\270\210\340\271\207\340\270\224" 3388 #define UTF8_TH_8 "\340\271\201\340\270\233\340\270\224" 3389 #define UTF8_TH_9 "\340\271\200\340\270\201\340\271\211\340\270\262" 3390 #define UTF8_TH_10 "\340\270\252\340\270\264\340\270\232" 3391 #define UTF8_TH_11 "\340\271\200\340\270\255\340\271\207\340\270\224" 3392 #define UTF8_TH_20 "\340\270\242\340\270\265\340\271\210" 3393 #define UTF8_TH_1E2 "\340\270\243\340\271\211\340\270\255\340\270\242" 3394 #define UTF8_TH_1E3 "\340\270\236\340\270\261\340\270\231" 3395 #define UTF8_TH_1E4 "\340\270\253\340\270\241\340\270\267\340\271\210\340\270\231" 3396 #define UTF8_TH_1E5 "\340\271\201\340\270\252\340\270\231" 3397 #define UTF8_TH_1E6 "\340\270\245\340\271\211\340\270\262\340\270\231" 3398 #define UTF8_TH_DOT0 "\340\270\226\340\271\211\340\270\247\340\270\231" 3399 #define UTF8_TH_BAHT "\340\270\232\340\270\262\340\270\227" 3400 #define UTF8_TH_SATANG "\340\270\252\340\270\225\340\270\262\340\270\207\340\270\204\340\271\214" 3401 #define UTF8_TH_MINUS "\340\270\245\340\270\232" 3402 3403 // local functions 3404 namespace { 3405 3406 void lclSplitBlock( double& rfInt, sal_Int32& rnBlock, double fValue, double fSize ) 3407 { 3408 rnBlock = static_cast< sal_Int32 >( modf( (fValue + 0.1) / fSize, &rfInt ) * fSize + 0.1 ); 3409 } 3410 3411 /** Appends a digit (0 to 9) to the passed string. */ 3412 void lclAppendDigit( OStringBuffer& rText, sal_Int32 nDigit ) 3413 { 3414 switch( nDigit ) 3415 { 3416 case 0: rText.append( UTF8_TH_0 ); break; 3417 case 1: rText.append( UTF8_TH_1 ); break; 3418 case 2: rText.append( UTF8_TH_2 ); break; 3419 case 3: rText.append( UTF8_TH_3 ); break; 3420 case 4: rText.append( UTF8_TH_4 ); break; 3421 case 5: rText.append( UTF8_TH_5 ); break; 3422 case 6: rText.append( UTF8_TH_6 ); break; 3423 case 7: rText.append( UTF8_TH_7 ); break; 3424 case 8: rText.append( UTF8_TH_8 ); break; 3425 case 9: rText.append( UTF8_TH_9 ); break; 3426 default: OSL_FAIL( "lclAppendDigit - illegal digit" ); 3427 } 3428 } 3429 3430 /** Appends a value raised to a power of 10: nDigit*10^nPow10. 3431 @param nDigit A digit in the range from 1 to 9. 3432 @param nPow10 A value in the range from 2 to 5. 3433 */ 3434 void lclAppendPow10( OStringBuffer& rText, sal_Int32 nDigit, sal_Int32 nPow10 ) 3435 { 3436 OSL_ENSURE( (1 <= nDigit) && (nDigit <= 9), "lclAppendPow10 - illegal digit" ); 3437 lclAppendDigit( rText, nDigit ); 3438 switch( nPow10 ) 3439 { 3440 case 2: rText.append( UTF8_TH_1E2 ); break; 3441 case 3: rText.append( UTF8_TH_1E3 ); break; 3442 case 4: rText.append( UTF8_TH_1E4 ); break; 3443 case 5: rText.append( UTF8_TH_1E5 ); break; 3444 default: OSL_FAIL( "lclAppendPow10 - illegal power" ); 3445 } 3446 } 3447 3448 /** Appends a block of 6 digits (value from 1 to 999,999) to the passed string. */ 3449 void lclAppendBlock( OStringBuffer& rText, sal_Int32 nValue ) 3450 { 3451 OSL_ENSURE( (1 <= nValue) && (nValue <= 999999), "lclAppendBlock - illegal value" ); 3452 if( nValue >= 100000 ) 3453 { 3454 lclAppendPow10( rText, nValue / 100000, 5 ); 3455 nValue %= 100000; 3456 } 3457 if( nValue >= 10000 ) 3458 { 3459 lclAppendPow10( rText, nValue / 10000, 4 ); 3460 nValue %= 10000; 3461 } 3462 if( nValue >= 1000 ) 3463 { 3464 lclAppendPow10( rText, nValue / 1000, 3 ); 3465 nValue %= 1000; 3466 } 3467 if( nValue >= 100 ) 3468 { 3469 lclAppendPow10( rText, nValue / 100, 2 ); 3470 nValue %= 100; 3471 } 3472 if( nValue > 0 ) 3473 { 3474 sal_Int32 nTen = nValue / 10; 3475 sal_Int32 nOne = nValue % 10; 3476 if( nTen >= 1 ) 3477 { 3478 if( nTen >= 3 ) 3479 lclAppendDigit( rText, nTen ); 3480 else if( nTen == 2 ) 3481 rText.append( UTF8_TH_20 ); 3482 rText.append( UTF8_TH_10 ); 3483 } 3484 if( (nTen > 0) && (nOne == 1) ) 3485 rText.append( UTF8_TH_11 ); 3486 else if( nOne > 0 ) 3487 lclAppendDigit( rText, nOne ); 3488 } 3489 } 3490 3491 } // namespace 3492 3493 void ScInterpreter::ScBahtText() 3494 { 3495 sal_uInt8 nParamCount = GetByte(); 3496 if ( MustHaveParamCount( nParamCount, 1 ) ) 3497 { 3498 double fValue = GetDouble(); 3499 if( nGlobalError != FormulaError::NONE ) 3500 { 3501 PushError( nGlobalError); 3502 return; 3503 } 3504 3505 // sign 3506 bool bMinus = fValue < 0.0; 3507 fValue = fabs( fValue ); 3508 3509 // round to 2 digits after decimal point, fValue contains Satang as integer 3510 fValue = ::rtl::math::approxFloor( fValue * 100.0 + 0.5 ); 3511 3512 // split Baht and Satang 3513 double fBaht = 0.0; 3514 sal_Int32 nSatang = 0; 3515 lclSplitBlock( fBaht, nSatang, fValue, 100.0 ); 3516 3517 OStringBuffer aText; 3518 3519 // generate text for Baht value 3520 if( fBaht == 0.0 ) 3521 { 3522 if( nSatang == 0 ) 3523 aText.append( UTF8_TH_0 ); 3524 } 3525 else while( fBaht > 0.0 ) 3526 { 3527 OStringBuffer aBlock; 3528 sal_Int32 nBlock = 0; 3529 lclSplitBlock( fBaht, nBlock, fBaht, 1.0e6 ); 3530 if( nBlock > 0 ) 3531 lclAppendBlock( aBlock, nBlock ); 3532 // add leading "million", if there will come more blocks 3533 if( fBaht > 0.0 ) 3534 aBlock.insert( 0, UTF8_TH_1E6 ); 3535 3536 aText.insert(0, aBlock.makeStringAndClear()); 3537 } 3538 if (!aText.isEmpty()) 3539 aText.append( UTF8_TH_BAHT ); 3540 3541 // generate text for Satang value 3542 if( nSatang == 0 ) 3543 { 3544 aText.append( UTF8_TH_DOT0 ); 3545 } 3546 else 3547 { 3548 lclAppendBlock( aText, nSatang ); 3549 aText.append( UTF8_TH_SATANG ); 3550 } 3551 3552 // add the minus sign 3553 if( bMinus ) 3554 aText.insert( 0, UTF8_TH_MINUS ); 3555 3556 PushString( OStringToOUString(aText.makeStringAndClear(), RTL_TEXTENCODING_UTF8) ); 3557 } 3558 } 3559 3560 void ScInterpreter::ScGetPivotData() 3561 { 3562 sal_uInt8 nParamCount = GetByte(); 3563 3564 if (!MustHaveParamCountMin(nParamCount, 2) || (nParamCount % 2) == 1) 3565 { 3566 PushError(FormulaError::NoRef); 3567 return; 3568 } 3569 3570 bool bOldSyntax = false; 3571 if (nParamCount == 2) 3572 { 3573 // if the first parameter is a ref, assume old syntax 3574 StackVar eFirstType = GetStackType(2); 3575 if (eFirstType == svSingleRef || eFirstType == svDoubleRef) 3576 bOldSyntax = true; 3577 } 3578 3579 std::vector<sheet::DataPilotFieldFilter> aFilters; 3580 OUString aDataFieldName; 3581 ScRange aBlock; 3582 3583 if (bOldSyntax) 3584 { 3585 aDataFieldName = GetString().getString(); 3586 3587 switch (GetStackType()) 3588 { 3589 case svDoubleRef : 3590 PopDoubleRef(aBlock); 3591 break; 3592 case svSingleRef : 3593 { 3594 ScAddress aAddr; 3595 PopSingleRef(aAddr); 3596 aBlock = aAddr; 3597 } 3598 break; 3599 default: 3600 PushError(FormulaError::NoRef); 3601 return; 3602 } 3603 } 3604 else 3605 { 3606 // Standard syntax: separate name/value pairs 3607 3608 sal_uInt16 nFilterCount = nParamCount / 2 - 1; 3609 aFilters.resize(nFilterCount); 3610 3611 sal_uInt16 i = nFilterCount; 3612 while (i-- > 0) 3613 { 3614 /* TODO: also, in case of numeric the entire filter match should 3615 * not be on a (even if locale independent) formatted string down 3616 * below in pDPObj->GetPivotData(). */ 3617 3618 bool bEvaluateFormatIndex; 3619 switch (GetRawStackType()) 3620 { 3621 case svSingleRef: 3622 case svDoubleRef: 3623 bEvaluateFormatIndex = true; 3624 break; 3625 default: 3626 bEvaluateFormatIndex = false; 3627 } 3628 3629 double fDouble; 3630 svl::SharedString aSharedString; 3631 bool bDouble = GetDoubleOrString( fDouble, aSharedString); 3632 if (nGlobalError != FormulaError::NONE) 3633 { 3634 PushError( nGlobalError); 3635 return; 3636 } 3637 3638 if (bDouble) 3639 { 3640 sal_uInt32 nNumFormat; 3641 if (bEvaluateFormatIndex && nCurFmtIndex) 3642 nNumFormat = nCurFmtIndex; 3643 else 3644 { 3645 if (nCurFmtType == SvNumFormatType::UNDEFINED) 3646 nNumFormat = 0; 3647 else 3648 nNumFormat = pFormatter->GetStandardFormat( nCurFmtType, ScGlobal::eLnge); 3649 } 3650 Color* pColor; 3651 pFormatter->GetOutputString( fDouble, nNumFormat, aFilters[i].MatchValueName, &pColor); 3652 aFilters[i].MatchValue = ScDPCache::GetLocaleIndependentFormattedString( 3653 fDouble, *pFormatter, nNumFormat); 3654 } 3655 else 3656 { 3657 aFilters[i].MatchValueName = aSharedString.getString(); 3658 3659 // Parse possible number from MatchValueName and format 3660 // locale independent as MatchValue. 3661 sal_uInt32 nNumFormat = 0; 3662 double fValue; 3663 if (pFormatter->IsNumberFormat( aFilters[i].MatchValueName, nNumFormat, fValue)) 3664 aFilters[i].MatchValue = ScDPCache::GetLocaleIndependentFormattedString( 3665 fValue, *pFormatter, nNumFormat); 3666 else 3667 aFilters[i].MatchValue = aFilters[i].MatchValueName; 3668 } 3669 3670 aFilters[i].FieldName = GetString().getString(); 3671 } 3672 3673 switch (GetStackType()) 3674 { 3675 case svDoubleRef : 3676 PopDoubleRef(aBlock); 3677 break; 3678 case svSingleRef : 3679 { 3680 ScAddress aAddr; 3681 PopSingleRef(aAddr); 3682 aBlock = aAddr; 3683 } 3684 break; 3685 default: 3686 PushError(FormulaError::NoRef); 3687 return; 3688 } 3689 3690 aDataFieldName = GetString().getString(); // First parameter is data field name. 3691 } 3692 3693 // Early bail-out, don't grind through data pilot cache and all. 3694 if (nGlobalError != FormulaError::NONE) 3695 { 3696 PushError( nGlobalError); 3697 return; 3698 } 3699 3700 // NOTE : MS Excel docs claim to use the 'most recent' which is not 3701 // exactly the same as what we do in ScDocument::GetDPAtBlock 3702 // However we do need to use GetDPABlock 3703 ScDPObject* pDPObj = pDok->GetDPAtBlock(aBlock); 3704 if (!pDPObj) 3705 { 3706 PushError(FormulaError::NoRef); 3707 return; 3708 } 3709 3710 if (bOldSyntax) 3711 { 3712 OUString aFilterStr = aDataFieldName; 3713 std::vector<sal_Int16> aFilterFuncs; 3714 if (!pDPObj->ParseFilters(aDataFieldName, aFilters, aFilterFuncs, aFilterStr)) 3715 { 3716 PushError(FormulaError::NoRef); 3717 return; 3718 } 3719 3720 // TODO : For now, we ignore filter functions since we couldn't find a 3721 // live example of how they are supposed to be used. We'll support 3722 // this again once we come across a real-world example. 3723 } 3724 3725 double fVal = pDPObj->GetPivotData(aDataFieldName, aFilters); 3726 if (rtl::math::isNan(fVal)) 3727 { 3728 PushError(FormulaError::NoRef); 3729 return; 3730 } 3731 PushDouble(fVal); 3732 } 3733 3734 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ 3735