1 /************************************************************************* 2 * 3 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. 4 * 5 * Copyright 2000, 2010 Oracle and/or its affiliates. 6 * 7 * OpenOffice.org - a multi-platform office productivity suite 8 * 9 * This file is part of OpenOffice.org. 10 * 11 * OpenOffice.org is free software: you can redistribute it and/or modify 12 * it under the terms of the GNU Lesser General Public License version 3 13 * only, as published by the Free Software Foundation. 14 * 15 * OpenOffice.org is distributed in the hope that it will be useful, 16 * but WITHOUT ANY WARRANTY; without even the implied warranty of 17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18 * GNU Lesser General Public License version 3 for more details 19 * (a copy is included in the LICENSE file that accompanied this code). 20 * 21 * You should have received a copy of the GNU Lesser General Public License 22 * version 3 along with OpenOffice.org. If not, see 23 * <http://www.openoffice.org/license.html> 24 * for a copy of the LGPLv3 License. 25 * 26 ************************************************************************/ 27 28 // MARKER(update_precomp.py): autogen include statement, do not remove 29 #include "precompiled_sc.hxx" 30 31 // INCLUDE --------------------------------------------------------------- 32 33 #include <sfx2/linkmgr.hxx> 34 #include <sfx2/dispatch.hxx> 35 #include <sfx2/objsh.hxx> 36 #include <svl/stritem.hxx> 37 #include <svl/zforlist.hxx> 38 #include <rtl/logfile.hxx> 39 40 #include "interpre.hxx" 41 #include "attrib.hxx" 42 #include "sc.hrc" 43 #include "ddelink.hxx" 44 #include "scmatrix.hxx" 45 #include "compiler.hxx" 46 #include "cell.hxx" 47 #include "document.hxx" 48 #include "dociter.hxx" 49 #include "docoptio.hxx" 50 #include "unitconv.hxx" 51 #include "globstr.hrc" 52 #include "hints.hxx" 53 #include "dpobject.hxx" 54 #include "postit.hxx" 55 56 #include <string.h> 57 #include <math.h> 58 59 using namespace formula; 60 // STATIC DATA ----------------------------------------------------------- 61 62 #define D_TIMEFACTOR 86400.0 63 #define SCdEpsilon 1.0E-7 64 65 //----------------------------------------------------------------------------- 66 // Datum und Zeit 67 //----------------------------------------------------------------------------- 68 69 double ScInterpreter::GetDateSerial( sal_Int16 nYear, sal_Int16 nMonth, sal_Int16 nDay, bool bStrict ) 70 { 71 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetDateSerial" ); 72 if ( nYear < 100 && !bStrict ) 73 nYear = pFormatter->ExpandTwoDigitYear( nYear ); 74 // Do not use a default Date ctor here because it asks system time with a 75 // performance penalty. 76 sal_Int16 nY, nM, nD; 77 if (bStrict) 78 nY = nYear, nM = nMonth, nD = nDay; 79 else 80 { 81 if (nMonth > 0) 82 { 83 nY = nYear + (nMonth-1) / 12; 84 nM = ((nMonth-1) % 12) + 1; 85 } 86 else 87 { 88 nY = nYear + (nMonth-12) / 12; 89 nM = 12 - (-nMonth) % 12; 90 } 91 nD = 1; 92 } 93 Date aDate( nD, nM, nY); 94 if (!bStrict) 95 aDate += nDay - 1; 96 if (aDate.IsValid()) 97 return (double) (aDate - *(pFormatter->GetNullDate())); 98 else 99 { 100 SetError(errNoValue); 101 return 0; 102 } 103 } 104 105 //----------------------------------------------------------------------------- 106 // Funktionen 107 //----------------------------------------------------------------------------- 108 109 void ScInterpreter::ScGetActDate() 110 { 111 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetActDate" ); 112 nFuncFmtType = NUMBERFORMAT_DATE; 113 Date aActDate; 114 long nDiff = aActDate - *(pFormatter->GetNullDate()); 115 PushDouble((double) nDiff); 116 } 117 118 void ScInterpreter::ScGetActTime() 119 { 120 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetActTime" ); 121 nFuncFmtType = NUMBERFORMAT_DATETIME; 122 Date aActDate; 123 long nDiff = aActDate - *(pFormatter->GetNullDate()); 124 Time aActTime; 125 double nTime = ((double)aActTime.Get100Sec() / 100 + 126 (double)(aActTime.GetSec() + 127 (aActTime.GetMin() * 60) + 128 (aActTime.GetHour() * 3600))) / D_TIMEFACTOR; 129 PushDouble( (double) nDiff + nTime ); 130 } 131 132 void ScInterpreter::ScGetYear() 133 { 134 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetYear" ); 135 Date aDate = *(pFormatter->GetNullDate()); 136 aDate += (long) ::rtl::math::approxFloor(GetDouble()); 137 PushDouble( (double) aDate.GetYear() ); 138 } 139 140 void ScInterpreter::ScGetMonth() 141 { 142 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetMonth" ); 143 Date aDate = *(pFormatter->GetNullDate()); 144 aDate += (long) ::rtl::math::approxFloor(GetDouble()); 145 PushDouble( (double) aDate.GetMonth() ); 146 } 147 148 void ScInterpreter::ScGetDay() 149 { 150 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetDay" ); 151 Date aDate = *(pFormatter->GetNullDate()); 152 aDate += (long)::rtl::math::approxFloor(GetDouble()); 153 PushDouble((double) aDate.GetDay()); 154 } 155 156 void ScInterpreter::ScGetMin() 157 { 158 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetMin" ); 159 double fTime = GetDouble(); 160 fTime -= ::rtl::math::approxFloor(fTime); // Datumsanteil weg 161 long nVal = (long)::rtl::math::approxFloor(fTime*D_TIMEFACTOR+0.5) % 3600; 162 PushDouble( (double) (nVal/60) ); 163 } 164 165 void ScInterpreter::ScGetSec() 166 { 167 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetSec" ); 168 double fTime = GetDouble(); 169 fTime -= ::rtl::math::approxFloor(fTime); // Datumsanteil weg 170 long nVal = (long)::rtl::math::approxFloor(fTime*D_TIMEFACTOR+0.5) % 60; 171 PushDouble( (double) nVal ); 172 } 173 174 void ScInterpreter::ScGetHour() 175 { 176 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetHour" ); 177 double fTime = GetDouble(); 178 fTime -= ::rtl::math::approxFloor(fTime); // Datumsanteil weg 179 long nVal = (long)::rtl::math::approxFloor(fTime*D_TIMEFACTOR+0.5) / 3600; 180 PushDouble((double) nVal); 181 } 182 183 void ScInterpreter::ScGetDateValue() 184 { 185 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetDateValue" ); 186 String aInputString = GetString(); 187 sal_uInt32 nFIndex = 0; // damit default Land/Spr. 188 double fVal; 189 if (pFormatter->IsNumberFormat(aInputString, nFIndex, fVal)) 190 { 191 short eType = pFormatter->GetType(nFIndex); 192 if (eType == NUMBERFORMAT_DATE || eType == NUMBERFORMAT_DATETIME) 193 PushDouble(::rtl::math::approxFloor(fVal)); 194 else 195 PushIllegalArgument(); 196 } 197 else 198 PushIllegalArgument(); 199 } 200 201 void ScInterpreter::ScGetDayOfWeek() 202 { 203 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetDayOfWeek" ); 204 sal_uInt8 nParamCount = GetByte(); 205 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 206 { 207 short nFlag; 208 if (nParamCount == 2) 209 nFlag = (short) ::rtl::math::approxFloor(GetDouble()); 210 else 211 nFlag = 1; 212 213 Date aDate = *(pFormatter->GetNullDate()); 214 aDate += (long)::rtl::math::approxFloor(GetDouble()); 215 int nVal = (int) aDate.GetDayOfWeek(); 216 if (nFlag == 1) 217 { 218 if (nVal == 6) 219 nVal = 1; 220 else 221 nVal += 2; 222 } 223 else if (nFlag == 2) 224 nVal += 1; 225 PushInt( nVal ); 226 } 227 } 228 229 void ScInterpreter::ScGetWeekOfYear() 230 { 231 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetWeekOfYear" ); 232 if ( MustHaveParamCount( GetByte(), 2 ) ) 233 { 234 short nFlag = (short) ::rtl::math::approxFloor(GetDouble()); 235 236 Date aDate = *(pFormatter->GetNullDate()); 237 aDate += (long)::rtl::math::approxFloor(GetDouble()); 238 PushInt( (int) aDate.GetWeekOfYear( nFlag == 1 ? SUNDAY : MONDAY )); 239 } 240 } 241 242 void ScInterpreter::ScEasterSunday() 243 { 244 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScEasterSunday" ); 245 nFuncFmtType = NUMBERFORMAT_DATE; 246 if ( MustHaveParamCount( GetByte(), 1 ) ) 247 { 248 sal_Int16 nDay, nMonth, nYear; 249 nYear = (sal_Int16) ::rtl::math::approxFloor( GetDouble() ); 250 if ( nYear < 100 ) 251 nYear = pFormatter->ExpandTwoDigitYear( nYear ); 252 // don't worry, be happy :) 253 int B,C,D,E,F,G,H,I,K,L,M,N,O; 254 N = nYear % 19; 255 B = int(nYear / 100); 256 C = nYear % 100; 257 D = int(B / 4); 258 E = B % 4; 259 F = int((B + 8) / 25); 260 G = int((B - F + 1) / 3); 261 H = (19 * N + B - D - G + 15) % 30; 262 I = int(C / 4); 263 K = C % 4; 264 L = (32 + 2 * E + 2 * I - H - K) % 7; 265 M = int((N + 11 * H + 22 * L) / 451); 266 O = H + L - 7 * M + 114; 267 nDay = sal::static_int_cast<sal_Int16>( O % 31 + 1 ); 268 nMonth = sal::static_int_cast<sal_Int16>( int(O / 31) ); 269 PushDouble( GetDateSerial( nYear, nMonth, nDay, true ) ); 270 } 271 } 272 273 void ScInterpreter::ScGetDate() 274 { 275 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetDate" ); 276 nFuncFmtType = NUMBERFORMAT_DATE; 277 if ( MustHaveParamCount( GetByte(), 3 ) ) 278 { 279 sal_Int16 nDay = (sal_Int16) ::rtl::math::approxFloor(GetDouble()); 280 sal_Int16 nMonth = (sal_Int16) ::rtl::math::approxFloor(GetDouble()); 281 sal_Int16 nYear = (sal_Int16) ::rtl::math::approxFloor(GetDouble()); 282 if (nYear < 0) 283 PushIllegalArgument(); 284 else 285 { 286 PushDouble(GetDateSerial(nYear, nMonth, nDay, false)); 287 } 288 } 289 } 290 291 void ScInterpreter::ScGetTime() 292 { 293 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetTime" ); 294 nFuncFmtType = NUMBERFORMAT_TIME; 295 if ( MustHaveParamCount( GetByte(), 3 ) ) 296 { 297 double nSec = GetDouble(); 298 double nMin = GetDouble(); 299 double nHour = GetDouble(); 300 double fTime = fmod( (nHour * 3600) + (nMin * 60) + nSec, D_TIMEFACTOR) / D_TIMEFACTOR; 301 if (fTime < 0) 302 PushIllegalArgument(); 303 else 304 PushDouble( fTime); 305 } 306 } 307 308 void ScInterpreter::ScGetDiffDate() 309 { 310 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetDiffDate" ); 311 if ( MustHaveParamCount( GetByte(), 2 ) ) 312 { 313 double nDate2 = GetDouble(); 314 double nDate1 = GetDouble(); 315 PushDouble(nDate1 - nDate2); 316 } 317 } 318 319 void ScInterpreter::ScGetDiffDate360() 320 { 321 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetDiffDate360" ); 322 /* Implementation follows 323 * http://www.bondmarkets.com/eCommerce/SMD_Fields_030802.pdf 324 * Appendix B: Day-Count Bases, there are 7 different ways to calculate the 325 * 30-days count. That document also claims that Excel implements the "PSA 326 * 30" or "NASD 30" method (funny enough they also state that Excel is the 327 * only tool that does so). 328 * 329 * Note that the definiton given in 330 * http://msdn.microsoft.com/library/en-us/office97/html/SEB7C.asp 331 * is _not_ the way how it is actually calculated by Excel (that would not 332 * even match any of the 7 methods mentioned above) and would result in the 333 * following test cases producing wrong results according to that appendix B: 334 * 335 * 28-Feb-95 31-Aug-95 181 instead of 180 336 * 29-Feb-96 31-Aug-96 181 instead of 180 337 * 30-Jan-96 31-Mar-96 61 instead of 60 338 * 31-Jan-96 31-Mar-96 61 instead of 60 339 * 340 * Still, there is a difference between OOoCalc and Excel: 341 * In Excel: 342 * 02-Feb-99 31-Mar-00 results in 419 343 * 31-Mar-00 02-Feb-99 results in -418 344 * In Calc the result is 419 respectively -419. I consider the -418 a bug in Excel. 345 */ 346 347 sal_uInt8 nParamCount = GetByte(); 348 if ( MustHaveParamCount( nParamCount, 2, 3 ) ) 349 { 350 sal_Bool bFlag; 351 if (nParamCount == 3) 352 bFlag = GetBool(); 353 else 354 bFlag = sal_False; 355 double nDate2 = GetDouble(); 356 double nDate1 = GetDouble(); 357 double fSign; 358 if (nGlobalError) 359 PushError( nGlobalError); 360 else 361 { 362 // #i84934# only for non-US European algorithm swap dates. Else 363 // follow Excel's meaningless extrapolation for "interoperability". 364 if (bFlag && (nDate2 < nDate1)) 365 { 366 fSign = nDate1; 367 nDate1 = nDate2; 368 nDate2 = fSign; 369 fSign = -1.0; 370 } 371 else 372 fSign = 1.0; 373 Date aDate1 = *(pFormatter->GetNullDate()); 374 aDate1 += (long) ::rtl::math::approxFloor(nDate1); 375 Date aDate2 = *(pFormatter->GetNullDate()); 376 aDate2 += (long) ::rtl::math::approxFloor(nDate2); 377 if (aDate1.GetDay() == 31) 378 aDate1 -= (sal_uLong) 1; 379 else if (!bFlag) 380 { 381 if (aDate1.GetMonth() == 2) 382 { 383 switch ( aDate1.GetDay() ) 384 { 385 case 28 : 386 if ( !aDate1.IsLeapYear() ) 387 aDate1.SetDay(30); 388 break; 389 case 29 : 390 aDate1.SetDay(30); 391 break; 392 } 393 } 394 } 395 if (aDate2.GetDay() == 31) 396 { 397 if (!bFlag ) 398 { 399 if (aDate1.GetDay() == 30) 400 aDate2 -= (sal_uLong) 1; 401 } 402 else 403 aDate2.SetDay(30); 404 } 405 PushDouble( fSign * (double) 406 ( (double) aDate2.GetDay() + (double) aDate2.GetMonth() * 30.0 + 407 (double) aDate2.GetYear() * 360.0 408 - (double) aDate1.GetDay() - (double) aDate1.GetMonth() * 30.0 409 - (double)aDate1.GetYear() * 360.0) ); 410 } 411 } 412 } 413 414 void ScInterpreter::ScGetTimeValue() 415 { 416 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetTimeValue" ); 417 String aInputString = GetString(); 418 sal_uInt32 nFIndex = 0; // damit default Land/Spr. 419 double fVal; 420 if (pFormatter->IsNumberFormat(aInputString, nFIndex, fVal)) 421 { 422 short eType = pFormatter->GetType(nFIndex); 423 if (eType == NUMBERFORMAT_TIME || eType == NUMBERFORMAT_DATETIME) 424 { 425 double fDateVal = rtl::math::approxFloor(fVal); 426 double fTimeVal = fVal - fDateVal; 427 PushDouble(fTimeVal); 428 } 429 else 430 PushIllegalArgument(); 431 } 432 else 433 PushIllegalArgument(); 434 } 435 436 void ScInterpreter::ScPlusMinus() 437 { 438 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScPlusMinus" ); 439 double nVal = GetDouble(); 440 short n = 0; 441 if (nVal < 0.0) 442 n = -1; 443 else if (nVal > 0.0) 444 n = 1; 445 PushInt( n ); 446 } 447 448 void ScInterpreter::ScAbs() 449 { 450 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScAbs" ); 451 PushDouble(fabs(GetDouble())); 452 } 453 454 void ScInterpreter::ScInt() 455 { 456 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScInt" ); 457 PushDouble(::rtl::math::approxFloor(GetDouble())); 458 } 459 460 461 void ScInterpreter::RoundNumber( rtl_math_RoundingMode eMode ) 462 { 463 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::RoundNumber" ); 464 sal_uInt8 nParamCount = GetByte(); 465 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 466 { 467 double fVal = 0.0; 468 if (nParamCount == 1) 469 fVal = ::rtl::math::round( GetDouble(), 0, eMode ); 470 else 471 { 472 sal_Int32 nDec = (sal_Int32) ::rtl::math::approxFloor(GetDouble()); 473 if( nDec < -20 || nDec > 20 ) 474 PushIllegalArgument(); 475 else 476 fVal = ::rtl::math::round( GetDouble(), (short)nDec, eMode ); 477 } 478 PushDouble(fVal); 479 } 480 } 481 482 void ScInterpreter::ScRound() 483 { 484 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRound" ); 485 RoundNumber( rtl_math_RoundingMode_Corrected ); 486 } 487 488 void ScInterpreter::ScRoundDown() 489 { 490 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRoundDown" ); 491 RoundNumber( rtl_math_RoundingMode_Down ); 492 } 493 494 void ScInterpreter::ScRoundUp() 495 { 496 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRoundUp" ); 497 RoundNumber( rtl_math_RoundingMode_Up ); 498 } 499 500 void ScInterpreter::ScCeil() 501 { 502 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCeil" ); 503 sal_uInt8 nParamCount = GetByte(); 504 if ( MustHaveParamCount( nParamCount, 2, 3 ) ) 505 { 506 sal_Bool bAbs = ( nParamCount == 3 ? GetBool() : sal_False ); 507 double fDec = GetDouble(); 508 double fVal = GetDouble(); 509 if ( fDec == 0.0 ) 510 PushInt(0); 511 else if (fVal*fDec < 0.0) 512 PushIllegalArgument(); 513 else 514 { 515 if ( !bAbs && fVal < 0.0 ) 516 PushDouble(::rtl::math::approxFloor(fVal/fDec) * fDec); 517 else 518 PushDouble(::rtl::math::approxCeil(fVal/fDec) * fDec); 519 } 520 } 521 } 522 523 void ScInterpreter::ScFloor() 524 { 525 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFloor" ); 526 sal_uInt8 nParamCount = GetByte(); 527 if ( MustHaveParamCount( nParamCount, 2, 3 ) ) 528 { 529 sal_Bool bAbs = ( nParamCount == 3 ? GetBool() : sal_False ); 530 double fDec = GetDouble(); 531 double fVal = GetDouble(); 532 if ( fDec == 0.0 ) 533 PushInt(0); 534 else if (fVal*fDec < 0.0) 535 PushIllegalArgument(); 536 else 537 { 538 if ( !bAbs && fVal < 0.0 ) 539 PushDouble(::rtl::math::approxCeil(fVal/fDec) * fDec); 540 else 541 PushDouble(::rtl::math::approxFloor(fVal/fDec) * fDec); 542 } 543 } 544 } 545 546 void ScInterpreter::ScEven() 547 { 548 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScEven" ); 549 double fVal = GetDouble(); 550 if (fVal < 0.0) 551 PushDouble(::rtl::math::approxFloor(fVal/2.0) * 2.0); 552 else 553 PushDouble(::rtl::math::approxCeil(fVal/2.0) * 2.0); 554 } 555 556 void ScInterpreter::ScOdd() 557 { 558 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScOdd" ); 559 double fVal = GetDouble(); 560 if (fVal >= 0.0) 561 { 562 fVal = ::rtl::math::approxCeil(fVal); 563 if (fmod(fVal, 2.0) == 0.0) 564 fVal += 1.0; 565 } 566 else 567 { 568 fVal = ::rtl::math::approxFloor(fVal); 569 if (fmod(fVal, 2.0) == 0.0) 570 fVal -= 1.0; 571 } 572 PushDouble(fVal); 573 } 574 575 void ScInterpreter::ScArcTan2() 576 { 577 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArcTan2" ); 578 if ( MustHaveParamCount( GetByte(), 2 ) ) 579 { 580 double nVal2 = GetDouble(); 581 double nVal1 = GetDouble(); 582 PushDouble(atan2(nVal2, nVal1)); 583 } 584 } 585 586 void ScInterpreter::ScLog() 587 { 588 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLog" ); 589 sal_uInt8 nParamCount = GetByte(); 590 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 591 { 592 double nBase; 593 if (nParamCount == 2) 594 nBase = GetDouble(); 595 else 596 nBase = 10.0; 597 double nVal = GetDouble(); 598 if (nVal > 0.0 && nBase > 0.0 && nBase != 1.0) 599 PushDouble(log(nVal) / log(nBase)); 600 else 601 PushIllegalArgument(); 602 } 603 } 604 605 void ScInterpreter::ScLn() 606 { 607 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLn" ); 608 double fVal = GetDouble(); 609 if (fVal > 0.0) 610 PushDouble(log(fVal)); 611 else 612 PushIllegalArgument(); 613 } 614 615 void ScInterpreter::ScLog10() 616 { 617 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLog10" ); 618 double fVal = GetDouble(); 619 if (fVal > 0.0) 620 PushDouble(log10(fVal)); 621 else 622 PushIllegalArgument(); 623 } 624 625 void ScInterpreter::ScNPV() 626 { 627 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNPV" ); 628 nFuncFmtType = NUMBERFORMAT_CURRENCY; 629 short nParamCount = GetByte(); 630 if ( MustHaveParamCount( nParamCount, 2, 31 ) ) 631 { 632 double nVal = 0.0; 633 // Wir drehen den Stack um!! 634 FormulaToken* pTemp[ 31 ]; 635 for( short i = 0; i < nParamCount; i++ ) 636 pTemp[ i ] = pStack[ sp - i - 1 ]; 637 memcpy( &pStack[ sp - nParamCount ], pTemp, nParamCount * sizeof( FormulaToken* ) ); 638 if (nGlobalError == 0) 639 { 640 double nCount = 1.0; 641 double nZins = GetDouble(); 642 --nParamCount; 643 size_t nRefInList = 0; 644 ScRange aRange; 645 while (nParamCount-- > 0) 646 { 647 switch (GetStackType()) 648 { 649 case svDouble : 650 { 651 nVal += (GetDouble() / pow(1.0 + nZins, (double)nCount)); 652 nCount++; 653 } 654 break; 655 case svSingleRef : 656 { 657 ScAddress aAdr; 658 PopSingleRef( aAdr ); 659 ScBaseCell* pCell = GetCell( aAdr ); 660 if (!HasCellEmptyData(pCell) && HasCellValueData(pCell)) 661 { 662 double nCellVal = GetCellValue( aAdr, pCell ); 663 nVal += (nCellVal / pow(1.0 + nZins, (double)nCount)); 664 nCount++; 665 } 666 } 667 break; 668 case svDoubleRef : 669 case svRefList : 670 { 671 sal_uInt16 nErr = 0; 672 double nCellVal; 673 PopDoubleRef( aRange, nParamCount, nRefInList); 674 ScHorizontalValueIterator aValIter( pDok, aRange, glSubTotal); 675 while ((nErr == 0) && aValIter.GetNext(nCellVal, nErr)) 676 { 677 nVal += (nCellVal / pow(1.0 + nZins, (double)nCount)); 678 nCount++; 679 } 680 if ( nErr != 0 ) 681 SetError(nErr); 682 } 683 break; 684 default : SetError(errIllegalParameter); break; 685 } 686 } 687 } 688 PushDouble(nVal); 689 } 690 } 691 692 void ScInterpreter::ScIRR() 693 { 694 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIRR" ); 695 double fSchaetzwert; 696 nFuncFmtType = NUMBERFORMAT_PERCENT; 697 sal_uInt8 nParamCount = GetByte(); 698 if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) 699 return; 700 if (nParamCount == 2) 701 fSchaetzwert = GetDouble(); 702 else 703 fSchaetzwert = 0.1; 704 sal_uInt16 sPos = sp; // Stack-Position merken 705 double fEps = 1.0; 706 double x, xNeu, fWert, fZaehler, fNenner, nCount; 707 if (fSchaetzwert == -1.0) 708 x = 0.1; // default gegen Nulldivisionen 709 else 710 x = fSchaetzwert; // Startwert 711 switch (GetStackType()) 712 { 713 case svDoubleRef : 714 break; 715 default: 716 { 717 PushIllegalParameter(); 718 return; 719 } 720 } 721 const sal_uInt16 nIterationsMax = 20; 722 sal_uInt16 nItCount = 0; 723 ScRange aRange; 724 while (fEps > SCdEpsilon && nItCount < nIterationsMax) 725 { // Newton-Verfahren: 726 sp = sPos; // Stack zuruecksetzen 727 nCount = 0.0; 728 fZaehler = 0.0; 729 fNenner = 0.0; 730 sal_uInt16 nErr = 0; 731 PopDoubleRef( aRange ); 732 ScValueIterator aValIter(pDok, aRange, glSubTotal); 733 if (aValIter.GetFirst(fWert, nErr)) 734 { 735 fZaehler += fWert / pow(1.0+x,(double)nCount); 736 fNenner += -nCount * fWert / pow(1.0+x,nCount+1.0); 737 nCount++; 738 while ((nErr == 0) && aValIter.GetNext(fWert, nErr)) 739 { 740 fZaehler += fWert / pow(1.0+x,(double)nCount); 741 fNenner += -nCount * fWert / pow(1.0+x,nCount+1.0); 742 nCount++; 743 } 744 SetError(nErr); 745 } 746 xNeu = x - fZaehler / fNenner; // x(i+1) = x(i)-f(x(i))/f'(x(i)) 747 nItCount++; 748 fEps = fabs(xNeu - x); 749 x = xNeu; 750 } 751 if (fSchaetzwert == 0.0 && fabs(x) < SCdEpsilon) 752 x = 0.0; // auf Null normieren 753 if (fEps < SCdEpsilon) 754 PushDouble(x); 755 else 756 PushError( errNoConvergence); 757 } 758 759 void ScInterpreter::ScMIRR() 760 { // range_of_values ; rate_invest ; rate_reinvest 761 nFuncFmtType = NUMBERFORMAT_PERCENT; 762 if( MustHaveParamCount( GetByte(), 3 ) ) 763 { 764 double fRate1_reinvest = GetDouble() + 1; 765 double fNPV_reinvest = 0.0; 766 double fPow_reinvest = 1.0; 767 768 double fRate1_invest = GetDouble() + 1; 769 double fNPV_invest = 0.0; 770 double fPow_invest = 1.0; 771 772 ScRange aRange; 773 PopDoubleRef( aRange ); 774 775 if( nGlobalError ) 776 PushError( nGlobalError); 777 else 778 { 779 ScValueIterator aValIter( pDok, aRange, glSubTotal ); 780 double fCellValue; 781 sal_uLong nCount = 0; 782 sal_uInt16 nIterError = 0; 783 784 sal_Bool bLoop = aValIter.GetFirst( fCellValue, nIterError ); 785 while( bLoop ) 786 { 787 if( fCellValue > 0.0 ) // reinvestments 788 fNPV_reinvest += fCellValue * fPow_reinvest; 789 else if( fCellValue < 0.0 ) // investments 790 fNPV_invest += fCellValue * fPow_invest; 791 fPow_reinvest /= fRate1_reinvest; 792 fPow_invest /= fRate1_invest; 793 nCount++; 794 795 bLoop = aValIter.GetNext( fCellValue, nIterError ); 796 } 797 if( nIterError ) 798 PushError( nIterError ); 799 else 800 { 801 double fResult = -fNPV_reinvest / fNPV_invest; 802 fResult *= pow( fRate1_reinvest, (double) nCount - 1 ); 803 fResult = pow( fResult, 1.0 / (nCount - 1) ); 804 PushDouble( fResult - 1.0 ); 805 } 806 } 807 } 808 } 809 810 811 void ScInterpreter::ScISPMT() 812 { // rate ; period ; total_periods ; invest 813 if( MustHaveParamCount( GetByte(), 4 ) ) 814 { 815 double fInvest = GetDouble(); 816 double fTotal = GetDouble(); 817 double fPeriod = GetDouble(); 818 double fRate = GetDouble(); 819 820 if( nGlobalError ) 821 PushError( nGlobalError); 822 else 823 PushDouble( fInvest * fRate * (fPeriod / fTotal - 1.0) ); 824 } 825 } 826 827 828 //----------------------- Finanzfunktionen ------------------------------------ 829 830 double ScInterpreter::ScGetBw(double fZins, double fZzr, double fRmz, 831 double fZw, double fF) 832 { 833 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScMIRR" ); 834 double fBw; 835 if (fZins == 0.0) 836 fBw = fZw + fRmz * fZzr; 837 else if (fF > 0.0) 838 fBw = (fZw * pow(1.0 + fZins, -fZzr)) 839 + (fRmz * (1.0 - pow(1.0 + fZins, -fZzr + 1.0)) / fZins) 840 + fRmz; 841 else 842 fBw = (fZw * pow(1.0 + fZins, -fZzr)) 843 + (fRmz * (1.0 - pow(1.0 + fZins, -fZzr)) / fZins); 844 return -fBw; 845 } 846 847 void ScInterpreter::ScBW() 848 { 849 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScBW" ); 850 nFuncFmtType = NUMBERFORMAT_CURRENCY; 851 double nRmz, nZzr, nZins, nZw = 0, nFlag = 0; 852 sal_uInt8 nParamCount = GetByte(); 853 if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) 854 return; 855 if (nParamCount == 5) 856 nFlag = GetDouble(); 857 if (nParamCount >= 4) 858 nZw = GetDouble(); 859 nRmz = GetDouble(); 860 nZzr = GetDouble(); 861 nZins = GetDouble(); 862 PushDouble(ScGetBw(nZins, nZzr, nRmz, nZw, nFlag)); 863 } 864 865 void ScInterpreter::ScDIA() 866 { 867 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDIA" ); 868 nFuncFmtType = NUMBERFORMAT_CURRENCY; 869 if ( MustHaveParamCount( GetByte(), 4 ) ) 870 { 871 double nZr = GetDouble(); 872 double nDauer = GetDouble(); 873 double nRest = GetDouble(); 874 double nWert = GetDouble(); 875 double nDia = ((nWert - nRest) * (nDauer - nZr + 1.0)) / 876 ((nDauer * (nDauer + 1.0)) / 2.0); 877 PushDouble(nDia); 878 } 879 } 880 881 double ScInterpreter::ScGetGDA(double fWert, double fRest, double fDauer, 882 double fPeriode, double fFaktor) 883 { 884 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetGDA" ); 885 double fGda, fZins, fAlterWert, fNeuerWert; 886 fZins = fFaktor / fDauer; 887 if (fZins >= 1.0) 888 { 889 fZins = 1.0; 890 if (fPeriode == 1.0) 891 fAlterWert = fWert; 892 else 893 fAlterWert = 0.0; 894 } 895 else 896 fAlterWert = fWert * pow(1.0 - fZins, fPeriode - 1.0); 897 fNeuerWert = fWert * pow(1.0 - fZins, fPeriode); 898 899 if (fNeuerWert < fRest) 900 fGda = fAlterWert - fRest; 901 else 902 fGda = fAlterWert - fNeuerWert; 903 if (fGda < 0.0) 904 fGda = 0.0; 905 return fGda; 906 } 907 908 void ScInterpreter::ScGDA() 909 { 910 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGDA" ); 911 nFuncFmtType = NUMBERFORMAT_CURRENCY; 912 sal_uInt8 nParamCount = GetByte(); 913 if ( MustHaveParamCount( nParamCount, 4, 5 ) ) 914 { 915 double nFaktor; 916 if (nParamCount == 5) 917 nFaktor = GetDouble(); 918 else 919 nFaktor = 2.0; 920 double nPeriode = GetDouble(); 921 double nDauer = GetDouble(); 922 double nRest = GetDouble(); 923 double nWert = GetDouble(); 924 if (nWert < 0.0 || nRest < 0.0 || nFaktor <= 0.0 || nRest > nWert 925 || nPeriode < 1.0 || nPeriode > nDauer) 926 PushIllegalArgument(); 927 else 928 PushDouble(ScGetGDA(nWert, nRest, nDauer, nPeriode, nFaktor)); 929 } 930 } 931 932 void ScInterpreter::ScGDA2() 933 { 934 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGDA2" ); 935 nFuncFmtType = NUMBERFORMAT_CURRENCY; 936 sal_uInt8 nParamCount = GetByte(); 937 if ( !MustHaveParamCount( nParamCount, 4, 5 ) ) 938 return ; 939 double nMonate; 940 if (nParamCount == 4) 941 nMonate = 12.0; 942 else 943 nMonate = ::rtl::math::approxFloor(GetDouble()); 944 double nPeriode = GetDouble(); 945 double nDauer = GetDouble(); 946 double nRest = GetDouble(); 947 double nWert = GetDouble(); 948 if (nMonate < 1.0 || nMonate > 12.0 || nDauer > 1200.0 || nRest < 0.0 || 949 nPeriode > (nDauer + 1.0) || nRest > nWert || nWert < 0.0) 950 { 951 PushIllegalArgument(); 952 return; 953 } 954 double nAbRate = 1.0 - pow(nRest / nWert, 1.0 / nDauer); 955 nAbRate = ::rtl::math::approxFloor((nAbRate * 1000.0) + 0.5) / 1000.0; 956 double nErsteAbRate = nWert * nAbRate * nMonate / 12.0; 957 double nGda2 = 0.0; 958 if (::rtl::math::approxFloor(nPeriode) == 1) 959 nGda2 = nErsteAbRate; 960 else 961 { 962 double nSummAbRate = nErsteAbRate; 963 double nMin = nDauer; 964 if (nMin > nPeriode) nMin = nPeriode; 965 sal_uInt16 iMax = (sal_uInt16)::rtl::math::approxFloor(nMin); 966 for (sal_uInt16 i = 2; i <= iMax; i++) 967 { 968 nGda2 = (nWert - nSummAbRate) * nAbRate; 969 nSummAbRate += nGda2; 970 } 971 if (nPeriode > nDauer) 972 nGda2 = ((nWert - nSummAbRate) * nAbRate * (12.0 - nMonate)) / 12.0; 973 } 974 PushDouble(nGda2); 975 } 976 977 978 double ScInterpreter::ScInterVDB(double fWert,double fRest,double fDauer, 979 double fDauer1,double fPeriode,double fFaktor) 980 { 981 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScInterVDB" ); 982 double fVdb=0; 983 double fIntEnd = ::rtl::math::approxCeil(fPeriode); 984 sal_uLong nLoopEnd = (sal_uLong) fIntEnd; 985 986 double fTerm, fLia; 987 double fRestwert = fWert - fRest; 988 sal_Bool bNowLia = sal_False; 989 990 double fGda; 991 sal_uLong i; 992 fLia=0; 993 for ( i = 1; i <= nLoopEnd; i++) 994 { 995 if(!bNowLia) 996 { 997 fGda = ScGetGDA(fWert, fRest, fDauer, (double) i, fFaktor); 998 fLia = fRestwert/ (fDauer1 - (double) (i-1)); 999 1000 if (fLia > fGda) 1001 { 1002 fTerm = fLia; 1003 bNowLia = sal_True; 1004 } 1005 else 1006 { 1007 fTerm = fGda; 1008 fRestwert -= fGda; 1009 } 1010 } 1011 else 1012 { 1013 fTerm = fLia; 1014 } 1015 1016 if ( i == nLoopEnd) 1017 fTerm *= ( fPeriode + 1.0 - fIntEnd ); 1018 1019 fVdb += fTerm; 1020 } 1021 return fVdb; 1022 } 1023 1024 1025 inline double DblMin( double a, double b ) 1026 { 1027 return (a < b) ? a : b; 1028 } 1029 1030 void ScInterpreter::ScVDB() 1031 { 1032 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScVDB" ); 1033 nFuncFmtType = NUMBERFORMAT_CURRENCY; 1034 sal_uInt8 nParamCount = GetByte(); 1035 if ( MustHaveParamCount( nParamCount, 5, 7 ) ) 1036 { 1037 double fWert, fRest, fDauer, fAnfang, fEnde, fFaktor, fVdb = 0.0; 1038 sal_Bool bFlag; 1039 if (nParamCount == 7) 1040 bFlag = GetBool(); 1041 else 1042 bFlag = sal_False; 1043 if (nParamCount >= 6) 1044 fFaktor = GetDouble(); 1045 else 1046 fFaktor = 2.0; 1047 fEnde = GetDouble(); 1048 fAnfang = GetDouble(); 1049 fDauer = GetDouble(); 1050 fRest = GetDouble(); 1051 fWert = GetDouble(); 1052 if (fAnfang < 0.0 || fEnde < fAnfang || fEnde > fDauer || fWert < 0.0 1053 || fRest > fWert || fFaktor <= 0.0) 1054 PushIllegalArgument(); 1055 else 1056 { 1057 double fIntStart = ::rtl::math::approxFloor(fAnfang); 1058 double fIntEnd = ::rtl::math::approxCeil(fEnde); 1059 sal_uLong nLoopStart = (sal_uLong) fIntStart; 1060 sal_uLong nLoopEnd = (sal_uLong) fIntEnd; 1061 1062 fVdb = 0.0; 1063 if (bFlag) 1064 { 1065 for (sal_uLong i = nLoopStart + 1; i <= nLoopEnd; i++) 1066 { 1067 double fTerm = ScGetGDA(fWert, fRest, fDauer, (double) i, fFaktor); 1068 1069 // Teilperioden am Anfang / Ende beruecksichtigen: 1070 if ( i == nLoopStart+1 ) 1071 fTerm *= ( DblMin( fEnde, fIntStart + 1.0 ) - fAnfang ); 1072 else if ( i == nLoopEnd ) 1073 fTerm *= ( fEnde + 1.0 - fIntEnd ); 1074 1075 fVdb += fTerm; 1076 } 1077 } 1078 else 1079 { 1080 1081 double fDauer1=fDauer; 1082 double fPart; 1083 1084 //@Die Frage aller Fragen: "Ist das hier richtig" 1085 if(!::rtl::math::approxEqual(fAnfang,::rtl::math::approxFloor(fAnfang))) 1086 { 1087 if(fFaktor>1) 1088 { 1089 if(fAnfang>fDauer/2 || ::rtl::math::approxEqual(fAnfang,fDauer/2)) 1090 { 1091 fPart=fAnfang-fDauer/2; 1092 fAnfang=fDauer/2; 1093 fEnde-=fPart; 1094 fDauer1+=1; 1095 } 1096 } 1097 } 1098 1099 fWert-=ScInterVDB(fWert,fRest,fDauer,fDauer1,fAnfang,fFaktor); 1100 fVdb=ScInterVDB(fWert,fRest,fDauer,fDauer-fAnfang,fEnde-fAnfang,fFaktor); 1101 } 1102 } 1103 PushDouble(fVdb); 1104 } 1105 } 1106 1107 void ScInterpreter::ScLaufz() 1108 { 1109 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLaufz" ); 1110 if ( MustHaveParamCount( GetByte(), 3 ) ) 1111 { 1112 double nZukunft = GetDouble(); 1113 double nGegenwart = GetDouble(); 1114 double nZins = GetDouble(); 1115 PushDouble(log(nZukunft / nGegenwart) / log(1.0 + nZins)); 1116 } 1117 } 1118 1119 void ScInterpreter::ScLIA() 1120 { 1121 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLIA" ); 1122 nFuncFmtType = NUMBERFORMAT_CURRENCY; 1123 if ( MustHaveParamCount( GetByte(), 3 ) ) 1124 { 1125 double nDauer = GetDouble(); 1126 double nRest = GetDouble(); 1127 double nWert = GetDouble(); 1128 PushDouble((nWert - nRest) / nDauer); 1129 } 1130 } 1131 1132 double ScInterpreter::ScGetRmz(double fRate, double fNper, double fPv, 1133 double fFv, double fPaytype) 1134 { 1135 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetRmz" ); 1136 double fPayment; 1137 if (fRate == 0.0) 1138 fPayment = (fPv + fFv) / fNper; 1139 else 1140 { 1141 if (fPaytype > 0.0) // payment in advance 1142 fPayment = (fFv + fPv * exp( fNper * ::rtl::math::log1p(fRate) ) ) * fRate / 1143 (::rtl::math::expm1( (fNper + 1) * ::rtl::math::log1p(fRate) ) - fRate); 1144 else // payment in arrear 1145 fPayment = (fFv + fPv * exp(fNper * ::rtl::math::log1p(fRate) ) ) * fRate / 1146 ::rtl::math::expm1( fNper * ::rtl::math::log1p(fRate) ); 1147 } 1148 return -fPayment; 1149 } 1150 1151 void ScInterpreter::ScRMZ() 1152 { 1153 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRMZ" ); 1154 double nZins, nZzr, nBw, nZw = 0, nFlag = 0; 1155 nFuncFmtType = NUMBERFORMAT_CURRENCY; 1156 sal_uInt8 nParamCount = GetByte(); 1157 if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) 1158 return; 1159 if (nParamCount == 5) 1160 nFlag = GetDouble(); 1161 if (nParamCount >= 4) 1162 nZw = GetDouble(); 1163 nBw = GetDouble(); 1164 nZzr = GetDouble(); 1165 nZins = GetDouble(); 1166 PushDouble(ScGetRmz(nZins, nZzr, nBw, nZw, nFlag)); 1167 } 1168 1169 void ScInterpreter::ScZGZ() 1170 { 1171 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScZGZ" ); 1172 nFuncFmtType = NUMBERFORMAT_PERCENT; 1173 if ( MustHaveParamCount( GetByte(), 3 ) ) 1174 { 1175 double nZukunftswert = GetDouble(); 1176 double nGegenwartswert = GetDouble(); 1177 double nZeitraum = GetDouble(); 1178 PushDouble(pow(nZukunftswert / nGegenwartswert, 1.0 / nZeitraum) - 1.0); 1179 } 1180 } 1181 1182 double ScInterpreter::ScGetZw(double fZins, double fZzr, double fRmz, 1183 double fBw, double fF) 1184 { 1185 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetZw" ); 1186 double fZw; 1187 if (fZins == 0.0) 1188 fZw = fBw + fRmz * fZzr; 1189 else 1190 { 1191 double fTerm = pow(1.0 + fZins, fZzr); 1192 if (fF > 0.0) 1193 fZw = fBw * fTerm + fRmz*(1.0 + fZins)*(fTerm - 1.0)/fZins; 1194 else 1195 fZw = fBw * fTerm + fRmz*(fTerm - 1.0)/fZins; 1196 } 1197 return -fZw; 1198 } 1199 1200 void ScInterpreter::ScZW() 1201 { 1202 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScZW" ); 1203 double nZins, nZzr, nRmz, nBw = 0, nFlag = 0; 1204 nFuncFmtType = NUMBERFORMAT_CURRENCY; 1205 sal_uInt8 nParamCount = GetByte(); 1206 if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) 1207 return; 1208 if (nParamCount == 5) 1209 nFlag = GetDouble(); 1210 if (nParamCount >= 4) 1211 nBw = GetDouble(); 1212 nRmz = GetDouble(); 1213 nZzr = GetDouble(); 1214 nZins = GetDouble(); 1215 PushDouble(ScGetZw(nZins, nZzr, nRmz, nBw, nFlag)); 1216 } 1217 1218 void ScInterpreter::ScZZR() 1219 { 1220 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScZZR" ); 1221 double nZins, nRmz, nBw, nZw = 0, nFlag = 0; 1222 sal_uInt8 nParamCount = GetByte(); 1223 if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) 1224 return; 1225 if (nParamCount == 5) 1226 nFlag = GetDouble(); 1227 if (nParamCount >= 4) 1228 nZw = GetDouble(); 1229 nBw = GetDouble(); 1230 nRmz = GetDouble(); 1231 nZins = GetDouble(); 1232 if (nZins == 0.0) 1233 PushDouble(-(nBw + nZw)/nRmz); 1234 else if (nFlag > 0.0) 1235 PushDouble(log(-(nZins*nZw-nRmz*(1.0+nZins))/(nZins*nBw+nRmz*(1.0+nZins))) 1236 /log(1.0+nZins)); 1237 else 1238 PushDouble(log(-(nZins*nZw-nRmz)/(nZins*nBw+nRmz))/log(1.0+nZins)); 1239 } 1240 1241 bool ScInterpreter::RateIteration( double fNper, double fPayment, double fPv, 1242 double fFv, double fPayType, double & fGuess ) 1243 { 1244 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::RateIteration" ); 1245 // See also #i15090# 1246 // Newton-Raphson method: x(i+1) = x(i) - f(x(i)) / f'(x(i)) 1247 // This solution handles integer and non-integer values of Nper different. 1248 // If ODFF will constraint Nper to integer, the distinction of cases can be 1249 // removed; only the integer-part is needed then. 1250 bool bValid = true, bFound = false; 1251 double fX, fXnew, fTerm, fTermDerivation; 1252 double fGeoSeries, fGeoSeriesDerivation; 1253 const sal_uInt16 nIterationsMax = 150; 1254 sal_uInt16 nCount = 0; 1255 const double fEpsilonSmall = 1.0E-14; 1256 // convert any fPayType situation to fPayType == zero situation 1257 fFv = fFv - fPayment * fPayType; 1258 fPv = fPv + fPayment * fPayType; 1259 if (fNper == ::rtl::math::round( fNper, 0, rtl_math_RoundingMode_Corrected )) 1260 { // Nper is an integer value 1261 fX = fGuess; 1262 double fPowN, fPowNminus1; // for (1.0+fX)^Nper and (1.0+fX)^(Nper-1) 1263 while (!bFound && nCount < nIterationsMax) 1264 { 1265 fPowNminus1 = pow( 1.0+fX, fNper-1.0); 1266 fPowN = fPowNminus1 * (1.0+fX); 1267 if (rtl::math::approxEqual( fabs(fX), 0.0)) 1268 { 1269 fGeoSeries = fNper; 1270 fGeoSeriesDerivation = fNper * (fNper-1.0)/2.0; 1271 } 1272 else 1273 { 1274 fGeoSeries = (fPowN-1.0)/fX; 1275 fGeoSeriesDerivation = fNper * fPowNminus1 / fX - fGeoSeries / fX; 1276 } 1277 fTerm = fFv + fPv *fPowN+ fPayment * fGeoSeries; 1278 fTermDerivation = fPv * fNper * fPowNminus1 + fPayment * fGeoSeriesDerivation; 1279 if (fabs(fTerm) < fEpsilonSmall) 1280 bFound = true; // will catch root which is at an extreme 1281 else 1282 { 1283 if (rtl::math::approxEqual( fabs(fTermDerivation), 0.0)) 1284 fXnew = fX + 1.1 * SCdEpsilon; // move away from zero slope 1285 else 1286 fXnew = fX - fTerm / fTermDerivation; 1287 nCount++; 1288 // more accuracy not possible in oscillating cases 1289 bFound = (fabs(fXnew - fX) < SCdEpsilon); 1290 fX = fXnew; 1291 } 1292 } 1293 // Gnumeric returns roots < -1, Excel gives an error in that cases, 1294 // ODFF says nothing about it. Enable the statement, if you want Excel's 1295 // behavior 1296 //bValid =(fX >=-1.0); 1297 } 1298 else 1299 { // Nper is not an integer value. 1300 fX = (fGuess < -1.0) ? -1.0 : fGuess; // start with a valid fX 1301 while (bValid && !bFound && nCount < nIterationsMax) 1302 { 1303 if (rtl::math::approxEqual( fabs(fX), 0.0)) 1304 { 1305 fGeoSeries = fNper; 1306 fGeoSeriesDerivation = fNper * (fNper-1.0)/2.0; 1307 } 1308 else 1309 { 1310 fGeoSeries = (pow( 1.0+fX, fNper) - 1.0) / fX; 1311 fGeoSeriesDerivation = fNper * pow( 1.0+fX, fNper-1.0) / fX - fGeoSeries / fX; 1312 } 1313 fTerm = fFv + fPv *pow(1.0 + fX,fNper)+ fPayment * fGeoSeries; 1314 fTermDerivation = fPv * fNper * pow( 1.0+fX, fNper-1.0) + fPayment * fGeoSeriesDerivation; 1315 if (fabs(fTerm) < fEpsilonSmall) 1316 bFound = true; // will catch root which is at an extreme 1317 else 1318 { 1319 if (rtl::math::approxEqual( fabs(fTermDerivation), 0.0)) 1320 fXnew = fX + 1.1 * SCdEpsilon; // move away from zero slope 1321 else 1322 fXnew = fX - fTerm / fTermDerivation; 1323 nCount++; 1324 // more accuracy not possible in oscillating cases 1325 bFound = (fabs(fXnew - fX) < SCdEpsilon); 1326 fX = fXnew; 1327 bValid = (fX >= -1.0); // otherwise pow(1.0+fX,fNper) will fail 1328 } 1329 } 1330 } 1331 fGuess = fX; // return approximate root 1332 return bValid && bFound; 1333 } 1334 1335 // In Calc UI it is the function RATE(Nper;Pmt;Pv;Fv;Type;Guess) 1336 void ScInterpreter::ScZins() 1337 { 1338 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScZins" ); 1339 double fPv, fPayment, fNper; 1340 // defaults for missing arguments, see ODFF spec 1341 double fFv = 0, fPayType = 0, fGuess = 0.1; 1342 bool bValid = true; 1343 nFuncFmtType = NUMBERFORMAT_PERCENT; 1344 sal_uInt8 nParamCount = GetByte(); 1345 if ( !MustHaveParamCount( nParamCount, 3, 6 ) ) 1346 return; 1347 if (nParamCount == 6) 1348 fGuess = GetDouble(); 1349 if (nParamCount >= 5) 1350 fPayType = GetDouble(); 1351 if (nParamCount >= 4) 1352 fFv = GetDouble(); 1353 fPv = GetDouble(); 1354 fPayment = GetDouble(); 1355 fNper = GetDouble(); 1356 if (fNper <= 0.0) // constraint from ODFF spec 1357 { 1358 PushIllegalArgument(); 1359 return; 1360 } 1361 // other values for fPayType might be meaningful, 1362 // ODFF spec is not clear yet, enable statement if you want only 0 and 1 1363 //if (fPayType != 0.0) fPayType = 1.0; 1364 bValid = RateIteration(fNper, fPayment, fPv, fFv, fPayType, fGuess); 1365 if (!bValid) 1366 SetError(errNoConvergence); 1367 PushDouble(fGuess); 1368 } 1369 1370 double ScInterpreter::ScGetZinsZ(double fZins, double fZr, double fZzr, double fBw, 1371 double fZw, double fF, double& fRmz) 1372 { 1373 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetZinsZ" ); 1374 fRmz = ScGetRmz(fZins, fZzr, fBw, fZw, fF); // fuer kapz auch bei fZr == 1 1375 double fZinsZ; 1376 nFuncFmtType = NUMBERFORMAT_CURRENCY; 1377 if (fZr == 1.0) 1378 { 1379 if (fF > 0.0) 1380 fZinsZ = 0.0; 1381 else 1382 fZinsZ = -fBw; 1383 } 1384 else 1385 { 1386 if (fF > 0.0) 1387 fZinsZ = ScGetZw(fZins, fZr-2.0, fRmz, fBw, 1.0) - fRmz; 1388 else 1389 fZinsZ = ScGetZw(fZins, fZr-1.0, fRmz, fBw, 0.0); 1390 } 1391 return fZinsZ * fZins; 1392 } 1393 1394 void ScInterpreter::ScZinsZ() 1395 { 1396 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScZinsZ" ); 1397 double nZins, nZr, nRmz, nZzr, nBw, nZw = 0, nFlag = 0; 1398 nFuncFmtType = NUMBERFORMAT_CURRENCY; 1399 sal_uInt8 nParamCount = GetByte(); 1400 if ( !MustHaveParamCount( nParamCount, 4, 6 ) ) 1401 return; 1402 if (nParamCount == 6) 1403 nFlag = GetDouble(); 1404 if (nParamCount >= 5) 1405 nZw = GetDouble(); 1406 nBw = GetDouble(); 1407 nZzr = GetDouble(); 1408 nZr = GetDouble(); 1409 nZins = GetDouble(); 1410 if (nZr < 1.0 || nZr > nZzr) 1411 PushIllegalArgument(); 1412 else 1413 PushDouble(ScGetZinsZ(nZins, nZr, nZzr, nBw, nZw, nFlag, nRmz)); 1414 } 1415 1416 void ScInterpreter::ScKapz() 1417 { 1418 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScKapz" ); 1419 double nZins, nZr, nZzr, nBw, nZw = 0, nFlag = 0, nRmz, nZinsz; 1420 nFuncFmtType = NUMBERFORMAT_CURRENCY; 1421 sal_uInt8 nParamCount = GetByte(); 1422 if ( !MustHaveParamCount( nParamCount, 4, 6 ) ) 1423 return; 1424 if (nParamCount == 6) 1425 nFlag = GetDouble(); 1426 if (nParamCount >= 5) 1427 nZw = GetDouble(); 1428 nBw = GetDouble(); 1429 nZzr = GetDouble(); 1430 nZr = GetDouble(); 1431 nZins = GetDouble(); 1432 if (nZr < 1.0 || nZr > nZzr) 1433 PushIllegalArgument(); 1434 else 1435 { 1436 nZinsz = ScGetZinsZ(nZins, nZr, nZzr, nBw, nZw, nFlag, nRmz); 1437 PushDouble(nRmz - nZinsz); 1438 } 1439 } 1440 1441 void ScInterpreter::ScKumZinsZ() 1442 { 1443 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScKumZinsZ" ); 1444 nFuncFmtType = NUMBERFORMAT_CURRENCY; 1445 if ( MustHaveParamCount( GetByte(), 6 ) ) 1446 { 1447 double fZins, fZzr, fBw, fAnfang, fEnde, fF, fRmz, fZinsZ; 1448 fF = GetDouble(); 1449 fEnde = ::rtl::math::approxFloor(GetDouble()); 1450 fAnfang = ::rtl::math::approxFloor(GetDouble()); 1451 fBw = GetDouble(); 1452 fZzr = GetDouble(); 1453 fZins = GetDouble(); 1454 if (fAnfang < 1.0 || fEnde < fAnfang || fZins <= 0.0 || 1455 fEnde > fZzr || fZzr <= 0.0 || fBw <= 0.0) 1456 PushIllegalArgument(); 1457 else 1458 { 1459 sal_uLong nAnfang = (sal_uLong) fAnfang; 1460 sal_uLong nEnde = (sal_uLong) fEnde ; 1461 fRmz = ScGetRmz(fZins, fZzr, fBw, 0.0, fF); 1462 fZinsZ = 0.0; 1463 if (nAnfang == 1) 1464 { 1465 if (fF <= 0.0) 1466 fZinsZ = -fBw; 1467 nAnfang++; 1468 } 1469 for (sal_uLong i = nAnfang; i <= nEnde; i++) 1470 { 1471 if (fF > 0.0) 1472 fZinsZ += ScGetZw(fZins, (double)(i-2), fRmz, fBw, 1.0) - fRmz; 1473 else 1474 fZinsZ += ScGetZw(fZins, (double)(i-1), fRmz, fBw, 0.0); 1475 } 1476 fZinsZ *= fZins; 1477 PushDouble(fZinsZ); 1478 } 1479 } 1480 } 1481 1482 void ScInterpreter::ScKumKapZ() 1483 { 1484 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScKumKapZ" ); 1485 nFuncFmtType = NUMBERFORMAT_CURRENCY; 1486 if ( MustHaveParamCount( GetByte(), 6 ) ) 1487 { 1488 double fZins, fZzr, fBw, fAnfang, fEnde, fF, fRmz, fKapZ; 1489 fF = GetDouble(); 1490 fEnde = ::rtl::math::approxFloor(GetDouble()); 1491 fAnfang = ::rtl::math::approxFloor(GetDouble()); 1492 fBw = GetDouble(); 1493 fZzr = GetDouble(); 1494 fZins = GetDouble(); 1495 if (fAnfang < 1.0 || fEnde < fAnfang || fZins <= 0.0 || 1496 fEnde > fZzr || fZzr <= 0.0 || fBw <= 0.0) 1497 PushIllegalArgument(); 1498 else 1499 { 1500 fRmz = ScGetRmz(fZins, fZzr, fBw, 0.0, fF); 1501 fKapZ = 0.0; 1502 sal_uLong nAnfang = (sal_uLong) fAnfang; 1503 sal_uLong nEnde = (sal_uLong) fEnde; 1504 if (nAnfang == 1) 1505 { 1506 if (fF <= 0.0) 1507 fKapZ = fRmz + fBw * fZins; 1508 else 1509 fKapZ = fRmz; 1510 nAnfang++; 1511 } 1512 for (sal_uLong i = nAnfang; i <= nEnde; i++) 1513 { 1514 if (fF > 0.0) 1515 fKapZ += fRmz - (ScGetZw(fZins, (double)(i-2), fRmz, fBw, 1.0) - fRmz) * fZins; 1516 else 1517 fKapZ += fRmz - ScGetZw(fZins, (double)(i-1), fRmz, fBw, 0.0) * fZins; 1518 } 1519 PushDouble(fKapZ); 1520 } 1521 } 1522 } 1523 1524 void ScInterpreter::ScEffektiv() 1525 { 1526 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScEffektiv" ); 1527 nFuncFmtType = NUMBERFORMAT_PERCENT; 1528 if ( MustHaveParamCount( GetByte(), 2 ) ) 1529 { 1530 double fPerioden = GetDouble(); 1531 double fNominal = GetDouble(); 1532 if (fPerioden < 1.0 || fNominal <= 0.0) 1533 PushIllegalArgument(); 1534 else 1535 { 1536 fPerioden = ::rtl::math::approxFloor(fPerioden); 1537 PushDouble(pow(1.0 + fNominal/fPerioden, fPerioden) - 1.0); 1538 } 1539 } 1540 } 1541 1542 void ScInterpreter::ScNominal() 1543 { 1544 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNominal" ); 1545 nFuncFmtType = NUMBERFORMAT_PERCENT; 1546 if ( MustHaveParamCount( GetByte(), 2 ) ) 1547 { 1548 double fPerioden = GetDouble(); 1549 double fEffektiv = GetDouble(); 1550 if (fPerioden < 1.0 || fEffektiv <= 0.0) 1551 PushIllegalArgument(); 1552 else 1553 { 1554 fPerioden = ::rtl::math::approxFloor(fPerioden); 1555 PushDouble( (pow(fEffektiv + 1.0, 1.0 / fPerioden) - 1.0) * fPerioden ); 1556 } 1557 } 1558 } 1559 1560 void ScInterpreter::ScMod() 1561 { 1562 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScMod" ); 1563 if ( MustHaveParamCount( GetByte(), 2 ) ) 1564 { 1565 double fVal2 = GetDouble(); // Denominator 1566 double fVal1 = GetDouble(); // Numerator 1567 if (fVal2 == floor(fVal2)) // a pure integral number stored in double 1568 { 1569 double fResult = fmod(fVal1,fVal2); 1570 if ( (fResult != 0.0) && 1571 ((fVal1 > 0.0 && fVal2 < 0.0) || (fVal1 < 0.0 && fVal2 > 0.0))) 1572 fResult += fVal2 ; 1573 PushDouble( fResult ); 1574 } 1575 else 1576 { 1577 PushDouble( ::rtl::math::approxSub( fVal1, 1578 ::rtl::math::approxFloor(fVal1 / fVal2) * fVal2)); 1579 } 1580 } 1581 } 1582 1583 /** (Goal Seek) Find a value of x that is a root of f(x) 1584 1585 This function is used internally for the goal seek operation. It uses the 1586 Regula Falsi (aka false position) algorithm to find a root of f(x). The 1587 start value and the target value are to be given by the user in the 1588 goal seek dialog. The f(x) in this case is defined as the formula in the 1589 formula cell minus target value. This function may also perform additional 1590 search in the horizontal directions when the f(x) is discrete in order to 1591 ensure a non-zero slope necessary for deriving a subsequent x that is 1592 reasonably close to the root of interest. 1593 1594 @change 24.10.2004 by Kohei Yoshida (kohei@openoffice.org) 1595 1596 @see #i28955# 1597 */ 1598 void ScInterpreter::ScBackSolver() 1599 { 1600 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScBackSolver" ); 1601 if ( MustHaveParamCount( GetByte(), 3 ) ) 1602 { 1603 sal_Bool bDoneIteration = sal_False; 1604 ScAddress aValueAdr, aFormulaAdr; 1605 double fTargetVal = GetDouble(); 1606 PopSingleRef( aFormulaAdr ); 1607 PopSingleRef( aValueAdr ); 1608 1609 if (nGlobalError == 0) 1610 { 1611 ScBaseCell* pVCell = GetCell( aValueAdr ); 1612 // CELLTYPE_NOTE: kein Value aber von Formel referiert 1613 sal_Bool bTempCell = (!pVCell || pVCell->GetCellType() == CELLTYPE_NOTE); 1614 ScBaseCell* pFCell = GetCell( aFormulaAdr ); 1615 1616 if ( ((pVCell && pVCell->GetCellType() == CELLTYPE_VALUE) || bTempCell) 1617 && pFCell && pFCell->GetCellType() == CELLTYPE_FORMULA ) 1618 { 1619 ScRange aVRange( aValueAdr, aValueAdr ); // fuer SetDirty 1620 double fSaveVal; // Original value to be restored later if necessary 1621 ScPostIt* pNote = 0; 1622 1623 if ( bTempCell ) 1624 { 1625 pNote = pVCell ? pVCell->ReleaseNote() : 0; 1626 fSaveVal = 0.0; 1627 pVCell = new ScValueCell( fSaveVal ); 1628 pDok->PutCell( aValueAdr, pVCell ); 1629 } 1630 else 1631 fSaveVal = GetCellValue( aValueAdr, pVCell ); 1632 1633 const sal_uInt16 nMaxIter = 100; 1634 const double fEps = 1E-10; 1635 const double fDelta = 1E-6; 1636 1637 double fBestX, fXPrev; 1638 double fBestF, fFPrev; 1639 fBestX = fXPrev = fSaveVal; 1640 1641 ScFormulaCell* pFormula = (ScFormulaCell*) pFCell; 1642 ScValueCell* pValue = (ScValueCell*) pVCell; 1643 1644 pFormula->Interpret(); 1645 sal_Bool bError = ( pFormula->GetErrCode() != 0 ); 1646 // bError always corresponds with fF 1647 1648 fFPrev = pFormula->GetValue() - fTargetVal; 1649 1650 fBestF = fabs( fFPrev ); 1651 if ( fBestF < fDelta ) 1652 bDoneIteration = sal_True; 1653 1654 double fX = fXPrev + fEps; 1655 double fF = fFPrev; 1656 double fSlope; 1657 1658 sal_uInt16 nIter = 0; 1659 1660 sal_Bool bHorMoveError = sal_False; 1661 // Nach der Regula Falsi Methode 1662 while ( !bDoneIteration && ( nIter++ < nMaxIter ) ) 1663 { 1664 pValue->SetValue( fX ); 1665 pDok->SetDirty( aVRange ); 1666 pFormula->Interpret(); 1667 bError = ( pFormula->GetErrCode() != 0 ); 1668 fF = pFormula->GetValue() - fTargetVal; 1669 1670 if ( fF == fFPrev && !bError ) 1671 { 1672 // HORIZONTAL SEARCH: Keep moving x in both directions until the f(x) 1673 // becomes different from the previous f(x). This routine is needed 1674 // when a given function is discrete, in which case the resulting slope 1675 // may become zero which ultimately causes the goal seek operation 1676 // to fail. #i28955# 1677 1678 sal_uInt16 nHorIter = 0; 1679 const double fHorStepAngle = 5.0; 1680 const double fHorMaxAngle = 80.0; 1681 int nHorMaxIter = static_cast<int>( fHorMaxAngle / fHorStepAngle ); 1682 sal_Bool bDoneHorMove = sal_False; 1683 1684 while ( !bDoneHorMove && !bHorMoveError && nHorIter++ < nHorMaxIter ) 1685 { 1686 double fHorAngle = fHorStepAngle * static_cast<double>( nHorIter ); 1687 double fHorTangent = ::rtl::math::tan( fHorAngle * F_PI / 180 ); 1688 1689 sal_uInt16 nIdx = 0; 1690 while( nIdx++ < 2 && !bDoneHorMove ) 1691 { 1692 double fHorX; 1693 if ( nIdx == 1 ) 1694 fHorX = fX + fabs(fF)*fHorTangent; 1695 else 1696 fHorX = fX - fabs(fF)*fHorTangent; 1697 1698 pValue->SetValue( fHorX ); 1699 pDok->SetDirty( aVRange ); 1700 pFormula->Interpret(); 1701 bHorMoveError = ( pFormula->GetErrCode() != 0 ); 1702 if ( bHorMoveError ) 1703 break; 1704 1705 fF = pFormula->GetValue() - fTargetVal; 1706 if ( fF != fFPrev ) 1707 { 1708 fX = fHorX; 1709 bDoneHorMove = sal_True; 1710 } 1711 } 1712 } 1713 if ( !bDoneHorMove ) 1714 bHorMoveError = sal_True; 1715 } 1716 1717 if ( bError ) 1718 { 1719 // move closer to last valid value (fXPrev), keep fXPrev & fFPrev 1720 double fDiff = ( fXPrev - fX ) / 2; 1721 if (fabs(fDiff) < fEps) 1722 fDiff = (fDiff < 0.0) ? - fEps : fEps; 1723 fX += fDiff; 1724 } 1725 else if ( bHorMoveError ) 1726 break; 1727 else if ( fabs(fF) < fDelta ) 1728 { 1729 // converged to root 1730 fBestX = fX; 1731 bDoneIteration = sal_True; 1732 } 1733 else 1734 { 1735 if ( fabs(fF) + fDelta < fBestF ) 1736 { 1737 fBestX = fX; 1738 fBestF = fabs(fF); 1739 } 1740 1741 if ( ( fXPrev - fX ) != 0 ) 1742 { 1743 fSlope = ( fFPrev - fF ) / ( fXPrev - fX ); 1744 if ( fabs( fSlope ) < fEps ) 1745 fSlope = fSlope < 0.0 ? -fEps : fEps; 1746 } 1747 else 1748 fSlope = fEps; 1749 1750 fXPrev = fX; 1751 fFPrev = fF; 1752 fX = fX - ( fF / fSlope ); 1753 } 1754 } 1755 1756 // Try a nice rounded input value if possible. 1757 const double fNiceDelta = (bDoneIteration && fabs(fBestX) >= 1e-3 ? 1e-3 : fDelta); 1758 double nX = ::rtl::math::approxFloor((fBestX / fNiceDelta) + 0.5) * fNiceDelta; 1759 // double nX = ::rtl::math::approxFloor((fBestX / fDelta) + 0.5) * fDelta; 1760 1761 if ( bDoneIteration ) 1762 { 1763 pValue->SetValue( nX ); 1764 pDok->SetDirty( aVRange ); 1765 pFormula->Interpret(); 1766 if ( fabs( pFormula->GetValue() - fTargetVal ) > fabs( fF ) ) 1767 nX = fBestX; 1768 } 1769 else if ( bError || bHorMoveError ) 1770 { 1771 nX = fBestX; 1772 } 1773 if ( bTempCell ) 1774 { 1775 pVCell = pNote ? new ScNoteCell( pNote ) : 0; 1776 pDok->PutCell( aValueAdr, pVCell ); 1777 } 1778 else 1779 pValue->SetValue( fSaveVal ); 1780 pDok->SetDirty( aVRange ); 1781 pFormula->Interpret(); 1782 if ( !bDoneIteration ) 1783 SetError(NOTAVAILABLE); 1784 PushDouble(nX); 1785 } 1786 else 1787 { 1788 if ( !bDoneIteration ) 1789 SetError(NOTAVAILABLE); 1790 PushInt(0); // falsche Zelltypen 1791 } 1792 } 1793 else 1794 { 1795 if ( !bDoneIteration ) 1796 SetError(NOTAVAILABLE); 1797 PushInt(0); // nGlobalError 1798 } 1799 } 1800 } 1801 1802 void ScInterpreter::ScIntersect() 1803 { 1804 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIntersect" ); 1805 formula::FormulaTokenRef p2nd = PopToken(); 1806 formula::FormulaTokenRef p1st = PopToken(); 1807 1808 if (nGlobalError || !p2nd || !p1st) 1809 { 1810 PushIllegalArgument(); 1811 return; 1812 } // if (nGlobalError || !xT2 || !xT1) 1813 1814 StackVar sv1 = p1st->GetType(); 1815 StackVar sv2 = p2nd->GetType(); 1816 if ((sv1 != svSingleRef && sv1 != svDoubleRef && sv1 != svRefList) || 1817 (sv2 != svSingleRef && sv2 != svDoubleRef && sv2 != svRefList)) 1818 { 1819 PushIllegalArgument(); 1820 return; 1821 } 1822 1823 ScToken* x1 = static_cast<ScToken*>(p1st.get()); 1824 ScToken* x2 = static_cast<ScToken*>(p2nd.get()); 1825 if (sv1 == svRefList || sv2 == svRefList) 1826 { 1827 // Now this is a bit nasty but it simplifies things, and having 1828 // intersections with lists isn't too common, if at all.. 1829 // Convert a reference to list. 1830 ScToken* xt[2] = { x1, x2 }; 1831 StackVar sv[2] = { sv1, sv2 }; 1832 for (size_t i=0; i<2; ++i) 1833 { 1834 if (sv[i] == svSingleRef) 1835 { 1836 ScComplexRefData aRef; 1837 aRef.Ref1 = aRef.Ref2 = xt[i]->GetSingleRef(); 1838 xt[i] = new ScRefListToken; 1839 xt[i]->GetRefList()->push_back( aRef); 1840 } 1841 else if (sv[i] == svDoubleRef) 1842 { 1843 ScComplexRefData aRef = xt[i]->GetDoubleRef(); 1844 xt[i] = new ScRefListToken; 1845 xt[i]->GetRefList()->push_back( aRef); 1846 } 1847 } 1848 x1 = xt[0], x2 = xt[1]; 1849 1850 x1->CalcAbsIfRel( aPos); 1851 x2->CalcAbsIfRel( aPos); 1852 ScTokenRef xRes = new ScRefListToken; 1853 ScRefList* pRefList = xRes->GetRefList(); 1854 ScRefList::const_iterator end1( x1->GetRefList()->end()); 1855 ScRefList::const_iterator end2( x2->GetRefList()->end()); 1856 for (ScRefList::const_iterator it1( x1->GetRefList()->begin()); 1857 it1 != end1; ++it1) 1858 { 1859 const ScSingleRefData& r11 = (*it1).Ref1; 1860 const ScSingleRefData& r12 = (*it1).Ref2; 1861 for (ScRefList::const_iterator it2( x2->GetRefList()->begin()); 1862 it2 != end2; ++it2) 1863 { 1864 const ScSingleRefData& r21 = (*it2).Ref1; 1865 const ScSingleRefData& r22 = (*it2).Ref2; 1866 SCCOL nCol1 = ::std::max( r11.nCol, r21.nCol); 1867 SCROW nRow1 = ::std::max( r11.nRow, r21.nRow); 1868 SCTAB nTab1 = ::std::max( r11.nTab, r21.nTab); 1869 SCCOL nCol2 = ::std::min( r12.nCol, r22.nCol); 1870 SCROW nRow2 = ::std::min( r12.nRow, r22.nRow); 1871 SCTAB nTab2 = ::std::min( r12.nTab, r22.nTab); 1872 if (nCol2 < nCol1 || nRow2 < nRow1 || nTab2 < nTab1) 1873 ; // nothing 1874 else 1875 { 1876 ScComplexRefData aRef; 1877 aRef.InitRange( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2); 1878 pRefList->push_back( aRef); 1879 } 1880 } 1881 } 1882 size_t n = pRefList->size(); 1883 if (!n) 1884 PushError( errNoRef); 1885 else if (n == 1) 1886 { 1887 const ScComplexRefData& rRef = (*pRefList)[0]; 1888 if (rRef.Ref1 == rRef.Ref2) 1889 PushTempToken( new ScSingleRefToken( rRef.Ref1)); 1890 else 1891 PushTempToken( new ScDoubleRefToken( rRef)); 1892 } 1893 else 1894 PushTempToken( xRes); 1895 } 1896 else 1897 { 1898 ScToken* pt[2] = { x1, x2 }; 1899 StackVar sv[2] = { sv1, sv2 }; 1900 SCCOL nC1[2], nC2[2]; 1901 SCROW nR1[2], nR2[2]; 1902 SCTAB nT1[2], nT2[2]; 1903 for (size_t i=0; i<2; ++i) 1904 { 1905 switch (sv[i]) 1906 { 1907 case svSingleRef: 1908 case svDoubleRef: 1909 pt[i]->CalcAbsIfRel( aPos); 1910 { 1911 const ScSingleRefData& r = pt[i]->GetSingleRef(); 1912 nC1[i] = r.nCol; 1913 nR1[i] = r.nRow; 1914 nT1[i] = r.nTab; 1915 } 1916 if (sv[i] == svDoubleRef) 1917 { 1918 const ScSingleRefData& r = pt[i]->GetSingleRef2(); 1919 nC2[i] = r.nCol; 1920 nR2[i] = r.nRow; 1921 nT2[i] = r.nTab; 1922 } 1923 else 1924 { 1925 nC2[i] = nC1[i]; 1926 nR2[i] = nR1[i]; 1927 nT2[i] = nT1[i]; 1928 } 1929 break; 1930 default: 1931 ; // nothing, prevent compiler warning 1932 } 1933 } 1934 SCCOL nCol1 = ::std::max( nC1[0], nC1[1]); 1935 SCROW nRow1 = ::std::max( nR1[0], nR1[1]); 1936 SCTAB nTab1 = ::std::max( nT1[0], nT1[1]); 1937 SCCOL nCol2 = ::std::min( nC2[0], nC2[1]); 1938 SCROW nRow2 = ::std::min( nR2[0], nR2[1]); 1939 SCTAB nTab2 = ::std::min( nT2[0], nT2[1]); 1940 if (nCol2 < nCol1 || nRow2 < nRow1 || nTab2 < nTab1) 1941 PushError( errNoRef); 1942 else if (nCol2 == nCol1 && nRow2 == nRow1 && nTab2 == nTab1) 1943 PushSingleRef( nCol1, nRow1, nTab1); 1944 else 1945 PushDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2); 1946 } 1947 } 1948 1949 1950 void ScInterpreter::ScRangeFunc() 1951 { 1952 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRangeFunc" ); 1953 formula::FormulaTokenRef x2 = PopToken(); 1954 formula::FormulaTokenRef x1 = PopToken(); 1955 1956 if (nGlobalError || !x2 || !x1) 1957 { 1958 PushIllegalArgument(); 1959 return; 1960 } // if (nGlobalError || !xT2 || !xT1) 1961 FormulaTokenRef xRes = ScToken::ExtendRangeReference( *x1, *x2, aPos, false); 1962 if (!xRes) 1963 PushIllegalArgument(); 1964 else 1965 PushTempToken( xRes); 1966 } 1967 1968 1969 void ScInterpreter::ScUnionFunc() 1970 { 1971 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScUnionFunc" ); 1972 formula::FormulaTokenRef p2nd = PopToken(); 1973 formula::FormulaTokenRef p1st = PopToken(); 1974 1975 if (nGlobalError || !p2nd || !p1st) 1976 { 1977 PushIllegalArgument(); 1978 return; 1979 } // if (nGlobalError || !xT2 || !xT1) 1980 1981 StackVar sv1 = p1st->GetType(); 1982 StackVar sv2 = p2nd->GetType(); 1983 if ((sv1 != svSingleRef && sv1 != svDoubleRef && sv1 != svRefList) || 1984 (sv2 != svSingleRef && sv2 != svDoubleRef && sv2 != svRefList)) 1985 { 1986 PushIllegalArgument(); 1987 return; 1988 } 1989 1990 ScToken* x1 = static_cast<ScToken*>(p1st.get()); 1991 ScToken* x2 = static_cast<ScToken*>(p2nd.get()); 1992 1993 1994 ScTokenRef xRes; 1995 // Append to an existing RefList if there is one. 1996 if (sv1 == svRefList) 1997 { 1998 xRes = x1; 1999 sv1 = svUnknown; // mark as handled 2000 } 2001 else if (sv2 == svRefList) 2002 { 2003 xRes = x2; 2004 sv2 = svUnknown; // mark as handled 2005 } 2006 else 2007 xRes = new ScRefListToken; 2008 ScRefList* pRes = xRes->GetRefList(); 2009 ScToken* pt[2] = { x1, x2 }; 2010 StackVar sv[2] = { sv1, sv2 }; 2011 for (size_t i=0; i<2; ++i) 2012 { 2013 if (pt[i] == xRes) 2014 continue; 2015 switch (sv[i]) 2016 { 2017 case svSingleRef: 2018 { 2019 ScComplexRefData aRef; 2020 aRef.Ref1 = aRef.Ref2 = pt[i]->GetSingleRef(); 2021 pRes->push_back( aRef); 2022 } 2023 break; 2024 case svDoubleRef: 2025 pRes->push_back( pt[i]->GetDoubleRef()); 2026 break; 2027 case svRefList: 2028 { 2029 const ScRefList* p = pt[i]->GetRefList(); 2030 ScRefList::const_iterator it( p->begin()); 2031 ScRefList::const_iterator end( p->end()); 2032 for ( ; it != end; ++it) 2033 { 2034 pRes->push_back( *it); 2035 } 2036 } 2037 break; 2038 default: 2039 ; // nothing, prevent compiler warning 2040 } 2041 } 2042 ValidateRef( *pRes); // set #REF! if needed 2043 PushTempToken( xRes); 2044 } 2045 2046 2047 void ScInterpreter::ScCurrent() 2048 { 2049 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCurrent" ); 2050 FormulaTokenRef xTok( PopToken()); 2051 if (xTok) 2052 { 2053 PushTempToken( xTok); 2054 PushTempToken( xTok); 2055 } 2056 else 2057 PushError( errUnknownStackVariable); 2058 } 2059 2060 void ScInterpreter::ScStyle() 2061 { 2062 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScStyle" ); 2063 sal_uInt8 nParamCount = GetByte(); 2064 if (nParamCount >= 1 && nParamCount <= 3) 2065 { 2066 String aStyle2; // Vorlage nach Timer 2067 if (nParamCount >= 3) 2068 aStyle2 = GetString(); 2069 long nTimeOut = 0; // Timeout 2070 if (nParamCount >= 2) 2071 nTimeOut = (long)(GetDouble()*1000.0); 2072 String aStyle1 = GetString(); // Vorlage fuer sofort 2073 2074 if (nTimeOut < 0) 2075 nTimeOut = 0; 2076 2077 // 2078 // Request ausfuehren, um Vorlage anzuwenden 2079 // 2080 2081 if ( !pDok->IsClipOrUndo() ) 2082 { 2083 SfxObjectShell* pShell = pDok->GetDocumentShell(); 2084 if (pShell) 2085 { 2086 //! notify object shell directly 2087 2088 ScRange aRange(aPos); 2089 ScAutoStyleHint aHint( aRange, aStyle1, nTimeOut, aStyle2 ); 2090 pShell->Broadcast( aHint ); 2091 } 2092 } 2093 2094 PushDouble(0.0); 2095 } 2096 else 2097 PushIllegalParameter(); 2098 } 2099 2100 ScDdeLink* lcl_GetDdeLink( sfx2::LinkManager* pLinkMgr, 2101 const String& rA, const String& rT, const String& rI, sal_uInt8 nM ) 2102 { 2103 sal_uInt16 nCount = pLinkMgr->GetLinks().Count(); 2104 for (sal_uInt16 i=0; i<nCount; i++ ) 2105 { 2106 ::sfx2::SvBaseLink* pBase = *pLinkMgr->GetLinks()[i]; 2107 if (pBase->ISA(ScDdeLink)) 2108 { 2109 ScDdeLink* pLink = (ScDdeLink*)pBase; 2110 if ( pLink->GetAppl() == rA && 2111 pLink->GetTopic() == rT && 2112 pLink->GetItem() == rI && 2113 pLink->GetMode() == nM ) 2114 return pLink; 2115 } 2116 } 2117 2118 return NULL; 2119 } 2120 2121 void ScInterpreter::ScDde() 2122 { 2123 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDde" ); 2124 // Applikation, Datei, Bereich 2125 // Application, Topic, Item 2126 2127 sal_uInt8 nParamCount = GetByte(); 2128 if ( MustHaveParamCount( nParamCount, 3, 4 ) ) 2129 { 2130 sal_uInt8 nMode = SC_DDE_DEFAULT; 2131 if (nParamCount == 4) 2132 nMode = (sal_uInt8) ::rtl::math::approxFloor(GetDouble()); 2133 String aItem = GetString(); 2134 String aTopic = GetString(); 2135 String aAppl = GetString(); 2136 2137 if (nMode > SC_DDE_TEXT) 2138 nMode = SC_DDE_DEFAULT; 2139 2140 // temporary documents (ScFunctionAccess) have no DocShell 2141 // and no LinkManager -> abort 2142 2143 sfx2::LinkManager* pLinkMgr = pDok->GetLinkManager(); 2144 if (!pLinkMgr) 2145 { 2146 PushNoValue(); 2147 return; 2148 } 2149 2150 // Nach dem Laden muss neu interpretiert werden (Verknuepfungen aufbauen) 2151 2152 if ( pMyFormulaCell->GetCode()->IsRecalcModeNormal() ) 2153 pMyFormulaCell->GetCode()->SetRecalcModeOnLoad(); 2154 2155 // solange der Link nicht ausgewertet ist, Idle abklemmen 2156 // (um zirkulaere Referenzen zu vermeiden) 2157 2158 sal_Bool bOldDis = pDok->IsIdleDisabled(); 2159 pDok->DisableIdle( sal_True ); 2160 2161 // Link-Objekt holen / anlegen 2162 2163 ScDdeLink* pLink = lcl_GetDdeLink( pLinkMgr, aAppl, aTopic, aItem, nMode ); 2164 2165 //! Dde-Links (zusaetzlich) effizienter am Dokument speichern !!!!! 2166 // ScDdeLink* pLink = pDok->GetDdeLink( aAppl, aTopic, aItem ); 2167 2168 sal_Bool bWasError = ( pMyFormulaCell->GetRawError() != 0 ); 2169 2170 if (!pLink) 2171 { 2172 pLink = new ScDdeLink( pDok, aAppl, aTopic, aItem, nMode ); 2173 pLinkMgr->InsertDDELink( pLink, aAppl, aTopic, aItem ); 2174 if ( pLinkMgr->GetLinks().Count() == 1 ) // erster ? 2175 { 2176 SfxBindings* pBindings = pDok->GetViewBindings(); 2177 if (pBindings) 2178 pBindings->Invalidate( SID_LINKS ); // Link-Manager enablen 2179 } 2180 2181 //! asynchron auswerten ??? 2182 pLink->TryUpdate(); // TryUpdate ruft Update nicht mehrfach auf 2183 2184 // StartListening erst nach dem Update, sonst circular reference 2185 pMyFormulaCell->StartListening( *pLink ); 2186 } 2187 else 2188 { 2189 pMyFormulaCell->StartListening( *pLink ); 2190 } 2191 2192 // Wenn aus dem Reschedule beim Ausfuehren des Links ein Fehler 2193 // (z.B. zirkulaere Referenz) entstanden ist, der vorher nicht da war, 2194 // das Fehler-Flag zuruecksetzen: 2195 2196 if ( pMyFormulaCell->GetRawError() && !bWasError ) 2197 pMyFormulaCell->SetErrCode(0); 2198 2199 // Wert abfragen 2200 2201 const ScMatrix* pLinkMat = pLink->GetResult(); 2202 if (pLinkMat) 2203 { 2204 SCSIZE nC, nR; 2205 pLinkMat->GetDimensions(nC, nR); 2206 ScMatrixRef pNewMat = GetNewMat( nC, nR); 2207 if (pNewMat) 2208 { 2209 pLinkMat->MatCopy(*pNewMat); // kopieren 2210 PushMatrix( pNewMat ); 2211 } 2212 else 2213 PushIllegalArgument(); 2214 } 2215 else 2216 PushNA(); 2217 2218 pDok->DisableIdle( bOldDis ); 2219 } 2220 } 2221 2222 void ScInterpreter::ScBase() 2223 { // Value, Base [, MinLen] 2224 sal_uInt8 nParamCount = GetByte(); 2225 if ( MustHaveParamCount( nParamCount, 2, 3 ) ) 2226 { 2227 static const sal_Unicode __FAR_DATA pDigits[] = { 2228 '0','1','2','3','4','5','6','7','8','9', 2229 'A','B','C','D','E','F','G','H','I','J','K','L','M', 2230 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z', 2231 0 2232 }; 2233 static const int nDigits = (sizeof(pDigits)/sizeof(sal_Unicode))-1; 2234 xub_StrLen nMinLen; 2235 if ( nParamCount == 3 ) 2236 { 2237 double fLen = ::rtl::math::approxFloor( GetDouble() ); 2238 if ( 1.0 <= fLen && fLen < STRING_MAXLEN ) 2239 nMinLen = (xub_StrLen) fLen; 2240 else if ( fLen == 0.0 ) 2241 nMinLen = 1; 2242 else 2243 nMinLen = 0; // Error 2244 } 2245 else 2246 nMinLen = 1; 2247 double fBase = ::rtl::math::approxFloor( GetDouble() ); 2248 double fVal = ::rtl::math::approxFloor( GetDouble() ); 2249 double fChars = ((fVal > 0.0 && fBase > 0.0) ? 2250 (ceil( log( fVal ) / log( fBase ) ) + 2.0) : 2251 2.0); 2252 if ( fChars >= STRING_MAXLEN ) 2253 nMinLen = 0; // Error 2254 2255 if ( !nGlobalError && nMinLen && 2 <= fBase && fBase <= nDigits && 0 <= fVal ) 2256 { 2257 const xub_StrLen nConstBuf = 128; 2258 sal_Unicode aBuf[nConstBuf]; 2259 xub_StrLen nBuf = Max( (xub_StrLen) fChars, (xub_StrLen) (nMinLen+1) ); 2260 sal_Unicode* pBuf = (nBuf <= nConstBuf ? aBuf : new sal_Unicode[nBuf]); 2261 for ( xub_StrLen j = 0; j < nBuf; ++j ) 2262 { 2263 pBuf[j] = '0'; 2264 } 2265 sal_Unicode* p = pBuf + nBuf - 1; 2266 *p = 0; 2267 if ( fVal <= (sal_uLong)(~0) ) 2268 { 2269 sal_uLong nVal = (sal_uLong) fVal; 2270 sal_uLong nBase = (sal_uLong) fBase; 2271 while ( nVal && p > pBuf ) 2272 { 2273 *--p = pDigits[ nVal % nBase ]; 2274 nVal /= nBase; 2275 } 2276 fVal = (double) nVal; 2277 } 2278 else 2279 { 2280 sal_Bool bDirt = sal_False; 2281 while ( fVal && p > pBuf ) 2282 { 2283 //! mit fmod Rundungsfehler ab 2**48 2284 // double fDig = ::rtl::math::approxFloor( fmod( fVal, fBase ) ); 2285 // so ist es etwas besser 2286 double fInt = ::rtl::math::approxFloor( fVal / fBase ); 2287 double fMult = fInt * fBase; 2288 #if OSL_DEBUG_LEVEL > 1 2289 // #53943# =BASIS(1e308;36) => GPF mit 2290 // nDig = (size_t) ::rtl::math::approxFloor( fVal - fMult ); 2291 // trotz vorheriger Pruefung ob fVal >= fMult 2292 double fDebug1 = fVal - fMult; 2293 // fVal := 7,5975311883090e+290 2294 // fMult := 7,5975311883090e+290 2295 // fDebug1 := 1,3848924157003e+275 <- RoundOff-Error 2296 // fVal != fMult, aber: ::rtl::math::approxEqual( fVal, fMult ) == TRUE 2297 double fDebug2 = ::rtl::math::approxSub( fVal, fMult ); 2298 // und ::rtl::math::approxSub( fVal, fMult ) == 0 2299 double fDebug3 = ( fInt ? fVal / fInt : 0.0 ); 2300 // Nach dem strange fDebug1 und fVal < fMult ist eigentlich 2301 // fDebug2 == fBase, trotzdem wird das mit einem Vergleich 2302 // nicht erkannt, dann schlaegt bDirt zu und alles wird wieder gut.. 2303 2304 // prevent compiler warnings 2305 (void)fDebug1; (void)fDebug2; (void)fDebug3; 2306 #endif 2307 size_t nDig; 2308 if ( fVal < fMult ) 2309 { // da ist was gekippt 2310 bDirt = sal_True; 2311 nDig = 0; 2312 } 2313 else 2314 { 2315 double fDig = ::rtl::math::approxFloor( ::rtl::math::approxSub( fVal, fMult ) ); 2316 if ( bDirt ) 2317 { 2318 bDirt = sal_False; 2319 --fDig; 2320 } 2321 if ( fDig <= 0.0 ) 2322 nDig = 0; 2323 else if ( fDig >= fBase ) 2324 nDig = ((size_t) fBase) - 1; 2325 else 2326 nDig = (size_t) fDig; 2327 } 2328 *--p = pDigits[ nDig ]; 2329 fVal = fInt; 2330 } 2331 } 2332 if ( fVal ) 2333 PushError( errStringOverflow ); 2334 else 2335 { 2336 if ( nBuf - (p - pBuf) <= nMinLen ) 2337 p = pBuf + nBuf - 1 - nMinLen; 2338 PushStringBuffer( p ); 2339 } 2340 if ( pBuf != aBuf ) 2341 delete [] pBuf; 2342 } 2343 else 2344 PushIllegalArgument(); 2345 } 2346 } 2347 2348 2349 void ScInterpreter::ScDecimal() 2350 { // Text, Base 2351 if ( MustHaveParamCount( GetByte(), 2 ) ) 2352 { 2353 double fBase = ::rtl::math::approxFloor( GetDouble() ); 2354 String aStr( GetString() ); 2355 if ( !nGlobalError && 2 <= fBase && fBase <= 36 ) 2356 { 2357 double fVal = 0.0; 2358 int nBase = (int) fBase; 2359 register const sal_Unicode* p = aStr.GetBuffer(); 2360 while ( *p == ' ' || *p == '\t' ) 2361 p++; // strip leading white space 2362 if ( nBase == 16 ) 2363 { // evtl. hex-prefix strippen 2364 if ( *p == 'x' || *p == 'X' ) 2365 p++; 2366 else if ( *p == '0' && (*(p+1) == 'x' || *(p+1) == 'X') ) 2367 p += 2; 2368 } 2369 while ( *p ) 2370 { 2371 int n; 2372 if ( '0' <= *p && *p <= '9' ) 2373 n = *p - '0'; 2374 else if ( 'A' <= *p && *p <= 'Z' ) 2375 n = 10 + (*p - 'A'); 2376 else if ( 'a' <= *p && *p <= 'z' ) 2377 n = 10 + (*p - 'a'); 2378 else 2379 n = nBase; 2380 if ( nBase <= n ) 2381 { 2382 if ( *(p+1) == 0 && 2383 ( (nBase == 2 && (*p == 'b' || *p == 'B')) 2384 ||(nBase == 16 && (*p == 'h' || *p == 'H')) ) 2385 ) 2386 ; // 101b und F00Dh sind ok 2387 else 2388 { 2389 PushIllegalArgument(); 2390 return ; 2391 } 2392 } 2393 else 2394 fVal = fVal * fBase + n; 2395 p++; 2396 2397 } 2398 PushDouble( fVal ); 2399 } 2400 else 2401 PushIllegalArgument(); 2402 } 2403 } 2404 2405 2406 void ScInterpreter::ScConvert() 2407 { // Value, FromUnit, ToUnit 2408 if ( MustHaveParamCount( GetByte(), 3 ) ) 2409 { 2410 String aToUnit( GetString() ); 2411 String aFromUnit( GetString() ); 2412 double fVal = GetDouble(); 2413 if ( nGlobalError ) 2414 PushError( nGlobalError); 2415 else 2416 { // erst die angegebene Reihenfolge suchen, wenn nicht gefunden den Kehrwert 2417 double fConv; 2418 if ( ScGlobal::GetUnitConverter()->GetValue( fConv, aFromUnit, aToUnit ) ) 2419 PushDouble( fVal * fConv ); 2420 else if ( ScGlobal::GetUnitConverter()->GetValue( fConv, aToUnit, aFromUnit ) ) 2421 PushDouble( fVal / fConv ); 2422 else 2423 PushNA(); 2424 } 2425 } 2426 } 2427 2428 2429 void ScInterpreter::ScRoman() 2430 { // Value [Mode] 2431 sal_uInt8 nParamCount = GetByte(); 2432 if( MustHaveParamCount( nParamCount, 1, 2 ) ) 2433 { 2434 double fMode = (nParamCount == 2) ? ::rtl::math::approxFloor( GetDouble() ) : 0.0; 2435 double fVal = ::rtl::math::approxFloor( GetDouble() ); 2436 if( nGlobalError ) 2437 PushError( nGlobalError); 2438 else if( (fMode >= 0.0) && (fMode < 5.0) && (fVal >= 0.0) && (fVal < 4000.0) ) 2439 { 2440 static const sal_Unicode pChars[] = { 'M', 'D', 'C', 'L', 'X', 'V', 'I' }; 2441 static const sal_uInt16 pValues[] = { 1000, 500, 100, 50, 10, 5, 1 }; 2442 static const sal_uInt16 nMaxIndex = (sal_uInt16)(sizeof(pValues) / sizeof(pValues[0]) - 1); 2443 2444 String aRoman; 2445 sal_uInt16 nVal = (sal_uInt16) fVal; 2446 sal_uInt16 nMode = (sal_uInt16) fMode; 2447 2448 for( sal_uInt16 i = 0; i <= nMaxIndex / 2; i++ ) 2449 { 2450 sal_uInt16 nIndex = 2 * i; 2451 sal_uInt16 nDigit = nVal / pValues[ nIndex ]; 2452 2453 if( (nDigit % 5) == 4 ) 2454 { 2455 sal_uInt16 nIndex2 = (nDigit == 4) ? nIndex - 1 : nIndex - 2; 2456 sal_uInt16 nSteps = 0; 2457 while( (nSteps < nMode) && (nIndex < nMaxIndex) ) 2458 { 2459 nSteps++; 2460 if( pValues[ nIndex2 ] - pValues[ nIndex + 1 ] <= nVal ) 2461 nIndex++; 2462 else 2463 nSteps = nMode; 2464 } 2465 aRoman += pChars[ nIndex ]; 2466 aRoman += pChars[ nIndex2 ]; 2467 nVal = sal::static_int_cast<sal_uInt16>( nVal + pValues[ nIndex ] ); 2468 nVal = sal::static_int_cast<sal_uInt16>( nVal - pValues[ nIndex2 ] ); 2469 } 2470 else 2471 { 2472 if( nDigit > 4 ) 2473 aRoman += pChars[ nIndex - 1 ]; 2474 aRoman.Expand( aRoman.Len() + (nDigit % 5), pChars[ nIndex ] ); 2475 nVal %= pValues[ nIndex ]; 2476 } 2477 } 2478 2479 PushString( aRoman ); 2480 } 2481 else 2482 PushIllegalArgument(); 2483 } 2484 } 2485 2486 2487 sal_Bool lcl_GetArabicValue( sal_Unicode cChar, sal_uInt16& rnValue, sal_Bool& rbIsDec ) 2488 { 2489 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScBase" ); 2490 switch( cChar ) 2491 { 2492 case 'M': rnValue = 1000; rbIsDec = sal_True; break; 2493 case 'D': rnValue = 500; rbIsDec = sal_False; break; 2494 case 'C': rnValue = 100; rbIsDec = sal_True; break; 2495 case 'L': rnValue = 50; rbIsDec = sal_False; break; 2496 case 'X': rnValue = 10; rbIsDec = sal_True; break; 2497 case 'V': rnValue = 5; rbIsDec = sal_False; break; 2498 case 'I': rnValue = 1; rbIsDec = sal_True; break; 2499 default: return sal_False; 2500 } 2501 return sal_True; 2502 } 2503 2504 2505 void ScInterpreter::ScArabic() 2506 { 2507 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScArabic" ); 2508 String aRoman( GetString() ); 2509 if( nGlobalError ) 2510 PushError( nGlobalError); 2511 else 2512 { 2513 aRoman.ToUpperAscii(); 2514 2515 sal_uInt16 nValue = 0; 2516 sal_uInt16 nValidRest = 3999; 2517 sal_uInt16 nCharIndex = 0; 2518 sal_uInt16 nCharCount = aRoman.Len(); 2519 sal_Bool bValid = sal_True; 2520 2521 while( bValid && (nCharIndex < nCharCount) ) 2522 { 2523 sal_uInt16 nDigit1 = 0; 2524 sal_uInt16 nDigit2 = 0; 2525 sal_Bool bIsDec1 = sal_False; 2526 sal_Bool bIsDec2 = sal_False; 2527 bValid = lcl_GetArabicValue( aRoman.GetChar( nCharIndex ), nDigit1, bIsDec1 ); 2528 if( bValid && (nCharIndex + 1 < nCharCount) ) 2529 bValid = lcl_GetArabicValue( aRoman.GetChar( nCharIndex + 1 ), nDigit2, bIsDec2 ); 2530 if( bValid ) 2531 { 2532 if( nDigit1 >= nDigit2 ) 2533 { 2534 nValue = sal::static_int_cast<sal_uInt16>( nValue + nDigit1 ); 2535 nValidRest %= (nDigit1 * (bIsDec1 ? 5 : 2)); 2536 bValid = (nValidRest >= nDigit1); 2537 if( bValid ) 2538 nValidRest = sal::static_int_cast<sal_uInt16>( nValidRest - nDigit1 ); 2539 nCharIndex++; 2540 } 2541 else if( nDigit1 * 2 != nDigit2 ) 2542 { 2543 sal_uInt16 nDiff = nDigit2 - nDigit1; 2544 nValue = sal::static_int_cast<sal_uInt16>( nValue + nDiff ); 2545 bValid = (nValidRest >= nDiff); 2546 if( bValid ) 2547 nValidRest = nDigit1 - 1; 2548 nCharIndex += 2; 2549 } 2550 else 2551 bValid = sal_False; 2552 } 2553 } 2554 if( bValid ) 2555 PushInt( nValue ); 2556 else 2557 PushIllegalArgument(); 2558 } 2559 } 2560 2561 2562 void ScInterpreter::ScHyperLink() 2563 { 2564 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScHyperLink" ); 2565 sal_uInt8 nParamCount = GetByte(); 2566 if ( MustHaveParamCount( nParamCount, 1, 2 ) ) 2567 { 2568 double fVal = 0.0; 2569 String aStr; 2570 ScMatValType nResultType = SC_MATVAL_STRING; 2571 2572 if ( nParamCount == 2 ) 2573 { 2574 switch ( GetStackType() ) 2575 { 2576 case svDouble: 2577 fVal = GetDouble(); 2578 nResultType = SC_MATVAL_VALUE; 2579 break; 2580 case svString: 2581 aStr = GetString(); 2582 break; 2583 case svSingleRef: 2584 case svDoubleRef: 2585 { 2586 ScAddress aAdr; 2587 if ( !PopDoubleRefOrSingleRef( aAdr ) ) 2588 break; 2589 ScBaseCell* pCell = GetCell( aAdr ); 2590 if (HasCellEmptyData( pCell)) 2591 nResultType = SC_MATVAL_EMPTY; 2592 else 2593 { 2594 sal_uInt16 nErr = GetCellErrCode( pCell ); 2595 if (nErr) 2596 SetError( nErr); 2597 else if (HasCellValueData( pCell)) 2598 { 2599 fVal = GetCellValue( aAdr, pCell ); 2600 nResultType = SC_MATVAL_VALUE; 2601 } 2602 else 2603 GetCellString( aStr, pCell ); 2604 } 2605 } 2606 break; 2607 case svMatrix: 2608 nResultType = GetDoubleOrStringFromMatrix( fVal, aStr); 2609 break; 2610 case svMissing: 2611 case svEmptyCell: 2612 Pop(); 2613 // mimic xcl 2614 fVal = 0.0; 2615 nResultType = SC_MATVAL_VALUE; 2616 break; 2617 default: 2618 PopError(); 2619 SetError( errIllegalArgument); 2620 } 2621 } 2622 String aUrl = GetString(); 2623 ScMatrixRef pResMat = GetNewMat( 1, 2); 2624 if (nGlobalError) 2625 { 2626 fVal = CreateDoubleError( nGlobalError); 2627 nResultType = SC_MATVAL_VALUE; 2628 } 2629 if (nParamCount == 2 || nGlobalError) 2630 { 2631 if (ScMatrix::IsValueType( nResultType)) 2632 pResMat->PutDouble( fVal, 0); 2633 else if (ScMatrix::IsRealStringType( nResultType)) 2634 pResMat->PutString( aStr, 0); 2635 else // EmptyType, EmptyPathType, mimic xcl 2636 pResMat->PutDouble( 0.0, 0 ); 2637 } 2638 else 2639 pResMat->PutString( aUrl, 0 ); 2640 pResMat->PutString( aUrl, 1 ); 2641 bMatrixFormula = true; 2642 PushMatrix(pResMat); 2643 } 2644 } 2645 2646 2647 sal_Bool lclConvertMoney( const String& aSearchUnit, double& rfRate, int& rnDec ) 2648 { 2649 struct ConvertInfo 2650 { 2651 const sal_Char* pCurrText; 2652 double fRate; 2653 int nDec; 2654 }; 2655 ConvertInfo aConvertTable[] = { 2656 { "EUR", 1.0, 2 }, 2657 { "ATS", 13.7603, 2 }, 2658 { "BEF", 40.3399, 0 }, 2659 { "DEM", 1.95583, 2 }, 2660 { "ESP", 166.386, 0 }, 2661 { "FIM", 5.94573, 2 }, 2662 { "FRF", 6.55957, 2 }, 2663 { "IEP", 0.787564, 2 }, 2664 { "ITL", 1936.27, 0 }, 2665 { "LUF", 40.3399, 0 }, 2666 { "NLG", 2.20371, 2 }, 2667 { "PTE", 200.482, 2 }, 2668 { "GRD", 340.750, 2 }, 2669 { "SIT", 239.640, 2 }, 2670 { "MTL", 0.429300, 2 }, 2671 { "CYP", 0.585274, 2 }, 2672 { "SKK", 30.1260, 2 } 2673 }; 2674 2675 const size_t nConversionCount = sizeof( aConvertTable ) / sizeof( aConvertTable[0] ); 2676 for ( size_t i = 0; i < nConversionCount; i++ ) 2677 if ( aSearchUnit.EqualsIgnoreCaseAscii( aConvertTable[i].pCurrText ) ) 2678 { 2679 rfRate = aConvertTable[i].fRate; 2680 rnDec = aConvertTable[i].nDec; 2681 return sal_True; 2682 } 2683 return sal_False; 2684 } 2685 2686 void ScInterpreter::ScEuroConvert() 2687 { //Value, FromUnit, ToUnit[, FullPrecision, [TriangulationPrecision]] 2688 sal_uInt8 nParamCount = GetByte(); 2689 if ( MustHaveParamCount( nParamCount, 3, 5 ) ) 2690 { 2691 double nPrecision = 0.0; 2692 if ( nParamCount == 5 ) 2693 { 2694 nPrecision = ::rtl::math::approxFloor(GetDouble()); 2695 if ( nPrecision < 3 ) 2696 { 2697 PushIllegalArgument(); 2698 return; 2699 } 2700 } 2701 sal_Bool bFullPrecision = sal_False; 2702 if ( nParamCount >= 4 ) 2703 bFullPrecision = GetBool(); 2704 String aToUnit( GetString() ); 2705 String aFromUnit( GetString() ); 2706 double fVal = GetDouble(); 2707 if ( nGlobalError ) 2708 PushError( nGlobalError); 2709 else 2710 { 2711 double fRes; 2712 double fFromRate; 2713 double fToRate; 2714 int nFromDec; 2715 int nToDec; 2716 String aEur( RTL_CONSTASCII_USTRINGPARAM("EUR")); 2717 if ( lclConvertMoney( aFromUnit, fFromRate, nFromDec ) 2718 && lclConvertMoney( aToUnit, fToRate, nToDec ) ) 2719 { 2720 if ( aFromUnit.EqualsIgnoreCaseAscii( aToUnit ) ) 2721 fRes = fVal; 2722 else 2723 { 2724 if ( aFromUnit.EqualsIgnoreCaseAscii( aEur ) ) 2725 fRes = fVal * fToRate; 2726 else 2727 { 2728 double fIntermediate = fVal / fFromRate; 2729 if ( nPrecision ) 2730 fIntermediate = ::rtl::math::round( fIntermediate, 2731 (int) nPrecision ); 2732 fRes = fIntermediate * fToRate; 2733 } 2734 if ( !bFullPrecision ) 2735 fRes = ::rtl::math::round( fRes, nToDec ); 2736 } 2737 PushDouble( fRes ); 2738 } 2739 else 2740 PushIllegalArgument(); 2741 } 2742 } 2743 } 2744 2745 2746 // BAHTTEXT =================================================================== 2747 2748 #define UTF8_TH_0 "\340\270\250\340\270\271\340\270\231\340\270\242\340\271\214" 2749 #define UTF8_TH_1 "\340\270\253\340\270\231\340\270\266\340\271\210\340\270\207" 2750 #define UTF8_TH_2 "\340\270\252\340\270\255\340\270\207" 2751 #define UTF8_TH_3 "\340\270\252\340\270\262\340\270\241" 2752 #define UTF8_TH_4 "\340\270\252\340\270\265\340\271\210" 2753 #define UTF8_TH_5 "\340\270\253\340\271\211\340\270\262" 2754 #define UTF8_TH_6 "\340\270\253\340\270\201" 2755 #define UTF8_TH_7 "\340\271\200\340\270\210\340\271\207\340\270\224" 2756 #define UTF8_TH_8 "\340\271\201\340\270\233\340\270\224" 2757 #define UTF8_TH_9 "\340\271\200\340\270\201\340\271\211\340\270\262" 2758 #define UTF8_TH_10 "\340\270\252\340\270\264\340\270\232" 2759 #define UTF8_TH_11 "\340\271\200\340\270\255\340\271\207\340\270\224" 2760 #define UTF8_TH_20 "\340\270\242\340\270\265\340\271\210" 2761 #define UTF8_TH_1E2 "\340\270\243\340\271\211\340\270\255\340\270\242" 2762 #define UTF8_TH_1E3 "\340\270\236\340\270\261\340\270\231" 2763 #define UTF8_TH_1E4 "\340\270\253\340\270\241\340\270\267\340\271\210\340\270\231" 2764 #define UTF8_TH_1E5 "\340\271\201\340\270\252\340\270\231" 2765 #define UTF8_TH_1E6 "\340\270\245\340\271\211\340\270\262\340\270\231" 2766 #define UTF8_TH_DOT0 "\340\270\226\340\271\211\340\270\247\340\270\231" 2767 #define UTF8_TH_BAHT "\340\270\232\340\270\262\340\270\227" 2768 #define UTF8_TH_SATANG "\340\270\252\340\270\225\340\270\262\340\270\207\340\270\204\340\271\214" 2769 #define UTF8_TH_MINUS "\340\270\245\340\270\232" 2770 2771 #define UTF8_STRINGPARAM( ascii ) ascii, static_cast< xub_StrLen >( sizeof( ascii ) - 1 ) 2772 #define UTF8_CREATE( ascii ) ByteString( UTF8_STRINGPARAM( ascii ) ) 2773 #define UTF8_APPEND( ascii ) Append( UTF8_STRINGPARAM( ascii ) ) 2774 #define UTF8_PREPEND( ascii ) Insert( UTF8_CREATE( ascii ), 0 ) 2775 2776 // local functions ------------------------------------------------------------ 2777 2778 namespace { 2779 2780 inline void lclSplitBlock( double& rfInt, sal_Int32& rnBlock, double fValue, double fSize ) 2781 { 2782 rnBlock = static_cast< sal_Int32 >( modf( (fValue + 0.1) / fSize, &rfInt ) * fSize + 0.1 ); 2783 } 2784 2785 /** Appends a digit (0 to 9) to the passed string. */ 2786 void lclAppendDigit( ByteString& rText, sal_Int32 nDigit ) 2787 { 2788 switch( nDigit ) 2789 { 2790 case 0: rText.UTF8_APPEND( UTF8_TH_0 ); break; 2791 case 1: rText.UTF8_APPEND( UTF8_TH_1 ); break; 2792 case 2: rText.UTF8_APPEND( UTF8_TH_2 ); break; 2793 case 3: rText.UTF8_APPEND( UTF8_TH_3 ); break; 2794 case 4: rText.UTF8_APPEND( UTF8_TH_4 ); break; 2795 case 5: rText.UTF8_APPEND( UTF8_TH_5 ); break; 2796 case 6: rText.UTF8_APPEND( UTF8_TH_6 ); break; 2797 case 7: rText.UTF8_APPEND( UTF8_TH_7 ); break; 2798 case 8: rText.UTF8_APPEND( UTF8_TH_8 ); break; 2799 case 9: rText.UTF8_APPEND( UTF8_TH_9 ); break; 2800 default: DBG_ERRORFILE( "lclAppendDigit - illegal digit" ); 2801 } 2802 } 2803 2804 /** Appends a value raised to a power of 10: nDigit*10^nPow10. 2805 @param nDigit A digit in the range from 1 to 9. 2806 @param nPow10 A value in the range from 2 to 5. 2807 */ 2808 void lclAppendPow10( ByteString& rText, sal_Int32 nDigit, sal_Int32 nPow10 ) 2809 { 2810 DBG_ASSERT( (1 <= nDigit) && (nDigit <= 9), "lclAppendPow10 - illegal digit" ); 2811 lclAppendDigit( rText, nDigit ); 2812 switch( nPow10 ) 2813 { 2814 case 2: rText.UTF8_APPEND( UTF8_TH_1E2 ); break; 2815 case 3: rText.UTF8_APPEND( UTF8_TH_1E3 ); break; 2816 case 4: rText.UTF8_APPEND( UTF8_TH_1E4 ); break; 2817 case 5: rText.UTF8_APPEND( UTF8_TH_1E5 ); break; 2818 default: DBG_ERRORFILE( "lclAppendPow10 - illegal power" ); 2819 } 2820 } 2821 2822 /** Appends a block of 6 digits (value from 1 to 999,999) to the passed string. */ 2823 void lclAppendBlock( ByteString& rText, sal_Int32 nValue ) 2824 { 2825 DBG_ASSERT( (1 <= nValue) && (nValue <= 999999), "lclAppendBlock - illegal value" ); 2826 if( nValue >= 100000 ) 2827 { 2828 lclAppendPow10( rText, nValue / 100000, 5 ); 2829 nValue %= 100000; 2830 } 2831 if( nValue >= 10000 ) 2832 { 2833 lclAppendPow10( rText, nValue / 10000, 4 ); 2834 nValue %= 10000; 2835 } 2836 if( nValue >= 1000 ) 2837 { 2838 lclAppendPow10( rText, nValue / 1000, 3 ); 2839 nValue %= 1000; 2840 } 2841 if( nValue >= 100 ) 2842 { 2843 lclAppendPow10( rText, nValue / 100, 2 ); 2844 nValue %= 100; 2845 } 2846 if( nValue > 0 ) 2847 { 2848 sal_Int32 nTen = nValue / 10; 2849 sal_Int32 nOne = nValue % 10; 2850 if( nTen >= 1 ) 2851 { 2852 if( nTen >= 3 ) 2853 lclAppendDigit( rText, nTen ); 2854 else if( nTen == 2 ) 2855 rText.UTF8_APPEND( UTF8_TH_20 ); 2856 rText.UTF8_APPEND( UTF8_TH_10 ); 2857 } 2858 if( (nTen > 0) && (nOne == 1) ) 2859 rText.UTF8_APPEND( UTF8_TH_11 ); 2860 else if( nOne > 0 ) 2861 lclAppendDigit( rText, nOne ); 2862 } 2863 } 2864 2865 } // namespace 2866 2867 // ---------------------------------------------------------------------------- 2868 2869 void ScInterpreter::ScBahtText() 2870 { 2871 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScBahtText" ); 2872 sal_uInt8 nParamCount = GetByte(); 2873 if ( MustHaveParamCount( nParamCount, 1 ) ) 2874 { 2875 double fValue = GetDouble(); 2876 if( nGlobalError ) 2877 { 2878 PushError( nGlobalError); 2879 return; 2880 } 2881 2882 // sign 2883 bool bMinus = fValue < 0.0; 2884 fValue = fabs( fValue ); 2885 2886 // round to 2 digits after decimal point, fValue contains Satang as integer 2887 fValue = ::rtl::math::approxFloor( fValue * 100.0 + 0.5 ); 2888 2889 // split Baht and Satang 2890 double fBaht = 0.0; 2891 sal_Int32 nSatang = 0; 2892 lclSplitBlock( fBaht, nSatang, fValue, 100.0 ); 2893 2894 ByteString aText; 2895 2896 // generate text for Baht value 2897 if( fBaht == 0.0 ) 2898 { 2899 if( nSatang == 0 ) 2900 aText.UTF8_APPEND( UTF8_TH_0 ); 2901 } 2902 else while( fBaht > 0.0 ) 2903 { 2904 ByteString aBlock; 2905 sal_Int32 nBlock = 0; 2906 lclSplitBlock( fBaht, nBlock, fBaht, 1.0e6 ); 2907 if( nBlock > 0 ) 2908 lclAppendBlock( aBlock, nBlock ); 2909 // add leading "million", if there will come more blocks 2910 if( fBaht > 0.0 ) 2911 aBlock.UTF8_PREPEND( UTF8_TH_1E6 ); 2912 aText.Insert( aBlock, 0 ); 2913 } 2914 if( aText.Len() > 0 ) 2915 aText.UTF8_APPEND( UTF8_TH_BAHT ); 2916 2917 // generate text for Satang value 2918 if( nSatang == 0 ) 2919 { 2920 aText.UTF8_APPEND( UTF8_TH_DOT0 ); 2921 } 2922 else 2923 { 2924 lclAppendBlock( aText, nSatang ); 2925 aText.UTF8_APPEND( UTF8_TH_SATANG ); 2926 } 2927 2928 // add the minus sign 2929 if( bMinus ) 2930 aText.UTF8_PREPEND( UTF8_TH_MINUS ); 2931 2932 PushString( String( aText, RTL_TEXTENCODING_UTF8 ) ); 2933 } 2934 } 2935 2936 // ============================================================================ 2937 2938 void ScInterpreter::ScGetPivotData() 2939 { 2940 RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetPivotData" ); 2941 sal_uInt8 nParamCount = GetByte(); 2942 2943 if ( MustHaveParamCount( nParamCount, 2, 30 ) ) 2944 { 2945 // there must be an even number of args 2946 // target, ref, then field/item pairs 2947 if( (nParamCount % 2) == 1) 2948 goto failed; 2949 2950 bool bOldSyntax = false; 2951 if ( nParamCount == 2 ) 2952 { 2953 // if the first parameter is a ref, assume old syntax 2954 StackVar eFirstType = GetStackType( 2 ); 2955 if ( eFirstType == svSingleRef || eFirstType == svDoubleRef ) 2956 bOldSyntax = true; 2957 } 2958 2959 ScDPGetPivotDataField aTarget; // target field, and returns result 2960 std::vector< ScDPGetPivotDataField > aFilters; 2961 String aFilterList; 2962 if ( bOldSyntax ) 2963 aFilterList = GetString(); // old syntax: second parameter is list of constraints 2964 else 2965 { 2966 // new syntax: separate name/value pairs 2967 2968 sal_uInt16 nFilterCount = nParamCount / 2 - 1; 2969 aFilters.resize( nFilterCount ); 2970 2971 sal_uInt16 i = nFilterCount; 2972 while( i-- > 0 ) 2973 { 2974 //! should allow numeric constraint values 2975 aFilters[i].mbValIsStr = sal_True; 2976 aFilters[i].maValStr = GetString(); 2977 2978 aFilters[i].maFieldName = GetString(); 2979 } 2980 } 2981 2982 // common to both syntaxes: a reference to the data pilot table 2983 2984 ScRange aBlock; 2985 switch ( GetStackType() ) 2986 { 2987 case svDoubleRef : 2988 PopDoubleRef( aBlock ); 2989 break; 2990 2991 case svSingleRef : 2992 { 2993 ScAddress aAddr; 2994 PopSingleRef( aAddr ); 2995 aBlock = aAddr; 2996 break; 2997 } 2998 default: 2999 goto failed; 3000 } 3001 // NOTE : MS Excel docs claim to use the 'most recent' which is not 3002 // exactly the same as what we do in ScDocument::GetDPAtBlock 3003 // However we do need to use GetDPABlock 3004 ScDPObject* pDPObj = pDok->GetDPAtBlock ( aBlock ); 3005 if( NULL == pDPObj) 3006 goto failed; 3007 3008 if ( bOldSyntax ) 3009 { 3010 // fill aFilters / aTarget from aFilterList string 3011 if ( !pDPObj->ParseFilters( aTarget, aFilters, aFilterList ) ) 3012 goto failed; 3013 } 3014 else 3015 aTarget.maFieldName = GetString(); // new syntax: first parameter is data field name 3016 3017 if( pDPObj->GetPivotData( aTarget, aFilters ) ) 3018 { 3019 if( aTarget.mbValIsStr ) 3020 PushString( aTarget.maValStr ); 3021 else 3022 PushDouble( aTarget.mnValNum ); 3023 return; 3024 } 3025 } 3026 3027 failed : 3028 PushError( errNoRef ); 3029 } 3030 3031