00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029 #include <assert.h>
00030
00031 #include <qcheckbox.h>
00032 #include <qcombobox.h>
00033 #include <qlabel.h>
00034 #include <qlayout.h>
00035 #include <qpushbutton.h>
00036
00037 #include <kdebug.h>
00038 #include <kdialogbase.h>
00039 #include <kmessagebox.h>
00040
00041 #include <koscript.h>
00042
00043 #include "kspread_dlg_cons.h"
00044
00045 #include <kspread_canvas.h>
00046 #include <kspread_doc.h>
00047 #include <kspread_global.h>
00048 #include <kspread_interpreter.h>
00049 #include <kspread_locale.h>
00050 #include <kspread_selection.h>
00051 #include <kspread_sheet.h>
00052 #include <kspread_util.h>
00053 #include <kspread_view.h>
00054
00055
00056 KSpreadConsolidate::KSpreadConsolidate( KSpreadView* parent, const char* name )
00057 : KDialogBase( parent, name, false, i18n("Consolidate"), Ok|Cancel )
00058 {
00059 m_pView = parent;
00060
00061 QWidget* page = new QWidget( this );
00062 setMainWidget( page );
00063
00064 QGridLayout *grid1 = new QGridLayout( page, 12, 2, marginHint(), spacingHint() );
00065
00066 QLabel* tmpQLabel;
00067 tmpQLabel = new QLabel( page, "Label_1" );
00068 grid1->addWidget(tmpQLabel,0,0);
00069 tmpQLabel->setText( i18n("&Function:") );
00070
00071 m_pFunction = new QComboBox( page );
00072 grid1->addWidget(m_pFunction,1,0);
00073 tmpQLabel->setBuddy(m_pFunction);
00074
00075 m_pFunction->insertItem( i18n("Sum"), Sum );
00076 m_pFunction->insertItem( i18n("Average"), Average );
00077 m_pFunction->insertItem( i18n("Count"), Count );
00078 m_pFunction->insertItem( i18n("Max"), Max );
00079 m_pFunction->insertItem( i18n("Min"), Min );
00080 m_pFunction->insertItem( i18n("Product"), Product );
00081 m_pFunction->insertItem( i18n("Standard Deviation"), StdDev );
00082 m_pFunction->insertItem( i18n("Variance"), Var );
00083
00084 tmpQLabel = new QLabel( page, "Label_1" );
00085 tmpQLabel->setText( i18n("Re&ference:") );
00086 grid1->addWidget(tmpQLabel,2,0);
00087
00088 m_pRef = new QLineEdit( page );
00089 grid1->addWidget(m_pRef,3,0);
00090 tmpQLabel->setBuddy(m_pRef);
00091
00092 tmpQLabel = new QLabel( page, "Label_1" );
00093 grid1->addWidget(tmpQLabel,4,0);
00094 tmpQLabel->setText( i18n("&Entered references:") );
00095
00096 m_pRefs = new QListBox( page );
00097 grid1->addMultiCellWidget( m_pRefs,5,8,0,0);
00098 tmpQLabel->setBuddy(m_pRefs);
00099
00100 m_pRow = new QCheckBox( i18n("&Description in row"), page );
00101 grid1->addWidget( m_pRow,9,0);
00102 m_pCol = new QCheckBox( i18n("De&scription in column"), page );
00103 grid1->addWidget(m_pCol,10,0);
00104 m_pCopy = new QCheckBox( i18n("Co&py data"), page );
00105 grid1->addWidget(m_pCopy,11,0);
00106
00107 m_pAdd = new QPushButton( i18n("&Add"), page );
00108 grid1->addWidget(m_pAdd,2,1);
00109 m_pRemove = new QPushButton( i18n("&Remove"), page );
00110 grid1->addWidget(m_pRemove,3,1);
00111
00112
00113 connect( m_pAdd, SIGNAL( clicked() ), this, SLOT( slotAdd() ) );
00114 connect( m_pRemove, SIGNAL( clicked() ), this, SLOT( slotRemove() ) );
00115 connect( m_pRef, SIGNAL( returnPressed() ), this, SLOT( slotReturnPressed() ) );
00116
00117 connect( m_pView, SIGNAL( sig_selectionChanged( KSpreadSheet*, const QRect& ) ),
00118 this, SLOT( slotSelectionChanged( KSpreadSheet*, const QRect& ) ) );
00119 }
00120
00121 KSpreadConsolidate::~KSpreadConsolidate()
00122 {
00123 kdDebug(36001)<<"KSpreadConsolidate::~KSpreadConsolidate()\n";
00124 }
00125
00126 enum Description { D_ROW, D_COL, D_NONE, D_BOTH };
00127
00128 struct st_cell
00129 {
00130 QString xdesc;
00131 QString ydesc;
00132 KSpreadCell* cell;
00133 QString sheet;
00134 int x;
00135 int y;
00136 };
00137
00138 void KSpreadConsolidate::slotOk()
00139 {
00140 m_pView->doc()->emitBeginOperation( false );
00141
00142 KSpreadMap *map = m_pView->doc()->map();
00143
00144 KSpreadSheet* sheet = m_pView->activeSheet();
00145 int dx = m_pView->selectionInfo()->selection().left();
00146 int dy = m_pView->selectionInfo()->selection().top();
00147
00148 QString function;
00149
00150 switch( m_pFunction->currentItem() )
00151 {
00152 case Sum: function = "SUM"; break;
00153 case Average: function = "AVERAGE"; break;
00154 case Count: function = "COUNT"; break;
00155 case Max: function = "MAX"; break;
00156 case Min: function = "MIN"; break;
00157 case Product: function = "PRODUCT"; break;
00158 case StdDev: function = "STDDEV"; break;
00159 case Var: function = "VARIANCE"; break;
00160 default: break;
00161 }
00162
00163 QStringList r = refs();
00164 QValueList<KSpreadRange> ranges;
00165 QStringList::Iterator s = r.begin();
00166 for( ; s != r.end(); ++s )
00167 {
00168 KSpreadRange r( *s, map );
00169
00170 Q_ASSERT( r.isValid() );
00171
00172 if ( r.sheet == 0 )
00173 {
00174 r.sheet = sheet;
00175 r.sheetName = sheet->sheetName();
00176 }
00177 ranges.append( r );
00178 }
00179
00180 Description desc;
00181 if ( m_pRow->isChecked() && m_pCol->isChecked() )
00182 desc = D_BOTH;
00183 else if ( m_pRow->isChecked() )
00184 desc = D_ROW;
00185 else if ( m_pCol->isChecked() )
00186 desc = D_COL;
00187 else
00188 desc = D_NONE;
00189
00190
00191 Q_ASSERT( ranges.count() > 0 );
00192 QValueList<KSpreadRange>::Iterator it = ranges.begin();
00193 int w = (*it).range.right() - (*it).range.left() + 1;
00194 int h = (*it).range.bottom() - (*it).range.top() + 1;
00195 if ( w <= ( ( desc == D_BOTH || desc == D_COL ) ? 1 : 0 ) ||
00196 h <= ( ( desc == D_BOTH || desc == D_ROW ) ? 1 : 0 ) )
00197 {
00198 m_pView->slotUpdateView( m_pView->activeSheet() );
00199 KMessageBox::error( this, i18n( "The range\n%1\nis too small" ).arg( *( r.begin() ) ));
00200 return;
00201 }
00202
00203 if( (*it).range.bottom()==KS_rowMax || (*it).range.right()== KS_colMax )
00204 {
00205 m_pView->slotUpdateView( m_pView->activeSheet() );
00206 KMessageBox::error( this, i18n( "The range\n%1\nis too large" ).arg( *( r.begin() ) ));
00207 return;
00208 }
00209
00210 ++it;
00211 int i = 1;
00212 for( ; it != ranges.end(); ++it, i++ )
00213 {
00214 int w2 = (*it).range.right() - (*it).range.left() + 1;
00215 int h2 = (*it).range.bottom() - (*it).range.top() + 1;
00216 if((*it).range.bottom()==KS_rowMax || (*it).range.right()== KS_colMax)
00217 {
00218 m_pView->slotUpdateView( m_pView->activeSheet() );
00219 KMessageBox::error( this, i18n( "The range\n%1\nis too large" ).arg( r[i]));
00220 return;
00221 }
00222 if ( ( desc == D_NONE && ( w != w2 || h != h2 ) ) ||
00223 ( desc == D_ROW && h != h2 ) ||
00224 ( desc == D_COL && w != w2 ) )
00225 {
00226 m_pView->slotUpdateView( m_pView->activeSheet() );
00227 QString tmp = i18n( "The ranges\n%1\nand\n%2\nhave different size").arg( *( r.begin() ) ).arg( r[i] );
00228 KMessageBox::error( this, tmp);
00229 return;
00230 }
00231 }
00232
00233
00234 if ( desc == D_NONE )
00235 {
00236
00237 QRect dest;
00238 dest.setCoords( dx, dy, dx + w - 1, dy + h - 1 );
00239 it = ranges.begin();
00240 for( ; it != ranges.end(); ++it )
00241 {
00242 KSpreadSheet *t = (*it).sheet;
00243 Q_ASSERT( t );
00244 QRect r;
00245 r.setCoords( (*it).range.left(), (*it).range.top(), (*it).range.right(), (*it).range.bottom() );
00246 if ( t == sheet && r.intersects( dest ) )
00247 {
00248 m_pView->slotUpdateView( m_pView->activeSheet() );
00249 QString tmp( i18n("The source tables intersect with the destination table") );
00250 KMessageBox::error( this, tmp);
00251 return;
00252 }
00253 }
00254
00255 for( int x = 0; x < w; x++ )
00256 {
00257 for( int y = 0; y < h; y++ )
00258 {
00259 bool novalue=true;
00260 QString formula = "=" + function + "(";
00261 it = ranges.begin();
00262 for( ; it != ranges.end(); ++it )
00263 {
00264 KSpreadSheet *t = (*it).sheet;
00265 assert( t );
00266 KSpreadCell *c = t->cellAt( x + (*it).range.left(), y + (*it).range.top() );
00267 if(!c->isDefault())
00268 novalue=false;
00269 if ( it != ranges.begin() )
00270 formula += ";";
00271 formula += (*it).sheetName + "!";
00272 formula += c->name();
00273 }
00274 formula += ")";
00275
00276 if(!novalue)
00277 sheet->setText( dy + y, dx + x,
00278 m_pCopy->isChecked() ? evaluate( formula, sheet ) : formula );
00279 }
00280 }
00281 }
00282 else if ( desc == D_ROW )
00283 {
00284
00285 QStringList lst;
00286 it = ranges.begin();
00287 for( ; it != ranges.end(); ++it )
00288 {
00289 KSpreadSheet *t = (*it).sheet;
00290 assert( t );
00291 kdDebug(36001) << "FROM " << (*it).range.left() << " to " << (*it).range.right() << endl;
00292 for( int x = (*it).range.left(); x <= (*it).range.right() ; ++x )
00293 {
00294 KSpreadCell *c = t->cellAt( x, (*it).range.top() );
00295 if ( c )
00296 {
00297 QString s = c->value().asString();
00298 if ( !lst.contains( s ) )
00299 lst.append( s );
00300 }
00301 }
00302 }
00303 lst.sort();
00304
00305
00306 QRect dest;
00307 dest.setCoords( dx, dy, dx + lst.count() - 1, dy + h - 1 );
00308 it = ranges.begin();
00309 for( ; it != ranges.end(); ++it )
00310 {
00311 KSpreadSheet *t = (*it).sheet;
00312 assert( t );
00313 QRect r;
00314 r.setCoords( (*it).range.left(), (*it).range.top(), (*it).range.right(), (*it).range.bottom() );
00315 if ( t == sheet && r.intersects( dest ) )
00316 {
00317 m_pView->slotUpdateView( m_pView->activeSheet() );
00318 QString tmp( i18n("The source tables intersect with the destination table") );
00319 KMessageBox::error( this, tmp);
00320 return;
00321 }
00322 }
00323
00324
00325 int x = 0;
00326 QStringList::Iterator s = lst.begin();
00327 for( ; s != lst.end(); ++s, ++x )
00328 {
00329 sheet->setText( dy, dx + x, *s );
00330
00331 for( int y = 1; y < h; ++y )
00332 {
00333 int count = 0;
00334 QString formula = "=" + function + "(";
00335 it = ranges.begin();
00336 for( ; it != ranges.end(); ++it )
00337 {
00338 for( int i = (*it).range.left(); i <= (*it).range.right(); ++i )
00339 {
00340 KSpreadSheet *t = (*it).sheet;
00341 assert( t );
00342 KSpreadCell *c = t->cellAt( i, (*it).range.top() );
00343 if ( c )
00344 {
00345 if ( c->value().asString() == *s )
00346 {
00347
00348 count++;
00349 if ( it != ranges.begin() )
00350 formula += ";";
00351 formula += (*it).sheetName + "!";
00352 formula += KSpreadCell::name( i, y + (*it).range.top() );
00353 }
00354 }
00355 }
00356 }
00357 formula += ")";
00358
00359 sheet->setText( dy + y, dx + x,
00360 m_pCopy->isChecked() ? evaluate( formula, sheet ) : formula );
00361 }
00362 }
00363 }
00364 else if ( desc == D_COL )
00365 {
00366
00367 QStringList lst;
00368 it = ranges.begin();
00369 for( ; it != ranges.end(); ++it )
00370 {
00371 KSpreadSheet *t = (*it).sheet;
00372 assert( t );
00373 for( int y = (*it).range.top(); y <= (*it).range.bottom() ; ++y )
00374 {
00375 KSpreadCell *c = t->cellAt( (*it).range.left(), y );
00376 if ( c )
00377 {
00378 QString s = c->value().asString();
00379 if ( !s.isEmpty() && lst.find( s ) == lst.end() )
00380 lst.append( s );
00381 }
00382 }
00383 }
00384 lst.sort();
00385
00386
00387 QRect dest;
00388 dest.setCoords( dx, dy, dx + w - 1, dy + lst.count() - 1 );
00389 it = ranges.begin();
00390 for( ; it != ranges.end(); ++it )
00391 {
00392 KSpreadSheet *t = (*it).sheet;
00393 assert( t );
00394 QRect r;
00395 r.setCoords( (*it).range.left(), (*it).range.top(), (*it).range.right(), (*it).range.bottom() );
00396 if ( t == sheet && r.intersects( dest ) )
00397 {
00398 m_pView->slotUpdateView( m_pView->activeSheet() );
00399 QString tmp( i18n("The source tables intersect with the destination table") );
00400 KMessageBox::error( this, tmp);
00401 return;
00402 }
00403 }
00404
00405
00406 int y = 0;
00407 QStringList::Iterator s = lst.begin();
00408 for( ; s != lst.end(); ++s, ++y )
00409 {
00410 sheet->setText( dy + y, dx, *s );
00411
00412 for( int x = 1; x < w; ++x )
00413 {
00414 int count = 0;
00415 QString formula = "=" + function + "(";
00416 it = ranges.begin();
00417 for( ; it != ranges.end(); ++it )
00418 {
00419 for( int i = (*it).range.top(); i <= (*it).range.bottom(); i++ )
00420 {
00421 KSpreadSheet *t = (*it).sheet;
00422 assert( t );
00423 KSpreadCell *c = t->cellAt( (*it).range.left(), i );
00424 if ( c )
00425 {
00426 QString v = c->value().asString();
00427 if ( !v.isEmpty() && *s == v )
00428 {
00429
00430 count++;
00431 if ( it != ranges.begin() ) formula += ";";
00432 formula += (*it).sheetName + "!";
00433 formula += KSpreadCell::name( i, y + (*it).range.top() );
00434 }
00435 }
00436 }
00437 }
00438
00439 formula += ")";
00440
00441 sheet->setText( dy + y, dx + x,
00442 m_pCopy->isChecked() ? evaluate( formula, sheet ) : formula );
00443 }
00444 }
00445 }
00446 else if ( desc == D_BOTH )
00447 {
00448
00449 QStringList cols;
00450 it = ranges.begin();
00451 for( ; it != ranges.end(); ++it )
00452 {
00453 KSpreadSheet *t = (*it).sheet;
00454 assert( t );
00455 for( int y = (*it).range.top() + 1; y <= (*it).range.bottom() ; ++y )
00456 {
00457 KSpreadCell *c = t->cellAt( (*it).range.left(), y );
00458 if ( c )
00459 {
00460 QString s = c->value().asString();
00461 if ( !s.isEmpty() && cols.find( s ) == cols.end() )
00462 cols.append( s );
00463 }
00464 }
00465 }
00466 cols.sort();
00467
00468
00469 QStringList rows;
00470 it = ranges.begin();
00471 for( ; it != ranges.end(); ++it )
00472 {
00473 KSpreadSheet *t = (*it).sheet;
00474 assert( t );
00475 for( int x = (*it).range.left() + 1; x <= (*it).range.right() ; ++x )
00476 {
00477 KSpreadCell *c = t->cellAt( x, (*it).range.top() );
00478 if ( c )
00479 {
00480 QString s = c->value().asString();
00481 if ( !s.isEmpty() && rows.find( s ) == rows.end() )
00482 rows.append( s );
00483 }
00484 }
00485 }
00486 rows.sort();
00487
00488
00489 QRect dest;
00490 dest.setCoords( dx, dy, dx + cols.count(), dy + rows.count() );
00491 it = ranges.begin();
00492 for( ; it != ranges.end(); ++it )
00493 {
00494 KSpreadSheet *t = (*it).sheet;
00495 assert( t );
00496 QRect r;
00497 r.setCoords( (*it).range.left(), (*it).range.top(), (*it).range.right(), (*it).range.bottom() );
00498 if ( t == sheet && r.intersects( dest ) )
00499 {
00500 m_pView->slotUpdateView( m_pView->activeSheet() );
00501 QString tmp( i18n("The source tables intersect with the destination table") );
00502 KMessageBox::error( this, tmp);
00503 return;
00504 }
00505 }
00506
00507
00508 QValueList<st_cell> lst;
00509 it = ranges.begin();
00510 for( ; it != ranges.end(); ++it )
00511 {
00512 KSpreadSheet *t = (*it).sheet;
00513 assert( t );
00514 for( int x = (*it).range.left() + 1; x <= (*it).range.right() ; ++x )
00515 {
00516 KSpreadCell *c = t->cellAt( x, (*it).range.top() );
00517 if ( c )
00518 {
00519 QString ydesc = c->value().asString();
00520 for( int y = (*it).range.top() + 1; y <= (*it).range.bottom() ; ++y )
00521 {
00522 KSpreadCell *c2 = t->cellAt( (*it).range.left(), y );
00523 if ( c2 )
00524 {
00525 QString xdesc = c2->value().asString();
00526 KSpreadCell *c3 = t->cellAt( x, y );
00527 if ( c3 && c3->value().isNumber() )
00528 {
00529 st_cell k;
00530 k.xdesc = xdesc;
00531 k.ydesc = ydesc;
00532 k.cell = c3;
00533 k.sheet = (*it).sheetName;
00534 k.x = x;
00535 k.y = y;
00536 lst.append( k );
00537 }
00538 }
00539 }
00540 }
00541 }
00542 }
00543
00544
00545 int i = 1;
00546 QStringList::Iterator s = rows.begin();
00547 for( ; s != rows.end(); ++s, ++i )
00548 sheet->setText( dy, dx + i, *s );
00549
00550
00551 i = 1;
00552 s = cols.begin();
00553 for( ; s != cols.end(); ++s, ++i )
00554 sheet->setText( dy + i, dx, *s );
00555
00556
00557 int x = 1;
00558 QStringList::Iterator ydesc = rows.begin();
00559 for( ; ydesc != rows.end(); ++ydesc, x++ )
00560 {
00561 int y = 1;
00562 QStringList::Iterator xdesc = cols.begin();
00563 for( ; xdesc != cols.end(); ++xdesc, y++ )
00564 {
00565 int count = 0;
00566 QString formula = "=" + function + "(";
00567 QValueList<st_cell>::Iterator lit = lst.begin();
00568 for( ; lit != lst.end(); ++lit )
00569 {
00570 if ( (*lit).xdesc == *xdesc && (*lit).ydesc == *ydesc )
00571 {
00572 count++;
00573 if ( it != ranges.begin() ) formula += ";";
00574 formula += (*it).sheetName + "!";
00575 formula += KSpreadCell::name( i, y + (*it).range.top() );
00576 }
00577 }
00578 formula += ")";
00579
00580 sheet->setText( dy + y, dx + x,
00581 m_pCopy->isChecked() ? evaluate( formula, sheet ) : formula );
00582 }
00583 }
00584 }
00585 m_pView->updateEditWidget();
00586 m_pView->slotUpdateView( m_pView->activeSheet() );
00587 accept();
00588 delete this;
00589 }
00590
00591 void KSpreadConsolidate::slotCancel()
00592 {
00593 reject();
00594 delete this;
00595 }
00596
00597 void KSpreadConsolidate::slotAdd()
00598 {
00599 slotReturnPressed();
00600 }
00601
00602 void KSpreadConsolidate::slotRemove()
00603 {
00604 int i = m_pRefs->currentItem();
00605 if ( i < 0 )
00606 return;
00607
00608 m_pRefs->removeItem( i );
00609
00610 if ( m_pRefs->count() == 0 )
00611 actionButton( Ok )->setEnabled( false );
00612 }
00613
00614 QStringList KSpreadConsolidate::refs()
00615 {
00616 QStringList list;
00617 int c = m_pRefs->count();
00618
00619 for( int i = 0; i < c; i++ )
00620 list.append( m_pRefs->text( i ) );
00621
00622 return list;
00623 }
00624
00625 void KSpreadConsolidate::slotSelectionChanged( KSpreadSheet* _sheet, const QRect& _selection )
00626 {
00627 if ( _selection.left() == 0 || _selection.top() == 0 ||
00628 _selection.right() == 0 || _selection.bottom() == 0 )
00629 {
00630 m_pRef->setText( "" );
00631 return;
00632 }
00633
00634 QString area = util_rangeName( _sheet, _selection );
00635 m_pRef->setText( area );
00636 m_pRef->setSelection( 0, area.length() );
00637 }
00638
00639 void KSpreadConsolidate::slotReturnPressed()
00640 {
00641 QString txt = m_pRef->text();
00642
00643 KSpreadRange r( txt, m_pView->doc()->map() );
00644 if ( !r.isValid() )
00645 {
00646 KMessageBox::error( this, i18n("The range\n%1\n is malformed").arg( txt ));
00647 return;
00648 }
00649
00650 if ( !txt.isEmpty() )
00651 {
00652 m_pRefs->insertItem( txt );
00653 actionButton( Ok )->setEnabled( true );
00654 }
00655 }
00656
00657 void KSpreadConsolidate::closeEvent ( QCloseEvent * )
00658 {
00659 delete this;
00660 }
00661
00662 QString KSpreadConsolidate::evaluate( const QString& formula, KSpreadSheet* sheet )
00663 {
00664 QString result = "###";
00665
00666 kdDebug(36001)<<"KSpreadConsolidate::evaluate " << formula << endl;
00667
00668 KSContext context;
00669
00670
00671 KSParseNode* code = sheet->doc()->interpreter()->parse( context,
00672 sheet, formula );
00673 if( !code ) return result;
00674
00675 context = sheet->doc()->context();
00676 if ( !sheet->doc()->interpreter()->evaluate( context, code, sheet, 0 ) )
00677 return result;
00678
00679 if ( context.value()->type() == KSValue::DoubleType )
00680 return QString::number( context.value()->doubleValue() );
00681
00682 if ( context.value()->type() == KSValue::IntType )
00683 return QString::number( context.value()->intValue() );
00684
00685 return result;
00686 }
00687
00688 #include "kspread_dlg_cons.moc"