kspread

kspread_autofill.cc

00001 /* This file is part of the KDE project
00002 
00003    Copyright 2005 Raphael Langerhorst <raphael.langerhorst@kdemail.net>
00004    Copyright 2002-2004 Ariya Hidayat <ariya@kde.org>
00005    Copyright 2002-2003 Norbert Andres <nandres@web.de>
00006    Copyright 2002 John Dailey <dailey@vt.edu>
00007    Copyright 2001-2002 Philipp Mueller <philipp.mueller@gmx.de>
00008    Copyright 2000-2002 Laurent Montel <montel@kde.org>
00009    Copyright 2000-2001 Werner Trobin <trobin@kde.org>
00010    Copyright 1999-2001 David Faure <faure@kde.org>
00011    Copyright 1998-2000 Torben Weis <weis@kde.org>
00012    Copyright 1998-1999 Stephan Kulow <coolo@kde.org>
00013    Copyright 1998 Reginald Stadlbauer <reggie@kde.org>
00014 
00015    This library is free software; you can redistribute it and/or
00016    modify it under the terms of the GNU Library General Public
00017    License as published by the Free Software Foundation; either
00018    version 2 of the License, or (at your option) any later version.
00019 
00020    This library is distributed in the hope that it will be useful,
00021    but WITHOUT ANY WARRANTY; without even the implied warranty of
00022    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00023    Library General Public License for more details.
00024 
00025    You should have received a copy of the GNU Library General Public License
00026    along with this library; see the file COPYING.LIB.  If not, write to
00027    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00028  * Boston, MA 02110-1301, USA.
00029 */
00030 
00031 #include <math.h>
00032 
00033 #include <qregexp.h>
00034 
00035 #include <kconfig.h>
00036 #include <kdebug.h>
00037 
00038 #include "kspread_doc.h"
00039 #include "kspread_locale.h"
00040 #include "kspread_sheet.h"
00041 #include "kspread_undo.h"
00042 #include "kspread_value.h"
00043 #include "valueconverter.h"
00044 #include "kspread_autofill.h"
00045 
00046 using namespace KSpread;
00047 
00048 QStringList *AutoFillSequenceItem::month = 0L;
00049 QStringList *AutoFillSequenceItem::shortMonth = 0L;
00050 QStringList *AutoFillSequenceItem::day = 0L;
00051 QStringList *AutoFillSequenceItem::shortDay = 0L;
00052 QStringList *AutoFillSequenceItem::other = 0L;
00053 /**********************************************************************************
00054  *
00055  * AutoFillDeltaSequence
00056  *
00057  **********************************************************************************/
00058 
00059 AutoFillDeltaSequence::AutoFillDeltaSequence( AutoFillSequence *_first, AutoFillSequence *_next )
00060   : m_ok(true),
00061     m_sequence(0L)
00062 {
00063   if ( _first->count() != _next->count() )
00064   {
00065     m_ok = false;
00066     return;
00067   }
00068 
00069   m_sequence = new QMemArray<double> ( _first->count() );
00070 
00071   AutoFillSequenceItem *item = _first->getFirst();
00072   AutoFillSequenceItem *item2 = _next->getFirst();
00073   int i = 0;
00074   // for( item = _first->getFirst(); item != 0L && item2 != 0L; item = _first->getNext() );
00075   for ( i = 0; i < _first->count(); i++ )
00076   {
00077     double d;
00078     if ( !item->getDelta( item2, d ) )
00079       {
00080         m_ok = false;
00081         return;
00082       }
00083     m_sequence->at( i++ ) = d;
00084     item2 = _next->getNext();
00085     item = _first->getNext();
00086   }
00087 }
00088 
00089 AutoFillDeltaSequence::~AutoFillDeltaSequence()
00090 {
00091     delete m_sequence;
00092 }
00093 
00094 bool AutoFillDeltaSequence::equals( AutoFillDeltaSequence *_delta )
00095 {
00096   if ( m_sequence == 0L )
00097     return false;
00098   if ( _delta->getSequence() == 0L )
00099     return false;
00100   if ( m_sequence->size() != _delta->getSequence()->size() )
00101     return false;
00102 
00103   for ( unsigned int i = 0; i < m_sequence->size(); i++ )
00104   {
00105     if ( m_sequence->at( i ) != _delta->getSequence()->at( i ) )
00106       return false;
00107   }
00108 
00109   return true;
00110 }
00111 
00112 double AutoFillDeltaSequence::getItemDelta( int _pos )
00113 {
00114   if ( m_sequence == 0L )
00115     return 0.0;
00116 
00117   return m_sequence->at( _pos );
00118 }
00119 
00120 /**********************************************************************************
00121  *
00122  * AutoFillSequenceItem
00123  *
00124  **********************************************************************************/
00125 
00126 AutoFillSequenceItem::AutoFillSequenceItem( int _i )
00127 {
00128     m_IValue = _i;
00129     m_Type = INTEGER;
00130 }
00131 
00132 AutoFillSequenceItem::AutoFillSequenceItem( double _d )
00133 {
00134     m_DValue = _d;
00135     m_Type = FLOAT;
00136 }
00137 
00138 AutoFillSequenceItem::AutoFillSequenceItem( const QString &_str )
00139 {
00140     m_String = _str;
00141     m_Type = STRING;
00142 
00143     if ( month == 0L )
00144     {
00145         month = new QStringList();
00146         month->append( i18n("January") );
00147         month->append( i18n("February") );
00148         month->append( i18n("March") );
00149         month->append( i18n("April") );
00150         month->append( i18n("May") );
00151         month->append( i18n("June") );
00152         month->append( i18n("July") );
00153         month->append( i18n("August") );
00154         month->append( i18n("September") );
00155         month->append( i18n("October") );
00156         month->append( i18n("November") );
00157         month->append( i18n("December") );
00158     }
00159 
00160     if ( shortMonth == 0L )
00161     {
00162         shortMonth = new QStringList();
00163         shortMonth->append( i18n("Jan") );
00164         shortMonth->append( i18n("Feb") );
00165         shortMonth->append( i18n("Mar") );
00166         shortMonth->append( i18n("Apr") );
00167         shortMonth->append( i18n("May short", "May") );
00168         shortMonth->append( i18n("Jun") );
00169         shortMonth->append( i18n("Jul") );
00170         shortMonth->append( i18n("Aug") );
00171         shortMonth->append( i18n("Sep") );
00172         shortMonth->append( i18n("Oct") );
00173         shortMonth->append( i18n("Nov") );
00174         shortMonth->append( i18n("Dec") );
00175     }
00176 
00177     if ( day == 0L )
00178     {
00179         day = new QStringList();
00180         day->append( i18n("Monday") );
00181         day->append( i18n("Tuesday") );
00182         day->append( i18n("Wednesday") );
00183         day->append( i18n("Thursday") );
00184         day->append( i18n("Friday") );
00185         day->append( i18n("Saturday") );
00186         day->append( i18n("Sunday") );
00187     }
00188 
00189     if ( shortDay == 0L )
00190     {
00191         shortDay = new QStringList();
00192         shortDay->append( i18n("Mon") );
00193         shortDay->append( i18n("Tue") );
00194         shortDay->append( i18n("Wed") );
00195         shortDay->append( i18n("Thu") );
00196         shortDay->append( i18n("Fri") );
00197         shortDay->append( i18n("Sat") );
00198         shortDay->append( i18n("Sun") );
00199     }
00200 
00201     if( other==0L)
00202       {
00203     //  other=new QStringList();
00204     KConfig *config = Factory::global()->config();
00205     config->setGroup( "Parameters" );
00206     other=new QStringList(config->readListEntry("Other list"));
00207       }
00208 
00209     if ( month->find( _str ) != month->end() )
00210     {
00211         m_Type = MONTH;
00212         return;
00213     }
00214 
00215     if ( shortMonth->find( _str ) != shortMonth->end() )
00216     {
00217         m_Type = SHORTMONTH;
00218         return;
00219     }
00220 
00221     if ( day->find( _str ) != day->end() )
00222     {
00223       m_Type = DAY;
00224       return;
00225     }
00226 
00227     if ( shortDay->find( _str ) != shortDay->end() )
00228     {
00229       m_Type = SHORTDAY;
00230       return;
00231     }
00232 
00233     if( other->find(_str)!=other->end())
00234       {
00235     m_Type = OTHER;
00236     m_OtherBegin=0;
00237     m_OtherEnd=other->count();
00238     int index= other->findIndex(_str);
00239     //find end and begin of qstringlist of other.
00240     for ( QStringList::Iterator it = other->find(_str); it != other->end();++it )
00241       {
00242         if((*it)=="\\")
00243           {
00244           m_OtherEnd=index;
00245           break;
00246           }
00247         index++;
00248       }
00249     index= other->findIndex(_str);
00250     for ( QStringList::Iterator it = other->find(_str); it != other->begin();--it )
00251       {
00252         if((*it)=="\\")
00253           {
00254           m_OtherBegin=index;
00255           break;
00256           }
00257         index--;
00258       }
00259     return;
00260       }
00261 
00262     if ( m_String[0] == '=' )
00263         m_Type = FORMULA;
00264 }
00265 
00266 bool AutoFillSequenceItem::getDelta( AutoFillSequenceItem *seq, double &_delta )
00267 {
00268     if ( seq->getType() != m_Type )
00269         return false;
00270 
00271     switch( m_Type )
00272     {
00273     case INTEGER:
00274         _delta = (double)( seq->getIValue() - m_IValue );
00275         return true;
00276     case FLOAT:
00277         _delta = seq->getDValue() - m_DValue;
00278         return true;
00279     case FORMULA:
00280     case STRING:
00281         if ( m_String == seq->getString() )
00282         {
00283             _delta = 0.0;
00284             return true;
00285         }
00286         return false;
00287     case MONTH:
00288         {
00289             int i = month->findIndex( m_String );
00290             int j = month->findIndex( seq->getString() );
00291             int k = j;
00292 
00293             if ( j + 1 == i )
00294                 _delta = -1.0;
00295             else
00296                 _delta = ( double )( k - i );
00297             return true;
00298         }
00299 
00300     case SHORTMONTH:
00301         {
00302             int i = shortMonth->findIndex( m_String );
00303             int j = shortMonth->findIndex( seq->getString() );
00304             int k = j;
00305 
00306             if ( j + 1 == i )
00307                 _delta = -1.0;
00308             else
00309                 _delta = ( double )( k - i );
00310             return true;
00311         }
00312 
00313     case DAY:
00314         {
00315             int i = day->findIndex( m_String );
00316             int j = day->findIndex( seq->getString() );
00317             int k = j;
00318 
00319             if ( j + 1 == i )
00320                 _delta = -1.0;
00321             else
00322                 _delta = ( double )( k - i );
00323             kdDebug() << m_String << " i: " << i << " j: " << j << " k: " << k << " delta: " << _delta << endl;
00324             return true;
00325         }
00326 
00327     case SHORTDAY:
00328         {
00329             int i = shortDay->findIndex( m_String );
00330             int j = shortDay->findIndex( seq->getString() );
00331             int k = j;
00332 
00333             if ( j + 1 == i )
00334                 _delta = -1.0;
00335             else
00336                 _delta = ( double )( k - i );
00337             return true;
00338         }
00339     case OTHER:
00340       {
00341     if( m_OtherEnd!= seq->getIOtherEnd() || m_OtherBegin!= seq->getIOtherBegin())
00342       return false;
00343     int i = other->findIndex( m_String );
00344     int j = other->findIndex( seq->getString() );
00345     int k = j;
00346     if ( j < i )
00347       k += (m_OtherEnd - m_OtherBegin - 1);
00348     /*if ( j + 1 == i )
00349       _delta = -1.0;
00350       else*/
00351       _delta = ( double )( k - i );
00352     return true;
00353       }
00354      default:
00355       return false;
00356     }
00357 }
00358 
00359 QString AutoFillSequenceItem::getSuccessor( int _no, double _delta )
00360 {
00361     QString erg;
00362     switch( m_Type )
00363     {
00364     case INTEGER:
00365         erg.sprintf("%i", m_IValue + _no * (int)_delta );
00366         break;
00367     case FLOAT:
00368         erg.sprintf("%f", m_DValue + (double)_no * _delta );
00369         break;
00370     case FORMULA:
00371     case STRING:
00372         erg = m_String;
00373         break;
00374     case MONTH:
00375         {
00376             int i = month->findIndex( m_String );
00377             int j = i + _no * (int) _delta;
00378             while (j < 0)
00379               j += month->count();
00380             int k = j % month->count();
00381             erg = (*month->at( k ));
00382         }
00383         break;
00384     case SHORTMONTH:
00385         {
00386             int i = shortMonth->findIndex( m_String );
00387             int j = i + _no * (int) _delta;
00388             while (j < 0)
00389               j += shortMonth->count();
00390             int k = j % shortMonth->count();
00391             erg = (*shortMonth->at( k ));
00392         }
00393         break;
00394     case DAY:
00395         {
00396             int i = day->findIndex( m_String );
00397             int j = i + _no * (int) _delta;
00398             while (j < 0)
00399               j += day->count();
00400             int k = j % day->count();
00401             erg = (*day->at( k ));
00402         }
00403     break;
00404     case SHORTDAY:
00405         {
00406             int i = shortDay->findIndex( m_String );
00407             int j = i + _no * (int) _delta;
00408             while (j < 0)
00409               j += shortDay->count();
00410             int k = j % shortDay->count();
00411             erg = (*shortDay->at( k ));
00412         }
00413         break;
00414     case OTHER:
00415       {
00416      int i = other->findIndex( m_String )-(m_OtherBegin+1);
00417      int j = i + _no * (int) _delta;
00418      int k = j % (m_OtherEnd - m_OtherBegin-1);
00419      erg = (*other->at( (k+m_OtherBegin+1) ));
00420       }
00421      case TIME:
00422      case DATE:
00423       // gets never called but fixes a warning while compiling
00424       break;
00425     }
00426 
00427     return QString( erg );
00428 }
00429 
00430 QString AutoFillSequenceItem::getPredecessor( int _no, double _delta )
00431 {
00432   QString erg;
00433   switch( m_Type )
00434   {
00435    case INTEGER:
00436     erg.sprintf("%i", m_IValue - _no * (int)_delta );
00437     break;
00438    case FLOAT:
00439     erg.sprintf("%f", m_DValue - (double)_no * _delta );
00440     break;
00441    case FORMULA:
00442    case STRING:
00443     erg = m_String;
00444     break;
00445    case MONTH:
00446     {
00447       int i = month->findIndex( m_String );
00448       int j = i - _no * (int) _delta;
00449       while ( j < 0 )
00450         j += month->count();
00451       int k = j % month->count();
00452       erg = (*month->at( k ));
00453     }
00454     break;
00455    case SHORTMONTH:
00456     {
00457       int i = shortMonth->findIndex( m_String );
00458       int j = i - _no * (int) _delta;
00459       while ( j < 0 )
00460         j += shortMonth->count();
00461       int k = j % shortMonth->count();
00462       erg = (*shortMonth->at( k ));
00463     }
00464     break;
00465    case DAY:
00466     {
00467       int i = day->findIndex( m_String );
00468       int j = i - _no * (int) _delta;
00469       while ( j < 0 )
00470         j += day->count();
00471       int k = j % day->count();
00472       erg = (*day->at( k ));
00473     }
00474     break;
00475    case SHORTDAY:
00476     {
00477       int i = shortDay->findIndex( m_String );
00478       int j = i - _no * (int) _delta;
00479       while ( j < 0 )
00480         j += shortDay->count();
00481       int k = j % shortDay->count();
00482       erg = (*shortDay->at( k ));
00483     }
00484     break;
00485    case OTHER:
00486     {
00487       int i = other->findIndex( m_String ) - (m_OtherBegin + 1);
00488       int j = i - _no * (int) _delta;
00489       while ( j < 0 )
00490         j += (m_OtherEnd - m_OtherBegin - 1);
00491       int k = j % (m_OtherEnd - m_OtherBegin - 1);
00492       erg = (*other->at( (k + m_OtherBegin + 1) ));
00493     }
00494    case TIME:
00495    case DATE:
00496     // gets never called but fixes a warning while compiling
00497     break;
00498   }
00499 
00500   return QString( erg );
00501 }
00502 
00503 /**********************************************************************************
00504  *
00505  * AutoFillSequence
00506  *
00507  **********************************************************************************/
00508 
00509 AutoFillSequence::AutoFillSequence( Cell *_cell )
00510 {
00511     sequence.setAutoDelete( true );
00512 
00513     if ( _cell->isFormula() )
00514     {
00515         QString d = _cell->encodeFormula();
00516         sequence.append( new AutoFillSequenceItem( d ) );
00517     }
00518     else if ( _cell->value().isNumber() )
00519     {
00520         if ( floor( _cell->value().asFloat() ) == _cell->value().asFloat() )
00521         {
00522             sequence.append( new AutoFillSequenceItem( (int)_cell->value().asFloat()) );
00523         }
00524         else
00525             sequence.append( new AutoFillSequenceItem(_cell->value().asFloat() ) );
00526     }
00527     else if ( !_cell->text().isEmpty() )
00528         sequence.append( new AutoFillSequenceItem( _cell->text() ) );
00529 }
00530 
00531 bool AutoFillSequence::matches( AutoFillSequence* _seq, AutoFillDeltaSequence *_delta )
00532 {
00533     AutoFillDeltaSequence delta( this, _seq );
00534     if ( !delta.isOk() )
00535         return false;
00536 
00537     if ( delta.equals( _delta ) )
00538          return true;
00539 
00540     return false;
00541 }
00542 
00543 void AutoFillSequence::fillCell( Cell *src, Cell *dest, AutoFillDeltaSequence *delta, int _block, bool down )
00544 {
00545     QString erg = "";
00546 
00547     // Special handling for formulas
00548     if ( sequence.first() != 0L && sequence.first()->getType() == AutoFillSequenceItem::FORMULA )
00549     {
00550         QString f = dest->decodeFormula( sequence.first()->getString() );
00551         dest->setCellText( f );
00552         dest->copyFormat( src );
00553         return;
00554     }
00555 
00556     AutoFillSequenceItem *item;
00557     int i = 0;
00558     if (down)
00559     {
00560       for ( item = sequence.first(); item != 0L; item = sequence.next() )
00561         erg += item->getSuccessor( _block, delta->getItemDelta( i++ ) );
00562     }
00563     else
00564     {
00565       for ( item = sequence.first(); item != 0L; item = sequence.next() )
00566         erg += item->getPredecessor( _block, delta->getItemDelta( i++ ) );
00567     }
00568 
00569     dest->setCellText( erg );
00570     dest->copyFormat( src );
00571 }
00572 
00573 /**********************************************************************************
00574  *
00575  * Sheet
00576  *
00577  **********************************************************************************/
00578 
00579 void Sheet::autofill( QRect &src, QRect &dest )
00580 {
00581     if (src == dest)
00582     {
00583         return;
00584     }
00585 
00586     setRegionPaintDirty( dest );
00587 
00588     doc()->emitBeginOperation();
00589 
00590     if ( !doc()->undoLocked() )
00591     {
00592       UndoAutofill *undo = new UndoAutofill( doc(), this, dest );
00593       doc()->addCommand( undo );
00594     }
00595 
00596     // disable the update of the max sroll range on each cell insertion
00597     // Bug 124806: creating series takes extremely long time
00598     enableScrollBarUpdates(false);
00599 
00600     // Fill from left to right
00601     if ( src.left() == dest.left() && src.right() < dest.right() )
00602     {
00603         for ( int y = src.top(); y <= src.bottom(); y++ )
00604         {
00605             int x;
00606             QPtrList<Cell> destList;
00607             for ( x = src.right() + 1; x <= dest.right(); x++ )
00608                 destList.append( nonDefaultCell( x, y ) );
00609             QPtrList<Cell> srcList;
00610             for ( x = src.left(); x <= src.right(); x++ )
00611                 srcList.append( cellAt( x, y ) );
00612             QPtrList<AutoFillSequence> seqList;
00613             seqList.setAutoDelete( true );
00614             for ( x = src.left(); x <= src.right(); x++ )
00615                 seqList.append( new AutoFillSequence( cellAt( x, y ) ) );
00616             fillSequence( srcList, destList, seqList );
00617         }
00618     }
00619 
00620     // Fill from top to bottom
00621     if ( src.top() == dest.top() && src.bottom() < dest.bottom() )
00622     {
00623         for ( int x = src.left(); x <= dest.right(); x++ )
00624         {
00625             int y;
00626             QPtrList<Cell> destList;
00627             for ( y = src.bottom() + 1; y <= dest.bottom(); y++ )
00628                 destList.append( nonDefaultCell( x, y ) );
00629             QPtrList<Cell> srcList;
00630             for ( y = src.top(); y <= src.bottom(); y++ )
00631             {
00632                 srcList.append( cellAt( x, y ) );
00633             }
00634             QPtrList<AutoFillSequence> seqList;
00635             seqList.setAutoDelete( true );
00636             for ( y = src.top(); y <= src.bottom(); y++ )
00637                 seqList.append( new AutoFillSequence( cellAt( x, y ) ) );
00638             fillSequence( srcList, destList, seqList );
00639         }
00640     }
00641 
00642     // Fill from right to left
00643     if ( ( src.left() == dest.right() || src.left() == dest.right() - 1) && src.right() >= dest.right() )
00644     {
00645         if ( src.left() != dest.right() )
00646             dest.setRight( dest.right() - 1 );
00647 
00648         for ( int y = dest.top(); y <= dest.bottom(); y++ )
00649         {
00650             int x;
00651             QPtrList<Cell> destList;
00652 
00653             for ( x = dest.left(); x < src.left(); x++ )
00654             {
00655                 destList.append( nonDefaultCell( x, y ) );
00656             }
00657             QPtrList<Cell> srcList;
00658             for ( x = src.left(); x <= src.right(); x++ )
00659             {
00660                 srcList.append( cellAt( x, y ) );
00661             }
00662             QPtrList<AutoFillSequence> seqList;
00663             seqList.setAutoDelete( true );
00664             for ( x = src.left(); x <= src.right(); x++ )
00665                 seqList.append( new AutoFillSequence( cellAt( x, y ) ) );
00666             fillSequence( srcList, destList, seqList, false );
00667         }
00668     }
00669 
00670     // Fill from bottom to top
00671     if ( (src.top() == dest.bottom() || src.top() == (dest.bottom() - 1) ) && src.bottom() >= dest.bottom() )
00672     {
00673         if (src.top() != dest.bottom() )
00674             dest.setBottom(dest.bottom() - 1);
00675         int startVal = QMIN( dest.left(), src.left());
00676         int endVal = QMAX(src.right(), dest.right());
00677         for ( int x = startVal; x <= endVal; x++ )
00678         {
00679             int y;
00680             QPtrList<Cell> destList;
00681             for ( y = dest.top(); y < src.top(); y++ )
00682                 destList.append( nonDefaultCell( x, y ) );
00683             QPtrList<Cell> srcList;
00684             for ( y = src.top(); y <= src.bottom(); ++y )
00685             {
00686                 srcList.append( cellAt( x, y ) );
00687             }
00688             QPtrList<AutoFillSequence> seqList;
00689             seqList.setAutoDelete( true );
00690             for ( y = src.top(); y <= src.bottom(); y++ )
00691                 seqList.append( new AutoFillSequence( cellAt( x, y ) ) );
00692             fillSequence( srcList, destList, seqList, false );
00693         }
00694     }
00695 
00696     // update the max sroll range ONCE here
00697     enableScrollBarUpdates(true);
00698     checkRangeHBorder(dest.right());
00699     checkRangeVBorder(dest.bottom());
00700 
00701     emit sig_updateView( this );
00702     // doc()->emitEndOperation();
00703 }
00704 
00705 
00706 void Sheet::fillSequence( QPtrList<Cell>& _srcList,
00707                  QPtrList<Cell>& _destList,
00708                                  QPtrList<AutoFillSequence>& _seqList,
00709                                  bool down)
00710 {
00711     doc()->emitBeginOperation(true);
00712 
00713     /* try finding an interval to use to fill the sequence */
00714     if (!FillSequenceWithInterval(_srcList, _destList, _seqList, down))
00715     {
00716       /* if no interval was found, just copy down through */
00717       FillSequenceWithCopy(_srcList, _destList, down);
00718     }
00719 
00720     doc()->emitEndOperation();
00721 
00722 }
00723 
00724 QVariant getDiff( const Value& value1, const Value& value2  , AutoFillSequenceItem::Type type  )
00725 {
00726   if ( type == AutoFillSequenceItem::FLOAT )
00727       return QVariant( value2.asFloat() - value1.asFloat() );
00728   if ( type == AutoFillSequenceItem::TIME || type == AutoFillSequenceItem::DATE )
00729       return QVariant( (int)( value2.asInteger() - value1.asInteger() ) );
00730 
00731   return QVariant( (int)0 );
00732   // note: date and time difference can be calculated as
00733   // the difference of the serial number
00734  /* if( (type == AutoFillSequenceItem::FLOAT) ||
00735       (type == AutoFillSequenceItem::DATE) ||
00736       (type == AutoFillSequenceItem::TIME) )
00737     return ( value2.asFloat() - value1.asFloat() );
00738   else
00739     return 0.0;*/
00740 }
00741 
00742 bool Sheet::FillSequenceWithInterval(QPtrList<Cell>& _srcList,
00743                                             QPtrList<Cell>& _destList,
00744                                             QPtrList<AutoFillSequence>& _seqList,
00745                                             bool down)
00746 {
00747   if (_srcList.first()->isFormula())
00748     return false;
00749 
00750   QPtrList<AutoFillDeltaSequence> deltaList;
00751   deltaList.setAutoDelete( true );
00752   bool ok = false;
00753 
00754   if ( _srcList.first()->value().isNumber() || _srcList.first()->isDate() || _srcList.first()->isTime() )
00755   {
00756     AutoFillSequenceItem::Type type;
00757 
00758     QValueVector< QVariant > tmp( _seqList.count() );  /*= new QValueList< QVariant > ( _seqList.count() )*/;
00759     QValueVector< QVariant > diff( _seqList.count() ); /*= new QValueList< QVariant > ( _seqList.count() )*/;
00760     int p = -1;
00761     int count = 0;
00762     int tmpcount = 0;
00763 
00764     Cell * cell = _srcList.first();
00765     Cell * cell2 = _srcList.next();
00766 
00767     bool singleCellOnly = (cell2 == 0);
00768 
00769     if ( cell->isDate() )
00770       type = AutoFillSequenceItem::DATE;
00771     else if ( cell->isTime() )
00772       type = AutoFillSequenceItem::TIME;
00773     else if ( cell->value().isNumber() )
00774       type = AutoFillSequenceItem::FLOAT;
00775     else
00776       return false; // Cannot happen due to if condition
00777 
00778     while ( cell && (cell2 || singleCellOnly) )
00779     {
00780 
00781       Value cellValue = cell->value();
00782       Value cell2Value;
00783 
00784       //If we only have a single cell, the interval will depend upon the data type.
00785       //- For numeric values, set the interval to 0 as we don't know what might be useful as a sequence
00786       //- For time values, set the interval to one hour, as this will probably be the most useful setting
00787       //- For date values, set the interval to one day, as this will probably be the most useful setting
00788       //
00789       //Note that the above options were chosen for consistency with Excel.  Gnumeric (1.59) sets
00790       //the interval to 0 for all types, OpenOffice.org (2.00) uses increments of 1.00, 1 hour and 1 day
00791       //respectively
00792       if (singleCellOnly)
00793       {
00794         if (type == AutoFillSequenceItem::FLOAT)
00795         cell2Value = cellValue;
00796     else if ( type == AutoFillSequenceItem::TIME)
00797         cell2Value = Value( cellValue.asTime().addSecs( 60*60 ) );
00798     else if ( type == AutoFillSequenceItem::DATE)
00799         cell2Value = Value ( cellValue.asDate().addDays( 1 ) );
00800       }
00801       else
00802       {
00803     cell2Value = cell2->value();
00804 
00805         // check if both cells contain the same type
00806         if ( ( !cellValue.isNumber() )
00807            || ( cell2->isDate() && type != AutoFillSequenceItem::DATE )
00808            || ( cell2->isTime() && type != AutoFillSequenceItem::TIME ) )
00809         {
00810             count = 0;
00811             ok = false;
00812             break;
00813         }
00814       }
00815 
00816       QVariant delta = getDiff(cellValue , cell2Value , type );
00817 
00818       if (count < 1)
00819       {
00820         p = count;
00821         diff[ count++ ] = delta;
00822       }
00823       else
00824       {
00825         // same value again?
00826         if (diff[ p ] == delta)
00827         {
00828           // store it somewhere else for the case we need it later
00829           ++p;
00830           tmp[ tmpcount++ ] = delta;
00831         }
00832         else
00833         {
00834           // if we have saved values in another buffer we have to insert them first
00835           if ( tmpcount > 0 )
00836           {
00837             for ( int i = 0; i < tmpcount; ++i )
00838             {
00839               diff[ count++ ] = tmp.at( i );
00840             }
00841 
00842             tmpcount = 0;
00843           }
00844 
00845           // insert the value
00846           p = 0;
00847           diff[ count++ ] = delta;
00848         }
00849       }
00850 
00851       // check next cell pair
00852       cell  = cell2;
00853       cell2 = _srcList.next();
00854     }
00855 
00856     // we have found something:
00857     if (count > 0 && (tmpcount > 0 || count == 1))
00858     {
00859       QVariant cellValue( (int) 0 );
00860 
00861       Cell * dest;
00862       Cell * src;
00863 
00864       int i = tmpcount;
00865       if (down)
00866       {
00867         dest = _destList.first();
00868         src  = _srcList.last();
00869       }
00870       else
00871       {
00872         dest = _destList.last();
00873         src  = _srcList.first();
00874 
00875         i   *= -1;
00876       }
00877 
00878       if ( type == AutoFillSequenceItem::FLOAT )
00879           cellValue = src->value().asFloat();
00880       else
00881           cellValue = (int)src->value().asInteger();
00882 
00883       QString res;
00884       // copy all the data
00885       while (dest)
00886       {
00887         if (down)
00888         {
00889           while ( i >= count )
00890             i -= count;
00891         }
00892         else
00893         {
00894           while ( i < 0)
00895             i += count;
00896         }
00897 
00898     QVariant currentDiff = diff.at( i );
00899 
00900     if (cellValue.type() == QVariant::Double)
00901             if (down)
00902                     cellValue = cellValue.asDouble() + currentDiff.asDouble();
00903             else
00904                     cellValue = cellValue.asDouble() -  currentDiff.asDouble();
00905     else
00906         if (down)
00907             cellValue = cellValue.asInt() + currentDiff.asInt();
00908         else
00909             cellValue = cellValue.asInt() - currentDiff.asInt();
00910 
00911     if ( type == AutoFillSequenceItem::TIME)
00912     {
00913         Value timeValue = doc()->converter()->asTime( Value(cellValue.asInt()) );
00914         Value stringValue = doc()->converter()->asString( timeValue );
00915         dest->setCellText( stringValue.asString() );
00916     }
00917     else if ( type == AutoFillSequenceItem::DATE)
00918     {
00919         Value dateValue = doc()->converter()->asDate( Value(cellValue.asInt()) );
00920         Value stringValue = doc()->converter()->asString( dateValue );
00921         dest->setCellText( stringValue.asString() );
00922     }
00923     else
00924             dest->setCellText( cellValue.asString() );
00925 
00926     dest->copyFormat( src );
00927 
00928         if (down)
00929         {
00930           ++i;
00931           dest = _destList.next();
00932           src = _srcList.next();
00933         }
00934         else
00935         {
00936           --i;
00937           dest = _destList.prev();
00938           src = _srcList.prev();
00939         }
00940 
00941         if (!src)
00942           src = _srcList.last();
00943       }
00944 
00945       ok = true;
00946     }
00947     else
00948     {
00949       ok = false;
00950     }
00951 
00952    // delete tmp;
00953    // delete diff;
00954 
00955     return ok;
00956   }
00957 
00958   // What is the interval (block)? If your sheet looks like this:
00959   // 1 3 5 7 9
00960   // then the interval has the length 1 and the delta list is [2].
00961   // 2 200 3 300 4 400
00962   // Here the interval has length 2 and the delta list is [1,100]
00963 
00964 
00965   // How big is the interval. It is in the range from [2...n/2].
00966   // The case of an interval of length n is handled below.
00967   //
00968   // We try to find the shortest interval.
00969   for ( unsigned int step = 1; step <= _seqList.count() / 2; step++ )
00970   {
00971     kdDebug() << "Looking for interval: " << step << " seqList count: " << _seqList.count() << endl;
00972     // If the interval is of length 'step' then the _seqList size must
00973     // be a multiple of 'step'
00974     if ( _seqList.count() % step == 0 )
00975     {
00976       // Be optimistic
00977       ok = true;
00978 
00979       deltaList.clear();
00980 
00981       // Guess the delta by looking at cells 0...2*step-1
00982       //
00983       // Since the interval may be of length 'step' we calculate the delta
00984       // between cells 0 and step, 1 and step+1, ...., step-1 and 2*step-1
00985       for ( unsigned int t = 0; t < step; t++ )
00986       {
00987     deltaList.append( new AutoFillDeltaSequence( _seqList.at(t),
00988                              _seqList.at(t+step) ) );
00989     ok = deltaList.getLast()->isOk();
00990       }
00991 
00992       /* Verify the delta by looking at cells step..._seqList.count()
00993      We only looked at the cells 0 ... '2*step-1'.
00994      Now test wether the cells from "(tst-1) * step + s" share the same delta
00995      with the cell "tst * step + s" for all test=1..._seqList.count()/step
00996      and for all s=0...step-1.
00997       */
00998       for ( unsigned int tst = 1; ok && ( tst * step < _seqList.count() );
00999         tst++ )
01000       {
01001     for ( unsigned int s = 0; ok && ( s < step ); s++ )
01002     {
01003       if ( !_seqList.at( (tst-1) * step + s )->
01004            matches( _seqList.at( tst * step + s ), deltaList.at( s ) ) )
01005         ok = false;
01006     }
01007       }
01008       // Did we find a valid interval ?
01009       if ( ok )
01010       {
01011     unsigned int s = 0;
01012     // Amount of intervals (blocks)
01013     int block = _seqList.count() / step;
01014 
01015     // Start iterating with the first cell
01016     Cell * cell;
01017         if (down)
01018           cell = _destList.first();
01019         else
01020         {
01021           cell = _destList.last();
01022           block -= (_seqList.count() - 1);
01023         }
01024 
01025 
01026     // Loop over all destination cells
01027     while ( cell )
01028     {
01029           kdDebug() << "Valid interval, cell: " << cell->row() << " block: " << block << endl;
01030 
01031       // End of block? -> start again from beginning
01032           if (down)
01033           {
01034             if ( s == step )
01035             {
01036               ++block;
01037               s = 0;
01038             }
01039           }
01040           else
01041           {
01042             if ( s >= step )
01043             {
01044               s = step - 1;
01045               ++block;
01046             }
01047           }
01048 
01049           kdDebug() << "Step: " << step << " S: " << s << " Block " << block
01050                     << " SeqList: " << _seqList.count()
01051                     << " SrcList: " << _srcList.count() << " DeltaList: " << deltaList.count()
01052                     << endl;
01053 
01054       // Set the value of 'cell' by adding 'block' times the delta tp the
01055       // value of cell 's'.
01056       _seqList.at( s )->fillCell( _srcList.at( s ), cell,
01057                       deltaList.at( s ), block, down );
01058 
01059           if (down)
01060           {
01061             // Next cell
01062             cell = _destList.next();
01063             ++s;
01064           }
01065           else
01066           {
01067             // Previous cell
01068             cell = _destList.prev();
01069             --s;
01070           }
01071     }
01072       }
01073     }
01074   }
01075   return ok;
01076 }
01077 
01078 void Sheet::FillSequenceWithCopy(QPtrList<Cell>& _srcList,
01079                                         QPtrList<Cell>& _destList,
01080                                         bool down)
01081 {
01082   // We did not find any valid interval. So just copy over the marked
01083   // area.
01084   Cell * cell;
01085 
01086   if (down)
01087     cell = _destList.first();
01088   else
01089     cell = _destList.last();
01090   int incr = 1;
01091   unsigned int s = 0;
01092   double factor = 1;
01093 
01094   if (!down)
01095     s = _srcList.count() - 1;
01096 
01097   if ( _srcList.at( s )->value().isNumber() &&
01098        !(_srcList.at( s )->isDate() || _srcList.at( s )->isTime() ) )
01099     factor = _srcList.at( s )->value().asFloat();
01100 
01101   while ( cell )
01102   {
01103     if (down)
01104     {
01105       if ( s == _srcList.count() )
01106         s = 0;
01107     }
01108     else
01109     {
01110       if ( s >= _srcList.count() )
01111         s = _srcList.count() - 1;
01112     }
01113 
01114     if ( !_srcList.at( s )->text().isEmpty() )
01115     {
01116       if ( _srcList.at( s )->isFormula() )
01117       {
01118         QString d = _srcList.at( s )->encodeFormula();
01119         cell->setCellText( cell->decodeFormula( d ) );
01120       }
01121       else if(_srcList.at( s )->value().isNumber() && _srcList.count()==1)
01122       {
01123         double val;
01124         int format_type = _srcList.at( s )->formatType();
01125         if ( format_type == Percentage_format )
01126         {
01127           factor = 0.01;  // one percent
01128         }
01129         else if ( _srcList.at( s )->isTime() )
01130         {
01131           // FIXME this is a workaround to avoid those nasty one minute off
01132           //       "dragging down" time is inaccurate overa large lists!
01133           //       This is the best approximation I could find (raphael)
01134           if (down)
01135           {
01136 //             factor = 1.000002/24.  + 0.000000001;
01137             factor = 0.041666751;
01138           }
01139           else
01140           {  //when dragging "up" the error must of course be the other way round
01141             factor = 0.0416665;
01142           }
01143         }
01144 
01145         if (!down)
01146           val = (_srcList.at( s )->value().asFloat() - (incr * factor));
01147         else
01148           val = (_srcList.at( s )->value().asFloat() + (incr * factor));
01149 
01150         QString tmp;
01151         tmp = tmp.setNum(val);
01152         cell->setCellText( tmp );
01153         ++incr;
01154       }
01155       else if((AutoFillSequenceItem::month != 0L)
01156           && AutoFillSequenceItem::month->find( _srcList.at( s )->text()) != 0L
01157           && AutoFillSequenceItem::month->find( _srcList.at( s )->text()) != AutoFillSequenceItem::month->end()
01158           && _srcList.count() == 1)
01159       {
01160     QString strMonth=_srcList.at( s )->text();
01161     int i = AutoFillSequenceItem::month->findIndex( strMonth )+incr;
01162     int k = (i) % AutoFillSequenceItem::month->count();
01163     cell->setCellText((*AutoFillSequenceItem::month->at( k )));
01164         incr++;
01165       }
01166       else if(AutoFillSequenceItem::day != 0L
01167           && AutoFillSequenceItem::day->find( _srcList.at( s )->text()) != 0L
01168           && AutoFillSequenceItem::day->find( _srcList.at( s )->text())
01169              != AutoFillSequenceItem::day->end()
01170           && _srcList.count()==1)
01171       {
01172     QString strDay=_srcList.at( s )->text();
01173     int i = AutoFillSequenceItem::day->findIndex( strDay )+incr;
01174     int k = (i) % AutoFillSequenceItem::day->count();
01175     cell->setCellText((*AutoFillSequenceItem::day->at( k )));
01176         incr++;
01177       }
01178       else
01179       {
01180     QRegExp number("(\\d+)");
01181     int pos =number.search(_srcList.at( s )->text());
01182     if( pos!=-1 )
01183     {
01184       QString tmp=number.cap(1);
01185       int num=tmp.toInt()+incr;
01186       cell->setCellText(_srcList.at( s )->text().replace(number,QString::number(num)));
01187           ++incr;
01188     }
01189     else if ( !_srcList.at( s )->link().isEmpty() )
01190         {
01191       cell->setCellText( _srcList.at( s )->text() );
01192       cell->setLink( _srcList.at( s )->link() );
01193         }
01194         else
01195         {
01196       cell->setCellText( _srcList.at( s )->text() );
01197         }
01198       }
01199     }
01200     else
01201       cell->setCellText( "" );
01202 
01203     cell->copyFormat( _srcList.at( s ) );
01204 
01205     if (down)
01206     {
01207       cell = _destList.next();
01208       ++s;
01209     }
01210     else
01211     {
01212       cell = _destList.prev();
01213       --s;
01214     }
01215   }
01216   return;
01217 }
KDE Home | KDE Accessibility Home | Description of Access Keys