Java/ Split a Worksheet into Several Excel Files

When dealing with a large Excel worksheet containing different types of data, we can split them into several Excel documents for better understanding and analysis. This article will give you the example of how to split a worksheet into several Excel documents in Java application.

Tools:
● Free Spire.XLS for Java
● IntelliJ IDEA

Installation
Method 1: Download the 3rd party free API and unzip it. Then add the Spire.Xls.jar file to your project as dependency.

Method 2: Directly add the jar dependency to maven project by adding the following configurations to the pom.xml.

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

Code Snippet

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("input.xlsx");

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

//Get the header row
CellRange headerRow = sheet.getCellRange(1, 1, 1, 6);

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

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

//Copy the header row and range 1 to the new workbook
sheet.copy(headerRow, 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("Order ID-10248.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(headerRow, 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("Order ID-10249.xlsx", ExcelVersion.Version2016);
}
}