Datagridview with totals for rows and columns

Hi Experts - Is it possible to have an 1 unbound datagridview (containing a fixed number of rows) that receives figures from the user and has BOTH row totals  (last column of datagridview) and column totals (last row of datagridview). Both  totals would need to update on an event. I have been messing around with the cell_value_changed and cell_leave events but i fear that this will cause a stack overflow. Can anyone tell me if it is possible or whether i need to have one of the totals in a seperate grid or textboxes.
Or should i be using some sort of spreadsheet control that accepts formulas?
staritsjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Priest04Commented:
If user is entereing data manually, then you can safely use CellValueChanged event for calculating total values. Just mark the last row and last col as read only and in CellValueChanged event put the code for calculating totals. Basically, you only calculate the total for the current row, current column, and the last value which is the total of all rows (cols) totals.

Goran
0
staritsjAuthor Commented:
Thanks Goran,

Does this still apply if i have several sub total colums within the grid. For example lets say there are 10 data columns in the grid. After the first five there is a column totalling the previous 5 and after the next 5 there is a another sub-total column followed by a grand total column. Will i still be able to use a datagridview and the CellValueChanged event?
0
staritsjAuthor Commented:
Hi Goran, I must be doing something wrong. I set up a little test datagridview with 4 columns and 3 rows. I placed the following on cell_value_changed event for the datagridview dgvTest.
***********************************************************************************************'
Private Sub dgvTest_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvTest.CellValueChanged
        If Me.dgvTest.RowCount > 0 Then
            If e.RowIndex <> -1 Then
'GET ROW TOTAL
                Me.dgvTest.Rows(Me.dgvTest.RowCount - 1).Cells(e.ColumnIndex).Value = CInt(Me.dgvTest.Rows(0).Cells(e.ColumnIndex).Value) + CInt(Me.dgvTest.Rows(1).Cells(e.ColumnIndex).Value)
'GET COLUMN TOTAL
                Me.dgvTest.Rows(e.RowIndex).Cells(3).Value = CInt(Me.dgvTest.Rows(e.RowIndex).Cells(0).Value) + CInt(Me.dgvTest.Rows(e.RowIndex).Cells(1).Value) + CInt(Me.dgvTest.Rows(e.RowIndex).Cells(2).Value)
                End If
        End If
    End Sub
****************************************************************************************************************
The problem appears to be that the 'e' reference is changing as the values in the TOTAL column and TOTAL row are changed. I don't know how else to reference the column and row and change only those totals as you suggested. The problem is that the on_cell_value_changed event keeps firing. Is there anyway of disabling this event after the initial firing.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Priest04Commented:
Yes, CellValueChanged event is fired everytime you set the total value for each cell. So in order to use CellValueChanged event, you would then need to check for the e.ColumnIndex and see if it is the index of a total row(col). Another way, better i believe, would be to use CellBeginEdit and CellEndEdit events

In CellBeginEdit, you should store the current value of the cell in a module variable. This would be the "old" value. Below is the code for catching the new value, and it would be the same for catching the oldvalue, only the OldValue variable should be declared on module level.

In CellEndEdit, which wold be fired only once (not for all total cols/rows) the code would simply look like this:

dim NewValue as double

; get the new value
Double.TryParse(dg.Item(e.columnindex,e.rowindex).value.tostring,newvalue)

' and now for every total column/row you should do calculations, I will give example for 4th column only
dg.item(4,e.rowindex).value=cdbl(dg.item(4,e.rowindex).value)+NewValue-OldValue

Goran
0
Priest04Commented:
One thing more, you could also parse dg.item(4,e.rowindex).value to double, if you are not certain it will always hold the value

dim NewValue as double
Double.TryParse(dg.Item(e.columnindex,e.rowindex).value.tostring,newvalue)

' and now for every total column/row you should do calculations, I will give example for 4th column only
dim TotalValue as double

Double.TryParse(dg.Item(4,e.rowindex).value.tostring,TotalValue)
TotalValue+=NewValue-OldValue
dg.Item(4,e.rowindex).value=TotalValue

Goran
0
Priest04Commented:
I forgot to add, afteryou have loaded data in datagridview, you need to do all total calulcations, since the above example is only used to calculate changed values.

Goran
0
SanclerCommented:
Can I suggest you have a look at the DataGridView's CellValidating and CellValidated events?  Their use might have two advantages.  First, they only fire when the USER changes a value in a cell, not when it is changed programmatically: so that should get rid of unwanted repeat-firing when you are changing the totals.  Secondly, if you use the CellValidating event, you can check for correct user entry (and if it is incorrect, send the user back to the cell concerned) before moving into your total-calculating routine.

Roger
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.