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