Java: Get the Intersection of Two Cell Ranges in Excel

When working with Excel, you may sometimes need to find the common values between two ranges of cells in a worksheet. For this reason, it’s recommended that Java codes can be used to automatically find the intersection of certain ranges. In this article, you'll learn how to achieve the operation using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's 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</artifactId>
        <version>14.11.0</version>
    </dependency>
</dependencies>
    

Get the Intersection of Two Cell Ranges in Excel

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

• Create a Workbook instance and load a sample Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet of the file using Workbook.getWorksheets().get() method.
• Specify two ranges of cells using Worksheet.getRange().get() method and get their intersection using XlsRange.intersect() method.
• Create a StringBuilder instance.
• Loop through the intersection and obtain cell values using CellRange.getValue() method.
• 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:E7").intersect(sheet.getRange().get("C3:D7"));

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

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

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

The input Excel:

The output result:

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.