querydepartmentform.vb

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

'文件名：QueryDepartmentForm.vb
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop.Excel
Imports System.Reflection
Imports Microsoft.Office.Core
Public Class QueryDepartmentForm
    Private MyQueryTable As System.Data.DataTable
    Public MyCompany As String
    Private Sub QueryDepartmentForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '获取公司的部门信息            
        Dim MySQLConnectionString As String = My.Settings.MyPersonnelConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        Dim MyDepartmentTable As New System.Data.DataTable()
        Dim MySQL As String = "Select 部门名称 From 公司部门"
        Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
        MyAdapter.Fill(MyDepartmentTable)
        For Each MyRow As DataRow In MyDepartmentTable.Rows
            Me.部门ToolStripComboBox.Items.Add(MyRow(0).ToString())
        Next
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
    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 员工编号, 姓名, 性别, 出生年月, 身份证号码, 民族, 政治面貌, 婚姻状况, 文化程度, 专业, 毕业院校, 职称, 职务, 进入本单位时间, 合同服务年限, 基本工资, 银行帐号, 养老保险帐号, 医疗保险帐号, 住房基金帐号, 联系电话, 手机号码, 电子信箱, 家庭住址, 邮政编码, 籍贯  From 基本档案 WHERE (部门='" + Me.部门ToolStripComboBox.Text + " ' AND 员工编号 NOT IN (SELECT 员工编号 FROM 离职管理))"
        Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
        MyAdapter.Fill(MyQueryTable)
        Me.基本档案DataGridView.DataSource = MyQueryTable
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
    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 MyColumns As Char
        Dim MyRange As Range
        Dim MyData(5000, 30) As Object
        Dim Count, 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)
        Dim MyChar As Integer = MyQueryTable.Columns.Count + 64
        MyColumns = Convert.ToChar(MyChar)
        MyRange = MyWorkSheet.Range("A5", MyColumns.ToString() + "5")
        Count = 0
        For Each MyNewColumn As DataColumn In MyQueryTable.Columns
            MyData(0, Count) = MyNewColumn.ColumnName
            Count = Count + 1
        Next
        j = 1
        '输出数据库记录
        For Each MyRow As DataRow In MyQueryTable.Rows
            For i = 0 To MyQueryTable.Columns.Count - 1 Step 1
                MyData(j, i) = MyRow(i).ToString()
                If (i = 3 Or i = 13) Then
                    MyData(j, i) = (MyRow(i)).ToShortDateString()
                End If
            Next
            j = j + 1
        Next
        MyRange = MyRange.Resize(MyQueryTable.Rows.Count + 1, MyQueryTable.Columns.Count)
        MyRange.Value2 = MyData
        MyRange.EntireColumn.AutoFit()
        MyWorkSheet.Cells(1, 2) = Me.MyCompany + Me.部门ToolStripComboBox.Text + "员工人事档案"
        MyWorkSheet.Cells(4, 1) = "制表日期：" + DateTime.Now.ToShortDateString()
    End Sub
End Class