1
/* This file is part of the KDE project
2
Copyright (C) 2002-2003 Norbert Andres <nandres@web.de>
3
(C) 2002-2003 Philipp Mueller <philipp.mueller@gmx.de>
4
(C) 2002 Laurent Montel <montel@kde.org>
5
(C) 2002 John Dailey <dailey@vt.edu>
6
(C) 2002 Ariya Hidayat <ariya@kde.org>
7
(C) 2002 Werner Trobin <trobin@kde.org>
8
(C) 2002 Harri Porten <porten@kde.org>
10
This library is free software; you can redistribute it and/or
11
modify it under the terms of the GNU Library General Public
12
License as published by the Free Software Foundation; either
13
version 2 of the License, or (at your option) any later version.
15
This library is distributed in the hope that it will be useful,
16
but WITHOUT ANY WARRANTY; without even the implied warranty of
17
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
18
Library General Public License for more details.
20
You should have received a copy of the GNU Library General Public License
21
along with this library; see the file COPYING.LIB. If not, write to
22
the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
23
Boston, MA 02111-1307, USA.
26
#include "kspread_dlg_goalseek.h"
28
#include "kspread_canvas.h"
29
#include "kspread_cell.h"
30
#include "kspread_doc.h"
31
#include "kspread_map.h"
32
#include "kspread_selection.h"
33
#include "kspread_sheet.h"
34
#include "kspread_undo.h"
35
#include "kspread_util.h"
36
#include "kspread_view.h"
38
#include <kapplication.h>
41
#include <kmessagebox.h>
42
#include <kstdguiitem.h>
43
#include <kpushbutton.h>
48
#include <qlineedit.h>
51
#include <qwhatsthis.h>
54
KSpreadGoalSeekDlg::KSpreadGoalSeekDlg( KSpreadView * parent, QPoint const & marker,
55
const char * name, bool, WFlags fl )
56
: KDialog( parent, name, false, fl ),
61
m_anchor( m_pView->canvasWidget()->selectionInfo()->selectionAnchor() ),
62
m_marker( m_pView->canvasWidget()->marker() ),
63
m_selection( m_pView->canvasWidget()->selection() )
65
setWFlags( Qt::WDestructiveClose );
68
setName( "KSpreadGoalSeekDlg" );
71
setCaption( i18n( "Goal Seek" ) );
72
setSizeGripEnabled( true );
74
KSpreadGoalSeekDlgLayout = new QGridLayout( this, 1, 1, 11, 6, "KSpreadGoalSeekDlgLayout");
76
m_startFrame = new QFrame( this, "m_startFrame" );
77
m_startFrame->setFrameShape( QFrame::StyledPanel );
78
m_startFrame->setFrameShadow( QFrame::Raised );
79
m_startFrameLayout = new QGridLayout( m_startFrame, 1, 1, 11, 6, "m_startFrameLayout");
81
QLabel * TextLabel4 = new QLabel( m_startFrame, "TextLabel4" );
82
TextLabel4->setText( i18n( "To value:" ) );
83
m_startFrameLayout->addWidget( TextLabel4, 1, 0 );
85
m_targetValueEdit = new QLineEdit( m_startFrame, "m_targetValueEdit" );
86
m_startFrameLayout->addWidget( m_targetValueEdit, 1, 1 );
88
m_targetEdit = new QLineEdit( m_startFrame, "m_targetEdit" );
89
m_startFrameLayout->addWidget( m_targetEdit, 0, 1 );
90
m_targetEdit->setText( KSpreadCell::name( marker.x(), marker.y() ) );
92
m_sourceEdit = new QLineEdit( m_startFrame, "m_sourceEdit" );
93
m_startFrameLayout->addWidget( m_sourceEdit, 2, 1 );
95
QLabel * TextLabel5 = new QLabel( m_startFrame, "TextLabel5" );
96
TextLabel5->setText( i18n( "By changing cell:" ) );
98
m_startFrameLayout->addWidget( TextLabel5, 2, 0 );
100
QLabel * TextLabel3 = new QLabel( m_startFrame, "TextLabel3" );
101
TextLabel3->setText( i18n( "Set cell:" ) );
103
m_startFrameLayout->addWidget( TextLabel3, 0, 0 );
104
KSpreadGoalSeekDlgLayout->addWidget( m_startFrame, 0, 0 );
106
QVBoxLayout * Layout5 = new QVBoxLayout( 0, 0, 6, "Layout5");
108
m_buttonOk = new QPushButton( this, "m_buttonOk" );
109
m_buttonOk->setText( i18n( "&Start" ) );
110
m_buttonOk->setAccel( 276824143 );
111
m_buttonOk->setAutoDefault( TRUE );
112
m_buttonOk->setDefault( TRUE );
113
Layout5->addWidget( m_buttonOk );
115
m_buttonCancel = new KPushButton( KStdGuiItem::cancel(), this, "m_buttonCancel" );
116
m_buttonCancel->setAccel( 276824131 );
117
m_buttonCancel->setAutoDefault( TRUE );
118
Layout5->addWidget( m_buttonCancel );
119
QSpacerItem* spacer = new QSpacerItem( 20, 20, QSizePolicy::Minimum, QSizePolicy::Expanding );
120
Layout5->addItem( spacer );
122
KSpreadGoalSeekDlgLayout->addMultiCellLayout( Layout5, 0, 1, 1, 1 );
124
m_resultFrame = new QFrame( this, "m_resultFrame" );
125
m_resultFrame->setFrameShape( QFrame::StyledPanel );
126
m_resultFrame->setFrameShadow( QFrame::Raised );
127
m_resultFrame->setMinimumWidth( 350 );
128
m_resultFrameLayout = new QGridLayout( m_resultFrame, 1, 1, 11, 6, "m_resultFrameLayout");
130
m_currentValueLabel = new QLabel( m_resultFrame, "m_currentValueLabel" );
131
m_currentValueLabel->setText( i18n( "Current value:" ) );
133
m_resultFrameLayout->addWidget( m_currentValueLabel, 2, 0 );
135
m_newValueDesc = new QLabel( m_resultFrame, "m_newValueDesc" );
136
m_newValueDesc->setText( i18n( "New value:" ) );
138
m_resultFrameLayout->addWidget( m_newValueDesc, 1, 0 );
140
m_newValue = new QLabel( m_resultFrame, "m_newValue" );
141
m_newValue->setText( "m_targetValueEdit" );
143
m_resultFrameLayout->addWidget( m_newValue, 1, 1 );
145
m_currentValue = new QLabel( m_resultFrame, "m_currentValue" );
146
m_currentValue->setText( "m_currentValue" );
148
m_resultFrameLayout->addWidget( m_currentValue, 2, 1 );
150
m_resultText = new QLabel( m_resultFrame, "m_resultText" );
151
m_resultText->setText( "Goal seeking with cell <cell> found <a | no> solution:" );
152
m_resultText->setAlignment( int( QLabel::WordBreak | QLabel::AlignVCenter ) );
154
m_resultFrameLayout->addMultiCellWidget( m_resultText, 0, 0, 0, 1 );
156
// KSpreadGoalSeekDlgLayout->addWidget( m_resultFrame, 1, 0 );
158
m_resultFrame->hide();
160
m_sheetName = m_pView->activeSheet()->sheetName();
162
// Allow the user to select cells on the spreadsheet.
163
m_pView->canvasWidget()->startChoose();
165
qApp->installEventFilter( this );
167
// signals and slots connections
168
connect( m_buttonOk, SIGNAL( clicked() ), this, SLOT( buttonOkClicked() ) );
169
connect( m_buttonCancel, SIGNAL( clicked() ), this, SLOT( buttonCancelClicked() ) );
171
connect( m_pView, SIGNAL( sig_chooseSelectionChanged( KSpreadSheet*, const QRect& ) ),
172
this, SLOT( slotSelectionChanged( KSpreadSheet *, const QRect & ) ) );
175
setTabOrder( m_targetEdit, m_targetValueEdit );
176
setTabOrder( m_targetValueEdit, m_sourceEdit );
177
setTabOrder( m_sourceEdit, m_buttonOk );
178
setTabOrder( m_buttonOk, m_buttonCancel );
181
KSpreadGoalSeekDlg::~KSpreadGoalSeekDlg()
183
kdDebug() << "~KSpreadGoalSeekDlg" << endl;
187
m_pView->doc()->emitBeginOperation( false );
188
m_sourceCell->setValue(m_oldSource);
189
m_targetCell->setCalcDirtyFlag();
190
m_targetCell->calc();
191
m_pView->slotUpdateView( m_pView->activeSheet() );
195
bool KSpreadGoalSeekDlg::eventFilter( QObject* obj, QEvent* ev )
197
if ( obj == m_targetValueEdit && ev->type() == QEvent::FocusIn )
198
m_focus = m_targetValueEdit;
199
else if ( obj == m_targetEdit && ev->type() == QEvent::FocusIn )
200
m_focus = m_targetEdit;
201
else if ( obj == m_sourceEdit && ev->type() == QEvent::FocusIn )
202
m_focus = m_sourceEdit;
207
m_pView->canvasWidget()->startChoose();
212
void KSpreadGoalSeekDlg::closeEvent ( QCloseEvent * e )
217
void KSpreadGoalSeekDlg::slotSelectionChanged( KSpreadSheet * _sheet, const QRect & _selection )
222
if ( _selection.left() <= 0 )
225
if ( _selection.left() >= _selection.right() && _selection.top() >= _selection.bottom() )
227
int dx = _selection.right();
228
int dy = _selection.bottom();
232
tmp = _sheet->sheetName() + "!" + KSpreadCell::columnName( dx ) + tmp;
233
m_focus->setText( tmp );
237
QString area = util_rangeName( _sheet, _selection );
238
m_focus->setText( area );
242
void KSpreadGoalSeekDlg::buttonOkClicked()
244
KSpreadDoc * pDoc = m_pView->doc();
245
pDoc->emitBeginOperation( false );
248
KSpreadSheet * sheet = m_pView->activeSheet();
250
KSpreadPoint source( m_sourceEdit->text(), sheet->workbook(), sheet );
251
if (!source.isValid())
253
KMessageBox::error( this, i18n("Cell reference is invalid.") );
254
m_sourceEdit->selectAll();
255
m_sourceEdit->setFocus();
257
m_pView->slotUpdateView( m_pView->activeSheet() );
261
KSpreadPoint target( m_targetEdit->text(), sheet->workbook(), sheet );
262
if (!target.isValid())
264
KMessageBox::error( this, i18n("Cell reference is invalid.") );
265
m_targetEdit->selectAll();
266
m_targetEdit->setFocus();
268
m_pView->slotUpdateView( m_pView->activeSheet() );
273
double goal = m_pView->doc()->locale()->readNumber(m_targetValueEdit->text(), &ok );
276
KMessageBox::error( this, i18n("Target value is invalid.") );
277
m_targetValueEdit->selectAll();
278
m_targetValueEdit->setFocus();
280
m_pView->slotUpdateView( m_pView->activeSheet() );
284
m_sourceCell = source.cell();
285
m_targetCell = target.cell();
287
if ( !m_sourceCell->value().isNumber() )
289
KMessageBox::error( this, i18n("Source cell must contain a numeric value.") );
290
m_sourceEdit->selectAll();
291
m_sourceEdit->setFocus();
293
m_pView->slotUpdateView( m_pView->activeSheet() );
297
if ( !m_targetCell->isFormula() )
299
KMessageBox::error( this, i18n("Target cell must contain a formula.") );
300
m_targetEdit->selectAll();
301
m_targetEdit->setFocus();
303
m_pView->slotUpdateView( m_pView->activeSheet() );
307
m_buttonOk->setText( i18n("&OK") );
308
m_buttonOk->setEnabled(false);
309
m_buttonCancel->setEnabled(false);
310
KSpreadGoalSeekDlgLayout->addWidget( m_resultFrame, 0, 0 );
311
m_startFrame->hide();
312
m_resultFrame->show();
313
if ( m_startFrame->width() > 350 )
314
m_resultFrame->setMinimumWidth( m_startFrame->width() );
318
startCalc( m_sourceCell->value().asFloat(), goal );
319
m_pView->slotUpdateView( m_pView->activeSheet() );
325
if ( !pDoc->undoLocked() )
327
KSpreadUndoSetText * undo
328
= new KSpreadUndoSetText( pDoc, m_pView->activeSheet(), QString::number(m_oldSource),
329
m_sourceCell->column(), m_sourceCell->row(),
330
m_sourceCell->formatType() );
332
pDoc->addCommand( undo );
339
m_pView->slotUpdateView( m_pView->activeSheet() );
343
void KSpreadGoalSeekDlg::buttonCancelClicked()
347
m_pView->doc()->emitBeginOperation( false );
348
m_sourceCell->setValue(m_oldSource);
349
m_targetCell->setCalcDirtyFlag();
350
m_targetCell->calc();
352
m_pView->slotUpdateView( m_pView->activeSheet() );
359
void KSpreadGoalSeekDlg::chooseCleanup()
361
m_pView->canvasWidget()->endChoose();
363
KSpreadSheet * sheet = 0;
365
// Switch back to the old sheet
366
if ( m_pView->activeSheet()->sheetName() != m_sheetName )
368
sheet = m_pView->doc()->map()->findSheet( m_sheetName );
370
m_pView->setActiveSheet( sheet );
373
sheet = m_pView->activeSheet();
375
// Revert the marker to its original position
376
m_pView->selectionInfo()->setSelection( m_marker, m_anchor, sheet );
380
void KSpreadGoalSeekDlg::startCalc(double _start, double _goal)
382
m_resultText->setText( i18n( "Starting..." ) );
383
m_newValueDesc->setText( i18n( "Iteration:" ) );
385
// lets be optimistic
388
// TODO: make this configurable
389
double eps = 0.0000001;
391
double startA = 0.0, startB;
392
double resultA, resultB;
395
m_oldSource = m_sourceCell->value().asFloat();
398
// initialize start value
400
double x = startB + 0.5;
402
// while the result is not close enough to zero
403
// or while the max number of iterations is not reached...
404
while ( fabs( resultA ) > eps && ( m_maxIter >= 0 ) )
409
m_sourceCell->setValue(startA);
410
// m_sourceCell->updateDepending();
411
m_sourceCell->setCalcDirtyFlag();
412
m_targetCell->calc( false );
413
resultA = m_targetCell->value().asFloat() - _goal;
414
// kdDebug() << "Target A: " << m_targetCell->value().asFloat() << ", " << m_targetCell->text() << " Calc: " << resultA << endl;
416
m_sourceCell->setValue(startB);
417
// m_sourceCell->updateDepending();
418
m_sourceCell->setCalcDirtyFlag();
419
m_targetCell->calc( false );
420
resultB = m_targetCell->value().asFloat() - _goal;
422
kdDebug() << "Target B: " << m_targetCell->value().asFloat() << ", " << m_targetCell->text() << " Calc: " << resultB << endl;
424
kdDebug() << "Iteration: " << m_maxIter << ", StartA: " << startA
425
<< ", ResultA: " << resultA << " (eps: " << eps << "), StartB: "
426
<< startB << ", ResultB: " << resultB << endl;
429
// find zero with secant method (rough implementation was provided by Franz-Xaver Meier):
430
// if the function returns the same for two different
431
// values we have something like a horizontal line
432
// => can't get zero.
433
if ( resultB == resultA )
435
// kdDebug() << " resultA == resultB" << endl;
436
if ( fabs( resultA ) < eps )
446
// Point of intersection of secant with x-axis
447
x = ( startA * resultB - startB * resultA ) / ( resultB - resultA );
449
if ( fabs(x) > 100000000 )
451
// kdDebug() << "fabs(x) > 100000000: " << x << endl;
456
// kdDebug() << "X: " << x << ", fabs (resultA): " << fabs(resultA) << ", Real start: " << startA << ", Real result: " << resultA << ", Iteration: " << m_maxIter << endl;
459
if ( m_maxIter % 20 == 0 )
460
m_newValue->setText( QString::number(m_maxIter) );
463
m_newValueDesc->setText( i18n( "New value:" ) );
466
m_sourceCell->setValue( startA );
467
m_sourceCell->setCalcDirtyFlag();
468
m_sourceCell->sheet()->setRegionPaintDirty(m_sourceCell->cellRect());
469
// m_targetCell->setCalcDirtyFlag();
470
m_targetCell->calc( false );
472
m_resultText->setText( i18n( "Goal seeking with cell %1 found a solution:" ).arg( m_sourceEdit->text() ) );
473
m_newValue->setText( m_pView->doc()->locale()->formatNumber( startA ) );
474
m_currentValue->setText( m_pView->doc()->locale()->formatNumber( m_oldSource ) );
479
// restore the old value
480
m_sourceCell->setValue( m_oldSource );
481
m_targetCell->setCalcDirtyFlag();
482
m_sourceCell->sheet()->setRegionPaintDirty(m_sourceCell->cellRect());
483
m_targetCell->calc( false );
484
m_resultText->setText( i18n( "Goal seeking with cell %1 has found NO solution." ).arg( m_sourceEdit->text() ) );
485
m_newValue->setText( "" );
486
m_currentValue->setText( m_pView->doc()->locale()->formatNumber( m_oldSource ) );
490
m_buttonOk->setEnabled( true );
491
m_buttonCancel->setEnabled( true );
495
#include "kspread_dlg_goalseek.moc"