Get the Intersection of Two Cell Ranges in Excel using Java

In the process of manipulating Excel worksheets, we might occasionally have to find the common values between two ranges of cells. In this case, Java codes can be used to automatically find the intersection of certain cell ranges.

DEPENDENCY

To achieve the operation listed above, we need a free third-party library called FreeSpire.XLS for Java to be installed in the Java program. It can be downloaded from the link, or create a Maven project and add the following code to the pom.xml file.

<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>

USING THE CODE

The following are detailed steps to get the intersection of two cell ranges in an Excel worksheet.

l  Create a Workbook instance and load a sample Excel file using Workbook.loadFromFile() method.

l  Get a specific worksheet of the file using Workbook.getWorksheets().get() method.

l  Specify two cell ranges using Worksheet.getRange().get() method and get their intersection using XlsRange.intersect() method.

l  Create a StringBuilder instance.

l  Loop through the intersection and obtain cell values using CellRange.getValue() method.

l  Append the result to the StringBuilder instance using StringBuilder.append() method.

import com.spire.xls.*;

public class getIntersectionOfTwoRanges {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load a sample Excel file
        workbook.loadFromFile( "C:\\Users\\Test1\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Specify two cell ranges and get their intersection
        CellRange range = sheet.getRange().get("B2:F10").intersect(sheet.getRange().get("E3:F8"));

        //Create a StringBuilder instance
        StringBuilder content = new StringBuilder();
        content.append("The intersection of the two ranges \"B2:F10\" and \"E3:F8\" is:"+"\n");

        //Loop through the intersection and obtain the values of cells
        for(CellRange r : range.getCellList())
        {
            content.append(r.getValue()+"\n");
        }

        //Output the result
        System.out.println(content);
    }
}

The input Excel:

The output result: