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

\'\'\'

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

drDaoRu.Save

\'End If

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

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)

Tables("存货信息表").Filter = ""

DataTables("存货信息导入数据表").DeleteFor("")

DataTables("存货信息导入数据表").Save

DataTables("存货统计表").DeleteFor("")

DataTables("存货统计表").Save

MessageBox.Show("导入成功","提示")

代码是这么写的，一个两万多条数据的表需要十几分钟导入时间，能不能优化一下？