1
/* This file is part of the KDE project
2
Copyright 2004 Tomas Mecir <mecirt@gmail.com>
3
Copyright (C) 1998-2004 KSpread Team <koffice-devel@mail.kde.org>
5
This library is free software; you can redistribute it and/or
6
modify it under the terms of the GNU Library General Public
7
License as published by the Free Software Foundation; either
8
version 2 of the License, or (at your option) any later version.
10
This library is distributed in the hope that it will be useful,
11
but WITHOUT ANY WARRANTY; without even the implied warranty of
12
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13
Library General Public License for more details.
15
You should have received a copy of the GNU Library General Public License
16
along with this library; see the file COPYING.LIB. If not, write to
17
the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
18
Boston, MA 02111-1307, USA.
21
#include "valueformatter.h"
23
#include "kspread_cell.h"
24
#include "kspread_locale.h"
25
#include "kspread_util.h"
26
#include "valueconverter.h"
28
#include <kcalendarsystem.h>
35
using namespace KSpread;
37
ValueFormatter::ValueFormatter (ValueConverter *conv) : converter( conv )
41
QString ValueFormatter::formatText (KSpreadCell *cell, FormatType fmtType)
43
if (cell->hasError ())
44
return errorFormat (cell);
48
KSpreadFormat::FloatFormat floatFormat =
49
cell->floatFormat (cell->column(), cell->row());
50
int precision = cell->precision (cell->column(), cell->row());
51
QString prefix = cell->prefix (cell->column(), cell->row());
52
QString postfix = cell->postfix (cell->column(), cell->row());
54
return formatText (cell->value(), fmtType, precision,
55
floatFormat, prefix, postfix);
58
QString ValueFormatter::formatText (const KSpreadValue &value,
59
FormatType fmtType, int precision, KSpreadFormat::FloatFormat floatFormat,
60
const QString &prefix, const QString &postfix)
62
//if we have an array, use its first element
64
return formatText (value.element (0, 0), fmtType, precision,
65
floatFormat, prefix, postfix);
69
//step 1: determine formatting that will be used
70
fmtType = determineFormatting (value, fmtType);
72
//step 2: format the value !
75
if (fmtType == Text_format)
77
str = converter->asString (value).asString();
78
if (!str.isEmpty() && str[0]=='\'' )
83
else if (formatIsDate (fmtType))
84
str = dateFormat (value.asDate(), fmtType);
87
else if (formatIsTime (fmtType))
88
str = timeFormat (value.asDateTime(), fmtType);
91
else if (formatIsFraction (fmtType))
92
str = fractionFormat (value.asFloat(), fmtType);
97
QChar decimal_point = converter->locale()->decimalSymbol()[0];
98
if ( decimal_point.isNull() )
101
//some cell parameters ...
102
double v = converter->asFloat (value).asFloat();
105
if ((floatFormat == KSpreadCell::AlwaysUnsigned) && (v < 0.0))
108
// Make a string out of it.
109
str = createNumberFormat (v, precision, fmtType,
110
(floatFormat == KSpreadCell::AlwaysSigned));
112
// Remove trailing zeros and the decimal point if necessary
113
// unless the number has no decimal point
115
removeTrailingZeros (str, decimal_point);
118
if (!prefix.isEmpty())
119
str = prefix + ' ' + str;
121
if( !postfix.isEmpty())
122
str += ' ' + postfix;
124
//kdDebug() << "ValueFormatter says: " << str << endl;
128
FormatType ValueFormatter::determineFormatting (const KSpreadValue &value,
131
//if the cell value is a string, then we want to display it as-is,
132
//no matter what, same if the cell is empty
133
if (value.isString () || (value.format() == KSpreadValue::fmt_None))
135
//same if we're supposed to display string, no matter what we actually got
136
if (fmtType == Text_format)
139
//now, everything depends on whether the formatting is Generic or not
140
if (fmtType == Generic_format)
142
//here we decide based on value's format...
143
KSpreadValue::Format fmt = value.format();
145
case KSpreadValue::fmt_None:
146
fmtType = Text_format;
148
case KSpreadValue::fmt_Boolean:
149
fmtType = Text_format;
151
case KSpreadValue::fmt_Number:
152
if (value.asFloat() > 1e+10)
153
fmtType = Scientific_format;
155
fmtType = Number_format;
157
case KSpreadValue::fmt_Percent:
158
fmtType = Percentage_format;
160
case KSpreadValue::fmt_Money:
161
fmtType = Money_format;
163
case KSpreadValue::fmt_DateTime:
164
fmtType = TextDate_format;
166
case KSpreadValue::fmt_Date:
167
fmtType = ShortDate_format;
169
case KSpreadValue::fmt_Time:
170
fmtType = Time_format;
172
case KSpreadValue::fmt_String:
173
//this should never happen
174
fmtType = Text_format;
181
//we'll mostly want to use the given formatting, the only exception
182
//being Boolean values
184
//TODO: is this correct? We may also want to convert bools to 1s and 0s
185
//if we want to display a number...
187
//TODO: what to do about Custom formatting? We don't support it as of now,
188
// but we'll have it ... one day, that is ...
189
if (value.isBoolean())
197
void ValueFormatter::removeTrailingZeros (QString &str, QChar decimal_point)
199
if (str.find (decimal_point) < 0)
200
//no decimal point -> nothing to do
204
int cslen = converter->locale()->currencySymbol().length();
205
if (str.find ('%') != -1)
207
else if (str.find (converter->locale()->currencySymbol()) ==
208
((int) (str.length() - cslen)))
210
else if ((start = str.find ('E')) != -1)
211
start = str.length() - start;
215
int i = str.length() - start;
216
bool bFinished = FALSE;
217
while ( !bFinished && i > 0 )
219
QChar ch = str[i - 1];
225
if (ch == decimal_point)
231
QString ValueFormatter::createNumberFormat ( double value, int precision,
232
FormatType fmt, bool alwaysSigned)
234
// if precision is -1, ask for a huge number of decimals, we'll remove
235
// the zeros later. Is 8 ok ?
236
int p = (precision == -1) ? 8 : precision;
237
QString localizedNumber;
240
//multiply value by 100 for percentage format
241
if (fmt == Percentage_format)
244
// this will avoid displaying negative zero, i.e "-0.0000"
245
if( fabs( value ) < DBL_EPSILON ) value = 0.0;
247
// round the number, based on desired precision if not scientific is chosen
248
//(scientific has relative precision)
249
if( fmt != Scientific_format )
251
double m[] = { 1, 10, 100, 1e3, 1e4, 1e5, 1e6, 1e7, 1e8, 1e9, 1e10 };
252
double mm = (p > 10) ? pow(10.0,p) : m[p];
253
bool neg = value < 0;
254
value = floor( fabs(value)*mm + 0.5 ) / mm;
255
if( neg ) value = -value;
262
localizedNumber = converter->locale()->formatNumber(value, p);
264
case Percentage_format:
265
localizedNumber = converter->locale()->formatNumber (value, p)+ " %";
268
localizedNumber = converter->locale()->formatMoney (value,
269
converter->locale()->currencySymbol(), p );
271
case Scientific_format:
272
decimal_point = converter->locale()->decimalSymbol()[0];
273
localizedNumber = QString::number (value, 'E', p);
274
if ((pos = localizedNumber.find ('.')) != -1)
275
localizedNumber = localizedNumber.replace (pos, 1, decimal_point);
279
kdDebug(36001)<<"Wrong usage of ValueFormatter::createNumberFormat\n";
283
//prepend positive sign if needed
284
if (alwaysSigned && value >= 0 )
285
if (converter->locale()->positiveSign().isEmpty())
286
localizedNumber='+'+localizedNumber;
288
return localizedNumber;
291
QString ValueFormatter::fractionFormat (double value, FormatType fmtType)
293
double result = value - floor(value);
297
/* return w/o fraction part if not necessary */
299
return QString::number(value);
305
case fraction_quarter:
308
case fraction_eighth:
311
case fraction_sixteenth:
317
case fraction_hundredth:
320
case fraction_one_digit:
324
case fraction_two_digits:
328
case fraction_three_digits:
333
kdDebug(36001) << "Error in Fraction format\n";
334
return QString::number(value);
339
/* handle halves, quarters, tenths, ... */
341
if (fmtType != fraction_three_digits
342
&& fmtType != fraction_two_digits
343
&& fmtType != fraction_one_digit) {
346
double diff = result;
347
for (int i = 1; i <= index; i++) {
348
calc = i * 1.0 / index;
349
if (fabs(result - calc) < diff) {
351
diff = fabs(result - calc);
354
if( index1 == 0 ) return QString("%1").arg( floor(value) );
355
if( index1 == index ) return QString("%1").arg( floor(value)+1 );
356
if( floor(value) == 0)
357
return QString("%1/%2").arg( index1 ).arg( index );
359
return QString("%1 %2/%3")
366
/* handle fraction_one_digit, fraction_two_digit
367
* and fraction_three_digit style */
369
double precision, denominator, numerator;
372
double val1 = result;
373
double val2 = rint(result);
378
precision = pow(10.0, -index);
382
while (fabs(numerator/denominator - result) > precision) {
383
val1 = (1 / (val1 - val2));
385
p = val2 * numerator + inter2;
386
q = val2 * denominator + inter4;
388
inter4 = denominator;
393
} while (fabs(denominator) > limit);
395
denominator = fabs(denominator);
396
numerator = fabs(numerator);
398
if (denominator == numerator)
399
return QString().setNum(floor(value + 1));
402
if ( floor(value) == 0 )
403
return QString("%1/%2").arg(numerator).arg(denominator);
405
return QString("%1 %2/%3")
412
QString ValueFormatter::timeFormat (const QDateTime &dt, FormatType fmtType)
414
if (fmtType == Time_format)
415
return converter->locale()->formatTime(dt.time(), false);
417
if (fmtType == SecondeTime_format)
418
return converter->locale()->formatTime(dt.time(), true);
420
int h = dt.time().hour();
421
int m = dt.time().minute();
422
int s = dt.time().second();
424
QString hour = ( h < 10 ? "0" + QString::number(h) : QString::number(h) );
425
QString minute = ( m < 10 ? "0" + QString::number(m) : QString::number(m) );
426
QString second = ( s < 10 ? "0" + QString::number(s) : QString::number(s) );
428
QString AMPM( pm ? i18n("PM"):i18n("AM") );
430
if (fmtType == Time_format1) { // 9 : 01 AM
431
return QString("%1:%2 %3")
432
.arg((pm ? h - 12 : h),2)
437
if (fmtType == Time_format2) { //9:01:05 AM
438
return QString("%1:%2:%3 %4")
439
.arg((pm ? h-12 : h),2)
445
if (fmtType == Time_format3) {
446
return QString("%1 %2 %3 %4 %5 %6") // 9 h 01 min 28 s
455
if (fmtType == Time_format4) { // 9:01
456
return QString("%1:%2").arg(hour, 2).arg(minute, 2);
459
if (fmtType == Time_format5) { // 9:01:12
460
return QString("%1:%2:%3").arg(hour, 2).arg(minute, 2).arg(second, 2);
464
QDate d2( 1899, 12, 31 );
465
int d = d2.daysTo( d1 ) + 1;
469
if (fmtType == Time_format6)
472
return QString("%1:%2").arg(m, 1).arg(second, 2);
474
if (fmtType == Time_format7) { // [h]:mm:ss
475
return QString("%1:%2:%3").arg(h, 1).arg(minute, 2).arg(second, 2);
477
if (fmtType == Time_format8)
480
return QString("%1:%2").arg(h, 1).arg(minute, 2);
483
return converter->locale()->formatTime( dt.time(), false );
486
QString ValueFormatter::dateFormat (const QDate &date, FormatType fmtType)
489
if (fmtType == ShortDate_format) {
490
tmp = converter->locale()->formatDate(date, true);
492
else if (fmtType == TextDate_format) {
493
tmp = converter->locale()->formatDate(date, false);
495
else if (fmtType == date_format1) { /*18-Feb-99 */
496
tmp = QString().sprintf("%02d", date.day());
497
tmp += "-" + converter->locale()->calendar()->monthString(date, true) + "-";
498
tmp += QString::number(date.year()).right(2);
500
else if (fmtType == date_format2) { /*18-Feb-1999 */
501
tmp = QString().sprintf("%02d", date.day());
502
tmp += "-" + converter->locale()->calendar()->monthString(date, true) + "-";
503
tmp += QString::number(date.year());
505
else if (fmtType == date_format3) { /*18-Feb */
506
tmp = QString().sprintf("%02d", date.day());
507
tmp += "-" + converter->locale()->calendar()->monthString(date, true);
509
else if (fmtType == date_format4) { /*18-05 */
510
tmp = QString().sprintf("%02d", date.day());
511
tmp += "-" + QString().sprintf("%02d", date.month() );
513
else if (fmtType == date_format5) { /*18/05/00 */
514
tmp = QString().sprintf("%02d", date.day());
515
tmp += "/" + QString().sprintf("%02d", date.month()) + "/";
516
tmp += QString::number(date.year()).right(2);
518
else if (fmtType == date_format6) { /*18/05/1999 */
519
tmp = QString().sprintf("%02d", date.day());
520
tmp += "/" + QString().sprintf("%02d", date.month()) + "/";
521
tmp += QString::number(date.year());
523
else if (fmtType == date_format7) { /*Feb-99 */
524
tmp = converter->locale()->calendar()->monthString(date, true) + "-";
525
tmp += QString::number(date.year()).right(2);
527
else if (fmtType == date_format8) { /*February-99 */
528
tmp = converter->locale()->calendar()->monthString(date, false) + "-";
529
tmp += QString::number(date.year()).right(2);
531
else if (fmtType == date_format9) { /*February-1999 */
532
tmp = converter->locale()->calendar()->monthString(date, false) + "-";
533
tmp += QString::number(date.year());
535
else if (fmtType == date_format10) { /*F-99 */
536
tmp = converter->locale()->calendar()->monthString(date, false).at(0) + "-";
537
tmp += QString::number(date.year()).right(2);
539
else if (fmtType == date_format11) { /*18/Feb */
540
tmp = QString().sprintf("%02d", date.day()) + "/";
541
tmp += converter->locale()->calendar()->monthString(date, true);
543
else if (fmtType == date_format12) { /*18/02 */
544
tmp = QString().sprintf("%02d", date.day()) + "/";
545
tmp += QString().sprintf("%02d", date.month());
547
else if (fmtType == date_format13) { /*18/Feb/1999 */
548
tmp = QString().sprintf("%02d", date.day());
549
tmp += "/" + converter->locale()->calendar()->monthString(date, true) + "/";
550
tmp += QString::number(date.year());
552
else if (fmtType == date_format14) { /*2000/Feb/18 */
553
tmp = QString::number(date.year());
554
tmp += "/" + converter->locale()->calendar()->monthString(date, true) + "/";
555
tmp += QString().sprintf("%02d", date.day());
557
else if (fmtType == date_format15) { /*2000-Feb-18 */
558
tmp = QString::number(date.year());
559
tmp += "-" + converter->locale()->calendar()->monthString(date, true) + "-";
560
tmp += QString().sprintf("%02d", date.day());
562
else if (fmtType == date_format16) { /*2000-02-18 */
563
tmp = QString::number(date.year());
564
tmp += "-" + QString().sprintf("%02d", date.month()) + "-";
565
tmp += QString().sprintf("%02d", date.day());
567
else if (fmtType == date_format17) { /*2 february 2000 */
568
tmp = QString().sprintf("%d", date.day());
569
tmp += " " + converter->locale()->calendar()->monthString(date, false) + " ";
570
tmp += QString::number(date.year());
572
else if (fmtType == date_format18) { /*02/18/1999 */
573
tmp = QString().sprintf("%02d", date.month());
574
tmp += "/" + QString().sprintf("%02d", date.day());
575
tmp += "/" + QString::number(date.year());
577
else if (fmtType == date_format19) { /*02/18/99 */
578
tmp = QString().sprintf("%02d", date.month());
579
tmp += "/" + QString().sprintf("%02d", date.day());
580
tmp += "/" + QString::number(date.year()).right(2);
582
else if (fmtType == date_format20) { /*Feb/18/99 */
583
tmp = converter->locale()->calendar()->monthString(date, true);
584
tmp += "/" + QString().sprintf("%02d", date.day());
585
tmp += "/" + QString::number(date.year()).right(2);
587
else if (fmtType == date_format21) { /*Feb/18/1999 */
588
tmp = converter->locale()->calendar()->monthString(date, true);
589
tmp += "/" + QString().sprintf("%02d", date.day());
590
tmp += "/" + QString::number(date.year());
592
else if (fmtType == date_format22) { /*Feb-1999 */
593
tmp = converter->locale()->calendar()->monthString(date, true) + "-";
594
tmp += QString::number(date.year());
596
else if (fmtType == date_format23) { /*1999 */
597
tmp = QString::number(date.year());
599
else if (fmtType == date_format24) { /*99 */
600
tmp = QString::number(date.year()).right(2);
602
else if (fmtType == date_format25) { /*2000/02/18 */
603
tmp = QString::number(date.year());
604
tmp += "/" + QString().sprintf("%02d", date.month());
605
tmp += "/" + QString().sprintf("%02d", date.day());
607
else if (fmtType == date_format26) { /*2000/Feb/18 */
608
tmp = QString::number(date.year());
609
tmp += "/" + converter->locale()->calendar()->monthString(date, true);
610
tmp += "/" + QString().sprintf("%02d", date.day());
613
tmp = converter->locale()->formatDate(date, true);
615
// Missing compared with gnumeric:
616
// "m/d/yy h:mm", /* 20 */
617
// "m/d/yyyy h:mm", /* 21 */
618
// "mmm/ddd/yy", /* 12 */
619
// "mmm/ddd/yyyy", /* 13 */
620
// "mm/ddd/yy", /* 14 */
621
// "mm/ddd/yyyy", /* 15 */
626
QString ValueFormatter::errorFormat (KSpreadCell *cell)
629
if (cell->testFlag (KSpreadCell::Flag_ParseError))
630
err = "#" + i18n ("Parse") + "!";
631
else if ( cell->testFlag (KSpreadCell::Flag_CircularCalculation))
632
err = "#" + i18n ("Circle") + "!";
633
else if ( cell->testFlag (KSpreadCell::Flag_DependancyError))
634
err = "#" + i18n ("Depend") + "!";
638
kdDebug(36001) << "Unhandled error type." << endl;