Java: Split Excel Worksheets into Separate Workbooks

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);
        }
    }
}

Java: Split Excel Worksheets into Separate Workbooks

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.