以文本方式查看主题 - Foxtable(狐表) (http://www.foxtable.com/bbs/index.asp) -- 专家坐堂 (http://www.foxtable.com/bbs/list.asp?boardid=2) ---- 代码执行效率问题 (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=139509) |
-- 作者:mxl810823 -- 发布时间:2019/8/14 14:22:00 -- 代码执行效率问题 以下这段程序是一上从EXCEL导入内容到表的,EXCEL表有两百多列,十几万行,但是执行时间达到十几个小时。前面还快点,后面就慢得要死。系统占用资源不高,但是CPU温度80度左右。是CPU算力差吗??? 我想知道是我的代码执行效率出了问题吗?有没有改进的地方?? Dim Result As DialogResult Result = MessageBox.Show("确定要导入结单数据吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) If Result = DialogResult.Yes Then Dim dlg As New OpenFileDialog \'定义一个新的OpenFileDialog dlg.Filter= "Excel文件|*.xlsx|Excel文件|*.xls" \'设置筛选器 If dlg.ShowDialog = DialogResult.Ok Then \'如果用户单击了确定按钮 Dim ip As String = dlg.FileName Dim Resu As DialogResult Resu = MessageBox.Show("当前选择的文件是""" & ip & """,确定吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) If Resu = DialogResult.Yes Then vars("导入数据") = True Dim Book As New XLS.Book("" & ip & "") Dim Sheet As XLS.Sheet = Book.Sheets(0) Dim ns As new List(of Integer) Dim fs As new List(of Integer) Dim gs As new List(of String) Dim ms As String() = {"日期","线别V班别","单号","OQC检验","电测操作者","外检操作者","追溯喷码V周期","标准等级","类型","型号","判定结果","备注","区块","入数","不良总数","电性抽检数","外观抽检数","主电性不良数","次电性不良数","外观不良数"} For p As Integer = 0 To ms.length - 1 For i As Integer = 0 To 30 If sheet(0,i).text = ms(p) Then ns.add(i) Exit For End If Next Next For i As Integer = 20 To 260 Dim s As String = sheet(0,i).text If s = "" Then Exit For Else fs.add(i) gs.add(s) End If Next Dim ww As Integer = 0 Dim q As Integer = 0 Dim jin As String For i As Integer = 1 To sheet.Rows.count-1 If i = 1 Or q = 1 Then ww = 0 Else ww = ww + 1 End If Dim dr As Row = Tables("不良PPM总表").AddNew Dim jdd As Date = cdate("" & sheet(i,ns(0)).text & "") Dim jww As String = Format(ww,"000000") Dim n As Date = Date.now() Dim y As Integer = jdd.year Dim m As Integer = jdd.month Dim mm As String = Format(m,"00") Dim d As Integer = jdd.day Dim dd As String = Format(d,"00") jin = y & "" & mm & "" & "" & dd & "" & "" & jww & "" dr("检测号") = jin dr("编辑者") = "" & uname & "" For v As Integer = 0 To 12 dr(ms(v)) = sheet(i,ns(v)).Text Next For v As Integer = 13 To 19 dr(ms(v)) = val(sheet(i,ns(v)).Text) Next For k As Integer = 0 To fs.count - 1 If val(sheet(i,fs(k)).Text) > 0 Then Dim ddr As Row = Tables("不良PPM分表").AddNew ddr("检测号") = jin ddr("NG项目") = gs(k) ddr("数量") = val(sheet(i,fs(k)).Text) ddr("编辑者") = "" & uname & "" End If Next If sheet(i+1,ns(0)).text <> "" Then If sheet(i+1,ns(0)).text = sheet(i,ns(0)).text Then q = 0 Else q = 1 End If End If Next End If Dim Res As DialogResult Res = MessageBox.Show("导入完成,现在要保存数据吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) If Res = DialogResult.Yes Then DataTables("不良PPM总表").save() DataTables("不良PPM分表").save() Else DataTables("不良PPM总表").RejectChanges() DataTables("不良PPM分表").RejectChanges() End If vars("导入数据") = False End If
|
-- 作者:有点蓝 -- 发布时间:2019/8/14 14:34:00 -- 1、导入前停掉所有表事件:http://www.foxtable.com/webhelp/topics/2218.htm 2、清除和这个表有关的所有表达式列的表达式:http://www.foxtable.com/webhelp/topics/1935.htm 十几万行,两百多列,数据不少了,按照普通一个表20列左右算,已经有一百多W行的记录了,何况代码里面还套了N个循环。 |