'''
Dim dlg As New OpenFileDialog '定义一个新的OpenFileDialog
dlg.Filter= "Excel文件|*.xls|Excel文件|*.xlsx" '设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then '如果用户单击了确定按钮
Else
Return
End If
Dim Book As New XLS.Book(dlg.FileName)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
‘注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"仓库名称""存货编码","全球代码","存货分类名称","存货名称","原现存数量","原可用数量","原参考售价标准","现存数量","可用数量","参考售价标准","批号"}
Dim nmsX() As String = {"仓库名称","存货编码","全球代码","存货分类名称","存货名称","现存数量","可用数量","参考售价标准","批号"}
’注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题
Dim pattern As String = "[\u4e00-\u9fa5| ]"
Dim strSta As String
For n As Integer = 1 To Sheet.Rows.Count -1
' If Sheet(n,5).Value.Contains("(") Or Sheet(n,5).Value.Contains(")") Or Sheet(n,6).Value.Contains("(") Or Sheet(n,6).Value.Contains(")") Or Sheet(n,7).Value.Contains("(") Or Sheet(n,7).Value.Contains(")") Then
' Else
Dim drDaoRu As DataRow = DataTables("存货信息导入数据表").AddNew()
For m As Integer = 0 To nmsX.Length - 1
If m=0 Then
Else If m=2 Then
Else If m=8 Then
Else If m=5 Then
If Sheet(n,5).Value = "充足" Then
drDaoRu(nmsX(5)) = 12
Else If Sheet(n,5).Value.Trim() = "" Then
drDaoRu(nmsX(5)) = 0
Else
drDaoRu(nmsX(5)) = CSng(Sheet(n,m).Value)
End If
Else If m=6 Then
If Sheet(n,6).Value = "充足" Then
drDaoRu(nmsX(6)) = 12
Else If Sheet(n,6).Value.Trim() = ""
drDaoRu(nmsX(6)) = 0
Else
drDaoRu(nmsX(6)) = CSng(Sheet(n,m).Value)
End If
Else If m=7 Then
If Sheet(n,7).Value.Trim() = "" Then
drDaoRu(nmsX(7)) = 0
Else If Sheet(n,7).Value.Trim() = "*" Then
drDaoRu(nmsX(7)) = 0
Else
Dim Multi As String = Sheet(n,m).Value
Dim Values1() As String
Values1 = Multi.split("（")
Dim Values2() As String
Values2 = Values1(0).split(" ")
Dim Values() As String
Values = Values2(0).split("-")
strSta = System.Text.RegularExpressions.Regex.Replace(Values(Values.Length-1), pattern ,"")
drDaoRu(nmsX(7)) = CSng(strSta.Trim())
End If
Else
drDaoRu(nmsX(m)) = Sheet(n,m).Value
End If
Next
drDaoRu.Save
'End If
Next
Dim g As New GroupTableBuilder("存货统计表", DataTables("存货信息导入数据表"))
g.Groups.AddDef("存货编码")
g.Groups.AddDef("存货分类名称")
g.Groups.AddDef("存货名称")
g.Totals.AddDef("现存数量")
g.Totals.AddDef("可用数量")
g.Totals.AddDef("参考售价标准")
g.Build()
Tables("存货信息表").StopRedraw()
Dim drListDaoRu As List (of DataRow)
Dim drNew As DataRow
Dim dr As DataRow
Dim drFind As DataRow
drListDaoRu = DataTables("存货统计表").Select("[存货编码]<>''")
For Each drTongJi As DataRow In drListDaoRu
dr = DataTables("存货信息表").Find("[存货编码] = '"& drTongJi("存货编码") &"' And [存货名称] = '"& drTongJi("存货名称") &"'")
If dr IsNot Nothing Then
dr("原现存数量") = dr("现存数量")
dr("原可用数量") = dr("可用数量")
dr("原参考售价标准") = dr("参考售价标准")
dr("原日期") = dr("现日期")
dr("现日期") = Date.Today
dr("现存数量") = drTongJi("现存数量")
dr("可用数量") = drTongJi("可用数量")
dr("参考售价标准") = drTongJi("参考售价标准")
Else
drNew = DataTables("存货信息表").AddNew
drNew("现日期") = Date.Today
drNew("存货编码")=drTongJi("存货编码")
drNew("存货分类名称")=drTongJi("存货分类名称")
drNew("存货名称")=drTongJi("存货名称")
drNew("现存数量")=drTongJi("现存数量")
drNew("可用数量")=drTongJi("可用数量")
drNew("参考售价标准") = drTongJi("参考售价标准")
drNew.Save
End If
Next
Tables("存货信息表").ResumeRedraw()
DataTables("存货信息表").Save
Dim fenLei As WinForm.ComboBox = e.Form.Controls("CboFenLei")
Dim s As String = DataTables("存货信息表").GetComboListString("存货分类名称")
fenLei.ComboList =s
Dim tv As WinForm.TreeView
tv =e.Form.Controls("TreeView1")
tv.Nodes.Clear
For Each ss As String In s.split("|")
Dim nd As WinForm.TreeNode = tv.nodes.Add(ss)
Next
Tables("存货信息表").Filter = ""
DataTables("存货信息导入数据表").DeleteFor("")
DataTables("存货信息导入数据表").Save
DataTables("存货统计表").DeleteFor("")
DataTables("存货统计表").Save
MessageBox.Show("导入成功","提示")
代码是这么写的，一个两万多条数据的表需要十几分钟导入时间，能不能优化一下？
加好友 
发短信
Post By：2017/9/4 18:05:00 [只看该作者]
