在VB.Net中，如何将数组写入Excel [英] In VB.Net, how to write an Array to Excel

问题描述

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

我尝试了几种方法，它不会写任何东西或仅写入Array的第一个值。

任何帮助将不胜感激。

谢谢。

 导入Excel = Microsoft.Office.Interop.Excel 
 ... 
 Dim Array（2）As String 
 Array（1）=Hello
 Array（2）=World
 ... 
'尝试了几种方式一个一个... 
 objSheet.Cells（C5：C6）= Array 
 objSheet.Cells（C5：C6）。Value = Array 
 objSheet.Range（ C5 = C6）Value = Array 
 objSheet.Range（C5）。Value = Array 
  

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

  Dim Array（2，0）As String 
 Array（0，0）=Hello
数组（1，0）=世界
数组（2，0）=一
 ... 
 
'Test 1 
 objSheet.Cells（C5：C6）。Value = Array'I获取无效参数（异常HRESULT：0x80070057（E_INVALIDARG））
 
'测试2 
 objxlRange = objSheet.Range（C5：C7）'将数组内容作为列（垂直）写入
 objxlRange.Value = Array 
 
'测试3 
 objxlRange = objSheet.Range（C5：E5）'只写入每个单元格中的第一个条目Hello
 objxlRange.Value = Array 
  

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

谢谢

编辑

确定谢谢， ！

这是所有人共享的最终工作代码！

 导入Excel = Microsoft.Office.Interop.Excel 
 
公共类Form1 
 
私有子按钮1_Click（发件人作为对象，作为EventArgs）处理Button1.Click 
 
'创建一个Excel文件，并将ArrayRow作为列和ArrayCol作为列
 Dim objApp作为Excel.Application 
 Dim objBook作为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 
 
'数组为行
 Dim ArrayRow（0，3）As String 
 ArrayRow（0，0）=This
 ArrayRow（0，1）=is
 ArrayRow（0,2）=a
 ArrayRow（0，3）=Row
 
'数组作为列
 Dim ArrayCol（3，0） As String 
 ArrayCol（0，0）=Now
 ArrayCol（1，0）=它的
 ArrayCol（2，0）=a
 ArrayCol 3，0）=列
 
'新的Excel实例并启动新的工作簿。 
 objApp = New Excel.Application（）
 objBooks = objApp.Workbooks 
 objBook = objBooks.Add 
 objSheets = objBook.Worksheets 
 objSheet = objSheets（1）
 
'将数组写入行
 StartRow = 1 
 StartCol = 1 
使用objSheet 
 Rng = .Range（.Cells（StartRow，StartCol） _ 
 .Cells（UBound（ArrayRow，1） -  LBound（ArrayRow，1）+ StartRow，_ 
 UBound（ArrayRow，2） -  LBound（ArrayRow，2）+ StartCol））
结束
 Rng.Value = ArrayRow'Row 
 
'将数组写入列
 StartRow = 3 
 StartCol = 1 
带objSheet 
 Rng = .Range（.Cells（StartRow，StartCol），_ 
 .Cells（UBound（ArrayCol，1） -  LBound（ArrayCol，1）+ StartRow，_ 
 UBound（ArrayCol，2） LBound（ArrayCol，2）+ StartCol））
结束
 Rng.Value = ArrayCol'列
 
'保存
 objBook.SaveAs（C：\Excel_Range_Test.xls，FileFormat：= 56）
 objBook.Close（）
 
 End Sub 
结束类
  

解决方案

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

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

  Dim Array（1，0）As String 
数组（0，0）=你好
 Array（1，0）=World
 
 objSheet.Range（C5：C6）。Value = Array 
  

EDIT

要做到这一行而不是列，请翻转数组的维度。

  Dim ArrayRow（0，1）As String 
 ArrayRow（0，0）=Goodnight
 ArrayRow（0， 1）=Moon
 objSheet.Range（C1：D1）。Value = ArrayRow 
  

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?

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

解决方案

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):

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

这篇关于在VB.Net中，如何将数组写入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！