vb.net操作excel代码太慢

[zss427607]

使用vb.net操作excel进行数据处理才700条就得2分钟太慢了，

主要实现一下操作，1、打开excel判断是否存在“产量”的表，存在新建0-31张新表，2、判断产量大于0的数

据全部放在0表中，3、日期.day=1，的放在1表中，等于2的，放在2表中，一直到31.

请各位帮忙优化一下，谢谢了。

代码如下：
Dim strFilePath As String
Dim i, j, k, l, m, n As Integer
OpenFileDialog1.Multiselect = False
OpenFileDialog1.Filter = "Excel File (*.xls)|*.xls"
OpenFileDialog1.ShowDialog()
If OpenFileDialog1.FileName <> "" Then
strFilePath = OpenFileDialog1.FileName
Dim eExcel As New ExcelHandler(strFilePath)
eExcel.Open(strFilePath)
Try
For i = 1 To eExcel.wWorkbook.Sheets.Count
If eExcel.wWorkbook.Sheets(i).name = "产量" Then
For j = 0 To 31
eExcel.AddSheet(j)
Next
End If
Next

l = 2
For k = 2 To 1000 'eExcel.wWorkbook.Sheets("产量").Rows.Count
If eExcel.wWorkbook.Sheets("产量").Cells(k, 13).Value > 0 Then
eExcel.wWorkbook.Sheets("0").Cells(l, 1).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 1).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 2).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 2).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 3).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 3).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 4).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 4).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 5).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 5).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 6).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 6).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 7).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 7).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 8).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 8).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 9).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 9).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 10).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 10).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 11).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 11).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 12).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 12).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 13).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 13).Value
eExcel.wWorkbook.Sheets("0").Cells(l, 14).Value = eExcel.wWorkbook.Sheets("产量").Cells(k, 14).Value
l = l + 1
End If
Next
Dim MyDate As Date
n = 2
For m = 2 To 1000 'eExcel.wWorkbook.Sheets("产量").Rows.Count
MyDate = eExcel.wWorkbook.Sheets("产量").Cells(m, 2).Value
If MyDate.Day = "1" Then
eExcel.wWorkbook.Sheets("1").Cells(n, 1).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 1).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 2).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 2).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 3).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 3).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 4).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 4).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 5).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 5).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 6).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 6).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 7).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 7).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 8).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 8).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 9).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 9).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 10).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 10).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 11).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 11).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 12).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 12).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 13).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 13).Value
eExcel.wWorkbook.Sheets("1").Cells(n, 14).Value = eExcel.wWorkbook.Sheets("产量").Cells(m, 14).Value
n = n + 1
End If
Next

Catch
End Try
eExcel.Close()
End If

[以专业开发人员为伍]

如果3次于Excel进行跨平台交互操作就能搞定的，你要用3000次操作搞定，这能不慢么？

[以专业开发人员为伍]

我少估计你的奢侈浪费的尺度。你那不是3000次，而是大概30000次。 比如说，给Excel的 B2:F1001 进行赋值操作，用一个二维数组直接给 worksheet.Range("B2:F1001").Value2 赋值就行了。一次操作，而你用了5000次交互操作！ 另外，对Excel操作之前，可以设置其 ScreenUpdating 为false，等更新完数据之后才设置为 True。 等等。对Excel操作需要认真学习Excel对象结构，不要用最懒惰和最不需要思考的c#代码。

[以专业开发人员为伍]

不要用最懒惰和最不需要思考的c#代码 --> 不要用最懒惰和最不需要思考的代码 代码不重要，思考和总结才重要。

[zss427607]

引用 4 楼 caozhy 的回复:

用OLEDB以数据库方式读取。

Dim strFilePath As String Dim i, j, k, l, m, n As Integer OpenFileDialog1.Multiselect = False OpenFileDialog1.Filter = "Excel File (*.xls)|*.xls" OpenFileDialog1.ShowDialog() If OpenFileDialog1.FileName <> "" Then strFilePath = OpenFileDialog1.FileName Dim eExcel As New ExcelHandler(strFilePath) eExcel.Open(strFilePath) Try For i = 1 To eExcel.wWorkbook.Sheets.Count If eExcel.wWorkbook.Sheets(i).name = "产量" Then For j = 0 To 31 eExcel.AddSheet(j) Next End If Next Dim Conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties=Excel 8.0;" Dim OleDbConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(Conn) Dim sql As String = "SELECT * FROM [产量$] where 产量 > 0" Dim OleDbDataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, Conn) Dim dt As New DataTable OleDbDataAdapter.Fill(dt) eExcel.wWorkbook.Sheets("0").cells(1, 1).value = dt.DataSet.Tables Catch End Try eExcel.Close() End If 取不到数据呀，请版主帮忙看看。

[threenewbee]

用OLEDB以数据库方式读取。