C#. microsoft interop excel.

Question

Im trying to insert RoomType array into the excel book. Range of RoomType is From D22 To D25 so problem is that this code only put the firts value In this range. if i insert RoomType.set_Value into the for loop, excel range filling with last array item. can anyone help me?

 Object[,] RoomtypeArray = new object[1, _RoomType.Count];
     for (int i = 0; i < _RoomType.Count; i++)
                {
                    RoomtypeArray[0, i] = _RoomType[i];

                }
 RoomType.set_Value(Type.Missing, RoomtypeArray);

What exactly is _RoomType? Same for RoomType. – MGZero Jul 28 '11 at 13:53 — MGZero, Jul 28 '11 at 13:53
RoomType is range and _RoomType is List – Irakli Lekishvili Jul 28 '11 at 14:04 — Irakli Lekishvili, Jul 28 '11 at 14:04

score 2 · Accepted Answer · edited May 23 '17 at 09:58

This is what you need:

//Microsoft.Office.Interop.Excel.Range RoomType;
//List<double> _RoomType;

object[,] roomTypeArray = Array.CreateInstance(
    typeof(object),
    new int[] { 1, _RoomType.Count},
    new int[] { 1, 1 });

for (int i = 0; i < _RoomType.Count; i++)
{
    roomTypeArray[1, i + 1] = _RoomType[i];
}

RoomType.Value2 = roomTypeArray;

because setting an array for a range requires 1-based indexes instead of 0-based which are used with the new statment in C#.

(look also in the accepted answer of How can I quickly up-cast object[,] into double[,]? to find a neat trick going between object[,] and double[,] for use in Excel Inerop).

C#. microsoft interop excel.

1 Answers1