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