LibreOffice Module sc (master) 1
dputil.cxx
Go to the documentation of this file.
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
10#include <dputil.hxx>
11#include <dpitemdata.hxx>
12#include <dpnumgroupinfo.hxx>
13#include <globstr.hrc>
14#include <scresid.hxx>
15#include <generalfunction.hxx>
16
17#include <comphelper/string.hxx>
20#include <svl/numformat.hxx>
21#include <svl/zforlist.hxx>
22#include <rtl/math.hxx>
23#include <o3tl/string_view.hxx>
24#include <osl/diagnose.h>
25
26#include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
27#include <com/sun/star/i18n/CalendarDisplayIndex.hpp>
28
29using namespace com::sun::star;
30
31namespace {
32
33const sal_uInt16 SC_DP_LEAPYEAR = 1648; // arbitrary leap year for date calculations
34
35OUString getTwoDigitString(sal_Int32 nValue)
36{
37 OUString aRet = OUString::number( nValue );
38 if ( aRet.getLength() < 2 )
39 aRet = "0" + aRet;
40 return aRet;
41}
42
43void appendDateStr(OUStringBuffer& rBuffer, double fValue, SvNumberFormatter* pFormatter)
44{
45 sal_uInt32 nFormat = pFormatter->GetStandardFormat( SvNumFormatType::DATE, ScGlobal::eLnge );
46 OUString aString;
47 pFormatter->GetInputLineString(fValue, nFormat, aString);
48 rBuffer.append(aString);
49}
50
51OUString getSpecialDateName(double fValue, bool bFirst, SvNumberFormatter* pFormatter)
52{
53 OUStringBuffer aBuffer;
54 aBuffer.append( bFirst ? '<' : '>' );
55 appendDateStr(aBuffer, fValue, pFormatter);
56 return aBuffer.makeStringAndClear();
57}
58
59}
60
61bool ScDPUtil::isDuplicateDimension(std::u16string_view rName)
62{
63 return o3tl::ends_with(rName, u"*");
64}
65
66OUString ScDPUtil::getSourceDimensionName(std::u16string_view rName)
67{
68 return OUString(comphelper::string::stripEnd(rName, '*'));
69}
70
72{
73 // Count all trailing '*'s.
74
75 sal_Int32 n = rName.getLength();
76 if (!n)
77 return 0;
78
79 sal_uInt8 nDupCount = 0;
80 const sal_Unicode* p = rName.getStr();
81 const sal_Unicode* pStart = p;
82 p += n-1; // Set it to the last char.
83 for (; p != pStart; --p, ++nDupCount)
84 {
85 if (*p != '*')
86 break;
87 }
88
89 return nDupCount;
90}
91
92OUString ScDPUtil::createDuplicateDimensionName(const OUString& rOriginal, size_t nDupCount)
93{
94 if (!nDupCount)
95 return rOriginal;
96
97 OUStringBuffer aBuf(rOriginal);
98 for (size_t i = 0; i < nDupCount; ++i)
99 aBuf.append('*');
100
101 return aBuf.makeStringAndClear();
102}
103
105 sal_Int32 nDatePart, sal_Int32 nValue, SvNumberFormatter* pFormatter,
106 double fStart, double fEnd)
107{
109 return getSpecialDateName(fStart, true, pFormatter);
111 return getSpecialDateName(fEnd, false, pFormatter);
112
113 switch ( nDatePart )
114 {
115 case sheet::DataPilotFieldGroupBy::YEARS:
116 return OUString::number(nValue);
117 case sheet::DataPilotFieldGroupBy::QUARTERS:
118 return ScGlobal::getLocaleData().getQuarterAbbreviation(sal_Int16(nValue-1)); // nValue is 1-based
119 case css::sheet::DataPilotFieldGroupBy::MONTHS:
121 i18n::CalendarDisplayIndex::MONTH, sal_Int16(nValue-1), 0); // 0-based, get short name
122 case sheet::DataPilotFieldGroupBy::DAYS:
123 {
124 Date aDate(1, 1, SC_DP_LEAPYEAR);
125 aDate.AddDays(nValue - 1); // nValue is 1-based
126 tools::Long nDays = aDate - pFormatter->GetNullDate();
127
128 const sal_uInt32 nFormat = pFormatter->GetFormatIndex(NF_DATE_SYS_DDMMM, ScGlobal::eLnge);
129 const Color* pColor;
130 OUString aStr;
131 pFormatter->GetOutputString(nDays, nFormat, aStr, &pColor);
132 return aStr;
133 }
134 case sheet::DataPilotFieldGroupBy::HOURS:
135 {
136 //TODO: allow am/pm format?
137 return getTwoDigitString(nValue);
138 }
139 break;
140 case sheet::DataPilotFieldGroupBy::MINUTES:
141 case sheet::DataPilotFieldGroupBy::SECONDS:
142 {
143 return ScGlobal::getLocaleData().getTimeSep() + getTwoDigitString(nValue);
144 }
145 break;
146 default:
147 OSL_FAIL("invalid date part");
148 }
149
150 return "FIXME: unhandled value";
151}
152
153double ScDPUtil::getNumGroupStartValue(double fValue, const ScDPNumGroupInfo& rInfo)
154{
155 if (fValue < rInfo.mfStart && !rtl::math::approxEqual(fValue, rInfo.mfStart))
156 return -std::numeric_limits<double>::infinity();
157
158 if (fValue > rInfo.mfEnd && !rtl::math::approxEqual(fValue, rInfo.mfEnd))
159 return std::numeric_limits<double>::infinity();
160
161 double fDiff = fValue - rInfo.mfStart;
162 double fDiv = rtl::math::approxFloor( fDiff / rInfo.mfStep );
163 double fGroupStart = rInfo.mfStart + fDiv * rInfo.mfStep;
164
165 if (rtl::math::approxEqual(fGroupStart, rInfo.mfEnd) &&
166 !rtl::math::approxEqual(fGroupStart, rInfo.mfStart))
167 {
168 if (!rInfo.mbDateValues)
169 {
170 // A group that would consist only of the end value is not
171 // created, instead the value is included in the last group
172 // before. So the previous group is used if the calculated group
173 // start value is the selected end value.
174
175 fDiv -= 1.0;
176 return rInfo.mfStart + fDiv * rInfo.mfStep;
177 }
178
179 // For date values, the end value is instead treated as above the
180 // limit if it would be a group of its own.
181
182 return rInfo.mfEnd + rInfo.mfStep;
183 }
184
185 return fGroupStart;
186}
187
188namespace {
189
190void lcl_AppendDateStr( OUStringBuffer& rBuffer, double fValue, SvNumberFormatter* pFormatter )
191{
192 sal_uInt32 nFormat = pFormatter->GetStandardFormat( SvNumFormatType::DATE, ScGlobal::eLnge );
193 OUString aString;
194 pFormatter->GetInputLineString( fValue, nFormat, aString );
195 rBuffer.append( aString );
196}
197
198OUString lcl_GetSpecialNumGroupName( double fValue, bool bFirst, sal_Unicode cDecSeparator,
199 bool bDateValues, SvNumberFormatter* pFormatter )
200{
201 OSL_ENSURE( cDecSeparator != 0, "cDecSeparator not initialized" );
202
203 OUStringBuffer aBuffer;
204 aBuffer.append( bFirst ? '<' : '>' );
205 if ( bDateValues )
206 lcl_AppendDateStr( aBuffer, fValue, pFormatter );
207 else
208 rtl::math::doubleToUStringBuffer( aBuffer, fValue, rtl_math_StringFormat_Automatic,
209 rtl_math_DecimalPlaces_Max, cDecSeparator, true );
210 return aBuffer.makeStringAndClear();
211}
212
213OUString lcl_GetNumGroupName(
214 double fStartValue, const ScDPNumGroupInfo& rInfo, sal_Unicode cDecSep,
215 SvNumberFormatter* pFormatter)
216{
217 OSL_ENSURE( cDecSep != 0, "cDecSeparator not initialized" );
218
219 double fStep = rInfo.mfStep;
220 double fEndValue = fStartValue + fStep;
221 if (rInfo.mbIntegerOnly && (rInfo.mbDateValues || !rtl::math::approxEqual(fEndValue, rInfo.mfEnd)))
222 {
223 // The second number of the group label is
224 // (first number + size - 1) if there are only integer numbers,
225 // (first number + size) if any non-integer numbers are involved.
226 // Exception: The last group (containing the end value) is always
227 // shown as including the end value (but not for dates).
228
229 fEndValue -= 1.0;
230 }
231
232 if ( fEndValue > rInfo.mfEnd && !rInfo.mbAutoEnd )
233 {
234 // limit the last group to the end value
235
236 fEndValue = rInfo.mfEnd;
237 }
238
239 OUStringBuffer aBuffer;
240 if ( rInfo.mbDateValues )
241 {
242 lcl_AppendDateStr( aBuffer, fStartValue, pFormatter );
243 aBuffer.append( " - " ); // with spaces
244 lcl_AppendDateStr( aBuffer, fEndValue, pFormatter );
245 }
246 else
247 {
248 rtl::math::doubleToUStringBuffer( aBuffer, fStartValue, rtl_math_StringFormat_Automatic,
249 rtl_math_DecimalPlaces_Max, cDecSep, true );
250 aBuffer.append( '-' );
251 rtl::math::doubleToUStringBuffer( aBuffer, fEndValue, rtl_math_StringFormat_Automatic,
252 rtl_math_DecimalPlaces_Max, cDecSep, true );
253 }
254
255 return aBuffer.makeStringAndClear();
256}
257
258}
259
261 double fValue, const ScDPNumGroupInfo& rInfo, sal_Unicode cDecSep, SvNumberFormatter* pFormatter)
262{
263 if ( fValue < rInfo.mfStart && !rtl::math::approxEqual( fValue, rInfo.mfStart ) )
264 return lcl_GetSpecialNumGroupName( rInfo.mfStart, true, cDecSep, rInfo.mbDateValues, pFormatter );
265
266 if ( fValue > rInfo.mfEnd && !rtl::math::approxEqual( fValue, rInfo.mfEnd ) )
267 return lcl_GetSpecialNumGroupName( rInfo.mfEnd, false, cDecSep, rInfo.mbDateValues, pFormatter );
268
269 double fDiff = fValue - rInfo.mfStart;
270 double fDiv = rtl::math::approxFloor( fDiff / rInfo.mfStep );
271 double fGroupStart = rInfo.mfStart + fDiv * rInfo.mfStep;
272
273 if ( rtl::math::approxEqual( fGroupStart, rInfo.mfEnd ) &&
274 !rtl::math::approxEqual( fGroupStart, rInfo.mfStart ) )
275 {
276 if (rInfo.mbDateValues)
277 {
278 // For date values, the end value is instead treated as above the limit
279 // if it would be a group of its own.
280 return lcl_GetSpecialNumGroupName( rInfo.mfEnd, false, cDecSep, rInfo.mbDateValues, pFormatter );
281 }
282 }
283
284 return lcl_GetNumGroupName(fGroupStart, rInfo, cDecSep, pFormatter);
285}
286
288 double fValue, const ScDPNumGroupInfo* pInfo, sal_Int32 nDatePart,
289 const SvNumberFormatter* pFormatter)
290{
291 // Start and end are inclusive
292 // (End date without a time value is included, with a time value it's not)
293
294 if (pInfo)
295 {
296 if (fValue < pInfo->mfStart && !rtl::math::approxEqual(fValue, pInfo->mfStart))
298 if (fValue > pInfo->mfEnd && !rtl::math::approxEqual(fValue, pInfo->mfEnd))
300 }
301
302 sal_Int32 nResult = 0;
303
304 if (nDatePart == sheet::DataPilotFieldGroupBy::HOURS ||
305 nDatePart == sheet::DataPilotFieldGroupBy::MINUTES ||
306 nDatePart == sheet::DataPilotFieldGroupBy::SECONDS)
307 {
308 // handle time
309 // (do as in the cell functions, ScInterpreter::ScGetHour() etc.)
310
311 sal_uInt16 nHour, nMinute, nSecond;
312 double fFractionOfSecond;
313 tools::Time::GetClock( fValue, nHour, nMinute, nSecond, fFractionOfSecond, 0);
314
315 switch (nDatePart)
316 {
317 case sheet::DataPilotFieldGroupBy::HOURS:
318 nResult = nHour;
319 break;
320 case sheet::DataPilotFieldGroupBy::MINUTES:
321 nResult = nMinute;
322 break;
323 case sheet::DataPilotFieldGroupBy::SECONDS:
324 nResult = nSecond;
325 break;
326 }
327 }
328 else
329 {
330 Date aDate = pFormatter->GetNullDate();
331 aDate.AddDays(::rtl::math::approxFloor(fValue));
332
333 switch ( nDatePart )
334 {
335 case css::sheet::DataPilotFieldGroupBy::YEARS:
336 nResult = aDate.GetYear();
337 break;
338 case css::sheet::DataPilotFieldGroupBy::QUARTERS:
339 nResult = 1 + (aDate.GetMonth() - 1) / 3; // 1..4
340 break;
341 case css::sheet::DataPilotFieldGroupBy::MONTHS:
342 nResult = aDate.GetMonth(); // 1..12
343 break;
344 case css::sheet::DataPilotFieldGroupBy::DAYS:
345 {
346 Date aYearStart(1, 1, aDate.GetYear());
347 nResult = (aDate - aYearStart) + 1; // Jan 01 has value 1
348 if (nResult >= 60 && !aDate.IsLeapYear())
349 {
350 // days are counted from 1 to 366 - if not from a leap year, adjust
351 ++nResult;
352 }
353 }
354 break;
355 default:
356 OSL_FAIL("invalid date part");
357 }
358 }
359
360 return nResult;
361}
362
363namespace {
364
365const TranslateId aFuncStrIds[] = {
366 {}, // SUBTOTAL_FUNC_NONE
367 STR_FUN_TEXT_AVG, // SUBTOTAL_FUNC_AVE
368 STR_FUN_TEXT_COUNT, // SUBTOTAL_FUNC_CNT
369 STR_FUN_TEXT_COUNT, // SUBTOTAL_FUNC_CNT2
370 STR_FUN_TEXT_MAX, // SUBTOTAL_FUNC_MAX
371 STR_FUN_TEXT_MIN, // SUBTOTAL_FUNC_MIN
372 STR_FUN_TEXT_PRODUCT, // SUBTOTAL_FUNC_PROD
373 STR_FUN_TEXT_STDDEV, // SUBTOTAL_FUNC_STD
374 STR_FUN_TEXT_STDDEV, // SUBTOTAL_FUNC_STDP
375 STR_FUN_TEXT_SUM, // SUBTOTAL_FUNC_SUM
376 STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VAR
377 STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VARP
378 STR_FUN_TEXT_MEDIAN, // SUBTOTAL_FUNC_MED
379 {} // SUBTOTAL_FUNC_SELECTION_COUNT - not used for pivot table
380};
381
382}
383
384OUString ScDPUtil::getDisplayedMeasureName(const OUString& rName, ScSubTotalFunc eFunc)
385{
386 assert(unsigned(eFunc) < SAL_N_ELEMENTS(aFuncStrIds));
387 TranslateId pId = aFuncStrIds[eFunc];
388 if (!pId)
389 return rName;
390 return ScResId(pId) + // function name
391 " - " +
392 rName; // field name
393}
394
396{
398 switch (eGenFunc)
399 {
400 case ScGeneralFunction::NONE: eSubTotal = SUBTOTAL_FUNC_NONE; break;
401 case ScGeneralFunction::SUM: eSubTotal = SUBTOTAL_FUNC_SUM; break;
402 case ScGeneralFunction::COUNT: eSubTotal = SUBTOTAL_FUNC_CNT2; break;
403 case ScGeneralFunction::AVERAGE: eSubTotal = SUBTOTAL_FUNC_AVE; break;
404 case ScGeneralFunction::MEDIAN: eSubTotal = SUBTOTAL_FUNC_MED; break;
405 case ScGeneralFunction::MAX: eSubTotal = SUBTOTAL_FUNC_MAX; break;
406 case ScGeneralFunction::MIN: eSubTotal = SUBTOTAL_FUNC_MIN; break;
407 case ScGeneralFunction::PRODUCT: eSubTotal = SUBTOTAL_FUNC_PROD; break;
408 case ScGeneralFunction::COUNTNUMS: eSubTotal = SUBTOTAL_FUNC_CNT; break;
409 case ScGeneralFunction::STDEV: eSubTotal = SUBTOTAL_FUNC_STD; break;
410 case ScGeneralFunction::STDEVP: eSubTotal = SUBTOTAL_FUNC_STDP; break;
411 case ScGeneralFunction::VAR: eSubTotal = SUBTOTAL_FUNC_VAR; break;
412 case ScGeneralFunction::VARP: eSubTotal = SUBTOTAL_FUNC_VARP; break;
413 case ScGeneralFunction::AUTO: eSubTotal = SUBTOTAL_FUNC_NONE; break;
414 default:
415 assert(false);
416 }
417 return eSubTotal;
418}
419
420/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
OUString getDisplayName(sal_Int16 nCalendarDisplayIndex, sal_Int16 nIdx, sal_Int16 nNameType) const
void AddDays(sal_Int32 nAddDays)
sal_Int16 GetYear() const
bool IsLeapYear() const
sal_uInt16 GetMonth() const
const OUString & getTimeSep() const
const OUString & getQuarterAbbreviation(sal_Int16 nQuarter) const
static const sal_Int32 DateFirst
Definition: dpitemdata.hxx:35
static const sal_Int32 DateLast
Definition: dpitemdata.hxx:36
static sal_uInt8 getDuplicateIndex(const OUString &rName)
Get a duplicate index in case the dimension is a duplicate.
Definition: dputil.cxx:71
static SC_DLLPUBLIC OUString getDateGroupName(sal_Int32 nDatePart, sal_Int32 nValue, SvNumberFormatter *pFormatter, double fStart, double fEnd)
Definition: dputil.cxx:104
static SC_DLLPUBLIC OUString getSourceDimensionName(std::u16string_view rName)
Definition: dputil.cxx:66
static sal_Int32 getDatePartValue(double fValue, const ScDPNumGroupInfo *pInfo, sal_Int32 nDatePart, const SvNumberFormatter *pFormatter)
Definition: dputil.cxx:287
static bool isDuplicateDimension(std::u16string_view rName)
Definition: dputil.cxx:61
static OUString getNumGroupName(double fValue, const ScDPNumGroupInfo &rInfo, sal_Unicode cDecSep, SvNumberFormatter *pFormatter)
Definition: dputil.cxx:260
static SC_DLLPUBLIC ScSubTotalFunc toSubTotalFunc(ScGeneralFunction eGenFunc)
Definition: dputil.cxx:395
static SC_DLLPUBLIC OUString createDuplicateDimensionName(const OUString &rOriginal, size_t nDupCount)
Definition: dputil.cxx:92
static SC_DLLPUBLIC OUString getDisplayedMeasureName(const OUString &rName, ScSubTotalFunc eFunc)
Definition: dputil.cxx:384
static double getNumGroupStartValue(double fValue, const ScDPNumGroupInfo &rInfo)
Definition: dputil.cxx:153
static SC_DLLPUBLIC LanguageType eLnge
Definition: global.hxx:545
static SC_DLLPUBLIC const LocaleDataWrapper & getLocaleData()
Definition: global.cxx:1015
static CalendarWrapper & GetCalendar()
Definition: global.cxx:1033
sal_uInt32 GetStandardFormat(SvNumFormatType eType, LanguageType eLnge=LANGUAGE_DONTKNOW)
void GetOutputString(const double &fOutNumber, sal_uInt32 nFIndex, OUString &sOutString, const Color **ppColor, bool bUseStarFormat=false)
void GetInputLineString(const double &fOutNumber, sal_uInt32 nFIndex, OUString &rOutString, bool bFiltering=false, bool bForceSystemLocale=false)
const Date & GetNullDate() const
sal_uInt32 GetFormatIndex(NfIndexTableOffset, LanguageType eLnge=LANGUAGE_DONTKNOW)
static void GetClock(double fTimeInDays, sal_uInt16 &nHour, sal_uInt16 &nMinute, sal_uInt16 &nSecond, double &fFractionOfSecond, int nFractionDecimals)
const sal_uInt16 SC_DP_LEAPYEAR
Definition: dpgroup.cxx:45
float u
sal_Int16 nValue
ScGeneralFunction
the css::sheet::GeneralFunction enum is extended by constants in GeneralFunction2,...
@ AVERAGE
average of all numerical values is calculated.
@ PRODUCT
product of all numerical values is calculated.
@ MAX
maximum value of all numerical values is calculated.
@ COUNT
all values, including non-numerical values, are counted.
@ VARP
variance is calculated based on the entire population.
@ SUM
sum of all numerical values is calculated.
@ STDEVP
standard deviation is calculated based on the entire population.
@ MEDIAN
median of all numerical values is calculated.
@ COUNTNUMS
numerical values are counted.
@ NONE
nothing is calculated.
@ MIN
minimum value of all numerical values is calculated.
@ VAR
variance is calculated based on a sample.
@ AUTO
function is determined automatically.
@ STDEV
standard deviation is calculated based on a sample.
ScSubTotalFunc
Definition: global.hxx:844
@ SUBTOTAL_FUNC_STDP
Definition: global.hxx:853
@ SUBTOTAL_FUNC_MAX
Definition: global.hxx:849
@ SUBTOTAL_FUNC_CNT2
Definition: global.hxx:848
@ SUBTOTAL_FUNC_AVE
Definition: global.hxx:846
@ SUBTOTAL_FUNC_VARP
Definition: global.hxx:856
@ SUBTOTAL_FUNC_VAR
Definition: global.hxx:855
@ SUBTOTAL_FUNC_NONE
Definition: global.hxx:845
@ SUBTOTAL_FUNC_SUM
Definition: global.hxx:854
@ SUBTOTAL_FUNC_MED
Definition: global.hxx:857
@ SUBTOTAL_FUNC_STD
Definition: global.hxx:852
@ SUBTOTAL_FUNC_MIN
Definition: global.hxx:850
@ SUBTOTAL_FUNC_CNT
Definition: global.hxx:847
@ SUBTOTAL_FUNC_PROD
Definition: global.hxx:851
void * p
sal_Int64 n
#define SAL_N_ELEMENTS(arr)
aStr
aBuf
OString stripEnd(const OString &rIn, char c)
int i
constexpr bool ends_with(std::basic_string_view< charT, traits > sv, std::basic_string_view< charT, traits > x) noexcept
long Long
OUString ScResId(TranslateId aId)
Definition: scdll.cxx:90
unsigned char sal_uInt8
sal_uInt16 sal_Unicode
std::unique_ptr< char[]> aBuffer
NF_DATE_SYS_DDMMM