用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屋！