kspread

valueformatter.cc

00001 /* This file is part of the KDE project
00002    Copyright 2004 Tomas Mecir <mecirt@gmail.com>
00003    Copyright (C) 1998-2004 KSpread Team <koffice-devel@kde.org>
00004 
00005    This library is free software; you can redistribute it and/or
00006    modify it under the terms of the GNU Library General Public
00007    License as published by the Free Software Foundation; either
00008    version 2 of the License, or (at your option) any later version.
00009 
00010    This library is distributed in the hope that it will be useful,
00011    but WITHOUT ANY WARRANTY; without even the implied warranty of
00012    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00013    Library General Public License for more details.
00014 
00015    You should have received a copy of the GNU Library General Public License
00016    along with this library; see the file COPYING.LIB.  If not, write to
00017    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00018  * Boston, MA 02110-1301, USA.
00019 */
00020 
00021 #include "valueformatter.h"
00022 
00023 #include "kspread_cell.h"
00024 #include "kspread_locale.h"
00025 #include "kspread_util.h"
00026 #include "valueconverter.h"
00027 
00028 #include <kcalendarsystem.h>
00029 #include <kdebug.h>
00030 #include <klocale.h>
00031 
00032 #include <float.h>
00033 #include <math.h>
00034 
00035 using namespace KSpread;
00036 
00037 ValueFormatter::ValueFormatter (ValueConverter *conv) : converter( conv )
00038 {
00039 }
00040 
00041 QString ValueFormatter::formatText (Cell *cell, FormatType fmtType)
00042 {
00043   if (cell->hasError ())
00044     return errorFormat (cell);
00045 
00046   QString str;
00047 
00048   Format::FloatFormat floatFormat =
00049       cell->format()->floatFormat (cell->column(), cell->row());
00050   int precision = cell->format()->precision (cell->column(), cell->row());
00051   QString prefix = cell->format()->prefix (cell->column(), cell->row());
00052   QString postfix = cell->format()->postfix (cell->column(), cell->row());
00053   Format::Currency currency;
00054   bool valid = cell->format()->currencyInfo(currency);
00055   QString currencySymbol = valid ? currency.symbol : QString::null;
00056 
00057   return formatText (cell->value(), fmtType, precision,
00058       floatFormat, prefix, postfix, currencySymbol);
00059 }
00060 
00061 QString ValueFormatter::formatText (const Value &value,
00062     FormatType fmtType, int precision, Format::FloatFormat floatFormat,
00063     const QString &prefix, const QString &postfix, const QString &currencySymbol)
00064 {
00065   //if we have an array, use its first element
00066   if (value.isArray())
00067     return formatText (value.element (0, 0), fmtType, precision,
00068         floatFormat, prefix, postfix, currencySymbol);
00069 
00070   QString str;
00071 
00072   //step 1: determine formatting that will be used
00073   fmtType = determineFormatting (value, fmtType);
00074 
00075   //step 2: format the value !
00076 
00077   //text
00078   if (fmtType == Text_format)
00079   {
00080     str = converter->asString (value).asString();
00081     if (!str.isEmpty() && str[0]=='\'' )
00082       str = str.mid(1);
00083   }
00084 
00085   //date
00086   else if (formatIsDate (fmtType))
00087     str = dateFormat (value.asDate(), fmtType);
00088 
00089   //time
00090   else if (formatIsTime (fmtType))
00091     str = timeFormat (value.asDateTime(), fmtType);
00092 
00093   //fraction
00094   else if (formatIsFraction (fmtType))
00095     str = fractionFormat (value.asFloat(), fmtType);
00096 
00097   //another
00098   else
00099   {
00100     //some cell parameters ...
00101     double v = converter->asFloat (value).asFloat();
00102 
00103     // Always unsigned ?
00104     if ((floatFormat == Format::AlwaysUnsigned) && (v < 0.0))
00105       v *= -1.0;
00106 
00107     // Make a string out of it.
00108     str = createNumberFormat (v, precision, fmtType,
00109         (floatFormat == Format::AlwaysSigned), currencySymbol);
00110 
00111     // Remove trailing zeros and the decimal point if necessary
00112     // unless the number has no decimal point
00113     if (precision == -1)
00114     {
00115       QChar decimal_point = converter->locale()->decimalSymbol()[0];
00116       if ( decimal_point.isNull() )
00117         decimal_point = '.';
00118 
00119       removeTrailingZeros (str, decimal_point);
00120     }
00121   }
00122 
00123   if (!prefix.isEmpty())
00124     str = prefix + ' ' + str;
00125 
00126   if( !postfix.isEmpty())
00127     str += ' ' + postfix;
00128 
00129   //kdDebug() << "ValueFormatter says: " << str << endl;
00130   return str;
00131 }
00132 
00133 FormatType ValueFormatter::determineFormatting (const Value &value,
00134     FormatType fmtType)
00135 {
00136   //if the cell value is a string, then we want to display it as-is,
00137   //no matter what, same if the cell is empty
00138   if (value.isString () || (value.format() == Value::fmt_None))
00139     return Text_format;
00140   //same if we're supposed to display string, no matter what we actually got
00141   if (fmtType == Text_format)
00142     return Text_format;
00143 
00144   //now, everything depends on whether the formatting is Generic or not
00145   if (fmtType == Generic_format)
00146   {
00147     //here we decide based on value's format...
00148     Value::Format fmt = value.format();
00149     switch (fmt) {
00150       case Value::fmt_None:
00151         fmtType = Text_format;
00152       break;
00153       case Value::fmt_Boolean:
00154         fmtType = Text_format;
00155       break;
00156       case Value::fmt_Number:
00157         if (value.asFloat() > 1e+10)
00158           fmtType = Scientific_format;
00159         else
00160           fmtType = Number_format;
00161       break;
00162       case Value::fmt_Percent:
00163         fmtType = Percentage_format;
00164       break;
00165       case Value::fmt_Money:
00166         fmtType = Money_format;
00167       break;
00168       case Value::fmt_DateTime:
00169         fmtType = TextDate_format;
00170       break;
00171       case Value::fmt_Date:
00172         fmtType = ShortDate_format;
00173       break;
00174       case Value::fmt_Time:
00175         fmtType = Time_format;
00176       break;
00177       case Value::fmt_String:
00178         //this should never happen
00179         fmtType = Text_format;
00180       break;
00181     };
00182     return fmtType;
00183   }
00184   else
00185   {
00186     //we'll mostly want to use the given formatting, the only exception
00187     //being Boolean values
00188 
00189     //TODO: is this correct? We may also want to convert bools to 1s and 0s
00190     //if we want to display a number...
00191 
00192     //TODO: what to do about Custom formatting? We don't support it as of now,
00193     //  but we'll have it ... one day, that is ...
00194     if (value.isBoolean())
00195       return Text_format;
00196     else
00197       return fmtType;
00198   }
00199 }
00200 
00201 
00202 void ValueFormatter::removeTrailingZeros (QString &str, QChar decimal_point)
00203 {
00204   if (str.find (decimal_point) < 0)
00205     //no decimal point -> nothing to do
00206     return;
00207 
00208   int start = 0;
00209   int cslen = converter->locale()->currencySymbol().length();
00210   if (str.find ('%') != -1)
00211     start = 2;
00212   else if (str.find (converter->locale()->currencySymbol()) ==
00213       ((int) (str.length() - cslen)))
00214     start = cslen + 1;
00215   else if ((start = str.find ('E')) != -1)
00216     start = str.length() - start;
00217   else
00218     start = 0;
00219 
00220   int i = str.length() - start;
00221   bool bFinished = false;
00222   while ( !bFinished && i > 0 )
00223   {
00224     QChar ch = str[i - 1];
00225     if (ch == '0')
00226       str.remove (--i,1);
00227     else
00228     {
00229       bFinished = true;
00230       if (ch == decimal_point)
00231         str.remove (--i, 1);
00232     }
00233   }
00234 }
00235 
00236 QString ValueFormatter::createNumberFormat ( double value, int precision,
00237     FormatType fmt, bool alwaysSigned, const QString& currencySymbol)
00238 {
00239   // if precision is -1, ask for a huge number of decimals, we'll remove
00240   // the zeros later. Is 8 ok ?
00241   // Stefan: No. Use maximum possible decimal precision (DBL_DIG) instead.
00242   int p = (precision == -1) ? 8 : precision;
00243   QString localizedNumber;
00244   int pos = 0;
00245 
00246   //multiply value by 100 for percentage format
00247   if (fmt == Percentage_format)
00248     value *= 100;
00249 
00250   // this will avoid displaying negative zero, i.e "-0.0000"
00251   if( fabs( value ) < DBL_EPSILON ) value = 0.0;
00252 
00253   // round the number, based on desired precision if not scientific is chosen
00254   //(scientific has relative precision)
00255   if( fmt != Scientific_format )
00256   {
00257     double m[] = { 1, 10, 100, 1e3, 1e4, 1e5, 1e6, 1e7, 1e8, 1e9, 1e10 };
00258     double mm = (p > 10) ? pow(10.0,p) : m[p];
00259     bool neg = value < 0;
00260     value = floor( fabs(value)*mm + 0.5 ) / mm;
00261     if( neg ) value = -value;
00262   }
00263 
00264   QChar decimal_point;
00265   switch (fmt)
00266   {
00267     case Number_format:
00268       localizedNumber = converter->locale()->formatNumber(value, p);
00269       break;
00270     case Percentage_format:
00271       localizedNumber = converter->locale()->formatNumber (value, p)+ " %";
00272       break;
00273     case Money_format:
00274       localizedNumber = converter->locale()->formatMoney (value,
00275         currencySymbol.isEmpty() ? converter->locale()->currencySymbol() : currencySymbol, p );
00276       break;
00277     case Scientific_format:
00278       decimal_point = converter->locale()->decimalSymbol()[0];
00279       localizedNumber = QString::number (value, 'E', p);
00280       if ((pos = localizedNumber.find ('.')) != -1)
00281         localizedNumber = localizedNumber.replace (pos, 1, decimal_point);
00282       break;
00283     default :
00284       //other formatting?
00285       // This happens with Custom_format...
00286       kdDebug(36001)<<"Wrong usage of ValueFormatter::createNumberFormat fmt=" << fmt << "\n";
00287       break;
00288   }
00289 
00290   //prepend positive sign if needed
00291   if (alwaysSigned && value >= 0 )
00292     if (converter->locale()->positiveSign().isEmpty())
00293       localizedNumber='+'+localizedNumber;
00294 
00295   return localizedNumber;
00296 }
00297 
00298 QString ValueFormatter::fractionFormat (double value, FormatType fmtType)
00299 {
00300   double result = value - floor(value);
00301   int index;
00302   int limit = 0;
00303 
00304   /* return w/o fraction part if not necessary */
00305   if (result == 0)
00306     return QString::number(value);
00307 
00308   switch (fmtType) {
00309   case fraction_half:
00310     index = 2;
00311     break;
00312   case fraction_quarter:
00313     index = 4;
00314     break;
00315   case fraction_eighth:
00316     index = 8;
00317     break;
00318   case fraction_sixteenth:
00319     index = 16;
00320     break;
00321   case fraction_tenth:
00322     index = 10;
00323     break;
00324   case fraction_hundredth:
00325     index = 100;
00326     break;
00327   case fraction_one_digit:
00328     index = 3;
00329     limit = 9;
00330     break;
00331   case fraction_two_digits:
00332     index = 4;
00333     limit = 99;
00334     break;
00335   case fraction_three_digits:
00336     index = 5;
00337     limit = 999;
00338     break;
00339   default:
00340     kdDebug(36001) << "Error in Fraction format\n";
00341     return QString::number(value);
00342     break;
00343   } /* switch */
00344 
00345 
00346   /* handle halves, quarters, tenths, ... */
00347 
00348   if (fmtType != fraction_three_digits
00349     && fmtType != fraction_two_digits
00350     && fmtType != fraction_one_digit) {
00351     double calc = 0;
00352     int index1 = 0;
00353     double diff = result;
00354     for (int i = 1; i <= index; i++) {
00355       calc = i * 1.0 / index;
00356       if (fabs(result - calc) < diff) {
00357         index1 = i;
00358         diff = fabs(result - calc);
00359       }
00360     }
00361     if( index1 == 0 ) return QString("%1").arg( floor(value) );
00362     if( index1 == index ) return QString("%1").arg( floor(value)+1 );
00363     if( floor(value) == 0)
00364       return QString("%1/%2").arg( index1 ).arg( index );
00365 
00366     return QString("%1 %2/%3")
00367         .arg( floor(value) )
00368         .arg( index1 )
00369         .arg( index );
00370   }
00371 
00372 
00373   /* handle fraction_one_digit, fraction_two_digit
00374     * and fraction_three_digit style */
00375 
00376   double precision, denominator, numerator;
00377 
00378   do {
00379     double val1 = result;
00380     double val2 = rint(result);
00381     double inter2 = 1;
00382     double inter4, p,  q;
00383     inter4 = p = q = 0;
00384 
00385     precision = pow(10.0, -index);
00386     numerator = val2;
00387     denominator = 1;
00388 
00389     while (fabs(numerator/denominator - result) > precision) {
00390       val1 = (1 / (val1 - val2));
00391       val2 = rint(val1);
00392       p = val2 * numerator + inter2;
00393       q = val2 * denominator + inter4;
00394       inter2 = numerator;
00395       inter4 = denominator;
00396       numerator = p;
00397       denominator = q;
00398     }
00399     index--;
00400   } while (fabs(denominator) > limit);
00401 
00402   denominator = fabs(denominator);
00403   numerator = fabs(numerator);
00404 
00405   if (denominator == numerator)
00406     return QString().setNum(floor(value + 1));
00407   else
00408   {
00409     if ( floor(value) == 0 )
00410       return QString("%1/%2").arg(numerator).arg(denominator);
00411     else
00412       return QString("%1 %2/%3")
00413         .arg(floor(value))
00414         .arg(numerator)
00415         .arg(denominator);
00416   }
00417 }
00418 
00419 QString ValueFormatter::timeFormat (const QDateTime &dt, FormatType fmtType)
00420 {
00421   if (fmtType == Time_format)
00422     return converter->locale()->formatTime(dt.time(), false);
00423 
00424   if (fmtType == SecondeTime_format)
00425     return converter->locale()->formatTime(dt.time(), true);
00426 
00427   int h = dt.time().hour();
00428   int m = dt.time().minute();
00429   int s = dt.time().second();
00430 
00431   QString hour = ( h < 10 ? "0" + QString::number(h) : QString::number(h) );
00432   QString minute = ( m < 10 ? "0" + QString::number(m) : QString::number(m) );
00433   QString second = ( s < 10 ? "0" + QString::number(s) : QString::number(s) );
00434   bool pm = (h > 12);
00435   QString AMPM( pm ? i18n("PM"):i18n("AM") );
00436 
00437   if (fmtType == Time_format1) {  // 9 : 01 AM
00438     return QString("%1:%2 %3")
00439       .arg((pm ? h - 12 : h),2)
00440       .arg(minute,2)
00441       .arg(AMPM);
00442   }
00443 
00444   if (fmtType == Time_format2) {  //9:01:05 AM
00445     return QString("%1:%2:%3 %4")
00446       .arg((pm ? h-12 : h),2)
00447       .arg(minute,2)
00448       .arg(second,2)
00449       .arg(AMPM);
00450   }
00451 
00452   if (fmtType == Time_format3) {
00453     return QString("%1 %2 %3 %4 %5 %6")      // 9 h 01 min 28 s
00454       .arg(hour,2)
00455       .arg(i18n("h"))
00456       .arg(minute,2)
00457       .arg(i18n("min"))
00458       .arg(second,2)
00459       .arg(i18n("s"));
00460   }
00461 
00462   if (fmtType == Time_format4) {  // 9:01
00463     return QString("%1:%2").arg(hour, 2).arg(minute, 2);
00464   }
00465 
00466   if (fmtType == Time_format5) {  // 9:01:12
00467     return QString("%1:%2:%3").arg(hour, 2).arg(minute, 2).arg(second, 2);
00468   }
00469 
00470   QDate d1(dt.date());
00471   QDate d2( 1899, 12, 31 );
00472   int d = d2.daysTo( d1 ) + 1;
00473 
00474   h += d * 24;
00475 
00476   if (fmtType == Time_format6)
00477   {  // [mm]:ss
00478     m += (h * 60);
00479     return QString("%1:%2").arg(m, 1).arg(second, 2);
00480   }
00481   if (fmtType == Time_format7) {  // [h]:mm:ss
00482     return QString("%1:%2:%3").arg(h, 1).arg(minute, 2).arg(second, 2);
00483   }
00484   if (fmtType == Time_format8)
00485   {  // [h]:mm
00486     m += (h * 60);
00487     return QString("%1:%2").arg(h, 1).arg(minute, 2);
00488   }
00489 
00490   return converter->locale()->formatTime( dt.time(), false );
00491 }
00492 
00493 QString ValueFormatter::dateFormat (const QDate &date, FormatType fmtType)
00494 {
00495   QString tmp;
00496   if (fmtType == ShortDate_format) {
00497     tmp = converter->locale()->formatDate(date, true);
00498   }
00499   else if (fmtType == TextDate_format) {
00500     tmp = converter->locale()->formatDate(date, false);
00501   }
00502   else if (fmtType == date_format1) {  /*18-Feb-99 */
00503     tmp = QString().sprintf("%02d", date.day());
00504     tmp += "-" + converter->locale()->calendar()->monthString(date, true) + "-";
00505     tmp += QString::number(date.year()).right(2);
00506   }
00507   else if (fmtType == date_format2) {  /*18-Feb-1999 */
00508     tmp = QString().sprintf("%02d", date.day());
00509     tmp += "-" + converter->locale()->calendar()->monthString(date, true) + "-";
00510     tmp += QString::number(date.year());
00511   }
00512   else if (fmtType == date_format3) {  /*18-Feb */
00513     tmp = QString().sprintf("%02d", date.day());
00514     tmp += "-" + converter->locale()->calendar()->monthString(date, true);
00515   }
00516   else if (fmtType == date_format4) {  /*18-05 */
00517     tmp = QString().sprintf("%02d", date.day());
00518     tmp += "-" + QString().sprintf("%02d", date.month() );
00519   }
00520   else if (fmtType == date_format5) {  /*18/05/00 */
00521     tmp = QString().sprintf("%02d", date.day());
00522     tmp += "/" + QString().sprintf("%02d", date.month()) + "/";
00523     tmp += QString::number(date.year()).right(2);
00524   }
00525   else if (fmtType == date_format6) {  /*18/05/1999 */
00526     tmp = QString().sprintf("%02d", date.day());
00527     tmp += "/" + QString().sprintf("%02d", date.month()) + "/";
00528     tmp += QString::number(date.year());
00529   }
00530   else if (fmtType == date_format7) {  /*Feb-99 */
00531     tmp = converter->locale()->calendar()->monthString(date, true) + "-";
00532     tmp += QString::number(date.year()).right(2);
00533   }
00534   else if (fmtType == date_format8) {  /*February-99 */
00535     tmp = converter->locale()->calendar()->monthString(date, false) + "-";
00536     tmp += QString::number(date.year()).right(2);
00537   }
00538   else if (fmtType == date_format9) {  /*February-1999 */
00539     tmp = converter->locale()->calendar()->monthString(date, false) + "-";
00540     tmp += QString::number(date.year());
00541   }
00542   else if (fmtType == date_format10) {  /*F-99 */
00543     tmp = converter->locale()->calendar()->monthString(date, false).at(0) + "-";
00544     tmp += QString::number(date.year()).right(2);
00545   }
00546   else if (fmtType == date_format11) {  /*18/Feb */
00547     tmp = QString().sprintf("%02d", date.day()) + "/";
00548     tmp += converter->locale()->calendar()->monthString(date, true);
00549   }
00550   else if (fmtType == date_format12) {  /*18/02 */
00551     tmp = QString().sprintf("%02d", date.day()) + "/";
00552     tmp += QString().sprintf("%02d", date.month());
00553   }
00554   else if (fmtType == date_format13) {  /*18/Feb/1999 */
00555     tmp = QString().sprintf("%02d", date.day());
00556     tmp += "/" + converter->locale()->calendar()->monthString(date, true) + "/";
00557     tmp += QString::number(date.year());
00558   }
00559   else if (fmtType == date_format14) {  /*2000/Feb/18 */
00560     tmp = QString::number(date.year());
00561     tmp += "/" + converter->locale()->calendar()->monthString(date, true) + "/";
00562     tmp += QString().sprintf("%02d", date.day());
00563   }
00564   else if (fmtType == date_format15) {  /*2000-Feb-18 */
00565     tmp = QString::number(date.year());
00566     tmp += "-" + converter->locale()->calendar()->monthString(date, true) + "-";
00567     tmp += QString().sprintf("%02d", date.day());
00568   }
00569   else if (fmtType == date_format16) {  /*2000-02-18 */
00570     tmp = QString::number(date.year());
00571     tmp += "-" + QString().sprintf("%02d", date.month()) + "-";
00572     tmp += QString().sprintf("%02d", date.day());
00573   }
00574   else if (fmtType == date_format17) {  /*2 february 2000 */
00575     tmp = QString().sprintf("%d", date.day());
00576     tmp += " " + converter->locale()->calendar()->monthString(date, false) + " ";
00577     tmp += QString::number(date.year());
00578   }
00579   else if (fmtType == date_format18) {  /*02/18/1999 */
00580     tmp = QString().sprintf("%02d", date.month());
00581     tmp += "/" + QString().sprintf("%02d", date.day());
00582     tmp += "/" + QString::number(date.year());
00583   }
00584   else if (fmtType == date_format19) {  /*02/18/99 */
00585     tmp = QString().sprintf("%02d", date.month());
00586     tmp += "/" + QString().sprintf("%02d", date.day());
00587     tmp += "/" + QString::number(date.year()).right(2);
00588   }
00589   else if (fmtType == date_format20) {  /*Feb/18/99 */
00590     tmp = converter->locale()->calendar()->monthString(date, true);
00591     tmp += "/" + QString().sprintf("%02d", date.day());
00592     tmp += "/" + QString::number(date.year()).right(2);
00593   }
00594   else if (fmtType == date_format21) {  /*Feb/18/1999 */
00595     tmp = converter->locale()->calendar()->monthString(date, true);
00596     tmp += "/" + QString().sprintf("%02d", date.day());
00597     tmp += "/" + QString::number(date.year());
00598   }
00599   else if (fmtType == date_format22) {  /*Feb-1999 */
00600     tmp = converter->locale()->calendar()->monthString(date, true) + "-";
00601     tmp += QString::number(date.year());
00602   }
00603   else if (fmtType == date_format23) {  /*1999 */
00604     tmp = QString::number(date.year());
00605   }
00606   else if (fmtType == date_format24) {  /*99 */
00607     tmp = QString::number(date.year()).right(2);
00608   }
00609   else if (fmtType == date_format25) {  /*2000/02/18 */
00610     tmp = QString::number(date.year());
00611     tmp += "/" + QString().sprintf("%02d", date.month());
00612     tmp += "/" + QString().sprintf("%02d", date.day());
00613   }
00614   else if (fmtType == date_format26) {  /*2000/Feb/18 */
00615     tmp = QString::number(date.year());
00616     tmp += "/" + converter->locale()->calendar()->monthString(date, true);
00617     tmp += "/" + QString().sprintf("%02d", date.day());
00618   }
00619   else
00620     tmp = converter->locale()->formatDate(date, true);
00621 
00622   // Missing compared with gnumeric:
00623   //  "m/d/yy h:mm",    /* 20 */
00624   //  "m/d/yyyy h:mm",  /* 21 */
00625   //  "mmm/ddd/yy",    /* 12 */
00626   //  "mmm/ddd/yyyy",    /* 13 */
00627   //  "mm/ddd/yy",    /* 14 */
00628   //  "mm/ddd/yyyy",    /* 15 */
00629 
00630   return tmp;
00631 }
00632 
00633 QString ValueFormatter::errorFormat (Cell *cell)
00634 {
00635   QString err;
00636   if (cell->testFlag (Cell::Flag_ParseError))
00637     err = "#" + i18n ("Parse") + "!";
00638   else if ( cell->testFlag (Cell::Flag_CircularCalculation))
00639     err = "#" + i18n ("Circle") + "!";
00640   else if ( cell->testFlag (Cell::Flag_DependancyError))
00641     err = "#" + i18n ("Depend") + "!";
00642   else
00643   {
00644     err = "####";
00645     kdDebug(36001) << "Unhandled error type." << endl;
00646   }
00647   return err;
00648 }
00649 
KDE Home | KDE Accessibility Home | Description of Access Keys