· 

Java - Split a Worksheet by Cell Ranges

Imagine you have a worksheet that contains the sales data over decades and each piece of data was recorded on a monthly basis, you may find it a bit difficult to locate the data of a specific year as quickly as possible. In such cases, you can split the large worksheet into multiple worksheets or workbooks by cell ranges.

 

 In this article, I am going to introduce how to split a worksheet by cell ranges using 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>

Example 1. Split a worksheet into different workbooks

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

public class SplitWorksheet {

   
public static void main(String[] args) {

       
//Create a Workbook object to load the original Excel document
       
Workbook bookOriginal = new Workbook();
        bookOriginal.loadFromFile(
"C:\\Users\\Administrator\\Desktop\\Employees.xlsx");

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

       
//Get the header row
       
CellRange headRow = sheet.getCellRange(1, 1, 1, 5);

       
//Get two cell ranges
       
CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
        CellRange range2 = sheet.getCellRange(
7, 1, 11, 5);

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

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

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

Example 2. Split a worksheet into multiple worksheets within the workbook

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

public class SplitWorksheetIntoMultiSheets {

   
public static void main(String[] args) {

       
//Create a Workbook object to load the original Excel document
       
Workbook bookOriginal = new Workbook();
        bookOriginal.loadFromFile(
"C:\\Users\\Administrator\\Desktop\\Employees.xlsx");

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

       
//Get the header row
       
CellRange headRow = sheet.getCellRange(1, 1, 1, 5);

       
//Get two cell ranges
       
CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
        CellRange range2 = sheet.getCellRange(
7, 1, 11, 5);

       
//Add a worksheet
       
Worksheet sheet2 = bookOriginal.getWorksheets().add("Sales");

       
//Copy the header row and range 1 to sheet2
       
sheet.copy(headRow, sheet2, 1, 1, true, false);
        sheet.copy(range1, sheet2,
2, 1, true, false);

       
//Add another worksheet
       
Worksheet sheet3 = bookOriginal.getWorksheets().add("Technicians");

       
//Copy the header row and range 2 to sheet3
       
sheet.copy(headRow, sheet3, 1, 1, true, false);
        sheet.copy(range2, sheet3,
2, 1, true, false);

       
//Copy the column width from the first worksheet to sheet2 and sheet3
       
for (int i = 0; i < sheet.getLastColumn(); i++) {

            sheet2.setColumnWidth(i +
1, sheet.getColumnWidth(i + 1));
            sheet3.setColumnWidth(i +
1, sheet.getColumnWidth(i + 1));
        }
       
       
//Save the document
       
bookOriginal.saveToFile("Splited.xlsx", ExcelVersion.Version2016
);
    }
}

Write a comment

Comments: 0