Equivalent to Excel.Range.Union() and Excel.Range.Intersect() Method in Aspose.Cells

Aspose.Cells Product Family
1

I am new to Aspose libraries and using trial version of Aspose.cells…
I am searching equivalent
methods for Excel.Range.Interesct() and Union() methods in Aspose.cells. library.

Please help me to get resolve this issue.

2
Hi,

We will check the feasibility if we can provide the methods regarding Intersection and Union operations for ranges. I think, currently, you may try some manual codes and workarounds for your need. For example, if you want to get to know whether a range is intersected by other range, you may try Range.IsIntersect() method, see the following sample code:

Workbook workbook = new Workbook();
workbook.Open(@"F:\test\rngBook2.xls");
Range[] ranges = workbook.Worksheets.GetNamedRanges();
bool intersection = ranges[0].IsIntersect(ranges[1]);

For getting the criterian or margins for the ranges in a workbook, you may try:

Workbook workbook = new Workbook();
workbook.Open(@"F:\test\rngBook2.xls");
Range[] ranges = workbook.Worksheets.GetNamedRanges();

int frow, fcol;
int rowcount, colcount;

if (ranges != null)
{
for (int i = 0; i < ranges.Length; i++)
{
frow = ranges[i].FirstRow;
fcol = ranges[i].FirstColumn;
string f1 = CellsHelper.CellIndexToName(frow, fcol);
//MessageBox.Show(ranges[i].FirstRow + ":" + ranges[i].FirstColumn);

rowcount = ranges[i].RowCount - 1 + ranges[i].FirstRow;
colcount = ranges[i].ColumnCount -1 + ranges[i].FirstColumn;
string f2 = CellsHelper.CellIndexToName(rowcount, colcount);

MessageBox.Show(f1 + ":" + f2);


}
}

Thank you.

3

Hi Amzad,

Thank you for quick reply on the my problem. Actually i want to make Interesect or Union of 2 seperate Ranges.
Like if i have 2 Ranges range1_ and range2_
in Execl it is like

range1_.Application.Intersect(range1_, range2_)

and for Union

range1_.Application.Union(range1_, range2_)

Thanks & Regards,
Sanjeev Gharmode

4

Hi Sanjeev,

Thanks for providing us further details,

We will get back to you soon.

Thank you.

5

Hi Amzad,

Any further updates on my Range issue, actually i also having one issue regarding Getting Value of the Range … same like Range.Value2 in Excel application object.
I aapriciate quick response on this.

Regards,
Sanjeev Gharmode

6

Hi Sanjeev,

Thanks for following up.

We are working on Intersect and Union functions, we will update you soon.

i also having one issue regarding Getting Value of the Range.....

Could you give us more details with sample code, template file etc, we check it soon. And, did you check the doc topic: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/named-ranges.html

Thank you.

7

Unlike MS Excel, Aspose.Cells' Range object is a rectangle area of cells. So we don't provide Intersect or Union method. To get a range of intersect or union of two range of cells, you have to use your own code.

8

Hi,

Please try the attached version. We have supported Range.Intersect() and Range.Union() methods.
Note:Range.Intersect() will return null if the two ranges are not intersected. Range.Union() will return an ArrayList wich contains one or more Range objects.
e.g..,
Workbook workbook = new Workbook();
workbook.Open(@"F:\test\rngBook2.xls");
Range[] ranges = workbook.Worksheets.GetNamedRanges();
bool isintersect = ranges[0].IsIntersect(ranges[2]);
MessageBox.Show(isintersect.ToString());
ArrayList al = new ArrayList();
al = ranges[1].Union(ranges[2]);
Range rng;

int frow, fcol, erow, ecol;
for (int i = 0; i < al.Count; i++)
{

rng = (Range)al[i];
frow = rng.FirstRow;
fcol = rng.FirstColumn;
erow = rng.RowCount;
ecol = rng.ColumnCount;

MessageBox.Show(frow.ToString());
MessageBox.Show(fcol.ToString());
MessageBox.Show(erow.ToString());
MessageBox.Show(ecol.ToString());
}
Thank you.
9

Hi Ahmad,

Thank you very much for your quick response on the issues raised.
Your Range.Union Works but Range.Intersect is returning NULL Value, where as i wanted to make Intersect of two ranges.
Is that possible same as Excel.Application.Range.Intersect supports?

Thanks & Regards,
Sanjeev Gharmode

10

Hi Sanjeev,

Your Range.Union Works but Range.Intersect is returning NULL Value...

Could you give us more details and post your simple code with template file (using Aspose.Cells API), we will check it soon.

Thank you.