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.

343 lines
8.6KB

  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 reference functions
  19. #include "kspread_cell.h"
  20. #include "kspread_sheet.h"
  21. #include "kspread_util.h"
  22. #include "kspread_value.h"
  23. #include "functions.h"
  24. #include "valuecalc.h"
  25. #include "valueconverter.h"
  26. using namespace KSpread;
  27. // prototypes (sorted alphabetically)
  28. Value func_address (valVector args, ValueCalc *calc, FuncExtra *);
  29. Value func_areas (valVector args, ValueCalc *calc, FuncExtra *);
  30. Value func_choose (valVector args, ValueCalc *calc, FuncExtra *);
  31. Value func_column (valVector args, ValueCalc *calc, FuncExtra *);
  32. Value func_columns (valVector args, ValueCalc *calc, FuncExtra *);
  33. Value func_index (valVector args, ValueCalc *calc, FuncExtra *);
  34. Value func_indirect (valVector args, ValueCalc *calc, FuncExtra *);
  35. Value func_lookup (valVector args, ValueCalc *calc, FuncExtra *);
  36. Value func_row (valVector args, ValueCalc *calc, FuncExtra *);
  37. Value func_rows (valVector args, ValueCalc *calc, FuncExtra *);
  38. // registers all reference functions
  39. void RegisterReferenceFunctions()
  40. {
  41. FunctionRepository* repo = FunctionRepository::self();
  42. Function *f;
  43. f = new Function ("ADDRESS", func_address);
  44. f->setParamCount (2, 5);
  45. repo->add (f);
  46. f = new Function ("AREAS", func_areas);
  47. f->setParamCount (1);
  48. f->setNeedsExtra (true);
  49. repo->add (f);
  50. f = new Function ("CHOOSE", func_choose);
  51. f->setParamCount (2, -1);
  52. f->setAcceptArray ();
  53. repo->add (f);
  54. f = new Function ("COLUMN", func_column);
  55. f->setParamCount (0, 1);
  56. repo->add (f);
  57. f = new Function ("COLUMNS", func_columns);
  58. f->setParamCount (1);
  59. f->setAcceptArray ();
  60. f->setNeedsExtra (true);
  61. repo->add (f);
  62. f = new Function ("INDEX", func_index);
  63. f->setParamCount (3);
  64. f->setAcceptArray ();
  65. repo->add (f);
  66. f = new Function ("INDIRECT", func_indirect);
  67. f->setParamCount (1, 2);
  68. f->setNeedsExtra (true);
  69. repo->add (f);
  70. f = new Function ("LOOKUP", func_lookup);
  71. f->setParamCount (3);
  72. f->setAcceptArray ();
  73. repo->add (f);
  74. f = new Function ("ROW", func_row);
  75. f->setParamCount (0, 1);
  76. repo->add (f);
  77. f = new Function ("ROWS", func_rows);
  78. f->setParamCount (1);
  79. f->setAcceptArray ();
  80. f->setNeedsExtra (true);
  81. repo->add (f);
  82. }
  83. // Function: ADDRESS
  84. Value func_address (valVector args, ValueCalc *calc, FuncExtra *)
  85. {
  86. bool r1c1 = false;
  87. TQString sheetName;
  88. int absNum = 1;
  89. if (args.count() > 2)
  90. absNum = calc->conv()->asInteger (args[2]).asInteger();
  91. if (args.count() > 3)
  92. r1c1 = !(calc->conv()->asBoolean (args[3]).asBoolean());
  93. if (args.count() == 5)
  94. sheetName = calc->conv()->asString (args[4]).asString();
  95. TQString result;
  96. int row = calc->conv()->asInteger (args[0]).asInteger();
  97. int col = calc->conv()->asInteger (args[1]).asInteger();
  98. if ( !sheetName.isEmpty() )
  99. {
  100. result += sheetName;
  101. result += "!";
  102. }
  103. if ( r1c1 )
  104. {
  105. // row first
  106. bool abs = false;
  107. if ( absNum == 1 || absNum == 2 )
  108. abs = true;
  109. result += 'R';
  110. if ( !abs )
  111. result += '[';
  112. result += TQString::number( row );
  113. if ( !abs )
  114. result += ']';
  115. // column
  116. abs = false;
  117. if ( absNum == 1 || absNum == 3 )
  118. abs = true;
  119. result += 'C';
  120. if ( !abs )
  121. result += '[';
  122. result += TQString::number( col );
  123. if ( !abs )
  124. result += ']';
  125. }
  126. else
  127. {
  128. bool abs = false;
  129. if ( absNum == 1 || absNum == 3 )
  130. abs = true;
  131. if ( abs )
  132. result += '$';
  133. result += Cell::columnName( col );
  134. abs = false;
  135. if ( absNum == 1 || absNum == 2 )
  136. abs = true;
  137. if ( abs )
  138. result += '$';
  139. result += TQString::number( row );
  140. }
  141. return Value (result);
  142. }
  143. bool checkRef( TQString const & ref )
  144. {
  145. Range r( ref );
  146. if ( !r.isValid() )
  147. {
  148. Point p( ref );
  149. if ( !p.isValid() )
  150. return false;
  151. }
  152. return true;
  153. }
  154. // Function: AREAS
  155. Value func_areas (valVector args, ValueCalc *calc, FuncExtra *e)
  156. {
  157. if (e) {
  158. if ((e->ranges[0].col1 != -1) && (e->ranges[0].row1 != -1) &&
  159. (e->ranges[0].col2 != -1) && (e->ranges[0].row2 != -1))
  160. // we have a range reference - return 1
  161. return 1;
  162. }
  163. TQString s = calc->conv()->asString (args[0]).asString();
  164. if ( s[0] != '(' || s[s.length() - 1] != ')' )
  165. return Value::errorVALUE();
  166. int l = s.length();
  167. int num = 0;
  168. TQString ref;
  169. for ( int i = 1; i < l; ++i )
  170. {
  171. if ( s[i] == ',' || s[i] == ')' )
  172. {
  173. if ( !checkRef( ref ) )
  174. return Value::errorVALUE();
  175. else
  176. {
  177. ++num;
  178. ref = "";
  179. }
  180. }
  181. else
  182. ref += s[i];
  183. }
  184. return Value (num);
  185. }
  186. // Function: CHOOSE
  187. Value func_choose (valVector args, ValueCalc *calc, FuncExtra *)
  188. {
  189. int cnt = args.count () - 1;
  190. int num = calc->conv()->asInteger (args[0]).asInteger();
  191. if ((num <= 0) || (num > cnt))
  192. return Value::errorVALUE();
  193. return args[num];
  194. }
  195. // Function: INDEX
  196. Value func_index (valVector args, ValueCalc *calc, FuncExtra *)
  197. {
  198. // first argument can be either a range, then we return a given cell's
  199. // value, or a single cell containing an array - then we return the array
  200. // element. In any case, this function can assume that the given value
  201. // is the same. Because it is.
  202. Value val = args[0];
  203. unsigned row = calc->conv()->asInteger (args[1]).asInteger() - 1;
  204. unsigned col = calc->conv()->asInteger (args[2]).asInteger() - 1;
  205. if ((row >= val.rows()) || (col >= val.columns()))
  206. return Value::errorREF();
  207. return val.element (col, row);
  208. }
  209. // Function: LOOKUP
  210. Value func_lookup (valVector args, ValueCalc *calc, FuncExtra *)
  211. {
  212. Value num = calc->conv()->asNumeric (args[0]);
  213. if (num.isArray())
  214. return Value::errorVALUE();
  215. Value lookup = args[1];
  216. Value rr = args[2];
  217. unsigned cols = lookup.columns();
  218. unsigned rows = lookup.rows();
  219. if ((cols != rr.columns()) || (rows != rr.rows()))
  220. return Value::errorVALUE();
  221. Value res = Value::errorNA();
  222. // now traverse the array and perform comparison
  223. for (unsigned r = 0; r < rows; ++r)
  224. for (unsigned c = 0; c < cols; ++c)
  225. {
  226. // update the result, return if we cross the line
  227. Value le = lookup.element (c, r);
  228. if (calc->lower (le, num) || calc->equal (num, le))
  229. res = rr.element (c, r);
  230. else
  231. return res;
  232. }
  233. return res;
  234. }
  235. // Function: COLUMN
  236. Value func_column (valVector args, ValueCalc *, FuncExtra *e)
  237. {
  238. int col = e ? e->mycol : 0;
  239. if (e && args.count())
  240. col = e->ranges[0].col1;
  241. if (col > 0)
  242. return Value (col);
  243. return Value::errorVALUE();
  244. }
  245. // Function: ROW
  246. Value func_row (valVector args, ValueCalc *, FuncExtra *e)
  247. {
  248. int row = e ? e->myrow : 0;
  249. if (e && args.count())
  250. row = e->ranges[0].row1;
  251. if (row > 0)
  252. return Value (row);
  253. return Value::errorVALUE();
  254. }
  255. // Function: COLUMNS
  256. Value func_columns (valVector, ValueCalc *, FuncExtra *e)
  257. {
  258. int col1 = e->ranges[0].col1;
  259. int col2 = e->ranges[0].col2;
  260. if ((col1 == -1) || (col2 == -1))
  261. return Value::errorVALUE();
  262. return Value (col2 - col1 + 1);
  263. }
  264. // Function: ROWS
  265. Value func_rows (valVector, ValueCalc *, FuncExtra *e)
  266. {
  267. int row1 = e->ranges[0].row1;
  268. int row2 = e->ranges[0].row2;
  269. if ((row1 == -1) || (row2 == -1))
  270. return Value::errorVALUE();
  271. return Value (row2 - row1 + 1);
  272. }
  273. // Function: INDIRECT
  274. Value func_indirect (valVector args, ValueCalc *calc, FuncExtra *e)
  275. {
  276. bool r1c1 = false;
  277. TQString ref = calc->conv()->asString (args[0]).asString();
  278. if (args.count() == 2)
  279. r1c1 = !(calc->conv()->asBoolean (args[1]).asBoolean());
  280. if (ref.isEmpty())
  281. return Value::errorVALUE();
  282. if ( r1c1 )
  283. {
  284. // TODO: translate the r1c1 style to a1 style
  285. ref = ref;
  286. }
  287. Point p (ref, e->sheet->workbook(), e->sheet);
  288. if ( !p.isValid() )
  289. return Value::errorVALUE();
  290. Cell * cell = p.cell();
  291. if (cell)
  292. return cell->value();
  293. return Value::errorVALUE();
  294. }