Copy and Move Excel Worksheets in Java

#java #excel #copyworksheet #moveworksheet

There may be times when you want to create a new worksheet based on an existing worksheet or moving an existing worksheet tab to another location. This article is aimed to show you how to copy and move worksheets in Excel in Java application using Free Spire.XLS for Java library.

Contents Summary

Add Dependencies
Copy worksheets within a workbook
Copy worksheets between workbooks
Copy particular cell ranges
Move worksheets

Add Dependencies

First of all, you need to add needed dependencies for including Free Spire.XLS for Java into your Java project. There are two ways to do that.

If you use maven, you need to add the following code to your project’s pom.xml file.

<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>

For non-maven projects, download Free Spire.XLS for Java pack from this website and add Spire.Xls.jar in the lib folder into your project as a dependency.

Copy worksheets within a workbook

Free Spire.XLS for Java supports copying worksheets within or between workbooks. Worksheet data, format, chart or other objects are copied with high degree of precision. The following example shows how to copy an worksheet within a workbook.

Free Spire.XLS for Java provides a Worksheet.copyFrom() method which allows copying a worksheet to another worksheet within a workbook. Here is the code snippet for your reference:

//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("Sample.xlsx");

//Get the first worksheet
Worksheet sheet1 = workbook.getWorksheets().get(0);
//Add a new worksheet to the workbook
Worksheet newSheet = workbook.getWorksheets().add("Copy of sheet1");
//Copy the first worksheet to the new worksheet
newSheet.copyFrom(sheet1);

//Save the result file
workbook.saveToFile("CopyWithinWorkbook.xlsx", FileFormat.Version2013);

Copy worksheets between workbooks

The Worksheet.copyFrom() method is also used to copy worksheets between workbooks. The following code snippet shows how to copy an existing worksheet from a workbook to another workbook.

//Create a Workbook instance
Workbook workbook1 = new Workbook();
//Load an Excel file
workbook1.loadFromFile("Sample.xlsx");

//Get the first worksheet of the workbook
Worksheet sheetOfWorkbook1 = workbook1.getWorksheets().get(0);

//Create a Workbook instance
Workbook workbook2 = new Workbook();
//Load another Excel file
workbook2.loadFromFile("Test.xlsx");

//Add a worksheet to the file
Worksheet sheetOfWorkbook2 = workbook2.getWorksheets().add("Copy of sheet1 in Sample.xlsx");
//Copy the first worksheet of the first Excel file to the newly added worksheet of the second Excel file
sheetOfWorkbook2.copyFrom(sheetOfWorkbook1);

//Save the result file
workbook2.saveToFile("CopyBetweenWorkbooks.xlsx", FileFormat.Version2013);

Copy particular cell ranges

Free Spire.XLS for Java provides a Worksheet.copy() method that is used to copy particular cell ranges. The following code snippet shows how to copy a particular cell range to another location.

//Create a Worbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("Sample.xlsx");

//Get the first worksheet
Worksheet sheet1 = workbook.getWorksheets().get(0);

//Specify the source cell range
CellRange range1 = sheet1.getCellRange(1,1,5,3);
//Specify the destination cell range
CellRange range2 = sheet1.getCellRange(1,5,5,7);

//Copy the source cell range to the destination cell range
sheet1.copy(range1, range2, true);

//Save the result file
workbook.saveToFile("CopyParticularCellRange.xlsx", FileFormat.Version2013);

Move worksheets

To move a worksheet to another location within a workbook, you need to use the Worksheet.moveWorksheet() method. The following code snippet shows how to move a worksheet to another location in the same workbook.

//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("Sample.xlsx");

//Get the first worksheet
Worksheet sheet1 = workbook.getWorksheets().get(0);
//Move the worksheet to another location within the workbook
sheet1.moveWorksheet(1);

//Save the result file
workbook.saveToFile("MoveWorksheets.xlsx", FileFormat.Version2013);