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