CRIAR UM RELATORIO SOMANDO HORAS DE FUNCIONARIOS
precsma dica para fazer a seguinte consulta:
Em um gridview preciso listar asssim:
101101-9 25/09/2013 08:30:00 25/09/2013 18:00:00 02:00:00 07:30:00
101101-9 26/09/2013 08:30:00 26/09/2013 18:00:00 02:00:00 07:30:00
TOTAL 15:00:00
102102-8 25/09/2013 08:30:00 25/09/2013 18:00:00 02:00:00 07:30:00
102102-8 26/09/2013 08:30:00 26/09/2013 18:00:00 02:00:00 07:30:00
TOTAL 15:00:00
E ASSIM POR DIANTE......
Desde já agradeço a atenção.
Henrique
ABAIXO ESTà O CÓDIGO PARA LISTAR UM ÚNICO FUNCIONÃRIO: E ESTà FUNCIONANDO BELEZA.
Imports Microsoft.Office.Interop
Imports System.Threading.Thread
Imports System.Globalization
Public Class Pesquisa
Dim aidtra(1) As Integer
Dim anrpm(1) As String
Dim adte(1) As DateTime
Dim adts(1) As DateTime
Dim aservico(1) As String
Dim adesconto(1) As String
Dim atotalhoras(1) As string
Dim pegatotal As String
Dim vnomepega As String
Private Sub Pesquisa_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dgvTrabalho.AlternatingRowsDefaultCellStyle.BackColor = Color.LightYellow
dgvTrabalho.Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(3).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(4).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(5).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
PopulaCombo()
[ô] Sortmode = nosortable
End Sub
Private Sub PopulaCombo()
Dim cs As String = My.Settings.PontoConnectionString
Dim conect As New OleDb.OleDbConnection
conect.ConnectionString = cs
Try
conect.Open()
Dim selcom As New OleDb.OleDbCommand
selcom.Connection = conect
selcom.CommandText = [Ô]SELECT NRPM from Servidores order by NRPM[Ô]
Dim vleitor As OleDb.OleDbDataReader
vleitor = selcom.ExecuteReader()
Dim c As Integer = 0
cmbNPM.Items.Clear()
Do While vleitor.Read
c = c + 1
ReDim Preserve anrpm(c)
anrpm(c) = vleitor([Ô]NRPM[Ô])
cmbNPM.Items.Add(anrpm(c))
Loop
cmbNPM.Text = [Ô]Escolha...[Ô]
conect.Close()
Catch ex As Exception
MessageBox.Show([Ô]Erro de carregamento do combobox![Ô] & vbCrLf & ex.Message, [Ô]ERRO[Ô], MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub btnProcessa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProcessa.Click
Dim cs As String = My.Settings.PontoConnectionString
Dim conect As New OleDb.OleDbConnection
Dim NPOLICIA As String
NPOLICIA = cmbNPM.Text
conect.ConnectionString = cs
Try
conect.Open()
Dim selcom As New OleDb.OleDbCommand
selcom.Connection = conect
If CDate(dtpi.Text) > CDate(dtpf.Text) Then
MessageBox.Show([Ô]A data inicial não pode ser maior que a final.[Ô], [Ô]ERRO[Ô], MessageBoxButtons.OK, MessageBoxIcon.Error)
dtpi.Focus()
Exit Sub
End If
[ô]-----------------------------------------------------------------------
selcom.CommandText = [Ô]SELECT NRPM from Servidores Where NRPM = [ô][Ô] & cmbNPM.Text & [Ô][ô][Ô]
Dim vexiste As String = selcom.ExecuteScalar
If Not vexiste = cmbNPM.Text Then
MessageBox.Show([Ô]ERRO. Servidor não está cadastrado no sistema.[Ô], [Ô]Alerta[Ô], MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return
End If
[ô]------------------------------------------------------------------------
selcom.CommandText = [Ô]SELECT NOME from Servidores Where NRPM = [ô][Ô] & cmbNPM.Text & [Ô][ô][Ô]
Dim vnome As String = selcom.ExecuteScalar
selcom.CommandText = [Ô]SELECT PGRAD from Servidores Where NRPM = [ô][Ô] & cmbNPM.Text & [Ô][ô][Ô]
Dim vgrad As String = selcom.ExecuteScalar
selcom.CommandText = [Ô]SELECT NRPM from Servidores Where NRPM = [ô][Ô] & cmbNPM.Text & [Ô][ô][Ô]
Dim vnrpm As String = selcom.ExecuteScalar
lblNRPM.Text = vnrpm
lblNome.Text = vnome
lblPgrad.Text = vgrad
vnomepega = lblNome.Text
[ô]------------------------------------------------------------------------
selcom.CommandText = [Ô]select * from Horas where DATA_ENTRADA>= #[Ô] + dtpi.Value.ToString([Ô]MM/dd/yyyy 00:00:00[Ô]) + [Ô]# AND DATA_SAIDA <= #[Ô] + dtpf.Value.ToString([Ô]MM/dd/yyyy 23:59:59[Ô]) + [Ô]# AND NRPM = [ô][Ô] & NPOLICIA & [Ô][ô] order by DATA_ENTRADA[Ô]
Dim vleitor As OleDb.OleDbDataReader
vleitor = selcom.ExecuteReader()
Dim c As Integer = 0
dgvTrabalho.Rows.Clear()
Do While vleitor.Read
c = c + 1
ReDim Preserve anrpm(c)
ReDim Preserve aidtra(c)
ReDim Preserve adte(c)
ReDim Preserve adts(c)
ReDim Preserve aservico(c)
ReDim Preserve adesconto(c)
ReDim Preserve atotalhoras(c)
anrpm(c) = vleitor([Ô]NRPM[Ô])
aidtra(c) = vleitor([Ô]ID[Ô])
adte(c) = vleitor([Ô]DATA_ENTRADA[Ô])
adts(c) = vleitor([Ô]DATA_SAIDA[Ô])
aservico(c) = vleitor([Ô]SERVICO[Ô])
adesconto(c) = vleitor([Ô]DESCONTO[Ô])
atotalhoras(c) = vleitor([Ô]TOTAL_HORAS[Ô])
dgvTrabalho.Rows.Add(anrpm(c), adte(c), adts(c), aservico(c), adesconto(c), atotalhoras(c))
Loop
calcula_hora()
dgvTrabalho.Rows.Add([Ô]Total de Horas:[Ô])
dgvTrabalho.Rows(c).Cells(5).Value = pegatotal
ToolStripStatusLabel1.Text = [Ô]Total de registros retornados na pesquisa: [Ô] & dgvTrabalho.RowCount - 1
conect.Close()
Catch ex As Exception
MessageBox.Show([Ô]Erro na execução da pesquisa![Ô] & vbCrLf & ex.Message, [Ô]ERRO[Ô], MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
[ô]Dim datetime1 As String = [Ô]2010/11/16 160:00:00[Ô]
[ô]Dim datetime2 As String = [Ô]2010/11/16 180:02:00[Ô]
[ô]Dim diferenca As TimeSpan = Convert.ToDateTime(datetime2) - Convert.ToDateTime(datetime1)
[ô]MessageBox.Show(diferenca.ToString())
End Sub
Private Sub btnSair_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSair.Click
Me.Close()
End Sub
Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
If (dgvTrabalho.RowCount = 0) Then
MessageBox.Show([Ô]Nenhum dado para exportar para o Excel[Ô], [Ô]Controle de Horas Trabalhadas[Ô], MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
ExportToExcel(Me.dgvTrabalho, xlsOption.xlsOpen)
End If
End Sub
Enum xlsOption
xlsSaveAs
xlsOpen
End Enum
[ô][ô][ô]
[ô][ô][ô] Exporta a informação de uma DataGridView para o Microsoft Excel
[ô][ô][ô]
[ô][ô][ô] Nome da DataGridView
[ô][ô][ô] SaveAs ou Open
[ô][ô][ô] Nome completo do ficheiro
Public Sub ExportToExcel(ByVal dgvName As DataGridView, ByVal [option] As xlsOption, Optional ByVal fileName As String = [Ô][Ô])
Dim objExcelApp As New Excel.Application()
Dim objExcelBook As Excel.Workbook
Dim objExcelSheet As Excel.Worksheet
Try
[ô] Se foi seleccionada a opção xlsSaveAs e não foi indicado ficheiro
If [option] = xlsOption.xlsSaveAs And fileName = String.Empty Then
MessageBox.Show([Ô]é necessário indicar um nome para o ficheiro[Ô])
Exit Sub
End If
[ô] Altera o tipo/localização para Inglês. Existe incompatibilidade
[ô] entre algumas versões de Excel vs Sistema Operativo
Dim oldCI As CultureInfo = CurrentThread.CurrentCulture
CurrentThread.CurrentCulture = New CultureInfo([Ô]en-US[Ô])
[ô] Adiciona um workbook e activa a worksheet actual
objExcelBook = objExcelApp.Workbooks.Add
objExcelSheet = CType(objExcelBook.Worksheets(1), Excel.Worksheet)
[ô] Ciclo nos cabeçalhos para escrever os tÃtulos a bold/negrito
Dim dgvColumnIndex As Int16 = 1
For Each col As DataGridViewColumn In dgvName.Columns
objExcelSheet.Cells(1, dgvColumnIndex) = col.HeaderText
objExcelSheet.Cells(1, dgvColumnIndex).Font.Bold = True
dgvColumnIndex += 1
Next
[ô] Ciclo nas linhas/células
Dim dgvRowIndex As Integer = 2
For Each row As DataGridViewRow In dgvName.Rows
Dim dgvCellIndex As Integer = 1
For Each cell As DataGridViewCell In row.Cells
objExcelSheet.Cells(dgvRowIndex, dgvCellIndex) = cell.Value
dgvCellIndex += 1
Next
dgvRowIndex += 1
Next
[ô] Ajusta o largura das colunas automaticamente
objExcelSheet.Columns.AutoFit()
[ô] Caso a opção seja gravar (xlsSaveAs) grava o ficheiro e fecha
[ô] o Workbook/Excel. Caso contrário (xlsOpen) abre o Excel
If [option] = xlsOption.xlsSaveAs Then
objExcelBook.SaveAs(fileName)
objExcelBook.Close()
Em um gridview preciso listar asssim:
101101-9 25/09/2013 08:30:00 25/09/2013 18:00:00 02:00:00 07:30:00
101101-9 26/09/2013 08:30:00 26/09/2013 18:00:00 02:00:00 07:30:00
TOTAL 15:00:00
102102-8 25/09/2013 08:30:00 25/09/2013 18:00:00 02:00:00 07:30:00
102102-8 26/09/2013 08:30:00 26/09/2013 18:00:00 02:00:00 07:30:00
TOTAL 15:00:00
E ASSIM POR DIANTE......
Desde já agradeço a atenção.
Henrique
ABAIXO ESTà O CÓDIGO PARA LISTAR UM ÚNICO FUNCIONÃRIO: E ESTà FUNCIONANDO BELEZA.
Imports Microsoft.Office.Interop
Imports System.Threading.Thread
Imports System.Globalization
Public Class Pesquisa
Dim aidtra(1) As Integer
Dim anrpm(1) As String
Dim adte(1) As DateTime
Dim adts(1) As DateTime
Dim aservico(1) As String
Dim adesconto(1) As String
Dim atotalhoras(1) As string
Dim pegatotal As String
Dim vnomepega As String
Private Sub Pesquisa_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dgvTrabalho.AlternatingRowsDefaultCellStyle.BackColor = Color.LightYellow
dgvTrabalho.Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(3).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(4).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvTrabalho.Columns(5).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
PopulaCombo()
[ô] Sortmode = nosortable
End Sub
Private Sub PopulaCombo()
Dim cs As String = My.Settings.PontoConnectionString
Dim conect As New OleDb.OleDbConnection
conect.ConnectionString = cs
Try
conect.Open()
Dim selcom As New OleDb.OleDbCommand
selcom.Connection = conect
selcom.CommandText = [Ô]SELECT NRPM from Servidores order by NRPM[Ô]
Dim vleitor As OleDb.OleDbDataReader
vleitor = selcom.ExecuteReader()
Dim c As Integer = 0
cmbNPM.Items.Clear()
Do While vleitor.Read
c = c + 1
ReDim Preserve anrpm(c)
anrpm(c) = vleitor([Ô]NRPM[Ô])
cmbNPM.Items.Add(anrpm(c))
Loop
cmbNPM.Text = [Ô]Escolha...[Ô]
conect.Close()
Catch ex As Exception
MessageBox.Show([Ô]Erro de carregamento do combobox![Ô] & vbCrLf & ex.Message, [Ô]ERRO[Ô], MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub btnProcessa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProcessa.Click
Dim cs As String = My.Settings.PontoConnectionString
Dim conect As New OleDb.OleDbConnection
Dim NPOLICIA As String
NPOLICIA = cmbNPM.Text
conect.ConnectionString = cs
Try
conect.Open()
Dim selcom As New OleDb.OleDbCommand
selcom.Connection = conect
If CDate(dtpi.Text) > CDate(dtpf.Text) Then
MessageBox.Show([Ô]A data inicial não pode ser maior que a final.[Ô], [Ô]ERRO[Ô], MessageBoxButtons.OK, MessageBoxIcon.Error)
dtpi.Focus()
Exit Sub
End If
[ô]-----------------------------------------------------------------------
selcom.CommandText = [Ô]SELECT NRPM from Servidores Where NRPM = [ô][Ô] & cmbNPM.Text & [Ô][ô][Ô]
Dim vexiste As String = selcom.ExecuteScalar
If Not vexiste = cmbNPM.Text Then
MessageBox.Show([Ô]ERRO. Servidor não está cadastrado no sistema.[Ô], [Ô]Alerta[Ô], MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return
End If
[ô]------------------------------------------------------------------------
selcom.CommandText = [Ô]SELECT NOME from Servidores Where NRPM = [ô][Ô] & cmbNPM.Text & [Ô][ô][Ô]
Dim vnome As String = selcom.ExecuteScalar
selcom.CommandText = [Ô]SELECT PGRAD from Servidores Where NRPM = [ô][Ô] & cmbNPM.Text & [Ô][ô][Ô]
Dim vgrad As String = selcom.ExecuteScalar
selcom.CommandText = [Ô]SELECT NRPM from Servidores Where NRPM = [ô][Ô] & cmbNPM.Text & [Ô][ô][Ô]
Dim vnrpm As String = selcom.ExecuteScalar
lblNRPM.Text = vnrpm
lblNome.Text = vnome
lblPgrad.Text = vgrad
vnomepega = lblNome.Text
[ô]------------------------------------------------------------------------
selcom.CommandText = [Ô]select * from Horas where DATA_ENTRADA>= #[Ô] + dtpi.Value.ToString([Ô]MM/dd/yyyy 00:00:00[Ô]) + [Ô]# AND DATA_SAIDA <= #[Ô] + dtpf.Value.ToString([Ô]MM/dd/yyyy 23:59:59[Ô]) + [Ô]# AND NRPM = [ô][Ô] & NPOLICIA & [Ô][ô] order by DATA_ENTRADA[Ô]
Dim vleitor As OleDb.OleDbDataReader
vleitor = selcom.ExecuteReader()
Dim c As Integer = 0
dgvTrabalho.Rows.Clear()
Do While vleitor.Read
c = c + 1
ReDim Preserve anrpm(c)
ReDim Preserve aidtra(c)
ReDim Preserve adte(c)
ReDim Preserve adts(c)
ReDim Preserve aservico(c)
ReDim Preserve adesconto(c)
ReDim Preserve atotalhoras(c)
anrpm(c) = vleitor([Ô]NRPM[Ô])
aidtra(c) = vleitor([Ô]ID[Ô])
adte(c) = vleitor([Ô]DATA_ENTRADA[Ô])
adts(c) = vleitor([Ô]DATA_SAIDA[Ô])
aservico(c) = vleitor([Ô]SERVICO[Ô])
adesconto(c) = vleitor([Ô]DESCONTO[Ô])
atotalhoras(c) = vleitor([Ô]TOTAL_HORAS[Ô])
dgvTrabalho.Rows.Add(anrpm(c), adte(c), adts(c), aservico(c), adesconto(c), atotalhoras(c))
Loop
calcula_hora()
dgvTrabalho.Rows.Add([Ô]Total de Horas:[Ô])
dgvTrabalho.Rows(c).Cells(5).Value = pegatotal
ToolStripStatusLabel1.Text = [Ô]Total de registros retornados na pesquisa: [Ô] & dgvTrabalho.RowCount - 1
conect.Close()
Catch ex As Exception
MessageBox.Show([Ô]Erro na execução da pesquisa![Ô] & vbCrLf & ex.Message, [Ô]ERRO[Ô], MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
[ô]Dim datetime1 As String = [Ô]2010/11/16 160:00:00[Ô]
[ô]Dim datetime2 As String = [Ô]2010/11/16 180:02:00[Ô]
[ô]Dim diferenca As TimeSpan = Convert.ToDateTime(datetime2) - Convert.ToDateTime(datetime1)
[ô]MessageBox.Show(diferenca.ToString())
End Sub
Private Sub btnSair_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSair.Click
Me.Close()
End Sub
Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
If (dgvTrabalho.RowCount = 0) Then
MessageBox.Show([Ô]Nenhum dado para exportar para o Excel[Ô], [Ô]Controle de Horas Trabalhadas[Ô], MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
ExportToExcel(Me.dgvTrabalho, xlsOption.xlsOpen)
End If
End Sub
Enum xlsOption
xlsSaveAs
xlsOpen
End Enum
[ô][ô][ô]
[ô][ô][ô] Exporta a informação de uma DataGridView para o Microsoft Excel
[ô][ô][ô]
[ô][ô][ô] Nome da DataGridView
[ô][ô][ô] SaveAs ou Open
[ô][ô][ô] Nome completo do ficheiro
Public Sub ExportToExcel(ByVal dgvName As DataGridView, ByVal [option] As xlsOption, Optional ByVal fileName As String = [Ô][Ô])
Dim objExcelApp As New Excel.Application()
Dim objExcelBook As Excel.Workbook
Dim objExcelSheet As Excel.Worksheet
Try
[ô] Se foi seleccionada a opção xlsSaveAs e não foi indicado ficheiro
If [option] = xlsOption.xlsSaveAs And fileName = String.Empty Then
MessageBox.Show([Ô]é necessário indicar um nome para o ficheiro[Ô])
Exit Sub
End If
[ô] Altera o tipo/localização para Inglês. Existe incompatibilidade
[ô] entre algumas versões de Excel vs Sistema Operativo
Dim oldCI As CultureInfo = CurrentThread.CurrentCulture
CurrentThread.CurrentCulture = New CultureInfo([Ô]en-US[Ô])
[ô] Adiciona um workbook e activa a worksheet actual
objExcelBook = objExcelApp.Workbooks.Add
objExcelSheet = CType(objExcelBook.Worksheets(1), Excel.Worksheet)
[ô] Ciclo nos cabeçalhos para escrever os tÃtulos a bold/negrito
Dim dgvColumnIndex As Int16 = 1
For Each col As DataGridViewColumn In dgvName.Columns
objExcelSheet.Cells(1, dgvColumnIndex) = col.HeaderText
objExcelSheet.Cells(1, dgvColumnIndex).Font.Bold = True
dgvColumnIndex += 1
Next
[ô] Ciclo nas linhas/células
Dim dgvRowIndex As Integer = 2
For Each row As DataGridViewRow In dgvName.Rows
Dim dgvCellIndex As Integer = 1
For Each cell As DataGridViewCell In row.Cells
objExcelSheet.Cells(dgvRowIndex, dgvCellIndex) = cell.Value
dgvCellIndex += 1
Next
dgvRowIndex += 1
Next
[ô] Ajusta o largura das colunas automaticamente
objExcelSheet.Columns.AutoFit()
[ô] Caso a opção seja gravar (xlsSaveAs) grava o ficheiro e fecha
[ô] o Workbook/Excel. Caso contrário (xlsOpen) abre o Excel
If [option] = xlsOption.xlsSaveAs Then
objExcelBook.SaveAs(fileName)
objExcelBook.Close()
Faça seu login para responder