How to put array into excel range

Question

I know how to write single cell into excel but when im trying it on array excel sheet is filling with only last value

this is my range

 Excel.Range ServiceName = (Excel.Range)_sheet.get_Range(_sheet.Cells[38, "B"] as Excel.Range, _sheet.Cells[45, "B"] as Excel.Range);

_ServiceName is List which contains 1,2,3,4,5,6

for (int i = 0; i < _ServiceName.Count; i++)
            {
                ServiceNameArray[0, i] = _ServiceName[i];

            }

this i my trying to write into excel but as i said it there is only last item (6) in excel book

 for (int i = 0; i < _ServiceName.Count; i++)
            {
                ServiceName.set_Value(Type.Missing, ServiceNameArray[0,i]);
            }

does anyone have an idea?

Community · Accepted Answer · 2017-05-23 12:21:12Z

2

Davide Piras is right. And you're doing a few other strange things there, I can elaborate by request.

For now I just want to point out that you can directly assign the .Value property of a Range to an array:

ServiceName.Value2 = _ServiceName.toArray();

This is much, much faster for bigger amounts of data.

(Side note: If you want to do the same with Formulas, for some strange reason you have to take an extra step (doubling the time):

range.Formula = array;
range.Formula = range.Formula;

unless there is a better way I don't know about yet.)

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Mar 10, 2012 at 16:25

Aaron Thoma

4,0461 gold badge40 silver badges38 bronze badges

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

Comments

Davide Piras · Accepted Answer · 2011-08-28 22:43:14Z

1

I see you looping on the ServiceName array to get all values one after the other but not see you changing the focused cell inside the cellrange at every loop iteration. Of course, I would say, you see only the last value, because you are writing all values one over the other always in the same place.

answered Aug 28, 2011 at 22:43

Davide Piras

44.7k10 gold badges103 silver badges150 bronze badges

Collectives™ on Stack Overflow

How to put array into excel range

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related