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