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

问题描述

我正在使用VB.NET中的Office Interop编写一个将数据导出到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屋！