Write Array to Excel Range

Question

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;

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.

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.

The selected answer doesn't make it immediately obvious, so if someone else ever has this problem, in short, the solution is that you need to assign a 2D array (`object[,]`) even though your data could fit on just one dimension. — zneak, May 17 '12 at 15:19

score 99 · Accepted Answer · edited Oct 04 '19 at 06:50

99

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;

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.

edited Oct 04 '19 at 06:50

AbdelAziz AbdelLatef

3,650
6
24
52

answered Feb 11 '09 at 13:15

petr k.

8,040
7
41
52

Interesting - that's exactly what I want to do, but I can't access the Value property of the range object - I can only access Value2, and I think that may be the problem. Any idea how I can access the Value property? What interops are you using? – Jon Artus Feb 11 '09 at 13:19
Imported from the Excel's type library (happens to be Office 2003 in my case). I.e., I am not using the primary interop assemblies. I just added the reference to Excel COM tlb in VS. Now I cannot remember the exact reason for not using the PIAs, though. – petr k. Feb 11 '09 at 13:27
2

I use the range.set_Value(Missing.Value, objectArray ); You need to make sure that the array is the same size as the range. – kͩeͣmͮpͥ ͩ Feb 11 '09 at 17:46
This depends on the Office version for which the interops are imported. I was using Office 2000 (not 2003 as I stated in the previous comment) in which the Value property has no optional parameters. It has one optional parameter in TLBs from version 2003 up, thus the property is translated into a – petr k. Feb 11 '09 at 18:44
method, since properties cannot have parameters in C#. – petr k. Feb 11 '09 at 18:45
In C# 4.0 there are indexed properties, see http://blogs.msdn.com/kirillosenkov/archive/2009/10/20/indexed-properties-in-c-4-0.aspx – Mikhail Poda Feb 15 '10 at 12:01
Any idea why this throws a COMException when trying to copy a uniqueidentifier column when using an object array, but a string array seems to handle it fine? – tbone Jun 09 '15 at 06:53
I had to use Excel.Range range = wsh.Range[c1, c2]; range.Value2 = arr; – Paul Dolphin Jul 04 '17 at 15:18
Great this works but I can able to write the data using above logic but if I apply the filter on the sheet and try to write the data using above logic than values of first columns data writing in all the columns, result not getting as expected. – Shubham Srivastava Aug 10 '17 at 12:05

score 11 · Answer 2 · answered Feb 11 '09 at 14:32

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.

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.

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.

thanks, it looks like given a 1D array, it will always write it horiztonally (even with a vertical range, in which case it writes the first element repeatedly). to get an array to write vertically, you must do as you said and make it an `[n, 1]` array. many thanks because I probably would never have discovered this myself. — Dave Cousineau, Apr 13 '17 at 23:58

score 5 · Answer 3 · answered Jul 13 '17 at 20:38

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.

//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)
{
    //...
}

score 4 · Answer 4 · answered Aug 23 '17 at 08:51

when you want to write a 1D Array in a Excel sheet you have to transpose it and you don't have to create a 2D array with 1 column ([n, 1]) as I read above! Here is a example of code :

 wSheet.Cells(RowIndex, colIndex).Resize(RowsCount, ).Value = _excel.Application.transpose(My1DArray)

Have a good day, Gilles

Galwegian · Answer 5 · 2009-02-11T14:14:20.090

4

You could put your data into a recordset and use Excel's CopyFromRecordset Method - it's much faster than populating cell-by-cell.

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.

edited Feb 11 '09 at 14:14

answered Feb 11 '09 at 13:01

Galwegian

41,475
16
112
158

This may be a silly question, but where can I find a Recordset class in C#? Or do you happen to know if there's another class I can pass in which will work? Many thanks! – Jon Artus Feb 11 '09 at 13:12
1

Add reference to assembly ADODB version 7.0.3300.0. using ADODB; ADODB has type "Recordset" – Kartikay Tripathi Sep 05 '13 at 10:32

score 2 · Answer 6 · answered Oct 25 '19 at 21:03

add ExcelUtility class to your project and enjoy it.

ExcelUtility.cs File content:

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!

score 1 · Answer 7 · answered Nov 02 '15 at 12:57

1

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;
}

answered Nov 02 '15 at 12:57

James John McGuire 'Jahmic'

11,728
11
67
78

score 1 · Answer 8 · answered Dec 09 '16 at 08:14

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

Defintion for columns: object[,] Array = new object[17, 1];

Defintion for rows 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;

LG Georg

Write Array to Excel Range

8 Answers8

Linked

Related