How to Copy Worksheets in Excel Using Java

Excel remains a cornerstone for data management and reporting across various industries. As developers, we often face the challenge of programmatically interacting with these files. A common requirement is to copy worksheets in Excel with Java, whether it's transferring data between existing files or duplicating templates within a single workbook. This tutorial will guide you through practical methods to achieve this, leveraging a powerful and user-friendly Java library to streamline your Excel automation tasks. Mastering this skill will significantly enhance your ability to build robust data processing applications.

Introduction to Spire.XLS for Java and Setup

When it comes to Java libraries for Excel manipulation, developers have several options. For this tutorial, we will be using Spire.XLS for Java due to its comprehensive feature set, ease of use, and excellent performance, particularly for complex Excel operations.

Why Spire.XLS for Java?

Spire.XLS for Java is a professional Excel API that allows developers to create, read, write, convert, and print Excel files in Java applications. It supports various Excel formats, including XLS, XLSX, and CSV, and offers extensive functionalities beyond basic data manipulation, such as chart creation, pivot tables, and conditional formatting. Its intuitive object model closely mirrors the structure of Excel itself, making it easy for developers to adapt. For our specific needs of copying worksheets, Spire.XLS provides straightforward methods that simplify what could otherwise be a complex task.

Installation Guide

Getting started with Spire.XLS for Java is simple. The easiest way to integrate it into your project is by adding the Maven dependency.

For Maven projects, add the following dependency to your 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>15.9.1</version>
    </dependency>
</dependencies>

For manual integration, you can download the JAR file from the official website and add it to your project's build path. Once configured, you're ready to start manipulating Excel files with Java.

How to Copy a Worksheet from One Excel File to Another using Java

A common scenario involves extracting a specific worksheet from one Excel file and inserting it into another, potentially existing, Excel file. This is particularly useful for consolidating reports or merging data from different sources. Spire.XLS for Java makes it incredibly easy to copy worksheets in Excel with Java across different workbooks.

Let's illustrate this with an example where we copy a worksheet from the source Excel file to the target workbook.

import com.spire.xls.*;

public class copyWorksheet {
    public static void main(String[] args) {

        //Create a Workbook
        Workbook sourceWorkbook = new Workbook();

        //Load the source Excel file from disk
        sourceWorkbook.loadFromFile("sample1.xlsx");

        //Get the first worksheet
        Worksheet srcWorksheet = sourceWorkbook.getWorksheets().get(0);

        //Create a another Workbook
        Workbook targetWorkbook = new Workbook();

        //Load the target Excel file from disk
        targetWorkbook.loadFromFile("sample2.xlsx");

        //Add a new worksheet
        Worksheet targetWorksheet = targetWorkbook.getWorksheets().add("added");

        //Copy the first worksheet of sample1 to the new added sheet of sample2
        targetWorksheet.copyFrom(srcWorksheet);

        //String for output file
        String outputFile = "output/CopyWorksheet.xlsx";

        //Save the result file
        targetWorkbook.saveToFile(outputFile, ExcelVersion.Version2013);
        sourceWorkbook.dispose();
        targetWorkbook.dispose();
    }
}

In this code:

• We first load the source file into a sourceWorkbook object.
• We retrieve the specific sourceSheet by its index. Robust applications should include checks for null if the sheet might not exist.
• We then initialize a targetWorkbook. We load another Excel file.
• The core operation is targetWorksheet.copyFrom(srcWorksheet). This method performs a deep copy of the sourceSheet and adds it to the targetWorkbook.
• We rename the newly copied sheet for clarity.
• Finally, we save the targetWorkbook. It's crucial to dispose() both workbooks in a finally block to prevent resource leaks.

How to Duplicate a Worksheet Within the Same Excel Workbook using Java

Sometimes, the requirement isn't to transfer a sheet between files, but to create a duplicate of an existing sheet within the same workbook. This is incredibly useful for creating templates, scenario planning, or generating multiple reports with minor variations based on a common structure. Learning how to copy a worksheet using Java within the same workbook is a fundamental skill for Excel automation.

Here's how you can duplicate a worksheet within a single Excel file using Spire.XLS for Java. We'll duplicate the first worksheet within a sample file.

import com.spire.xls.*;

public class copySheetWithinWorkbook {
    public static void main(String[] args) {
        //Create a Workbook
        Workbook workbook = new Workbook();

        //Load the sample file from disk
        workbook.loadFromFile("sample1.xlsx");

        //Get the first sheet and add a new worksheet to this file
        Worksheet sheet = workbook.getWorksheets().get(0);
        Worksheet sheet1 = workbook.getWorksheets().add("MySheet");

        //Copy the first worksheet to the second one
        sheet1.copyFrom(sheet);

        //String for output file
        String result = "output/CopySheetWithinWorkbook.xlsx";

        //Save to file
        workbook.saveToFile(result, ExcelVersion.Version2013);
        workbook.dispose();
    }
}

In this example:

• We load the sample Excel file into a workbook object.
• We obtain a certain worksheet and add a new sheet called "MySheet" using Workbook.getWorksheets().add() method.
• We use Worksheet.copyFrom() method to copy the worksheet.

This method effectively creates an exact replica of the source worksheet, including its data, formatting, charts, and anything else contained within it, all within the same Excel file.

Conclusion

This tutorial has guided you through the essential steps to programmatically copy worksheets in Excel with Java, demonstrating how to both transfer a worksheet between different Excel files and duplicate a worksheet within the same workbook. By leveraging the powerful capabilities of Spire.XLS for Java, these otherwise complex tasks become straightforward and efficient. We've seen how its intuitive API simplifies operations from loading workbooks to adding and renaming sheets. We encourage you to explore the extensive documentation of Spire.XLS for Java to unlock its full potential for more advanced Excel automation needs in your Java applications.