computeform.vb

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

'文件名：ComputeForm.vb
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop.Excel
Imports System.Reflection
Imports Microsoft.Office.Core
Public Class ComputeForm
    Public MyCompany As String
    Private Sub 工资核算表BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 工资核算表BindingNavigatorSaveItem.Click
        Me.Validate()
        Me.工资核算表BindingSource.EndEdit()
        Me.工资核算表TableAdapter.Update(Me.MyPersonnelDataSet.工资核算表)
    End Sub
    Private Sub ComputeForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Me.工资核算表TableAdapter.Fill(Me.MyPersonnelDataSet.工资核算表)
        Dim MySQLConnectionString As String = My.Settings.MyPersonnelConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        Dim MyQueryTable As New System.Data.DataTable()
        Dim MySQL As String = "Select * From 在职员工视图 WHERE 员工编号 NOT IN (SELECT 员工编号 FROM 工资核算表)"
        Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
        MyAdapter.Fill(MyQueryTable)
        For Each MyRow As DataRow In MyQueryTable.Rows
            Dim My自编号 As String = Me.GetMyID()
            Dim My员工编号 As String = MyRow(1).ToString()
            Dim My员工姓名 As String = MyRow(3).ToString()
            Dim My基本工资 As Double
            Try
                My基本工资 = Convert.ToDouble(MyRow(17).ToString())
            Catch ex As Exception
            End Try
            MySQL = "INSERT INTO 工资核算表([自编号] ,[员工编号], [员工姓名], [基本工资]) VALUES (@自编号 ,@员工编号, @员工姓名, @基本工资)"
            MyConnection = New SqlConnection(MySQLConnectionString)
            MyConnection.Open()
            Dim MyCommand As SqlCommand = MyConnection.CreateCommand()
            MyCommand.CommandText = MySQL
            MyCommand.Parameters.Add(New SqlParameter("@自编号", SqlDbType.VarChar))
            MyCommand.Parameters.Add(New SqlParameter("@员工编号", SqlDbType.VarChar))
            MyCommand.Parameters.Add(New SqlParameter("@员工姓名", SqlDbType.VarChar))
            MyCommand.Parameters.Add(New SqlParameter("@基本工资", SqlDbType.Float))
            MyCommand.Parameters("@自编号").Value = My自编号
            MyCommand.Parameters("@员工编号").Value = My员工编号
            MyCommand.Parameters("@员工姓名").Value = My员工姓名
            MyCommand.Parameters("@基本工资").Value = My基本工资
            MyCommand.ExecuteNonQuery()
        Next
        '获取公司的部门信息            
        Dim MyDepartmentTable As New System.Data.DataTable()
        MySQL = "Select 部门名称 From 公司部门"
        MyAdapter = 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
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
    End Sub
    Private Function GetMyID() As String
        Dim MySQLConnectionString As String = My.Settings.MyPersonnelConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        Dim MyCommand As SqlCommand = MyConnection.CreateCommand()
        MyCommand.CommandText = "Select max(自编号) 最大编号 From 工资核算表"
        Dim MyResult As Object = MyCommand.ExecuteScalar()
        Dim MyID As System.Int64 = 1
        If (Not (MyResult Is System.DBNull.Value)) Then
            Dim MyMaxID As String = MyResult.ToString().Trim()
            MyMaxID = MyMaxID.Substring(2, MyMaxID.Length - 2)
            MyID = Convert.ToInt64(MyMaxID) + 1
        End If
        Dim MyLength As Integer = MyID.ToString().Length
        Dim MyNewID As String = ""
        Select Case (MyLength)
            Case 1
                    MyNewID = "HS0000000" + MyID.ToString()
            Case 2
                    MyNewID = "HS000000" + MyID.ToString()
            Case 3
                    MyNewID = "HS00000" + MyID.ToString()
            Case 4
                    MyNewID = "HS0000" + MyID.ToString()
            Case 5
                    MyNewID = "HS000" + MyID.ToString()
            Case 6
                    MyNewID = "HS00" + MyID.ToString()
            Case 7
                MyNewID = "HS0" + MyID.ToString()
        End Select
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
        Return MyNewID
    End Function
    Private Sub 查询ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 查询ToolStripButton.Click
        Try
            Me.工资核算表TableAdapter.FillBy(Me.MyPersonnelDataSet.工资核算表, 部门ToolStripComboBox.Text)
        Catch ex As 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
        '输出数据库记录