Java – How to Split a Worksheet into Multiple Sheets by Cell Ranges

Dividing a large worksheet into two or more smaller worksheets can sometimes bring you much convenience. For example, you can filter out the sensitive information in a worksheet and distribute the remaining data to others.

In this article, I am going to introduce how to split an Excel worksheet into multiple worksheets by using Free Spire.XLS for Java. Depending on whether to save the newly generated worksheets in the same workbook, this topic can be divided into the following two topics.

• Split a worksheet into different workbooks
• Split a worksheet into multiple worksheets within the same workbook

Add Spire.Xls.jar as Dependency

If you are working on a maven project, you can include the dependency in pom.xml file using this:

<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>5.1.0</version>
    </dependency>
</dependencies>

If you are not using maven, then you can find the required jar files from the zip file available in this location. Include all the jar files into the application lib folder to run the sample code given in this tutorial.

Example 1. Split a worksheet into different workbooks

Suppose you have a large worksheet, and you want to split the worksheet by cell ranges and store each piece of data in an individual workbook, so you can quickly find the data you need in a specified Excel file. To realize the above requirements with the program thinking, the steps should be:

1. Create a workbook to load the source Excel file.
2. Get the worksheet, and then the cell ranges that you want to split.
3. Create a new workbook, and copy the cell ranges from the source document to the new workbook.
4. Save the new workbook to an Excel file.
5. Repeat step 3 and step 4 to create another Excel file containing the other cell ranges copied from the source document.

The following is the code snippet of splitting a worksheet by cell ranges and save in different workbooks using Free Spire.XLS for Java library.

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

There also might be situations where you want to split a worksheet into multiple worksheets and save them in the same Excel workbook. The steps to achieve this task are:

1. Create a workbook to load the source Excel document.
2. Get the specific worksheet and then the cell ranges that you want to split.
3. Add a new worksheet, and copy the cell ranges from original sheet to the new sheet.
4. Add another worksheet, and copy the other cell ranges from the original sheet the new sheet.
5. Save the workbook to a new Excel document.

The following is the code snippet of splitting a worksheet into multiple worksheets within the same workbook using Free Spire.XLS for Java library.

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

public class SplitWorksheetsIntoMultipleSheets {

    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("Divided.xlsx", ExcelVersion.Version2016);
    }
}

Thank you for reading this article. In case you get any questions, just feel free to comment below.