.net Excel 批量插入性能慢
.NET Excel 2003 批量插入数据很慢的解决办法
.Net利用Office组件的操作Excel批量插入数据据的时候，会非常慢。
我也曾经想利用其它组件来实现这个功能，但是效果不是很理想。后来经过查阅资料，发现Excel.Range的Value属性是Object的二维数组。
于是我想，不去遍历每个单元格，而是一次取得所要操作区域（Range）的Value属性，对这个二维数组操作，最后再把这个二维数组赋回去，就大功能告成了。
经过编码测试，与我想象中的一样，速度非常之快。
之前代码：
Dim app As New Excel.Application
app.Visible = True
Dim book As Excel.Workbook = app.Workbooks.Add()
Dim sheet As Excel.Worksheet = book.Sheets(1)
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
Dim book As Excel.Workbook = app.Workbooks.Add()
Dim sheet As Excel.Worksheet = book.Sheets(1)
Dim ary As Object(,) = sheet.Range("A1:Z1000").Value
For i As Integer = 1 To 1000
For j As Integer = 1 To 26
ary(i, j) = "R" & i & "C" & j
Next
Next
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属性，对这个二维数组操作，最后再把这个二维数组赋回去，就大功能告成了。
经过编码测试，与我想象中的一样，速度非常之快。
之前代码：
Dim app As New Excel.Application app.Visible = True Dim book As Excel.Workbook = app.Workbooks.Add() Dim sheet As Excel.Worksheet = book.Sheets(1) 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 Dim book As Excel.Workbook = app.Workbooks.Add() Dim sheet As Excel.Worksheet = book.Sheets(1) Dim ary As Object(,) = sheet.Range("A1:Z1000").Value For i As Integer = 1 To 1000 For j As Integer = 1 To 26 ary(i, j) = "R" & i & "C" & j Next Next 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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25897606/viewspace-712706/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
Linux 编程工具简单介绍
请登录后发表评论
登录
全部评论
