When multiple worksheets are combined into one workbook, it is convenient to manage relevant data in a single file. However, we sometimes need to split the worksheets into separate Excel files so that individual worksheets can be distributed without revealing the other information. This article introduces how to split Excel worksheets into separate workbooks using Spire.XLS for Java.
Install Spire.XLS for Java
First, 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>13.1.3</version>
</dependency>
</dependencies>
Split Excel Workbooks into Separate Workbooks
The following are the main steps to split Excel sheets into separate workbooks using Spire.XLS for Java.
- Create a Workbook object
- Load a sample Excel file using Workbook.loadFromFile() method.
- Declare a Workbook variable, which is used to create new workbooks.
- Loop through the worksheets in the source document.
- Initialize the Workbook object, and add the copy of a specific worksheet of source document into it.
- Save the workbook to an Excel file using Workbook.saveToFile() method.
- Java
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
public class SplitWorksheets {
public static void main(String[] args) {
//Create a Workbook object
Workbook wb = new Workbook();
//Load an Excel document
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");
//Declare a Workbook variable
Workbook newWb;
//Declare a String variable
String sheetName;
//Specify the folder path, which is used to store the generated Excel files
String folderPath = "C:\\Users\\Administrator\\Desktop\\Output\\";
//Loop through the worksheets in the source file
for (int i = 0; i < wb.getWorksheets().getCount(); i++) {
//Initialize the Workbook object
newWb = new Workbook();
//Remove the default sheets
newWb.getWorksheets().clear();
//Add the the specific worksheet of the source document to the new workbook
newWb.getWorksheets().addCopy(wb.getWorksheets().get(i));
//Get the worksheet name
sheetName = wb.getWorksheets().get(i).getName();
//Save the new workbook to the specified folder
newWb.saveToFile(folderPath + sheetName + ".xlsx", FileFormat.Version2013);
}
}
}
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.
