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 #include "kspread_dlg_goalseek.h"
00027
00028 #include "kspread_canvas.h"
00029 #include "kspread_cell.h"
00030 #include "kspread_doc.h"
00031 #include "kspread_map.h"
00032 #include "selection.h"
00033 #include "kspread_sheet.h"
00034 #include "kspread_undo.h"
00035 #include "kspread_util.h"
00036 #include "kspread_view.h"
00037
00038 #include <kapplication.h>
00039 #include <kdebug.h>
00040 #include <klocale.h>
00041 #include <kmessagebox.h>
00042 #include <kstdguiitem.h>
00043 #include <kpushbutton.h>
00044
00045 #include <qframe.h>
00046 #include <qlabel.h>
00047 #include <qlayout.h>
00048 #include <qlineedit.h>
00049 #include <qtooltip.h>
00050 #include <qvariant.h>
00051 #include <qwhatsthis.h>
00052
00053 #include <math.h>
00054
00055 using namespace KSpread;
00056
00057 GoalSeekDialog::GoalSeekDialog( View * parent, QPoint const & marker,
00058 const char * name, bool, WFlags fl )
00059 : KDialog( parent, name, false, fl ),
00060 m_pView( parent ),
00061 m_maxIter( 1000 ),
00062 m_restored( true ),
00063 m_focus(0),
00064 m_anchor( m_pView->selectionInfo()->anchor() ),
00065 m_marker( m_pView->selectionInfo()->marker() ),
00066 m_selection( m_pView->selectionInfo()->selection() )
00067 {
00068 setWFlags( Qt::WDestructiveClose );
00069
00070 if ( !name )
00071 setName( "GoalSeekDialog" );
00072
00073 resize( 458, 153 );
00074 setCaption( i18n( "Goal Seek" ) );
00075 setSizeGripEnabled( true );
00076
00077 GoalSeekDialogLayout = new QGridLayout( this, 1, 1, 11, 6, "GoalSeekDialogLayout");
00078
00079 m_startFrame = new QFrame( this, "m_startFrame" );
00080 m_startFrame->setFrameShape( QFrame::StyledPanel );
00081 m_startFrame->setFrameShadow( QFrame::Raised );
00082 m_startFrameLayout = new QGridLayout( m_startFrame, 1, 1, 11, 6, "m_startFrameLayout");
00083
00084 QLabel * TextLabel4 = new QLabel( m_startFrame, "TextLabel4" );
00085 TextLabel4->setText( i18n( "To value:" ) );
00086 m_startFrameLayout->addWidget( TextLabel4, 1, 0 );
00087
00088 m_targetValueEdit = new QLineEdit( m_startFrame, "m_targetValueEdit" );
00089 m_startFrameLayout->addWidget( m_targetValueEdit, 1, 1 );
00090
00091 m_targetEdit = new QLineEdit( m_startFrame, "m_targetEdit" );
00092 m_startFrameLayout->addWidget( m_targetEdit, 0, 1 );
00093 m_targetEdit->setText( Cell::name( marker.x(), marker.y() ) );
00094
00095 m_sourceEdit = new QLineEdit( m_startFrame, "m_sourceEdit" );
00096 m_startFrameLayout->addWidget( m_sourceEdit, 2, 1 );
00097
00098 QLabel * TextLabel5 = new QLabel( m_startFrame, "TextLabel5" );
00099 TextLabel5->setText( i18n( "By changing cell:" ) );
00100
00101 m_startFrameLayout->addWidget( TextLabel5, 2, 0 );
00102
00103 QLabel * TextLabel3 = new QLabel( m_startFrame, "TextLabel3" );
00104 TextLabel3->setText( i18n( "Set cell:" ) );
00105
00106 m_startFrameLayout->addWidget( TextLabel3, 0, 0 );
00107 GoalSeekDialogLayout->addWidget( m_startFrame, 0, 0 );
00108
00109 QVBoxLayout * Layout5 = new QVBoxLayout( 0, 0, 6, "Layout5");
00110
00111 m_buttonOk = new QPushButton( this, "m_buttonOk" );
00112 m_buttonOk->setText( i18n( "&Start" ) );
00113 m_buttonOk->setAccel( 276824143 );
00114 m_buttonOk->setAutoDefault( TRUE );
00115 m_buttonOk->setDefault( TRUE );
00116 Layout5->addWidget( m_buttonOk );
00117
00118 m_buttonCancel = new KPushButton( KStdGuiItem::cancel(), this, "m_buttonCancel" );
00119 m_buttonCancel->setAccel( 276824131 );
00120 m_buttonCancel->setAutoDefault( TRUE );
00121 Layout5->addWidget( m_buttonCancel );
00122 QSpacerItem* spacer = new QSpacerItem( 20, 20, QSizePolicy::Minimum, QSizePolicy::Expanding );
00123 Layout5->addItem( spacer );
00124
00125 GoalSeekDialogLayout->addMultiCellLayout( Layout5, 0, 1, 1, 1 );
00126
00127 m_resultFrame = new QFrame( this, "m_resultFrame" );
00128 m_resultFrame->setFrameShape( QFrame::StyledPanel );
00129 m_resultFrame->setFrameShadow( QFrame::Raised );
00130 m_resultFrame->setMinimumWidth( 350 );
00131 m_resultFrameLayout = new QGridLayout( m_resultFrame, 1, 1, 11, 6, "m_resultFrameLayout");
00132
00133 m_currentValueLabel = new QLabel( m_resultFrame, "m_currentValueLabel" );
00134 m_currentValueLabel->setText( i18n( "Current value:" ) );
00135
00136 m_resultFrameLayout->addWidget( m_currentValueLabel, 2, 0 );
00137
00138 m_newValueDesc = new QLabel( m_resultFrame, "m_newValueDesc" );
00139 m_newValueDesc->setText( i18n( "New value:" ) );
00140
00141 m_resultFrameLayout->addWidget( m_newValueDesc, 1, 0 );
00142
00143 m_newValue = new QLabel( m_resultFrame, "m_newValue" );
00144 m_newValue->setText( "m_targetValueEdit" );
00145
00146 m_resultFrameLayout->addWidget( m_newValue, 1, 1 );
00147
00148 m_currentValue = new QLabel( m_resultFrame, "m_currentValue" );
00149 m_currentValue->setText( "m_currentValue" );
00150
00151 m_resultFrameLayout->addWidget( m_currentValue, 2, 1 );
00152
00153 m_resultText = new QLabel( m_resultFrame, "m_resultText" );
00154 m_resultText->setText( "Goal seeking with cell <cell> found <a | no> solution:" );
00155 m_resultText->setAlignment( int( QLabel::WordBreak | QLabel::AlignVCenter ) );
00156
00157 m_resultFrameLayout->addMultiCellWidget( m_resultText, 0, 0, 0, 1 );
00158
00159
00160
00161 m_resultFrame->hide();
00162
00163 m_sheetName = m_pView->activeSheet()->sheetName();
00164
00165
00166 m_pView->canvasWidget()->startChoose();
00167
00168 qApp->installEventFilter( this );
00169
00170
00171 connect( m_buttonOk, SIGNAL( clicked() ), this, SLOT( buttonOkClicked() ) );
00172 connect( m_buttonCancel, SIGNAL( clicked() ), this, SLOT( buttonCancelClicked() ) );
00173
00174 connect( m_pView->choice(), SIGNAL(changed(const Region&)),
00175 this, SLOT(slotSelectionChanged()));
00176
00177
00178 setTabOrder( m_targetEdit, m_targetValueEdit );
00179 setTabOrder( m_targetValueEdit, m_sourceEdit );
00180 setTabOrder( m_sourceEdit, m_buttonOk );
00181 setTabOrder( m_buttonOk, m_buttonCancel );
00182 }
00183
00184 GoalSeekDialog::~GoalSeekDialog()
00185 {
00186 kdDebug() << "~GoalSeekDialog" << endl;
00187
00188 if ( !m_restored )
00189 {
00190 m_pView->doc()->emitBeginOperation( false );
00191 m_sourceCell->setValue(m_oldSource);
00192 m_targetCell->setCalcDirtyFlag();
00193 m_targetCell->calc();
00194 m_pView->slotUpdateView( m_pView->activeSheet() );
00195 }
00196 }
00197
00198 bool GoalSeekDialog::eventFilter( QObject* obj, QEvent* ev )
00199 {
00200 if ( obj == m_targetValueEdit && ev->type() == QEvent::FocusIn )
00201 m_focus = m_targetValueEdit;
00202 else if ( obj == m_targetEdit && ev->type() == QEvent::FocusIn )
00203 m_focus = m_targetEdit;
00204 else if ( obj == m_sourceEdit && ev->type() == QEvent::FocusIn )
00205 m_focus = m_sourceEdit;
00206 else
00207 return FALSE;
00208
00209 if ( m_focus )
00210 m_pView->canvasWidget()->startChoose();
00211
00212 return FALSE;
00213 }
00214
00215 void GoalSeekDialog::closeEvent ( QCloseEvent * e )
00216 {
00217 e->accept();
00218 }
00219
00220 void GoalSeekDialog::slotSelectionChanged()
00221 {
00222 if ( !m_focus )
00223 return;
00224
00225 if (m_pView->choice()->isValid())
00226 {
00227 QString area = m_pView->choice()->name();
00228 m_focus->setText( area );
00229 }
00230 }
00231
00232 void GoalSeekDialog::buttonOkClicked()
00233 {
00234 Doc * pDoc = m_pView->doc();
00235 pDoc->emitBeginOperation( false );
00236 if (m_maxIter > 0)
00237 {
00238 Sheet * sheet = m_pView->activeSheet();
00239
00240 Point source( m_sourceEdit->text(), sheet->workbook(), sheet );
00241 if (!source.isValid())
00242 {
00243 KMessageBox::error( this, i18n("Cell reference is invalid.") );
00244 m_sourceEdit->selectAll();
00245 m_sourceEdit->setFocus();
00246
00247 m_pView->slotUpdateView( m_pView->activeSheet() );
00248 return;
00249 }
00250
00251 Point target( m_targetEdit->text(), sheet->workbook(), sheet );
00252 if (!target.isValid())
00253 {
00254 KMessageBox::error( this, i18n("Cell reference is invalid.") );
00255 m_targetEdit->selectAll();
00256 m_targetEdit->setFocus();
00257
00258 m_pView->slotUpdateView( m_pView->activeSheet() );
00259 return;
00260 }
00261
00262 bool ok = false;
00263 double goal = m_pView->doc()->locale()->readNumber(m_targetValueEdit->text(), &ok );
00264 if ( !ok )
00265 {
00266 KMessageBox::error( this, i18n("Target value is invalid.") );
00267 m_targetValueEdit->selectAll();
00268 m_targetValueEdit->setFocus();
00269
00270 m_pView->slotUpdateView( m_pView->activeSheet() );
00271 return;
00272 }
00273
00274 m_sourceCell = source.cell();
00275 m_targetCell = target.cell();
00276
00277 if ( !m_sourceCell->value().isNumber() )
00278 {
00279 KMessageBox::error( this, i18n("Source cell must contain a numeric value.") );
00280 m_sourceEdit->selectAll();
00281 m_sourceEdit->setFocus();
00282
00283 m_pView->slotUpdateView( m_pView->activeSheet() );
00284 return;
00285 }
00286
00287 if ( !m_targetCell->isFormula() )
00288 {
00289 KMessageBox::error( this, i18n("Target cell must contain a formula.") );
00290 m_targetEdit->selectAll();
00291 m_targetEdit->setFocus();
00292
00293 m_pView->slotUpdateView( m_pView->activeSheet() );
00294 return;
00295 }
00296
00297 m_buttonOk->setText( i18n("&OK") );
00298 m_buttonOk->setEnabled(false);
00299 m_buttonCancel->setEnabled(false);
00300 GoalSeekDialogLayout->addWidget( m_resultFrame, 0, 0 );
00301 m_startFrame->hide();
00302 m_resultFrame->show();
00303 if ( m_startFrame->width() > 350 )
00304 m_resultFrame->setMinimumWidth( m_startFrame->width() );
00305
00306 m_restored = false;
00307
00308 startCalc( m_sourceCell->value().asFloat(), goal );
00309 m_pView->slotUpdateView( m_pView->activeSheet() );
00310
00311 return;
00312 }
00313 else
00314 {
00315 if ( !pDoc->undoLocked() )
00316 {
00317 UndoSetText * undo
00318 = new UndoSetText( pDoc, m_pView->activeSheet(), QString::number(m_oldSource),
00319 m_sourceCell->column(), m_sourceCell->row(),
00320 m_sourceCell->formatType() );
00321
00322 pDoc->addCommand( undo );
00323 }
00324
00325 m_restored = true;
00326 }
00327 chooseCleanup();
00328
00329 m_pView->slotUpdateView( m_pView->activeSheet() );
00330 accept();
00331 }
00332
00333 void GoalSeekDialog::buttonCancelClicked()
00334 {
00335 if ( !m_restored )
00336 {
00337 m_pView->doc()->emitBeginOperation( false );
00338 m_sourceCell->setValue(m_oldSource);
00339 m_targetCell->setCalcDirtyFlag();
00340 m_targetCell->calc();
00341 m_restored = true;
00342 m_pView->slotUpdateView( m_pView->activeSheet() );
00343 }
00344
00345 chooseCleanup();
00346 reject();
00347 }
00348
00349 void GoalSeekDialog::chooseCleanup()
00350 {
00351 m_pView->canvasWidget()->endChoose();
00352
00353 Sheet * sheet = 0;
00354
00355
00356 if ( m_pView->activeSheet()->sheetName() != m_sheetName )
00357 {
00358 sheet = m_pView->doc()->map()->findSheet( m_sheetName );
00359 if ( sheet )
00360 m_pView->setActiveSheet( sheet );
00361 }
00362 else
00363 sheet = m_pView->activeSheet();
00364
00365
00366 m_pView->selectionInfo()->initialize(QRect(m_marker, m_anchor));
00367 }
00368
00369
00370 void GoalSeekDialog::startCalc(double _start, double _goal)
00371 {
00372 m_resultText->setText( i18n( "Starting..." ) );
00373 m_newValueDesc->setText( i18n( "Iteration:" ) );
00374
00375
00376 bool ok = true;
00377
00378
00379 double eps = 0.0000001;
00380
00381 double startA = 0.0, startB;
00382 double resultA, resultB;
00383
00384
00385 m_oldSource = m_sourceCell->value().asFloat();
00386 resultA = _goal;
00387
00388
00389 startB = _start;
00390 double x = startB + 0.5;
00391
00392
00393
00394 while ( fabs( resultA ) > eps && ( m_maxIter >= 0 ) )
00395 {
00396 startA = startB;
00397 startB = x;
00398
00399 m_sourceCell->setValue(startA);
00400
00401 m_sourceCell->setCalcDirtyFlag();
00402 m_targetCell->calc( false );
00403 resultA = m_targetCell->value().asFloat() - _goal;
00404
00405
00406 m_sourceCell->setValue(startB);
00407
00408 m_sourceCell->setCalcDirtyFlag();
00409 m_targetCell->calc( false );
00410 resultB = m_targetCell->value().asFloat() - _goal;
00411
00412
00413
00414
00415
00416
00417
00418
00419
00420
00421
00422
00423 if ( resultB == resultA )
00424 {
00425
00426 if ( fabs( resultA ) < eps )
00427 {
00428 ok = true;
00429 break;
00430 }
00431
00432 ok = false;
00433 break;
00434 }
00435
00436
00437 x = ( startA * resultB - startB * resultA ) / ( resultB - resultA );
00438
00439 if ( fabs(x) > 100000000 )
00440 {
00441
00442 ok = false;
00443 break;
00444 }
00445
00446
00447
00448 --m_maxIter;
00449 if ( m_maxIter % 20 == 0 )
00450 m_newValue->setText( QString::number(m_maxIter) );
00451 }
00452
00453 m_newValueDesc->setText( i18n( "New value:" ) );
00454 if ( ok )
00455 {
00456 m_sourceCell->setValue( startA );
00457 m_sourceCell->setCalcDirtyFlag();
00458 m_sourceCell->sheet()->setRegionPaintDirty(m_sourceCell->cellRect());
00459
00460 m_targetCell->calc( false );
00461
00462 m_resultText->setText( i18n( "Goal seeking with cell %1 found a solution:" ).arg( m_sourceEdit->text() ) );
00463 m_newValue->setText( m_pView->doc()->locale()->formatNumber( startA ) );
00464 m_currentValue->setText( m_pView->doc()->locale()->formatNumber( m_oldSource ) );
00465 m_restored = false;
00466 }
00467 else
00468 {
00469
00470 m_sourceCell->setValue( m_oldSource );
00471 m_targetCell->setCalcDirtyFlag();
00472 m_sourceCell->sheet()->setRegionPaintDirty(m_sourceCell->cellRect());
00473 m_targetCell->calc( false );
00474 m_resultText->setText( i18n( "Goal seeking with cell %1 has found NO solution." ).arg( m_sourceEdit->text() ) );
00475 m_newValue->setText( "" );
00476 m_currentValue->setText( m_pView->doc()->locale()->formatNumber( m_oldSource ) );
00477 m_restored = true;
00478 }
00479
00480 m_buttonOk->setEnabled( true );
00481 m_buttonCancel->setEnabled( true );
00482 m_maxIter = 0;
00483 }
00484
00485 #include "kspread_dlg_goalseek.moc"
00486