- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- 如何才能快速导入EXCEL数据 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=132026)
-- 发布时间：2019/3/12 9:50:00
-- 如何才能快速导入EXCEL数据
数据有15万行左右，
用下面的代码导入输入实在是太慢了，就像假死了一样，有没有快速一些的方法呢？
Dim v1 As WinForm.NumericComboBox = e.Form.Controls("NumericComboBox1")
Dim v2 As WinForm.NumericComboBox = e.Form.Controls("NumericComboBox2")
If v1.Value Is Nothing Then
MessageBox.show("必须选择年份")
Return
End If
If v2.Value Is Nothing Then
MessageBox.show("必须选择月份")
Return
End If
Dim t1 As Date = Date.Now
Dim tb As Table = Tables(e.Form.Name & "_table1")
Dim nt As Integer = v1.Value
Dim y As Integer = v2.Value
Dim dlg As New OpenFileDialog \'定义一个新的OpenFileDialog,就是打开
dlg.Filter= "Excel| *.xls;*.xlsx" \'设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then \'如果用户单击了确定按钮
Dim book As new XLS.Book(dlg.FileName)
Dim sheet As XLS.Sheet = book.Sheets(0) \'第一个表
Dim bar As WinForm.ProgressBar = e.Form.Controls("ProgressBar1")
bar.Minimum= 0
bar.Maximum =sheet.Rows.Count
Dim nma() As String = {"catd","catz","catx","hotkeyword","keyfen","szkey","total","mobile","web","jz","ppc","click","roas"}
DataTables("ST11Keyword").StopRedraw()
tb.StopRedraw()
For n As Integer = 3 To sheet.Rows.Count - 1
Dim v As Integer = v + 1
bar.Value = v
Dim dr As DataRow = DataTables("ST11Keyword").AddNew()
For i As Integer = 0 To sheet.Cols.Count - 1
dr(nma(i)) = Sheet(n,i).Value
Next
dr("年") = nt
dr("月") = y
Next
DataTables("ST11Keyword").Save
DataTables("ST11Keyword").LoadFilter = "_identify = 0"
DataTables("ST11Keyword").Load
DataTables("ST11Keyword").ResumeRedraw()
tb.ResumeRedraw()
msgbox("ok")
End If
MessageBox.Show("计算结束, 耗时: " & (Date.Now - t1).TotalSeconds & "秒")
-- 发布时间：2019/3/12 9:56:00
--
首先数据量大,第二个你这个是用的扫描的方式,自然慢,第三个导入后立马保存15万行,需要非常就的时间.
-- 发布时间：2019/3/12 9:58:00
--
有好的办法吗？
-- 发布时间：2019/3/12 10:07:00
--
1、试试这种方式
| 下载信息 [文件大小：312.0 KB 下载次数：4] | |
 点击浏览该文件:excel作为数据源_合并.table |
2、你把后面保存和加载的代码去掉，看速度是否正常
3、如果是导入外部数据库，建议直接在数据库那保存或者导入
方法一：http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=98689&skin=0
方法二：http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=109019&skin=0
方法三：直接把excel拷贝到服务器，导入sqlserver
http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=9001&skin=0
https://blog.csdn.net/u011646279/article/details/80447990