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