vb.net 使用 Office Interop 将单元格写入 Excel 的最快方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3096090/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fastest way to write cells to Excel with Office Interop?
提问by davidscolgan
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:
我正在编写一个函数来使用 VB .NET 中的 Office Interop 将数据导出到 Excel。我目前正在使用 Excel 工作表的 Cells() 方法直接编写单元格：
worksheet.Cells(rowIndex, colIndex) = data(rowIndex)(colIndex)
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?
对于大量数据，这需要很长时间。有没有更快的方法将大量数据一次写入 Excel？做一些有范围的事情会更快吗？
回答by Mathias
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.
如果可以，您应该避免逐个单元地读取和写入。使用数组并一次读取或写入整个块要快得多。不久前，我写了一篇关于使用 C# 读取工作表的文章；基本上，相同的代码以相反的方式工作（见下文），并且运行速度会更快，尤其是对于更大的数据块。
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;
回答by Tommy
If you haven't already, make sure to set Application.ScreenUpdating = falsebefore you start to output your data. This will make things go much faster. The set it back to True when you are done outputting your data. Having to redraw the screen on each cell change takes a good bit of time, bypassing this saves that.
如果您还没有设置Application.ScreenUpdating = false，请确保在开始输出数据之前进行设置。这将使事情进展得更快。完成输出数据后，将其设置回 True。必须在每次单元格更改时重绘屏幕需要很长时间，绕过它可以节省时间。
As for using ranges, you still will need to target 1 (one) specific cell for a value, so I see no benefit here. I am not aware of doing this any faster than what you are doing in regards to actually outputting the data.
至于使用范围，您仍然需要将 1（一个）特定单元格定位为一个值，所以我认为这里没有任何好处。我不知道这样做比实际输出数据的速度更快。
回答by potatopeelings
Just to add to Tommy's answer.
只是为了补充汤米的答案。
- You might also want to set the calculation to manual before you start writing.
- 您可能还想在开始编写之前将计算设置为手动。
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationManual
And set it back to automatic when you're done with your writing. (if there's a chance that the original mode could have been anything other than automatic, you will have to store that value before setting it to manual)
并在您完成写作后将其设置回自动。（如果原始模式有可能不是自动模式，则必须在将其设置为手动之前存储该值）
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationAutomatic
- You could also use the CopyFromRecordset method of the Range object.
- 您还可以使用 Range 对象的 CopyFromRecordset 方法。
回答by Sunny Sharma
The fastest way to write and read values from excel ranges is Range.get_Valueand Range.set_Value.
从 excel 范围写入和读取值的最快方法是Range.get_Value和Range.set_Value。
The way is as below:
方法如下：
Range filledRange = Worksheet.get_Range("A1:Z678",Missing);
object[,] rngval = (object[,]) filledRange.get_Value (XlRangeValueDataType.xlRangeValueDefault);
Range Destination = Worksheet2.get_Range("A1:Z678",Missing);
destination.set_Value(Missing,rngval);
and yes, no iteration required. Performance is just voila!!
是的，不需要迭代。性能只是瞧！
Hope it helps !!
希望能帮助到你 ！！
回答by Josaph
Honestly, the fastest way to write it is with comma delimiters. It's easier to write a line of fields using the Join(",").ToString method instead of trying to iterate through cells. Then save the file as ".csv". Using interop, open the file as a csv which will automatically do the cell update for you upon open.
老实说，最快的写法是使用逗号分隔符。使用 Join(",").ToString 方法编写一行字段比尝试遍历单元格更容易。然后将文件另存为“.csv”。使用互操作，将文件作为 csv 打开，它会在打开时自动为您更新单元格。
回答by VenerableAgents
In case someone else comes along like me looking for a full solution using the method given by @Mathias (which seems to be the fastest for loading into Excel) with @IMil's suggestion on the Array.
Here you go:
如果其他人像我一样使用@Mathias 给出的方法（这似乎是加载到 Excel 中最快的方法）和 @IMil 对数组的建议来寻找完整的解决方案。
干得好：
'dt (DataTable) is the already populated DataTable
'myExcelWorksheet (Worksheet) is the worksheet we are populating
'rowNum (Integer) is the row we want to start from (usually 1)
Dim misValue As Object = System.Reflection.Missing.Value
Dim arr As Object = DataTableToArray(dt)
'Char 65 is the letter "A"
Dim RangeTopLeft As String = Convert.ToChar(65 + 0).ToString() + rowNum.ToString()
Dim RangeBottomRight As String = Convert.ToChar(65 + dt.Columns.Count - 1).ToString() + (rowNum + dt.Rows.Count - 1).ToString()
Dim Range As String = RangeTopLeft + ":" + RangeBottomRight
myExcelWorksheet.Range(Range, misValue).NumberFormat = "@" 'Include this line to format all cells as type "Text" (optional step)
'Assign to the worksheet
myExcelWorksheet.Range(Range, misValue).Value2 = arr
Then
然后
Function DataTableToArray(dt As DataTable) As Object
Dim arr As Object = Array.CreateInstance(GetType(Object), New Integer() {dt.Rows.Count, dt.Columns.Count})
For nRow As Integer = 0 To dt.Rows.Count - 1
For nCol As Integer = 0 To dt.Columns.Count - 1
arr(nRow, nCol) = dt.Rows(nRow).Item(nCol).ToString()
Next
Next
Return arr
End Function
Limitations include only allowing 26 columns before it would need better code for coming up with the range value letters.
限制包括在需要更好的代码来提供范围值字母之前只允许 26 列。
