vb.net复制数组到excel电子表格 [英] vb.net copy array to excel spreadsheet

问题描述

我在VB中创建了一个数组，并希望将整个数组复制到excel电子表格中，而不是一次一行。这将使该过程更快。



VB端的数组代码如下所示

I have created an array in VB and want to copy the whole array to an excel spreadsheet rather than doing it a line at a time. This will make the process much quicker.

The array code at VB end is like as follows

xlWorkSheet.Range("A2").Resize(x, y).Value = MyArray

excel电子表格有标题



姓名，部门，薪水



而不是被视为

The excel spreadsheet has headings

Name, Dept, Salary

Rather than being dispalyed as

Name     Dept        Salary
Smith    Accounts    10000
Green    Sales       14500 
Brown    Accounts    12000

它正在获得显示为

It is getting displayed as

Name       Dept   Salary
Smith      Green  Brown
Accounts   Sales  Accounts
10000      14500  12000

必须有一个简单的解决方案吗？

There must be a simple solution?

推荐答案

假设A2，你应该有什么工作是你的起始单元格，x和y是你的数组维度的长度附件。更一般地说，

What you've got should work, assuming that A2 is your starting cell, and that x and y are the lengths of your array dimensions. More generically,

xlWorkSheet.Range("A2").Resize(MyArray.GetUpperBound(0) + 1, MyArray.GetUpperBound(1) + 1).Value = MyArray

请注意，通过数组填充时，单元格大小存在（或者是）未经广泛的限制。过去，如果您的数据超过912个字符，则会留空。处理这种情况的方法是检查数组中的这些实例并用一些虚拟值替换它们，存储索引，然后在加载工作表后，返回并替换虚拟值。那是Excel的旧版本（可能是2007年？），所以现在可以修复。



查看你的结果，我猜你的数组加载错了。在将其放入工作表之前，请先查看数组中的数据。

Be aware that there is (or was) an unadvertised limit on the cell size when populating via an array. Used to be that if your data was more than 912 characters, it'd get left blank. Way to handle that was to check your array for such instances and replace them with some dummy value, storing the indices, and then after you've loaded the sheet, go back through and replace the dummy values. That was an older version of Excel (maybe 2007?), so that may be fixed now.

Looking at your results, I'm guessing your array is loaded wrong. Take a look at the data in the array before your put it in the sheet.

这篇关于vb.net复制数组到excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！