00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024 #include "kspread_cell.h"
00025 #include "kspread_sheet.h"
00026 #include "kspread_util.h"
00027 #include "kspread_value.h"
00028
00029 #include "functions.h"
00030 #include "valuecalc.h"
00031 #include "valueconverter.h"
00032
00033 using namespace KSpread;
00034
00035
00036 Value func_address (valVector args, ValueCalc *calc, FuncExtra *);
00037 Value func_areas (valVector args, ValueCalc *calc, FuncExtra *);
00038 Value func_choose (valVector args, ValueCalc *calc, FuncExtra *);
00039 Value func_column (valVector args, ValueCalc *calc, FuncExtra *);
00040 Value func_columns (valVector args, ValueCalc *calc, FuncExtra *);
00041 Value func_index (valVector args, ValueCalc *calc, FuncExtra *);
00042 Value func_indirect (valVector args, ValueCalc *calc, FuncExtra *);
00043 Value func_lookup (valVector args, ValueCalc *calc, FuncExtra *);
00044 Value func_row (valVector args, ValueCalc *calc, FuncExtra *);
00045 Value func_rows (valVector args, ValueCalc *calc, FuncExtra *);
00046
00047
00048 void RegisterReferenceFunctions()
00049 {
00050 FunctionRepository* repo = FunctionRepository::self();
00051 Function *f;
00052
00053 f = new Function ("ADDRESS", func_address);
00054 f->setParamCount (2, 5);
00055 repo->add (f);
00056 f = new Function ("AREAS", func_areas);
00057 f->setParamCount (1);
00058 f->setNeedsExtra (true);
00059 repo->add (f);
00060 f = new Function ("CHOOSE", func_choose);
00061 f->setParamCount (2, -1);
00062 f->setAcceptArray ();
00063 repo->add (f);
00064 f = new Function ("COLUMN", func_column);
00065 f->setParamCount (0, 1);
00066 repo->add (f);
00067 f = new Function ("COLUMNS", func_columns);
00068 f->setParamCount (1);
00069 f->setAcceptArray ();
00070 f->setNeedsExtra (true);
00071 repo->add (f);
00072 f = new Function ("INDEX", func_index);
00073 f->setParamCount (3);
00074 f->setAcceptArray ();
00075 repo->add (f);
00076 f = new Function ("INDIRECT", func_indirect);
00077 f->setParamCount (1, 2);
00078 f->setNeedsExtra (true);
00079 repo->add (f);
00080 f = new Function ("LOOKUP", func_lookup);
00081 f->setParamCount (3);
00082 f->setAcceptArray ();
00083 repo->add (f);
00084 f = new Function ("ROW", func_row);
00085 f->setParamCount (0, 1);
00086 repo->add (f);
00087 f = new Function ("ROWS", func_rows);
00088 f->setParamCount (1);
00089 f->setAcceptArray ();
00090 f->setNeedsExtra (true);
00091 repo->add (f);
00092 }
00093
00094
00095 Value func_address (valVector args, ValueCalc *calc, FuncExtra *)
00096 {
00097 bool r1c1 = false;
00098 QString sheetName;
00099 int absNum = 1;
00100 if (args.count() > 2)
00101 absNum = calc->conv()->asInteger (args[2]).asInteger();
00102 if (args.count() > 3)
00103 r1c1 = !(calc->conv()->asBoolean (args[3]).asBoolean());
00104 if (args.count() == 5)
00105 sheetName = calc->conv()->asString (args[4]).asString();
00106
00107 QString result;
00108 int row = calc->conv()->asInteger (args[0]).asInteger();
00109 int col = calc->conv()->asInteger (args[1]).asInteger();
00110
00111 if ( !sheetName.isEmpty() )
00112 {
00113 result += sheetName;
00114 result += "!";
00115 }
00116
00117 if ( r1c1 )
00118 {
00119
00120 bool abs = false;
00121 if ( absNum == 1 || absNum == 2 )
00122 abs = true;
00123
00124 result += 'R';
00125 if ( !abs )
00126 result += '[';
00127 result += QString::number( row );
00128
00129 if ( !abs )
00130 result += ']';
00131
00132
00133 abs = false;
00134 if ( absNum == 1 || absNum == 3 )
00135 abs = true;
00136
00137 result += 'C';
00138 if ( !abs )
00139 result += '[';
00140 result += QString::number( col );
00141
00142 if ( !abs )
00143 result += ']';
00144 }
00145 else
00146 {
00147 bool abs = false;
00148 if ( absNum == 1 || absNum == 3 )
00149 abs = true;
00150
00151 if ( abs )
00152 result += '$';
00153
00154 result += Cell::columnName( col );
00155
00156 abs = false;
00157 if ( absNum == 1 || absNum == 2 )
00158 abs = true;
00159
00160 if ( abs )
00161 result += '$';
00162
00163 result += QString::number( row );
00164 }
00165
00166 return Value (result);
00167 }
00168
00169 bool checkRef( QString const & ref )
00170 {
00171 Range r( ref );
00172 if ( !r.isValid() )
00173 {
00174 Point p( ref );
00175 if ( !p.isValid() )
00176 return false;
00177 }
00178 return true;
00179 }
00180
00181
00182 Value func_areas (valVector args, ValueCalc *calc, FuncExtra *e)
00183 {
00184 if (e) {
00185 if ((e->ranges[0].col1 != -1) && (e->ranges[0].row1 != -1) &&
00186 (e->ranges[0].col2 != -1) && (e->ranges[0].row2 != -1))
00187
00188 return 1;
00189 }
00190
00191 QString s = calc->conv()->asString (args[0]).asString();
00192 if ( s[0] != '(' || s[s.length() - 1] != ')' )
00193 return Value::errorVALUE();
00194
00195 int l = s.length();
00196
00197 int num = 0;
00198 QString ref;
00199 for ( int i = 1; i < l; ++i )
00200 {
00201 if ( s[i] == ',' || s[i] == ')' )
00202 {
00203 if ( !checkRef( ref ) )
00204 return Value::errorVALUE();
00205 else
00206 {
00207 ++num;
00208 ref = "";
00209 }
00210 }
00211 else
00212 ref += s[i];
00213 }
00214
00215 return Value (num);
00216 }
00217
00218
00219 Value func_choose (valVector args, ValueCalc *calc, FuncExtra *)
00220 {
00221 int cnt = args.count () - 1;
00222 int num = calc->conv()->asInteger (args[0]).asInteger();
00223 if ((num <= 0) || (num > cnt))
00224 return Value::errorVALUE();
00225 return args[num];
00226 }
00227
00228
00229 Value func_index (valVector args, ValueCalc *calc, FuncExtra *)
00230 {
00231
00232
00233
00234
00235
00236 Value val = args[0];
00237 unsigned row = calc->conv()->asInteger (args[1]).asInteger() - 1;
00238 unsigned col = calc->conv()->asInteger (args[2]).asInteger() - 1;
00239 if ((row >= val.rows()) || (col >= val.columns()))
00240 return Value::errorREF();
00241 return val.element (col, row);
00242 }
00243
00244
00245 Value func_lookup (valVector args, ValueCalc *calc, FuncExtra *)
00246 {
00247 Value num = calc->conv()->asNumeric (args[0]);
00248 if (num.isArray())
00249 return Value::errorVALUE();
00250 Value lookup = args[1];
00251 Value rr = args[2];
00252 unsigned cols = lookup.columns();
00253 unsigned rows = lookup.rows();
00254 if ((cols != rr.columns()) || (rows != rr.rows()))
00255 return Value::errorVALUE();
00256 Value res;
00257
00258
00259 for (unsigned r = 0; r < rows; ++r)
00260 for (unsigned c = 0; c < cols; ++c)
00261 {
00262
00263 Value le = lookup.element (c, r);
00264 if (calc->lower (lookup, le) || calc->equal (lookup, le))
00265 res = rr.element (c, r);
00266 else
00267 return res;
00268 }
00269 return res;
00270 }
00271
00272
00273 Value func_column (valVector args, ValueCalc *, FuncExtra *e)
00274 {
00275 int col = e ? e->mycol : 0;
00276 if (e && args.count())
00277 col = e->ranges[0].col1;
00278 if (col > 0)
00279 return Value (col);
00280 return Value::errorVALUE();
00281 }
00282
00283
00284 Value func_row (valVector args, ValueCalc *, FuncExtra *e)
00285 {
00286 int row = e ? e->myrow : 0;
00287 if (e && args.count())
00288 row = e->ranges[0].row1;
00289 if (row > 0)
00290 return Value (row);
00291 return Value::errorVALUE();
00292 }
00293
00294
00295 Value func_columns (valVector, ValueCalc *, FuncExtra *e)
00296 {
00297 int col1 = e->ranges[0].col1;
00298 int col2 = e->ranges[0].col2;
00299 if ((col1 == -1) || (col2 == -1))
00300 return Value::errorVALUE();
00301 return Value (col2 - col1 + 1);
00302 }
00303
00304
00305 Value func_rows (valVector, ValueCalc *, FuncExtra *e)
00306 {
00307 int row1 = e->ranges[0].row1;
00308 int row2 = e->ranges[0].row2;
00309 if ((row1 == -1) || (row2 == -1))
00310 return Value::errorVALUE();
00311 return Value (row2 - row1 + 1);
00312 }
00313
00314
00315
00316 Value func_indirect (valVector args, ValueCalc *calc, FuncExtra *e)
00317 {
00318 bool r1c1 = false;
00319 QString ref = calc->conv()->asString (args[4]).asString();
00320 if (args.count() == 2)
00321 r1c1 = !(calc->conv()->asBoolean (args[1]).asBoolean());
00322
00323 if (ref.isEmpty())
00324 return Value::errorVALUE();
00325
00326 if ( r1c1 )
00327 {
00328
00329 ref = ref;
00330 }
00331
00332 Point p (ref, e->sheet->workbook(), e->sheet);
00333
00334 if ( !p.isValid() )
00335 return Value::errorVALUE();
00336
00337 Cell * cell = p.cell();
00338 if (cell)
00339 return cell->value();
00340 return Value::errorVALUE();
00341 }
00342