Solved

Datagridview with totals for rows and columns

Posted on 2007-04-07
7
2,536 Views
Last Modified: 2013-12-25
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?
0
Comment
Question by:staritsj
  • 4
  • 2
7 Comments
 
LVL 18

Expert Comment

by:Priest04
ID: 18870475
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
 

Author Comment

by:staritsj
ID: 18870771
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
 

Author Comment

by:staritsj
ID: 18871072
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 18

Expert Comment

by:Priest04
ID: 18871954
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
 
LVL 18

Expert Comment

by:Priest04
ID: 18871957
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
 
LVL 18

Assisted Solution

by:Priest04
Priest04 earned 200 total points
ID: 18871967
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
 
LVL 34

Accepted Solution

by:
Sancler earned 300 total points
ID: 18872409
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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now