myWorksheet.Range.Resize.Value = 数组在高行数时失败
我继承了一个大型项目,它使用 Office.Interop.Excel 将数据插入电子表格。
在可视交互方面,您单击一个按钮,就会出现一个 Excel 电子表格,其中所有数据都已插入。我提到这一点是因为我在其他地方看到过它已经打开,并且已经插入了值。我正在使用的程序会在 Excel 出现之前插入数据。
不幸的是,一旦插入相对大量的数据,到 Excel 的信息传输就会锁定,程序无法继续。
无论如何,我做了一些调查并发现了以下行:
myWorksheet.Range("A" & rowNumber).Resize(dataArray.GetUpperBound(0) + 1, columnCount).Value = dataArray
这会将 dataArray 中的所有数据直接复制到工作表中的一堆单元格上。
我猜测问题可能是由于尝试一次传输如此大量的数据而引起的,因此我将其更改为逐行复制数据:
For horizIndex As Integer = 0 To dataArray.GetUpperBound(0)
Dim subArr(0, arr.GetUpperBound(1)) As Object
For columnIndex As Integer = 0 To dataArray.GetUpperBound(1)
subArr(0, columnIndex) = dataArray(horizIndex, columnIndex)
Next
myWorksheet.Range("A" & (rowNumber + horizIndex)).Resize(horizIndex + 1, columnCount).Value = arr
Next
这会将 350 左右行复制到 Excel,然后将行设置值只是停止返回。
谁能建议解决这个问题的方法吗?
编辑:好的,我已经尝试了下面拉扎勒斯建议的一些方法,这就是我现在的位置:
只要数据相对较短,或者数据类型相当简单(整数等),代码的所有变体工作正常。只要数据集很小或很简单,原始的批量复制就可以,逐行复制和按单元复制都可以。
不过,我的实际数据集相对复杂,并且包含多种数据类型,包括字符串。
排了 350 行之后它就倒塌了。它落在代码的 Excel 端,线程转到 Excel 并且再也不会回来。
那么,还有更多想法吗?
I've inherited a large project which uses Office.Interop.Excel to insert data into a spreadsheet.
In terms visible interaction, you click on a button and an Excel spreadsheet appears, with all the data already inserted. I mention this because I've seen it done elsewhere with the program already being open and values being inserted. The program I am working with inserts the data before Excel appears.
Unfortunately, once a relatively large amount of data is being inserted, the information transfer to Excel locks up and the program cannot continue.
Anyway, I did some investigating and found the following line:
myWorksheet.Range("A" & rowNumber).Resize(dataArray.GetUpperBound(0) + 1, columnCount).Value = dataArray
This copies all of the data in dataArray directly onto a bunch of cells in the worksheet.
I guessed that the problem might be caused by attempting to transfer such a large amount of data at once, so I altered it to copy data over row-by-row:
For horizIndex As Integer = 0 To dataArray.GetUpperBound(0)
Dim subArr(0, arr.GetUpperBound(1)) As Object
For columnIndex As Integer = 0 To dataArray.GetUpperBound(1)
subArr(0, columnIndex) = dataArray(horizIndex, columnIndex)
Next
myWorksheet.Range("A" & (rowNumber + horizIndex)).Resize(horizIndex + 1, columnCount).Value = arr
Next
This will copy 350 or so rows to Excel and then the line setting Value simply stops returning.
Can anyone suggest a way around this?
EDIT: Okay, I've tried a bunch of what Lazarus suggested below, and here's where I am right now:
So long as the data is relatively short, or the datatypes are fairly simple (Integers, etc.) all variations of the code work fine. The original mass-copy works fine, the copy-by-line works and the copy-by-cell works, so long as the dataset is either small or simple.
My actual dataset, though, is relatively complex, and contains several datatypes, including Strings.
It falls over after 350-ish rows. It falls over on the Excel side of the code, the thread goes away to Excel and never comes back.
So, any more ideas, anyone?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我认为这里的问题在于细胞范围不断扩大。
我会重新设计该行:
阅读
编辑
鉴于您正在有效地迭代每个单元格,为什么不利用这一点:
I think the problem here is with your ever expanding cell range.
I'd rework the line:
to read
EDIT
Given that you are effectively iterating over every cell why not take advantage of that: