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