Excel Get_Range with multiple areas

Question

I'm trying to get a range from Excel, which has multiple areas specified, essentially I've got...

int StartColumn
int EndColumn
int[] ColumnsToSkip

When you combine these it's possible to produce a range with non-contiguous areas. Unfortunately I can't quite figure out the call to get this... MSDN isn't very useful...

WorkSheet sheet;

sheet.get_Range( what goes in here??? );

Anyone provide any help? Cheers.

Cally

Answer 1

A very simple solution is to specify different areas in comma-separated form:

sheet.get_Range( "A1:B1,E1:G1");

For programmatic range combinations, there are also the Union and Intersection methods of the ExcelApplication object. Those are a little bit clumsy to use in C# because of many optional parameters. See here

http://codeidol.com/csharp/c-sharp-in-office/Working-with-Excel-Objects/Working-with-the-Range-Object/

for examples.

EDIT: some additional hints:

In your case, you first should transform the "ColumnsToSkip" in "ColumnsToKeep", since that is what you will need for any kind of cell union. Here is a Linq solution:

int[] ColumnsToKeep = Enumerable.Range(StartColumn, EndColumn -StartColumn + 1)
                      .Except(ColumnsToSkip)
                      .ToArray();

Then, you can create something along the lines of this example:

   Excel.Range totalRange = null;
   foreach(int col in ColumnsToKeep)
   {
        totalRange = Union(excelApp,totalRange,(Excel.Range)sh.Cells[row, col]);
   }

where "Union" is defined, for example, like this:

    static Excel.Range Union(Excel.Application app, Excel.Range r1, Excel.Range r2)
    {
        if (r1 == null && r2 == null)
            return null;
        if (r1 == null)
            return r2;
        if (r2 == null)
            return r1;
        return  app.Union(r1, r2,
            null, null, null, null, null, null,
            null, null, null, null, null, null,
            null, null, null, null, null, null,
            null, null, null, null, null, null,
            null, null, null, null);
    }

Raby

It's a bit more awkward, as I'm dealing with indicies, but if it's the only way.. – Cally

@Ian: see my elaborated answer above. – Raby

Not tried it yet but looks like that'll do the job. Just about to give it a whirl. Gotta love the intelli-sense for that app.Union method, nice to at last have optional params in C#! – Cally

second parameter of Enumerable.Range is count : int[] ColumnsToKeep = Enumerable.Range(StartColumn, EndColumn - StartColumn + 1).Except(ColumnsToSkip).ToArray(); – Campania

Answer 2

Try this:

using Excel = Microsoft.Office.Interop.Excel;

1. Collect your ranges into an array:

Excel.Range[] ranges = new Excel.Range[] {yourRange1, yourRange2, ... };

2. Create string-range with ranges addresses and get multi-range from it:

string multiRangeStr = "";
foreach (Excel.Range range in ranges)
{
    string address = range.Address[true, true, Excel.XlReferenceStyle.xlA1];
    multiRangeStr += (multiRangeStr == "" ? "" : ";") + address;
}
//output: multiRangeStr: "A1:A3;B1:B3"

Excel.Range multiRange = wsheet.Range(multiRangeStr);

Presumptuous

Congrats on your fist post! – Floccose

Answer 3

Another way to work with non contiguous ranges is to just combine them together within Excel using a named range, e.g.:

=CHOOSE({1;2;3},RANGE1,RANGE2,RANGE3)

This will produce an array consisting of the ranges "stacked" on top of each other. Assign this formula to a named range and you can use it programmatically just like any other Range object.

Stucco