KOffice – TDE office suite
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

637 lines
18KB

  1. /* This file is part of the KDE project
  2. Copyright (C) 1998-2002 The KSpread Team
  3. www.koffice.org/kspread
  4. Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
  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.
  9. This library is distributed in the hope that it will be useful,
  10. but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  12. Library General Public License for more details.
  13. You should have received a copy of the GNU Library General Public License
  14. along with this library; see the file COPYING.LIB. If not, write to
  15. the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
  16. * Boston, MA 02110-1301, USA.
  17. */
  18. // built-in text functions
  19. // please keep it in alphabetical order
  20. #include <tqregexp.h>
  21. #include <kdebug.h>
  22. #include <tdelocale.h>
  23. #include <math.h>
  24. #include "functions.h"
  25. #include "valuecalc.h"
  26. #include "valueconverter.h"
  27. using namespace KSpread;
  28. // Functions DOLLAR and FIXED convert data to double, hence they will not
  29. // support arbitrary precision, when it will be introduced.
  30. // prototypes
  31. Value func_char (valVector args, ValueCalc *calc, FuncExtra *);
  32. Value func_clean (valVector args, ValueCalc *calc, FuncExtra *);
  33. Value func_code (valVector args, ValueCalc *calc, FuncExtra *);
  34. Value func_compare (valVector args, ValueCalc *calc, FuncExtra *);
  35. Value func_concatenate (valVector args, ValueCalc *calc, FuncExtra *);
  36. Value func_dollar (valVector args, ValueCalc *calc, FuncExtra *);
  37. Value func_exact (valVector args, ValueCalc *calc, FuncExtra *);
  38. Value func_find (valVector args, ValueCalc *calc, FuncExtra *);
  39. Value func_fixed (valVector args, ValueCalc *calc, FuncExtra *);
  40. Value func_left (valVector args, ValueCalc *calc, FuncExtra *);
  41. Value func_len (valVector args, ValueCalc *calc, FuncExtra *);
  42. Value func_lower (valVector args, ValueCalc *calc, FuncExtra *);
  43. Value func_mid (valVector args, ValueCalc *calc, FuncExtra *);
  44. Value func_proper (valVector args, ValueCalc *calc, FuncExtra *);
  45. Value func_regexp (valVector args, ValueCalc *calc, FuncExtra *);
  46. Value func_regexpre (valVector args, ValueCalc *calc, FuncExtra *);
  47. Value func_replace (valVector args, ValueCalc *calc, FuncExtra *);
  48. Value func_rept (valVector args, ValueCalc *calc, FuncExtra *);
  49. Value func_rot (valVector args, ValueCalc *calc, FuncExtra *);
  50. Value func_right (valVector args, ValueCalc *calc, FuncExtra *);
  51. Value func_search (valVector args, ValueCalc *calc, FuncExtra *);
  52. Value func_sleek (valVector args, ValueCalc *calc, FuncExtra *);
  53. Value func_substitute (valVector args, ValueCalc *calc, FuncExtra *);
  54. Value func_t (valVector args, ValueCalc *calc, FuncExtra *);
  55. Value func_text (valVector args, ValueCalc *calc, FuncExtra *);
  56. Value func_toggle (valVector args, ValueCalc *calc, FuncExtra *);
  57. Value func_trim (valVector args, ValueCalc *calc, FuncExtra *);
  58. Value func_upper (valVector args, ValueCalc *calc, FuncExtra *);
  59. Value func_value (valVector args, ValueCalc *calc, FuncExtra *);
  60. // registers all text functions
  61. void RegisterTextFunctions()
  62. {
  63. FunctionRepository* repo = FunctionRepository::self();
  64. Function *f;
  65. // one-parameter functions
  66. f = new Function ("CHAR", func_char);
  67. repo->add (f);
  68. f = new Function ("CLEAN", func_clean);
  69. repo->add (f);
  70. f = new Function ("CODE", func_code);
  71. repo->add (f);
  72. f = new Function ("LEN", func_len);
  73. repo->add (f);
  74. f = new Function ("LOWER", func_lower);
  75. repo->add (f);
  76. f = new Function ("PROPER", func_proper);
  77. repo->add (f);
  78. f = new Function ("ROT", func_rot);
  79. repo->add (f);
  80. f = new Function ("SLEEK", func_sleek);
  81. repo->add (f);
  82. f = new Function ("T", func_t);
  83. repo->add (f);
  84. f = new Function ("TOGGLE", func_toggle);
  85. repo->add (f);
  86. f = new Function ("TRIM", func_trim);
  87. repo->add (f);
  88. f = new Function ("UPPER", func_upper);
  89. repo->add (f);
  90. f = new Function ("VALUE", func_value);
  91. repo->add (f);
  92. // other functions
  93. f = new Function ("COMPARE", func_compare);
  94. f->setParamCount (3);
  95. repo->add (f);
  96. f = new Function ("CONCATENATE", func_concatenate);
  97. f->setParamCount (1, -1);
  98. f->setAcceptArray ();
  99. repo->add (f);
  100. f = new Function ("DOLLAR", func_dollar);
  101. f->setParamCount (1, 2);
  102. repo->add (f);
  103. f = new Function ("EXACT", func_exact);
  104. f->setParamCount (2);
  105. repo->add (f);
  106. f = new Function ("FIND", func_find);
  107. f->setParamCount (2, 3);
  108. repo->add (f);
  109. f = new Function ("FIXED", func_fixed);
  110. f->setParamCount (1, 3);
  111. repo->add (f);
  112. f = new Function ("LEFT", func_left);
  113. f->setParamCount (2);
  114. repo->add (f);
  115. f = new Function ("MID", func_mid);
  116. f->setParamCount (2, 3);
  117. repo->add (f);
  118. f = new Function ("REGEXP", func_regexp);
  119. f->setParamCount (2, 4);
  120. repo->add (f);
  121. f = new Function ("REGEXPRE", func_regexpre);
  122. f->setParamCount (3);
  123. repo->add (f);
  124. f = new Function ("REPLACE", func_replace);
  125. f->setParamCount (4);
  126. repo->add (f);
  127. f = new Function ("REPT", func_rept);
  128. f->setParamCount (2);
  129. repo->add (f);
  130. f = new Function ("RIGHT", func_right);
  131. f->setParamCount (2);
  132. repo->add (f);
  133. f = new Function ("SEARCH", func_search);
  134. f->setParamCount (2, 3);
  135. repo->add (f);
  136. f = new Function ("SUBSTITUTE", func_substitute);
  137. f->setParamCount (3, 4);
  138. repo->add (f);
  139. f = new Function ("TEXT", func_text);
  140. f->setParamCount (1, 2);
  141. repo->add (f);
  142. }
  143. // Function: CHAR
  144. Value func_char (valVector args, ValueCalc *calc, FuncExtra *)
  145. {
  146. int val = calc->conv()->asInteger (args[0]).asInteger ();
  147. return Value (TQString (TQChar (val)));
  148. }
  149. // Function: CLEAN
  150. Value func_clean (valVector args, ValueCalc *calc, FuncExtra *)
  151. {
  152. TQString str (calc->conv()->asString (args[0]).asString());
  153. TQString result;
  154. TQChar c;
  155. int i;
  156. int l = str.length();
  157. for (i = 0; i < l; ++i)
  158. {
  159. c = str[i];
  160. if (c.isPrint())
  161. result += c;
  162. }
  163. return Value (result);
  164. }
  165. // Function: CODE
  166. Value func_code (valVector args, ValueCalc *calc, FuncExtra *)
  167. {
  168. TQString str (calc->conv()->asString (args[0]).asString());
  169. if (str.length() <= 0)
  170. return Value::errorVALUE();
  171. return Value (str[0].unicode());
  172. }
  173. // Function: COMPARE
  174. Value func_compare (valVector args, ValueCalc *calc, FuncExtra *)
  175. {
  176. int result = 0;
  177. bool exact = calc->conv()->asBoolean (args[2]).asBoolean();
  178. TQString s1 = calc->conv()->asString (args[0]).asString();
  179. TQString s2 = calc->conv()->asString (args[1]).asString();
  180. if (!exact)
  181. result = s1.lower().localeAwareCompare(s2.lower());
  182. else
  183. result = s1.localeAwareCompare(s2);
  184. if (result < 0)
  185. result = -1;
  186. else if (result > 0)
  187. result = 1;
  188. return Value (result);
  189. }
  190. void func_concatenate_helper (Value val, ValueCalc *calc,
  191. TQString& tmp)
  192. {
  193. if (val.isArray()) {
  194. for (unsigned int row = 0; row < val.rows(); ++row)
  195. for (unsigned int col = 0; col < val.columns(); ++col)
  196. func_concatenate_helper (val.element (col, row), calc, tmp);
  197. } else
  198. tmp += calc->conv()->asString (val).asString();
  199. }
  200. // Function: CONCATENATE
  201. Value func_concatenate (valVector args, ValueCalc *calc, FuncExtra *)
  202. {
  203. TQString tmp;
  204. for (unsigned int i = 0; i < args.count(); ++i)
  205. func_concatenate_helper (args[i], calc, tmp);
  206. return Value (tmp);
  207. }
  208. // Function: DOLLAR
  209. Value func_dollar (valVector args, ValueCalc *calc, FuncExtra *)
  210. {
  211. // ValueConverter doesn't support money directly, hence we need to
  212. // use the locale. This code has the same effect as the output
  213. // of ValueFormatter for money format.
  214. // This function converts data to double/int, hence it won't support
  215. // larger precision.
  216. double value = calc->conv()->asFloat (args[0]).asFloat();
  217. int precision = 2;
  218. if (args.count() == 2)
  219. precision = calc->conv()->asInteger (args[1]).asInteger();
  220. // do round, because formatMoney doesn't
  221. value = floor (value * pow (10.0, precision) + 0.5) / pow (10.0, precision);
  222. TDELocale *locale = calc->conv()->locale();
  223. TQString s = locale->formatMoney (value, locale->currencySymbol(), precision);
  224. return Value (s);
  225. }
  226. // Function: EXACT
  227. Value func_exact (valVector args, ValueCalc *calc, FuncExtra *)
  228. {
  229. TQString s1 = calc->conv()->asString (args[0]).asString();
  230. TQString s2 = calc->conv()->asString (args[1]).asString();
  231. bool exact = (s1 == s2);
  232. return Value (exact);
  233. }
  234. // Function: FIND
  235. Value func_find (valVector args, ValueCalc *calc, FuncExtra *)
  236. {
  237. TQString find_text, within_text;
  238. int start_num = 1;
  239. find_text = calc->conv()->asString (args[0]).asString();
  240. within_text = calc->conv()->asString (args[1]).asString();
  241. if (args.count() == 3)
  242. start_num = calc->conv()->asInteger (args[2]).asInteger();
  243. // conforms to Excel behaviour
  244. if (start_num <= 0) return Value::errorVALUE();
  245. if (start_num > (int)within_text.length()) return Value::errorVALUE();
  246. int pos = within_text.find (find_text, start_num - 1);
  247. if( pos < 0 ) return Value::errorNA();
  248. return Value (pos + 1);
  249. }
  250. // Function: FIXED
  251. Value func_fixed (valVector args, ValueCalc *calc, FuncExtra *)
  252. {
  253. // uses double, hence won't support big precision
  254. int decimals = 2;
  255. bool no_commas = false;
  256. double number = calc->conv()->asFloat (args[0]).asFloat();
  257. if (args.count() > 1)
  258. decimals = calc->conv()->asInteger (args[1]).asInteger();
  259. if (args.count() == 3)
  260. no_commas = calc->conv()->asBoolean (args[2]).asBoolean();
  261. TQString result;
  262. TDELocale *locale = calc->conv()->locale();
  263. // unfortunately, we can't just use TDELocale::formatNumber because
  264. // * if decimals < 0, number is rounded
  265. // * if no_commas is true, thousand separators shouldn't show up
  266. if( decimals < 0 )
  267. {
  268. decimals = -decimals;
  269. number = floor( number/pow(10.0,decimals)+0.5 ) * pow(10.0,decimals);
  270. decimals = 0;
  271. }
  272. bool neg = number < 0;
  273. result = TQString::number( neg ? -number:number, 'f', decimals );
  274. int pos = result.find('.');
  275. if (pos == -1) pos = result.length();
  276. else result.replace(pos, 1, locale->decimalSymbol());
  277. if( !no_commas )
  278. while (0 < (pos -= 3))
  279. result.insert(pos, locale->thousandsSeparator());
  280. result.prepend( neg ? locale->negativeSign():
  281. locale->positiveSign() );
  282. return Value (result);
  283. }
  284. // Function: LEFT
  285. Value func_left (valVector args, ValueCalc *calc, FuncExtra *)
  286. {
  287. TQString str = calc->conv()->asString (args[0]).asString();
  288. int nb = 1;
  289. if (args.count() == 2)
  290. nb = calc->conv()->asInteger (args[1]).asInteger();
  291. return Value (str.left (nb));
  292. }
  293. // Function: LEN
  294. Value func_len (valVector args, ValueCalc *calc, FuncExtra *)
  295. {
  296. int nb = calc->conv()->asString (args[0]).asString().length();
  297. return Value (nb);
  298. }
  299. // Function: LOWER
  300. Value func_lower (valVector args, ValueCalc *calc, FuncExtra *)
  301. {
  302. return Value (calc->conv()->asString (args[0]).asString().lower());
  303. }
  304. // Function: MID
  305. Value func_mid (valVector args, ValueCalc *calc, FuncExtra *)
  306. {
  307. TQString str = calc->conv()->asString (args[0]).asString();
  308. int pos = calc->conv()->asInteger (args[1]).asInteger();
  309. uint len = 0xffffffff;
  310. if (args.count() == 3)
  311. len = (uint) calc->conv()->asInteger (args[2]).asInteger();
  312. // Excel compatible
  313. pos--;
  314. return Value (str.mid (pos, len));
  315. }
  316. // Function: PROPER
  317. Value func_proper (valVector args, ValueCalc *calc, FuncExtra *)
  318. {
  319. TQString str = calc->conv()->asString (args[0]).asString().lower();
  320. TQChar f;
  321. bool first = true;
  322. for (unsigned int i = 0; i < str.length(); ++i)
  323. {
  324. if (first)
  325. {
  326. f = str[i];
  327. if (f.isNumber())
  328. continue;
  329. f = f.upper();
  330. str[i] = f;
  331. first = false;
  332. continue;
  333. }
  334. if (str[i] == ' ' || str[i] == '-')
  335. first = true;
  336. }
  337. return Value (str);
  338. }
  339. // Function: REGEXP
  340. Value func_regexp (valVector args, ValueCalc *calc, FuncExtra *)
  341. {
  342. // ensure that we got a valid regular expression
  343. TQRegExp exp (calc->conv()->asString (args[1]).asString());
  344. if (!exp.isValid ())
  345. return Value::errorVALUE();
  346. TQString s = calc->conv()->asString (args[0]).asString();
  347. TQString defText;
  348. if (args.count() > 2)
  349. defText = calc->conv()->asString (args[2]).asString();
  350. int bkref = 0;
  351. if (args.count() == 4)
  352. bkref = calc->conv()->asInteger (args[3]).asInteger();
  353. if (bkref < 0) // strange back-reference
  354. return Value::errorVALUE();
  355. TQString returnValue;
  356. int pos = exp.search (s);
  357. if (pos == -1)
  358. returnValue = defText;
  359. else
  360. returnValue = exp.cap (bkref);
  361. return Value (returnValue);
  362. }
  363. // Function: REGEXPRE
  364. Value func_regexpre (valVector args, ValueCalc *calc, FuncExtra *)
  365. {
  366. // ensure that we got a valid regular expression
  367. TQRegExp exp (calc->conv()->asString (args[1]).asString());
  368. if (!exp.isValid ())
  369. return Value::errorVALUE();
  370. TQString s = calc->conv()->asString (args[0]).asString();
  371. TQString str = calc->conv()->asString (args[2]).asString();
  372. int pos = 0;
  373. while ((pos = exp.search (s, pos)) != -1)
  374. {
  375. int i = exp.matchedLength();
  376. s = s.replace (pos, i, str);
  377. pos += str.length();
  378. }
  379. return Value (s);
  380. }
  381. // Function: REPLACE
  382. Value func_replace (valVector args, ValueCalc *calc, FuncExtra *)
  383. {
  384. TQString text = calc->conv()->asString (args[0]).asString();
  385. int pos = calc->conv()->asInteger (args[1]).asInteger();
  386. int len = calc->conv()->asInteger (args[2]).asInteger();
  387. TQString new_text = calc->conv()->asString (args[3]).asString();
  388. if (pos < 0) pos = 0;
  389. TQString result = text.replace (pos-1, len, new_text);
  390. return Value (result);
  391. }
  392. // Function: REPT
  393. Value func_rept (valVector args, ValueCalc *calc, FuncExtra *)
  394. {
  395. TQString s = calc->conv()->asString (args[0]).asString();
  396. int nb = calc->conv()->asInteger (args[1]).asInteger();
  397. TQString result;
  398. for (int i = 0; i < nb; i++) result += s;
  399. return Value (result);
  400. }
  401. // Function: RIGHT
  402. Value func_right (valVector args, ValueCalc *calc, FuncExtra *)
  403. {
  404. TQString str = calc->conv()->asString (args[0]).asString();
  405. int nb = 1;
  406. if (args.count() == 2)
  407. nb = calc->conv()->asInteger (args[1]).asInteger();
  408. return Value (str.right (nb));
  409. }
  410. // Function: ROT
  411. Value func_rot (valVector args, ValueCalc *calc, FuncExtra *)
  412. {
  413. TQString text = calc->conv()->asString (args[0]).asString();
  414. for( unsigned i=0; i<text.length(); i++ )
  415. {
  416. unsigned c = text[i].upper().unicode();
  417. if( ( c >= 'A' ) && ( c <= 'M' ) )
  418. text[i] = TQChar( text[i].unicode() + 13);
  419. if( ( c >= 'N' ) && ( c <= 'Z' ) )
  420. text[i] = TQChar( text[i].unicode() - 13);
  421. }
  422. return Value (text);
  423. }
  424. // Function: SEARCH
  425. Value func_search (valVector args, ValueCalc *calc, FuncExtra *)
  426. {
  427. TQString find_text = calc->conv()->asString (args[0]).asString();
  428. TQString within_text = calc->conv()->asString (args[1]).asString();
  429. int start_num = 1;
  430. if (args.count() == 3)
  431. start_num = calc->conv()->asInteger (args[2]).asInteger();
  432. // conforms to Excel behaviour
  433. if (start_num <= 0) return Value::errorVALUE();
  434. if (start_num > (int)within_text.length()) return Value::errorVALUE();
  435. // use globbing feature of TQRegExp
  436. TQRegExp regex( find_text, false, true );
  437. int pos = within_text.find( regex, start_num-1 );
  438. if( pos < 0 ) return Value::errorNA();
  439. return Value (pos + 1);
  440. }
  441. // Function: SLEEK
  442. Value func_sleek (valVector args, ValueCalc *calc, FuncExtra *)
  443. {
  444. TQString str = calc->conv()->asString (args[0]).asString();
  445. TQString result;
  446. TQChar c;
  447. int i;
  448. int l = str.length();
  449. for (i = 0; i < l; ++i)
  450. {
  451. c = str[i];
  452. if (!c.isSpace())
  453. result += c;
  454. }
  455. return Value (result);
  456. }
  457. // Function: SUBSTITUTE
  458. Value func_substitute (valVector args, ValueCalc *calc, FuncExtra *)
  459. {
  460. int num = 1;
  461. bool all = true;
  462. if (args.count() == 4)
  463. {
  464. num = calc->conv()->asInteger (args[3]).asInteger();
  465. all = false;
  466. }
  467. TQString text = calc->conv()->asString (args[0]).asString();
  468. TQString old_text = calc->conv()->asString (args[1]).asString();
  469. TQString new_text = calc->conv()->asString (args[2]).asString();
  470. if( num <= 0 ) return Value::errorVALUE();
  471. if (old_text.length() == 0) return Value (text);
  472. TQString result = text;
  473. int p = result.find (old_text);
  474. while ((p != -1) && (num > 0))
  475. {
  476. result.replace( p, old_text.length(), new_text );
  477. // find another location, starting straight after the replaced text
  478. p = result.find (old_text, p + new_text.length());
  479. if( !all ) num--;
  480. }
  481. return Value (result);
  482. }
  483. // Function: T
  484. Value func_t (valVector args, ValueCalc *calc, FuncExtra *)
  485. {
  486. return calc->conv()->asString (args[0]);
  487. }
  488. // Function: TEXT
  489. Value func_text (valVector args, ValueCalc *calc, FuncExtra *)
  490. {
  491. //Currently the same as the T function ...
  492. //Second parameter is format_text. It is currently ignored.
  493. return calc->conv()->asString (args[0]);
  494. }
  495. // Function: TOGGLE
  496. Value func_toggle (valVector args, ValueCalc *calc, FuncExtra *)
  497. {
  498. TQString str = calc->conv()->asString (args[0]).asString();
  499. int i;
  500. int l = str.length();
  501. for (i = 0; i < l; ++i)
  502. {
  503. TQChar c = str[i];
  504. TQChar lc = c.lower();
  505. TQChar uc = c.upper();
  506. if (c == lc) // it is in lowercase
  507. str[i] = c.upper();
  508. else if (c == uc) // it is in uppercase
  509. str[i] = c.lower();
  510. }
  511. return Value (str);
  512. }
  513. // Function: TRIM
  514. Value func_trim (valVector args, ValueCalc *calc, FuncExtra *)
  515. {
  516. return Value (
  517. calc->conv()->asString (args[0]).asString().simplifyWhiteSpace());
  518. }
  519. // Function: UPPER
  520. Value func_upper (valVector args, ValueCalc *calc, FuncExtra *)
  521. {
  522. return Value (calc->conv()->asString (args[0]).asString().upper());
  523. }
  524. // Function: VALUE
  525. Value func_value (valVector args, ValueCalc *calc, FuncExtra *)
  526. {
  527. // same as the N function
  528. return calc->conv()->asFloat (args[0]);
  529. }