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

问题描述

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

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.

有没有人成功地做到了这一点？我目前正在写由电池阵列单元，但它需要应付的行手（> 50000），因此这是非常耗时的。

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）。您还可以更改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;

当然，你不需要使用中间数据表像我一样，在code摘录只是为了展示一个数组如何被写入单个调用到工作表。

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屋！