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