C# 将数组写入 Excel 范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/536636/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Write Array to Excel Range
提问by Jon Artus
I'm currently trying to write data from an array of objects to a range in Excel using the following code, where objDatais just an array of strings:
我目前正在尝试使用以下代码将对象数组中的数据写入 Excel 中的某个范围，其中objData只是一个字符串数组：
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;
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.
这几乎有效，问题是范围被填充，但每个单元格都获得objData.
The inverse works, i.e.
逆向工作，即
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.
将返回一个包含工作表中所有值的数组，所以我不确定为什么读取和赋值的工作方式不同。
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.
有没有人成功地做到了这一点？我目前正在逐个单元地写入数组，但它需要处理大量（> 50,000）行，因此这非常耗时。
采纳答案by petr k.
This is an excerpt from method of mine, which converts a DataTable(the dtvariable) into an array and then writes the array into a Rangeon a worksheet (wshvar). You can also change the topRowvariable to whatever row you want the array of strings to be placed at.
这是我的方法的摘录，该方法将 a DataTable（dt变量）转换为数组，然后将数组写入Range工作表 ( wshvar)上的 a 。您还可以将topRow变量更改为您想要放置字符串数组的任何行。
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;
Of course you do not need to use an intermediate DataTablelike I did, the code excerpt is just to demonstrate how an array can be written to worksheet in single call.
当然，您不需要DataTable像我一样使用中间体，代码摘录只是为了演示如何在单个调用中将数组写入工作表。
回答by Galwegian
You could put your data into a recordset and use Excel's CopyFromRecordset Method- it's much faster than populating cell-by-cell.
您可以将数据放入记录集中并使用Excel 的 CopyFromRecordset 方法- 它比逐个单元格填充要快得多。
You can create a recordset from a dataset using this code. You will have to do some trials to see if using this method is faster than what you are currently doing.
您可以使用此代码从数据集创建记录集。您将不得不进行一些试验，以查看使用此方法是否比您目前正在执行的操作更快。
回答by Jon Artus
Thanks for the pointers guys - the Value vs Value2 argument got me a different set of search results which helped me realise what the answer is. Incidentally, the Value property is a parametrized property, which must be accessed through an accessor in C#. These are called get_Value and set_Value, and take an optional enum value. If anyone's interested, this explains it nicely.
感谢您的指点 - Value vs Value2 参数为我提供了一组不同的搜索结果，这帮助我意识到答案是什么。顺便说一下，Value 属性是一个参数化的属性，必须通过 C# 中的访问器来访问。这些称为 get_Value 和 set_Value，并采用可选的枚举值。如果有人感兴趣，这很好地解释了它。
It's possible to make the assignment via the Value2 property however, which is preferable as the interop documentation recommends against the use use of the get_Value and set_Value methods, for reasons beyond my understanding.
但是，可以通过 Value2 属性进行分配，因为互操作文档建议不要使用 get_Value 和 set_Value 方法，原因超出了我的理解。
The key seems to be the dimension of the array of objects. For the call to work the array must be declared as two-dimensional, even if you're only assigning one-dimensional data.
关键似乎是对象数组的维度。为了调用工作，必须将数组声明为二维数组，即使您只分配一维数据。
I declared my data array as an object[NumberofRows,1]and the assignment call worked.
我将我的数据数组声明为 anobject[NumberofRows,1]并且赋值调用有效。
回答by Jahmic
For some reason, converting to a 2 dimensional array didn't work for me. But the following approach did:
出于某种原因，转换为二维数组对我不起作用。但以下方法确实做到了：
public void SetRow(Range range, string[] data)
{
range.get_Resize(1, data.Length).Value2 = data;
}
回答by Georg
The kind of array definition seems the key: In my case it is a one dimension array of 17 items which have to convert to a two dimension array
数组定义的类型似乎是关键：在我的情况下，它是一个包含 17 个项目的一维数组，必须转换为二维数组
Defintion for columns: object[,] Array = new object[17, 1];
列的定义：object[,] Array = new object[17, 1];
Defintion for rows object[,] Array= new object[1,17];
行的定义 object[,] Array= new object[1,17];
The code for value2 is in both cases the same Excel.Range cell = activeWorksheet.get_Range(Range); cell.Value2 = Array;
value2 的代码在两种情况下都是相同的 Excel.Range cell = activeWorksheet.get_Range(Range); cell.Value2 = 数组；
LG Georg
LG乔治
回答by Crazy Cat
In my case, the program queries the database which returns a DataGridView. I then copy that to an array. I get the size of the just created array and then write the array to an Excel spreadsheet. This code outputs over 5000 lines of data in about two seconds.
就我而言，程序查询返回 DataGridView 的数据库。然后我将它复制到一个数组中。我得到刚刚创建的数组的大小，然后将数组写入 Excel 电子表格。这段代码在大约两秒钟内输出了 5000 多行数据。
//private System.Windows.Forms.DataGridView dgvResults;
dgvResults.DataSource = DB.getReport();
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
try
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
var dgArray = new object[dgvResults.RowCount, dgvResults.ColumnCount+1];
foreach (DataGridViewRow i in dgvResults.Rows)
{
if (i.IsNewRow) continue;
foreach (DataGridViewCell j in i.Cells)
{
dgArray[j.RowIndex, j.ColumnIndex] = j.Value.ToString();
}
}
Microsoft.Office.Interop.Excel.Range chartRange;
int rowCount = dgArray.GetLength(0);
int columnCount = dgArray.GetLength(1);
chartRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[2, 1]; //I have header info on row 1, so start row 2
chartRange = chartRange.get_Resize(rowCount, columnCount);
chartRange.set_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault, dgArray);
oXL.Visible = false;
oXL.UserControl = false;
string outputFile = "Output_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
oWB.SaveAs("c:\temp\"+outputFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
}
catch (Exception ex)
{
//...
}
回答by Gilles5678
when you want to write a 1D Array in a Excel sheet you have to transpose itand you don't have to create a 2D array with 1 column ([n, 1])as I read above! Here is a example of code :
当您想在 Excel 工作表中写入一维数组时，您必须对其进行转置，并且您不必像我上面读到的那样创建具有 1 列 ([n, 1]) 的二维数组！这是一个代码示例：
wSheet.Cells(RowIndex, colIndex).Resize(RowsCount, ).Value = _excel.Application.transpose(My1DArray)
Have a good day, Gilles
祝你有美好的一天，吉尔斯
回答by D.L.MAN
add ExcelUtilityclass to your project and enjoy it.
将ExcelUtility类添加到您的项目中并享受它。
ExcelUtility.csFile content:
ExcelUtility.cs文件内容：
using System;
using Microsoft.Office.Interop.Excel;
static class ExcelUtility
{
public static void WriteArray<T>(this _Worksheet sheet, int startRow, int startColumn, T[,] array)
{
var row = array.GetLength(0);
var col = array.GetLength(1);
Range c1 = (Range) sheet.Cells[startRow, startColumn];
Range c2 = (Range) sheet.Cells[startRow + row - 1, startColumn + col - 1];
Range range = sheet.Range[c1, c2];
range.Value = array;
}
public static bool SaveToExcel<T>(T[,] data, string path)
{
try
{
//Start Excel and get Application object.
var oXl = new Application {Visible = false};
//Get a new workbook.
var oWb = (_Workbook) (oXl.Workbooks.Add(""));
var oSheet = (_Worksheet) oWb.ActiveSheet;
//oSheet.WriteArray(1, 1, bufferData1);
oSheet.WriteArray(1, 1, data);
oXl.Visible = false;
oXl.UserControl = false;
oWb.SaveAs(path, XlFileFormat.xlWorkbookDefault, Type.Missing,
Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWb.Close(false);
oXl.Quit();
}
catch (Exception e)
{
return false;
}
return true;
}
}
usage :
用法 ：
var data = new[,]
{
{11, 12, 13, 14, 15, 16, 17, 18, 19, 20},
{21, 22, 23, 24, 25, 26, 27, 28, 29, 30},
{31, 32, 33, 34, 35, 36, 37, 38, 39, 40}
};
ExcelUtility.SaveToExcel(data, "test.xlsx");
Best Regards!
此致！
