frmjswgxg.vb

一个实际的商业项目

        '
        'btnExcel
        '
        Me.btnExcel.Location = New System.Drawing.Point(24, 384)
        Me.btnExcel.Name = "btnExcel"
        Me.btnExcel.Size = New System.Drawing.Size(152, 23)
        Me.btnExcel.TabIndex = 25
        Me.btnExcel.Text = "导出数据到Excel"
        '
        'btnUpdata
        '
        Me.btnUpdata.ForeColor = System.Drawing.Color.Red
        Me.btnUpdata.Location = New System.Drawing.Point(504, 384)
        Me.btnUpdata.Name = "btnUpdata"
        Me.btnUpdata.TabIndex = 24
        Me.btnUpdata.Text = "更新数据"
        '
        'daJsWgxg
        '
        Me.daJsWgxg.DeleteCommand = Me.OleDbDeleteCommand1
        Me.daJsWgxg.InsertCommand = Me.OleDbInsertCommand1
        Me.daJsWgxg.SelectCommand = Me.OleDbSelectCommand1
        Me.daJsWgxg.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "JsWg", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Rq", "Rq"), New System.Data.Common.DataColumnMapping("Gh", "Gh"), New System.Data.Common.DataColumnMapping("Xm", "Xm"), New System.Data.Common.DataColumnMapping("Gx", "Gx"), New System.Data.Common.DataColumnMapping("Sl", "Sl"), New System.Data.Common.DataColumnMapping("Bz", "Bz")})})
        Me.daJsWgxg.UpdateCommand = Me.OleDbUpdateCommand1
        '
        'OleDbDeleteCommand1
        '
        Me.OleDbDeleteCommand1.CommandText = "DELETE FROM JsWg WHERE (Gh = ?) AND (Gx = ?) AND (Rq = ?) AND (Xm = ?) AND (Bz = " & _
        "? OR ? IS NULL AND Bz IS NULL) AND (Sl = ?)"
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Gh", System.Data.OleDb.OleDbType.VarWChar, 16, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Gh", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Gx", System.Data.OleDb.OleDbType.VarWChar, 20, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Gx", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Rq", System.Data.OleDb.OleDbType.VarWChar, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Rq", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Xm", System.Data.OleDb.OleDbType.VarWChar, 16, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Xm", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Bz", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Bz", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Bz1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Bz", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Sl", System.Data.OleDb.OleDbType.Double, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sl", System.Data.DataRowVersion.Original, Nothing))
        '
        'OleDbInsertCommand1
        '
        Me.OleDbInsertCommand1.CommandText = "INSERT INTO JsWg(Rq, Gh, Xm, Gx, Sl, Bz) VALUES (?, ?, ?, ?, ?, ?)"
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Rq", System.Data.OleDb.OleDbType.VarWChar, 8, "Rq"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Gh", System.Data.OleDb.OleDbType.VarWChar, 16, "Gh"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Xm", System.Data.OleDb.OleDbType.VarWChar, 16, "Xm"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Gx", System.Data.OleDb.OleDbType.VarWChar, 20, "Gx"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Sl", System.Data.OleDb.OleDbType.Double, 0, "Sl"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Bz", System.Data.OleDb.OleDbType.VarWChar, 50, "Bz"))
        '
        'OleDbSelectCommand1
        '
        Me.OleDbSelectCommand1.CommandText = "SELECT Rq, Gh, Xm, Gx, Sl, Bz FROM JsWg"
        '
        'OleDbUpdateCommand1
        '
        Me.OleDbUpdateCommand1.CommandText = "UPDATE JsWg SET Rq = ?, Gh = ?, Xm = ?, Gx = ?, Sl = ?, Bz = ? WHERE (Gh = ?) AND" & _
        " (Gx = ?) AND (Rq = ?) AND (Xm = ?) AND (Bz = ? OR ? IS NULL AND Bz IS NULL) AND" & _
        " (Sl = ?)"
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Rq", System.Data.OleDb.OleDbType.VarWChar, 8, "Rq"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Gh", System.Data.OleDb.OleDbType.VarWChar, 16, "Gh"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Xm", System.Data.OleDb.OleDbType.VarWChar, 16, "Xm"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Gx", System.Data.OleDb.OleDbType.VarWChar, 20, "Gx"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Sl", System.Data.OleDb.OleDbType.Double, 0, "Sl"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Bz", System.Data.OleDb.OleDbType.VarWChar, 50, "Bz"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Gh", System.Data.OleDb.OleDbType.VarWChar, 16, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Gh", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Gx", System.Data.OleDb.OleDbType.VarWChar, 20, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Gx", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Rq", System.Data.OleDb.OleDbType.VarWChar, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Rq", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Xm", System.Data.OleDb.OleDbType.VarWChar, 16, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Xm", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Bz", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Bz", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Bz1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Bz", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Sl", System.Data.OleDb.OleDbType.Double, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sl", System.Data.DataRowVersion.Original, Nothing))
        '
        'frmJsWgxg
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(608, 430)
        Me.Controls.Add(Me.Label6)
        Me.Controls.Add(Me.btnExcel)
        Me.Controls.Add(Me.btnUpdata)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.btnList)
        Me.Controls.Add(Me.Label5)
        Me.Controls.Add(Me.txtGx)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.txtXm)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.dp2)
        Me.Controls.Add(Me.dp1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "frmJsWgxg"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterParent
        Me.Text = "计时完工单查询修改"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DsJsWgxg1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region


    Public Sub Exports2Excel2(ByVal Dtg As DataGrid)
        If Dtg.VisibleRowCount > 0 Then
            Try
                Me.Cursor = Cursors.WaitCursor
                Dim datav As New DataView
                If TypeOf Dtg.DataSource Is DataView Then
                    datav = CType(Dtg.DataSource, DataView)
                ElseIf TypeOf Dtg.DataSource Is DataSet Then
                    datav = CType(Dtg.DataSource, DataSet).Tables(0).DefaultView
                ElseIf TypeOf Dtg.DataSource Is DataTable Then
                    datav = CType(Dtg.DataSource, DataTable).DefaultView
                End If
                Dim i, j As Integer
                Dim rows As Integer = datav.Table.Rows.Count
                Dim cols As Integer = datav.Table.Columns.Count
                Dim DataArray(rows - 1, cols - 1) As Object
                Dim myExcel As Excel.Application = New Excel.Application
                For i = 0 To rows - 1
                    For j = 0 To cols - 1

                        If datav.Table.Rows(i).IsNull(j) Then
                            DataArray(i, j) = ""
                        Else
                            DataArray(i, j) = datav.Table.Rows(i).Item(j)
                        End If




                    Next
                Next
                myExcel.Application.Workbooks.Add(True)
                myExcel.Visible = True
                For j = 0 To cols - 1

                    Select Case j
                        Case 0
                            myExcel.Cells(1, j + 1) = "完工日期"
                        Case 1
                            myExcel.Cells(1, j + 1) = "工号"
                        Case 2
                            myExcel.Cells(1, j + 1) = "姓名"
                        Case 3
                            myExcel.Cells(1, j + 1) = "工序"
                        Case 4
                            myExcel.Cells(1, j + 1) = "完工数"
                        Case 5
                            myExcel.Cells(1, j + 1) = "备注"

                    End Select


                    'myExcel.Cells(1, j + 1) = datav.Table.Columns(j).ColumnName

                Next
                myExcel.Range("A2").Resize(rows, cols).Value = DataArray

            Catch exp As Exception
                MessageBox.Show("数据导出失败!请查看是否已经安装了Excel", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)

                'MessageBox.Show(exp.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Finally
                Me.Cursor = Cursors.Default
            End Try
        Else
            MessageBox.Show("没有数据!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub


    Private Function convertDate(ByVal dp As Date) As String
        Dim strdate As String

        strdate = dp.Year.ToString
        If dp.Month.ToString.Length = 1 Then
            strdate = strdate & "0" & dp.Month.ToString
        Else
            strdate = strdate & dp.Month.ToString
        End If
        If dp.Day.ToString.Length = 1 Then
            strdate = strdate & "0" & dp.Day.ToString
        Else
            strdate = strdate & dp.Day.ToString
        End If
        Return strdate
    End Function

    Private Sub frmJsWgxg_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.OleDbSelectCommand1.Connection = cnData
        Me.OleDbInsertCommand1.Connection = cnData
        Me.OleDbUpdateCommand1.Connection = cnData
        Me.OleDbDeleteCommand1.Connection = cnData

        cnData.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:database password=power;;Data Source= " & Application.StartupPath & "\gongzi.mdb;Persist Security Info=False"
        'daWgxg.Fill(DsWgxg1)
        dp1.Value = Now.AddMonths(-1)
    End Sub

    Private Sub btnList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnList.Click
        DsJsWgxg1.Clear()

        Dim strCmd As String
        strCmd = "SELECT Rq, Gh, Xm, Gx, Sl, Bz FROM JsWg"
        strDate1 = convertDate(dp1.Value)
        strDate2 = convertDate(dp2.Value)

        strCmd &= " WHERE (Rq BETWEEN '" & strDate1 & "' AND '" & strDate2 & "')"
        If txtXm.Text <> "" Then
            strCmd &= " AND Xm = '" & txtXm.Text & "'"
        End If
        If txtGx.Text <> "" Then
            strCmd &= " AND Gx = '" & txtGx.Text & "'"
        End If




        Me.OleDbSelectCommand1.CommandText = strCmd
        daJsWgxg.Fill(DsJsWgxg1)
    End Sub

    Private Sub btnUpdata_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdata.Click

        If DialogResult.Yes = MessageBox.Show("确实要更新数据吗？", "系统信息", MessageBoxButtons.YesNo, MessageBoxIcon.Question) Then
            daJsWgxg.Update(DsJsWgxg1)
        End If

    End Sub

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        Exports2Excel2(DataGrid1)
    End Sub

    Private Sub frmJsWgxg_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        Dim pProcess As System.Diagnostics.Process

        For Each pProcess In System.Diagnostics.Process.GetProcesses()
            If pProcess.ProcessName.ToString() = "Excel" Then pProcess.Kill()
        Next
    End Sub
End Class