主题：自选excel位置添加导入数据

Dim dlg As New OpenFileDialog '定义一个新的OpenFileDialog

dlg.Filter = "excel文件|*.xls;*.xlsx" '设置筛选器

If dlg.ShowDialog = DialogResult.Ok Then '如果用户单击了确定按钮

    Dim App As New MSExcel.Application

    Try

        Dim Wb As MSExcel.Workbook = App.WorkBooks.Open(dlg.FileName)

        Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)

        Tables("基本信息").StopRedraw()

        Dim nms() As String = {"棋赛名称", "棋赛副名", "编号", "姓名", "身份"}

        Dim dict As New Dictionary(Of String, Integer)

        Dim dict2 As New Dictionary(Of String, Integer)

        Dim Rg As MSExcel.Range = Ws.UsedRange

        Dim ary = rg.value

        For c As Integer = 1 To rg.Columns.Count

            If array.Indexof(nms, ary(1, c)) >= 0 Then

                dict.add(ary(1, c), c)

            End If

            If Tables("基本信息").Cols.Contains(ary(1, c)) Then

                dict2.add(ary(1, c), c)

            End If

        Next

        Dim newcount As Integer = 0

        Dim Modifycount As Integer = 0

        For n As Integer = 2 To rg.Rows.count

            Dim bh1 As String = ary(n, 1)

            Dim bh2 As String = ary(n, 2)

            Dim bh3 As String = ary(n, 3)

            Dim bh4 As String = ary(n, 4)

            Dim dr As DataRow

            If bh2 = 0 Then

                If bh3 = 0 Then

                    dr = DataTables("基本信息").SQLFind("[棋赛名称] = '" & bh1 & "' And [棋赛副名] is null And [编号] is null And [姓名] = '" & bh4 & "' And [身份] <> '参赛人'")

                Else

                    dr = DataTables("基本信息").SQLFind("[棋赛名称] = '" & bh1 & "' And [棋赛副名] is null And [编号] = '" & bh3 & "' And [姓名] = '" & bh4 & "' And [身份] = '参赛人'")

                End If

            ElseIf bh2 <> 0 Then

                If bh3 = 0 Then

                    dr = DataTables("基本信息").SQLFind("[棋赛名称] = '" & bh1 & "' And [棋赛副名] = '" & bh2 & "' And [编号] Is null And [姓名] = '" & bh4 & "' And [身份] <> '参赛人'")

                Else

                    dr = DataTables("基本信息").SQLFind("[棋赛名称] = '" & bh1 & "' And [棋赛副名] = '" & bh2 & "' And [编号] =" & bh3 & " And [姓名] = '" & bh4 & "' And [身份] = '参赛人'")

                End If

            End If

            If dr Is Nothing Then '如果基本信息表不存在同一棋赛名称、编号、姓名的行

                dr = DataTables("基本信息").AddNew()

                For Each key As String In dict2.Keys

                    dr(key) = ary(n, dict2(key))

                Next

                newcount += 1

            Else

                For Each key As String In dict.Keys

                    dr(key) = ary(n, dict(key))

                Next

                Modifycount += 1

            End If          

        Next

        Tables("基本信息").ResumeRedraw()

    Catch ex As exception

        app.quit

    End Try

End If

见有数据导入了，这是Excel表上的数据：

此主题相关图片如下：微信图片_20240314230137.png

导入基本信息表后基本信息表上的身份变成性别，因为上面的数组没有性别，而性别列在身份列前，造成取数有误，身份被性别取替，是否所有有数据的列，数组上都要有名称？这样数组岂不是很长？