- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- [求助] 怎样在A表查重导入B表 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=117848)
-- 发布时间：2018/4/19 20:49:00
-- [求助] 怎样在A表查重导入B表
要把一个EXCEL导入两个表；
因数据量大，每次导入均要花很久（现在是 38000行*44列的数据导入2个表花了近7小时）
个人估计是查重的时间太久导致导入时间长
如下代码，本人是小白一个
只能把导入一个EXCEL的代码再写一遍这样来导入2个表
两个表均要先查重再导入；
因（订单）表内数据量太大，能不能都在（订单简表）里面查重，
只要系统单号在（订单简表）里的系统单号列找不到
那就把该订单导入到（订单）表；
就是第一段的代码该怎么优化呢
===================================================================
Dim dlg As New OpenFileDialog
dlg.Filter = "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog =DialogResult.OK Then
Dim t As Table = Tables("订单")
t.ResumeRedraw()
t.StopRedraw()
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)
Dim Rg As MSExcel.Range = Ws.UsedRange
Dim arg = rg.value
For n As Integer = 2 To rg.Rows.Count
Dim r As DataRow = t.DataTable.Find("系统单号 = \'" & arg(n, 1) & "\' and 商品编码 = \'" & arg(n, 24) & "\'")
If r Is Nothing Then r = t.DataTable.AddNew()
For i As Integer = 1 To rg.Columns.count
Dim cname As String = arg(1,i)
If cname > "" AndAlso t.Cols.Contains(cname) Then
If t.Cols(cname).IsDate AndAlso arg(n,i).Gettype.name = "double" Then
r(cname) = DateTime.FromOADate(arg(n,i))
Else
r(cname) = arg(n, i)
End If
ElseIf cname = "收件人姓名*"
r("收件人姓名") = arg(n, i)
ElseIf cname = "收件人电话/手机"
r("收件人电话") = arg(n, i)
ElseIf cname = "商品编码*"
r("商品编码") = arg(n, i)
End If
Next
Next
catch ex As exception
msgbox(ex.message)
MessageBox.Show("导入失败!","恭喜!")
finally
t.ResumeRedraw()
app.quit
End try
End If
Dim t2 As Table = Tables("订单简表")
t2.ResumeRedraw()
t2.StopRedraw()
Dim App2 As New MSExcel.Application
try
Dim Wb2 As MSExcel.WorkBook = App2.WorkBooks.Open(dlg.FileName)
Dim Ws2 As MSExcel.WorkSheet = Wb2.WorkSheets(1)
Dim Rg2 As MSExcel.Range = Ws2.UsedRange
Dim arg2 = rg2.value
For n As Integer = 2 To rg2.Rows.Count
Dim r2 As DataRow = t2.DataTable.Find("系统单号 = \'" & arg2(n, 1) & "\'")
If r2 Is Nothing Then r2 = t2.DataTable.AddNew()
For i As Integer = 1 To rg2.Columns.count
Dim cname As String = arg2(1,i)
If cname > "" AndAlso t2.Cols.Contains(cname) Then
If t2.Cols(cname).IsDate AndAlso arg2(n,i).Gettype.name = "double" Then
r2(cname) = DateTime.FromOADate(arg2(n,i))
Else
r2(cname) = arg2(n, i)
End If
ElseIf cname = "收件人姓名*"
r2("收件人姓名") = arg2(n, i)
ElseIf cname = "收件人电话/手机"
r2("收件人电话") = arg2(n, i)
End If
Next
Next
catch ex As exception
msgbox(ex.message)
MessageBox.Show("导入失败!","恭喜!")
finally
t2.ResumeRedraw()
app2.quit
End try
-- 发布时间：2018/4/19 21:06:00
--
这句代码
t.DataTable.Find("系统单号 = \'" & arg(n, 1) & "\' and 商品编码 = \'" & arg(n, 24) & "\'")
和这句代码
t2.DataTable.Find("系统单号 = \'" & arg2(n, 1) & "\'")
删除后，测试。
然后参考这种方式导入数据
| 下载信息 [文件大小：31.3 KB 下载次数：7] | |
 点击浏览该文件:导入excel文件.zip |