专家坐堂-导入速度Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  导入速度  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=101590)

--  作者：刘林
--  发布时间：2017/6/3 15:41:00
--  导入速度

\'DataTables("学生信息").loadfilter = "学校名称 = \'" & _usergroup &"\'"
\'DataTables("学生信息").load
Dim dlg As New OpenFileDialog
dlg.Filter = "Excel|*.xls;*.xlsx;*.csv"
If dlg.ShowDialog = DialogResult.OK Then
Dim Book As New XLS.Book(dlg.filename)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim bj1 As Integer
Dim xm1 As Integer
Dim bsm1 As Integer
Dim sfzjh1 As Integer
Dim csrq As Integer = -1
Dim p As WinForm.ProgressBar
p = e.Form.Controls("ProgressBar1")
p.Maximum = sheet.Rows.Count-1 \'设置最大值
p.Minimum = 0 \'设置最小值
p.Value = 0 \'设置当前值
Dim s1 As new List(of String)
For m As Integer = 0 To sheet.Cols.count -1
If Tables("学生信息").Cols.Contains(sheet(0,m).value) = False Then
s1.add(sheet(0,m).value)
End If
Next
If s1.count >0
Dim yw As String
For yw1 As Integer =0 To s1.count-1
yw = yw & s1(yw1) & "列 "
Next
messagebox.show("请注意,你的学生信息样表表头存在" & s1.count &" 处错误,分别是:" & yw & " 请返回修改后再重新导入","提示" )
Return
End If
For i As Integer = 0 To sheet.Cols.count -1
If sheet(0,i).value = "班级"
bj1 =i
End If
If sheet(0,i).value = "学生姓名"
xm1 = i
End If
If sheet(0,i).value = "身份证件号"
sfzjh1 = i
End If
If sheet(0,i).value ="出生日期"
csrq = i
End If
Next
For n1 As Integer = 1 To Sheet.Rows.Count -1
If trim(sheet(n1,bj1).value).length <> 9 AndAlso trim(sheet(n1,bj1).value).length <> 10
messagebox.show(sheet(n1,bj1).value.Substring(0,2) & "请注意,你的班级列中第" & n1+1 & "行有字符宽度不正确,正确格式应为初中20XX级X班或小学20XX级X班","错误提示",MessageBoxButtons.OK)
Return
Else
If sheet(n1,bj1).value.Substring(0,2).contains("小学") = False AndAlso sheet(n1,bj1).value.Substring(0,2).contains("初中") = False
messagebox.show(sheet(n1,bj1).value.Substring(0,2) & "请注意,你的班级列中第" & n1+1 & "行有字符不规范内容,正确格式应为初中20XX级X班或小学20XX级X班","错误提示",MessageBoxButtons.OK)
Return
End If
End If
Next
\' Tables("学生信息").StopRedraw()
For n As Integer = 1 To Sheet.Rows.Count -1
Dim bj As String = sheet(n,bj1).value.replace(" ","").replace(chr(9),"")
Dim xm As String = sheet(n,xm1).value.replace(" ","").replace(chr(9),"")
Dim sfzjh As String = sheet(n,sfzjh1).value.replace(" ","").replace(chr(9),"")
Dim dr As DataRow = DataTables("学生信息").sqlFind("班级 = \'" & bj & "\'and 学校名称 = \'" & _usergroup & "\' And 学生姓名 = \'" & xm & "\' and 身份证件号 = \'" & sfzjh & "\'")
If dr Is Nothing Then \'如果不存在同考号的行
dr = DataTables("学生信息").AddNew()
End If
For m As Integer = 0 To sheet.Cols.count -1
If sheet(0,m).text =""
Exit For
End If
If sheet(0,m).value IsNot Nothing AndAlso Tables("学生信息").Cols.Contains(sheet(0,m).value) Then
If m = csrq
dr(sheet(0,m).value) = Nothing
Else
dr(sheet(0,m).value) = Sheet(n,m).Value.replace(" ","").replace(chr(9),"")
dr("状态") = "在读:原籍在读"
If dr("学校名称") = ""
dr("学校名称") = _usergroup
End If
End If
End If
dr.save()
Next
p.Value = n
Next
Tables("学生信息").ResumeRedraw()
For Each r As Row In Tables("学生信息").Rows
For Each c As Col In Tables("学生信息").Cols
If r.IsNull(c.Name) = False AndAlso c.IsString Then
r(c.Name) = r(c.Name).replace(" ","")
r(c.Name) = r(c.Name).replace(chr(9),"")
End If
Next
Next
DataTables("学生信息").DataCols("身份证件号").RaiseDataColChanged()
DataTables("学生信息").DataCols("班级").RaiseDataColChanged()
DataTables("学生信息").save()
End If

老师之前问过，就是导入时出现提示不存在""列，我加了红色代码来解决，现在有个问题是整速度较慢，100条记录可能要花1-2分钟，请老师帮改进下导入速度，看哪些地方可优化一下

--  作者：有点蓝
--  发布时间：2017/6/3 16:12:00
--
慢在了sqlFind。

如果Excel文件只有一个班级的数据，那么先把这个班级的数据全部加载出来，再使用Find

如果Excel文件有整个学校的数据，那么先把这个学校的数据全部加载出来，再使用Find

一个学校差不多几千的数据，加载应该不是问题。一个班级就更不要说了