C# Interop write array of arrays to worksheet

Question

I'm trying to write a 2d array to worksheet and I'm getting the following error:

Specified array was not of the expected type.

This is my code:

            string[][] allEvars = new string[evars.Count][];

            for (var i = 0; i < evars.Count; i++) {
                var evarsList = new List<string>();
                evarsList.Add(evars[i].id.Value);
                evarsList.Add(evars[i].name.Value);
                if (evars[i].enabled != null)
                    evarsList.Add(evars[i].enabled.Value.ToString());
                else
                    evarsList.Add("");
                if (evars[i].description != null)
                    evarsList.Add(evars[i].description.Value);
                else
                    evarsList.Add("");
                string[] evarsArray = evarsList.ToArray();
                //var range = (Excel.Range)wsEvars.Range[wsEvars.Cells[i + 5, 2], wsEvars.Cells[i + 5, evarsArray.Length]];
                //range.Value = evarsArray;
                allEvars[i] = evarsList.ToArray();
            }
            var range = (Excel.Range)wsEvars.Range[wsEvars.Cells[5, 2], wsEvars.Cells[allEvars.Length, 3]];
            range.Value2 = allEvars; // This is the line with the exception

The 2 commented lines from my code add the array to the worksheet one by one and I'm trying to add them all at once to save some time.

score 3 · Accepted Answer · edited May 23 '17 at 10:30

You can cast array of arrays to 2d array. Use Jon Skeet method from this answer

static T[,] CreateRectangularArray<T>(T[][] arrays)
    {
        // TODO: Validation and special-casing for arrays.Count == 0
        int minorLength = arrays[0].Length;
        T[,] ret = new T[arrays.Length, minorLength];
        for (int i = 0; i < arrays.Length; i++)
        {
            var array = arrays[i];
            if (array.Length != minorLength)
            {
                throw new ArgumentException
                    ("All arrays must be the same length");
            }
            for (int j = 0; j < minorLength; j++)
            {
                ret[i, j] = array[j];
            }
        }
        return ret;
    }

In your code:

        string[][] allEvars = new string[evars.Count][];

        for (var i = 0; i < evars.Count; i++) {
            var evarsList = new List<string>();
            evarsList.Add(evars[i].id.Value);
            evarsList.Add(evars[i].name.Value);
            if (evars[i].enabled != null)
                evarsList.Add(evars[i].enabled.Value.ToString());
            else
                evarsList.Add("");
            if (evars[i].description != null)
                evarsList.Add(evars[i].description.Value);
            else
                evarsList.Add("");
            string[] evarsArray = evarsList.ToArray();
            //var range = (Excel.Range)wsEvars.Range[wsEvars.Cells[i + 5, 2], wsEvars.Cells[i + 5, evarsArray.Length]];
            //range.Value = evarsArray;
            allEvars[i] = evarsList.ToArray();
        }
        var range = (Excel.Range)wsEvars.Range[wsEvars.Cells[5, 2], wsEvars.Cells[allEvars.Length, 3]];
        string[,] newallEvars = CreateRectangularArray<string>(allEvars); //added row
        range.Value2 = newallEvars; // changed row

C# Interop write array of arrays to worksheet

1 Answers1