.NET Forum / Windows Forms / WinForm Data Binding / August 2008
DataGridView Summary Row?
|
|
Thread rating:  |
AnikSol - 07 Oct 2006 07:40 GMT Hi All,
In VB 2005, typed dataset, Bound Datagridview, winforms
Is it possible to add a row to a databound datagridview to show the sum of the cells for that particular column? Meaning - there are 15 columns and say 10 rows We need to have a 11th row which would show the sum of the cells for their respective columns.
Any ideas are welcome?
Thanks.
Linda Liu [MSFT] - 09 Oct 2006 04:20 GMT Hi AnikSol,
To add an extra row, which doesn't belong to the underlying data source, into a data grid view, I think you could paint the cells of that row by yourself. To do this, you may handle the CellPainting event of the data grid view.
The following is a sample. In the sample, I draw the extra row in the new row within the data grid view.
public partial class Form1 : Form { public Form1() { InitializeComponent(); this.dataGridView1.CellPainting += new DataGridViewCellPaintingEventHandler(dataGridView1_CellPainting); } void dataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e) { int sum = 0; // only draw the cells of the extra row by ourselves, leaving the rest cells to the system if (e.RowIndex == this.dataGridView1.NewRowIndex && e.ColumnIndex > -1) { for (int i = 0; i < this.dataGridView1.NewRowIndex; i++) { if (this.dataGridView1.Rows[i].Cells[e.ColumnIndex].Value.ToString().Trim() != "") { sum += (int)this.dataGridView1.Rows[i].Cells[e.ColumnIndex].Value; } } e.PaintBackground(e.CellBounds, false); e.Graphics.DrawString(sum.ToString(), this.dataGridView1.Font, Brushes.Black, e.CellBounds.Left + 2,e.CellBounds.Top + 2); e.Handled = true; } } }
Hope this helps. If you have anything unclear, please feel free to let me know.
Sincerely, Linda Liu Microsoft Online Community Support
================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
AnikSol - 10 Oct 2006 07:59 GMT Hello Linda
Thanks a lot.
Since the code is in C#, its hard to understand. Will it be possible in VB?
regards
> Hi AnikSol, > [quoted text clipped - 71 lines] > This posting is provided "AS IS" with no warranties, and confers no > rights. Linda Liu [MSFT] - 10 Oct 2006 09:23 GMT Hi AnikSol,
A sample code of VB version is like below.
Private Sub DataGridView1_CellPainting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting Dim sum As Integer = 0 If (e.RowIndex = DataGridView1.NewRowIndex And e.ColumnIndex > -1) Then For i As Integer = 0 To DataGridView1.NewRowIndex - 1 If (DataGridView1.Rows(i).Cells(e.ColumnIndex).Value.ToString().Trim() <> "") Then sum += Convert.ToInt32(DataGridView1.Rows(i).Cells(e.ColumnIndex).Value) End If Next i
e.PaintBackground(e.CellBounds, False) e.Graphics.DrawString(sum.ToString(), DataGridView1.Font, Brushes.Black, e.CellBounds.Left + 2, e.CellBounds.Top + 2) e.Handled = True End If End Sub
Hope this helps. If you have anything unclear, please feel free to let me know.
Sincerely, Linda Liu Microsoft Online Community Support
AnikSol - 10 Oct 2006 14:02 GMT Hi Linda,
Thanks a lot. a) I have modified so that both sum and average can be displayed in the same row. Do you think this is the right way ? b) In order that we don't display any sum or average values for certain columns (in this case column index = 0,1,2) , is there any other better way than what we have done? If we don't do this, certain columns (like date columns) will show sum or average as Zero.
Once again, thanks a lot.
The modified code as follows:
Private Sub DataGridView1_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
Dim sum As Decimal
Dim avg As Decimal
If e.RowIndex = DataGridView1.NewRowIndex And e.ColumnIndex > -1 Then
Dim cnt As Integer = 0
Dim i As Integer
For i = 0 To DataGridView1.NewRowIndex - 1
If IsNumeric(DataGridView1.Rows(i).Cells(e.ColumnIndex).Value) = True Then
If DataGridView1.Rows(i).Cells(e.ColumnIndex).Value.ToString().Trim() <> "" Then
sum += CType(Me.DataGridView1.Rows(i).Cells(e.ColumnIndex).Value, Decimal)
cnt = cnt + 1
avg = CType(sum / cnt, Decimal)
End If
End If
Next i
e.PaintBackground(e.CellBounds, False)
DataGridView1.Rows.Item(i).Height = 44
If (e.ColumnIndex <> 0) And (e.ColumnIndex <> 1) And e.ColumnIndex <> 2 Then
e.Graphics.DrawString(sum.ToString, Me.DataGridView1.Font, Brushes.Black, e.CellBounds.Left + 2, e.CellBounds.Top + 2)
e.Graphics.DrawString(avg.ToString, Me.DataGridView1.Font, Brushes.Blue, e.CellBounds.Left + 2, e.CellBounds.Bottom - 18)
DataGridView1.Rows.Item(i).DefaultCellStyle.BackColor = Color.Aqua
DataGridView1.Rows.Item(i).HeaderCell.Value = "Sum" & Chr(13) & Chr(13) & "Avg"
DataGridView1.Rows.Item(i).ReadOnly = True
End If
e.Handled = True
End If
End Sub
> Hi AnikSol, > [quoted text clipped - 29 lines] > Linda Liu > Microsoft Online Community Support AnikSol - 10 Oct 2006 14:15 GMT Hello Linda,
One more query - is it possible to have this summary row as the first row of the datagridview?
regards
> Hi Linda, > [quoted text clipped - 106 lines] >> Linda Liu >> Microsoft Online Community Support Linda Liu [MSFT] - 11 Oct 2006 10:46 GMT Hi AnikSol,
Your modified code is good. What you have done to display both sum and average in the same row is correct.
As for the second question, if you want to determine for which column the sum or average values will be displayed by the value type in that column, you may use the ValueType property of that column to distinguish it. The following is a sample.
If (Me.DataGridView1.Columns(e.ColumnIndex).ValueType().Name = "Int32") Then ... End If
As for your third question, I don't think we think we could do this easily because all the cells except those on the new row are populated by data binding now. If you want to draw the summary row as the first row of the data grid view, you need to draw all the cells in the data grid view by yourself.
In this case, the 'data' displayed in the data grid view don't correspond to the real data in the data source, so the data grid view may lost many data bining features, such as the position of current row, sorting on a column, editing the value of a cell and etc.
In short, it is possible to have the summary row as the first row of the data grid view, but I don't recommend you to do like that because this would make the data grid view lose many data binding features.
Hope this helps. If you have any concerns, please feel free to let me know.
Sincerely, Linda Liu Microsoft Online Community Support
AnikSol - 11 Oct 2006 15:06 GMT Linda,
Thanks a ton for your guidance.
regards
> Hi AnikSol, > [quoted text clipped - 33 lines] > Linda Liu > Microsoft Online Community Support hiteshas - 27 Dec 2007 07:45 GMT HI Linda,
I'm new to .NET and am facing the same difficulty. That is, adding a summary row to a datagridview.
The code in this article is excellent. However, it works only when the datagridview's datasource's "AllowNew" property is set to true. If the AllowNew property is set to false, the code does not get invoked.
Can you please suggest a workaround for this, as my requirement is that the "AllowNew" property should be False.
Thanks, Regards,
Hitesh.
>Hi AnikSol, > [quoted text clipped - 28 lines] >Linda Liu >Microsoft Online Community Support hiteshas - 27 Dec 2007 10:18 GMT Ok there,
I figured it out. The code is pasted below. This will not work in the case when the total number of rows in the grid exacly fits the grid bounds. This is because, the painting is done in the area of the grid that is not visible. Still trying to get a work around for that.
Here is the code bit from my sample application:
Private Sub dgvWSData_CellPainting(ByVal sender As Object, ByVal e As System. Windows.Forms.DataGridViewCellPaintingEventArgs) Handles dgvWSData. CellPainting
Dim sum As Integer = 0 '''for totalling the fourth column only If (e.RowIndex = dgvWSData.RowCount - 1 And e.ColumnIndex = 4) Then
For i As Integer = 0 To dgvWSData.RowCount - 1 If dgvWSData.Rows(i).Cells(e.ColumnIndex).Value IsNot Nothing Then If (dgvWSData.Rows(i).Cells(e.ColumnIndex).Value.ToString ().Trim() <> "") Then sum += Convert.ToInt32(dgvWSData.Rows(i).Cells(e. ColumnIndex).Value) End If End If Next i
'get the rectangular bounds of the 4th column of the last row Dim rct As Rectangle = e.CellBounds
're-position the rectangle to be positioned below the last row of the 4th column rct.X = rct.X + rct.Height
e.PaintBackground(rct, True)
e.Graphics.DrawString(sum.ToString(), dgvWSData.Font, _ Brushes.Black, e.CellBounds.Left + 2, e.CellBounds.Top + e. CellBounds.Height + 2) e.Handled = False 'le the datagridview continue with it's own formatting and painting End If
End Sub
>HI Linda, > [quoted text clipped - 18 lines] >>Linda Liu >>Microsoft Online Community Support Ron Frick - 22 Aug 2008 17:36 GMT Thanks so much for posting this. The code works great and saved me alot of headache. So thanks again
From http://www.developmentnow.com/g/31_2006_10_0_0_831733/DataGridView-Summary-Row.ht
Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
|
|