vb.net 在VB.Net中，如何将数组写入Excel

In VB.Net, how to write an Array to Excel

提问by user3469022

In order to speed up writing values to Excel, in VB.Net is it possible to write an Array to a Row rather than a Value to a Cell?

为了加快将值写入 Excel，在 VB.Net 中是否可以将数组写入行而不是将值写入单元格？

I have tried several ways, it either writes nothing or writes just the first value of the Array.

我尝试了几种方法，它要么什么都不写，要么只写数组的第一个值。

Any help would be greatly appreciated.

任何帮助将不胜感激。

Thanks.

谢谢。

Imports Excel = Microsoft.Office.Interop.Excel
...
Dim Array(2) As String
Array(1) = "Hello"
Array(2) = "World"
...
' Tried several ways one at a time...
objSheet.Cells("C5:C6") = Array
objSheet.Cells("C5:C6").Value = Array
objSheet.Range("C5:C6").Value = Array
objSheet.Range("C5").Value = Array

After the first answer, here is the revised code

第一个回答后，这里是修改后的代码

Dim Array(2, 0) As String
Array(0, 0) = "Hello"
Array(1, 0) = "World"
Array(2, 0) = "One"
...

' Test 1
objSheet.Cells("C5:C6").Value = Array 'I get Invalid Parameter (Exception HRESULT : 0x80070057 (E_INVALIDARG))

' Test 2
objxlRange = objSheet.Range("C5:C7") ' Writes Array content as a column (Vertically)
objxlRange.Value = Array

' Test 3
objxlRange = objSheet.Range("C5:E5") ' Writes only first entry 'Hello' in each cell  
objxlRange.Value = Array

How can I write the Array to a Row (Horizontally)?

如何将数组写入一行（水平）？

Thanks

谢谢

EDIT

编辑

OK Thanks, now it works!

好的，谢谢，现在可以了！

Here is the final working code for all to share!

这是大家分享的最终工作代码！

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        ' Create an Excel file and write ArrayRow as a Row and ArrayCol as a Column
        Dim objApp As Excel.Application
        Dim objBook As Excel._Workbook
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim Rng As Excel.Range
        Dim StartRow, StartCol

        ' Array as a Row
        Dim ArrayRow(0, 3) As String
        ArrayRow(0, 0) = "This"
        ArrayRow(0, 1) = "is"
        ArrayRow(0, 2) = "a"
        ArrayRow(0, 3) = "Row"

        ' Array as a Column
        Dim ArrayCol(3, 0) As String
        ArrayCol(0, 0) = "Now"
        ArrayCol(1, 0) = "it's"
        ArrayCol(2, 0) = "a"
        ArrayCol(3, 0) = "Column"

        ' New instance of Excel and start a new workbook.
        objApp = New Excel.Application()
        objBooks = objApp.Workbooks
        objBook = objBooks.Add
        objSheets = objBook.Worksheets
        objSheet = objSheets(1)

        'Write Array as a Row
        StartRow = 1
        StartCol = 1
        With objSheet
            Rng = .Range(.Cells(StartRow, StartCol), _
                .Cells(UBound(ArrayRow, 1) - LBound(ArrayRow, 1) + StartRow, _
                UBound(ArrayRow, 2) - LBound(ArrayRow, 2) + StartCol))
        End With
        Rng.Value = ArrayRow ' Row

        'Write Array as a Column
        StartRow = 3
        StartCol = 1
        With objSheet
            Rng = .Range(.Cells(StartRow, StartCol), _
                .Cells(UBound(ArrayCol, 1) - LBound(ArrayCol, 1) + StartRow, _
                UBound(ArrayCol, 2) - LBound(ArrayCol, 2) + StartCol))
        End With
        Rng.Value = ArrayCol ' Column

        ' Save      
        objBook.SaveAs("C:\Excel_Range_Test.xls", FileFormat:=56)
        objBook.Close()

    End Sub
End Class

采纳答案by aphoria

It's been awhile, but I think you have to use a multidimensional array to do this.

已经有一段时间了，但我认为你必须使用多维数组来做到这一点。

So, something like this (remember, arrays are 0-based):

所以，像这样（记住，数组是基于 0 的）：

Dim Array(1, 0) As String
Array(0, 0) = "Hello"
Array(1, 0) = "World"

objSheet.Range("C5:C6").Value = Array

EDIT

编辑

To do it as a row instead of a column, flip the dimensions of your array.

要将其作为一行而不是列来执行，请翻转数组的维度。

Dim ArrayRow(0, 1) As String
ArrayRow(0, 0) = "Goodnight"
ArrayRow(0, 1) = "Moon"
objSheet.Range("C1:D1").Value = ArrayRow