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