如何在Excel Interop中将导出数据优化为excel? [英] How to optimize exporting data to excel in Excel Interop?
问题描述
我正在使用Excel interop从查询结果中创建excel工作簿.当有成千上万的记录时,要花很长时间才能生成工作簿.以下代码是我如何填充单元格的示例.
I am using Excel interop to create excel workbooks from my query results. When there are thousands of records it takes a long time for the workbook to be generated. The below code is a sample of how I am populating the cells.
RowNo = 1
For i = 1 To 4000
ColNo = 1
For j = 1 To 5
Dim cell As excel.Range = ws.Cells(RowNo, ColNo)
Dim value = j
cell.Value = value
ColNo = ColNo + 1
Next
RowNo = RowNo + 1
Next
要运行上述代码,需要花费一分钟以上的时间.我该如何优化呢?
For the above code to run it takes more than a minute. How can I optimize it?
推荐答案
找到了答案.您可以将数据写入数组,然后将数组写入excel范围,而不是逐个单元地写入数据.参见 http: //www.clear-lines.com/blog/post/Write-data-to-an-Excel-worksheet-with-C-fast.aspx
Found the answer. You can write data to an array and then write the array to the excel range rather than writing the data cell by cell. See http://www.clear-lines.com/blog/post/Write-data-to-an-Excel-worksheet-with-C-fast.aspx
private static void WriteArray(int rows, int columns, Worksheet worksheet)
{
var data = new object[rows, columns];
for (var row = 1; row <= rows; row++)
{
for (var column = 1; column <= columns; column++)
{
data[row - 1, column - 1] = "Test";
}
}
var startCell = (Range)worksheet.Cells[1, 1];
var endCell = (Range)worksheet.Cells[rows, columns];
var writeRange = worksheet.Range[startCell, endCell];
writeRange.Value2 = data;
}
这篇关于如何在Excel Interop中将导出数据优化为excel?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!