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.5.2</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.