kspread Library API Documentation

kspread_dlg_goalseek.cc

00001 /* This file is part of the KDE project
00002    Copyright (C) 2002-2003 Norbert Andres <nandres@web.de>
00003              (C) 2002-2003 Philipp Mueller <philipp.mueller@gmx.de>
00004              (C) 2002 Laurent Montel <montel@kde.org>
00005              (C) 2002 John Dailey <dailey@vt.edu>
00006              (C) 2002 Ariya Hidayat <ariya@kde.org>
00007              (C) 2002 Werner Trobin <trobin@kde.org>
00008              (C) 2002 Harri Porten <porten@kde.org>
00009 
00010    This library is free software; you can redistribute it and/or
00011    modify it under the terms of the GNU Library General Public
00012    License as published by the Free Software Foundation; either
00013    version 2 of the License, or (at your option) any later version.
00014 
00015    This library is distributed in the hope that it will be useful,
00016    but WITHOUT ANY WARRANTY; without even the implied warranty of
00017    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00018    Library General Public License for more details.
00019 
00020    You should have received a copy of the GNU Library General Public License
00021    along with this library; see the file COPYING.LIB.  If not, write to
00022    the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
00023    Boston, MA 02111-1307, USA.
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   //  KSpreadGoalSeekDlgLayout->addWidget( m_resultFrame, 1, 0 );
00157 
00158   m_resultFrame->hide();
00159 
00160   m_sheetName = m_pView->activeSheet()->sheetName();
00161 
00162   // Allow the user to select cells on the spreadsheet.
00163   m_pView->canvasWidget()->startChoose();
00164 
00165   qApp->installEventFilter( this );
00166 
00167   // signals and slots connections
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   // tab order
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   // Switch back to the old sheet
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   // Revert the marker to its original position
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   // lets be optimistic
00386   bool ok = true;
00387 
00388   // TODO: make this configurable
00389   double eps = 0.0000001;
00390 
00391   double startA = 0.0, startB;
00392   double resultA, resultB;
00393 
00394   // save old value
00395   m_oldSource = m_sourceCell->value().asFloat();
00396   resultA = _goal;
00397 
00398   // initialize start value
00399   startB = _start;
00400   double x = startB + 0.5;
00401 
00402   // while the result is not close enough to zero
00403   // or while the max number of iterations is not reached...
00404   while ( fabs( resultA ) > eps && ( m_maxIter >= 0 ) )
00405   {
00406     startA = startB;
00407     startB = x;
00408 
00409     m_sourceCell->setValue(startA);
00410     //    m_sourceCell->updateDepending();
00411     m_sourceCell->setCalcDirtyFlag();
00412     m_targetCell->calc( false );
00413     resultA = m_targetCell->value().asFloat() - _goal;
00414     //    kdDebug() << "Target A: " << m_targetCell->value().asFloat() << ", " << m_targetCell->text() << " Calc: " << resultA << endl;
00415 
00416     m_sourceCell->setValue(startB);
00417     //    m_sourceCell->updateDepending();
00418     m_sourceCell->setCalcDirtyFlag();
00419     m_targetCell->calc( false );
00420     resultB = m_targetCell->value().asFloat() - _goal;
00421     /*
00422       kdDebug() << "Target B: " << m_targetCell->value().asFloat() << ", " << m_targetCell->text() << " Calc: " << resultB << endl;
00423 
00424       kdDebug() << "Iteration: " << m_maxIter << ", StartA: " << startA
00425               << ", ResultA: " << resultA << " (eps: " << eps << "), StartB: "
00426               << startB << ", ResultB: " << resultB << endl;
00427     */
00428 
00429     // find zero with secant method (rough implementation was provided by Franz-Xaver Meier):
00430     // if the function returns the same for two different
00431     // values we have something like a horizontal line
00432     // => can't get zero.
00433     if ( resultB == resultA )
00434     {
00435       //      kdDebug() << " resultA == resultB" << endl;
00436       if ( fabs( resultA ) < eps )
00437       {
00438         ok = true;
00439         break;
00440       }
00441 
00442       ok = false;
00443       break;
00444     }
00445 
00446     // Point of intersection of secant with x-axis
00447     x = ( startA * resultB - startB * resultA ) / ( resultB - resultA );
00448 
00449     if ( fabs(x) > 100000000 )
00450     {
00451       //      kdDebug() << "fabs(x) > 100000000: " << x << endl;
00452       ok = false;
00453       break;
00454     }
00455 
00456     //    kdDebug() << "X: " << x << ", fabs (resultA): " << fabs(resultA) << ", Real start: " << startA << ", Real result: " << resultA << ", Iteration: " << m_maxIter << endl;
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     //    m_targetCell->setCalcDirtyFlag();
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     // restore the old value
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 
KDE Logo
This file is part of the documentation for kspread Library Version 1.4.2.
Documentation copyright © 1996-2004 the KDE developers.
Generated on Mon Feb 13 09:42:54 2006 by doxygen 1.4.2 written by Dimitri van Heesch, © 1997-2003