kspread

kspread_functions_text.cc

00001 /* This file is part of the KDE project
00002    Copyright (C) 1998-2002 The KSpread Team
00003                            www.koffice.org/kspread
00004    Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
00005 
00006    This library is free software; you can redistribute it and/or
00007    modify it under the terms of the GNU Library General Public
00008    License as published by the Free Software Foundation; either
00009    version 2 of the License.
00010 
00011    This library is distributed in the hope that it will be useful,
00012    but WITHOUT ANY WARRANTY; without even the implied warranty of
00013    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00014    Library General Public License for more details.
00015 
00016    You should have received a copy of the GNU Library General Public License
00017    along with this library; see the file COPYING.LIB.  If not, write to
00018    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00019  * Boston, MA 02110-1301, USA.
00020 */
00021 
00022 
00023 // built-in text functions
00024 // please keep it in alphabetical order
00025 
00026 #include <qregexp.h>
00027 #include <kdebug.h>
00028 #include <klocale.h>
00029 #include <math.h>
00030 
00031 #include "functions.h"
00032 #include "valuecalc.h"
00033 #include "valueconverter.h"
00034 
00035 using namespace KSpread;
00036 
00037 // Functions DOLLAR and FIXED convert data to double, hence they will not
00038 // support arbitrary precision, when it will be introduced.
00039 
00040 // prototypes
00041 Value func_char (valVector args, ValueCalc *calc, FuncExtra *);
00042 Value func_clean (valVector args, ValueCalc *calc, FuncExtra *);
00043 Value func_code (valVector args, ValueCalc *calc, FuncExtra *);
00044 Value func_compare (valVector args, ValueCalc *calc, FuncExtra *);
00045 Value func_concatenate (valVector args, ValueCalc *calc, FuncExtra *);
00046 Value func_dollar (valVector args, ValueCalc *calc, FuncExtra *);
00047 Value func_exact (valVector args, ValueCalc *calc, FuncExtra *);
00048 Value func_find (valVector args, ValueCalc *calc, FuncExtra *);
00049 Value func_fixed (valVector args, ValueCalc *calc, FuncExtra *);
00050 Value func_left (valVector args, ValueCalc *calc, FuncExtra *);
00051 Value func_len (valVector args, ValueCalc *calc, FuncExtra *);
00052 Value func_lower (valVector args, ValueCalc *calc, FuncExtra *);
00053 Value func_mid (valVector args, ValueCalc *calc, FuncExtra *);
00054 Value func_proper (valVector args, ValueCalc *calc, FuncExtra *);
00055 Value func_regexp (valVector args, ValueCalc *calc, FuncExtra *);
00056 Value func_regexpre (valVector args, ValueCalc *calc, FuncExtra *);
00057 Value func_replace (valVector args, ValueCalc *calc, FuncExtra *);
00058 Value func_rept (valVector args, ValueCalc *calc, FuncExtra *);
00059 Value func_rot (valVector args, ValueCalc *calc, FuncExtra *);
00060 Value func_right (valVector args, ValueCalc *calc, FuncExtra *);
00061 Value func_search (valVector args, ValueCalc *calc, FuncExtra *);
00062 Value func_sleek (valVector args, ValueCalc *calc, FuncExtra *);
00063 Value func_substitute (valVector args, ValueCalc *calc, FuncExtra *);
00064 Value func_t (valVector args, ValueCalc *calc, FuncExtra *);
00065 Value func_text (valVector args, ValueCalc *calc, FuncExtra *);
00066 Value func_toggle (valVector args, ValueCalc *calc, FuncExtra *);
00067 Value func_trim (valVector args, ValueCalc *calc, FuncExtra *);
00068 Value func_upper (valVector args, ValueCalc *calc, FuncExtra *);
00069 Value func_value (valVector args, ValueCalc *calc, FuncExtra *);
00070 
00071 // registers all text functions
00072 void RegisterTextFunctions()
00073 {
00074   FunctionRepository* repo = FunctionRepository::self();
00075   Function *f;
00076 
00077   // one-parameter functions
00078   f = new Function ("CHAR", func_char);
00079   repo->add (f);
00080   f = new Function ("CLEAN", func_clean);
00081   repo->add (f);
00082   f = new Function ("CODE", func_code);
00083   repo->add (f);
00084   f = new Function ("LEN", func_len);
00085   repo->add (f);
00086   f = new Function ("LOWER", func_lower);
00087   repo->add (f);
00088   f = new Function ("PROPER", func_proper);
00089   repo->add (f);
00090   f = new Function ("ROT", func_rot);
00091   repo->add (f);
00092   f = new Function ("SLEEK", func_sleek);
00093   repo->add (f);
00094   f = new Function ("T", func_t);
00095   repo->add (f);
00096   f = new Function ("TOGGLE", func_toggle);
00097   repo->add (f);
00098   f = new Function ("TRIM", func_trim);
00099   repo->add (f);
00100   f = new Function ("UPPER", func_upper);
00101   repo->add (f);
00102   f = new Function ("VALUE", func_value);
00103   repo->add (f);
00104   
00105   // other functions
00106   f = new Function ("COMPARE", func_compare);
00107   f->setParamCount (3);
00108   repo->add (f);
00109   f = new Function ("CONCATENATE", func_concatenate);
00110   f->setParamCount (1, -1);
00111   f->setAcceptArray ();
00112   repo->add (f);
00113   f = new Function ("DOLLAR", func_dollar);
00114   f->setParamCount (1, 2);
00115   repo->add (f);
00116   f = new Function ("EXACT", func_exact);
00117   f->setParamCount (2);
00118   repo->add (f);
00119   f = new Function ("FIND", func_find);
00120   f->setParamCount (2, 3);
00121   repo->add (f);
00122   f = new Function ("FIXED", func_fixed);
00123   f->setParamCount (1, 3);
00124   repo->add (f);
00125   f = new Function ("LEFT", func_left);
00126   f->setParamCount (2);
00127   repo->add (f);
00128   f = new Function ("MID", func_mid);
00129   f->setParamCount (2, 3);
00130   repo->add (f);
00131   f = new Function ("REGEXP", func_regexp);
00132   f->setParamCount (2, 4);
00133   repo->add (f);
00134   f = new Function ("REGEXPRE", func_regexpre);
00135   f->setParamCount (3);
00136   repo->add (f);
00137   f = new Function ("REPLACE", func_replace);
00138   f->setParamCount (4);
00139   repo->add (f);
00140   f = new Function ("REPT", func_rept);
00141   f->setParamCount (2);
00142   repo->add (f);
00143   f = new Function ("RIGHT", func_right);
00144   f->setParamCount (2);
00145   repo->add (f);
00146   f = new Function ("SEARCH", func_search);
00147   f->setParamCount (2, 3);
00148   repo->add (f);
00149   f = new Function ("SUBSTITUTE", func_substitute);
00150   f->setParamCount (3, 4);
00151   repo->add (f);
00152   f = new Function ("TEXT", func_text);
00153   f->setParamCount (1, 2);
00154   repo->add (f);
00155 }
00156 
00157 
00158 // Function: CHAR
00159 Value func_char (valVector args, ValueCalc *calc, FuncExtra *)
00160 {
00161   int val = calc->conv()->asInteger (args[0]).asInteger ();
00162   return Value (QString (QChar (val)));
00163 }
00164 
00165 // Function: CLEAN
00166 Value func_clean (valVector args, ValueCalc *calc, FuncExtra *)
00167 {
00168   QString str (calc->conv()->asString (args[0]).asString());
00169   QString result;
00170   QChar   c;
00171   int     i;
00172   int     l = str.length();
00173   
00174   for (i = 0; i < l; ++i)
00175   {
00176     c = str[i];
00177     if (c.isPrint())
00178       result += c;
00179   }
00180   
00181   return Value (result);
00182 }
00183 
00184 // Function: CODE
00185 Value func_code (valVector args, ValueCalc *calc, FuncExtra *)
00186 {
00187   QString str (calc->conv()->asString (args[0]).asString());
00188   if (str.length() <= 0)
00189      return Value::errorVALUE();
00190 
00191   return Value (str[0].unicode());
00192 }
00193 
00194 // Function: COMPARE
00195 Value func_compare (valVector args, ValueCalc *calc, FuncExtra *)
00196 {
00197   int  result = 0;
00198   bool exact = calc->conv()->asBoolean (args[2]).asBoolean();
00199 
00200   QString s1 = calc->conv()->asString (args[0]).asString();
00201   QString s2 = calc->conv()->asString (args[1]).asString();
00202 
00203   if (!exact)
00204     result = s1.lower().localeAwareCompare(s2.lower());
00205   else
00206     result = s1.localeAwareCompare(s2);
00207 
00208   if (result < 0)
00209     result = -1;
00210   else if (result > 0)
00211     result = 1;
00212 
00213   return Value (result);
00214 }
00215 
00216 void func_concatenate_helper (Value val, ValueCalc *calc,
00217     QString& tmp)
00218 {
00219   if (val.isArray()) {
00220     for (unsigned int row = 0; row < val.rows(); ++row)
00221       for (unsigned int col = 0; col < val.columns(); ++col)
00222         func_concatenate_helper (val.element (col, row), calc, tmp);
00223   } else
00224     tmp += calc->conv()->asString (val).asString();
00225 }
00226 
00227 // Function: CONCATENATE
00228 Value func_concatenate (valVector args, ValueCalc *calc, FuncExtra *)
00229 {
00230   QString tmp;
00231   for (unsigned int i = 0; i < args.count(); ++i)
00232     func_concatenate_helper (args[i], calc, tmp);
00233   
00234   return Value (tmp);
00235 }
00236 
00237 // Function: DOLLAR
00238 Value func_dollar (valVector args, ValueCalc *calc, FuncExtra *)
00239 {
00240   // ValueConverter doesn't support money directly, hence we need to
00241   // use the locale. This code has the same effect as the output
00242   // of ValueFormatter for money format.
00243   
00244   // This function converts data to double/int, hence it won't support
00245   // larger precision.
00246   
00247   double value = calc->conv()->asFloat (args[0]).asFloat();
00248   int precision = 2;
00249   if (args.count() == 2)
00250     precision = calc->conv()->asInteger (args[1]).asInteger();
00251 
00252   // do round, because formatMoney doesn't
00253   value = floor (value * pow (10.0, precision) + 0.5) / pow (10.0, precision);
00254   
00255   KLocale *locale = calc->conv()->locale();
00256   QString s = locale->formatMoney (value, locale->currencySymbol(), precision);
00257   
00258   return Value (s);
00259 }
00260 
00261 // Function: EXACT
00262 Value func_exact (valVector args, ValueCalc *calc, FuncExtra *)
00263 {
00264   QString s1 = calc->conv()->asString (args[0]).asString();
00265   QString s2 = calc->conv()->asString (args[1]).asString();
00266   bool exact = (s1 == s2);
00267   return Value (exact);
00268 }
00269 
00270 // Function: FIND
00271 Value func_find (valVector args, ValueCalc *calc, FuncExtra *)
00272 {
00273   QString find_text, within_text;
00274   int start_num = 1;
00275 
00276   find_text = calc->conv()->asString (args[0]).asString();
00277   within_text = calc->conv()->asString (args[1]).asString();
00278   if (args.count() == 3)
00279     start_num = calc->conv()->asInteger (args[2]).asInteger();
00280 
00281   // conforms to Excel behaviour
00282   if (start_num <= 0) return Value::errorVALUE();
00283   if (start_num > (int)within_text.length()) return Value::errorVALUE();
00284 
00285   int pos = within_text.find (find_text, start_num - 1);
00286   if( pos < 0 ) return Value::errorNA();
00287 
00288   return Value (pos + 1);
00289 }
00290 
00291 // Function: FIXED
00292 Value func_fixed (valVector args, ValueCalc *calc, FuncExtra *)
00293 {
00294   // uses double, hence won't support big precision
00295 
00296   int decimals = 2;
00297   bool no_commas = false;
00298 
00299   double number = calc->conv()->asFloat (args[0]).asFloat();
00300   if (args.count() > 1)
00301     decimals = calc->conv()->asInteger (args[1]).asInteger();
00302   if (args.count() == 3)
00303     no_commas = calc->conv()->asBoolean (args[2]).asBoolean();
00304 
00305   QString result;
00306   KLocale *locale = calc->conv()->locale();
00307 
00308   // unfortunately, we can't just use KLocale::formatNumber because
00309   // * if decimals < 0, number is rounded
00310   // * if no_commas is true, thousand separators shouldn't show up
00311 
00312   if( decimals < 0 )
00313   {
00314     decimals = -decimals;
00315     number = floor( number/pow(10.0,decimals)+0.5 ) * pow(10.0,decimals);
00316     decimals = 0;
00317   }
00318 
00319   bool neg = number < 0;
00320   result = QString::number( neg ? -number:number, 'f', decimals );
00321 
00322   int pos = result.find('.');
00323   if (pos == -1) pos = result.length();
00324     else result.replace(pos, 1, locale->decimalSymbol());
00325   if( !no_commas )
00326     while (0 < (pos -= 3))
00327       result.insert(pos, locale->thousandsSeparator());
00328 
00329   result.prepend( neg ? locale->negativeSign():
00330     locale->positiveSign() );
00331 
00332   return Value (result);
00333 }
00334 
00335 // Function: LEFT
00336 Value func_left (valVector args, ValueCalc *calc, FuncExtra *)
00337 {
00338   QString str = calc->conv()->asString (args[0]).asString();
00339   int nb = 1;
00340   if (args.count() == 2)
00341     nb = calc->conv()->asInteger (args[1]).asInteger();
00342   
00343   return Value (str.left (nb));
00344 }
00345 
00346 // Function: LEN
00347 Value func_len (valVector args, ValueCalc *calc, FuncExtra *)
00348 {
00349   int nb = calc->conv()->asString (args[0]).asString().length();
00350   return Value (nb);
00351 }
00352 
00353 // Function: LOWER
00354 Value func_lower (valVector args, ValueCalc *calc, FuncExtra *)
00355 {
00356   return Value (calc->conv()->asString (args[0]).asString().lower());
00357 }
00358 
00359 // Function: MID
00360 Value func_mid (valVector args, ValueCalc *calc, FuncExtra *)
00361 {
00362   QString str = calc->conv()->asString (args[0]).asString();
00363   int pos = calc->conv()->asInteger (args[1]).asInteger();
00364   uint len = 0xffffffff;
00365   if (args.count() == 3)
00366     len = (uint) calc->conv()->asInteger (args[2]).asInteger();
00367   
00368   // Excel compatible
00369   pos--;
00370 
00371   return Value (str.mid (pos, len));
00372 }
00373 
00374 // Function: PROPER
00375 Value func_proper (valVector args, ValueCalc *calc, FuncExtra *)
00376 {
00377   QString str = calc->conv()->asString (args[0]).asString().lower();
00378     
00379   QChar f;
00380   bool  first = true;
00381 
00382   for (unsigned int i = 0; i < str.length(); ++i)
00383   {
00384     if (first)
00385     {
00386       f = str[i];
00387       if (f.isNumber())
00388         continue;
00389 
00390       f = f.upper();
00391 
00392       str[i] = f;
00393       first = false;
00394 
00395       continue;
00396     }
00397 
00398     if (str[i] == ' ' || str[i] == '-')
00399       first = true;
00400   }
00401 
00402   return Value (str);
00403 }
00404 
00405 // Function: REGEXP
00406 Value func_regexp (valVector args, ValueCalc *calc, FuncExtra *)
00407 {
00408   // ensure that we got a valid regular expression
00409   QRegExp exp (calc->conv()->asString (args[1]).asString());
00410   if (!exp.isValid ())
00411     return Value::errorVALUE();
00412   
00413   QString s = calc->conv()->asString (args[0]).asString();
00414   QString defText;
00415   if (args.count() > 2)
00416     defText = calc->conv()->asString (args[2]).asString();
00417   int bkref = 0;
00418   if (args.count() == 4)
00419     bkref = calc->conv()->asInteger (args[3]).asInteger();
00420   if (bkref < 0)   // strange back-reference
00421     return Value::errorVALUE();
00422 
00423   QString returnValue;
00424   
00425   int pos = exp.search (s);
00426   if (pos == -1)
00427     returnValue = defText;
00428   else
00429     returnValue = exp.cap (bkref);
00430 
00431   return Value (returnValue);
00432 }
00433 
00434 // Function: REGEXPRE
00435 Value func_regexpre (valVector args, ValueCalc *calc, FuncExtra *)
00436 {
00437   // ensure that we got a valid regular expression
00438   QRegExp exp (calc->conv()->asString (args[1]).asString());
00439   if (!exp.isValid ())
00440     return Value::errorVALUE();
00441   
00442   QString s = calc->conv()->asString (args[0]).asString();
00443   QString str = calc->conv()->asString (args[2]).asString();
00444 
00445   int pos = 0;
00446   while ((pos = exp.search (s, pos)) != -1)
00447   {
00448     int i = exp.matchedLength();
00449     s = s.replace (pos, i, str);
00450     pos += str.length();
00451   }
00452 
00453   return Value (s);
00454 }
00455 
00456 // Function: REPLACE
00457 Value func_replace (valVector args, ValueCalc *calc, FuncExtra *)
00458 {
00459   QString text = calc->conv()->asString (args[0]).asString();
00460   int pos = calc->conv()->asInteger (args[1]).asInteger();
00461   int len = calc->conv()->asInteger (args[2]).asInteger();
00462   QString new_text = calc->conv()->asString (args[3]).asString();
00463   
00464   if (pos < 0) pos = 0;
00465   
00466   QString result = text.replace (pos-1, len, new_text);
00467   return Value (result);
00468 }
00469 
00470 // Function: REPT
00471 Value func_rept (valVector args, ValueCalc *calc, FuncExtra *)
00472 {
00473   QString s = calc->conv()->asString (args[0]).asString();
00474   int nb = calc->conv()->asInteger (args[1]).asInteger();
00475   
00476   QString result;
00477   for (int i = 0; i < nb; i++) result += s;
00478   return Value (result);
00479 }
00480 
00481 // Function: RIGHT
00482 Value func_right (valVector args, ValueCalc *calc, FuncExtra *)
00483 {
00484   QString str = calc->conv()->asString (args[0]).asString();
00485   int nb = 1;
00486   if (args.count() == 2)
00487     nb = calc->conv()->asInteger (args[1]).asInteger();
00488   
00489   return Value (str.right (nb));
00490 }
00491 
00492 // Function: ROT
00493 Value func_rot (valVector args, ValueCalc *calc, FuncExtra *)
00494 {
00495   QString text = calc->conv()->asString (args[0]).asString();
00496 
00497   for( unsigned i=0; i<text.length(); i++ )
00498   {
00499     unsigned c = text[i].upper().unicode();
00500     if( ( c >= 'A' ) && ( c <= 'M' ) )
00501       text[i] = QChar( text[i].unicode() + 13);
00502     if( ( c >= 'N' ) && ( c <= 'Z' ) )
00503       text[i] = QChar( text[i].unicode() - 13);
00504   }
00505 
00506   return Value (text);
00507 }
00508 
00509 // Function: SEARCH
00510 Value func_search (valVector args, ValueCalc *calc, FuncExtra *)
00511 {
00512   QString find_text = calc->conv()->asString (args[0]).asString();
00513   QString within_text = calc->conv()->asString (args[1]).asString();
00514   int start_num = 1;
00515   if (args.count() == 3)
00516     start_num = calc->conv()->asInteger (args[2]).asInteger();
00517 
00518   // conforms to Excel behaviour
00519   if (start_num <= 0) return Value::errorVALUE();
00520   if (start_num > (int)within_text.length()) return Value::errorVALUE();
00521 
00522   // use globbing feature of QRegExp
00523   QRegExp regex( find_text, false, true );
00524   int pos = within_text.find( regex, start_num-1 );
00525   if( pos < 0 ) return Value::errorNA();
00526 
00527   return Value (pos + 1);
00528 }
00529 
00530 // Function: SLEEK
00531 Value func_sleek (valVector args, ValueCalc *calc, FuncExtra *)
00532 {
00533   QString str = calc->conv()->asString (args[0]).asString();
00534   QString result;
00535   QChar   c;
00536   int     i;
00537   int     l = str.length();
00538 
00539   for (i = 0; i < l; ++i)
00540   {
00541     c = str[i];
00542     if (!c.isSpace())
00543       result += c;
00544   }
00545 
00546   return Value (result);
00547 }
00548 
00549 // Function: SUBSTITUTE
00550 Value func_substitute (valVector args, ValueCalc *calc, FuncExtra *)
00551 {
00552   int num = 1;
00553   bool all = true;
00554 
00555   if (args.count() == 4)
00556   {
00557     num = calc->conv()->asInteger (args[3]).asInteger();
00558     all = false;
00559   }
00560   
00561   QString text = calc->conv()->asString (args[0]).asString();
00562   QString old_text = calc->conv()->asString (args[1]).asString();
00563   QString new_text = calc->conv()->asString (args[2]).asString();
00564 
00565   if( num <= 0 ) return Value::errorVALUE();
00566   if (old_text.length() == 0) return Value (text);
00567 
00568   QString result = text;
00569 
00570   int p = result.find (old_text);
00571   while ((p != -1) && (num > 0))
00572   {
00573     result.replace( p, old_text.length(), new_text );
00574     // find another location, starting straight after the replaced text
00575     p = result.find (old_text, p + new_text.length());
00576     if( !all ) num--;
00577   }
00578 
00579   return Value (result);
00580 }
00581 
00582 // Function: T
00583 Value func_t (valVector args, ValueCalc *calc, FuncExtra *)
00584 {
00585   return calc->conv()->asString (args[0]);
00586 }
00587 
00588 // Function: TEXT
00589 Value func_text (valVector args, ValueCalc *calc, FuncExtra *)
00590 {
00591   //Currently the same as the T function ...
00592   //Second parameter is format_text. It is currently ignored.
00593   return calc->conv()->asString (args[0]);
00594 }
00595 
00596 // Function: TOGGLE
00597 Value func_toggle (valVector args, ValueCalc *calc, FuncExtra *)
00598 {
00599   QString str = calc->conv()->asString (args[0]).asString();
00600   int i;
00601   int l = str.length();
00602 
00603   for (i = 0; i < l; ++i)
00604   {
00605     QChar c = str[i];
00606     QChar lc = c.lower();
00607     QChar uc = c.upper();
00608 
00609     if (c == lc) // it is in lowercase
00610       str[i] = c.upper();
00611     else if (c == uc) // it is in uppercase
00612       str[i] = c.lower();
00613   }
00614 
00615   return Value (str);
00616 }
00617 
00618 // Function: TRIM
00619 Value func_trim (valVector args, ValueCalc *calc, FuncExtra *)
00620 {
00621   return Value (
00622       calc->conv()->asString (args[0]).asString().simplifyWhiteSpace());
00623 }
00624 
00625 // Function: UPPER
00626 Value func_upper (valVector args, ValueCalc *calc, FuncExtra *)
00627 {
00628   return Value (calc->conv()->asString (args[0]).asString().upper());
00629 }
00630 
00631 // Function: VALUE
00632 Value func_value (valVector args, ValueCalc *calc, FuncExtra *)
00633 {
00634   // same as the N function
00635   return calc->conv()->asFloat (args[0]);
00636 }
KDE Home | KDE Accessibility Home | Description of Access Keys