In VB.Net, wie schreibt man ein Array in Excel

Um zu beschleunigen, schreiben von Werten in Excel, in VB.Net ist es möglich, zu schreiben, die ein Array mit einer Zeile, anstatt einen Wert zu einer Zelle?

Ich habe versucht, mehrere Möglichkeiten, entweder es schreibt nichts, oder schreibt nur den ersten Wert des Arrays.

Jegliche Hilfe würde sehr geschätzt werden.

Dank.

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

Nach der ersten Antwort, hier ist der überarbeitete 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

Wie kann ich schreiben, dass das Array eine Zeile (Horizontal)?

Dank

BEARBEITEN

OK Danke, jetzt funktioniert es!

Hier ist der Letzte funktionierende code für alle zu teilen!

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

InformationsquelleAutor user3469022 | 2014-03-27