将数组写入 Excel 范围 [英] Write Array to Excel Range

问题描述

我目前正在尝试使用以下代码将对象数组中的数据写入 Excel 中的某个范围，其中 objData 只是一个字符串数组:

I'm currently trying to write data from an array of objects to a range in Excel using the following code, where objData is just an array of strings:

private object m = System.Type.Missing;
object[] objData = getDataIWantToWrite();

Range rn_Temp;
rn_Temp = (Range)XlApp.get_Range(RangeName, m);
rn_Temp = rn_Temp.get_Resize(objData.GetUpperBound(), 1);
rn_Temp.value2 = objData;

这几乎有效，问题是范围被填充，但每个单元格都获得 objData 中第一项的值.

This very nearly works, the problem being that the range gets filled but every cell gets the value of the first item in the objData.

反之，即

private object m = System.Type.Missing;
object[] objData = new object[x,y]

Range rn_Temp;
rn_Temp = (Range)XlApp.get_Range(RangeName, m);
rn_Temp = rn_Temp.get_Resize(objData.GetUpperBound(), 1);
objData = (object[])rn_Temp.value2;

将返回一个包含工作表中所有值的数组，所以我不确定为什么读取和赋值的工作方式不同.

would return an array containing all of the values from the worksheet, so I'm not sure why reading and assignment work differently.

有人成功过吗?我目前正在逐个单元地写入数组，但它需要处理大量 (>50,000) 行，因此这非常耗时.

Has anyone ever done this successfully? I'm currently writing the array cell by cell, but it needs to cope with lots (>50,000) of rows and this is therefore very time consuming.

推荐答案

这是我的方法的摘录，它将DataTable(dt 变量)转换为一个数组，然后将该数组写入工作表 (wsh var) 上的 Range.您还可以将 topRow 变量更改为您想要放置字符串数组的任何行.

This is an excerpt from method of mine, which converts a DataTable (the dt variable) into an array and then writes the array into a Range on a worksheet (wsh var). You can also change the topRow variable to whatever row you want the array of strings to be placed at.

object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count; r++)
{
    DataRow dr = dt.Rows[r];
    for (int c = 0; c < dt.Columns.Count; c++)
    {
        arr[r, c] = dr[c];
    }
}
Excel.Range c1 = (Excel.Range)wsh.Cells[topRow, 1];
Excel.Range c2 = (Excel.Range)wsh.Cells[topRow + dt.Rows.Count - 1, dt.Columns.Count];
Excel.Range range = wsh.get_Range(c1, c2);
range.Value = arr;

当然你不需要像我一样使用中间的DataTable，代码摘录只是为了演示如何在一次调用中将数组写入工作表.

Of course you do not need to use an intermediate DataTable like I did, the code excerpt is just to demonstrate how an array can be written to worksheet in single call.

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