frmimportdata.vb

代码：是由本人自主开发完成的

        Me.DataGrid1.Location = New System.Drawing.Point(8, 104)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.ParentRowsBackColor = System.Drawing.Color.Lavender
        Me.DataGrid1.ParentRowsForeColor = System.Drawing.Color.MidnightBlue
        Me.DataGrid1.ReadOnly = True
        Me.DataGrid1.SelectionBackColor = System.Drawing.Color.Teal
        Me.DataGrid1.SelectionForeColor = System.Drawing.Color.PaleGreen
        Me.DataGrid1.Size = New System.Drawing.Size(472, 320)
        Me.DataGrid1.TabIndex = 0
        '
        'CheckBox1
        '
        Me.CheckBox1.Location = New System.Drawing.Point(400, 53)
        Me.CheckBox1.Name = "CheckBox1"
        Me.CheckBox1.Size = New System.Drawing.Size(64, 19)
        Me.CheckBox1.TabIndex = 21
        Me.CheckBox1.Text = "有或无"
        '
        'frmimportdata
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(818, 599)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.GroupBox1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.MaximizeBox = False
        Me.Name = "frmimportdata"
        Me.Text = "数据导入"
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox2.ResumeLayout(False)
        Me.GroupBox3.ResumeLayout(False)
        Me.GbMarjoy.ResumeLayout(False)
        Me.GbDepartment.ResumeLayout(False)
        Me.GroupBox5.ResumeLayout(False)
        Me.GroupBox8.ResumeLayout(False)
        Me.GroupBox7.ResumeLayout(False)
        Me.GroupBox6.ResumeLayout(False)
        Me.GroupBox9.ResumeLayout(False)
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region
    Dim DS As System.Data.DataSet
    Dim FStuid As Boolean
    Dim FName As Boolean
    Dim FSexy As Boolean
    Dim FDepartment As Boolean
    Dim FMarjoy As Boolean
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        FStuid = True
        If Len(Trim(myfilename)) = 0 Then
            Exit Sub
        End If
        If Len(Trim(TextBox1.Text)) = 0 Then
            Exit Sub
        End If
        Dim MyCommand As OleDbDataAdapter
        Dim MyConnection As OleDbConnection
        Dim connectstr As String
        connectstr = "provider=Microsoft.Jet.OLEDB.4.0; " & _
                      "data source="
        connectstr = connectstr & Trim(myfilename) & _
                  ";Extended Properties=Excel 8.0;"
        Try
            MyConnection = New System.Data.OleDb.OleDbConnection(connectstr)
            ' Select the data from Sheet1 of the workbook.
            MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
                  "select * from [" & Trim(TextBox2.Text) & "$]", MyConnection)
            DS = Nothing
            DS = New System.Data.DataSet
            MyCommand.Fill(DS)
            MyConnection.Close()
            Dim mycol As DataColumn
            CombStuCardID.Items.Clear()
            CombStuName.Items.Clear()
            CombSexy.Items.Clear()
            CombDepartment.Items.Clear()
            CombMarjoy.Items.Clear()
            If Not DS Is Nothing Then
                If DS.Tables(0).Columns.Count >= 5 Then
                    CheckBox1.Checked = True
                ElseIf DS.Tables(0).Columns.Count = 4 Then
                    CheckBox1.Checked = False
                Else
                    DS = Nothing
                    Exit Sub
                End If
                If CheckBox1.Checked Then
                    For Each mycol In DS.Tables(0).Columns
                        CombStuCardID.Items.Add(Trim(mycol.Caption))
                        CombStuName.Items.Add(Trim(mycol.Caption))
                        CombSexy.Items.Add(Trim(mycol.Caption))
                        CombDepartment.Items.Add(Trim(mycol.Caption))
                        CombMarjoy.Items.Add(Trim(mycol.Caption))
                    Next

                    CombStuCardID.SelectedIndex = 0
                    CombStuName.SelectedIndex = 0
                    CombSexy.SelectedIndex = 0
                    CombDepartment.SelectedIndex = 0
                    CombMarjoy.SelectedIndex = 0
                Else

                    For Each mycol In DS.Tables(0).Columns
                        CombStuCardID.Items.Add(Trim(mycol.Caption))
                        CombStuName.Items.Add(Trim(mycol.Caption))
                        CombSexy.Items.Add(Trim(mycol.Caption))
                        'CombDepartment.Items.Add(Trim(mycol.Caption))
                        CombMarjoy.Items.Add(Trim(mycol.Caption))
                    Next

                    CombStuCardID.SelectedIndex = 0
                    CombStuName.SelectedIndex = 0
                    CombSexy.SelectedIndex = 0
                    ' CombDepartment.SelectedIndex = 0
                    CombMarjoy.SelectedIndex = 0
                End If
            End If
        Catch
            MsgBox("请您输入正确的工作表名称", MsgBoxStyle.Exclamation, "翼清考试系统V2.0")
            MyConnection = Nothing
            MyCommand = Nothing
            DS = Nothing
            Exit Sub
        End Try
    End Sub
    Private Sub cmdfileName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdfileName.Click
        Openfile.Filter = "(Excel文件 *.xls)|*.xls"
        If Openfile.ShowDialog() = DialogResult.OK Then
            If Len(Trim(Openfile.FileName)) = 0 Then
                myfilename = ""
                Exit Sub
            End If
            TextBox1.Text = Openfile.FileName
            myfilename = Openfile.FileName
        Else
            myfilename = ""
        End If
    End Sub


    Private Sub cmdCheckOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCheckOK.Click
        Dim drow As DataRow
        ProgressBar1.Value = 0
        ProgressBar1.Visible = True
        If Not DS Is Nothing Then
            myTempStu.DeleteAllDB()
            ProgressBar1.Minimum = 0
            ProgressBar1.Maximum = DS.Tables(0).Rows.Count
            For Each drow In DS.Tables(0).Rows
                myTempStu.Initials()
                If IsDBNull(drow.Item(Trim(CombStuCardID.Text))) Then
                    myTempStu.StudentCardID = ""
                Else
                    myTempStu.StudentCardID = Trim(drow.Item(Trim(CombStuCardID.Text)))
                End If
                If IsDBNull(drow.Item(Trim(CombStuName.Text))) Then
                    myTempStu.StudentName = ""
                Else
                    myTempStu.StudentName = Trim(drow.Item(Trim(CombStuName.Text)))
                End If
                If IsDBNull(drow.Item(Trim(CombSexy.Text))) Then
                    myTempStu.Sexy = ""
                Else
                    myTempStu.Sexy = Trim(drow.Item(Trim(CombSexy.Text)))
                End If
                If CheckBox1.Checked Then
                    If Len(Trim(CombDepartment.Text)) > 0 Then
                        If IsDBNull(drow.Item(Trim(CombDepartment.Text))) Then
                            myTempStu.DepartmentName = ""
                        Else
                            myTempStu.DepartmentName = Trim(drow.Item(Trim(CombDepartment.Text)))
                        End If
                    Else
                        myTempStu.DepartmentName = "西北大学"
                    End If

                Else
                    myTempStu.DepartmentName = "西北大学"
                End If


                If IsDBNull(drow.Item(Trim(CombMarjoy.Text))) Then
                    myTempStu.MarjoyName = ""
                Else
                    myTempStu.MarjoyName = Trim(drow.Item(Trim(CombMarjoy.Text)))
                End If

                myTempStu.InsertDB()
                ProgressBar1.PerformStep()
            Next
            TvTempDepart.Nodes.Clear()
            LbTempmar.Items.Clear()
            GroupBox6.Text = "院系:[]"
            GroupBox7.Text = "专业:[]"
            Dim dbset As DataSet
            dbset = myTempStu.GetDepartments()
            If Not dbset Is Nothing Then
                Dim node As TreeNode
                For Each drow In dbset.Tables(0).Rows
                    node = TvTempDepart.Nodes.Add(drow.Item("院系"))
                    node.ImageIndex = 0
                    node.SelectedImageIndex = 1
                Next
            End If
        End If
        ProgressBar1.Visible = False
    End Sub

    Private Sub frmimportdata_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FStuid = True
        FName = False
        FSexy = False
        FDepartment = False
        FMarjoy = False
        MyDep.Initials()
        CurDep.Initials()
        MyMar.Initials()
        CurMar.Initials()
        MyStu.Initials()
        CurStu.Initials()
        CheckBox1.Checked = True
        MyDep.LoadDepNodesToTV(TvDep)
    End Sub

    Private Sub CombStuCardID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CombStuCardID.SelectedIndexChanged
        If CheckBox1.Checked Then
            If CombStuCardID.Text <> CombStuName.Text And _
                        CombStuCardID.Text <> CombSexy.Text And _
                        CombStuCardID.Text <> CombDepartment.Text And _
                        CombStuCardID.Text <> CombMarjoy.Text Then
                FStuid = True
            Else
                FStuid = False
            End If
            If FStuid And FName And FSexy And FDepartment And FMarjoy Then
                cmdCheckOK.Enabled = True
            Else
                cmdCheckOK.Enabled = False
            End If
        Else
            If CombStuCardID.Text <> CombStuName.Text And _
                                   CombStuCardID.Text <> CombSexy.Text And _
                                   CombStuCardID.Text <> CombMarjoy.Text Then
                FStuid = True
            Else
                FStuid = False
            End If
            If FStuid And FName And FSexy And FMarjoy Then
                cmdCheckOK.Enabled = True
            Else
                cmdCheckOK.Enabled = False
            End If
        End If






    End Sub

    Private Sub CombStuName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CombStuName.SelectedIndexChanged
        If CheckBox1.Checked Then
            If CombStuName.Text <> CombStuCardID.Text And _
                            CombStuName.Text <> CombSexy.Text And _
                            CombStuName.Text <> CombDepartment.Text And _
                            CombStuName.Text <> CombMarjoy.Text Then