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