Dim dlg As New OpenFileDialog '定义一个新的OpenFileDialog
dlg.Filter = "Excel文件|*.xlsx" '设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then '如果用户单击了确定按钮
Dim Book As New XLS.Book(dlg.FileName)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
If Sheet.Rows.Count = 1 Then
MessageBox.Show("模板中无导入数据,请填充")
'Return
End If
'3检查列名和顺序是否正常
Dim errCol As String = "" '记录异常列名
Dim dcNames As New List(Of String) '定义要上成绩的列名
dcNames.Add("语文")
dcNames.Add("数学")
dcNames.Add("英语")
dcNames.Add("科学")
dcNames.Add("政治")
dcNames.Add("历史")
dcNames.Add("地理")
dcNames.Add("生物")
dcNames.Add("物理")
dcNames.Add("化学")
dcNames.Add("音乐")
dcNames.Add("体育")
dcNames.Add("美术")
'4检查通过,开始导入数据到预览表格
Dim dtb As New DataTableBuilder("daoru")
dtb.AddDef("检测号2024上", Gettype(String), 16)
dtb.AddDef("语文", Gettype(Single))
dtb.AddDef("学生姓名", Gettype(String), 16)
dtb.AddDef("数学", Gettype(Single))
dtb.AddDef("英语", Gettype(Single))
dtb.AddDef("科学", Gettype(Single))
dtb.AddDef("政治", Gettype(Single))
dtb.AddDef("历史", Gettype(Single))
dtb.AddDef("地理", Gettype(Single))
dtb.AddDef("生物", Gettype(Single))
dtb.AddDef("物理", Gettype(Single))
dtb.AddDef("化学", Gettype(Single))
dtb.AddDef("音乐", Gettype(Single))
dtb.AddDef("体育", Gettype(Single))
dtb.AddDef("美术", Gettype(Single))
Forms("成绩统计窗体202407").Open
Dim t As Table = Tables( "成绩统计窗体202407_Table1")
t.DataSource = dtb.BuildDataSource
'5 判断excel表是否有关键字段数据,
Dim zd As New Dictionary(Of String,Integer )
'定义一个集合来接收能导入的数据库
zd.Clear
Dim sfdr As Boolean = False '定义一个逻辑变量为是否导入,来接收是否有标识列
For i As Integer = 0 To Sheet.Cols.Count - 1
If Sheet(0, i).Value ="检测号2024上" Or Sheet(0, i).Value ="学生姓名" Then '查看sheet 表中有无关键字段检测号和学生姓名
errCol = "找到了" & Sheet(0, i).Value & "请继续"
sfdr = True '判断此表包含要导入的列
'Exit For
End If
zd.Add(Sheet(0, i).Value ,i)
Next
'测试字典的内容
'For Each k As String In zd.Keys '显示所有键及其对应的值
'output.Show(zd("检测号2024上"))
'Next
If sfdr = True Then
For n As Integer = 1 To Sheet.Rows.Count - 1
Dim h As Integer
Dim dr As DataRow = Tables("成绩统计窗体202407_table1").DataTable.find("检测号2024上="& Sheet(n,zd("检测号2024上")).Value)
If dr IsNot Nothing Then
For h = 0 To zd.Count -1
If t.DataTable.DataCols.Contains(zd.keys(h)) And zd.keys(h)<>"检测号2024上" Then
'dr(zd.keys(h)) = sheet(n,zd.Values(h)) '这里的字典装载的是要导入成绩的学科
End If
Next
Else
Dim nr As Row = t.AddNew
If t.DataTable.DataCols.Contains(zd.keys(h)) And zd.keys(h)<>"检测号2024上" Then
'dr(zd.keys(h)) = sheet(n,zd.Values(h)) '这里的字典装载的是要导入成绩的学科
End If
End If
Next
End If
End If
加好友 
发短信
Post By：2024/7/3 10:51:00 [只看该作者]
