How can I access the values from `Series.Values` (Excel Chart)

Question

I am using the Microsoft.Office.Interop.Excel namespace and I am creating a chart. At a certain moment, I want to retrieve the values of a certain series. On MSDN it says that a Series object has a property Values. This returns either a Range object or an array of values, I assume an object[]. In my code I have the following statement:

Series series = (Series)chart.SeriesCollection(i);
object[] values = (object[])series.Values;

I get an InvalidCastException with the message:Unable to cast object of type 'System.Object[*]' to type 'System.Object[]'.

When I debug, using Visual Studio 2008, I can inspect the type of series.Values and it says object{object[1..7]}. This means (as I understand) that it is declared as an object but its actual type is object[1..7]. But object[1..7] is not really a type I can cast to and neither is object[*].

I suspect (or guess) that it might have something to do with the fact that the array starts at 1 instead of 0 (probably because of VB). I didn't even know you could define a 1 based array in c#...

Thanks in advance.

Answer 1

Even tough it might seem weird in C# to create non-zero index based arrays it is actually possible:

var array = Array.CreateInstance(
    typeof(object), 
    new int[] { 7 }, 
    new int[] { 1 });

In your case I think you should be able to cast to an array and enumerate:

foreach (object item in (Array)series.Values)
{
}

And there's an interesting article explaining the saga around this types of arrays in the CLR.

Answer 2

Actually why even define the array before you create it. since you are getting the result back from .Values property.

This should work...

Series series = (Series)chart.SeriesCollection(i); 
object[,] values = (object[,])series.Values; 

Darin is correct it is 1 based, but it is also not a normal 2 dimensional array. Range and other Excel objects use a 1 based "jagged" (i think this is the correct description) array since any range object is not necessarily contigiously aligned.

Using Array class as in Darin's suggestion will work since it accepts any form of object array. Its probably easier to work with using his example if you just need to enumerate the items.

Answer 3

I still received the problem with casting using the solution here - (could not cast from Object[*] to Object[]). I was attempting to change the fill colour on some bar charts in Excel if they were negative (obviously not dynamic).

        //from a call to NewSeries()
        Range xlRange = xlSheetDataSummary.get_Range("B1:B29");
        xlSeries.XValues = xlSheetDataSummary.get_Range("A1:A29");
        xlSeries.Values = xlRange;

        int p = 1; //not 0
        foreach (Range cell in xlRange.Cells)
        {
            if (cell.Value < 0)
            {
                Microsoft.Office.Interop.Excel.Point po = xlSeries.Points(p);
                po.Format.Fill.ForeColor.SchemeColor = 10; //red
            }
            p++;
        }