querypayform.vb

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

'文件名：QueryPayForm.vb
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop.Excel
Imports System.Reflection
Imports Microsoft.Office.Core
Public Class QueryPayForm
    Public MyCompany As String
    Private Sub QueryPayForm_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
        For i As Integer = 2006 To 2100 Step 1
            Me.年份ToolStripComboBox.Items.Add(i.ToString())
        Next
        For i As Integer = 1 To 12 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
        Try
            Me.工资发放表TableAdapter.FillBy(Me.MyPersonnelDataSet.工资发放表, New System.Nullable(Of Integer)(CType(月份ToolStripComboBox.Text, Integer)), 部门ToolStripComboBox.Text, New System.Nullable(Of Integer)(CType(年份ToolStripComboBox.Text, Integer)))
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub 打印全部信息ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 打印全部信息ToolStripButton.Click
        '导出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(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)
        Dim MyChar As Integer = Me.MyPersonnelDataSet.工资发放表.Columns.Count + 64
        MyColumns = Convert.ToChar(MyChar)
        MyRange = MyWorkSheet.Range("A5", MyColumns.ToString() + "5")
        Dim Count As Integer = 0
        For Each MyNewColumn As DataColumn In Me.MyPersonnelDataSet.工资发放表.Columns
            MyData(0, Count) = MyNewColumn.ColumnName
            Count = Count + 1
        Next
        j = 1
        '输出数据库记录
        For Each MyRow As DataRow In Me.MyPersonnelDataSet.工资发放表.Rows
            For i = 0 To Me.MyPersonnelDataSet.工资发放表.Columns.Count - 1 Step 1
                MyData(j, i) = MyRow(i).ToString()
            Next
            j = j + 1
        Next
        MyRange = MyRange.Resize(Me.MyPersonnelDataSet.工资发放表.Rows.Count + 1, Me.MyPersonnelDataSet.工资发放表.Columns.Count)
        MyRange.Value2 = MyData
        MyRange.EntireColumn.AutoFit()
        MyWorkSheet.Cells(2, 2) = Me.MyCompany + "工资发放表"
        MyWorkSheet.Cells(4, 1) = "日期：" + Me.年份ToolStripComboBox.Text + "年" + Me.月份ToolStripComboBox.Text + "月"
    End Sub
    Private Sub 打印银行入帐单ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 打印银行入帐单ToolStripButton.Click
        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 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", "C5")
        MyData(0, 0) = "姓名"
        MyData(0, 1) = "银行帐号"
        MyData(0, 2) = "金额"
        j = 1
        '输出数据库记录
        For Each MyRow As DataRow In Me.MyPersonnelDataSet.工资发放表.Rows
            MyData(j, 0) = MyRow(2).ToString()
            MyData(j, 1) = MyRow(17).ToString()
            MyData(j, 2) = MyRow(16).ToString()
            j = j + 1
        Next
        MyRange = MyRange.Resize(Me.MyPersonnelDataSet.工资发放表.Rows.Count + 1, Me.MyPersonnelDataSet.工资发放表.Columns.Count)
        MyRange.Value2 = MyData
        MyRange.EntireColumn.AutoFit()
        MyWorkSheet.Cells(2, 2) = Me.MyCompany + "员工工资银行入帐单"
        MyWorkSheet.Cells(4, 1) = "日期：" + Me.年份ToolStripComboBox.Text + "年" + Me.月份ToolStripComboBox.Text + "月"
    End Sub
    Private Sub 打印养老险入帐单ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 打印养老险入帐单ToolStripButton.Click
        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 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", "C5")
        MyData(0, 0) = "姓名"
        MyData(0, 1) = "养老保险帐号"
        MyData(0, 2) = "金额"
        j = 1
        '输出数据库记录
        For Each MyRow As DataRow In Me.MyPersonnelDataSet.工资发放表.Rows
            MyData(j, 0) = MyRow(2).ToString()
            MyData(j, 1) = MyRow(18).ToString()
            MyData(j, 2) = MyRow(11).ToString()
            j = j + 1
        Next
        MyRange = MyRange.Resize(Me.MyPersonnelDataSet.工资发放表.Rows.Count + 1, Me.MyPersonnelDataSet.工资发放表.Columns.Count)
        MyRange.Value2 = MyData
        MyRange.EntireColumn.AutoFit()
        MyWorkSheet.Cells(2, 2) = Me.MyCompany + "员工养老保险入帐单"
        MyWorkSheet.Cells(4, 1) = "日期：" + Me.年份ToolStripComboBox.Text + "年" + Me.月份ToolStripComboBox.Text + "月"
    End Sub
    Private Sub 打印医疗险入帐单ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 打印医疗险入帐单ToolStripButton.Click
        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 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", "C5")
        MyData(0, 0) = "姓名"
        MyData(0, 1) = "医疗保险帐号"
        MyData(0, 2) = "金额"
        j = 1
        '输出数据库记录
        For Each MyRow As DataRow In Me.MyPersonnelDataSet.工资发放表.Rows
            MyData(j, 0) = MyRow(2).ToString()
            MyData(j, 1) = MyRow(19).ToString()
            MyData(j, 2) = MyRow(12).ToString()
            j = j + 1
        Next
        MyRange = MyRange.Resize(Me.MyPersonnelDataSet.工资发放表.Rows.Count + 1, Me.MyPersonnelDataSet.工资发放表.Columns.Count)
        MyRange.Value2 = MyData
        MyRange.EntireColumn.AutoFit()
        MyWorkSheet.Cells(2, 2) = Me.MyCompany + "员工医疗保险入帐单"
        MyWorkSheet.Cells(4, 1) = "日期：" + Me.年份ToolStripComboBox.Text + "年" + Me.月份ToolStripComboBox.Text + "月"
    End Sub
    Private Sub 打印住房基金入帐单ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 打印住房基金入帐单ToolStripButton.Click
        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 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", "C5")
        MyData(0, 0) = "姓名"
        MyData(0, 1) = "住房基金帐号"
        MyData(0, 2) = "金额"
        j = 1
        '输出数据库记录
        For Each MyRow As DataRow In Me.MyPersonnelDataSet.工资发放表.Rows
            MyData(j, 0) = MyRow(2).ToString()
            MyData(j, 1) = MyRow(20).ToString()
            MyData(j, 2) = MyRow(13).ToString()
            j = j + 1
        Next
        MyRange = MyRange.Resize(Me.MyPersonnelDataSet.工资发放表.Rows.Count + 1, Me.MyPersonnelDataSet.工资发放表.Columns.Count)
        MyRange.Value2 = MyData
        MyRange.EntireColumn.AutoFit()
        MyWorkSheet.Cells(2, 2) = Me.MyCompany + "员工住房基金入帐单"
        MyWorkSheet.Cells(4, 1) = "日期：" + Me.年份ToolStripComboBox.Text + "年" + Me.月份ToolStripComboBox.Text + "月"
    End Sub
End Class