Split Excel worksheets into multiple files in Java

I introduced how to merge Excel files using a free Java API before. Today this tutorial will demonstrate how to split Excel worksheets into multiple files using the same API. The content is divided into two parts as below according to different requirements.

l  Split several worksheets into multiple Excel files

l  Split a worksheet into multiple Excel files

The free Java API I used is called Free Spire.XLS for Java. Before running codes, you need to add Spire.Xls.jar in the API to IDEA. You can download it from the link, or directly reference it using the following Maven configuration.

<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>3.9.1</version>
</dependency>
</dependencies>

Split several worksheets into multiple Excel files

There are two worksheets in an Excel workbook as below. Now I’ll use the following codes to split them into two Excel files, and every file only includes one worksheet of the original workbook.

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\\Test1\\Desktop\\Sample.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 = "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

Split a worksheet into multiple Excel files

An Excel workbook includes one worksheet, and now I’ll use the following codes to split it into two Excel files according to the data range.

import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class SplitWorksheetIntoMultiFiles {
    public static void main(String[] args) {
       //Create a Workbook object and load the original Excel document
        Workbook bookOriginal = new Workbook();
        bookOriginal.loadFromFile("C:\\Users\\Test1\\Desktop\\Test.xlsx");

        //Get the first worksheet
        Worksheet sheet = bookOriginal.getWorksheets().get(0);

        //Get the header row
        CellRange headerRow1 = sheet.getCellRange(1, 1, 1, 6);
        CellRange headerRow2 = sheet.getCellRange(11, 1, 11, 5);
        //Get two cell ranges
        CellRange range1 = sheet.getCellRange(2, 1, 10, 6);
        CellRange range2 = sheet.getCellRange(12, 1, 17, 5);

        //Create a new workbook
        Workbook newBook1 = new Workbook();

        //Copy the header row 1 and range 1 to the new workbook
        sheet.copy(headerRow1, 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("output/Vendors_output.xlsx", ExcelVersion.Version2016);

        //Copy the header row 2 and range 2 to another workbook, and save it to another Excel file
        Workbook newBook2 = new Workbook();
        sheet.copy(headerRow2, 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("output/Country List_output.xlsx", ExcelVersion.Version2016);
    }
}

Output