Vb.net 史上最快的数据导入到EXCEL程序

KunSai 2007-12-24 05:45:49

最近一直为从程序中导入数据到EXCEL速度太慢而苦恼（从程序中提取的数据量有几万行），因此到处查找一些最快的方法，甚至请教朋友手下的工程师也无果。从网上搜索的一些资料也大同小异。后来在车上时突然灵光一闪，把其中一种方法改一下形式可不可以达到呢？回家迫不及待地一试，没想到成功了。本着乐于分享的精神，拿出来在这里供大家参考一下，如果有用得着别忘了留言给我哦!

目前，从程序中导入EXCEL有三种通用方法（据我所知的~)，按快到慢的顺序如下：

1..数据导入dataset的表中，然后FOR循环表的行与列，并将值写入EXCEL的CELL中（最慢）

2。数据导入dataset的表中，然后FOR循环表的行与列，并将值写入数组DataArray(,),然后利用MyExcel.Range("A2").resize(m,n).value=DataArray (较快)

3..

最近一直为从程序中导入数据到EXCEL速度太慢而苦恼（从程序中提取的数据量有几万行），因此到处查找一些最快的方法，甚至请教朋友手下的工程师也无果。从网上搜索的一些资料也大同小异。后来在车上时突然灵光一闪，把其中一种方法改一下形式可不可以达到呢？回家迫不及待地一试，没想到成功了。本着乐于分享的精神，拿出来在这里供大家参考一下，如果有用得着别忘了留言给我哦1

目前，从程序中导入EXCEL有三种通用方法（据我所知的~)，按快到慢的顺序如下：

1..数据导入dataset的表中，然后FOR循环表的行与列，并将值写入EXCEL的CELL中（最慢）

2。数据导入dataset的表中，然后FOR循环表的行与列，并将值写入数组DataArray(,),然后利用MyExcel.Range("A2").resize(m,n).value=DataArray (较快，但数据一大特慢)

3..数据导入dataset的表中，然后FOR循环表的行与列，并将值写入数组DataArray(,),然后将数组发送给剪贴板 System.Windows.Forms.Clipboard.SetDataObject(DataArray)，然后在EXCEL对象中 MyExcel.Range("A1").Select() -----MyExcel.Paste即可（较快，但数据一大特慢）

在发现这几种方法不如意后我一直在思考这个问题。后来发现使用些种方法最佳（导几万条记录一分钟左右）！

1、不要将数据读入dataset的表中，而是直接使用datareader

然后调用datareader.read()方法，读取一行则将数据不断写入二维数组

2、然后利用MyExcel.Range("A2").resize(m,n).value=DataArray 方法一次写入excel

试用发现这种方法确实非常快。下面附上我写的一段代码，请大家参考！

Sqlcommand.Connection = SqlConn
Sqlcommand.CommandType = CommandType.StoredProcedure
Sqlcommand.CommandText = "pBillandStockComp"
Sqlcommand.CommandTimeout = 600
With Sqlcommand.Parameters
.Add("@SDate", SqlDbType.VarChar)
.Add("@EDate", SqlDbType.VarChar)
.Add("@Area", SqlDbType.VarChar)
.Add("@Brand", SqlDbType.VarChar)
.Add("@Property", SqlDbType.VarChar)
.Add("@year", SqlDbType.VarChar)
.Add("@season", SqlDbType.VarChar)
.Add("@sex", SqlDbType.VarChar)
.Add("@isShowShopInfo", SqlDbType.Bit)
.Add("@isShowShopDetail", SqlDbType.Bit)
.Add("@isShowProdNo", SqlDbType.Bit)
.Add("@isShowGoodsDetail", SqlDbType.Bit)
.Add("@isShowDiscount", SqlDbType.Bit)
.Add("@isShowColor", SqlDbType.Bit)
.Add("@isShowSize", SqlDbType.Bit)
.Add("@shop", SqlDbType.VarChar)
.Add("@ITEM", SqlDbType.VarChar)
.Add("@userOrderBy", SqlDbType.VarChar)
.Add("@WH", SqlDbType.VarChar)
.Add("@Having", SqlDbType.VarChar)
End With

With Sqlcommand.Parameters
.Item("@SDate").Value = DateTimePicker1.Value.ToString("yyyy-MM-dd")
.Item("@EDate").Value = DateTimePicker2.Value.ToString("yyyy-MM-dd")
.Item("@Area").Value = (ComboBox1.Text)
.Item("@Brand").Value = (ComboBox13.Text)
.Item("@Property").Value = (ComboBox14.Text)
.Item("@year").Value = (ComboBox4.Text)
.Item("@season").Value = (ComboBox5.Text)
.Item("@sex").Value = (ComboBox6.Text)
.Item("@isShowShopInfo").Value = ConverBoolean(ComboBox11.Text)
.Item("@isShowShopDetail").Value = ConverBoolean(ComboBox10.Text)
.Item("@isShowProdNo").Value = 1
.Item("@isShowGoodsDetail").Value = ConverBoolean(ComboBox12.Text)
.Item("@isShowDiscount").Value = ConverBoolean(ComboBox7.Text)
.Item("@isShowColor").Value = ConverBoolean(ComboBox8.Text)
.Item("@isShowSize").Value = ConverBoolean(ComboBox9.Text)
.Item("@shop").Value = ConverBoolean(ComboBox2.Text)
.Item("@ITEM").Value = ConverBoolean(ComboBox3.Text)
.Item("@userOrderBy").Value = ""
.Item("@WH").Value = ""
.Item("@Having").Value = ""
End With

SqlConn.Open()
dr = Sqlcommand.ExecuteReader(CommandBehavior.CloseConnection)

'讯取dt中的数据
ReDim DataArray(65000, 28)

While dr.Read()
DataArray(i, 0) = dr.Item(0)
DataArray(i, 1) = dr.Item(1)
DataArray(i, 2) = dr.Item(2)
DataArray(i, 3) = dr.Item(3)
DataArray(i, 4) = dr.Item(4)
DataArray(i, 5) = dr.Item(5)
DataArray(i, 6) = ""
DataArray(i, 7) = ""
DataArray(i, 8) = dr.Item(8)
DataArray(i, 9) = dr.Item(9)
DataArray(i, 10) = dr.Item(10)
DataArray(i, 11) = dr.Item(11)
DataArray(i, 12) = dr.Item(12)
DataArray(i, 13) = dr.Item(13)
DataArray(i, 14) = dr.Item(14)
DataArray(i, 15) = dr.Item(15)
DataArray(i, 16) = dr.Item(16)
DataArray(i, 17) = dr.Item(17)
DataArray(i, 18) = dr.Item(18)
DataArray(i, 19) = dr.Item(19)
DataArray(i, 20) = dr.Item(20)
DataArray(i, 21) = dr.Item(21)
DataArray(i, 22) = dr.Item(22)
DataArray(i, 23) = dr.Item(23)
DataArray(i, 24) = dr.Item(24)
DataArray(i, 25) = dr.Item(25)
DataArray(i, 26) = dr.Item(26)
DataArray(i, 27) = dr.Item(27)
i += 1
End While

Dim Exlapp As New Excel.Application
Dim ExlBook As Workbook = Exlapp.Workbooks.Add()
Dim ExlSheet As Worksheet

ExlSheet = ExlBook.Worksheets.Add()
ExlSheet.Name = "存销表"
ExlSheet.Activate()
ExlSheet.Range("A2").Resize(m, n).Value = DataArray

...全文