Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / Windows Forms / WinForm Data Binding / August 2008

Tip: Looking for answers? Try searching our database.

DataGridView Summary Row?

Thread view: 
Enable EMail Alerts  Start New Thread
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 Magazines

Get 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 ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.