querycheckform.vb

这是一个人事工资管理系统

'文件名：QueryCheckForm.vb
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop.Excel
Imports System.Reflection
Imports Microsoft.Office.Core
Public Class QueryCheckForm
    Public MyCompany As String
    Private My员工姓名 As String
    Private MyQueryTable As System.Data.DataTable
    Private Sub 考勤记录BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.考勤记录BindingSource.EndEdit()
        Me.考勤记录TableAdapter.Update(Me.MyPersonnelDataSet.考勤记录)
    End Sub
    Private Sub QueryCheckForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.考勤记录TableAdapter.Fill(Me.MyPersonnelDataSet.考勤记录)
        Me.MyPersonnelDataSet.考勤记录.Rows.Clear()
        For i As Integer = 2006 To 2100 Step 1
            Me.考勤年份ToolStripComboBox.Items.Add(i.ToString())
        Next
    End Sub
    Private Sub 查询个人年度考勤ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 查询个人年度考勤ToolStripButton.Click
        Dim MySQLConnectionString As String = My.Settings.MyPersonnelConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        MyQueryTable = New System.Data.DataTable()
        Dim MySQL As String = "SELECT 员工姓名,考勤月份, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30],  [31] FROM 考勤记录 WHERE (员工编号 LIKE '" + Me.员工编号ToolStripTextBox.Text + "') AND (考勤年份=" + Me.考勤年份ToolStripComboBox.Text + ") ORDER BY 考勤月份"
        Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
        MyAdapter.Fill(MyQueryTable)
        Me.My员工姓名 = MyQueryTable.Rows(1)(0).ToString()
        Me.Text = "查询个人年度考勤-" + Me.My员工姓名
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
        Me.考勤记录DataGridView.DataSource = MyQueryTable
    End Sub
    Private Sub 打印个人年度考勤ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 打印个人年度考勤ToolStripButton.Click
        If (MyQueryTable.Rows.Count < 1) Then
            Return
        End If
        '导出Excel表格数据文件
        Dim MyExcel As ApplicationClass
        Dim MyWorkBooks As Workbooks
        Dim MyWorkBook As Workbook
        Dim MyWorkSheet As Worksheet
        Dim MyRange As Range
        Dim MyData(500, 35) As Object
        Dim i, j As Integer
        MyExcel = New ApplicationClass()
        MyExcel.Visible = True
        If (MyExcel Is Nothing) Then
            MessageBox.Show("Excel程序无法启动！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return
        End If
        MyWorkBooks = MyExcel.Workbooks
        MyWorkBook = MyWorkBooks.Add(Missing.Value)
        MyWorkSheet = MyWorkBook.Worksheets(1)
        MyRange = MyWorkSheet.Range("A5", "AF5")
        MyData(0, 0) = "月份"
        For i = 1 To 31 Step 1
            MyData(0, i) = i.ToString()
        Next
        j = 1
        '输出数据库记录
        For Each MyRow As DataRow In MyQueryTable.Rows
            For i = 1 To MyQueryTable.Columns.Count - 2 Step 1
                MyData(j, i - 1) = MyRow(i).ToString()
            Next
            j = j + 1
        Next
        MyRange = MyRange.Resize(MyQueryTable.Rows.Count + 1, 32)
        MyRange.Value2 = MyData
        MyRange.EntireColumn.AutoFit()
        MyWorkSheet.Cells(2, 2) = Me.MyCompany + "个人年度考勤表"
        MyWorkSheet.Cells(4, 1) = "姓名：" + Me.My员工姓名 + "      考勤年度：" + Me.考勤年份ToolStripComboBox.Text + "年"
        MyWorkSheet.Cells(MyQueryTable.Rows.Count + 7, 1) = "考勤符号说明：出勤 /， 迟到>， 早退<， 产假√，事假#，病假+，婚假△，旷工×"
    End Sub
End Class