使用 Office Interop 将单元格写入 Excel 的最快方法? [英] Fastest way to write cells to Excel with Office Interop?

问题描述

我正在编写一个函数来使用 VB .NET 中的 Office 互操作将数据导出到 Excel.我目前正在使用 Excel 工作表的 Cells() 方法直接编写单元格:

I am writing a function to export data to Excel using the Office Interop in VB .NET. I am currently writing the cells directly using the Excel worksheet's Cells() method:

worksheet.Cells(rowIndex, colIndex) = data(rowIndex)(colIndex)

处理大量数据需要很长时间.有没有更快的方法一次将大量数据写入 Excel?使用范围做某事会更快吗?

This is taking a long time for large amounts of data. Is there a faster way to write a lot of data to Excel at once? Would doing something with ranges be faster?

推荐答案

如果可以的话，你应该避免逐个单元格地读写.使用数组并一次读取或写入整个块要快得多.我不久前在 使用 C# 从工作表中读取;基本上，相同的代码以相反的方式工作(见下文)，并且运行速度更快，尤其是对于更大的数据块.

You should avoid reading and writing cell by cell if you can. It is much faster to work with arrays, and read or write entire blocks at once. I wrote a post a while back on reading from worksheets using C#; basically, the same code works the other way around (see below), and will run much faster, especially with larger blocks of data.

  var sheet = (Worksheet)Application.ActiveSheet;
  var range = sheet.get_Range("A1", "B2");
  var data = new string[3,3];
  data[0, 0] = "A1";
  data[0, 1] = "B1";
  data[1, 0] = "A2";
  data[1, 1] = "B2";
  range.Value2 = data;

这篇关于使用 Office Interop 将单元格写入 Excel 的最快方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！