00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
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
00038
00039
00040
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
00072 void RegisterTextFunctions()
00073 {
00074 FunctionRepository* repo = FunctionRepository::self();
00075 Function *f;
00076
00077
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
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
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
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
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
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
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
00238 Value func_dollar (valVector args, ValueCalc *calc, FuncExtra *)
00239 {
00240
00241
00242
00243
00244
00245
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
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
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
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
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
00292 Value func_fixed (valVector args, ValueCalc *calc, FuncExtra *)
00293 {
00294
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
00309
00310
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
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
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
00354 Value func_lower (valVector args, ValueCalc *calc, FuncExtra *)
00355 {
00356 return Value (calc->conv()->asString (args[0]).asString().lower());
00357 }
00358
00359
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
00369 pos--;
00370
00371 return Value (str.mid (pos, len));
00372 }
00373
00374
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
00406 Value func_regexp (valVector args, ValueCalc *calc, FuncExtra *)
00407 {
00408
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)
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
00435 Value func_regexpre (valVector args, ValueCalc *calc, FuncExtra *)
00436 {
00437
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
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
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
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
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
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
00519 if (start_num <= 0) return Value::errorVALUE();
00520 if (start_num > (int)within_text.length()) return Value::errorVALUE();
00521
00522
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
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
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
00575 p = result.find (old_text, p + new_text.length());
00576 if( !all ) num--;
00577 }
00578
00579 return Value (result);
00580 }
00581
00582
00583 Value func_t (valVector args, ValueCalc *calc, FuncExtra *)
00584 {
00585 return calc->conv()->asString (args[0]);
00586 }
00587
00588
00589 Value func_text (valVector args, ValueCalc *calc, FuncExtra *)
00590 {
00591
00592
00593 return calc->conv()->asString (args[0]);
00594 }
00595
00596
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)
00610 str[i] = c.upper();
00611 else if (c == uc)
00612 str[i] = c.lower();
00613 }
00614
00615 return Value (str);
00616 }
00617
00618
00619 Value func_trim (valVector args, ValueCalc *calc, FuncExtra *)
00620 {
00621 return Value (
00622 calc->conv()->asString (args[0]).asString().simplifyWhiteSpace());
00623 }
00624
00625
00626 Value func_upper (valVector args, ValueCalc *calc, FuncExtra *)
00627 {
00628 return Value (calc->conv()->asString (args[0]).asString().upper());
00629 }
00630
00631
00632 Value func_value (valVector args, ValueCalc *calc, FuncExtra *)
00633 {
00634
00635 return calc->conv()->asFloat (args[0]);
00636 }