c# interop excel: Write 1D array to excel range

Question

I want to copy an 1D array to a column range in excel. I'm using interop for this purpose.

I have already tried these things:

range.get_Resize(Ary.Length, 1).Value2 = Ary;
range.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, Ary);
and as simple as range.Value = Ary;

I have tried using even range.value2, but these things copy the very first index value in the array to the entire range.

So say suppose, if there are 200 rows in the range and the array contains integers 101-300, than only 101 is copied throughout the range with the above tried methods.

Can anyone please help me with this? It would be more helpful if someone can explain me this strange behavior! Thanks in advance.

Note: I know I can do it through a for loop, but that takes time. I would surely like to know a method which takes less than a minute to iterate a million rows.

In my old (Visual Basic) code, I can set Value directly. What type is your ´Ary´. — GvS
– GvS, Commented Aug 16, 2019 at 7:55
Its a string array. But the problem is, I have tried this even with int array and object array with no luck. I don't understand why it isn't working. — StackUseR
– StackUseR, Commented Aug 16, 2019 at 7:56
Could you try making range a 'dynamic' variable? Also: When the array is too large, the set of 'Value' throws an exception, and I set the data one by one. — GvS
– GvS, Commented Aug 16, 2019 at 8:09
As you suggested, I tried with dynamic variable but didn't work. Still the same behavior. — StackUseR
– StackUseR, Commented Aug 16, 2019 at 8:17
Latest suggestion: Try it in Visual Basic. Yes I know, but in old&dark ages of .Net 1.0 it was unbeatable for interop, specially with late binding. The world is a better place now. We've added a .vb project to our solution that is otherwise in C#. — GvS
– GvS, Commented Aug 16, 2019 at 8:36

StackUseR · Accepted Answer · 2019-08-16 13:46:38Z

1

I seriously don't know what exactly is wrong with the above methods. But I found the solution:

Excel.Range range = sheetSource.UsedRange.Columns[columnIndx];
range.Value = application.WorksheetFunction.Transpose(Ary);

answered Aug 16, 2019 at 13:46

StackUseR

9581 gold badge12 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

c# interop excel: Write 1D array to excel range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related