myWorksheet.Range.Resize.Value = array failing at high row numbers
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 settin开发者_如何学Gog 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?
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
精彩评论