Spire.Xls.CellRange class provides a method named Intersect(CellRange range) that is used to find the intersection of certain ranges. This is very useful when we need to get the common value(s) of two ranges in an excel worksheet.
In below picture, we take range A2:C8 and range B2:D8 as an example. Cells filled in yellow color are the intersection of the two ranges.
Now refer to the following detail steps:
Step 1: Instantiate an object of Workbook class and load the Excel document.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Get the intersection of the two ranges and print the common values of them.
CellRange range = sheet.Range["A2:C8"].Intersect(sheet.Range["B2:D8"]);
foreach (CellRange r in range)
{
Console.WriteLine(r.Value);
}
Output:
Full code:
[C#]
using System;
using Spire.Xls;
namespace Get_the_instersection_of_two_ranges
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
CellRange range = sheet.Range["A2:C8"].Intersect(sheet.Range["B2:D8"]);
foreach (CellRange r in range)
{
Console.WriteLine(r.Value);
}
Console.ReadKey();
}
}
}
[VB.NET]
Imports Spire.Xls
Namespace Get_the_instersection_of_two_ranges
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("Sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim range As CellRange = sheet.Range("A2:C8").Intersect(sheet.Range("B2:D8"))
For Each r As CellRange In range
Console.WriteLine(r.Value)
Next
Console.ReadKey()
End Sub
End Class
End Namespace
