kspread

kspread_autofill.cpp
1/* This file is part of the KDE project
2
3 Copyright 2005 Raphael Langerhorst <raphael.langerhorst@kdemail.net>
4 Copyright 2002-2004 Ariya Hidayat <ariya@kde.org>
5 Copyright 2002-2003 Norbert Andres <nandres@web.de>
6 Copyright 2002 John Dailey <dailey@vt.edu>
7 Copyright 2001-2002 Philipp Mueller <philipp.mueller@gmx.de>
8 Copyright 2000-2002 Laurent Montel <montel@kde.org>
9 Copyright 2000-2001 Werner Trobin <trobin@kde.org>
10 Copyright 1999-2001 David Faure <faure@kde.org>
11 Copyright 1998-2000 Torben Weis <weis@kde.org>
12 Copyright 1998-1999 Stephan Kulow <coolo@kde.org>
13 Copyright 1998 Reginald Stadlbauer <reggie@kde.org>
14
15 This library is free software; you can redistribute it and/or
16 modify it under the terms of the GNU Library General Public
17 License as published by the Free Software Foundation; either
18 version 2 of the License, or (at your option) any later version.
19
20 This library is distributed in the hope that it will be useful,
21 but WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
23 Library General Public License for more details.
24
25 You should have received a copy of the GNU Library General Public License
26 along with this library; see the file COPYING.LIB. If not, write to
27 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
28 * Boston, MA 02110-1301, USA.
29*/
30
31#include <math.h>
32
33#include <tqregexp.h>
34
35#include <tdeconfig.h>
36#include <kdebug.h>
37
38#include "kspread_doc.h"
39#include "kspread_locale.h"
40#include "kspread_sheet.h"
41#include "kspread_undo.h"
42#include "kspread_value.h"
43#include "valueconverter.h"
44#include "kspread_autofill.h"
45
46using namespace KSpread;
47
48TQStringList *AutoFillSequenceItem::month = 0L;
49TQStringList *AutoFillSequenceItem::shortMonth = 0L;
50TQStringList *AutoFillSequenceItem::day = 0L;
51TQStringList *AutoFillSequenceItem::shortDay = 0L;
52TQStringList *AutoFillSequenceItem::other = 0L;
53/**********************************************************************************
54 *
55 * AutoFillDeltaSequence
56 *
57 **********************************************************************************/
58
59AutoFillDeltaSequence::AutoFillDeltaSequence( AutoFillSequence *_first, AutoFillSequence *_next )
60 : m_ok(true),
61 m_sequence(0L)
62{
63 if ( _first->count() != _next->count() )
64 {
65 m_ok = false;
66 return;
67 }
68
69 m_sequence = new TQMemArray<double> ( _first->count() );
70
71 AutoFillSequenceItem *item = _first->getFirst();
72 AutoFillSequenceItem *item2 = _next->getFirst();
73 int i = 0;
74 // for( item = _first->getFirst(); item != 0L && item2 != 0L; item = _first->getNext() );
75 for ( i = 0; i < _first->count(); i++ )
76 {
77 double d;
78 if ( !item->getDelta( item2, d ) )
79 {
80 m_ok = false;
81 return;
82 }
83 m_sequence->at( i++ ) = d;
84 item2 = _next->getNext();
85 item = _first->getNext();
86 }
87}
88
89AutoFillDeltaSequence::~AutoFillDeltaSequence()
90{
91 delete m_sequence;
92}
93
94bool AutoFillDeltaSequence::equals( AutoFillDeltaSequence *_delta )
95{
96 if ( m_sequence == 0L )
97 return false;
98 if ( _delta->getSequence() == 0L )
99 return false;
100 if ( m_sequence->size() != _delta->getSequence()->size() )
101 return false;
102
103 for ( unsigned int i = 0; i < m_sequence->size(); i++ )
104 {
105 if ( m_sequence->at( i ) != _delta->getSequence()->at( i ) )
106 return false;
107 }
108
109 return true;
110}
111
112double AutoFillDeltaSequence::getItemDelta( int _pos )
113{
114 if ( m_sequence == 0L )
115 return 0.0;
116
117 return m_sequence->at( _pos );
118}
119
120/**********************************************************************************
121 *
122 * AutoFillSequenceItem
123 *
124 **********************************************************************************/
125
126AutoFillSequenceItem::AutoFillSequenceItem( int _i )
127{
128 m_IValue = _i;
129 m_Type = INTEGER;
130}
131
132AutoFillSequenceItem::AutoFillSequenceItem( double _d )
133{
134 m_DValue = _d;
135 m_Type = FLOAT;
136}
137
138AutoFillSequenceItem::AutoFillSequenceItem( const TQString &_str )
139{
140 m_String = _str;
141 m_Type = STRING;
142
143 if ( month == 0L )
144 {
145 month = new TQStringList();
146 month->append( i18n("January") );
147 month->append( i18n("February") );
148 month->append( i18n("March") );
149 month->append( i18n("April") );
150 month->append( i18n("May") );
151 month->append( i18n("June") );
152 month->append( i18n("July") );
153 month->append( i18n("August") );
154 month->append( i18n("September") );
155 month->append( i18n("October") );
156 month->append( i18n("November") );
157 month->append( i18n("December") );
158 }
159
160 if ( shortMonth == 0L )
161 {
162 shortMonth = new TQStringList();
163 shortMonth->append( i18n("Jan") );
164 shortMonth->append( i18n("Feb") );
165 shortMonth->append( i18n("Mar") );
166 shortMonth->append( i18n("Apr") );
167 shortMonth->append( i18n("May short", "May") );
168 shortMonth->append( i18n("Jun") );
169 shortMonth->append( i18n("Jul") );
170 shortMonth->append( i18n("Aug") );
171 shortMonth->append( i18n("Sep") );
172 shortMonth->append( i18n("Oct") );
173 shortMonth->append( i18n("Nov") );
174 shortMonth->append( i18n("Dec") );
175 }
176
177 if ( day == 0L )
178 {
179 day = new TQStringList();
180 day->append( i18n("Monday") );
181 day->append( i18n("Tuesday") );
182 day->append( i18n("Wednesday") );
183 day->append( i18n("Thursday") );
184 day->append( i18n("Friday") );
185 day->append( i18n("Saturday") );
186 day->append( i18n("Sunday") );
187 }
188
189 if ( shortDay == 0L )
190 {
191 shortDay = new TQStringList();
192 shortDay->append( i18n("Mon") );
193 shortDay->append( i18n("Tue") );
194 shortDay->append( i18n("Wed") );
195 shortDay->append( i18n("Thu") );
196 shortDay->append( i18n("Fri") );
197 shortDay->append( i18n("Sat") );
198 shortDay->append( i18n("Sun") );
199 }
200
201 if( other==0L)
202 {
203 // other=new TQStringList();
204 TDEConfig *config = Factory::global()->config();
205 config->setGroup( "Parameters" );
206 other=new TQStringList(config->readListEntry("Other list"));
207 }
208
209 if ( month->find( _str ) != month->end() )
210 {
211 m_Type = MONTH;
212 return;
213 }
214
215 if ( shortMonth->find( _str ) != shortMonth->end() )
216 {
217 m_Type = SHORTMONTH;
218 return;
219 }
220
221 if ( day->find( _str ) != day->end() )
222 {
223 m_Type = DAY;
224 return;
225 }
226
227 if ( shortDay->find( _str ) != shortDay->end() )
228 {
229 m_Type = SHORTDAY;
230 return;
231 }
232
233 if( other->find(_str)!=other->end())
234 {
235 m_Type = OTHER;
236 m_OtherBegin=0;
237 m_OtherEnd=other->count();
238 int index= other->findIndex(_str);
239 //find end and begin of qstringlist of other.
240 for ( TQStringList::Iterator it = other->find(_str); it != other->end();++it )
241 {
242 if((*it)=="\\")
243 {
244 m_OtherEnd=index;
245 break;
246 }
247 index++;
248 }
249 index= other->findIndex(_str);
250 for ( TQStringList::Iterator it = other->find(_str); it != other->begin();--it )
251 {
252 if((*it)=="\\")
253 {
254 m_OtherBegin=index;
255 break;
256 }
257 index--;
258 }
259 return;
260 }
261
262 if ( m_String[0] == '=' )
263 m_Type = FORMULA;
264}
265
266bool AutoFillSequenceItem::getDelta( AutoFillSequenceItem *seq, double &_delta )
267{
268 if ( seq->getType() != m_Type )
269 return false;
270
271 switch( m_Type )
272 {
273 case INTEGER:
274 _delta = (double)( seq->getIValue() - m_IValue );
275 return true;
276 case FLOAT:
277 _delta = seq->getDValue() - m_DValue;
278 return true;
279 case FORMULA:
280 case STRING:
281 if ( m_String == seq->getString() )
282 {
283 _delta = 0.0;
284 return true;
285 }
286 return false;
287 case MONTH:
288 {
289 int i = month->findIndex( m_String );
290 int j = month->findIndex( seq->getString() );
291 int k = j;
292
293 if ( j + 1 == i )
294 _delta = -1.0;
295 else
296 _delta = ( double )( k - i );
297 return true;
298 }
299
300 case SHORTMONTH:
301 {
302 int i = shortMonth->findIndex( m_String );
303 int j = shortMonth->findIndex( seq->getString() );
304 int k = j;
305
306 if ( j + 1 == i )
307 _delta = -1.0;
308 else
309 _delta = ( double )( k - i );
310 return true;
311 }
312
313 case DAY:
314 {
315 int i = day->findIndex( m_String );
316 int j = day->findIndex( seq->getString() );
317 int k = j;
318
319 if ( j + 1 == i )
320 _delta = -1.0;
321 else
322 _delta = ( double )( k - i );
323 kdDebug() << m_String << " i: " << i << " j: " << j << " k: " << k << " delta: " << _delta << endl;
324 return true;
325 }
326
327 case SHORTDAY:
328 {
329 int i = shortDay->findIndex( m_String );
330 int j = shortDay->findIndex( seq->getString() );
331 int k = j;
332
333 if ( j + 1 == i )
334 _delta = -1.0;
335 else
336 _delta = ( double )( k - i );
337 return true;
338 }
339 case OTHER:
340 {
341 if( m_OtherEnd!= seq->getIOtherEnd() || m_OtherBegin!= seq->getIOtherBegin())
342 return false;
343 int i = other->findIndex( m_String );
344 int j = other->findIndex( seq->getString() );
345 int k = j;
346 if ( j < i )
347 k += (m_OtherEnd - m_OtherBegin - 1);
348 /*if ( j + 1 == i )
349 _delta = -1.0;
350 else*/
351 _delta = ( double )( k - i );
352 return true;
353 }
354 default:
355 return false;
356 }
357}
358
359TQString AutoFillSequenceItem::getSuccessor( int _no, double _delta )
360{
361 TQString erg;
362 switch( m_Type )
363 {
364 case INTEGER:
365 erg.sprintf("%i", m_IValue + _no * (int)_delta );
366 break;
367 case FLOAT:
368 erg.sprintf("%f", m_DValue + (double)_no * _delta );
369 break;
370 case FORMULA:
371 case STRING:
372 erg = m_String;
373 break;
374 case MONTH:
375 {
376 int i = month->findIndex( m_String );
377 int j = i + _no * (int) _delta;
378 while (j < 0)
379 j += month->count();
380 int k = j % month->count();
381 erg = (*month->at( k ));
382 }
383 break;
384 case SHORTMONTH:
385 {
386 int i = shortMonth->findIndex( m_String );
387 int j = i + _no * (int) _delta;
388 while (j < 0)
389 j += shortMonth->count();
390 int k = j % shortMonth->count();
391 erg = (*shortMonth->at( k ));
392 }
393 break;
394 case DAY:
395 {
396 int i = day->findIndex( m_String );
397 int j = i + _no * (int) _delta;
398 while (j < 0)
399 j += day->count();
400 int k = j % day->count();
401 erg = (*day->at( k ));
402 }
403 break;
404 case SHORTDAY:
405 {
406 int i = shortDay->findIndex( m_String );
407 int j = i + _no * (int) _delta;
408 while (j < 0)
409 j += shortDay->count();
410 int k = j % shortDay->count();
411 erg = (*shortDay->at( k ));
412 }
413 break;
414 case OTHER:
415 {
416 int i = other->findIndex( m_String )-(m_OtherBegin+1);
417 int j = i + _no * (int) _delta;
418 int k = j % (m_OtherEnd - m_OtherBegin-1);
419 erg = (*other->at( (k+m_OtherBegin+1) ));
420 }
421 case TIME:
422 case DATE:
423 // gets never called but fixes a warning while compiling
424 break;
425 }
426
427 return TQString( erg );
428}
429
430TQString AutoFillSequenceItem::getPredecessor( int _no, double _delta )
431{
432 TQString erg;
433 switch( m_Type )
434 {
435 case INTEGER:
436 erg.sprintf("%i", m_IValue - _no * (int)_delta );
437 break;
438 case FLOAT:
439 erg.sprintf("%f", m_DValue - (double)_no * _delta );
440 break;
441 case FORMULA:
442 case STRING:
443 erg = m_String;
444 break;
445 case MONTH:
446 {
447 int i = month->findIndex( m_String );
448 int j = i - _no * (int) _delta;
449 while ( j < 0 )
450 j += month->count();
451 int k = j % month->count();
452 erg = (*month->at( k ));
453 }
454 break;
455 case SHORTMONTH:
456 {
457 int i = shortMonth->findIndex( m_String );
458 int j = i - _no * (int) _delta;
459 while ( j < 0 )
460 j += shortMonth->count();
461 int k = j % shortMonth->count();
462 erg = (*shortMonth->at( k ));
463 }
464 break;
465 case DAY:
466 {
467 int i = day->findIndex( m_String );
468 int j = i - _no * (int) _delta;
469 while ( j < 0 )
470 j += day->count();
471 int k = j % day->count();
472 erg = (*day->at( k ));
473 }
474 break;
475 case SHORTDAY:
476 {
477 int i = shortDay->findIndex( m_String );
478 int j = i - _no * (int) _delta;
479 while ( j < 0 )
480 j += shortDay->count();
481 int k = j % shortDay->count();
482 erg = (*shortDay->at( k ));
483 }
484 break;
485 case OTHER:
486 {
487 int i = other->findIndex( m_String ) - (m_OtherBegin + 1);
488 int j = i - _no * (int) _delta;
489 while ( j < 0 )
490 j += (m_OtherEnd - m_OtherBegin - 1);
491 int k = j % (m_OtherEnd - m_OtherBegin - 1);
492 erg = (*other->at( (k + m_OtherBegin + 1) ));
493 }
494 case TIME:
495 case DATE:
496 // gets never called but fixes a warning while compiling
497 break;
498 }
499
500 return TQString( erg );
501}
502
503/**********************************************************************************
504 *
505 * AutoFillSequence
506 *
507 **********************************************************************************/
508
509AutoFillSequence::AutoFillSequence( Cell *_cell )
510{
511 sequence.setAutoDelete( true );
512
513 if ( _cell->isFormula() )
514 {
515 TQString d = _cell->encodeFormula();
516 sequence.append( new AutoFillSequenceItem( d ) );
517 }
518 else if ( _cell->value().isNumber() )
519 {
520 if ( floor( _cell->value().asFloat() ) == _cell->value().asFloat() )
521 {
522 sequence.append( new AutoFillSequenceItem( (int)_cell->value().asFloat()) );
523 }
524 else
525 sequence.append( new AutoFillSequenceItem(_cell->value().asFloat() ) );
526 }
527 else if ( !_cell->text().isEmpty() )
528 sequence.append( new AutoFillSequenceItem( _cell->text() ) );
529}
530
531bool AutoFillSequence::matches( AutoFillSequence* _seq, AutoFillDeltaSequence *_delta )
532{
533 AutoFillDeltaSequence delta( this, _seq );
534 if ( !delta.isOk() )
535 return false;
536
537 if ( delta.equals( _delta ) )
538 return true;
539
540 return false;
541}
542
543void AutoFillSequence::fillCell( Cell *src, Cell *dest, AutoFillDeltaSequence *delta, int _block, bool down )
544{
545 TQString erg = "";
546
547 // Special handling for formulas
548 if ( sequence.first() != 0L && sequence.first()->getType() == AutoFillSequenceItem::FORMULA )
549 {
550 TQString f = dest->decodeFormula( sequence.first()->getString() );
551 dest->setCellText( f );
552 dest->copyFormat( src );
553 return;
554 }
555
556 AutoFillSequenceItem *item;
557 int i = 0;
558 if (down)
559 {
560 for ( item = sequence.first(); item != 0L; item = sequence.next() )
561 erg += item->getSuccessor( _block, delta->getItemDelta( i++ ) );
562 }
563 else
564 {
565 for ( item = sequence.first(); item != 0L; item = sequence.next() )
566 erg += item->getPredecessor( _block, delta->getItemDelta( i++ ) );
567 }
568
569 dest->setCellText( erg );
570 dest->copyFormat( src );
571}
572
573/**********************************************************************************
574 *
575 * Sheet
576 *
577 **********************************************************************************/
578
579void Sheet::autofill( TQRect &src, TQRect &dest )
580{
581 if (src == dest)
582 {
583 return;
584 }
585
586 setRegionPaintDirty( dest );
587
588 doc()->emitBeginOperation();
589
590 if ( !doc()->undoLocked() )
591 {
592 UndoAutofill *undo = new UndoAutofill( doc(), this, dest );
593 doc()->addCommand( undo );
594 }
595
596 // disable the update of the max sroll range on each cell insertion
597 // Bug 124806: creating series takes extremely long time
598 enableScrollBarUpdates(false);
599
600 // Fill from left to right
601 if ( src.left() == dest.left() && src.right() < dest.right() )
602 {
603 for ( int y = src.top(); y <= src.bottom(); y++ )
604 {
605 int x;
606 TQPtrList<Cell> destList;
607 for ( x = src.right() + 1; x <= dest.right(); x++ )
608 destList.append( nonDefaultCell( x, y ) );
609 TQPtrList<Cell> srcList;
610 for ( x = src.left(); x <= src.right(); x++ )
611 srcList.append( cellAt( x, y ) );
612 TQPtrList<AutoFillSequence> seqList;
613 seqList.setAutoDelete( true );
614 for ( x = src.left(); x <= src.right(); x++ )
615 seqList.append( new AutoFillSequence( cellAt( x, y ) ) );
616 fillSequence( srcList, destList, seqList );
617 }
618 }
619
620 // Fill from top to bottom
621 if ( src.top() == dest.top() && src.bottom() < dest.bottom() )
622 {
623 for ( int x = src.left(); x <= dest.right(); x++ )
624 {
625 int y;
626 TQPtrList<Cell> destList;
627 for ( y = src.bottom() + 1; y <= dest.bottom(); y++ )
628 destList.append( nonDefaultCell( x, y ) );
629 TQPtrList<Cell> srcList;
630 for ( y = src.top(); y <= src.bottom(); y++ )
631 {
632 srcList.append( cellAt( x, y ) );
633 }
634 TQPtrList<AutoFillSequence> seqList;
635 seqList.setAutoDelete( true );
636 for ( y = src.top(); y <= src.bottom(); y++ )
637 seqList.append( new AutoFillSequence( cellAt( x, y ) ) );
638 fillSequence( srcList, destList, seqList );
639 }
640 }
641
642 // Fill from right to left
643 if ( ( src.left() == dest.right() || src.left() == dest.right() - 1) && src.right() >= dest.right() )
644 {
645 if ( src.left() != dest.right() )
646 dest.setRight( dest.right() - 1 );
647
648 for ( int y = dest.top(); y <= dest.bottom(); y++ )
649 {
650 int x;
651 TQPtrList<Cell> destList;
652
653 for ( x = dest.left(); x < src.left(); x++ )
654 {
655 destList.append( nonDefaultCell( x, y ) );
656 }
657 TQPtrList<Cell> srcList;
658 for ( x = src.left(); x <= src.right(); x++ )
659 {
660 srcList.append( cellAt( x, y ) );
661 }
662 TQPtrList<AutoFillSequence> seqList;
663 seqList.setAutoDelete( true );
664 for ( x = src.left(); x <= src.right(); x++ )
665 seqList.append( new AutoFillSequence( cellAt( x, y ) ) );
666 fillSequence( srcList, destList, seqList, false );
667 }
668 }
669
670 // Fill from bottom to top
671 if ( (src.top() == dest.bottom() || src.top() == (dest.bottom() - 1) ) && src.bottom() >= dest.bottom() )
672 {
673 if (src.top() != dest.bottom() )
674 dest.setBottom(dest.bottom() - 1);
675 int startVal = TQMIN( dest.left(), src.left());
676 int endVal = TQMAX(src.right(), dest.right());
677 for ( int x = startVal; x <= endVal; x++ )
678 {
679 int y;
680 TQPtrList<Cell> destList;
681 for ( y = dest.top(); y < src.top(); y++ )
682 destList.append( nonDefaultCell( x, y ) );
683 TQPtrList<Cell> srcList;
684 for ( y = src.top(); y <= src.bottom(); ++y )
685 {
686 srcList.append( cellAt( x, y ) );
687 }
688 TQPtrList<AutoFillSequence> seqList;
689 seqList.setAutoDelete( true );
690 for ( y = src.top(); y <= src.bottom(); y++ )
691 seqList.append( new AutoFillSequence( cellAt( x, y ) ) );
692 fillSequence( srcList, destList, seqList, false );
693 }
694 }
695
696 // update the max sroll range ONCE here
697 enableScrollBarUpdates(true);
698 checkRangeHBorder(dest.right());
699 checkRangeVBorder(dest.bottom());
700
701 emit sig_updateView( this );
702 // doc()->emitEndOperation();
703}
704
705
706void Sheet::fillSequence( TQPtrList<Cell>& _srcList,
707 TQPtrList<Cell>& _destList,
708 TQPtrList<AutoFillSequence>& _seqList,
709 bool down)
710{
711 doc()->emitBeginOperation(true);
712
713 /* try finding an interval to use to fill the sequence */
714 if (!FillSequenceWithInterval(_srcList, _destList, _seqList, down))
715 {
716 /* if no interval was found, just copy down through */
717 FillSequenceWithCopy(_srcList, _destList, down);
718 }
719
720 doc()->emitEndOperation();
721
722}
723
724TQVariant getDiff( const Value& value1, const Value& value2 , AutoFillSequenceItem::Type type )
725{
726 if ( type == AutoFillSequenceItem::FLOAT )
727 return TQVariant( value2.asFloat() - value1.asFloat() );
728 if ( type == AutoFillSequenceItem::TIME || type == AutoFillSequenceItem::DATE )
729 return TQVariant( (int)( value2.asInteger() - value1.asInteger() ) );
730
731 return TQVariant( (int)0 );
732 // note: date and time difference can be calculated as
733 // the difference of the serial number
734 /* if( (type == AutoFillSequenceItem::FLOAT) ||
735 (type == AutoFillSequenceItem::DATE) ||
736 (type == AutoFillSequenceItem::TIME) )
737 return ( value2.asFloat() - value1.asFloat() );
738 else
739 return 0.0;*/
740}
741
742bool Sheet::FillSequenceWithInterval(TQPtrList<Cell>& _srcList,
743 TQPtrList<Cell>& _destList,
744 TQPtrList<AutoFillSequence>& _seqList,
745 bool down)
746{
747 if (_srcList.first()->isFormula())
748 return false;
749
750 TQPtrList<AutoFillDeltaSequence> deltaList;
751 deltaList.setAutoDelete( true );
752 bool ok = false;
753
754 if ( _srcList.first()->value().isNumber() || _srcList.first()->isDate() || _srcList.first()->isTime() )
755 {
756 AutoFillSequenceItem::Type type;
757
758 TQValueVector< TQVariant > tmp( _seqList.count() ); /*= new TQValueList< TQVariant > ( _seqList.count() )*/;
759 TQValueVector< TQVariant > diff( _seqList.count() ); /*= new TQValueList< TQVariant > ( _seqList.count() )*/;
760 int p = -1;
761 int count = 0;
762 int tmpcount = 0;
763
764 Cell * cell = _srcList.first();
765 Cell * cell2 = _srcList.next();
766
767 bool singleCellOnly = (cell2 == 0);
768
769 if ( cell->isDate() )
770 type = AutoFillSequenceItem::DATE;
771 else if ( cell->isTime() )
772 type = AutoFillSequenceItem::TIME;
773 else if ( cell->value().isNumber() )
774 type = AutoFillSequenceItem::FLOAT;
775 else
776 return false; // Cannot happen due to if condition
777
778 while ( cell && (cell2 || singleCellOnly) )
779 {
780
781 Value cellValue = cell->value();
782 Value cell2Value;
783
784 //If we only have a single cell, the interval will depend upon the data type.
785 //- For numeric values, set the interval to 0 as we don't know what might be useful as a sequence
786 //- For time values, set the interval to one hour, as this will probably be the most useful setting
787 //- For date values, set the interval to one day, as this will probably be the most useful setting
788 //
789 //Note that the above options were chosen for consistency with Excel. Gnumeric (1.59) sets
790 //the interval to 0 for all types, OpenOffice.org (2.00) uses increments of 1.00, 1 hour and 1 day
791 //respectively
792 if (singleCellOnly)
793 {
794 if (type == AutoFillSequenceItem::FLOAT)
795 cell2Value = cellValue;
796 else if ( type == AutoFillSequenceItem::TIME)
797 cell2Value = Value( cellValue.asTime().addSecs( 60*60 ) );
798 else if ( type == AutoFillSequenceItem::DATE)
799 cell2Value = Value ( cellValue.asDate().addDays( 1 ) );
800 }
801 else
802 {
803 cell2Value = cell2->value();
804
805 // check if both cells contain the same type
806 if ( ( !cellValue.isNumber() )
807 || ( cell2->isDate() && type != AutoFillSequenceItem::DATE )
808 || ( cell2->isTime() && type != AutoFillSequenceItem::TIME ) )
809 {
810 count = 0;
811 ok = false;
812 break;
813 }
814 }
815
816 TQVariant delta = getDiff(cellValue , cell2Value , type );
817
818 if (count < 1)
819 {
820 p = count;
821 diff[ count++ ] = delta;
822 }
823 else
824 {
825 // same value again?
826 if (diff[ p ] == delta)
827 {
828 // store it somewhere else for the case we need it later
829 ++p;
830 tmp[ tmpcount++ ] = delta;
831 }
832 else
833 {
834 // if we have saved values in another buffer we have to insert them first
835 if ( tmpcount > 0 )
836 {
837 for ( int i = 0; i < tmpcount; ++i )
838 {
839 diff[ count++ ] = tmp.at( i );
840 }
841
842 tmpcount = 0;
843 }
844
845 // insert the value
846 p = 0;
847 diff[ count++ ] = delta;
848 }
849 }
850
851 // check next cell pair
852 cell = cell2;
853 cell2 = _srcList.next();
854 }
855
856 // we have found something:
857 if (count > 0 && (tmpcount > 0 || count == 1))
858 {
859 TQVariant cellValue( (int) 0 );
860
861 Cell * dest;
862 Cell * src;
863
864 int i = tmpcount;
865 if (down)
866 {
867 dest = _destList.first();
868 src = _srcList.last();
869 }
870 else
871 {
872 dest = _destList.last();
873 src = _srcList.first();
874
875 i *= -1;
876 }
877
878 if ( type == AutoFillSequenceItem::FLOAT )
879 cellValue = src->value().asFloat();
880 else
881 cellValue = (int)src->value().asInteger();
882
883 TQString res;
884 // copy all the data
885 while (dest)
886 {
887 if (down)
888 {
889 while ( i >= count )
890 i -= count;
891 }
892 else
893 {
894 while ( i < 0)
895 i += count;
896 }
897
898 TQVariant currentDiff = diff.at( i );
899
900 if (cellValue.type() == TQVariant::Double)
901 if (down)
902 cellValue = cellValue.asDouble() + currentDiff.asDouble();
903 else
904 cellValue = cellValue.asDouble() - currentDiff.asDouble();
905 else
906 if (down)
907 cellValue = cellValue.asInt() + currentDiff.asInt();
908 else
909 cellValue = cellValue.asInt() - currentDiff.asInt();
910
911 if ( type == AutoFillSequenceItem::TIME)
912 {
913 Value timeValue = doc()->converter()->asTime( Value(cellValue.asInt()) );
914 Value stringValue = doc()->converter()->asString( timeValue );
915 dest->setCellText( stringValue.asString() );
916 }
917 else if ( type == AutoFillSequenceItem::DATE)
918 {
919 Value dateValue = doc()->converter()->asDate( Value(cellValue.asInt()) );
920 Value stringValue = doc()->converter()->asString( dateValue );
921 dest->setCellText( stringValue.asString() );
922 }
923 else
924 dest->setCellText( cellValue.asString() );
925
926 dest->copyFormat( src );
927
928 if (down)
929 {
930 ++i;
931 dest = _destList.next();
932 src = _srcList.next();
933 }
934 else
935 {
936 --i;
937 dest = _destList.prev();
938 src = _srcList.prev();
939 }
940
941 if (!src)
942 src = _srcList.last();
943 }
944
945 ok = true;
946 }
947 else
948 {
949 ok = false;
950 }
951
952 // delete tmp;
953 // delete diff;
954
955 return ok;
956 }
957
958 // What is the interval (block)? If your sheet looks like this:
959 // 1 3 5 7 9
960 // then the interval has the length 1 and the delta list is [2].
961 // 2 200 3 300 4 400
962 // Here the interval has length 2 and the delta list is [1,100]
963
964
965 // How big is the interval. It is in the range from [2...n/2].
966 // The case of an interval of length n is handled below.
967 //
968 // We try to find the shortest interval.
969 for ( unsigned int step = 1; step <= _seqList.count() / 2; step++ )
970 {
971 kdDebug() << "Looking for interval: " << step << " seqList count: " << _seqList.count() << endl;
972 // If the interval is of length 'step' then the _seqList size must
973 // be a multiple of 'step'
974 if ( _seqList.count() % step == 0 )
975 {
976 // Be optimistic
977 ok = true;
978
979 deltaList.clear();
980
981 // Guess the delta by looking at cells 0...2*step-1
982 //
983 // Since the interval may be of length 'step' we calculate the delta
984 // between cells 0 and step, 1 and step+1, ...., step-1 and 2*step-1
985 for ( unsigned int t = 0; t < step; t++ )
986 {
987 deltaList.append( new AutoFillDeltaSequence( _seqList.at(t),
988 _seqList.at(t+step) ) );
989 ok = deltaList.getLast()->isOk();
990 }
991
992 /* Verify the delta by looking at cells step..._seqList.count()
993 We only looked at the cells 0 ... '2*step-1'.
994 Now test wether the cells from "(tst-1) * step + s" share the same delta
995 with the cell "tst * step + s" for all test=1..._seqList.count()/step
996 and for all s=0...step-1.
997 */
998 for ( unsigned int tst = 1; ok && ( tst * step < _seqList.count() );
999 tst++ )
1000 {
1001 for ( unsigned int s = 0; ok && ( s < step ); s++ )
1002 {
1003 if ( !_seqList.at( (tst-1) * step + s )->
1004 matches( _seqList.at( tst * step + s ), deltaList.at( s ) ) )
1005 ok = false;
1006 }
1007 }
1008 // Did we find a valid interval ?
1009 if ( ok )
1010 {
1011 unsigned int s = 0;
1012 // Amount of intervals (blocks)
1013 int block = _seqList.count() / step;
1014
1015 // Start iterating with the first cell
1016 Cell * cell;
1017 if (down)
1018 cell = _destList.first();
1019 else
1020 {
1021 cell = _destList.last();
1022 block -= (_seqList.count() - 1);
1023 }
1024
1025
1026 // Loop over all destination cells
1027 while ( cell )
1028 {
1029 kdDebug() << "Valid interval, cell: " << cell->row() << " block: " << block << endl;
1030
1031 // End of block? -> start again from beginning
1032 if (down)
1033 {
1034 if ( s == step )
1035 {
1036 ++block;
1037 s = 0;
1038 }
1039 }
1040 else
1041 {
1042 if ( s >= step )
1043 {
1044 s = step - 1;
1045 ++block;
1046 }
1047 }
1048
1049 kdDebug() << "Step: " << step << " S: " << s << " Block " << block
1050 << " SeqList: " << _seqList.count()
1051 << " SrcList: " << _srcList.count() << " DeltaList: " << deltaList.count()
1052 << endl;
1053
1054 // Set the value of 'cell' by adding 'block' times the delta tp the
1055 // value of cell 's'.
1056 _seqList.at( s )->fillCell( _srcList.at( s ), cell,
1057 deltaList.at( s ), block, down );
1058
1059 if (down)
1060 {
1061 // Next cell
1062 cell = _destList.next();
1063 ++s;
1064 }
1065 else
1066 {
1067 // Previous cell
1068 cell = _destList.prev();
1069 --s;
1070 }
1071 }
1072 }
1073 }
1074 }
1075 return ok;
1076}
1077
1078void Sheet::FillSequenceWithCopy(TQPtrList<Cell>& _srcList,
1079 TQPtrList<Cell>& _destList,
1080 bool down)
1081{
1082 // We did not find any valid interval. So just copy over the marked
1083 // area.
1084 Cell * cell;
1085
1086 if (down)
1087 cell = _destList.first();
1088 else
1089 cell = _destList.last();
1090 int incr = 1;
1091 unsigned int s = 0;
1092 double factor = 1;
1093
1094 if (!down)
1095 s = _srcList.count() - 1;
1096
1097 if ( _srcList.at( s )->value().isNumber() &&
1098 !(_srcList.at( s )->isDate() || _srcList.at( s )->isTime() ) )
1099 factor = _srcList.at( s )->value().asFloat();
1100
1101 while ( cell )
1102 {
1103 if (down)
1104 {
1105 if ( s == _srcList.count() )
1106 s = 0;
1107 }
1108 else
1109 {
1110 if ( s >= _srcList.count() )
1111 s = _srcList.count() - 1;
1112 }
1113
1114 if ( !_srcList.at( s )->text().isEmpty() )
1115 {
1116 if ( _srcList.at( s )->isFormula() )
1117 {
1118 TQString d = _srcList.at( s )->encodeFormula();
1119 cell->setCellText( cell->decodeFormula( d ) );
1120 }
1121 else if(_srcList.at( s )->value().isNumber() && _srcList.count()==1)
1122 {
1123 double val;
1124 int format_type = _srcList.at( s )->formatType();
1125 if ( format_type == Percentage_format )
1126 {
1127 factor = 0.01; // one percent
1128 }
1129 else if ( _srcList.at( s )->isTime() )
1130 {
1131 // FIXME this is a workaround to avoid those nasty one minute off
1132 // "dragging down" time is inaccurate overa large lists!
1133 // This is the best approximation I could find (raphael)
1134 if (down)
1135 {
1136// factor = 1.000002/24. + 0.000000001;
1137 factor = 0.041666751;
1138 }
1139 else
1140 { //when dragging "up" the error must of course be the other way round
1141 factor = 0.0416665;
1142 }
1143 }
1144
1145 if (!down)
1146 val = (_srcList.at( s )->value().asFloat() - (incr * factor));
1147 else
1148 val = (_srcList.at( s )->value().asFloat() + (incr * factor));
1149
1150 TQString tmp;
1151 tmp = tmp.setNum(val);
1152 cell->setCellText( tmp );
1153 ++incr;
1154 }
1155 else if((AutoFillSequenceItem::month != 0L)
1156 && AutoFillSequenceItem::month->find( _srcList.at( s )->text()) != 0L
1157 && AutoFillSequenceItem::month->find( _srcList.at( s )->text()) != AutoFillSequenceItem::month->end()
1158 && _srcList.count() == 1)
1159 {
1160 TQString strMonth=_srcList.at( s )->text();
1161 int i = AutoFillSequenceItem::month->findIndex( strMonth )+incr;
1162 int k = (i) % AutoFillSequenceItem::month->count();
1163 cell->setCellText((*AutoFillSequenceItem::month->at( k )));
1164 incr++;
1165 }
1166 else if(AutoFillSequenceItem::day != 0L
1167 && AutoFillSequenceItem::day->find( _srcList.at( s )->text()) != 0L
1168 && AutoFillSequenceItem::day->find( _srcList.at( s )->text())
1169 != AutoFillSequenceItem::day->end()
1170 && _srcList.count()==1)
1171 {
1172 TQString strDay=_srcList.at( s )->text();
1173 int i = AutoFillSequenceItem::day->findIndex( strDay )+incr;
1174 int k = (i) % AutoFillSequenceItem::day->count();
1175 cell->setCellText((*AutoFillSequenceItem::day->at( k )));
1176 incr++;
1177 }
1178 else
1179 {
1180 TQRegExp number("(\\d+)");
1181 int pos =number.search(_srcList.at( s )->text());
1182 if( pos!=-1 )
1183 {
1184 TQString tmp=number.cap(1);
1185 int num=tmp.toInt()+incr;
1186 cell->setCellText(_srcList.at( s )->text().replace(number,TQString::number(num)));
1187 ++incr;
1188 }
1189 else if ( !_srcList.at( s )->link().isEmpty() )
1190 {
1191 cell->setCellText( _srcList.at( s )->text() );
1192 cell->setLink( _srcList.at( s )->link() );
1193 }
1194 else
1195 {
1196 cell->setCellText( _srcList.at( s )->text() );
1197 }
1198 }
1199 }
1200 else
1201 cell->setCellText( "" );
1202
1203 cell->copyFormat( _srcList.at( s ) );
1204
1205 if (down)
1206 {
1207 cell = _destList.next();
1208 ++s;
1209 }
1210 else
1211 {
1212 cell = _destList.prev();
1213 --s;
1214 }
1215 }
1216 return;
1217}
KSpread::Cell
For every cell in the spread sheet there is a Cell object.
Definition kspread_cell.h:138
KSpread::Cell::encodeFormula
TQString encodeFormula(bool _era=false, int _col=-1, int _row=-1) const
encode a formula into a text representation
Definition kspread_cell.cpp:952
KSpread::Cell::text
TQString text() const
Return the text the user entered.
Definition kspread_cell.cpp:448
KSpread::Cell::row
int row() const
Returns the cell's row.
Definition kspread_cell.cpp:334
KSpread::Cell::value
const Value value() const
Returns the value that this cell holds.
Definition kspread_cell.cpp:471
KSpread::Cell::setLink
void setLink(const TQString &link)
Sets a link for this cell.
Definition kspread_cell.cpp:4692
KSpread::Cell::isFormula
bool isFormula() const
Returns true if this cell holds a formula.
Definition kspread_cell.cpp:436
KSpread::Cell::setCellText
void setCellText(const TQString &_text, bool asString=false)
The high-level method for setting text, when the user inputs it.
Definition kspread_cell.cpp:4621
KSpread::Cell::copyFormat
void copyFormat(const int column, const int row)
Copyies the format from the cell at the position (_column|_row).
Definition kspread_cell.cpp:575
KSpread::Cell::isDate
bool isDate() const
returns true, if cell format is of date type or content is a date
Definition kspread_cell.cpp:4975
KSpread::Cell::isTime
bool isTime() const
returns true, if cell format is of time type or content is a time
Definition kspread_cell.cpp:4983
KSpread::Value
Provides a wrapper for cell value.
Definition kspread_value.h:42
KSpread::Value::isNumber
bool isNumber() const
Returns true if the type of this value is either integer or floating-point.
Definition kspread_value.h:184
KSpread::Value::asDate
TQDate asDate() const
Returns the date representation of this value.
Definition kspread_value.cpp:598
KSpread::Value::type
Type type() const
Returns the type of the value.
Definition kspread_value.cpp:429
KSpread::Value::asFloat
double asFloat() const
Returns the floating-point value of this value.
Definition kspread_value.cpp:501
KSpread::Value::asInteger
long asInteger() const
Returns the integer value of this value.
Definition kspread_value.cpp:473
KSpread::Value::asString
TQString asString() const
Returns the string value of this value.
Definition kspread_value.cpp:524
KSpread::Value::asTime
TQTime asTime() const
Returns the time representation of this value.
Definition kspread_value.cpp:609
KSpread
The KSpread namespace.
Definition commands.h:39