Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
373 views
in Technique[技术] by (71.8m points)

c# - Excel Get_Range with multiple areas

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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);
    }

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...