以下这段程序是一上从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