How show grand total in datagridview using vb.net?

Question

 my datagridview table is this

 

Vehicle type	Count of vehicle	Total amount	Extra total amount	Total
2w	9	90	35	125
2w movie	3	50	20	70
4w	3	90	20	110
4w movie	5	110	80	190
Complimentary	3	0	0	0
HT vehicle	2	60	20	60
Valet vehicle	3	90	0	110

 but i want grand total on this datagridview

Vehicle type	Count of vehicle	Total amount	Extra total amount	Total
2w	9	90	35	125
2w movie	3	50	20	70
4w	3	90	20	110
4w movie	5	110	80	190
Complimentary	3	0	0	0
HT vehicle	2	60	20	60
Valet vehicle	3	90	0	110
TOTAL				665

 

 And i am using this code only .please solve as soon as possible .

 

Imports System.IO

Imports System.Data

Imports System.Reflection

Imports iTextSharp.text

Imports iTextSharp.text.pdf

Public Class frmDetailreport

Dim grpid As Integer = 0

Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

grid_data.Tables.Clear()

Dim strsql As String

Dim m_db As New CDataAccess

''strsql = "SELECT tblcollection.TrasactionID, tblcollection.VehicleNumber, tblcollection.VehicleType, Format(INDATE,'dd/mm/yyyy') AS VehicleINDATE, Format(INDATE,'hh:nn:ss') AS VehicleINTIME,InSlip, OutSlip, DeviceID, Amount, SupervisorID, Fromtime, Totime, ExtraAmountperHour, Billtype, SupervisorName, ShiftNo FROM tblcollection where INDATE " ''>=#" & DtFrom.Value.Date & "# and INDATE<=#" & DTto.Value.Date & "#;"

''strsql = "SELECT DISTINCT tblcollection.VehicleType, Count(tblcollection.VehicleType) AS CountOfVehicleType, Count(tblcollection.VehicleType)*Avg(tblcollection.Amount) AS TotalAmount FROM tblcollection WHERE (((tblcollection.DeviceID)='" & cmbdeviceid.Text & "'))GROUP BY tblcollection.VehicleType"

''strsql = "SELECT DISTINCT tblcollection.VehicleType, Count(tblcollection.VehicleType) AS CountOfVehicleType, Count(tblcollection.VehicleType)*Avg(tblcollection.Amount) AS TotalAmount FROM tblcollection WHERE tblcollection.INDATE Between #" & DtFrom.Value.Date.ToString("MM/dd/yyyy") & "# And #" & DTto.Value.Date.ToString("MM/dd/yyyy") & "# AND tblcollection.DeviceID ='" & cmbdeviceid.Text & "' GROUP BY tblcollection.VehicleType"

strsql = "SELECT DISTINCT tblcollection.VehicleType, Count(tblcollection.VehicleType) AS CountOfVehicleType, Count(tblcollection.VehicleType)*Avg(tblcollection.Amount) AS TotalAmount, Count(tblcollection.VehicleType)*Avg(tblcollection.ExtraAmountperHour) AS ExtraTotalAmount, (TotalAmount+extraTotalAmount) AS Total FROM tblcollection WHERE tblcollection.INDATE Between #" & DtFrom.Value.Date.ToString("MM/dd/yyyy 06:00:00") & "# And #" & DTto.Value.Date.ToString("MM/dd/yyyy 05:59:59") & "# AND tblcollection.DeviceID ='" & cmbdeviceid.Text & "' GROUP BY tblcollection.VehicleType"

grid_data = m_db.getdata(strsql, strconnection)

If Not grid_data Is Nothing Then

DataGridView1.DataSource = grid_data.Tables(0)

Else

MsgBox("No Records found", MsgBoxStyle.Information)

End If

End Sub

Private grid_data As New DataSet

Private Sub loaddata()

Dim strsql As String = ""

Dim m_ds As DataSet

Dim m_db As New CDataAccess

'' strsql = "SELECT tblcollection.TrasactionID, tblcollection.VehicleNumber, tblcollection.VehicleType, Format(INDATE,'dd/mm/yyyy') AS VehicleINDATE, Format(INDATE,'hh:nn:ss') AS VehicleINTIME,InSlip, OutSlip, DeviceID, Amount, SupervisorID, Fromtime, Totime, ExtraAmountperHour, Billtype, SupervisorName, ShiftNo FROM tblcollection"

strsql = "SELECT DISTINCT tblcollection.VehicleType, Count(tblcollection.VehicleType) AS CountOfVehicleType, Count(tblcollection.VehicleType)*Avg(tblcollection.Amount) AS TotalAmount, Count(tblcollection.VehicleType)*Avg(tblcollection.ExtraAmountperHour) AS ExtraTotalAmount, (TotalAmount+extraTotalAmount) AS Total FROM tblcollection"

m_ds = m_db.getdata(strsql, strconnection)

If Not m_ds Is Nothing Then

DataGridView1.DataSource = m_ds.Tables(0).DefaultView

DataGridView1.AutoResizeColumns()

DataGridView1.ReadOnly = True

End If

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

If (grid_data.Tables.Count = 0) Then

MsgBox("Click on GenerateReport Before use this Expordata Otherwise No Records will be Found", MsgBoxStyle.Critical)

Return

End If

If (grid_data.Tables.Count = 0) Then

MsgBox("Click on GenerateReport Before use this Expordata Otherwise No Records will be Found", MsgBoxStyle.Critical)

Return

End If

txtbrowse.Text = ""

If SaveFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

txtbrowse.Text = SaveFileDialog1.FileName() + ".pdf"

Dim pdfTable As New PdfPTable(DataGridView1.ColumnCount)

pdfTable.DefaultCell.Padding = 0 '3

pdfTable.WidthPercentage = 101 '30

pdfTable.HorizontalAlignment = Element.ALIGN_RIGHT

pdfTable.DefaultCell.BorderWidth = 1

pdfTable.HorizontalAlignment = 0

Dim headfont As New Font(iTextSharp.text.Font.FontFamily.COURIER, 8, FontStyle.Regular)

Dim datafont As New Font(iTextSharp.text.Font.FontFamily.COURIER, 6, FontStyle.Regular)

For Each column As DataGridViewColumn In DataGridView1.Columns

Dim cell As New PdfPCell(New Phrase(column.HeaderText, headfont))

cell.BackgroundColor = New iTextSharp.text.BaseColor(240, 240, 240)

pdfTable.AddCell(cell)

Next

'Adding DataRow

For Each row As DataGridViewRow In DataGridView1.Rows

For Each cell As DataGridViewCell In row.Cells

pdfTable.AddCell(New PdfPCell(New Phrase(cell.Value.ToString(), datafont)))

Next

Next

'Exporting to PDF

Using stream As New FileStream(txtbrowse.Text, FileMode.Create)

Dim pdfDoc As New Document(PageSize.A4, 20.0F, 20.0F, 20.0F, 20.0F)

Dim iTextfont As New Font(iTextSharp.text.Font.FontFamily.TIMES_ROMAN, 10, 10)

PdfWriter.GetInstance(pdfDoc, stream)

pdfDoc.Open()

pdfDoc.Add(pdfTable)

pdfDoc.Close()

stream.Close()

End Using

End If

End Sub

Public Sub ExportDataToExcel(ByVal FilePath As String, ByVal DataGrid As DataGridView)

Dim _mFileStream As New IO.StreamWriter(FilePath, False)

Try

_mFileStream.WriteLine("<?xml version=""1.0""?>")

_mFileStream.WriteLine("<?mso-application progid=""Excel.Sheet""?>")

_mFileStream.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")

_mFileStream.WriteLine(" <ss:Styles>")

_mFileStream.WriteLine(" <ss:Style ss:ID=""1"">")

_mFileStream.WriteLine(" <ss:Font ss:Bold=""1""/>")

_mFileStream.WriteLine(" <ss:FontName=""Courier New""/>")

_mFileStream.WriteLine(" </ss:Style>")

_mFileStream.WriteLine(" </ss:Styles>")

_mFileStream.WriteLine(" <ss:Worksheet ss:Name=""Sheet1"">")

_mFileStream.WriteLine(" <ss:Table>")

For x As Integer = 0 To DataGrid.Columns.Count - 1

_mFileStream.WriteLine(" <ss:Column ss:Width=""{0}""/>", DataGrid.Columns.Item(x).Width)

Next

_mFileStream.WriteLine(" <ss:Row ss:StyleID=""1"">")

For i As Integer = 0 To DataGrid.Columns.Count - 1

_mFileStream.WriteLine(" <ss:Cell>")

_mFileStream.WriteLine(String.Format(" <ss:Data ss:Type=""String"">{0}</ss:Data>", DataGrid.Columns.Item(i).HeaderText))

_mFileStream.WriteLine("</ss:Cell>")

Next

_mFileStream.WriteLine(" </ss:Row>")

For intRow As Integer = 0 To DataGrid.RowCount - 2

_mFileStream.WriteLine(String.Format(" <ss:Row ss:Height =""{0}"">", DataGrid.Rows(intRow).Height))

For intCol As Integer = 0 To DataGrid.Columns.Count - 1

_mFileStream.WriteLine(" <ss:Cell>")

_mFileStream.WriteLine(String.Format(" <ss:Data ss:Type=""String"">{0}</ss:Data>", DataGrid.Item(intCol, intRow).Value.ToString))

_mFileStream.WriteLine(" </ss:Cell>")

Next

_mFileStream.WriteLine(" </ss:Row>")

Next

_mFileStream.WriteLine(" </ss:Table>")

_mFileStream.WriteLine(" </ss:Worksheet>")

_mFileStream.WriteLine("</ss:Workbook>")

_mFileStream.Close()

_mFileStream.Dispose()

_mFileStream = Nothing

Catch ex As Exception

_mFileStream.Close()

_mFileStream.Dispose()

_mFileStream = Nothing

MessageBox.Show("Error While Exporting Data To Excel. Error : " & ex.Message)

End Try

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Me.Close()

End Sub

Private Sub frmDetailreport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim m_passtypes As New frmDetailreport

If DataGridView1.SelectedRows.Count > 0 Then

m_passtypes.txtVehicleType.Text = DataGridView1.SelectedRows(0).Cells(0).Value.ToString()

m_passtypes.txtVehiclecount.Text = DataGridView1.SelectedRows(0).Cells(1).Value.ToString()

m_passtypes.txtamount.Text = DataGridView1.SelectedRows(0).Cells(2).Value.ToString()

m_passtypes.txtextraamount.Text = DataGridView1.SelectedRows(0).Cells(3).Value.ToString()

m_passtypes.txttotalamount.Text = DataGridView1.SelectedRows(0).Cells(4).Value.ToString()

End If

Dim m_db As New CDataAccess

Dim m_ds As New DataSet, strqry As String = ""

Dim i As Integer

strqry = "SELECT DISTINCT DeviceID FROM tblcollection;"

m_ds = m_db.getdata(strqry, strconnection)

If Not m_ds Is Nothing Then

If m_ds.Tables(0).Rows.Count > 0 Then

For i = 0 To m_ds.Tables(0).Rows.Count - 1

cmbdeviceid.Items.Add(m_ds.Tables(0).Rows(i)(0))

Next i

End If

m_ds.Dispose()

End If

End Sub

Private Sub DataGridView1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridView1.SelectionChanged

If DataGridView1.SelectedRows.Count > 0 Then

txtVehicleType.Text = DataGridView1.SelectedRows(0).Cells(0).Value.ToString()

txtVehiclecount.Text = DataGridView1.SelectedRows(0).Cells(1).Value.ToString()

txtamount.Text = DataGridView1.SelectedRows(0).Cells(2).Value.ToString()

txtextraamount.Text = DataGridView1.SelectedRows(0).Cells(3).Value.ToString()

txttotalamount.Text = DataGridView1.SelectedRows(0).Cells(4).Value.ToString()

End If

End Sub

Private Sub DataGridView1_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting

Dim specialCellStyle As New DataGridViewCellStyle

specialCellStyle.BackColor = Color.Snow

specialCellStyle.ForeColor = Color.Black

Dim regularCellStyle As New DataGridViewCellStyle

regularCellStyle.BackColor = Color.AliceBlue

regularCellStyle.ForeColor = Color.Black

If e.RowIndex Mod 2 = 0 Then

DataGridView1.Rows(e.RowIndex).DefaultCellStyle = specialCellStyle

Else

DataGridView1.Rows(e.RowIndex).DefaultCellStyle = regularCellStyle

End If

End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

Label2.Left -= 10

If Label2.Left <= -Width Then

Label2.Left = Width

End If

End Sub

End Class