.Net利用Office组件的操作Excel批量插入数据据的时候,会非常慢。
我也曾经想利用其它组件来实现这个功能,但是效果不是很理想。后来经过查阅资料,发现Excel.Range的Value属性是Object的二维数组。
于是我想,不去遍历每个单元格,而是一次取得所要操作区域(Range)的Value属性,对这个二维数组操作,最后再把这个二维数组赋回去,就大功能告成了。
经过编码测试,与我想象中的一样,速度非常之快。
之前代码:
Dim
app
As
New
Excel.Application
app.Visible
=
True
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Dim
book
As
Excel.Workbook
=
app.Workbooks.Add()
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Dim
sheet
As
Excel.Worksheet
=
book.Sheets(
1
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
For
i
As
Integer
=
1
To
1000
For
j
As
Integer
=
1
To
26
sheet.Cells(i, j).Value
=
"
R
"
&
i
&
"
C
"
&
j
Next
Next
之后代码:
Dim
app
As
New
Excel.Application
app.Visible
=
True
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Dim
book
As
Excel.Workbook
=
app.Workbooks.Add()
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Dim
sheet
As
Excel.Worksheet
=
book.Sheets(
1
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Dim
ary
As
Object
(,)
=
sheet.Range(
"
A1:Z1000
"
).Value
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
For
i
As
Integer
=
1
To
1000
For
j
As
Integer
=
1
To
26
ary(i, j)
=
"
R
"
&
i
&
"
C
"
&
j
Next
Next
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
sheet.Range(
"
A1:Z1000
"
).Value
=
ary
附上C#的代码
Excel.Application app = new Excel.Application();
app.Visible = true ;
Excel.Workbook book = app.Workbooks.Add(Missing.Value);
Excel.Worksheet sheet = (Excel.Worksheet)(book.Sheets[ 1 ]);
object [,] ary = ( object [,])(sheet.get_Range( " A1:Z1000 " , Missing.Value).Value2);
for ( int i = 1 ; i <= 1000 ; i ++ )
{
for ( int j = 1 ; j <= 26 ; j ++ )
{
ary[i, j] = " R " + i.ToString() + " C " + j.ToString();
}
}
sheet.get_Range( " A1:Z1000 " , Missing.Value).Value2 = ary;
我也曾经想利用其它组件来实现这个功能,但是效果不是很理想。后来经过查阅资料,发现Excel.Range的Value属性是Object的二维数组。
于是我想,不去遍历每个单元格,而是一次取得所要操作区域(Range)的Value属性,对这个二维数组操作,最后再把这个二维数组赋回去,就大功能告成了。
经过编码测试,与我想象中的一样,速度非常之快。
之前代码:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
附上C#的代码
Excel.Application app = new Excel.Application();
app.Visible = true ;
Excel.Workbook book = app.Workbooks.Add(Missing.Value);
Excel.Worksheet sheet = (Excel.Worksheet)(book.Sheets[ 1 ]);
object [,] ary = ( object [,])(sheet.get_Range( " A1:Z1000 " , Missing.Value).Value2);
for ( int i = 1 ; i <= 1000 ; i ++ )
{
for ( int j = 1 ; j <= 26 ; j ++ )
{
ary[i, j] = " R " + i.ToString() + " C " + j.ToString();
}
}
sheet.get_Range( " A1:Z1000 " , Missing.Value).Value2 = ary;