Dim dlg As New OpenFileDialog '定义一个新的OpenFileDialog
dlg.Filter = "excel文件|*.xls;*.xlsx" '设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then '如果用户单击了确定按钮
Try
Dim Book As New XLS.Book(dlg.FileName)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("基本信息").StopRedraw()
Dim dict2 As New Dictionary(Of String, Integer)
For c As Integer = 0 To Sheet.Cols.Count - 1
If Tables("基本信息").Cols.Contains(Sheet(0, c).Value) Then
dict2.add(Sheet(0, c).Value, c)
End If
Next
Dim dr As DataRow
For n As Integer = 1 To Sheet.Rows.Count - 1
Dim bh1 As String = Sheet(n, dict2("棋赛名称")).Value
If bh1 = "" Then Exit For
Dim bh2 As String = Sheet(n, dict2("棋赛副名")).Value
Dim bh3 As String = Sheet(n, dict2("编号")).Value
Dim bh4 As String = Sheet(n, dict2("姓名")).Value
Dim filter As String
If bh2 = "0" OrElse bh2 = "" Then
If bh3 = "0" OrElse bh3 = "" Then
filter = "[棋赛名称] = '" & bh1 & "' And [棋赛副名] is null And [编号] is null And [姓名] = '" & bh4 & "' And [身份] <> '参赛人'"
Else
filter = "[棋赛名称] = '" & bh1 & "' And [棋赛副名] is null And [编号] = '" & bh3 & "' And [姓名] = '" & bh4 & "' And [身份] = '参赛人'"
End If
Else
If bh3 = "0" OrElse bh3 = "" Then
filter = "[棋赛名称] = '" & bh1 & "' And [棋赛副名] = '" & bh2 & "' And [编号] Is null And [姓名] = '" & bh4 & "' And [身份] <> '参赛人'"
Else
filter = "[棋赛名称] = '" & bh1 & "' And [棋赛副名] = '" & bh2 & "' And [编号] =" & bh3 & " And [姓名] = '" & bh4 & "' And [身份] = '参赛人'"
End If
End If
dr = DataTables("基本信息").SQLFind(filter)
If dr Is Nothing Then '如果基本信息表不存在同一棋赛名称、编号、姓名的行
dr = DataTables("基本信息").AddNew()
End If
For Each key As String In dict2.Keys
If Sheet(n, dict2(key)).text > "" Then
dr(key) = Sheet(n, dict2(key)).Value
Else
dr(key) = Nothing
End If
Next
Next
Tables("基本信息").ResumeRedraw()
Catch ex As exception
MsgBox(ex.Message)
Tables("基本信息").ResumeRedraw()
End Try
End If