00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
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
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
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 );
00080 }
00081
00082
00083
00084
00085
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
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 )
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 );
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 )
00213 {
00214 cell = sheet->cellAt( column, row );
00215 kdDebug() << "Cell: " << column << ", " << row << " - " << cell->strOutText() << endl;
00216 if ( cell->isDefault() )
00217 continue;
00218
00219
00220
00221
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;
00241 }
00242 }
00243 if ( add )
00244 break;
00245
00246 criterias = conditions->next();
00247 }
00248 if ( add )
00249 {
00250 kdDebug() << "Appending cell: " << cell->strOutText() << endl;
00251 result->append( cell );
00252 }
00253 }
00254
00255 return result;
00256 }
00257
00258
00259
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
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
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
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
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
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
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
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
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
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
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
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
00995
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 }