Java - Split Each Sheet into Separate Excel Files

You may need to split a large workbook to separate Excel files with saving each worksheet of the workbook as an individual Excel file. This article will introduce how to split a large workbook to separate Excel files based on each worksheet, by using Free 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>

Using the code 

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;

public class SplitWorkbook {

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

Output