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