Excel: Values from Ranges With Multiple Areas

Question

I would like to get the values of a non contiguous, multi area range in excel using C#. I have seen another SO question that says I can do something like this:

obj[,] data = sheet.get_Range("B4:K4,B5:K5").get_Value();

However, when I examine the result I see that I only get the data from the first area: "B4:K4".

Testing further, I found that if I request the data in the following way:

obj[,] data = sheet.get_Range( "B4:K4","B5:K5").get_Value();

I get the data for both areas...

So, my question is, is there a way to programmatically combine area addresses such as "B4:K4,B5:K5" in order to get all of the data to which they refer?

Thanks

Answer 1

The solution I came up with isn't as elegant as I would have liked but it does the trick nonetheless:

public List<List<object>> 
GetNonContiguousRowValue(Excel.Worksheet ws, string noncontiguous_address)
{
    var addresses = noncontiguous_address.Split(','); // e.g. "A1:D1,A4:D4"
    var row_data = new List<List<object>>();

    // Get the value of one row at a time:
    foreach (var addr in addresses)
    {
        object[,] arr = ws.get_Range(addr).Value;
        List<object> row = arr.Cast<object>)
                              .Take(arr.GetLength(dimension:1))
                              .ToList<object>();
        row_data.Add(row);
    }
    return row_data;
}

Hope this helps someone else...