如何加快写入Excel工作表的速度

本文关键字：速度 工作 Excel 何加快 | 更新日期: 2023-09-27 17:57:57

我目前正在使用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];
}

如何加快写入Excel工作表的速度

尝试将数组分配给一个范围，而不是逐个单元格进行中断和分配：

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;

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

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;
相关文章：