frmwgxg.vb

一个实际的商业项目

        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.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sl", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Sl1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sl1", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Sl11", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sl1", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Sl2", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sl2", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Sl21", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sl2", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Sl3", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sl3", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Sl31", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sl3", System.Data.DataRowVersion.Original, Nothing))
        '
        'frmWgxg
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(680, 446)
        Me.Controls.Add(Me.Label6)
        Me.Controls.Add(Me.btnExcel)
        Me.Controls.Add(Me.btnUpdata)
        Me.Controls.Add(Me.btnList)
        Me.Controls.Add(Me.Label5)
        Me.Controls.Add(Me.txtGx)
        Me.Controls.Add(Me.txtPf)
        Me.Controls.Add(Me.Label4)
        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.Controls.Add(Me.DataGrid1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "frmWgxg"
        Me.ShowInTaskbar = False
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterParent
        Me.Text = "计件完工单查询修改"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DsWgxg1, 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) = "完工数"
                        Case 6
                            myExcel.Cells(1, j + 1) = "不合格(料)"
                        Case 7
                            myExcel.Cells(1, j + 1) = "不合格(责)"
                        Case 8
                            myExcel.Cells(1, j + 1) = "不合格(回用)"
                        Case 9
                            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 frmWgxg_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
        DsWgxg1.Clear()

        Dim strCmd As String
        strCmd = "SELECT Rq, Gh, Xm, Pf, Gx, Sl, Sl1, Sl2, Sl3, Bz FROM Wg"
        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 txtPf.Text <> "" Then
            strCmd &= " AND Pf = '" & txtPf.Text & "'"
        End If
        If txtGx.Text <> "" Then
            strCmd &= " AND Gx = '" & txtGx.Text & "'"
        End If




        Me.OleDbSelectCommand1.CommandText = strCmd
        daWgxg.Fill(DsWgxg1)
    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
            daWgxg.Update(DsWgxg1)
        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 frmWgxg_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