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