将数据数组传输到工作表上的范围 [英] transfer an array of data to a range on a worksheet

问题描述

我想将VB数组中的数据复制到Excel中。使用该方法逐个单元传输数据，传输10,000多个记录花费的时间太长。有一种更快捷的方法就是传输数据数组。我有语法等但似乎无法将其显示在我的Excel电子表格上。



数组如下所示



'创建一个包含3列和4行的数组

DataArray（0,0）=帐户

DataArray（1,0）=史密斯

DataArray（2,0）=John

DataArray（0,1）=账户

DataArray（1,1 ）=绿色

DataArray（2,1）=Jill

DataArray（0,2）=账户

DataArray （1,2）=布朗

DataArray（2,2）=杰夫

DataArray（0,3）=HR

DataArray（1,3）=Neil

DataArray（2,3）=Anne





代码如下



'在第1行的工作表中添加标题。

oSheet = oBook.Worksheets（1 ）

oSheet.Range（A1）。Value =Department

oSheet.Range（B1）。Value =Surname
$ b $博Sheet.Range（C1）。Value =Forename



'从数组A2开始将数组传输到工作表。

oSheet.Range（A2）。调整大小（4,3）.Value = DataArray



我在Excel电子表格中得到的结果是



部门姓氏姓名

账户账户账户等



我知道哪里出错了？

I want to copy data from a VB array into Excel. Using the method transfer data cell by cell, transferring 10,000 plus records was taking too long. There is a quicker way and that is transferring the array of data. I have the syntax etc but cannot seem to get it displayed on my Excel spreadsheet.

The array is like as follows

'Create an array with 3 columns and 4 rows
DataArray(0, 0) = "Accounts"
DataArray(1,0) = "Smith"
DataArray(2,0) = "John"
DataArray(0, 1) = "Accounts"
DataArray(1,1) = "Green"
DataArray(2,1) = "Jill"
DataArray(0, 2) = "Accounts"
DataArray(1,2) = "Brown"
DataArray(2,2) = "Jeff"
DataArray(0, 3) = "HR"
DataArray(1,3) = "Neil"
DataArray(2,3) = "Anne"


Code is as follows

'Add headers to the worksheet on row 1.
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Department"
oSheet.Range("B1").Value = "Surname"
oSheet.Range("C1").Value = "Forename"

'Transfer the array to the worksheet starting at cell A2.
oSheet.Range("A2").Resize(4, 3).Value = DataArray

The result I get in the Excel spreadsheet is

Department Surname Forename
Accounts Accounts Accounts etc

Any idea where I am going wrong?

推荐答案

关于此，这与您当前的查询几乎相同（ vb.net copy array to excel电子表格

[ ^ ]），你似乎在苦苦寻求多维数组。将多维数组的索引视为行/列。所以x（0,0）指的是第一行，第一列; x（0,1）表示第一行，第二列，x（1,0）表示第二行。因此，对于上述数据（以及当前查询的相同数据），数据在数组中正确显示。如果您希望以不同方式显示它，请以不同方式将其加载到数组中。这是一个简单的演示循环 - 选择你想要的任何正数作为NumRows和NumColumns：

Regarding this, which is pretty much the same as your current query (vb.net copy array to excel spreadsheet
[^]), you seem to be struggling with multi-dimensional arrays. Think of the indices of your multi-dimensiona array as row/column. So x(0,0) refers to the first row, first column; x(0,1) refers to the first row, second column, and x(1,0) starts the second row. So for the above data (and the same for your current query), the data is displaying correctly as you have it in the array. If you want it to display differently, load it into the array differently. Here's a simple loop to demonstrate--select whatever positive number you want as NumRows and NumColumns:

Dim MyArray(NumRows - 1, NumColumns - 1) as String
For row as Integer = 0 to NumRows - 1
    For column as Integer = 0 to NumColumns - 1
        MyArray(row, col) = row & "_" & column
    Next column
Next row

尝试

http://support.microsoft.com/kb/ 306022 [ ^ ]

http://www.made4dotnet.com/pages /tabid/141/aid/19/Export-Array-to-Excel-worksheet.aspx [ ^ ]

这篇关于将数据数组传输到工作表上的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！