如何加速向Excel表格写入数据

Question

如何加速向Excel表格写入数据

6

我目前使用Interop对象向Excel电子表格中写入内容。通过使用计时器，我发现以下实现相当及时。在foreach循环中的过程通常需要大约50秒来写入大约2000行，每行有9列。

有没有什么方法可以加快速度呢？

List<string[]> allEntries = fillStringArrayList();

// Set-up for running Excel
xls = new Excel.Application();
workBooks = xls.Workbooks;
workBook = workBooks.Open(workbookPath);
var workSheet = workBook.Sheets["Sheet1"];

// Insert new entries
foreach (string[] entry in allEntries)
{
    // Get the final row in the sheet that is being used
    Excel.Range usedRange = workSheet.UsedRange;
    int rowCount = usedRange.Rows.Count;

    // Format Column A to be type "text"
    workSheet.Cells[rowCount + 1, 1].NumberFormat = "@";

    workSheet.Cells[rowCount + 1, 1] = entry[0];
    workSheet.Cells[rowCount + 1, 2] = entry[1];
    workSheet.Cells[rowCount + 1, 3] = entry[2];
    workSheet.Cells[rowCount + 1, 4] = entry[3];
    workSheet.Cells[rowCount + 1, 5] = entry[4];
    workSheet.Cells[rowCount + 1, 6] = entry[5];
    workSheet.Cells[rowCount + 1, 7] = entry[6];
    workSheet.Cells[rowCount + 1, 8] = entry[7];
    workSheet.Cells[rowCount + 1, 9] = entry[8];
}

- Queue

检查CopyFromRecordset。注释 https://support.microsoft.com/en-us/kb/246335 - Fionnuala

使用秒表和启用优化的发布模式？ - Thomas Ayoub

@ThomasAyoub 是的，没错。 - Queue

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Magnetron · Accepted Answer

尝试将数组分配给范围，而不是逐个单元格地断开并分配：

Excel.Range c1 = (Excel.Range)workSheet.Cells[rowCount + 1, 1];
Excel.Range c2 = (Excel.Range)workSheet.Cells[rowCount + 1, 9];
Excel.Range range = workSheet.get_Range(c1, c2);
range.Value = entry;

编辑：如果您想进一步加快速度，可以将二维数组（字符串[, ]，其中第一维是行，第二维是列）分配给相同大小的范围。

int firstLine = rowCount + 1;
Excel.Range c1 = (Excel.Range)workSheet.Cells[firstLine, 1];

//Gets the size of the first dimension of the array
int arrayXSize = allEntries.GetLength(0); 

Excel.Range c2 = (Excel.Range)workSheet.Cells[firstLine + arrayXSize -1, 9];
Excel.Range range = workSheet.get_Range(c1, c2);

//allEntries is a string[,]
range.Value = allEntries;