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);
}
}
Write a comment