kspread Library API Documentation

kspread_functions_database.cc

00001 /* This file is part of the KDE project
00002    Copyright (C) 1998-2002 The KSpread Team
00003                            www.koffice.org/kspread
00004 
00005    This library is free software; you can redistribute it and/or
00006    modify it under the terms of the GNU Library General Public
00007    License as published by the Free Software Foundation; either
00008    version 2 of the License.
00009 
00010    This library is distributed in the hope that it will be useful,
00011    but WITHOUT ANY WARRANTY; without even the implied warranty of
00012    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00013    Library General Public License for more details.
00014 
00015    You should have received a copy of the GNU Library General Public License
00016    along with this library; see the file COPYING.LIB.  If not, write to
00017    the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
00018    Boston, MA 02111-1307, USA.
00019 */
00020 
00021 // built-in database functions
00022 
00023 #include <stdlib.h>
00024 #include <math.h>
00025 #include <float.h>
00026 
00027 #include <kdebug.h>
00028 
00029 #include <koscript_parser.h>
00030 #include <koscript_util.h>
00031 #include <koscript_func.h>
00032 #include <koscript_synext.h>
00033 
00034 #include "kspread_cell.h"
00035 #include "kspread_sheet.h"
00036 #include "kspread_interpreter.h"
00037 #include "kspread_doc.h"
00038 #include "kspread_functions.h"
00039 #include "kspread_functions_helper.h"
00040 #include "kspread_util.h"
00041 
00042 #include <qmemarray.h>
00043 #include <qptrlist.h>
00044 #include <qrect.h>
00045 #include <qvaluelist.h>
00046 
00047 // prototypes
00048 bool kspreadfunc_daverage( KSContext & context );
00049 bool kspreadfunc_dcount( KSContext & context );
00050 bool kspreadfunc_dcounta( KSContext & context );
00051 bool kspreadfunc_dget( KSContext & context );
00052 bool kspreadfunc_dmax( KSContext & context );
00053 bool kspreadfunc_dmin( KSContext & context );
00054 bool kspreadfunc_dproduct( KSContext & context );
00055 bool kspreadfunc_dstdev( KSContext & context );
00056 bool kspreadfunc_dstdevp( KSContext & context );
00057 bool kspreadfunc_dsum( KSContext & context );
00058 bool kspreadfunc_dvar( KSContext & context );
00059 bool kspreadfunc_dvarp( KSContext & context );
00060 bool kspreadfunc_getpivotdata( KSContext & context );
00061 
00062 // registers all database functions
00063 void KSpreadRegisterDatabaseFunctions()
00064 {
00065   KSpreadFunctionRepository * repo = KSpreadFunctionRepository::self();
00066 
00067   repo->registerFunction( "DAVERAGE",     kspreadfunc_daverage );
00068   repo->registerFunction( "DCOUNT",       kspreadfunc_dcount );
00069   repo->registerFunction( "DCOUNTA",      kspreadfunc_dcounta );
00070   repo->registerFunction( "DGET",         kspreadfunc_dget );
00071   repo->registerFunction( "DMAX",         kspreadfunc_dmax );
00072   repo->registerFunction( "DMIN",         kspreadfunc_dmin );
00073   repo->registerFunction( "DPRODUCT",     kspreadfunc_dproduct );
00074   repo->registerFunction( "DSTDEV",       kspreadfunc_dstdev );
00075   repo->registerFunction( "DSTDEVP",      kspreadfunc_dstdevp );
00076   repo->registerFunction( "DSUM",         kspreadfunc_dsum );
00077   repo->registerFunction( "DVAR",         kspreadfunc_dvar );
00078   repo->registerFunction( "DVARP",        kspreadfunc_dvarp );
00079   repo->registerFunction( "GETPIVOTDATA", kspreadfunc_getpivotdata ); // partially Excel-compatible
00080 }
00081 
00082 /*********************************************************************
00083  *
00084  * Helper function to avoid problems with rounding floating point
00085  * values. Idea for this kind of solution taken from Openoffice.
00086  *
00087  *********************************************************************/
00088 
00101 bool conditionMatches( KSpreadDB::Condition &cond, KSpreadCell * cell )
00102 {
00103   if ( !cell || cell->isEmpty() || cell->isDefault() )
00104   {
00105     kdDebug() << "Match: Cell is empty " << endl;
00106     return false;
00107   }
00108 
00109   if ( cond.type == KSpreadDB::numeric && cell->value().isNumber() ) {
00110     double d = cell->value().asFloat();
00111     return conditionMatches( cond, d );
00112   }
00113   if ( cond.type == KSpreadDB::string && cell->value().isString() ) {
00114     QString d = cell->strOutText();
00115     return conditionMatches( cond, d );
00116   }
00117 
00118   return false;
00119 }
00120 
00121 int getFieldIndex( QString const & fieldname, QRect const & database, KSpreadSheet * sheet )
00122 {
00123   int r   = database.right();
00124   int row = database.top();
00125   KSpreadCell * cell = 0;
00126 
00127   kdDebug() << "Database: " << database.left() << ", " << row << ", right: " << r << endl;
00128 
00129   for ( int i = database.left(); i <= r; ++i )
00130   {
00131     cell = sheet->cellAt( i, row );
00132     if ( cell->isDefault() )
00133       continue;
00134 
00135     if ( fieldname.lower() == cell->strOutText().lower() )
00136       return i;
00137   }
00138 
00139   return -1;
00140 }
00141 
00142 void parseConditions( QPtrList<KSpreadDB::ConditionList> * result, QRect const & database, QRect const & conditions, KSpreadSheet * sheet )
00143 {
00144   int cCols  = conditions.width();
00145   int right  = conditions.right();
00146   int left   = conditions.left();
00147   int top    = conditions.top();
00148   int bottom = conditions.bottom();
00149 
00150   QMemArray<int> list( cCols );
00151   KSpreadCell * cell = 0;
00152 
00153   kdDebug() << "Top: " << top << ", Left: " << left << ", right: " << right << ", " << bottom << endl;
00154 
00155   // Save the databases indices of condition header
00156   for ( int i = left; i <= right; ++i )
00157   {
00158     cell = sheet->cellAt( i, top );
00159     if ( cell->isDefault() || cell->isEmpty() )
00160       list[i - 1] = -1;
00161     else
00162     {
00163       int p = getFieldIndex( cell->strOutText(), database, sheet );
00164       list[i - 1] = p;
00165 
00166       kdDebug() << "header: " << cell->strOutText() << ", " << list[i] << ", P: " << p << endl;
00167     }
00168   }
00169 
00170   for ( int r = top + 1; r <= bottom; ++r ) // first row are headers
00171   {
00172     KSpreadDB::ConditionList * criteria = new KSpreadDB::ConditionList();
00173 
00174     for ( int c = 0; c < cCols; ++c )
00175     {
00176       if ( list[c] == -1 )
00177         continue;
00178 
00179       KSpreadDB::Condition cond;
00180       cond.index = list[c];
00181 
00182       kdDebug() << "Cell: " << c+left << ", " << r << ", Str: "
00183                 << sheet->cellAt( c + left, r )->strOutText() << ", index: " << list[c] << endl;
00184 
00185       if( !sheet->cellAt( c + left,r )->isEmpty() )
00186       {
00187         getCond( cond, sheet->cellAt( c + left, r )->strOutText() );
00188         criteria->append( cond );
00189       }
00190     }
00191 
00192     result->append( criteria );
00193   }
00194   kdDebug() << "Criterias: " << result->count() << endl;
00195 }
00196 
00197 QPtrList<KSpreadCell> * getCellList( QRect const & db, KSpreadSheet * sheet, int column, QPtrList<KSpreadDB::ConditionList> * conditions )
00198 {
00199   kdDebug() << "***** getCellList *****" << endl;
00200 
00201   int top    = db.top();
00202   int bottom = db.bottom();
00203 
00204   QPtrList<KSpreadCell> * result = new QPtrList<KSpreadCell>();
00205   result->setAutoDelete( false ); // better not delete the cells...
00206 
00207   QValueList<KSpreadDB::Condition>::const_iterator it;
00208   QValueList<KSpreadDB::Condition>::const_iterator end;
00209   KSpreadCell * cell    = 0;
00210   KSpreadCell * conCell = 0;
00211 
00212   for ( int row = top + 1; row <= bottom; ++row ) // first row contains header
00213   {
00214     cell = sheet->cellAt( column, row );
00215     kdDebug() << "Cell: " << column << ", " << row << " - " << cell->strOutText() << endl;
00216     if ( cell->isDefault() )
00217       continue;
00218 
00219     // go through conditions
00220     //   go through criterias => all have to match
00221     //   if first condition matches => add cell, next row
00222     KSpreadDB::ConditionList * criterias = conditions->first();
00223 
00224     bool add = true;
00225     while ( criterias )
00226     {
00227       add = true;
00228 
00229       it  = criterias->begin();
00230       end = criterias->end();
00231 
00232       for ( ; it != end; ++it )
00233       {
00234         KSpreadDB::Condition cond = *it;
00235         conCell = sheet->cellAt( cond.index, row );
00236         kdDebug() << "Checking cell: " << cond.index << ", " << row << " - " << conCell->strOutText() << endl;
00237         if ( !conditionMatches( cond, conCell ) )
00238         {
00239           add = false;
00240           break; // all conditions per criteria must match, but just one criteria
00241         }
00242       }
00243       if ( add )
00244         break; // just one criteria need to match
00245 
00246       criterias = conditions->next();
00247     }
00248     if ( add )
00249     {
00250       kdDebug() << "Appending cell: " << cell->strOutText() << endl;
00251       result->append( cell );
00252     }
00253   } // for row =...
00254 
00255   return result;
00256 }
00257 
00258 
00259 // Function: DSUM
00260 bool kspreadfunc_dsum( KSContext & context )
00261 {
00262   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00263   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00264 
00265   if ( !KSUtil::checkArgumentsCount( context, 3, "dsum", true ) )
00266     return false;
00267 
00268   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00269   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00270 
00271   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00272   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00273 
00274   if ( !db.isValid() || !conditions.isValid() )
00275     return false;
00276 
00277   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00278   if ( fieldIndex == -1 )
00279     return false;
00280 
00281   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00282 
00283   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00284   cond->setAutoDelete( true );
00285 
00286   parseConditions( cond, db.range, conditions.range, sheet );
00287 
00288   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00289 
00290   double sum = 0;
00291 
00292   KSpreadCell * cell = cells->first();
00293   while ( cell )
00294   {
00295     if ( cell->value().isNumber() )
00296       sum += cell->value().asFloat();
00297 
00298     cell = cells->next();
00299   }
00300 
00301   context.setValue( new KSValue( sum ) );
00302 
00303   delete cond;
00304   delete cells;
00305 
00306   return true;
00307 }
00308 
00309 // Function: DAVERAGE
00310 bool kspreadfunc_daverage( KSContext & context )
00311 {
00312   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00313   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00314 
00315   if ( !KSUtil::checkArgumentsCount( context, 3, "DAVERAGE", true ) )
00316     return false;
00317 
00318   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00319   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00320 
00321   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00322   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00323 
00324   if ( !db.isValid() || !conditions.isValid() )
00325     return false;
00326 
00327   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00328   if ( fieldIndex == -1 )
00329     return false;
00330 
00331   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00332 
00333   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00334   cond->setAutoDelete( true );
00335 
00336   parseConditions( cond, db.range, conditions.range, sheet );
00337 
00338   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00339 
00340   int    count = 0;
00341   double sum   = 0;
00342 
00343   KSpreadCell * cell = cells->first();
00344   while ( cell )
00345   {
00346     if ( cell->value().isNumber() )
00347     {
00348       ++count;
00349       sum += cell->value().asFloat();
00350     }
00351 
00352     cell = cells->next();
00353   }
00354 
00355   context.setValue( new KSValue( (double) ( sum / count) ) );
00356 
00357   delete cond;
00358   delete cells;
00359 
00360   return true;
00361 }
00362 
00363 // Function: DCOUNT
00364 bool kspreadfunc_dcount( KSContext & context )
00365 {
00366   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00367   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00368 
00369   if ( !KSUtil::checkArgumentsCount( context, 3, "DCOUNT", true ) )
00370     return false;
00371 
00372   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00373   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00374 
00375   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00376   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00377 
00378   if ( !db.isValid() || !conditions.isValid() )
00379     return false;
00380 
00381   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00382   if ( fieldIndex == -1 )
00383     return false;
00384 
00385   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00386 
00387   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00388   cond->setAutoDelete( true );
00389 
00390   parseConditions( cond, db.range, conditions.range, sheet );
00391 
00392   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00393 
00394   int count = 0;
00395 
00396   KSpreadCell * cell = cells->first();
00397   while ( cell )
00398   {
00399     if ( cell->value().isNumber() )
00400     {
00401       ++count;
00402     }
00403 
00404     cell = cells->next();
00405   }
00406 
00407   context.setValue( new KSValue( count ) );
00408 
00409   delete cond;
00410   delete cells;
00411 
00412   return true;
00413 }
00414 
00415 // Function: DCOUNTA
00416 bool kspreadfunc_dcounta( KSContext & context )
00417 {
00418   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00419   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00420 
00421   if ( !KSUtil::checkArgumentsCount( context, 3, "DCOUNTA", true ) )
00422     return false;
00423 
00424   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00425   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00426 
00427   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00428   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00429 
00430   if ( !db.isValid() || !conditions.isValid() )
00431     return false;
00432 
00433   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00434   if ( fieldIndex == -1 )
00435     return false;
00436 
00437   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00438 
00439   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00440   cond->setAutoDelete( true );
00441 
00442   parseConditions( cond, db.range, conditions.range, sheet );
00443 
00444   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00445 
00446   int count = 0;
00447 
00448   KSpreadCell * cell = cells->first();
00449   while ( cell )
00450   {
00451     if ( !cell->isEmpty() )
00452       ++count;
00453 
00454     cell = cells->next();
00455   }
00456 
00457   context.setValue( new KSValue( count ) );
00458 
00459   delete cond;
00460   delete cells;
00461 
00462   return true;
00463 }
00464 
00465 // Function: DGET
00466 bool kspreadfunc_dget( KSContext & context )
00467 {
00468   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00469   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00470 
00471   if ( !KSUtil::checkArgumentsCount( context, 3, "DGET", true ) )
00472     return false;
00473 
00474   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00475   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00476 
00477   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00478   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00479 
00480   if ( !db.isValid() || !conditions.isValid() )
00481     return false;
00482 
00483   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00484   if ( fieldIndex == -1 )
00485     return false;
00486 
00487   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00488   cond->setAutoDelete( true );
00489 
00490   parseConditions( cond, db.range, conditions.range, sheet );
00491 
00492   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00493 
00494   KSValue value;
00495   int count = 0;
00496 
00497   KSpreadCell * cell = cells->first();
00498 
00499   while ( cell )
00500   {
00501     if ( !cell->isEmpty() )
00502     {
00503       ++count;
00504       if ( count > 1 )
00505         return false;
00506 
00507       if ( cell->value().isNumber() )
00508         value.setValue( cell->value().asFloat() );
00509       else if ( cell->value().isString() )
00510         value.setValue( cell->value().asString() );
00511       else if ( cell->value().isBoolean() )
00512         value.setValue( cell->value().asBoolean() );
00513       else
00514         return false;
00515     }
00516 
00517     cell = cells->next();
00518   }
00519 
00520   if ( count == 0 )
00521     return false;
00522 
00523   context.setValue( new KSValue( value ) );
00524 
00525   delete cond;
00526   delete cells;
00527 
00528   return true;
00529 }
00530 
00531 // Function: DMAX
00532 bool kspreadfunc_dmax( KSContext & context )
00533 {
00534   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00535   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00536 
00537   if ( !KSUtil::checkArgumentsCount( context, 3, "DMAX", true ) )
00538     return false;
00539 
00540   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00541   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00542 
00543   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00544   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00545 
00546   if ( !db.isValid() || !conditions.isValid() )
00547     return false;
00548 
00549   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00550   if ( fieldIndex == -1 )
00551     return false;
00552 
00553   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00554 
00555   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00556   cond->setAutoDelete( true );
00557 
00558   parseConditions( cond, db.range, conditions.range, sheet );
00559 
00560   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00561 
00562   double max = 0.0;
00563 
00564   KSpreadCell * cell = cells->first();
00565   if ( cell && cell->value().isNumber() )
00566     max = cell->value().asFloat();
00567 
00568   while ( cell )
00569   {
00570     if ( cell->value().isNumber() )
00571     {
00572       if ( cell->value().asFloat() > max )
00573         max = cell->value().asFloat();
00574     }
00575 
00576     cell = cells->next();
00577   }
00578 
00579   context.setValue( new KSValue( max ) );
00580 
00581   delete cond;
00582   delete cells;
00583 
00584   return true;
00585 }
00586 
00587 // Function: DMIN
00588 bool kspreadfunc_dmin( KSContext & context )
00589 {
00590   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00591   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00592 
00593   if ( !KSUtil::checkArgumentsCount( context, 3, "DMIN", true ) )
00594     return false;
00595 
00596   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00597   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00598 
00599   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00600   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00601 
00602   if ( !db.isValid() || !conditions.isValid() )
00603     return false;
00604 
00605   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00606   if ( fieldIndex == -1 )
00607     return false;
00608 
00609   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00610 
00611   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00612   cond->setAutoDelete( true );
00613 
00614   parseConditions( cond, db.range, conditions.range, sheet );
00615 
00616   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00617 
00618   double min = 0.0;
00619 
00620   KSpreadCell * cell = cells->first();
00621   if ( cell && cell->value().isNumber() )
00622     min = cell->value().asFloat();
00623 
00624   while ( cell )
00625   {
00626     if ( cell->value().isNumber() )
00627     {
00628       if ( cell->value().asFloat() < min )
00629         min = cell->value().asFloat();
00630     }
00631 
00632     cell = cells->next();
00633   }
00634 
00635   context.setValue( new KSValue( min ) );
00636 
00637   delete cond;
00638   delete cells;
00639 
00640   return true;
00641 }
00642 
00643 // Function: DPRODUCT
00644 bool kspreadfunc_dproduct( KSContext & context )
00645 {
00646   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00647   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00648 
00649   if ( !KSUtil::checkArgumentsCount( context, 3, "DPRODUCT", true ) )
00650     return false;
00651 
00652   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00653   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00654 
00655   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00656   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00657 
00658   if ( !db.isValid() || !conditions.isValid() )
00659     return false;
00660 
00661   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00662   if ( fieldIndex == -1 )
00663     return false;
00664 
00665   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00666 
00667   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00668   cond->setAutoDelete( true );
00669 
00670   parseConditions( cond, db.range, conditions.range, sheet );
00671 
00672   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00673 
00674   double product = 1.0;
00675   int count = 0;
00676 
00677   KSpreadCell * cell = cells->first();
00678 
00679   while ( cell )
00680   {
00681     if ( cell->value().isNumber() )
00682     {
00683       ++count;
00684       product *= cell->value().asFloat();
00685     }
00686 
00687     cell = cells->next();
00688   }
00689 
00690   if ( count == 0 )
00691     return false;
00692 
00693   context.setValue( new KSValue( product ) );
00694 
00695   delete cond;
00696   delete cells;
00697 
00698   return true;
00699 }
00700 
00701 // Function: DSTDEV
00702 bool kspreadfunc_dstdev( KSContext & context )
00703 {
00704   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00705   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00706 
00707   if ( !KSUtil::checkArgumentsCount( context, 3, "DSTDEV", true ) )
00708     return false;
00709 
00710   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00711   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00712 
00713   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00714   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00715 
00716   if ( !db.isValid() || !conditions.isValid() )
00717     return false;
00718 
00719   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00720   if ( fieldIndex == -1 )
00721     return false;
00722 
00723   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00724 
00725   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00726   cond->setAutoDelete( true );
00727 
00728   parseConditions( cond, db.range, conditions.range, sheet );
00729 
00730   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00731 
00732   double sum = 0.0;
00733   int count = 0;
00734 
00735   KSpreadCell * cell = cells->first();
00736 
00737   while ( cell )
00738   {
00739     if ( cell->value().isNumber() )
00740     {
00741       sum += cell->value().asFloat();
00742       ++count;
00743     }
00744 
00745     cell = cells->next();
00746   }
00747 
00748   if ( count == 0 )
00749     return false;
00750 
00751   double average = sum / count;
00752   double result = 0.0;
00753 
00754   cell = cells->first();
00755 
00756   while ( cell )
00757   {
00758     if ( cell->value().isNumber() )
00759     {
00760       result += ( ( cell->value().asFloat() - average ) * ( cell->value().asFloat() - average ) );
00761     }
00762 
00763     cell = cells->next();
00764   }
00765 
00766 
00767   context.setValue( new KSValue( sqrt( result / ( ( double )( count - 1 ) ) ) ) );
00768 
00769   delete cond;
00770   delete cells;
00771 
00772   return true;
00773 }
00774 
00775 // Function: DSTDEVP
00776 bool kspreadfunc_dstdevp( KSContext & context )
00777 {
00778   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00779   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00780 
00781   if ( !KSUtil::checkArgumentsCount( context, 3, "DSTDEVP", true ) )
00782     return false;
00783 
00784   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00785   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00786 
00787   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00788   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00789 
00790   if ( !db.isValid() || !conditions.isValid() )
00791     return false;
00792 
00793   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00794   if ( fieldIndex == -1 )
00795     return false;
00796 
00797   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00798 
00799   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00800   cond->setAutoDelete( true );
00801 
00802   parseConditions( cond, db.range, conditions.range, sheet );
00803 
00804   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00805 
00806   double sum = 0.0;
00807   int count = 0;
00808 
00809   KSpreadCell * cell = cells->first();
00810 
00811   while ( cell )
00812   {
00813     if ( cell->value().isNumber() )
00814     {
00815       sum += cell->value().asFloat();
00816       ++count;
00817     }
00818 
00819     cell = cells->next();
00820   }
00821 
00822   if ( count == 0 )
00823     return false;
00824 
00825   double average = sum / count;
00826   double result = 0.0;
00827 
00828   cell = cells->first();
00829 
00830   while ( cell )
00831   {
00832     if ( cell->value().isNumber() )
00833     {
00834       result += ( ( cell->value().asFloat() - average ) * ( cell->value().asFloat() - average ) );
00835     }
00836 
00837     cell = cells->next();
00838   }
00839 
00840   context.setValue( new KSValue( sqrt( result / count ) ) );
00841 
00842   delete cond;
00843   delete cells;
00844 
00845   return true;
00846 }
00847 
00848 // Function: DVAR
00849 bool kspreadfunc_dvar( KSContext & context )
00850 {
00851   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00852   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00853 
00854   if ( !KSUtil::checkArgumentsCount( context, 3, "DVAR", true ) )
00855     return false;
00856 
00857   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00858   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00859 
00860   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00861   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00862 
00863   if ( !db.isValid() || !conditions.isValid() )
00864     return false;
00865 
00866   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00867   if ( fieldIndex == -1 )
00868     return false;
00869 
00870   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00871 
00872   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00873   cond->setAutoDelete( true );
00874 
00875   parseConditions( cond, db.range, conditions.range, sheet );
00876 
00877   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00878 
00879   double sum = 0.0;
00880   int count = 0;
00881 
00882   KSpreadCell * cell = cells->first();
00883 
00884   while ( cell )
00885   {
00886     if ( cell->value().isNumber() )
00887     {
00888       sum += cell->value().asFloat();
00889       ++count;
00890     }
00891 
00892     cell = cells->next();
00893   }
00894 
00895   if ( count == 0 )
00896     return false;
00897 
00898   double average = sum / count;
00899   double result = 0.0;
00900 
00901   cell = cells->first();
00902 
00903   while ( cell )
00904   {
00905     if ( cell->value().isNumber() )
00906     {
00907       result += ( ( cell->value().asFloat() - average ) * ( cell->value().asFloat() - average ) );
00908     }
00909 
00910     cell = cells->next();
00911   }
00912 
00913   context.setValue( new KSValue( result / (double) (count - 1) ) );
00914 
00915   delete cond;
00916   delete cells;
00917 
00918   return true;
00919 }
00920 
00921 // Function: DVARP
00922 bool kspreadfunc_dvarp( KSContext & context )
00923 {
00924   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00925   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
00926 
00927   if ( !KSUtil::checkArgumentsCount( context, 3, "DVARP", true ) )
00928     return false;
00929 
00930   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
00931   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
00932 
00933   KSpreadRange db( extra[0]->stringValue(), map, sheet );
00934   KSpreadRange conditions( extra[2]->stringValue(), map, sheet );
00935 
00936   if ( !db.isValid() || !conditions.isValid() )
00937     return false;
00938 
00939   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
00940   if ( fieldIndex == -1 )
00941     return false;
00942 
00943   kdDebug() << "Fieldindex: " << fieldIndex << endl;
00944 
00945   QPtrList<KSpreadDB::ConditionList> * cond = new QPtrList<KSpreadDB::ConditionList>();
00946   cond->setAutoDelete( true );
00947 
00948   parseConditions( cond, db.range, conditions.range, sheet );
00949 
00950   QPtrList<KSpreadCell> * cells = getCellList( db.range, sheet, fieldIndex, cond );
00951 
00952   double sum = 0.0;
00953   int count = 0;
00954 
00955   KSpreadCell * cell = cells->first();
00956 
00957   while ( cell )
00958   {
00959     if ( cell->value().isNumber() )
00960     {
00961       sum += cell->value().asFloat();
00962       ++count;
00963     }
00964 
00965     cell = cells->next();
00966   }
00967 
00968   if ( count == 0 )
00969     return false;
00970 
00971   double average = sum / count;
00972   double result = 0.0;
00973 
00974   cell = cells->first();
00975 
00976   while ( cell )
00977   {
00978     if ( cell->value().isNumber() )
00979     {
00980       result += ( ( cell->value().asFloat() - average ) * ( cell->value().asFloat() - average ) );
00981     }
00982 
00983     cell = cells->next();
00984   }
00985 
00986   context.setValue( new KSValue( result / count ) );
00987 
00988   delete cond;
00989   delete cells;
00990 
00991   return true;
00992 }
00993 
00994 // Function: GETPIVOTDATA
00995 // FIXME implement more things with this, see Excel !
00996 bool kspreadfunc_getpivotdata( KSContext & context )
00997 {
00998   QValueList<KSValue::Ptr> & args  = context.value()->listValue();
00999   QValueList<KSValue::Ptr> & extra = context.extraData()->listValue();
01000 
01001   if ( !KSUtil::checkArgumentsCount( context, 2, "GETPIVOTDATA", true ) )
01002     return false;
01003 
01004   KSpreadMap *   map   = ((KSpreadInterpreter *) context.interpreter() )->document()->map();
01005   KSpreadSheet * sheet = ((KSpreadInterpreter *) context.interpreter() )->sheet();
01006 
01007   KSpreadRange db( extra[0]->stringValue(), map, sheet );
01008   if ( !db.isValid()  )
01009     return false;
01010 
01011   int fieldIndex = getFieldIndex( args[1]->stringValue(), db.range, sheet );
01012   if ( fieldIndex == -1 )
01013     return false;
01014 
01015   kdDebug() << "Fieldindex: " << fieldIndex << endl;
01016 
01017   KSpreadCell * cell = sheet->cellAt( fieldIndex, db.range.bottom() );
01018   if( cell->isEmpty() )
01019     return false;
01020 
01021   KSValue value;
01022   if ( cell->value().isNumber() )
01023     value.setValue( cell->value().asFloat() );
01024   else if ( cell->value().isString() )
01025     value.setValue( cell->value().asString() );
01026   else if ( cell->value().isBoolean() )
01027     value.setValue( cell->value().asBoolean() );
01028   else
01029     return false;
01030 
01031   context.setValue( new KSValue( value ) );
01032 
01033   return true;
01034 }
KDE Logo
This file is part of the documentation for kspread Library Version 1.4.2.
Documentation copyright © 1996-2004 the KDE developers.
Generated on Mon Feb 13 09:43:09 2006 by doxygen 1.4.2 written by Dimitri van Heesch, © 1997-2003