00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022 #include "kspread_dlg_subtotal.h"
00023 #include "kspreadsubtotal.h"
00024 #include "kspread_sheet.h"
00025 #include "kspread_view.h"
00026 #include "kspread_doc.h"
00027 #include "kspread_util.h"
00028
00029 #include <kdebug.h>
00030 #include <klocale.h>
00031 #include <kmessagebox.h>
00032
00033 #include <qcheckbox.h>
00034 #include <qcombobox.h>
00035 #include <qlistview.h>
00036 #include <qmemarray.h>
00037
00038
00039 KSpreadSubtotalDlg::KSpreadSubtotalDlg( KSpreadView * parent, QRect const & selection, const char * name )
00040 : KDialogBase(parent, name, true, i18n( "Subtotals" ), Ok | Cancel | User1, Ok, true, KGuiItem(i18n( "Remove All" )) ),
00041 m_pView( parent ),
00042 m_pSheet( m_pView->activeSheet() ),
00043 m_selection( selection ),
00044 m_dialog( new KSpreadSubtotal( this ) )
00045 {
00046 setButtonBoxOrientation( Vertical );
00047 setMainWidget( m_dialog );
00048
00049 fillColumnBoxes();
00050 fillFunctionBox();
00051 }
00052
00053 KSpreadSubtotalDlg::~KSpreadSubtotalDlg()
00054 {
00055 }
00056
00057 void KSpreadSubtotalDlg::slotOk()
00058 {
00059 int numOfCols = m_selection.width();
00060 QMemArray<int> columns( numOfCols );
00061
00062 int n = 0;
00063 bool empty = true;
00064 int left = m_selection.left();
00065 for ( QListViewItem * item = m_dialog->m_columnList->firstChild(); item; item = item->nextSibling() )
00066 {
00067 if ( ((QCheckListItem * ) item)->isOn() )
00068 {
00069 columns[n] = left + n;
00070 empty = false;
00071 }
00072 else
00073 columns[n] = -1;
00074 ++n;
00075 }
00076
00077 if ( empty )
00078 {
00079 KMessageBox::sorry( this, i18n("You need to select at least one column for adding subtotals.") );
00080 return;
00081 }
00082
00083 if ( m_dialog->m_replaceSubtotals->isChecked() )
00084 removeSubtotalLines();
00085
00086 int mainCol = left + m_dialog->m_columnBox->currentItem();
00087 int bottom = m_selection.bottom();
00088 int top = m_selection.top();
00089 left = m_selection.left();
00090 QString oldText = m_pSheet->cellAt( mainCol, top )->strOutText();
00091 QString newText;
00092 QString result( " " + i18n("Result") );
00093 int lastChangedRow = top;
00094
00095 m_pView->doc()->emitBeginOperation( false );
00096 bool ignoreEmptyCells = m_dialog->m_IgnoreBox->isChecked();
00097 bool addRow;
00098 if ( !m_dialog->m_summaryOnly->isChecked() )
00099 {
00100 int y = top + 1;
00101 kdDebug() << "Starting in row " << y << endl;
00102 while ( y <= bottom )
00103 {
00104 addRow = true;
00105 newText = m_pSheet->cellAt( mainCol, y )->strOutText();
00106
00107 if ( ignoreEmptyCells && (newText.length() == 0) )
00108 {
00109 ++y;
00110 kdDebug() << "Still the same -> " << y << endl;
00111 continue;
00112 }
00113
00114 if (newText != oldText)
00115 {
00116 int saveY = y;
00117 for (int x = 0; x < numOfCols; ++x)
00118 {
00119 kdDebug() << "Column: " << x << ", " << columns[x] << endl;
00120 if (columns[x] != -1)
00121 {
00122 if (!addSubtotal( mainCol, columns[x], y - 1, lastChangedRow, addRow, oldText + result))
00123 reject();
00124
00125 if ( addRow )
00126 {
00127 ++saveY;
00128 ++bottom;
00129 }
00130
00131 addRow = false;
00132 }
00133 }
00134 y = saveY;
00135 lastChangedRow = y;
00136 }
00137 oldText = newText;
00138 ++y;
00139 }
00140
00141 addRow = true;
00142 for ( int x = 0; x < numOfCols; ++x )
00143 {
00144 if ( columns[x] != -1 )
00145 {
00146 if ( !addSubtotal( mainCol, columns[x], y - 1, lastChangedRow, addRow, oldText + result ) )
00147 reject();
00148 addRow = false;
00149 }
00150 }
00151 ++y;
00152 }
00153
00154 if ( m_dialog->m_summaryBelow->isChecked() )
00155 {
00156 addRow = true;
00157 int bottom = m_selection.bottom();
00158 for (int x = 0; x < numOfCols; ++x)
00159 {
00160 if (columns[x] != -1)
00161 {
00162 addSubtotal( mainCol, columns[x], bottom, top, addRow, i18n("Grand Total") );
00163 addRow = false;
00164 }
00165 }
00166 }
00167
00168 m_pView->slotUpdateView( m_pView->activeSheet() );
00169 accept();
00170 }
00171
00172 void KSpreadSubtotalDlg::slotCancel()
00173 {
00174 reject();
00175 }
00176
00177 void KSpreadSubtotalDlg::slotUser1()
00178 {
00179 m_pView->doc()->emitBeginOperation( false );
00180 removeSubtotalLines();
00181 m_pView->slotUpdateView( m_pView->activeSheet() );
00182 accept();
00183 }
00184
00185 void KSpreadSubtotalDlg::removeSubtotalLines()
00186 {
00187 kdDebug() << "Removing subtotal lines" << endl;
00188
00189 int r = m_selection.right();
00190 int l = m_selection.left();
00191 int t = m_selection.top();
00192
00193 KSpreadCell * cell;
00194 QString text;
00195
00196 for ( int y = m_selection.bottom(); y >= t; --y )
00197 {
00198 kdDebug() << "Checking row: " << y << endl;
00199 bool containsSubtotal = false;
00200 for (int x = l; x <= r; ++x )
00201 {
00202 cell = m_pSheet->cellAt( x, y );
00203 if ( cell->isDefault() || !cell->isFormula() )
00204 continue;
00205
00206 text = cell->text();
00207 if ( text.find( "SUBTOTAL" ) != -1 )
00208 {
00209 containsSubtotal = true;
00210 break;
00211 }
00212 }
00213
00214 if ( containsSubtotal )
00215 {
00216 kdDebug() << "Line " << y << " contains a subtotal " << endl;
00217 QRect rect( l, y, m_selection.width(), 1 );
00218
00219 m_pSheet->unshiftColumn( rect );
00220 m_selection.setHeight( m_selection.height() - 1 );
00221 }
00222 }
00223 kdDebug() << "Done removing subtotals" << endl;
00224 }
00225
00226 void KSpreadSubtotalDlg::fillColumnBoxes()
00227 {
00228 int r = m_selection.right();
00229 int row = m_selection.top();
00230
00231 KSpreadCell * cell;
00232 QCheckListItem * item;
00233
00234 QString text;
00235 QString col( i18n( "Column '%1' ") );
00236
00237 for ( int i = m_selection.left(); i <= r; ++i )
00238 {
00239 cell = m_pSheet->cellAt( i, row );
00240 text = cell->strOutText();
00241
00242 if ( text.length() > 0 )
00243 {
00244 text = col.arg( KSpreadCell::columnName( i ) );
00245 }
00246
00247 m_dialog->m_columnBox->insertItem( text );
00248
00249 item = new QCheckListItem( m_dialog->m_columnList,
00250 text,
00251 QCheckListItem::CheckBox );
00252 item->setOn(false);
00253 m_dialog->m_columnList->insertItem( item );
00254 }
00255 }
00256
00257 void KSpreadSubtotalDlg::fillFunctionBox()
00258 {
00259 QStringList lst;
00260 lst << i18n( "Average" );
00261 lst << i18n( "Count" );
00262 lst << i18n( "CountA" );
00263 lst << i18n( "Max" );
00264 lst << i18n( "Min" );
00265 lst << i18n( "Product" );
00266 lst << i18n( "StDev" );
00267 lst << i18n( "StDevP" );
00268 lst << i18n( "Sum" );
00269 lst << i18n( "Var" );
00270 lst << i18n( "VarP" );
00271 m_dialog->m_functionBox->insertStringList(lst);
00272 }
00273
00274 bool KSpreadSubtotalDlg::addSubtotal( int mainCol, int column, int row, int topRow,
00275 bool addRow, QString const & text )
00276 {
00277 kdDebug() << "Adding subtotal: " << mainCol << ", " << column << ", Rows: " << row << ", " << topRow
00278 << ": addRow: " << addRow << ", Text: " << text << endl;
00279 if ( addRow )
00280 {
00281 QRect rect(m_selection.left(), row + 1, m_selection.width(), 1);
00282 if ( !m_pSheet->shiftColumn( rect ) )
00283 return false;
00284
00285 m_selection.setHeight( m_selection.height() + 1 );
00286
00287 KSpreadCell * cell = m_pSheet->nonDefaultCell( mainCol, row + 1 );
00288 cell->setCellText( text );
00289 cell->setTextFontBold( true );
00290 cell->setTextFontItalic( true );
00291 cell->setTextFontUnderline( true );
00292 }
00293
00294 QString colName = KSpreadCell::columnName( column );
00295
00296 QString formula("=SUBTOTAL(");
00297 formula += QString::number( m_dialog->m_functionBox->currentItem() + 1 );
00298 formula += "; ";
00299 formula += colName;
00300 formula += QString::number( topRow );
00301
00302 {
00303 formula += ":";
00304 formula += colName;
00305 formula += QString::number( row );
00306 }
00307 formula += ")";
00308
00309 KSpreadCell * cell = m_pSheet->nonDefaultCell( column, row + 1 );
00310 cell->setCellText( formula );
00311 cell->setTextFontBold( true );
00312 cell->setTextFontItalic( true );
00313 cell->setTextFontUnderline( true );
00314
00315 return true;
00316 }
00317
00318 #include "kspread_dlg_subtotal.moc"
00319