kspread

kspread_functions_reference.cc

00001 /* This file is part of the KDE project
00002    Copyright (C) 1998-2002 The KSpread Team
00003                            www.koffice.org/kspread
00004    Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
00005 
00006    This library is free software; you can redistribute it and/or
00007    modify it under the terms of the GNU Library General Public
00008    License as published by the Free Software Foundation; either
00009    version 2 of the License.
00010 
00011    This library is distributed in the hope that it will be useful,
00012    but WITHOUT ANY WARRANTY; without even the implied warranty of
00013    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00014    Library General Public License for more details.
00015 
00016    You should have received a copy of the GNU Library General Public License
00017    along with this library; see the file COPYING.LIB.  If not, write to
00018    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00019  * Boston, MA 02110-1301, USA.
00020 */
00021 
00022 // built-in reference functions
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 // prototypes (sorted alphabetically)
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 // registers all reference functions
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 // Function: ADDRESS
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     // row first
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     // column
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 // Function: AREAS
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       // we have a range reference - return 1
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 // Function: CHOOSE
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 // Function: INDEX
00229 Value func_index (valVector args, ValueCalc *calc, FuncExtra *)
00230 {
00231   // first argument can be either a range, then we return a given cell's
00232   // value, or a single cell containing an array - then we return the array
00233   // element. In any case, this function can assume that the given value
00234   // is the same. Because it is.
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 // Function: LOOKUP
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   // now traverse the array and perform comparison
00259   for (unsigned r = 0; r < rows; ++r)
00260     for (unsigned c = 0; c < cols; ++c)
00261     {
00262       // update the result, return if we cross the line
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 // Function: COLUMN
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 // Function: ROW
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 // Function: COLUMNS
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 // Function: ROWS
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 // Function: INDIRECT
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     // TODO: translate the r1c1 style to a1 style
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 
KDE Home | KDE Accessibility Home | Description of Access Keys