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.

650 lines
19KB

  1. /* This file is part of the KDE project
  2. Copyright 2004 Tomas Mecir <mecirt@gmail.com>
  3. Copyright (C) 1998-2004 KSpread Team <koffice-devel@kde.org>
  4. This library is free software; you can redistribute it and/or
  5. modify it under the terms of the GNU Library General Public
  6. License as published by the Free Software Foundation; either
  7. version 2 of the License, or (at your option) any later version.
  8. This library is distributed in the hope that it will be useful,
  9. but WITHOUT ANY WARRANTY; without even the implied warranty of
  10. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  11. Library General Public License for more details.
  12. You should have received a copy of the GNU Library General Public License
  13. along with this library; see the file COPYING.LIB. If not, write to
  14. the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
  15. * Boston, MA 02110-1301, USA.
  16. */
  17. #include "valueformatter.h"
  18. #include "kspread_cell.h"
  19. #include "kspread_locale.h"
  20. #include "kspread_util.h"
  21. #include "valueconverter.h"
  22. #include <kcalendarsystem.h>
  23. #include <kdebug.h>
  24. #include <tdelocale.h>
  25. #include <float.h>
  26. #include <math.h>
  27. using namespace KSpread;
  28. ValueFormatter::ValueFormatter (ValueConverter *conv) : converter( conv )
  29. {
  30. }
  31. TQString ValueFormatter::formatText (Cell *cell, FormatType fmtType)
  32. {
  33. if (cell->hasError ())
  34. return errorFormat (cell);
  35. TQString str;
  36. Format::FloatFormat floatFormat =
  37. cell->format()->floatFormat (cell->column(), cell->row());
  38. int precision = cell->format()->precision (cell->column(), cell->row());
  39. TQString prefix = cell->format()->prefix (cell->column(), cell->row());
  40. TQString postfix = cell->format()->postfix (cell->column(), cell->row());
  41. Format::Currency currency;
  42. bool valid = cell->format()->currencyInfo(currency);
  43. TQString currencySymbol = valid ? currency.symbol : TQString();
  44. return formatText (cell->value(), fmtType, precision,
  45. floatFormat, prefix, postfix, currencySymbol);
  46. }
  47. TQString ValueFormatter::formatText (const Value &value,
  48. FormatType fmtType, int precision, Format::FloatFormat floatFormat,
  49. const TQString &prefix, const TQString &postfix, const TQString &currencySymbol)
  50. {
  51. //if we have an array, use its first element
  52. if (value.isArray())
  53. return formatText (value.element (0, 0), fmtType, precision,
  54. floatFormat, prefix, postfix, currencySymbol);
  55. TQString str;
  56. //step 1: determine formatting that will be used
  57. fmtType = determineFormatting (value, fmtType);
  58. //step 2: format the value !
  59. //text
  60. if (fmtType == Text_format)
  61. {
  62. str = converter->asString (value).asString();
  63. if (!str.isEmpty() && str[0]=='\'' )
  64. str = str.mid(1);
  65. }
  66. //date
  67. else if (formatIsDate (fmtType))
  68. str = dateFormat (value.asDate(), fmtType);
  69. //time
  70. else if (formatIsTime (fmtType))
  71. str = timeFormat (value.asDateTime(), fmtType);
  72. //fraction
  73. else if (formatIsFraction (fmtType))
  74. str = fractionFormat (value.asFloat(), fmtType);
  75. //another
  76. else
  77. {
  78. //some cell parameters ...
  79. double v = converter->asFloat (value).asFloat();
  80. // Always unsigned ?
  81. if ((floatFormat == Format::AlwaysUnsigned) && (v < 0.0))
  82. v *= -1.0;
  83. // Make a string out of it.
  84. str = createNumberFormat (v, precision, fmtType,
  85. (floatFormat == Format::AlwaysSigned), currencySymbol);
  86. // Remove trailing zeros and the decimal point if necessary
  87. // unless the number has no decimal point
  88. if (precision == -1)
  89. {
  90. TQChar decimal_point = converter->locale()->decimalSymbol()[0];
  91. if ( decimal_point.isNull() )
  92. decimal_point = '.';
  93. removeTrailingZeros (str, decimal_point);
  94. }
  95. }
  96. if (!prefix.isEmpty())
  97. str = prefix + ' ' + str;
  98. if( !postfix.isEmpty())
  99. str += ' ' + postfix;
  100. //kdDebug() << "ValueFormatter says: " << str << endl;
  101. return str;
  102. }
  103. FormatType ValueFormatter::determineFormatting (const Value &value,
  104. FormatType fmtType)
  105. {
  106. //if the cell value is a string, then we want to display it as-is,
  107. //no matter what, same if the cell is empty
  108. if (value.isString () || (value.format() == Value::fmt_None))
  109. return Text_format;
  110. //same if we're supposed to display string, no matter what we actually got
  111. if (fmtType == Text_format)
  112. return Text_format;
  113. //now, everything depends on whether the formatting is Generic or not
  114. if (fmtType == Generic_format)
  115. {
  116. //here we decide based on value's format...
  117. Value::Format fmt = value.format();
  118. switch (fmt) {
  119. case Value::fmt_None:
  120. fmtType = Text_format;
  121. break;
  122. case Value::fmt_Boolean:
  123. fmtType = Text_format;
  124. break;
  125. case Value::fmt_Number:
  126. if (value.asFloat() > 1e+10)
  127. fmtType = Scientific_format;
  128. else
  129. fmtType = Number_format;
  130. break;
  131. case Value::fmt_Percent:
  132. fmtType = Percentage_format;
  133. break;
  134. case Value::fmt_Money:
  135. fmtType = Money_format;
  136. break;
  137. case Value::fmt_DateTime:
  138. fmtType = TextDate_format;
  139. break;
  140. case Value::fmt_Date:
  141. fmtType = ShortDate_format;
  142. break;
  143. case Value::fmt_Time:
  144. fmtType = Time_format;
  145. break;
  146. case Value::fmt_String:
  147. //this should never happen
  148. fmtType = Text_format;
  149. break;
  150. };
  151. return fmtType;
  152. }
  153. else
  154. {
  155. //we'll mostly want to use the given formatting, the only exception
  156. //being Boolean values
  157. //TODO: is this correct? We may also want to convert bools to 1s and 0s
  158. //if we want to display a number...
  159. //TODO: what to do about Custom formatting? We don't support it as of now,
  160. // but we'll have it ... one day, that is ...
  161. if (value.isBoolean())
  162. return Text_format;
  163. else
  164. return fmtType;
  165. }
  166. }
  167. void ValueFormatter::removeTrailingZeros (TQString &str, TQChar decimal_point)
  168. {
  169. if (str.find (decimal_point) < 0)
  170. //no decimal point -> nothing to do
  171. return;
  172. int start = 0;
  173. int cslen = converter->locale()->currencySymbol().length();
  174. if (str.find ('%') != -1)
  175. start = 2;
  176. else if (str.find (converter->locale()->currencySymbol()) ==
  177. ((int) (str.length() - cslen)))
  178. start = cslen + 1;
  179. else if ((start = str.find ('E')) != -1)
  180. start = str.length() - start;
  181. else
  182. start = 0;
  183. int i = str.length() - start;
  184. bool bFinished = false;
  185. while ( !bFinished && i > 0 )
  186. {
  187. TQChar ch = str[i - 1];
  188. if (ch == '0')
  189. str.remove (--i,1);
  190. else
  191. {
  192. bFinished = true;
  193. if (ch == decimal_point)
  194. str.remove (--i, 1);
  195. }
  196. }
  197. }
  198. TQString ValueFormatter::createNumberFormat ( double value, int precision,
  199. FormatType fmt, bool alwaysSigned, const TQString& currencySymbol)
  200. {
  201. // if precision is -1, ask for a huge number of decimals, we'll remove
  202. // the zeros later. Is 8 ok ?
  203. // Stefan: No. Use maximum possible decimal precision (DBL_DIG) instead.
  204. int p = (precision == -1) ? 8 : precision;
  205. TQString localizedNumber;
  206. int pos = 0;
  207. //multiply value by 100 for percentage format
  208. if (fmt == Percentage_format)
  209. value *= 100;
  210. // this will avoid displaying negative zero, i.e "-0.0000"
  211. if( fabs( value ) < DBL_EPSILON ) value = 0.0;
  212. // round the number, based on desired precision if not scientific is chosen
  213. //(scientific has relative precision)
  214. if( fmt != Scientific_format )
  215. {
  216. double m[] = { 1, 10, 100, 1e3, 1e4, 1e5, 1e6, 1e7, 1e8, 1e9, 1e10 };
  217. double mm = (p > 10) ? pow(10.0,p) : m[p];
  218. bool neg = value < 0;
  219. value = floor( fabs(value)*mm + 0.5 ) / mm;
  220. if( neg ) value = -value;
  221. }
  222. TQChar decimal_point;
  223. switch (fmt)
  224. {
  225. case Number_format:
  226. localizedNumber = converter->locale()->formatNumber(value, p);
  227. break;
  228. case Percentage_format:
  229. localizedNumber = converter->locale()->formatNumber (value, p)+ " %";
  230. break;
  231. case Money_format:
  232. localizedNumber = converter->locale()->formatMoney (value,
  233. currencySymbol.isEmpty() ? converter->locale()->currencySymbol() : currencySymbol, p );
  234. break;
  235. case Scientific_format:
  236. decimal_point = converter->locale()->decimalSymbol()[0];
  237. localizedNumber = TQString::number (value, 'E', p);
  238. if ((pos = localizedNumber.find ('.')) != -1)
  239. localizedNumber = localizedNumber.replace (pos, 1, decimal_point);
  240. break;
  241. default :
  242. //other formatting?
  243. // This happens with Custom_format...
  244. kdDebug(36001)<<"Wrong usage of ValueFormatter::createNumberFormat fmt=" << fmt << "\n";
  245. break;
  246. }
  247. //prepend positive sign if needed
  248. if (alwaysSigned && value >= 0 )
  249. if (converter->locale()->positiveSign().isEmpty())
  250. localizedNumber='+'+localizedNumber;
  251. return localizedNumber;
  252. }
  253. TQString ValueFormatter::fractionFormat (double value, FormatType fmtType)
  254. {
  255. double result = value - floor(value);
  256. int index;
  257. int limit = 0;
  258. /* return w/o fraction part if not necessary */
  259. if (result == 0)
  260. return TQString::number(value);
  261. switch (fmtType) {
  262. case fraction_half:
  263. index = 2;
  264. break;
  265. case fraction_quarter:
  266. index = 4;
  267. break;
  268. case fraction_eighth:
  269. index = 8;
  270. break;
  271. case fraction_sixteenth:
  272. index = 16;
  273. break;
  274. case fraction_tenth:
  275. index = 10;
  276. break;
  277. case fraction_hundredth:
  278. index = 100;
  279. break;
  280. case fraction_one_digit:
  281. index = 3;
  282. limit = 9;
  283. break;
  284. case fraction_two_digits:
  285. index = 4;
  286. limit = 99;
  287. break;
  288. case fraction_three_digits:
  289. index = 5;
  290. limit = 999;
  291. break;
  292. default:
  293. kdDebug(36001) << "Error in Fraction format\n";
  294. return TQString::number(value);
  295. break;
  296. } /* switch */
  297. /* handle halves, quarters, tenths, ... */
  298. if (fmtType != fraction_three_digits
  299. && fmtType != fraction_two_digits
  300. && fmtType != fraction_one_digit) {
  301. double calc = 0;
  302. int index1 = 0;
  303. double diff = result;
  304. for (int i = 1; i <= index; i++) {
  305. calc = i * 1.0 / index;
  306. if (fabs(result - calc) < diff) {
  307. index1 = i;
  308. diff = fabs(result - calc);
  309. }
  310. }
  311. if( index1 == 0 ) return TQString("%1").arg( floor(value) );
  312. if( index1 == index ) return TQString("%1").arg( floor(value)+1 );
  313. if( floor(value) == 0)
  314. return TQString("%1/%2").arg( index1 ).arg( index );
  315. return TQString("%1 %2/%3")
  316. .arg( floor(value) )
  317. .arg( index1 )
  318. .arg( index );
  319. }
  320. /* handle fraction_one_digit, fraction_two_digit
  321. * and fraction_three_digit style */
  322. double precision, denominator, numerator;
  323. do {
  324. double val1 = result;
  325. double val2 = rint(result);
  326. double inter2 = 1;
  327. double inter4, p, q;
  328. inter4 = p = q = 0;
  329. precision = pow(10.0, -index);
  330. numerator = val2;
  331. denominator = 1;
  332. while (fabs(numerator/denominator - result) > precision) {
  333. val1 = (1 / (val1 - val2));
  334. val2 = rint(val1);
  335. p = val2 * numerator + inter2;
  336. q = val2 * denominator + inter4;
  337. inter2 = numerator;
  338. inter4 = denominator;
  339. numerator = p;
  340. denominator = q;
  341. }
  342. index--;
  343. } while (fabs(denominator) > limit);
  344. denominator = fabs(denominator);
  345. numerator = fabs(numerator);
  346. if (denominator == numerator)
  347. return TQString().setNum(floor(value + 1));
  348. else
  349. {
  350. if ( floor(value) == 0 )
  351. return TQString("%1/%2").arg(numerator).arg(denominator);
  352. else
  353. return TQString("%1 %2/%3")
  354. .arg(floor(value))
  355. .arg(numerator)
  356. .arg(denominator);
  357. }
  358. }
  359. TQString ValueFormatter::timeFormat (const TQDateTime &dt, FormatType fmtType)
  360. {
  361. if (fmtType == Time_format)
  362. return converter->locale()->formatTime(dt.time(), false);
  363. if (fmtType == SecondeTime_format)
  364. return converter->locale()->formatTime(dt.time(), true);
  365. int h = dt.time().hour();
  366. int m = dt.time().minute();
  367. int s = dt.time().second();
  368. TQString hour = ( h < 10 ? "0" + TQString::number(h) : TQString::number(h) );
  369. TQString minute = ( m < 10 ? "0" + TQString::number(m) : TQString::number(m) );
  370. TQString second = ( s < 10 ? "0" + TQString::number(s) : TQString::number(s) );
  371. bool pm = (h > 12);
  372. TQString AMPM( pm ? i18n("PM"):i18n("AM") );
  373. if (fmtType == Time_format1) { // 9 : 01 AM
  374. return TQString("%1:%2 %3")
  375. .arg((pm ? h - 12 : h),2)
  376. .arg(minute,2)
  377. .arg(AMPM);
  378. }
  379. if (fmtType == Time_format2) { //9:01:05 AM
  380. return TQString("%1:%2:%3 %4")
  381. .arg((pm ? h-12 : h),2)
  382. .arg(minute,2)
  383. .arg(second,2)
  384. .arg(AMPM);
  385. }
  386. if (fmtType == Time_format3) {
  387. return TQString("%1 %2 %3 %4 %5 %6") // 9 h 01 min 28 s
  388. .arg(hour,2)
  389. .arg(i18n("h"))
  390. .arg(minute,2)
  391. .arg(i18n("min"))
  392. .arg(second,2)
  393. .arg(i18n("s"));
  394. }
  395. if (fmtType == Time_format4) { // 9:01
  396. return TQString("%1:%2").arg(hour, 2).arg(minute, 2);
  397. }
  398. if (fmtType == Time_format5) { // 9:01:12
  399. return TQString("%1:%2:%3").arg(hour, 2).arg(minute, 2).arg(second, 2);
  400. }
  401. TQDate d1(dt.date());
  402. TQDate d2( 1899, 12, 31 );
  403. int d = d2.daysTo( d1 ) + 1;
  404. h += d * 24;
  405. if (fmtType == Time_format6)
  406. { // [mm]:ss
  407. m += (h * 60);
  408. return TQString("%1:%2").arg(m, 1).arg(second, 2);
  409. }
  410. if (fmtType == Time_format7) { // [h]:mm:ss
  411. return TQString("%1:%2:%3").arg(h, 1).arg(minute, 2).arg(second, 2);
  412. }
  413. if (fmtType == Time_format8)
  414. { // [h]:mm
  415. m += (h * 60);
  416. return TQString("%1:%2").arg(h, 1).arg(minute, 2);
  417. }
  418. return converter->locale()->formatTime( dt.time(), false );
  419. }
  420. TQString ValueFormatter::dateFormat (const TQDate &date, FormatType fmtType)
  421. {
  422. TQString tmp;
  423. if (fmtType == ShortDate_format) {
  424. tmp = converter->locale()->formatDate(date, true);
  425. }
  426. else if (fmtType == TextDate_format) {
  427. tmp = converter->locale()->formatDate(date, false);
  428. }
  429. else if (fmtType == date_format1) { /*18-Feb-99 */
  430. tmp = TQString().sprintf("%02d", date.day());
  431. tmp += "-" + converter->locale()->calendar()->monthString(date, true) + "-";
  432. tmp += TQString::number(date.year()).right(2);
  433. }
  434. else if (fmtType == date_format2) { /*18-Feb-1999 */
  435. tmp = TQString().sprintf("%02d", date.day());
  436. tmp += "-" + converter->locale()->calendar()->monthString(date, true) + "-";
  437. tmp += TQString::number(date.year());
  438. }
  439. else if (fmtType == date_format3) { /*18-Feb */
  440. tmp = TQString().sprintf("%02d", date.day());
  441. tmp += "-" + converter->locale()->calendar()->monthString(date, true);
  442. }
  443. else if (fmtType == date_format4) { /*18-05 */
  444. tmp = TQString().sprintf("%02d", date.day());
  445. tmp += "-" + TQString().sprintf("%02d", date.month() );
  446. }
  447. else if (fmtType == date_format5) { /*18/05/00 */
  448. tmp = TQString().sprintf("%02d", date.day());
  449. tmp += "/" + TQString().sprintf("%02d", date.month()) + "/";
  450. tmp += TQString::number(date.year()).right(2);
  451. }
  452. else if (fmtType == date_format6) { /*18/05/1999 */
  453. tmp = TQString().sprintf("%02d", date.day());
  454. tmp += "/" + TQString().sprintf("%02d", date.month()) + "/";
  455. tmp += TQString::number(date.year());
  456. }
  457. else if (fmtType == date_format7) { /*Feb-99 */
  458. tmp = converter->locale()->calendar()->monthString(date, true) + "-";
  459. tmp += TQString::number(date.year()).right(2);
  460. }
  461. else if (fmtType == date_format8) { /*February-99 */
  462. tmp = converter->locale()->calendar()->monthString(date, false) + "-";
  463. tmp += TQString::number(date.year()).right(2);
  464. }
  465. else if (fmtType == date_format9) { /*February-1999 */
  466. tmp = converter->locale()->calendar()->monthString(date, false) + "-";
  467. tmp += TQString::number(date.year());
  468. }
  469. else if (fmtType == date_format10) { /*F-99 */
  470. tmp = converter->locale()->calendar()->monthString(date, false).at(0) + "-";
  471. tmp += TQString::number(date.year()).right(2);
  472. }
  473. else if (fmtType == date_format11) { /*18/Feb */
  474. tmp = TQString().sprintf("%02d", date.day()) + "/";
  475. tmp += converter->locale()->calendar()->monthString(date, true);
  476. }
  477. else if (fmtType == date_format12) { /*18/02 */
  478. tmp = TQString().sprintf("%02d", date.day()) + "/";
  479. tmp += TQString().sprintf("%02d", date.month());
  480. }
  481. else if (fmtType == date_format13) { /*18/Feb/1999 */
  482. tmp = TQString().sprintf("%02d", date.day());
  483. tmp += "/" + converter->locale()->calendar()->monthString(date, true) + "/";
  484. tmp += TQString::number(date.year());
  485. }
  486. else if (fmtType == date_format14) { /*2000/Feb/18 */
  487. tmp = TQString::number(date.year());
  488. tmp += "/" + converter->locale()->calendar()->monthString(date, true) + "/";
  489. tmp += TQString().sprintf("%02d", date.day());
  490. }
  491. else if (fmtType == date_format15) { /*2000-Feb-18 */
  492. tmp = TQString::number(date.year());
  493. tmp += "-" + converter->locale()->calendar()->monthString(date, true) + "-";
  494. tmp += TQString().sprintf("%02d", date.day());
  495. }
  496. else if (fmtType == date_format16) { /*2000-02-18 */
  497. tmp = TQString::number(date.year());
  498. tmp += "-" + TQString().sprintf("%02d", date.month()) + "-";
  499. tmp += TQString().sprintf("%02d", date.day());
  500. }
  501. else if (fmtType == date_format17) { /*2 february 2000 */
  502. tmp = TQString().sprintf("%d", date.day());
  503. tmp += " " + converter->locale()->calendar()->monthString(date, false) + " ";
  504. tmp += TQString::number(date.year());
  505. }
  506. else if (fmtType == date_format18) { /*02/18/1999 */
  507. tmp = TQString().sprintf("%02d", date.month());
  508. tmp += "/" + TQString().sprintf("%02d", date.day());
  509. tmp += "/" + TQString::number(date.year());
  510. }
  511. else if (fmtType == date_format19) { /*02/18/99 */
  512. tmp = TQString().sprintf("%02d", date.month());
  513. tmp += "/" + TQString().sprintf("%02d", date.day());
  514. tmp += "/" + TQString::number(date.year()).right(2);
  515. }
  516. else if (fmtType == date_format20) { /*Feb/18/99 */
  517. tmp = converter->locale()->calendar()->monthString(date, true);
  518. tmp += "/" + TQString().sprintf("%02d", date.day());
  519. tmp += "/" + TQString::number(date.year()).right(2);
  520. }
  521. else if (fmtType == date_format21) { /*Feb/18/1999 */
  522. tmp = converter->locale()->calendar()->monthString(date, true);
  523. tmp += "/" + TQString().sprintf("%02d", date.day());
  524. tmp += "/" + TQString::number(date.year());
  525. }
  526. else if (fmtType == date_format22) { /*Feb-1999 */
  527. tmp = converter->locale()->calendar()->monthString(date, true) + "-";
  528. tmp += TQString::number(date.year());
  529. }
  530. else if (fmtType == date_format23) { /*1999 */
  531. tmp = TQString::number(date.year());
  532. }
  533. else if (fmtType == date_format24) { /*99 */
  534. tmp = TQString::number(date.year()).right(2);
  535. }
  536. else if (fmtType == date_format25) { /*2000/02/18 */
  537. tmp = TQString::number(date.year());
  538. tmp += "/" + TQString().sprintf("%02d", date.month());
  539. tmp += "/" + TQString().sprintf("%02d", date.day());
  540. }
  541. else if (fmtType == date_format26) { /*2000/Feb/18 */
  542. tmp = TQString::number(date.year());
  543. tmp += "/" + converter->locale()->calendar()->monthString(date, true);
  544. tmp += "/" + TQString().sprintf("%02d", date.day());
  545. }
  546. else
  547. tmp = converter->locale()->formatDate(date, true);
  548. // Missing compared with gnumeric:
  549. // "m/d/yy h:mm", /* 20 */
  550. // "m/d/yyyy h:mm", /* 21 */
  551. // "mmm/ddd/yy", /* 12 */
  552. // "mmm/ddd/yyyy", /* 13 */
  553. // "mm/ddd/yy", /* 14 */
  554. // "mm/ddd/yyyy", /* 15 */
  555. return tmp;
  556. }
  557. TQString ValueFormatter::errorFormat (Cell *cell)
  558. {
  559. TQString err;
  560. if (cell->testFlag (Cell::Flag_ParseError))
  561. err = "#" + i18n ("Parse") + "!";
  562. else if ( cell->testFlag (Cell::Flag_CircularCalculation))
  563. err = "#" + i18n ("Circle") + "!";
  564. else if ( cell->testFlag (Cell::Flag_DependancyError))
  565. err = "#" + i18n ("Depend") + "!";
  566. else
  567. {
  568. err = "####";
  569. kdDebug(36001) << "Unhandled error type." << endl;
  570. }
  571. return err;
  572. }