myWorksheet.Range.Resize.Value = 数组在高行数时失败

发布于 2024-10-15 14:43:28 字数 1185 浏览 2 评论 0原文

我继承了一个大型项目,它使用 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技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

掀纱窥君容 2024-10-22 14:43:28

我认为这里的问题在于细胞范围不断扩大。

我会重新设计该行:

myWorksheet.Range("A" & (rowNumber + horizIndex)).Resize(horizIndex + 1, columnCount).Value = arr

阅读

myWorksheet.Range("A" & (rowNumber + horizIndex)).Resize(1, columnCount).Value = subArr

编辑

鉴于您正在有效地迭代每个单元格,为什么不利用这一点:

For horizIndex As Integer = 0 To dataArray.GetUpperBound(0)
    For columnIndex As Integer = 0 To dataArray.GetUpperBound(1)
        myWorksheet.Cells(horizIndex + 1, columnIndex + 1).Value = dataArray(horizIndex, columnIndex)
    Next
Next

I think the problem here is with your ever expanding cell range.

I'd rework the line:

myWorksheet.Range("A" & (rowNumber + horizIndex)).Resize(horizIndex + 1, columnCount).Value = arr

to read

myWorksheet.Range("A" & (rowNumber + horizIndex)).Resize(1, columnCount).Value = subArr

EDIT

Given that you are effectively iterating over every cell why not take advantage of that:

For horizIndex As Integer = 0 To dataArray.GetUpperBound(0)
    For columnIndex As Integer = 0 To dataArray.GetUpperBound(1)
        myWorksheet.Cells(horizIndex + 1, columnIndex + 1).Value = dataArray(horizIndex, columnIndex)
    Next
Next
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文