| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/LibreOffice/sc/source/core/tool/ (Office von Apache Version 25.8.3.2©) Datei vom 5.10.2025 mit Größe 117 kB |
|
SSL interpr2.cxxSprache: C |
|||||||||||||||
|
/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ /* * This file is part of the LibreOffice project. * * This Source Code Form is subject to the terms of the Mozilla Public * License, v. 2.0. If a copy of the MPL was not distributed with this * file, You can obtain one at http://mozilla.org/MPL/2.0/. * * This file incorporates work covered by the following license notice: * * Licensed to the Apache Software Foundation (ASF) under one or more * contributor license agreements. See the NOTICE file distributed * with this work for additional information regarding copyright * ownership. The ASF licenses this file to you under the Apache * License, Version 2.0 (the "License"); you may not use this file * except in compliance with the License. You may obtain a copy of * the License at http://www.apache.org/licenses/LICENSE-2.0 . */ #include <memory> #include <interpre.hxx> #include <comphelper/string.hxx> #include <o3tl/float_int_conversion.hxx> #include <o3tl/string_view.hxx> #include <sfx2/bindings.hxx> #include <sfx2/linkmgr.hxx> #include <sfx2/objsh.hxx> #include <svl/numformat.hxx> #include <svl/zforlist.hxx> #include <tools/duration.hxx> #include <sal/macros.h> #include <osl/diagnose.h> #include <sc.hrc> #include <ddelink.hxx> #include <scmatrix.hxx> #include <formulacell.hxx> #include <document.hxx> #include <dociter.hxx> #include <docsh.hxx> #include <unitconv.hxx> #include <hints.hxx> #include <dpobject.hxx> #include <tokenarray.hxx> #include <globalnames.hxx> #include <stlpool.hxx> #include <stlsheet.hxx> #include <dpcache.hxx> #include <com/sun/star/sheet/DataPilotFieldFilter.hpp> #include <string.h> using ::std::vector; using namespace com::sun::star; using namespace formula; #define SCdEpsilon 1.0E-7 // Date and Time double ScInterpreter::GetDateSerial( sal_Int16 nYear, sal_Int16 nMonth, sal_Int16 nDay, bool bStrict ) { if ( nYear < 100 && !bStrict ) nYear = mrContext.NFExpandTwoDigitYear( nYear ); // Do not use a default Date ctor here because it asks system time with a // performance penalty. sal_Int16 nY, nM, nD; if (bStrict) { nY = nYear; nM = nMonth; nD = nDay; } else { if (nMonth > 0) { nY = nYear + (nMonth-1) / 12; nM = ((nMonth-1) % 12) + 1; } else { nY = nYear + (nMonth-12) / 12; nM = 12 - (-nMonth) % 12; } nD = 1; } Date aDate( nD, nM, nY); if (!bStrict) aDate.AddDays( nDay - 1 ); if (aDate.IsValidAndGregorian()) return static_cast<double>(aDate - mrContext.NFGetNullDate()); else { SetError(FormulaError::NoValue); return 0; } } void ScInterpreter::ScGetActDate() { nFuncFmtType = SvNumFormatType::DATE; Date aActDate( Date::SYSTEM ); tools::Long nDiff = aActDate - mrContext.NFGetNullDate(); PushDouble(static_cast<double>(nDiff)); } void ScInterpreter::ScGetActTime() { nFuncFmtType = SvNumFormatType::DATETIME; DateTime aActTime( DateTime::SYSTEM ); tools::Long nDiff = aActTime - mrContext.NFGetNullDate(); double fTime = aActTime.GetHour() / static_cast<double>(::tools::Time::hourPerDay) + aActTime.GetMin() / static_cast<double>(::tools::Time::minutePerDay) + aActTime.GetSec() / static_cast<double>(::tools::Time::secondPerDay) + aActTime.GetNanoSec() / static_cast<double>(::tools::Time::nanoSecPerDay); PushDouble( static_cast<double>(nDiff) + fTime ); } void ScInterpreter::ScGetYear() { Date aDate = mrContext.NFGetNullDate(); aDate.AddDays( GetFloor32()); PushDouble( static_cast<double>(aDate.GetYear()) ); } void ScInterpreter::ScGetMonth() { Date aDate = mrContext.NFGetNullDate(); aDate.AddDays( GetFloor32()); PushDouble( static_cast<double>(aDate.GetMonth()) ); } void ScInterpreter::ScGetDay() { Date aDate = mrContext.NFGetNullDate(); aDate.AddDays( GetFloor32()); PushDouble(static_cast<double>(aDate.GetDay())); } void ScInterpreter::ScGetMin() { sal_uInt16 nHour, nMinute, nSecond; double fFractionOfSecond; tools::Time::GetClock( GetDouble(), nHour, nMinute, nSecond, fFractionOfSecond, 0); PushDouble( nMinute); } void ScInterpreter::ScGetSec() { sal_uInt16 nHour, nMinute, nSecond; double fFractionOfSecond; tools::Time::GetClock( GetDouble(), nHour, nMinute, nSecond, fFractionOfSecond, 0); if ( fFractionOfSecond >= 0.5 ) nSecond = ( nSecond + 1 ) % 60; PushDouble( nSecond ); } void ScInterpreter::ScGetHour() { sal_uInt16 nHour, nMinute, nSecond; double fFractionOfSecond; tools::Time::GetClock( GetDouble(), nHour, nMinute, nSecond, fFractionOfSecond, 0); PushDouble( nHour); } void ScInterpreter::ScGetDateValue() { OUString aInputString = GetString().getString(); sal_uInt32 nFIndex = 0; // for a default country/language double fVal; if (mrContext.NFIsNumberFormat(aInputString, nFIndex, fVal)) { SvNumFormatType eType = mrContext.NFGetType(nFIndex); if (eType == SvNumFormatType::DATE || eType == SvNumFormatType::DATETIME) { nFuncFmtType = SvNumFormatType::DATE; PushDouble(::rtl::math::approxFloor(fVal)); } else PushIllegalArgument(); } else PushIllegalArgument(); } void ScInterpreter::ScGetDayOfWeek() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; sal_Int16 nFlag; if (nParamCount == 2) nFlag = GetInt16(); else nFlag = 1; Date aDate = mrContext.NFGetNullDate(); aDate.AddDays( GetFloor32()); int nVal = static_cast<int>(aDate.GetDayOfWeek()); // MONDAY = 0 switch (nFlag) { case 1: // Sunday = 1 if (nVal == 6) nVal = 1; else nVal += 2; break; case 2: // Monday = 1 nVal += 1; break; case 3: // Monday = 0 ; // nothing break; case 11: // Monday = 1 case 12: // Tuesday = 1 case 13: // Wednesday = 1 case 14: // Thursday = 1 case 15: // Friday = 1 case 16: // Saturday = 1 case 17: // Sunday = 1 if (nVal < nFlag - 11) // x = nFlag - 11 = 0,1,2,3,4,5,6 nVal += 19 - nFlag; // nVal += (8 - (nFlag - 11) = 8 - x = 8,7,6,5,4,3,2) else nVal -= nFlag - 12; // nVal -= ((nFlag - 11) - 1 = x - 1 = -1,0,1,2,3,4,5) break; default: SetError( FormulaError::IllegalArgument); } PushInt( nVal ); } void ScInterpreter::ScWeeknumOOo() { if ( MustHaveParamCount( GetByte(), 2 ) ) { sal_Int16 nFlag = GetInt16(); Date aDate = mrContext.NFGetNullDate(); aDate.AddDays( GetFloor32()); PushInt( static_cast<int>(aDate.GetWeekOfYear( nFlag == 1 ? SUNDAY : MONDAY ))); } } void ScInterpreter::ScGetWeekOfYear() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; sal_Int16 nFlag = (nParamCount == 1) ? 1 : GetInt16WithDefault(1); Date aDate = mrContext.NFGetNullDate(); aDate.AddDays( GetFloor32()); sal_Int32 nMinimumNumberOfDaysInWeek; DayOfWeek eFirstDayOfWeek; switch ( nFlag ) { case 1 : eFirstDayOfWeek = SUNDAY; nMinimumNumberOfDaysInWeek = 1; break; case 2 : eFirstDayOfWeek = MONDAY; nMinimumNumberOfDaysInWeek = 1; break; case 11 : case 12 : case 13 : case 14 : case 15 : case 16 : case 17 : eFirstDayOfWeek = static_cast<DayOfWeek>( nFlag - 11 ); // MONDAY := 0 nMinimumNumberOfDaysInWeek = 1; //the week containing January 1 is week 1 break; case 21 : case 150 : // ISO 8601 eFirstDayOfWeek = MONDAY; nMinimumNumberOfDaysInWeek = 4; break; default : PushIllegalArgument(); return; } PushInt( static_cast<int>(aDate.GetWeekOfYear( eFirstDayOfWeek, nMinimumNumberOfDaysI } void ScInterpreter::ScGetIsoWeekOfYear() { if ( MustHaveParamCount( GetByte(), 1 ) ) { Date aDate = mrContext.NFGetNullDate(); aDate.AddDays( GetFloor32()); PushInt( static_cast<int>(aDate.GetWeekOfYear()) ); } } void ScInterpreter::ScEasterSunday() { nFuncFmtType = SvNumFormatType::DATE; if ( !MustHaveParamCount( GetByte(), 1 ) ) return; sal_Int16 nYear = GetInt16(); if (nGlobalError != FormulaError::NONE) { PushError( nGlobalError); return; } if ( nYear < 100 ) nYear = mrContext.NFExpandTwoDigitYear( nYear ); if (nYear < 1583 || nYear > 9956) { // Valid Gregorian and maximum year constraints not met. PushIllegalArgument(); return; } // don't worry, be happy :) int B,C,D,E,F,G,H,I,K,L,M,N,O; N = nYear % 19; B = int(nYear / 100); C = nYear % 100; D = int(B / 4); E = B % 4; F = int((B + 8) / 25); G = int((B - F + 1) / 3); H = (19 * N + B - D - G + 15) % 30; I = int(C / 4); K = C % 4; L = (32 + 2 * E + 2 * I - H - K) % 7; M = int((N + 11 * H + 22 * L) / 451); O = H + L - 7 * M + 114; sal_Int16 nDay = sal::static_int_cast<sal_Int16>( O % 31 + 1 ); sal_Int16 nMonth = sal::static_int_cast<sal_Int16>( int(O / 31) ); PushDouble( GetDateSerial( nYear, nMonth, nDay, true ) ); } FormulaError ScInterpreter::GetWeekendAndHolidayMasks( const sal_uInt8 nParamCount, const sal_Int32 nNullDate, vector< double >& rSortArray, bool bWeekendMask[ 7 ] ) { if ( nParamCount == 4 ) { vector< double > nWeekendDays; GetNumberSequenceArray( 1, nWeekendDays, false ); if ( nGlobalError != FormulaError::NONE ) return nGlobalError; else { if ( nWeekendDays.size() != 7 ) return FormulaError::IllegalArgument; // Weekend days defined by string, Sunday...Saturday for ( int i = 0; i < 7; i++ ) bWeekendMask[ i ] = static_cast<bool>(nWeekendDays[ ( i == 6 ? 0 : i + 1 ) ]); } } else { for ( int i = 0; i < 7; i++ ) bWeekendMask[ i] = false; bWeekendMask[ SATURDAY ] = true; bWeekendMask[ SUNDAY ] = true; } if ( nParamCount >= 3 ) { GetSortArray( 1, rSortArray, nullptr, true, true ); size_t nMax = rSortArray.size(); for ( size_t i = 0; i < nMax; i++ ) rSortArray.at( i ) = ::rtl::math::approxFloor( rSortArray.at( i ) ) + nNullDate; } return nGlobalError; } FormulaError ScInterpreter::GetWeekendAndHolidayMasks_MS( const sal_uInt8 nParamCount, const sal_Int32 nNullDate, vector< double >& rSortArray, bool bWeekendMask[ 7 ], bool bWorkdayFunction ) { FormulaError nErr = FormulaError::NONE; OUString aWeekendDays; if ( nParamCount == 4 ) { GetSortArray( 1, rSortArray, nullptr, true, true ); size_t nMax = rSortArray.size(); for ( size_t i = 0; i < nMax; i++ ) rSortArray.at( i ) = ::rtl::math::approxFloor( rSortArray.at( i ) ) + nNullDate; } if ( nParamCount >= 3 ) { if ( IsMissing() ) Pop(); else { switch ( GetStackType() ) { case svDoubleRef : case svExternalDoubleRef : return FormulaError::NoValue; default : { double fDouble; svl::SharedString aSharedString; bool bDouble = GetDoubleOrString( fDouble, aSharedString); if ( bDouble ) { if ( fDouble >= 1.0 && fDouble <= 17 ) aWeekendDays = OUString::number( fDouble ); else return FormulaError::NoValue; } else { if ( aSharedString.isEmpty() || aSharedString.getLength() != 7 || ( bWorkdayFunction && aSharedString.getString() == "1111111" ) ) return FormulaError::NoValue; else aWeekendDays = aSharedString.getString(); } } break; } } } for ( int i = 0; i < 7; i++ ) bWeekendMask[ i] = false; if ( aWeekendDays.isEmpty() ) { bWeekendMask[ SATURDAY ] = true; bWeekendMask[ SUNDAY ] = true; } else { switch ( aWeekendDays.getLength() ) { case 1 : // Weekend days defined by code switch ( aWeekendDays[ 0 ] ) { case '1' : bWeekendMask[ SATURDAY ] = true; bWeekendMask[ SUNDAY ] = true; break; case '2' : bWeekendMask[ SUNDAY ] = true; bWeekendMask[ MONDAY ] = true; break; case '3' : bWeekendMask[ MONDAY ] = true; bWeekendMask[ TUESDAY ] = true; break; case '4' : bWeekendMask[ TUESDAY ] = true; bWeekendMask[ WEDNESDAY ] = true; break; case '5' : bWeekendMask[ WEDNESDAY ] = true; bWeekendMask[ THURSDAY ] = true; break; case '6' : bWeekendMask[ THURSDAY ] = true; bWeekendMask[ FRIDAY ] = true; break; case '7' : bWeekendMask[ FRIDAY ] = true; bWeekendMask[ SATURDAY ] = true; break; default : nErr = FormulaError::IllegalArgument; break; } break; case 2 : // Weekend day defined by code if ( aWeekendDays[ 0 ] == '1' ) { switch ( aWeekendDays[ 1 ] ) { case '1' : bWeekendMask[ SUNDAY ] = true; break; case '2' : bWeekendMask[ MONDAY ] = true; break; case '3' : bWeekendMask[ TUESDAY ] = true; break; case '4' : bWeekendMask[ WEDNESDAY ] = true; break; case '5' : bWeekendMask[ THURSDAY ] = true; break; case '6' : bWeekendMask[ FRIDAY ] = true; break; case '7' : bWeekendMask[ SATURDAY ] = true; break; default : nErr = FormulaError::IllegalArgument; break; } } else nErr = FormulaError::IllegalArgument; break; case 7 : // Weekend days defined by string for ( int i = 0; i < 7 && nErr == FormulaError::NONE; i++ ) { switch ( aWeekendDays[ i ] ) { case '0' : bWeekendMask[ i ] = false; break; case '1' : bWeekendMask[ i ] = true; break; default : nErr = FormulaError::IllegalArgument; break; } } break; default : nErr = FormulaError::IllegalArgument; break; } } return nErr; } void ScInterpreter::ScNetWorkdays( bool bOOXML_Version ) { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 2, 4 ) ) return; vector<double> nSortArray; bool bWeekendMask[ 7 ]; const Date& rNullDate = mrContext.NFGetNullDate(); sal_Int32 nNullDate = rNullDate.GetAsNormalizedDays(); FormulaError nErr; if ( bOOXML_Version ) { nErr = GetWeekendAndHolidayMasks_MS( nParamCount, nNullDate, nSortArray, bWeekendMask, false ); } else { nErr = GetWeekendAndHolidayMasks( nParamCount, nNullDate, nSortArray, bWeekendMask ); } if ( nErr != FormulaError::NONE ) PushError( nErr ); else { sal_Int32 nDate2 = GetFloor32(); sal_Int32 nDate1 = GetFloor32(); if (nGlobalError != FormulaError::NONE || (nDate1 > SAL_MAX_INT32 - nNullDate) || nDate2 > (SAL { PushIllegalArgument(); return; } nDate2 += nNullDate; nDate1 += nNullDate; sal_Int32 nCnt = 0; size_t nRef = 0; bool bReverse = ( nDate1 > nDate2 ); if ( bReverse ) std::swap( nDate1, nDate2 ); size_t nMax = nSortArray.size(); while ( nDate1 <= nDate2 ) { if ( !bWeekendMask[ GetDayOfWeek( nDate1 ) ] ) { while ( nRef < nMax && nSortArray.at( nRef ) < nDate1 ) nRef++; if ( nRef >= nMax || nSortArray.at( nRef ) != nDate1 ) nCnt++; } ++nDate1; } PushDouble( static_cast<double>( bReverse ? -nCnt : nCnt ) ); } } void ScInterpreter::ScWorkday_MS() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 2, 4 ) ) return; nFuncFmtType = SvNumFormatType::DATE; vector<double> nSortArray; bool bWeekendMask[ 7 ]; const Date& rNullDate = mrContext.NFGetNullDate(); sal_Int32 nNullDate = rNullDate.GetAsNormalizedDays(); FormulaError nErr = GetWeekendAndHolidayMasks_MS( nParamCount, nNullDate, nSortArray, bWeekendMask, true ); if ( nErr != FormulaError::NONE ) PushError( nErr ); else { sal_Int32 nDays = GetFloor32(); sal_Int32 nDate = GetFloor32(); if (nGlobalError != FormulaError::NONE || (nDate > SAL_MAX_INT32 - nNullDate)) { PushIllegalArgument(); return; } nDate += nNullDate; if ( !nDays ) PushDouble( static_cast<double>( nDate - nNullDate ) ); else { size_t nMax = nSortArray.size(); if ( nDays > 0 ) { size_t nRef = 0; while ( nDays ) { do { ++nDate; } while ( bWeekendMask[ GetDayOfWeek( nDate ) ] ); //jump over weekend day(s) while ( nRef < nMax && nSortArray.at( nRef ) < nDate ) nRef++; if ( nRef >= nMax || nSortArray.at( nRef ) != nDate ) nDays--; } } else { sal_Int16 nRef = nMax - 1; while ( nDays ) { do { --nDate; } while ( bWeekendMask[ GetDayOfWeek( nDate ) ] ); //jump over weekend day(s) while ( nRef >= 0 && nSortArray.at( nRef ) > nDate ) nRef--; if (nRef < 0 || nSortArray.at(nRef) != nDate) nDays++; } } PushDouble( static_cast<double>( nDate - nNullDate ) ); } } } void ScInterpreter::ScGetDate() { nFuncFmtType = SvNumFormatType::DATE; if ( !MustHaveParamCount( GetByte(), 3 ) ) return; sal_Int16 nDay = GetInt16(); sal_Int16 nMonth = GetInt16(); if (IsMissing()) SetError( FormulaError::ParameterExpected); // Year must be given. sal_Int16 nYear = GetInt16(); if (nGlobalError != FormulaError::NONE || nYear < 0) PushIllegalArgument(); else PushDouble(GetDateSerial(nYear, nMonth, nDay, false)); } void ScInterpreter::ScGetTime() { nFuncFmtType = SvNumFormatType::TIME; if ( MustHaveParamCount( GetByte(), 3 ) ) { double fSec = GetDouble(); double fMin = GetDouble(); double fHour = GetDouble(); double fTime = fmod( (fHour * ::tools::Time::secondPerHour) + (fMin * ::tools::Time::secon if (fTime < 0) PushIllegalArgument(); else PushDouble( fTime); } } void ScInterpreter::ScGetDiffDate() { if ( MustHaveParamCount( GetByte(), 2 ) ) { double fDate2 = GetDouble(); double fDate1 = GetDouble(); PushDouble(fDate1 - fDate2); } } void ScInterpreter::ScGetDiffDate360() { /* Implementation follows * http://www.bondmarkets.com/eCommerce/SMD_Fields_030802.pdf * Appendix B: Day-Count Bases, there are 7 different ways to calculate the * 30-days count. That document also claims that Excel implements the "PSA * 30" or "NASD 30" method (funny enough they also state that Excel is the * only tool that does so). * * Note that the definition given in * http://msdn.microsoft.com/library/en-us/office97/html/SEB7C.asp * is _not_ the way how it is actually calculated by Excel (that would not * even match any of the 7 methods mentioned above) and would result in the * following test cases producing wrong results according to that appendix B: * * 28-Feb-95 31-Aug-95 181 instead of 180 * 29-Feb-96 31-Aug-96 181 instead of 180 * 30-Jan-96 31-Mar-96 61 instead of 60 * 31-Jan-96 31-Mar-96 61 instead of 60 * * Still, there is a difference between OOoCalc and Excel: * In Excel: * 02-Feb-99 31-Mar-00 results in 419 * 31-Mar-00 02-Feb-99 results in -418 * In Calc the result is 419 respectively -419. I consider the -418 a bug in Excel. */ sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 2, 3 ) ) return; bool bFlag = nParamCount == 3 && GetBool(); sal_Int32 nDate2 = GetFloor32(); sal_Int32 nDate1 = GetFloor32(); if (nGlobalError != FormulaError::NONE) PushError( nGlobalError); else { sal_Int32 nSign; // #i84934# only for non-US European algorithm swap dates. Else // follow Excel's meaningless extrapolation for "interoperability". if (bFlag && (nDate2 < nDate1)) { nSign = nDate1; nDate1 = nDate2; nDate2 = nSign; nSign = -1; } else nSign = 1; Date aDate1 = mrContext.NFGetNullDate(); aDate1.AddDays( nDate1); Date aDate2 = mrContext.NFGetNullDate(); aDate2.AddDays( nDate2); if (aDate1.GetDay() == 31) aDate1.AddDays( -1); else if (!bFlag) { if (aDate1.GetMonth() == 2) { switch ( aDate1.GetDay() ) { case 28 : if ( !aDate1.IsLeapYear() ) aDate1.SetDay(30); break; case 29 : aDate1.SetDay(30); break; } } } if (aDate2.GetDay() == 31) { if (!bFlag ) { if (aDate1.GetDay() == 30) aDate2.AddDays( -1); } else aDate2.SetDay(30); } PushDouble( static_cast<double>(nSign) * ( static_cast<double>(aDate2.GetDay()) + static_cast<double>(aDate2.GetMonth()) * 30.0 + static_cast<double>(aDate2.GetYear()) * 360.0 - static_cast<double>(aDate1.GetDay()) - static_cast<double>(aDate1.GetMonth()) * 30.0 - static_cast<double>(aDate1.GetYear()) * 360.0) ); } } // fdo#44456 function DATEDIF as defined in ODF1.2 (Par. 6.10.3) void ScInterpreter::ScGetDateDif() { if ( !MustHaveParamCount( GetByte(), 3 ) ) return; OUString aInterval = GetString().getString(); sal_Int32 nDate2 = GetFloor32(); sal_Int32 nDate1 = GetFloor32(); if (nGlobalError != FormulaError::NONE) { PushError( nGlobalError); return; } // Excel doesn't swap dates or return negative numbers, so don't we. if (nDate1 > nDate2) { PushIllegalArgument(); return; } double dd = nDate2 - nDate1; // Zero difference or number of days can be returned immediately. if (dd == 0.0 || aInterval.equalsIgnoreAsciiCase( "d" )) { PushDouble( dd ); return; } // split dates in day, month, year for use with formats other than "d" sal_uInt16 d1, m1, d2, m2; sal_Int16 y1, y2; Date aDate1( mrContext.NFGetNullDate()); aDate1.AddDays( nDate1); y1 = aDate1.GetYear(); m1 = aDate1.GetMonth(); d1 = aDate1.GetDay(); Date aDate2( mrContext.NFGetNullDate()); aDate2.AddDays( nDate2); y2 = aDate2.GetYear(); m2 = aDate2.GetMonth(); d2 = aDate2.GetDay(); // Close the year 0 gap to calculate year difference. if (y1 < 0 && y2 > 0) ++y1; else if (y1 > 0 && y2 < 0) ++y2; if ( aInterval.equalsIgnoreAsciiCase( "m" ) ) { // Return number of months. int md = m2 - m1 + 12 * (y2 - y1); if (d1 > d2) --md; PushInt( md ); } else if ( aInterval.equalsIgnoreAsciiCase( "y" ) ) { // Return number of years. int yd; if ( y2 > y1 ) { if (m2 > m1 || (m2 == m1 && d2 >= d1)) yd = y2 - y1; // complete years between dates else yd = y2 - y1 - 1; // one incomplete year } else { // Year is equal as we don't allow reversed arguments, no // complete year between dates. yd = 0; } PushInt( yd ); } else if ( aInterval.equalsIgnoreAsciiCase( "md" ) ) { // Return number of days, excluding months and years. // This is actually the remainder of days when subtracting years // and months from the difference of dates. Birthday-like 23 years // and 10 months and 19 days. // Algorithm's roll-over behavior extracted from Excel by try and // error... // If day1 <= day2 then simply day2 - day1. // If day1 > day2 then set month1 to month2-1 and year1 to // year2(-1) and subtract dates, e.g. for 2012-01-28,2012-03-01 set // 2012-02-28 and then (2012-03-01)-(2012-02-28) => 2 days (leap // year). // For 2011-01-29,2011-03-01 the non-existent 2011-02-29 rolls over // to 2011-03-01 so the result is 0. Same for day 31 in months with // only 30 days. tools::Long nd; if (d1 <= d2) nd = d2 - d1; else { if (m2 == 1) { aDate1.SetYear( y2 == 1 ? -1 : y2 - 1 ); aDate1.SetMonth( 12 ); } else { aDate1.SetYear( y2 ); aDate1.SetMonth( m2 - 1 ); } aDate1.Normalize(); nd = aDate2 - aDate1; } PushDouble( nd ); } else if ( aInterval.equalsIgnoreAsciiCase( "ym" ) ) { // Return number of months, excluding years. int md = m2 - m1 + 12 * (y2 - y1); if (d1 > d2) --md; md %= 12; PushInt( md ); } else if ( aInterval.equalsIgnoreAsciiCase( "yd" ) ) { // Return number of days, excluding years. // Condition corresponds with "y". if (m2 > m1 || (m2 == m1 && d2 >= d1)) aDate1.SetYear( y2 ); else aDate1.SetYear( y2 - 1 ); // XXX NOTE: Excel for the case 1988-06-22,2012-05-11 returns // 323, whereas the result here is 324. Don't they use the leap // year of 2012? // http://www.cpearson.com/excel/datedif.aspx "DATEDIF And Leap // Years" is not correct and Excel 2010 correctly returns 0 in // both cases mentioned there. Also using year1 as mentioned // produces incorrect results in other cases and different from // Excel 2010. Apparently they fixed some calculations. aDate1.Normalize(); double fd = aDate2 - aDate1; PushDouble( fd ); } else PushIllegalArgument(); // unsupported format } void ScInterpreter::ScGetTimeValue() { OUString aInputString = GetString().getString(); sal_uInt32 nFIndex = 0; // damit default Land/Spr. double fVal; if (mrContext.NFIsNumberFormat(aInputString, nFIndex, fVal, SvNumInputOptions::LAX_T { SvNumFormatType eType = mrContext.NFGetType(nFIndex); if (eType == SvNumFormatType::TIME || eType == SvNumFormatType::DATETIME) { nFuncFmtType = SvNumFormatType::TIME; double fDateVal = rtl::math::approxFloor(fVal); double fTimeVal = fVal - fDateVal; fTimeVal = ::tools::Duration(fTimeVal).GetInDays(); // force corrected PushDouble(fTimeVal); } else PushIllegalArgument(); } else PushIllegalArgument(); } void ScInterpreter::ScPlusMinus() { double fVal = GetDouble(); short n = 0; if (fVal < 0.0) n = -1; else if (fVal > 0.0) n = 1; PushInt( n ); } void ScInterpreter::ScAbs() { PushDouble(std::abs(GetDouble())); } void ScInterpreter::ScInt() { PushDouble(::rtl::math::approxFloor(GetDouble())); } void ScInterpreter::RoundNumber( rtl_math_RoundingMode eMode ) { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; double fVal = 0.0; if (nParamCount == 1) fVal = ::rtl::math::round( GetDouble(), 0, eMode ); else { const sal_Int16 nDec = GetInt16(); const double fX = GetDouble(); if (nGlobalError == FormulaError::NONE) { // A quite aggressive approach with 12 significant digits. // However, using 14 or some other doesn't work because other // values may fail, like =ROUNDDOWN(2-5E-015;13) would produce // 2 (another example in tdf#124286). constexpr sal_Int16 kSigDig = 12; if ( ( eMode == rtl_math_RoundingMode_Down || eMode == rtl_math_RoundingMode_Up ) && nDec < kSigDig && fmod( fX, 1.0 ) != 0.0 ) { // tdf124286 : round to significant digits before rounding // down or up to avoid unexpected rounding errors // caused by decimal -> binary -> decimal conversion double fRes = fX; // Similar to RoundSignificant() but omitting the back-scaling // and interim integer rounding before the final rounding, // which would result in double rounding. Instead, adjust the // decimals and round into integer part before scaling back. const double fTemp = floor( log10( std::abs(fRes))) + 1.0 - kSigDig; // Avoid inaccuracy of negative powers of 10. if (fTemp < 0.0) fRes *= pow(10.0, -fTemp); else fRes /= pow(10.0, fTemp); if (std::isfinite(fRes)) { // fRes is now at a decimal normalized scale. // Truncate up-rounding to opposite direction for values // like 0.0600000000000005 =ROUNDUP(8.06-8;2) that here now // is 600000000000.005 and otherwise would yield 0.07 if (eMode == rtl_math_RoundingMode_Up) fRes = ::rtl::math::approxFloor(fRes); fVal = ::rtl::math::round( fRes, nDec + fTemp, eMode ); if (fTemp < 0.0) fVal /= pow(10.0, -fTemp); else fVal *= pow(10.0, fTemp); } else { // Overflow. Let our round() decide if and how to round. fVal = ::rtl::math::round( fX, nDec, eMode ); } } else fVal = ::rtl::math::round( fX, nDec, eMode ); } } PushDouble(fVal); } void ScInterpreter::ScRound() { RoundNumber( rtl_math_RoundingMode_Corrected ); } void ScInterpreter::ScRoundDown() { RoundNumber( rtl_math_RoundingMode_Down ); } void ScInterpreter::ScRoundUp() { RoundNumber( rtl_math_RoundingMode_Up ); } void ScInterpreter::RoundSignificant( double fX, double fDigits, double &fRes ) { double fTemp = floor( log10( std::abs(fX) ) ) + 1.0 - fDigits; double fIn = fX; // Avoid inaccuracy of negative powers of 10. if (fTemp < 0.0) fIn *= pow(10.0, -fTemp); else fIn /= pow(10.0, fTemp); // For very large fX there might be an overflow in fIn resulting in // non-finite. rtl::math::round() handles that and it will be propagated as // usual. fRes = ::rtl::math::round(fIn); if (fTemp < 0.0) fRes /= pow(10.0, -fTemp); else fRes *= pow(10.0, fTemp); } // tdf#105931 void ScInterpreter::ScRoundSignificant() { if ( !MustHaveParamCount( GetByte(), 2 ) ) return; double fDigits = ::rtl::math::approxFloor( GetDouble() ); double fX = GetDouble(); if ( nGlobalError != FormulaError::NONE || fDigits < 1.0 ) { PushIllegalArgument(); return; } if ( fX == 0.0 ) PushDouble( 0.0 ); else { double fRes; RoundSignificant( fX, fDigits, fRes ); PushDouble( fRes ); } } /** tdf69552 ODFF1.2 function CEILING and Excel function CEILING.MATH In essence, the difference between the two is that ODFF-CEILING needs to have arguments value and significance of the same sign and with CEILING.MATH the sign of argument significance is irrevelevant. This is why ODFF-CEILING is exported to Excel as CEILING.MATH and CEILING.MATH is imported in Calc as CEILING.MATH */ void ScInterpreter::ScCeil( bool bODFF ) { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 3 ) ) return; bool bAbs = nParamCount == 3 && GetBool(); double fDec, fVal; if ( nParamCount == 1 ) { fVal = GetDouble(); fDec = ( fVal < 0 ? -1 : 1 ); } else { bool bArgumentMissing = IsMissing(); fDec = GetDouble(); fVal = GetDouble(); if ( bArgumentMissing ) fDec = ( fVal < 0 ? -1 : 1 ); } if ( fVal == 0 || fDec == 0.0 ) PushInt( 0 ); else { if ( bODFF && fVal * fDec < 0 ) PushIllegalArgument(); else { if ( fVal * fDec < 0.0 ) fDec = -fDec; if ( !bAbs && fVal < 0.0 ) PushDouble(::rtl::math::approxFloor( fVal / fDec ) * fDec ); else PushDouble(::rtl::math::approxCeil( fVal / fDec ) * fDec ); } } } void ScInterpreter::ScCeil_MS() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 2 ) ) return; double fDec = GetDouble(); double fVal = GetDouble(); if ( fVal == 0 || fDec == 0.0 ) PushInt(0); else if ( fVal * fDec > 0 ) PushDouble(::rtl::math::approxCeil( fVal / fDec ) * fDec ); else if ( fVal < 0.0 ) PushDouble(::rtl::math::approxFloor( fVal / -fDec ) * -fDec ); else PushIllegalArgument(); } void ScInterpreter::ScCeil_Precise() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; double fDec, fVal; if ( nParamCount == 1 ) { fVal = GetDouble(); fDec = 1.0; } else { fDec = std::abs( GetDoubleWithDefault( 1.0 )); fVal = GetDouble(); } if ( fDec == 0.0 || fVal == 0.0 ) PushInt( 0 ); else PushDouble(::rtl::math::approxCeil( fVal / fDec ) * fDec ); } /** tdf69552 ODFF1.2 function FLOOR and Excel function FLOOR.MATH In essence, the difference between the two is that ODFF-FLOOR needs to have arguments value and significance of the same sign and with FLOOR.MATH the sign of argument significance is irrevelevant. This is why ODFF-FLOOR is exported to Excel as FLOOR.MATH and FLOOR.MATH is imported in Calc as FLOOR.MATH */ void ScInterpreter::ScFloor( bool bODFF ) { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 3 ) ) return; bool bAbs = ( nParamCount == 3 && GetBool() ); double fDec, fVal; if ( nParamCount == 1 ) { fVal = GetDouble(); fDec = ( fVal < 0 ? -1 : 1 ); } else { bool bArgumentMissing = IsMissing(); fDec = GetDouble(); fVal = GetDouble(); if ( bArgumentMissing ) fDec = ( fVal < 0 ? -1 : 1 ); } if ( fDec == 0.0 || fVal == 0.0 ) PushInt( 0 ); else { if ( bODFF && ( fVal * fDec < 0.0 ) ) PushIllegalArgument(); else { if ( fVal * fDec < 0.0 ) fDec = -fDec; if ( !bAbs && fVal < 0.0 ) PushDouble(::rtl::math::approxCeil( fVal / fDec ) * fDec ); else PushDouble(::rtl::math::approxFloor( fVal / fDec ) * fDec ); } } } void ScInterpreter::ScFloor_MS() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 2 ) ) return; double fDec = GetDouble(); double fVal = GetDouble(); if ( fVal == 0 ) PushInt( 0 ); else if ( fVal * fDec > 0 ) PushDouble(::rtl::math::approxFloor( fVal / fDec ) * fDec ); else if ( fDec == 0 ) PushIllegalArgument(); else if ( fVal < 0.0 ) PushDouble(::rtl::math::approxCeil( fVal / -fDec ) * -fDec ); else PushIllegalArgument(); } void ScInterpreter::ScFloor_Precise() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; double fDec = nParamCount == 1 ? 1.0 : std::abs( GetDoubleWithDefault( 1.0 ) ); double fVal = GetDouble(); if ( fDec == 0.0 || fVal == 0.0 ) PushInt( 0 ); else PushDouble(::rtl::math::approxFloor( fVal / fDec ) * fDec ); } void ScInterpreter::ScEven() { double fVal = GetDouble(); if (fVal < 0.0) PushDouble(::rtl::math::approxFloor(fVal/2.0) * 2.0); else PushDouble(::rtl::math::approxCeil(fVal/2.0) * 2.0); } void ScInterpreter::ScOdd() { double fVal = GetDouble(); if (fVal >= 0.0) { fVal = ::rtl::math::approxCeil(fVal); if (fmod(fVal, 2.0) == 0.0) ++fVal; } else { fVal = ::rtl::math::approxFloor(fVal); if (fmod(fVal, 2.0) == 0.0) --fVal; } PushDouble(fVal); } void ScInterpreter::ScArcTan2() { if ( MustHaveParamCount( GetByte(), 2 ) ) { double fVal2 = GetDouble(); double fVal1 = GetDouble(); PushDouble(atan2(fVal2, fVal1)); } } void ScInterpreter::ScLog() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; double fBase = nParamCount == 2 ? GetDouble() : 10.0; double fVal = GetDouble(); if (fVal > 0.0 && fBase > 0.0 && fBase != 1.0) PushDouble(log(fVal) / log(fBase)); else PushIllegalArgument(); } void ScInterpreter::ScLn() { double fVal = GetDouble(); if (fVal > 0.0) PushDouble(log(fVal)); else PushIllegalArgument(); } void ScInterpreter::ScLog10() { double fVal = GetDouble(); if (fVal > 0.0) PushDouble(log10(fVal)); else PushIllegalArgument(); } void ScInterpreter::ScNPV() { nFuncFmtType = SvNumFormatType::CURRENCY; short nParamCount = GetByte(); if ( !MustHaveParamCountMin( nParamCount, 2) ) return; KahanSum fVal = 0.0; // We turn the stack upside down! ReverseStack( nParamCount); if (nGlobalError == FormulaError::NONE) { double fCount = 1.0; double fRate = GetDouble(); --nParamCount; size_t nRefInList = 0; ScRange aRange; while (nParamCount-- > 0) { switch (GetStackType()) { case svDouble : { fVal += GetDouble() / pow(1.0 + fRate, fCount); fCount++; } break; case svSingleRef : { ScAddress aAdr; PopSingleRef( aAdr ); ScRefCellValue aCell(mrDoc, aAdr); if (!aCell.hasEmptyValue() && aCell.hasNumeric()) { double fCellVal = GetCellValue(aAdr, aCell); fVal += fCellVal / pow(1.0 + fRate, fCount); fCount++; } } break; case svDoubleRef : case svRefList : { FormulaError nErr = FormulaError::NONE; double fCellVal; PopDoubleRef( aRange, nParamCount, nRefInList); ScHorizontalValueIterator aValIter( mrDoc, aRange ); while ((nErr == FormulaError::NONE) && aValIter.GetNext(fCellVal, nErr)) { fVal += fCellVal / pow(1.0 + fRate, fCount); fCount++; } if ( nErr != FormulaError::NONE ) SetError(nErr); } break; case svMatrix : case svExternalSingleRef: case svExternalDoubleRef: { ScMatrixRef pMat = GetMatrix(); if (pMat) { SCSIZE nC, nR; pMat->GetDimensions(nC, nR); if (nC == 0 || nR == 0) { PushIllegalArgument(); return; } else { double fx; for ( SCSIZE j = 0; j < nC; j++ ) { for (SCSIZE k = 0; k < nR; ++k) { if (!pMat->IsValue(j,k)) { PushIllegalArgument(); return; } fx = pMat->GetDouble(j,k); fVal += fx / pow(1.0 + fRate, fCount); fCount++; } } } } } break; default : SetError(FormulaError::IllegalParameter); break; } } } PushDouble(fVal.get()); } void ScInterpreter::ScIRR() { nFuncFmtType = SvNumFormatType::PERCENT; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; double fEstimated = nParamCount == 2 ? GetDouble() : 0.1; double fEps = 1.0; // If it's -1 the default result for division by zero else startvalue double x = fEstimated == -1.0 ? 0.1 : fEstimated; double fValue; ScRange aRange; ScMatrixRef pMat; SCSIZE nC = 0; SCSIZE nR = 0; bool bIsMatrix = false; switch (GetStackType()) { case svDoubleRef: PopDoubleRef(aRange); break; case svMatrix: case svExternalSingleRef: case svExternalDoubleRef: pMat = GetMatrix(); if (pMat) { pMat->GetDimensions(nC, nR); if (nC == 0 || nR == 0) { PushIllegalParameter(); return; } bIsMatrix = true; } else { PushIllegalParameter(); return; } break; default: { PushIllegalParameter(); return; } } const sal_uInt16 nIterationsMax = 20; sal_uInt16 nItCount = 0; FormulaError nIterError = FormulaError::NONE; while (fEps > SCdEpsilon && nItCount < nIterationsMax && nGlobalError == FormulaError::NONE) { // Newtons method: KahanSum fNom = 0.0; KahanSum fDenom = 0.0; double fCount = 0.0; if (bIsMatrix) { for (SCSIZE j = 0; j < nC && nGlobalError == FormulaError::NONE; j++) { for (SCSIZE k = 0; k < nR; k++) { if (!pMat->IsValue(j, k)) continue; fValue = pMat->GetDouble(j, k); if (nGlobalError != FormulaError::NONE) break; fNom += fValue / pow(1.0+x,fCount); fDenom += -fCount * fValue / pow(1.0+x,fCount+1.0); fCount++; } } } else { ScValueIterator aValIter(mrContext, aRange, mnSubTotalFlags); bool bLoop = aValIter.GetFirst(fValue, nIterError); while (bLoop && nIterError == FormulaError::NONE) { fNom += fValue / pow(1.0+x,fCount); fDenom += -fCount * fValue / pow(1.0+x,fCount+1.0); fCount++; bLoop = aValIter.GetNext(fValue, nIterError); } SetError(nIterError); } double xNew = x - fNom.get() / fDenom.get(); // x(i+1) = x(i)-f(x(i))/f'(x(i)) nItCount++; fEps = std::abs(xNew - x); x = xNew; } if (fEstimated == 0.0 && std::abs(x) < SCdEpsilon) x = 0.0; // adjust to zero if (fEps < SCdEpsilon) PushDouble(x); else PushError( FormulaError::NoConvergence); } void ScInterpreter::ScMIRR() { // range_of_values ; rate_invest ; rate_reinvest nFuncFmtType = SvNumFormatType::PERCENT; if ( !MustHaveParamCount( GetByte(), 3 ) ) return; double fRate1_reinvest = GetDouble() + 1; double fRate1_invest = GetDouble() + 1; ScRange aRange; ScMatrixRef pMat; SCSIZE nC = 0; SCSIZE nR = 0; bool bIsMatrix = false; switch ( GetStackType() ) { case svDoubleRef : PopDoubleRef( aRange ); break; case svMatrix : case svExternalSingleRef: case svExternalDoubleRef: { pMat = GetMatrix(); if ( pMat ) { pMat->GetDimensions( nC, nR ); if ( nC == 0 || nR == 0 ) SetError( FormulaError::IllegalArgument ); bIsMatrix = true; } else SetError( FormulaError::IllegalArgument ); } break; default : SetError( FormulaError::IllegalParameter ); break; } if ( nGlobalError != FormulaError::NONE ) PushError( nGlobalError ); else { KahanSum fNPV_reinvest = 0.0; double fPow_reinvest = 1.0; KahanSum fNPV_invest = 0.0; double fPow_invest = 1.0; sal_uLong nCount = 0; bool bHasPosValue = false; bool bHasNegValue = false; if ( bIsMatrix ) { double fX; for ( SCSIZE j = 0; j < nC; j++ ) { for ( SCSIZE k = 0; k < nR; ++k ) { if ( !pMat->IsValue( j, k ) ) continue; fX = pMat->GetDouble( j, k ); if ( nGlobalError != FormulaError::NONE ) break; if ( fX > 0.0 ) { // reinvestments bHasPosValue = true; fNPV_reinvest += fX * fPow_reinvest; } else if ( fX < 0.0 ) { // investments bHasNegValue = true; fNPV_invest += fX * fPow_invest; } fPow_reinvest /= fRate1_reinvest; fPow_invest /= fRate1_invest; nCount++; } } } else { ScValueIterator aValIter( mrContext, aRange, mnSubTotalFlags ); double fCellValue; FormulaError nIterError = FormulaError::NONE; bool bLoop = aValIter.GetFirst( fCellValue, nIterError ); while( bLoop ) { if( fCellValue > 0.0 ) // reinvestments { // reinvestments bHasPosValue = true; fNPV_reinvest += fCellValue * fPow_reinvest; } else if( fCellValue < 0.0 ) // investments { // investments bHasNegValue = true; fNPV_invest += fCellValue * fPow_invest; } fPow_reinvest /= fRate1_reinvest; fPow_invest /= fRate1_invest; nCount++; bLoop = aValIter.GetNext( fCellValue, nIterError ); } if ( nIterError != FormulaError::NONE ) SetError( nIterError ); } if ( !( bHasPosValue && bHasNegValue ) ) SetError( FormulaError::IllegalArgument ); if ( nGlobalError != FormulaError::NONE ) PushError( nGlobalError ); else { double fResult = -fNPV_reinvest.get() / fNPV_invest.get(); fResult *= pow( fRate1_reinvest, static_cast<double>( nCount - 1 ) ); fResult = pow( fResult, div( 1.0, (nCount - 1)) ); PushDouble( fResult - 1.0 ); } } } void ScInterpreter::ScISPMT() { // rate ; period ; total_periods ; invest if( MustHaveParamCount( GetByte(), 4 ) ) { double fInvest = GetDouble(); double fTotal = GetDouble(); double fPeriod = GetDouble(); double fRate = GetDouble(); if( nGlobalError != FormulaError::NONE ) PushError( nGlobalError); else PushDouble( fInvest * fRate * (fPeriod / fTotal - 1.0) ); } } // financial functions double ScInterpreter::ScGetPV(double fRate, double fNper, double fPmt, double fFv, bool bPayInAdvance) { double fPv; if (fRate == 0.0) fPv = fFv + fPmt * fNper; else { if (bPayInAdvance) fPv = (fFv * pow(1.0 + fRate, -fNper)) + (fPmt * (1.0 - pow(1.0 + fRate, -fNper + 1.0)) / fRate) + fPmt; else fPv = (fFv * pow(1.0 + fRate, -fNper)) + (fPmt * (1.0 - pow(1.0 + fRate, -fNper)) / fRate); } return -fPv; } void ScInterpreter::ScPV() { nFuncFmtType = SvNumFormatType::CURRENCY; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) return; bool bPayInAdvance = nParamCount == 5 && GetBool(); double fFv = nParamCount >= 4 ? GetDouble() : 0; double fPmt = GetDouble(); double fNper = GetDouble(); double fRate = GetDouble(); PushDouble(ScGetPV(fRate, fNper, fPmt, fFv, bPayInAdvance)); } void ScInterpreter::ScSYD() { nFuncFmtType = SvNumFormatType::CURRENCY; if ( MustHaveParamCount( GetByte(), 4 ) ) { double fPer = GetDouble(); double fLife = GetDouble(); double fSalvage = GetDouble(); double fCost = GetDouble(); double fSyd = ((fCost - fSalvage) * (fLife - fPer + 1.0)) / ((fLife * (fLife + 1.0)) / 2.0); PushDouble(fSyd); } } double ScInterpreter::ScGetDDB(double fCost, double fSalvage, double fLife, double fPeriod, double fFactor) { double fDdb, fRate, fOldValue, fNewValue; fRate = fFactor / fLife; if (fRate >= 1.0) { fRate = 1.0; fOldValue = fPeriod == 1.0 ? fCost : 0; } else fOldValue = fCost * pow(1.0 - fRate, fPeriod - 1.0); fNewValue = fCost * pow(1.0 - fRate, fPeriod); fDdb = fNewValue < fSalvage ? fOldValue - fSalvage : fOldValue - fNewValue; return fDdb < 0 ? 0 : fDdb; } void ScInterpreter::ScDDB() { nFuncFmtType = SvNumFormatType::CURRENCY; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 4, 5 ) ) return; double fFactor = nParamCount == 5 ? GetDouble() : 2.0; double fPeriod = GetDouble(); double fLife = GetDouble(); double fSalvage = GetDouble(); double fCost = GetDouble(); if (fCost < 0.0 || fSalvage < 0.0 || fFactor <= 0.0 || fSalvage > fCost || fPeriod < 1.0 || fPeriod > fLife) PushIllegalArgument(); else PushDouble(ScGetDDB(fCost, fSalvage, fLife, fPeriod, fFactor)); } void ScInterpreter::ScDB() { nFuncFmtType = SvNumFormatType::CURRENCY; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 4, 5 ) ) return ; double fMonths = nParamCount == 4 ? 12.0 : ::rtl::math::approxFloor(GetDouble()); double fPeriod = GetDouble(); double fLife = GetDouble(); double fSalvage = GetDouble(); double fCost = GetDouble(); if (fMonths < 1.0 || fMonths > 12.0 || fLife > 1200.0 || fSalvage < 0.0 || fPeriod > (fLife + 1.0) || fSalvage > fCost || fCost <= 0.0 || fLife <= 0 || fPeriod <= 0 ) { PushIllegalArgument(); return; } double fOffRate = 1.0 - pow(fSalvage / fCost, 1.0 / fLife); fOffRate = ::rtl::math::approxFloor((fOffRate * 1000.0) + 0.5) / 1000.0; double fFirstOffRate = fCost * fOffRate * fMonths / 12.0; double fDb = 0.0; if (::rtl::math::approxFloor(fPeriod) == 1) fDb = fFirstOffRate; else { KahanSum fSumOffRate = fFirstOffRate; double fMin = fLife; if (fMin > fPeriod) fMin = fPeriod; sal_uInt16 iMax = static_cast<sal_uInt16>(::rtl::math::approxFloor(fMin)); for (sal_uInt16 i = 2; i <= iMax; i++) { fDb = -(fSumOffRate - fCost).get() * fOffRate; fSumOffRate += fDb; } if (fPeriod > fLife) fDb = -(fSumOffRate - fCost).get() * fOffRate * (12.0 - fMonths) / 12.0; } PushDouble(fDb); } double ScInterpreter::ScInterVDB(double fCost, double fSalvage, double fLife, double fLife1, double fPeriod, double fFactor) { KahanSum fVdb = 0.0; double fIntEnd = ::rtl::math::approxCeil(fPeriod); sal_uLong nLoopEnd = static_cast<sal_uLong>(fIntEnd); double fTerm, fSln = 0; // SLN: Straight-Line Depreciation double fSalvageValue = fCost - fSalvage; bool bNowSln = false; double fDdb; sal_uLong i; for ( i = 1; i <= nLoopEnd; i++) { if(!bNowSln) { fDdb = ScGetDDB(fCost, fSalvage, fLife, static_cast<double>(i), fFactor); fSln = fSalvageValue/ (fLife1 - static_cast<double>(i-1)); if (fSln > fDdb) { fTerm = fSln; bNowSln = true; } else { fTerm = fDdb; fSalvageValue -= fDdb; } } else { fTerm = fSln; } if ( i == nLoopEnd) fTerm *= ( fPeriod + 1.0 - fIntEnd ); fVdb += fTerm; } return fVdb.get(); } void ScInterpreter::ScVDB() { nFuncFmtType = SvNumFormatType::CURRENCY; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 5, 7 ) ) return; KahanSum fVdb = 0.0; bool bNoSwitch = nParamCount == 7 && GetBool(); double fFactor = nParamCount >= 6 ? GetDouble() : 2.0; double fEnd = GetDouble(); double fStart = GetDouble(); double fLife = GetDouble(); double fSalvage = GetDouble(); double fCost = GetDouble(); if (fStart < 0.0 || fEnd < fStart || fEnd > fLife || fCost < 0.0 || fSalvage > fCost || fFactor <= 0.0) PushIllegalArgument(); else { double fIntStart = ::rtl::math::approxFloor(fStart); double fIntEnd = ::rtl::math::approxCeil(fEnd); sal_uLong nLoopStart = static_cast<sal_uLong>(fIntStart); sal_uLong nLoopEnd = static_cast<sal_uLong>(fIntEnd); if (bNoSwitch) { for (sal_uLong i = nLoopStart + 1; i <= nLoopEnd; i++) { double fTerm = ScGetDDB(fCost, fSalvage, fLife, static_cast<double>(i), fFactor); //respect partial period in the Beginning/ End: if ( i == nLoopStart+1 ) fTerm *= ( std::min( fEnd, fIntStart + 1.0 ) - fStart ); else if ( i == nLoopEnd ) fTerm *= ( fEnd + 1.0 - fIntEnd ); fVdb += fTerm; } } else { double fPart = 0.0; // respect partial period in the Beginning / End: if ( !::rtl::math::approxEqual( fStart, fIntStart ) || !::rtl::math::approxEqual( fEnd, fIntEnd ) ) { if ( !::rtl::math::approxEqual( fStart, fIntStart ) ) { // part to be subtracted at the beginning double fTempIntEnd = fIntStart + 1.0; double fTempValue = fCost - ScInterVDB( fCost, fSalvage, fLife, fLife, fIntStart, fFactor ); fPart += ( fStart - fIntStart ) * ScInterVDB( fTempValue, fSalvage, fLife, fLife - fIntStart, fTempIntEnd - fIntStart, fFactor); } if ( !::rtl::math::approxEqual( fEnd, fIntEnd ) ) { // part to be subtracted at the end double fTempIntStart = fIntEnd - 1.0; double fTempValue = fCost - ScInterVDB( fCost, fSalvage, fLife, fLife, fTempIntStart, fFactor ); fPart += ( fIntEnd - fEnd ) * ScInterVDB( fTempValue, fSalvage, fLife, fLife - fTempIntStart, fIntEnd - fTempIntStart, fFactor); } } // calculate depreciation for whole periods fCost -= ScInterVDB( fCost, fSalvage, fLife, fLife, fIntStart, fFactor ); fVdb = ScInterVDB( fCost, fSalvage, fLife, fLife - fIntStart, fIntEnd - fIntStart, fFactor); fVdb -= fPart; } } PushDouble(fVdb.get()); } void ScInterpreter::ScPDuration() { if ( MustHaveParamCount( GetByte(), 3 ) ) { double fFuture = GetDouble(); double fPresent = GetDouble(); double fRate = GetDouble(); if ( fFuture <= 0.0 || fPresent <= 0.0 || fRate <= 0.0 ) PushIllegalArgument(); else PushDouble( std::log( fFuture / fPresent ) / std::log1p( fRate ) ); } } void ScInterpreter::ScSLN() { nFuncFmtType = SvNumFormatType::CURRENCY; if ( MustHaveParamCount( GetByte(), 3 ) ) { double fLife = GetDouble(); double fSalvage = GetDouble(); double fCost = GetDouble(); PushDouble( div( fCost - fSalvage, fLife ) ); } } double ScInterpreter::ScGetPMT(double fRate, double fNper, double fPv, double fFv, bool bPayInAdvance) { double fPayment; if (fRate == 0.0) fPayment = (fPv + fFv) / fNper; else { if (bPayInAdvance) // payment in advance fPayment = (fFv + fPv * exp( fNper * ::std::log1p(fRate) ) ) * fRate / (std::expm1( (fNper + 1) * ::std::log1p(fRate) ) - fRate); else // payment in arrear fPayment = (fFv + fPv * exp(fNper * ::std::log1p(fRate) ) ) * fRate / std::expm1( fNper * ::std::log1p(fRate) ); } return -fPayment; } void ScInterpreter::ScPMT() { nFuncFmtType = SvNumFormatType::CURRENCY; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) return; bool bPayInAdvance = nParamCount == 5 && GetBool(); double fFv = nParamCount >= 4 ? GetDouble() : 0; double fPv = GetDouble(); double fNper = GetDouble(); double fRate = GetDouble(); PushDouble(ScGetPMT(fRate, fNper, fPv, fFv, bPayInAdvance)); } void ScInterpreter::ScRRI() { nFuncFmtType = SvNumFormatType::PERCENT; if ( MustHaveParamCount( GetByte(), 3 ) ) { double fFutureValue = GetDouble(); double fPresentValue = GetDouble(); double fNrOfPeriods = GetDouble(); if ( fNrOfPeriods <= 0.0 || fPresentValue == 0.0 ) PushIllegalArgument(); else PushDouble(pow(fFutureValue / fPresentValue, 1.0 / fNrOfPeriods) - 1.0); } } double ScInterpreter::ScGetFV(double fRate, double fNper, double fPmt, double fPv, bool bPayInAdvance) { double fFv; if (fRate == 0.0) fFv = fPv + fPmt * fNper; else { double fTerm = pow(1.0 + fRate, fNper); if (bPayInAdvance) fFv = fPv * fTerm + fPmt*(1.0 + fRate)*(fTerm - 1.0)/fRate; else fFv = fPv * fTerm + fPmt*(fTerm - 1.0)/fRate; } return -fFv; } void ScInterpreter::ScFV() { nFuncFmtType = SvNumFormatType::CURRENCY; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) return; bool bPayInAdvance = nParamCount == 5 && GetBool(); double fPv = nParamCount >= 4 ? GetDouble() : 0; double fPmt = GetDouble(); double fNper = GetDouble(); double fRate = GetDouble(); PushDouble(ScGetFV(fRate, fNper, fPmt, fPv, bPayInAdvance)); } void ScInterpreter::ScNper() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) return; bool bPayInAdvance = nParamCount == 5 && GetBool(); double fFV = nParamCount >= 4 ? GetDouble() : 0; double fPV = GetDouble(); // Present Value double fPmt = GetDouble(); // Payment double fRate = GetDouble(); // Note that due to the function specification in ODFF1.2 (and Excel) the // amount to be paid to get from fPV to fFV is fFV_+_fPV. if ( fPV + fFV == 0.0 ) PushDouble( 0.0 ); else if (fRate == 0.0) PushDouble(-(fPV + fFV)/fPmt); else if (bPayInAdvance) PushDouble(log(-(fRate*fFV-fPmt*(1.0+fRate))/(fRate*fPV+fPmt*(1.0+fRate))) / std::log1p(fRate)); else PushDouble(log(-(fRate*fFV-fPmt)/(fRate*fPV+fPmt)) / std::log1p(fRate)); } bool ScInterpreter::RateIteration( double fNper, double fPayment, double fPv, double fFv, bool bPayType, double & fGuess ) { // See also #i15090# // Newton-Raphson method: x(i+1) = x(i) - f(x(i)) / f'(x(i)) // This solution handles integer and non-integer values of Nper different. // If ODFF will constraint Nper to integer, the distinction of cases can be // removed; only the integer-part is needed then. bool bValid = true, bFound = false; double fX, fXnew, fTerm, fTermDerivation; double fGeoSeries, fGeoSeriesDerivation; const sal_uInt16 nIterationsMax = 150; sal_uInt16 nCount = 0; const double fEpsilonSmall = 1.0E-14; if ( bPayType ) { // payment at beginning of each period fFv = fFv - fPayment; fPv = fPv + fPayment; } if (fNper == ::rtl::math::round( fNper )) { // Nper is an integer value fX = fGuess; while (!bFound && nCount < nIterationsMax) { double fPowN, fPowNminus1; // for (1.0+fX)^Nper and (1.0+fX)^(Nper-1) fPowNminus1 = pow( 1.0+fX, fNper-1.0); fPowN = fPowNminus1 * (1.0+fX); if (fX == 0.0) { fGeoSeries = fNper; fGeoSeriesDerivation = fNper * (fNper-1.0)/2.0; } else { fGeoSeries = (fPowN-1.0)/fX; fGeoSeriesDerivation = fNper * fPowNminus1 / fX - fGeoSeries / fX; } fTerm = fFv + fPv *fPowN+ fPayment * fGeoSeries; fTermDerivation = fPv * fNper * fPowNminus1 + fPayment * fGeoSeriesDerivation; if (std::abs(fTerm) < fEpsilonSmall) bFound = true; // will catch root which is at an extreme else { if (fTermDerivation == 0.0) fXnew = fX + 1.1 * SCdEpsilon; // move away from zero slope else fXnew = fX - fTerm / fTermDerivation; nCount++; // more accuracy not possible in oscillating cases bFound = (std::abs(fXnew - fX) < SCdEpsilon); fX = fXnew; } } // Gnumeric returns roots < -1, Excel gives an error in that cases, // ODFF says nothing about it. Enable the statement, if you want Excel's // behavior. //bValid =(fX >=-1.0); // Update 2013-06-17: Gnumeric (v1.12.2) doesn't return roots <= -1 // anymore. bValid = (fX > -1.0); } else { // Nper is not an integer value. fX = (fGuess < -1.0) ? -1.0 : fGuess; // start with a valid fX while (bValid && !bFound && nCount < nIterationsMax) { if (fX == 0.0) { fGeoSeries = fNper; fGeoSeriesDerivation = fNper * (fNper-1.0)/2.0; } else { fGeoSeries = (pow( 1.0+fX, fNper) - 1.0) / fX; fGeoSeriesDerivation = fNper * pow( 1.0+fX, fNper-1.0) / fX - fGeoSeries / fX; } fTerm = fFv + fPv *pow(1.0 + fX,fNper)+ fPayment * fGeoSeries; fTermDerivation = fPv * fNper * pow( 1.0+fX, fNper-1.0) + fPayment * fGeoSeriesDerivation; if (std::abs(fTerm) < fEpsilonSmall) bFound = true; // will catch root which is at an extreme else { if (fTermDerivation == 0.0) fXnew = fX + 1.1 * SCdEpsilon; // move away from zero slope else fXnew = fX - fTerm / fTermDerivation; nCount++; // more accuracy not possible in oscillating cases bFound = (std::abs(fXnew - fX) < SCdEpsilon); fX = fXnew; bValid = (fX >= -1.0); // otherwise pow(1.0+fX,fNper) will fail } } } fGuess = fX; // return approximate root return bValid && bFound; } // In Calc UI it is the function RATE(Nper;Pmt;Pv;Fv;Type;Guess) void ScInterpreter::ScRate() { nFuncFmtType = SvNumFormatType::PERCENT; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 3, 6 ) ) return; // defaults for missing arguments, see ODFF spec double fGuess = nParamCount == 6 ? GetDouble() : 0.1; bool bDefaultGuess = nParamCount != 6; bool bPayType = nParamCount >= 5 && GetBool(); double fFv = nParamCount >= 4 ? GetDouble() : 0; double fPv = GetDouble(); double fPayment = GetDouble(); double fNper = GetDouble(); double fOrigGuess = fGuess; if (fNper <= 0.0) // constraint from ODFF spec { PushIllegalArgument(); return; } bool bValid = RateIteration(fNper, fPayment, fPv, fFv, bPayType, fGuess); if (!bValid) { /* TODO: try also for specified guess values, not only default? As is, * a specified 0.1 guess may be error result but a default 0.1 guess * may succeed. On the other hand, using a different guess value than * the specified one may not be desired, even if that didn't match. */ if (bDefaultGuess) { /* TODO: this is rather ugly, instead of looping over different * guess values and doing a Newton goal seek for each we could * first insert the values into the RATE equation to obtain a set * of y values and then do a bisecting goal seek, possibly using * different algorithms. */ double fX = fOrigGuess; for (int nStep = 2; nStep <= 10 && !bValid; ++nStep) { fGuess = fX * nStep; bValid = RateIteration( fNper, fPayment, fPv, fFv, bPayType, fGuess); if (!bValid) { fGuess = fX / nStep; bValid = RateIteration( fNper, fPayment, fPv, fFv, bPayType, fGuess); } } } if (!bValid) SetError(FormulaError::NoConvergence); } PushDouble(fGuess); } double ScInterpreter::ScGetIpmt(double fRate, double fPer, double fNper, double fPv, double fFv, bool bPayInAdvance, double& fPmt) { fPmt = ScGetPMT(fRate, fNper, fPv, fFv, bPayInAdvance); // for PPMT also if fPer == 1 double fIpmt; nFuncFmtType = SvNumFormatType::CURRENCY; if (fPer == 1.0) fIpmt = bPayInAdvance ? 0.0 : -fPv; else { if (bPayInAdvance) fIpmt = ScGetFV(fRate, fPer-2.0, fPmt, fPv, true) - fPmt; else fIpmt = ScGetFV(fRate, fPer-1.0, fPmt, fPv, false); } return fIpmt * fRate; } void ScInterpreter::ScIpmt() { nFuncFmtType = SvNumFormatType::CURRENCY; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 4, 6 ) ) return; bool bPayInAdvance = nParamCount == 6 && GetBool(); double fFv = nParamCount >= 5 ? GetDouble() : 0; double fPv = GetDouble(); double fNper = GetDouble(); double fPer = GetDouble(); double fRate = GetDouble(); if (fPer < 1.0 || fPer > fNper) PushIllegalArgument(); else { double fPmt; PushDouble(ScGetIpmt(fRate, fPer, fNper, fPv, fFv, bPayInAdvance, fPmt)); } } void ScInterpreter::ScPpmt() { nFuncFmtType = SvNumFormatType::CURRENCY; sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 4, 6 ) ) return; bool bPayInAdvance = nParamCount == 6 && GetBool(); double fFv = nParamCount >= 5 ? GetDouble() : 0; double fPv = GetDouble(); double fNper = GetDouble(); double fPer = GetDouble(); double fRate = GetDouble(); if (fPer < 1.0 || fPer > fNper) PushIllegalArgument(); else { double fPmt; double fInterestPer = ScGetIpmt(fRate, fPer, fNper, fPv, fFv, bPayInAdvance, fPmt); PushDouble(fPmt - fInterestPer); } } void ScInterpreter::ScCumIpmt() { nFuncFmtType = SvNumFormatType::CURRENCY; if ( !MustHaveParamCount( GetByte(), 6 ) ) return; double fFlag = GetDoubleWithDefault( -1.0 ); double fEnd = ::rtl::math::approxFloor(GetDouble()); double fStart = ::rtl::math::approxFloor(GetDouble()); double fPv = GetDouble(); double fNper = GetDouble(); double fRate = GetDouble(); if (fStart < 1.0 || fEnd < fStart || fRate <= 0.0 || fEnd > fNper || fNper <= 0.0 || fPv <= 0.0 || ( fFlag != 0.0 && fFlag != 1.0 )) PushIllegalArgument(); else { bool bPayInAdvance = static_cast<bool>(fFlag); sal_uLong nStart = static_cast<sal_uLong>(fStart); sal_uLong nEnd = static_cast<sal_uLong>(fEnd) ; double fPmt = ScGetPMT(fRate, fNper, fPv, 0.0, bPayInAdvance); KahanSum fIpmt = 0.0; if (nStart == 1) { if (!bPayInAdvance) fIpmt = -fPv; nStart++; } for (sal_uLong i = nStart; i <= nEnd; i++) { if (bPayInAdvance) fIpmt += ScGetFV(fRate, static_cast<double>(i-2), fPmt, fPv, true) - fPmt; else fIpmt += ScGetFV(fRate, static_cast<double>(i-1), fPmt, fPv, false); } fIpmt *= fRate; PushDouble(fIpmt.get()); } } void ScInterpreter::ScCumPrinc() { nFuncFmtType = SvNumFormatType::CURRENCY; if ( !MustHaveParamCount( GetByte(), 6 ) ) return; double fFlag = GetDoubleWithDefault( -1.0 ); double fEnd = ::rtl::math::approxFloor(GetDouble()); double fStart = ::rtl::math::approxFloor(GetDouble()); double fPv = GetDouble(); double fNper = GetDouble(); double fRate = GetDouble(); if (fStart < 1.0 || fEnd < fStart || fRate <= 0.0 || fEnd > fNper || fNper <= 0.0 || fPv <= 0.0 || ( fFlag != 0.0 && fFlag != 1.0 )) PushIllegalArgument(); else { bool bPayInAdvance = static_cast<bool>(fFlag); double fPmt = ScGetPMT(fRate, fNper, fPv, 0.0, bPayInAdvance); KahanSum fPpmt = 0.0; sal_uLong nStart = static_cast<sal_uLong>(fStart); sal_uLong nEnd = static_cast<sal_uLong>(fEnd); if (nStart == 1) { fPpmt = bPayInAdvance ? fPmt : fPmt + fPv * fRate; nStart++; } for (sal_uLong i = nStart; i <= nEnd; i++) { if (bPayInAdvance) fPpmt += fPmt - (ScGetFV(fRate, static_cast<double>(i-2), fPmt, fPv, true) - fPmt) * fRate; else fPpmt += fPmt - ScGetFV(fRate, static_cast<double>(i-1), fPmt, fPv, false) * fRate; } PushDouble(fPpmt.get()); } } void ScInterpreter::ScEffect() { nFuncFmtType = SvNumFormatType::PERCENT; if ( !MustHaveParamCount( GetByte(), 2 ) ) return; double fPeriods = GetDouble(); double fNominal = GetDouble(); if (fPeriods < 1.0 || fNominal < 0.0) PushIllegalArgument(); else if ( fNominal == 0.0 ) PushDouble( 0.0 ); else { fPeriods = ::rtl::math::approxFloor(fPeriods); PushDouble(pow(1.0 + fNominal/fPeriods, fPeriods) - 1.0); } } void ScInterpreter::ScNominal() { nFuncFmtType = SvNumFormatType::PERCENT; if ( MustHaveParamCount( GetByte(), 2 ) ) { double fPeriods = GetDouble(); double fEffective = GetDouble(); if (fPeriods < 1.0 || fEffective <= 0.0) PushIllegalArgument(); else { fPeriods = ::rtl::math::approxFloor(fPeriods); PushDouble( (pow(fEffective + 1.0, 1.0 / fPeriods) - 1.0) * fPeriods ); } } } void ScInterpreter::ScMod() { if ( !MustHaveParamCount( GetByte(), 2 ) ) return; double fDenom = GetDouble(); // Denominator if ( fDenom == 0.0 ) { PushError(FormulaError::DivisionByZero); return; } double fNum = GetDouble(); // Numerator double fRes = ::rtl::math::approxSub( fNum, ::rtl::math::approxFloor( fNum / fDenom ) * fDenom ); if ( ( fDenom > 0 && fRes >= 0 && fRes < fDenom ) || ( fDenom < 0 && fRes <= 0 && fRes > fDenom ) ) PushDouble( fRes ); else PushError( FormulaError::NoValue ); } void ScInterpreter::ScIntersect() { formula::FormulaConstTokenRef p2nd = PopToken(); formula::FormulaConstTokenRef p1st = PopToken(); if (nGlobalError != FormulaError::NONE || !p2nd || !p1st) { PushIllegalArgument(); return; } StackVar sv1 = p1st->GetType(); StackVar sv2 = p2nd->GetType(); if ((sv1 != svSingleRef && sv1 != svDoubleRef && sv1 != svRefList) || (sv2 != svSingleRef && sv2 != svDoubleRef && sv2 != svRefList)) { PushIllegalArgument(); return; } const formula::FormulaToken* x1 = p1st.get(); const formula::FormulaToken* x2 = p2nd.get(); if (sv1 == svRefList || sv2 == svRefList) { // Now this is a bit nasty but it simplifies things, and having // intersections with lists isn't too common, if at all... // Convert a reference to list. const formula::FormulaToken* xt[2] = { x1, x2 }; StackVar sv[2] = { sv1, sv2 }; // There may only be one reference; the other is necessarily a list // Ensure converted list proper destruction std::unique_ptr<formula::FormulaToken> p; for (size_t i=0; i<2; ++i) { if (sv[i] == svSingleRef) { ScComplexRefData aRef; aRef.Ref1 = aRef.Ref2 = *xt[i]->GetSingleRef(); p.reset(new ScRefListToken); p->GetRefList()->push_back( aRef); xt[i] = p.get(); } else if (sv[i] == svDoubleRef) { ScComplexRefData aRef = *xt[i]->GetDoubleRef(); p.reset(new ScRefListToken); p->GetRefList()->push_back( aRef); xt[i] = p.get(); } } x1 = xt[0]; x2 = xt[1]; ScTokenRef xRes = new ScRefListToken; ScRefList* pRefList = xRes->GetRefList(); for (const auto& rRef1 : *x1->GetRefList()) { const ScAddress r11 = rRef1.Ref1.toAbs(mrDoc, aPos); const ScAddress r12 = rRef1.Ref2.toAbs(mrDoc, aPos); for (const auto& rRef2 : *x2->GetRefList()) { const ScAddress r21 = rRef2.Ref1.toAbs(mrDoc, aPos); const ScAddress r22 = rRef2.Ref2.toAbs(mrDoc, aPos); SCCOL nCol1 = ::std::max( r11.Col(), r21.Col()); SCROW nRow1 = ::std::max( r11.Row(), r21.Row()); SCTAB nTab1 = ::std::max( r11.Tab(), r21.Tab()); SCCOL nCol2 = ::std::min( r12.Col(), r22.Col()); SCROW nRow2 = ::std::min( r12.Row(), r22.Row()); SCTAB nTab2 = ::std::min( r12.Tab(), r22.Tab()); if (nCol2 < nCol1 || nRow2 < nRow1 || nTab2 < nTab1) ; // nothing else { ScComplexRefData aRef; aRef.InitRange( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2); pRefList->push_back( aRef); } } } size_t n = pRefList->size(); if (!n) PushError( FormulaError::NoCode); else if (n == 1) { const ScComplexRefData& rRef = (*pRefList)[0]; if (rRef.Ref1 == rRef.Ref2) PushTempToken( new ScSingleRefToken(mrDoc.GetSheetLimits(), rRef.Ref1)); else PushTempToken( new ScDoubleRefToken(mrDoc.GetSheetLimits(), rRef)); } else PushTokenRef( xRes); } else { const formula::FormulaToken* pt[2] = { x1, x2 }; StackVar sv[2] = { sv1, sv2 }; SCCOL nC1[2], nC2[2]; SCROW nR1[2], nR2[2]; SCTAB nT1[2], nT2[2]; for (size_t i=0; i<2; ++i) { switch (sv[i]) { case svSingleRef: case svDoubleRef: { { const ScAddress r = pt[i]->GetSingleRef()->toAbs(mrDoc, aPos); nC1[i] = r.Col(); nR1[i] = r.Row(); nT1[i] = r.Tab(); } if (sv[i] == svDoubleRef) { const ScAddress r = pt[i]->GetSingleRef2()->toAbs(mrDoc, aPos); nC2[i] = r.Col(); nR2[i] = r.Row(); nT2[i] = r.Tab(); } else { nC2[i] = nC1[i]; nR2[i] = nR1[i]; nT2[i] = nT1[i]; } } break; default: ; // nothing, prevent compiler warning } } SCCOL nCol1 = ::std::max( nC1[0], nC1[1]); SCROW nRow1 = ::std::max( nR1[0], nR1[1]); SCTAB nTab1 = ::std::max( nT1[0], nT1[1]); SCCOL nCol2 = ::std::min( nC2[0], nC2[1]); SCROW nRow2 = ::std::min( nR2[0], nR2[1]); SCTAB nTab2 = ::std::min( nT2[0], nT2[1]); if (nCol2 < nCol1 || nRow2 < nRow1 || nTab2 < nTab1) PushError( FormulaError::NoCode); else if (nCol2 == nCol1 && nRow2 == nRow1 && nTab2 == nTab1) PushSingleRef( nCol1, nRow1, nTab1); else PushDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2); } } void ScInterpreter::ScRangeFunc() { formula::FormulaConstTokenRef x2 = PopToken(); formula::FormulaConstTokenRef x1 = PopToken(); if (nGlobalError != FormulaError::NONE || !x2 || !x1) { PushIllegalArgument(); return; } // We explicitly tell extendRangeReference() to not reuse the token, // casting const away spares two clones. FormulaTokenRef xRes = extendRangeReference( mrDoc.GetSheetLimits(), const_cast<FormulaToken&>(*x1), const_cast<FormulaToken&>( if (!xRes) PushIllegalArgument(); else PushTokenRef( xRes); } void ScInterpreter::ScUnionFunc() { formula::FormulaConstTokenRef p2nd = PopToken(); formula::FormulaConstTokenRef p1st = PopToken(); if (nGlobalError != FormulaError::NONE || !p2nd || !p1st) { PushIllegalArgument(); return; } StackVar sv1 = p1st->GetType(); StackVar sv2 = p2nd->GetType(); if ((sv1 != svSingleRef && sv1 != svDoubleRef && sv1 != svRefList) || (sv2 != svSingleRef && sv2 != svDoubleRef && sv2 != svRefList)) { PushIllegalArgument(); return; } const formula::FormulaToken* x1 = p1st.get(); const formula::FormulaToken* x2 = p2nd.get(); ScTokenRef xRes; // Append to an existing RefList if there is one. if (sv1 == svRefList) { xRes = x1->Clone(); sv1 = svUnknown; // mark as handled } else if (sv2 == svRefList) { xRes = x2->Clone(); sv2 = svUnknown; // mark as handled } else xRes = new ScRefListToken; ScRefList* pRes = xRes->GetRefList(); const formula::FormulaToken* pt[2] = { x1, x2 }; StackVar sv[2] = { sv1, sv2 }; for (size_t i=0; i<2; ++i) { if (pt[i] == xRes) continue; switch (sv[i]) { case svSingleRef: { ScComplexRefData aRef; aRef.Ref1 = aRef.Ref2 = *pt[i]->GetSingleRef(); pRes->push_back( aRef); } break; case svDoubleRef: pRes->push_back( *pt[i]->GetDoubleRef()); break; case svRefList: { const ScRefList* p = pt[i]->GetRefList(); for (const auto& rRef : *p) { pRes->push_back( rRef); } } break; default: ; // nothing, prevent compiler warning } } ValidateRef( *pRes); // set #REF! if needed PushTokenRef( xRes); } void ScInterpreter::ScCurrent() { FormulaConstTokenRef xTok( PopToken()); if (xTok) { PushTokenRef( xTok); PushTokenRef( xTok); } else PushError( FormulaError::UnknownStackVariable); } void ScInterpreter::ScStyle() { sal_uInt8 nParamCount = GetByte(); if (!MustHaveParamCount(nParamCount, 1, 3)) return; OUString aStyle2; // Style after timer if (nParamCount >= 3) aStyle2 = GetString().getString(); tools::Long nTimeOut = 0; // timeout if (nParamCount >= 2) nTimeOut = static_cast<tools::Long>(GetDouble()*1000.0); OUString aStyle1 = GetString().getString(); // Style for immediate if (nTimeOut < 0) nTimeOut = 0; // Execute request to apply style if ( !mrDoc.IsClipOrUndo() ) { ScDocShell* pShell = mrDoc.GetDocumentShell(); if (pShell) { // Normalize style names right here, making sure that character case is correct, // and that we only apply anything when there's something to apply auto pPool = mrDoc.GetStyleSheetPool(); if (!aStyle1.isEmpty()) { if (auto pNewStyle = pPool->FindAutoStyle(aStyle1)) aStyle1 = pNewStyle->GetName(); else aStyle1.clear(); } if (!aStyle2.isEmpty()) { if (auto pNewStyle = pPool->FindAutoStyle(aStyle2)) aStyle2 = pNewStyle->GetName(); else aStyle2.clear(); } // notify object shell directly! if (!aStyle1.isEmpty() || !aStyle2.isEmpty()) { const ScStyleSheet* pStyle = mrDoc.GetStyle(aPos.Col(), aPos.Row(), aPos.Tab()); const bool bNotify = !pStyle || (!aStyle1.isEmpty() && pStyle->GetName() != aStyle1) || (!aStyle2.isEmpty() && pStyle->GetName() != aStyle2); if (bNotify) { ScRange aRange(aPos); ScAutoStyleHint aHint(aRange, aStyle1, nTimeOut, aStyle2); pShell->Broadcast(aHint); } } } } PushDouble(0.0); } static ScDdeLink* lcl_GetDdeLink( const sfx2::LinkManager* pLinkMgr, std::u16string_view rA, std::u16string_view rT, std::u16string_view rI, sal_uInt8 nM ) { size_t nCount = pLinkMgr->GetLinks().size(); for (size_t i=0; i<nCount; i++ ) { ::sfx2::SvBaseLink* pBase = pLinkMgr->GetLinks()[i].get(); if (ScDdeLink* pLink = dynamic_cast<ScDdeLink*>(pBase)) { if ( pLink->GetAppl() == rA && pLink->GetTopic() == rT && pLink->GetItem() == rI && pLink->GetMode() == nM ) return pLink; } } return nullptr; } void ScInterpreter::ScDde() { // application, file, scope // application, Topic, Item sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 3, 4 ) ) return; sal_uInt8 nMode = SC_DDE_DEFAULT; if (nParamCount == 4) { sal_Int32 nTmp = GetInt32(); if (nGlobalError != FormulaError::NONE || nTmp < 0 || nTmp > SAL_MAX_UINT8) { PushIllegalArgument(); return; } nMode = static_cast<sal_uInt8>(nTmp); } OUString aItem = GetString().getString(); OUString aTopic = GetString().getString(); OUString aAppl = GetString().getString(); if (nMode > SC_DDE_TEXT) nMode = SC_DDE_DEFAULT; // temporary documents (ScFunctionAccess) have no DocShell // and no LinkManager -> abort //sfx2::LinkManager* pLinkMgr = mrDoc.GetLinkManager(); if (!mpLinkManager) { PushNoValue(); return; } // Need to reinterpret after loading (build links) pArr->AddRecalcMode( ScRecalcMode::ONLOAD_LENIENT ); // while the link is not evaluated, idle must be disabled (to avoid circular references) bool bOldEnabled = mrDoc.IsIdleEnabled(); mrDoc.EnableIdle(false); // Get/ Create link object ScDdeLink* pLink = lcl_GetDdeLink( mpLinkManager, aAppl, aTopic, aItem, nMode ); //TODO: Save Dde-links (in addition) more efficient at document !!!!! // ScDdeLink* pLink = mrDoc.GetDdeLink( aAppl, aTopic, aItem ); bool bWasError = ( pMyFormulaCell && pMyFormulaCell->GetRawError() != FormulaError::NONE ); if (!pLink) { pLink = new ScDdeLink( mrDoc, aAppl, aTopic, aItem, nMode ); mpLinkManager->InsertDDELink( pLink, aAppl, aTopic, aItem ); if ( mpLinkManager->GetLinks().size() == 1 ) // the first one? { SfxBindings* pBindings = mrDoc.GetViewBindings(); if (pBindings) pBindings->Invalidate( SID_LINKS ); // Link-Manager enabled } //if the document was just loaded, but the ScDdeLink entry was missing, then //don't update this link until the links are updated in response to the users //decision if (!mrDoc.HasLinkFormulaNeedingCheck()) { //TODO: evaluate asynchron ??? pLink->TryUpdate(); // TryUpdate doesn't call Update multiple times } if (pMyFormulaCell) { // StartListening after the Update to avoid circular references pMyFormulaCell->StartListening( *pLink ); } } else { if (pMyFormulaCell) pMyFormulaCell->StartListening( *pLink ); } // If a new Error from Reschedule appears when the link is executed then reset the errorflag if ( pMyFormulaCell && pMyFormulaCell->GetRawError() != FormulaError::NONE && !bWasError ) pMyFormulaCell->SetErrCode(FormulaError::NONE); // check the value const ScMatrix* pLinkMat = pLink->GetResult(); if (pLinkMat) { SCSIZE nC, nR; pLinkMat->GetDimensions(nC, nR); ScMatrixRef pNewMat = GetNewMat( nC, nR, /*bEmpty*/true); if (pNewMat) { pLinkMat->MatCopy(*pNewMat); // copy PushMatrix( pNewMat ); } else PushIllegalArgument(); } else PushNA(); mrDoc.EnableIdle(bOldEnabled); mpLinkManager->CloseCachedComps(); } void ScInterpreter::ScBase() { // Value, Base [, MinLen] sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 2, 3 ) ) return; static const sal_Unicode pDigits[] = { '0','1','2','3','4','5','6','7','8','9', 'A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z', 0 }; static const int nDigits = SAL_N_ELEMENTS(pDigits) - 1; sal_Int32 nMinLen; if ( nParamCount == 3 ) { double fLen = ::rtl::math::approxFloor( GetDouble() ); if ( 1.0 <= fLen && fLen < SAL_MAX_UINT16 ) nMinLen = static_cast<sal_Int32>(fLen); else nMinLen = fLen == 0.0 ? 1 : 0; // 0 means error } else nMinLen = 1; double fBase = ::rtl::math::approxFloor( GetDouble() ); double fVal = ::rtl::math::approxFloor( GetDouble() ); double fChars = ((fVal > 0.0 && fBase > 0.0) ? (ceil( log( fVal ) / log( fBase ) ) + 2.0) : 2.0); if ( fChars >= SAL_MAX_UINT16 ) nMinLen = 0; // Error if ( nGlobalError == FormulaError::NONE && nMinLen && 2 <= fBase && fBase <= nDigits && 0 <= fVal ) { const sal_Int32 nConstBuf = 128; sal_Unicode aBuf[nConstBuf]; sal_Int32 nBuf = std::max<sal_Int32>( fChars, nMinLen + 1 ); sal_Unicode* pBuf = (nBuf <= nConstBuf ? aBuf : new sal_Unicode[nBuf]); for ( sal_Int32 j = 0; j < nBuf; ++j ) { pBuf[j] = '0'; } sal_Unicode* p = pBuf + nBuf - 1; *p = 0; if ( o3tl::convertsToAtMost(fVal, sal_uLong(~0)) ) { sal_uLong nVal = static_cast<sal_uLong>(fVal); sal_uLong nBase = static_cast<sal_uLong>(fBase); while ( nVal && p > pBuf ) { *--p = pDigits[ nVal % nBase ]; nVal /= nBase; } fVal = static_cast<double>(nVal); } else { bool bDirt = false; while ( fVal && p > pBuf ) { //TODO: roundoff error starting with numbers greater than 2**48 // double fDig = ::rtl::math::approxFloor( fmod( fVal, fBase ) ); // a little bit better: double fInt = ::rtl::math::approxFloor( fVal / fBase ); double fMult = fInt * fBase; #if 0 // =BASIS(1e308;36) => GPF with // nDig = (size_t) ::rtl::math::approxFloor( fVal - fMult ); // in spite off previous test if fVal >= fMult double fDebug1 = fVal - fMult; // fVal := 7,5975311883090e+290 // fMult := 7,5975311883090e+290 // fDebug1 := 1,3848924157003e+275 <- RoundOff-Error // fVal != fMult, aber: ::rtl::math::approxEqual( fVal, fMult ) == TRUE double fDebug2 = ::rtl::math::approxSub( fVal, fMult ); // and ::rtl::math::approxSub( fVal, fMult ) == 0 double fDebug3 = ( fInt ? fVal / fInt : 0.0 ); // Actual after strange fDebug1 and fVal < fMult is fDebug2 == fBase, but // anyway it can't be compared, then bDirt is executed an everything is good... // prevent compiler warnings (void)fDebug1; (void)fDebug2; (void)fDebug3; #endif size_t nDig; if ( fVal < fMult ) { // something is wrong there bDirt = true; nDig = 0; } else { double fDig = ::rtl::math::approxFloor( ::rtl::math::approxSub( fVal, fMult ) ); if ( bDirt ) { bDirt = false; --fDig; } if ( fDig <= 0.0 ) nDig = 0; else if ( fDig >= fBase ) nDig = static_cast<size_t>(fBase) - 1; else nDig = static_cast<size_t>(fDig); } *--p = pDigits[ nDig ]; fVal = fInt; } } if ( fVal ) PushError( FormulaError::StringOverflow ); else { if ( nBuf - (p - pBuf) <= nMinLen ) p = pBuf + nBuf - 1 - nMinLen; PushStringBuffer( p ); } if ( pBuf != aBuf ) delete [] pBuf; } else PushIllegalArgument(); } void ScInterpreter::ScDecimal() { // Text, Base if ( !MustHaveParamCount( GetByte(), 2 ) ) return; double fBase = ::rtl::math::approxFloor( GetDouble() ); OUString aStr = GetString().getString(); if ( nGlobalError == FormulaError::NONE && 2 <= fBase && fBase <= 36 ) { double fVal = 0.0; int nBase = static_cast<int>(fBase); const sal_Unicode* p = aStr.getStr(); while ( *p == ' ' || *p == '\t' ) p++; // strip leading white space if ( nBase == 16 ) { // evtl. hex-prefix stripped if ( *p == 'x' || *p == 'X' ) p++; else if ( *p == '0' && (*(p+1) == 'x' || *(p+1) == 'X') ) p += 2; } while ( *p ) { int n; if ( '0' <= *p && *p <= '9' ) n = *p - '0'; else if ( 'A' <= *p && *p <= 'Z' ) n = 10 + (*p - 'A'); else if ( 'a' <= *p && *p <= 'z' ) n = 10 + (*p - 'a'); else n = nBase; if ( nBase <= n ) { if ( *(p+1) == 0 && ( (nBase == 2 && (*p == 'b' || *p == 'B')) ||(nBase == 16 && (*p == 'h' || *p == 'H')) ) ) ; // 101b and F00Dh are ok else { PushIllegalArgument(); return ; } } else fVal = fVal * fBase + n; p++; } PushDouble( fVal ); } else PushIllegalArgument(); } void ScInterpreter::ScConvertOOo() { // Value, FromUnit, ToUnit if ( !MustHaveParamCount( GetByte(), 3 ) ) return; OUString aToUnit = GetString().getString(); OUString aFromUnit = GetString().getString(); double fVal = GetDouble(); if ( nGlobalError != FormulaError::NONE ) PushError( nGlobalError); else { // first of all search for the given order; if it can't be found then search for the inverse double fConv; if ( ScGlobal::GetUnitConverter()->GetValue( fConv, aFromUnit, aToUnit ) ) PushDouble( fVal * fConv ); else if ( ScGlobal::GetUnitConverter()->GetValue( fConv, aToUnit, aFromUnit ) ) PushDouble( fVal / fConv ); else PushNA(); } } void ScInterpreter::ScRoman() { // Value [Mode] sal_uInt8 nParamCount = GetByte(); if( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; double fMode = (nParamCount == 2) ? ::rtl::math::approxFloor( GetDouble() ) : 0.0; double fVal = ::rtl::math::approxFloor( GetDouble() ); if( nGlobalError != FormulaError::NONE ) PushError( nGlobalError); else if( (fMode >= 0.0) && (fMode < 5.0) && (fVal >= 0.0) && (fVal < 4000.0) ) { static const sal_Unicode pChars[] = { 'M', 'D', 'C', 'L', 'X', 'V', 'I' }; static const sal_uInt16 pValues[] = { 1000, 500, 100, 50, 10, 5, 1 }; static const sal_uInt16 nMaxIndex = sal_uInt16(SAL_N_ELEMENTS(pValues) - 1); OUStringBuffer aRoman; sal_uInt16 nVal = static_cast<sal_uInt16>(fVal); sal_uInt16 nMode = static_cast<sal_uInt16>(fMode); for( sal_uInt16 i = 0; i <= nMaxIndex / 2; i++ ) { sal_uInt16 nIndex = 2 * i; sal_uInt16 nDigit = nVal / pValues[ nIndex ]; if( (nDigit % 5) == 4 ) { // assert can't happen with nVal<4000 precondition assert( ((nDigit == 4) ? (nIndex >= 1) : (nIndex >= 2))); sal_uInt16 nIndex2 = (nDigit == 4) ? nIndex - 1 : nIndex - 2; sal_uInt16 nSteps = 0; while( (nSteps < nMode) && (nIndex < nMaxIndex) ) { nSteps++; if( pValues[ nIndex2 ] - pValues[ nIndex + 1 ] <= nVal ) nIndex++; else nSteps = nMode; } aRoman.append( OUStringChar(pChars[ nIndex ]) + OUStringChar(pChars[ nIndex2 ]) ); nVal = sal::static_int_cast<sal_uInt16>( nVal + pValues[ nIndex ] ); nVal = sal::static_int_cast<sal_uInt16>( nVal - pValues[ nIndex2 ] ); } else { if( nDigit > 4 ) { // assert can't happen with nVal<4000 precondition assert( nIndex >= 1 ); aRoman.append( pChars[ nIndex - 1 ] ); } sal_Int32 nPad = nDigit % 5; if (nPad) { comphelper::string::padToLength(aRoman, aRoman.getLength() + nPad, pChars[nIndex]); } nVal %= pValues[ nIndex ]; } } PushString( aRoman.makeStringAndClear() ); } else PushIllegalArgument(); } static bool lcl_GetArabicValue( sal_Unicode cChar, sal_uInt16& rnValue, bool& rb { switch( cChar ) { case 'M': rnValue = 1000; rbIsDec = true; break; case 'D': rnValue = 500; rbIsDec = false; break; case 'C': rnValue = 100; rbIsDec = true; break; case 'L': rnValue = 50; rbIsDec = false; break; case 'X': rnValue = 10; rbIsDec = true; break; case 'V': rnValue = 5; rbIsDec = false; break; case 'I': rnValue = 1; rbIsDec = true; break; default: return false; } return true; } void ScInterpreter::ScArabic() { OUString aRoman = GetString().getString(); if( nGlobalError != FormulaError::NONE ) PushError( nGlobalError); else { aRoman = aRoman.toAsciiUpperCase(); sal_uInt16 nValue = 0; sal_uInt16 nValidRest = 3999; sal_Int32 nCharIndex = 0; sal_Int32 nCharCount = aRoman.getLength(); bool bValid = true; while( bValid && (nCharIndex < nCharCount) ) { sal_uInt16 nDigit1 = 0; sal_uInt16 nDigit2 = 0; bool bIsDec1 = false; bValid = lcl_GetArabicValue( aRoman[nCharIndex], nDigit1, bIsDec1 ); if( bValid && (nCharIndex + 1 < nCharCount) ) { bool bIsDec2 = false; bValid = lcl_GetArabicValue( aRoman[nCharIndex + 1], nDigit2, bIsDec2 ); } if( bValid ) { if( nDigit1 >= nDigit2 ) { nValue = sal::static_int_cast<sal_uInt16>( nValue + nDigit1 ); nValidRest %= (nDigit1 * (bIsDec1 ? 5 : 2)); bValid = (nValidRest >= nDigit1); if( bValid ) nValidRest = sal::static_int_cast<sal_uInt16>( nValidRest - nDigit1 ); nCharIndex++; } else if( nDigit1 * 2 != nDigit2 ) { sal_uInt16 nDiff = nDigit2 - nDigit1; nValue = sal::static_int_cast<sal_uInt16>( nValue + nDiff ); bValid = (nValidRest >= nDiff); if( bValid ) nValidRest = nDigit1 - 1; nCharIndex += 2; } else bValid = false; } } if( bValid ) PushInt( nValue ); else PushIllegalArgument(); } } void ScInterpreter::ScHyperLink() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1, 2 ) ) return; double fVal = 0.0; svl::SharedString aStr; ScMatValType nResultType = ScMatValType::String; if ( nParamCount == 2 ) { switch ( GetStackType() ) { case svDouble: fVal = GetDouble(); nResultType = ScMatValType::Value; break; case svString: aStr = GetString(); break; case svSingleRef: case svDoubleRef: { ScAddress aAdr; if ( !PopDoubleRefOrSingleRef( aAdr ) ) break; ScRefCellValue aCell(mrDoc, aAdr); if (aCell.hasEmptyValue()) nResultType = ScMatValType::Empty; else { FormulaError nErr = GetCellErrCode(aCell); if (nErr != FormulaError::NONE) SetError( nErr); else if (aCell.hasNumeric()) { fVal = GetCellValue(aAdr, aCell); nResultType = ScMatValType::Value; } else GetCellString(aStr, aCell); } } break; case svMatrix: nResultType = GetDoubleOrStringFromMatrix( fVal, aStr); break; case svMissing: case svEmptyCell: Pop(); // mimic xcl fVal = 0.0; nResultType = ScMatValType::Value; break; default: PopError(); SetError( FormulaError::IllegalArgument); } } svl::SharedString aUrl = GetString(); ScMatrixRef pResMat = GetNewMat( 1, 2); if (nGlobalError != FormulaError::NONE) { fVal = CreateDoubleError( nGlobalError); nResultType = ScMatValType::Value; } if (nParamCount == 2 || nGlobalError != FormulaError::NONE) { if (ScMatrix::IsValueType( nResultType)) pResMat->PutDouble( fVal, 0); else if (ScMatrix::IsRealStringType( nResultType)) pResMat->PutString(aStr, 0); else // EmptyType, EmptyPathType, mimic xcl pResMat->PutDouble( 0.0, 0 ); } else pResMat->PutString(aUrl, 0); pResMat->PutString(aUrl, 1); bMatrixFormula = true; PushMatrix(pResMat); } /** Resources at the website of the European Commission: http://ec.europa.eu/economy_finance/euro/adoption/conversion/ http://ec.europa.eu/economy_finance/euro/countries/ */ static bool lclConvertMoney( std::u16string_view aSearchUnit, double& rfRate, int&&nb { struct ConvertInfo { const char* pCurrText; double fRate; int nDec; }; static const ConvertInfo aConvertTable[] = { { "EUR", 1.0, 2 }, { "ATS", 13.7603, 2 }, { "BEF", 40.3399, 0 }, { "DEM", 1.95583, 2 }, { "ESP", 166.386, 0 }, { "FIM", 5.94573, 2 }, { "FRF", 6.55957, 2 }, { "IEP", 0.787564, 2 }, { "ITL", 1936.27, 0 }, { "LUF", 40.3399, 0 }, { "NLG", 2.20371, 2 }, { "PTE", 200.482, 2 }, { "GRD", 340.750, 2 }, { "SIT", 239.640, 2 }, { "MTL", 0.429300, 2 }, { "CYP", 0.585274, 2 }, { "SKK", 30.1260, 2 }, { "EEK", 15.6466, 2 }, { "LVL", 0.702804, 2 }, { "LTL", 3.45280, 2 }, { "HRK", 7.53450, 2 }, { "BLN", 1.95583, 2 } }; for (const auto & i : aConvertTable) if ( o3tl::equalsIgnoreAsciiCase( aSearchUnit, i.pCurrText ) ) { rfRate = i.fRate; rnDec = i.nDec; return true; } return false; } void ScInterpreter::ScEuroConvert() { //Value, FromUnit, ToUnit[, FullPrecision, [TriangulationPrecision]] sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 3, 5 ) ) return; double fPrecision = 0.0; if ( nParamCount == 5 ) { fPrecision = ::rtl::math::approxFloor(GetDouble()); if ( fPrecision < 3 ) { PushIllegalArgument(); return; } } bool bFullPrecision = nParamCount >= 4 && GetBool(); OUString aToUnit = GetString().getString(); OUString aFromUnit = GetString().getString(); double fVal = GetDouble(); if ( nGlobalError != FormulaError::NONE ) PushError( nGlobalError); else { double fFromRate; double fToRate; int nFromDec; int nToDec; if ( lclConvertMoney( aFromUnit, fFromRate, nFromDec ) && lclConvertMoney( aToUnit, fToRate, nToDec ) ) { double fRes; if ( aFromUnit.equalsIgnoreAsciiCase( aToUnit ) ) fRes = fVal; else { if ( aFromUnit.equalsIgnoreAsciiCase( "EUR" ) ) fRes = fVal * fToRate; else { double fIntermediate = fVal / fFromRate; if ( fPrecision ) fIntermediate = ::rtl::math::round( fIntermediate, static_cast<int>(fPrecision) ); fRes = fIntermediate * fToRate; } if ( !bFullPrecision ) fRes = ::rtl::math::round( fRes, nToDec ); } PushDouble( fRes ); } else PushIllegalArgument(); } } // BAHTTEXT #define UTF8_TH_0 "\340\270\250\340\270\271\340\270\231\340\270\242\340\271\214" #define UTF8_TH_1 "\340\270\253\340\270\231\340\270\266\340\271\210\340\270\207" #define UTF8_TH_2 "\340\270\252\340\270\255\340\270\207" #define UTF8_TH_3 "\340\270\252\340\270\262\340\270\241" #define UTF8_TH_4 "\340\270\252\340\270\265\340\271\210" #define UTF8_TH_5 "\340\270\253\340\271\211\340\270\262" #define UTF8_TH_6 "\340\270\253\340\270\201" #define UTF8_TH_7 "\340\271\200\340\270\210\340\271\207\340\270\224" #define UTF8_TH_8 "\340\271\201\340\270\233\340\270\224" #define UTF8_TH_9 "\340\271\200\340\270\201\340\271\211\340\270\262" #define UTF8_TH_10 "\340\270\252\340\270\264\340\270\232" #define UTF8_TH_11 "\340\271\200\340\270\255\340\271\207\340\270\224" #define UTF8_TH_20 "\340\270\242\340\270\265\340\271\210" #define UTF8_TH_1E2 "\340\270\243\340\271\211\340\270\255\340\270\242" #define UTF8_TH_1E3 "\340\270\236\340\270\261\340\270\231" #define UTF8_TH_1E4 "\340\270\253\340\270\241\340\270\267\340\271\210\340\270\231" #define UTF8_TH_1E5 "\340\271\201\340\270\252\340\270\231" #define UTF8_TH_1E6 "\340\270\245\340\271\211\340\270\262\340\270\231" #define UTF8_TH_DOT0 "\340\270\226\340\271\211\340\270\247\340\270\231" #define UTF8_TH_BAHT "\340\270\232\340\270\262\340\270\227" #define UTF8_TH_SATANG "\340\270\252\340\270\225\340\270\262\340\270\207\340\270\2 #define UTF8_TH_MINUS "\340\270\245\340\270\232" // local functions namespace { void lclSplitBlock( double& rfInt, sal_Int32& rnBlock, double fValue, double fSiz { rnBlock = static_cast< sal_Int32 >( modf( (fValue + 0.1) / fSize, &rfInt ) * fSize + 0.1 ); } /** Appends a digit (0 to 9) to the passed string. */ void lclAppendDigit( OStringBuffer& rText, sal_Int32 nDigit ) { switch( nDigit ) { case 0: rText.append( UTF8_TH_0 ); break; case 1: rText.append( UTF8_TH_1 ); break; case 2: rText.append( UTF8_TH_2 ); break; case 3: rText.append( UTF8_TH_3 ); break; case 4: rText.append( UTF8_TH_4 ); break; case 5: rText.append( UTF8_TH_5 ); break; case 6: rText.append( UTF8_TH_6 ); break; case 7: rText.append( UTF8_TH_7 ); break; case 8: rText.append( UTF8_TH_8 ); break; case 9: rText.append( UTF8_TH_9 ); break; default: OSL_FAIL( "lclAppendDigit - illegal digit" ); } } /** Appends a value raised to a power of 10: nDigit*10^nPow10. @param nDigit A digit in the range from 1 to 9. @param nPow10 A value in the range from 2 to 5. */ void lclAppendPow10( OStringBuffer& rText, sal_Int32 nDigit, sal_Int32 nPow10 ) { OSL_ENSURE( (1 <= nDigit) && (nDigit <= 9), "lclAppendPow10 - illegal digit" ); lclAppendDigit( rText, nDigit ); switch( nPow10 ) { case 2: rText.append( UTF8_TH_1E2 ); break; case 3: rText.append( UTF8_TH_1E3 ); break; case 4: rText.append( UTF8_TH_1E4 ); break; case 5: rText.append( UTF8_TH_1E5 ); break; default: OSL_FAIL( "lclAppendPow10 - illegal power" ); } } /** Appends a block of 6 digits (value from 1 to 999,999) to the passed string. */ void lclAppendBlock( OStringBuffer& rText, sal_Int32 nValue ) { OSL_ENSURE( (1 <= nValue) && (nValue <= 999999), "lclAppendBlock - illegal value" ); if( nValue >= 100000 ) { lclAppendPow10( rText, nValue / 100000, 5 ); nValue %= 100000; } if( nValue >= 10000 ) { lclAppendPow10( rText, nValue / 10000, 4 ); nValue %= 10000; } if( nValue >= 1000 ) { lclAppendPow10( rText, nValue / 1000, 3 ); nValue %= 1000; } if( nValue >= 100 ) { lclAppendPow10( rText, nValue / 100, 2 ); nValue %= 100; } if( nValue <= 0 ) return; sal_Int32 nTen = nValue / 10; sal_Int32 nOne = nValue % 10; if( nTen >= 1 ) { if( nTen >= 3 ) lclAppendDigit( rText, nTen ); else if( nTen == 2 ) rText.append( UTF8_TH_20 ); rText.append( UTF8_TH_10 ); } if( (nTen > 0) && (nOne == 1) ) rText.append( UTF8_TH_11 ); else if( nOne > 0 ) lclAppendDigit( rText, nOne ); } } // namespace void ScInterpreter::ScBahtText() { sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 1 ) ) return; double fValue = GetDouble(); if( nGlobalError != FormulaError::NONE ) { PushError( nGlobalError); return; } // sign bool bMinus = fValue < 0.0; fValue = std::abs( fValue ); // round to 2 digits after decimal point, fValue contains Satang as integer fValue = ::rtl::math::approxFloor( fValue * 100.0 + 0.5 ); // split Baht and Satang double fBaht = 0.0; sal_Int32 nSatang = 0; lclSplitBlock( fBaht, nSatang, fValue, 100.0 ); OStringBuffer aText; // generate text for Baht value if( fBaht == 0.0 ) { if( nSatang == 0 ) aText.append( UTF8_TH_0 ); } else while( fBaht > 0.0 ) { OStringBuffer aBlock; sal_Int32 nBlock = 0; lclSplitBlock( fBaht, nBlock, fBaht, 1.0e6 ); if( nBlock > 0 ) lclAppendBlock( aBlock, nBlock ); // add leading "million", if there will come more blocks if( fBaht > 0.0 ) aBlock.insert( 0, UTF8_TH_1E6 ); aText.insert(0, aBlock); } if (!aText.isEmpty()) aText.append( UTF8_TH_BAHT ); // generate text for Satang value if( nSatang == 0 ) { aText.append( UTF8_TH_DOT0 ); } else { lclAppendBlock( aText, nSatang ); aText.append( UTF8_TH_SATANG ); } // add the minus sign if( bMinus ) aText.insert( 0, UTF8_TH_MINUS ); PushString( OStringToOUString(aText, RTL_TEXTENCODING_UTF8) ); } void ScInterpreter::ScGetPivotData() { sal_uInt8 nParamCount = GetByte(); if (!MustHaveParamCountMin(nParamCount, 2) || (nParamCount % 2) == 1) { PushError(FormulaError::NoRef); return; } bool bOldSyntax = false; if (nParamCount == 2) { // if the first parameter is a ref, assume old syntax StackVar eFirstType = GetStackType(2); if (eFirstType == svSingleRef || eFirstType == svDoubleRef) bOldSyntax = true; } std::vector<sheet::DataPilotFieldFilter> aFilters; OUString aDataFieldName; ScRange aBlock; if (bOldSyntax) { aDataFieldName = GetString().getString(); switch (GetStackType()) { case svDoubleRef : PopDoubleRef(aBlock); break; case svSingleRef : { ScAddress aAddr; PopSingleRef(aAddr); aBlock = aAddr; } break; default: PushError(FormulaError::NoRef); return; } } else { // Standard syntax: separate name/value pairs sal_uInt16 nFilterCount = nParamCount / 2 - 1; aFilters.resize(nFilterCount); sal_uInt16 i = nFilterCount; while (i > 0) { --i; /* TODO: also, in case of numeric the entire filter match should * not be on a (even if locale independent) formatted string down * below in pDPObj->GetPivotData(). */ bool bEvaluateFormatIndex; switch (GetRawStackType()) { case svSingleRef: case svDoubleRef: bEvaluateFormatIndex = true; break; default: bEvaluateFormatIndex = false; } double fDouble; svl::SharedString aSharedString; bool bDouble = GetDoubleOrString( fDouble, aSharedString); if (nGlobalError != FormulaError::NONE) { PushError( nGlobalError); return; } if (bDouble) { sal_uInt32 nNumFormat; if (bEvaluateFormatIndex && nCurFmtIndex) nNumFormat = nCurFmtIndex; else { if (nCurFmtType == SvNumFormatType::UNDEFINED) nNumFormat = 0; else nNumFormat = mrContext.NFGetStandardFormat( nCurFmtType, ScGlobal::eLnge); } const Color* pColor; mrContext.NFGetOutputString( fDouble, nNumFormat, aFilters[i].MatchValueName, &pColor); aFilters[i].MatchValue = ScDPCache::GetLocaleIndependentFormattedString( fDouble, mrContext, nNumFormat); } else { aFilters[i].MatchValueName = aSharedString.getString(); // Parse possible number from MatchValueName and format // locale independent as MatchValue. sal_uInt32 nNumFormat = 0; double fValue; if (mrContext.NFIsNumberFormat( aFilters[i].MatchValueName, nNumFormat, fValue)) aFilters[i].MatchValue = ScDPCache::GetLocaleIndependentFormattedString( fValue, mrContext, nNumFormat); else aFilters[i].MatchValue = aFilters[i].MatchValueName; } aFilters[i].FieldName = GetString().getString(); } switch (GetStackType()) { case svDoubleRef : PopDoubleRef(aBlock); break; case svSingleRef : { ScAddress aAddr; PopSingleRef(aAddr); aBlock = aAddr; } break; default: PushError(FormulaError::NoRef); return; } aDataFieldName = GetString().getString(); // First parameter is data field name. } // Early bail-out, don't grind through data pilot cache and all. if (nGlobalError != FormulaError::NONE) { PushError( nGlobalError); return; } // NOTE : MS Excel docs claim to use the 'most recent' which is not // exactly the same as what we do in ScDocument::GetDPAtBlock // However we do need to use GetDPABlock ScDPObject* pDPObj = mrDoc.GetDPAtBlock(aBlock); if (!pDPObj) { PushError(FormulaError::NoRef); return; } if (bOldSyntax) { OUString aFilterStr = aDataFieldName; std::vector<sal_Int16> aFilterFuncs; if (!pDPObj->ParseFilters(aDataFieldName, aFilters, aFilterFuncs, aFilterStr)) { PushError(FormulaError::NoRef); return; } // TODO : For now, we ignore filter functions since we couldn't find a // live example of how they are supposed to be used. We'll support // this again once we come across a real-world example. } double fVal = pDPObj->GetPivotData(aDataFieldName, aFilters); if (std::isnan(fVal)) { PushError(FormulaError::NoRef); return; } PushDouble(fVal); } /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
¤ Dauer der Verarbeitung: 0.70 Sekunden (vorverarbeitet am 2026-04-26) ¤*© Formatika GbR, Deutschland |
|
||||||||||||||
2026-05-26