kspread Library API Documentation

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