Java - Split a Worksheet by Cell Ranges

Imagine you have a worksheet that contains the sales data over decades and each piece of data was recorded on a monthly basis, you may find it a bit difficult to locate the data of a specific year as quickly as possible. In such cases, you can split the large worksheet into multiple worksheets or workbooks by cell ranges.

 In this article, I am going to introduce how to split a worksheet by cell ranges using Spire.XLS for Java.

Installing Spire.Xls.jar

If you create a Maven project, you can easily import the jar in your application using the following configurations. For non-Maven projects, download the jar file from this link and add it as a dependency in your application.

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

Example 1. Split a worksheet into different workbooks

import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class SplitWorksheet {

    public static void main(String[] args) {

        //Create a Workbook object to load the original Excel document
        Workbook bookOriginal = new Workbook();
        bookOriginal.loadFromFile("C:\\Users\\Administrator\\Desktop\\Employees.xlsx");

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

        //Get the header row
        CellRange headRow = sheet.getCellRange(1, 1, 1, 5);

        //Get two cell ranges
        CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
        CellRange range2 = sheet.getCellRange(7, 1, 11, 5);

        //Create a new workbook
        Workbook newBook1 = new Workbook();

        //Copy the header row and range 1 to the new workbook
        sheet.copy(headRow, newBook1.getWorksheets().get(0), 1, 1, true, false);
        sheet.copy(range1, newBook1.getWorksheets().get(0), 2, 1, true, false);

        //Copy the column width from the original workbook to the new workbook
        for (int i = 0; i < sheet.getLastColumn(); i++) {

            newBook1.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
        }

        //Save the new workbook to an Excel file
        newBook1.saveToFile("Sales.xlsx", ExcelVersion.Version2016);

        //Copy the header row and range 2 to another workbook, and save it to another Excel file
        Workbook newBook2 = new Workbook();
        sheet.copy(headRow, newBook2.getWorksheets().get(0), 1, 1, true, false);
        sheet.copy(range2, newBook2.getWorksheets().get(0), 2, 1, true, false);
        for (int i = 0; i < sheet.getLastColumn(); i++) {

            newBook2.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
        }
        newBook2.saveToFile("Technicians.xlsx", ExcelVersion.Version2016);
    }
}

Example 2. Split a worksheet into multiple worksheets within the workbook

import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class SplitWorksheetIntoMultiSheets {

    public static void main(String[] args) {

        //Create a Workbook object to load the original Excel document
        Workbook bookOriginal = new Workbook();
        bookOriginal.loadFromFile("C:\\Users\\Administrator\\Desktop\\Employees.xlsx");

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

        //Get the header row
        CellRange headRow = sheet.getCellRange(1, 1, 1, 5);

        //Get two cell ranges
        CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
        CellRange range2 = sheet.getCellRange(7, 1, 11, 5);

        //Add a worksheet
        Worksheet sheet2 = bookOriginal.getWorksheets().add("Sales");

        //Copy the header row and range 1 to sheet2
        sheet.copy(headRow, sheet2, 1, 1, true, false);
        sheet.copy(range1, sheet2, 2, 1, true, false);

        //Add another worksheet
        Worksheet sheet3 = bookOriginal.getWorksheets().add("Technicians");

        //Copy the header row and range 2 to sheet3
        sheet.copy(headRow, sheet3, 1, 1, true, false);
        sheet.copy(range2, sheet3, 2, 1, true, false);

        //Copy the column width from the first worksheet to sheet2 and sheet3
        for (int i = 0; i < sheet.getLastColumn(); i++) {

            sheet2.setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
            sheet3.setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
        }
       
        //Save the document
        bookOriginal.saveToFile("Splited.xlsx", ExcelVersion.Version2016);
    }
}