Java: Copy Worksheets in Excel

Copying worksheet involves duplicating an existing worksheet within the same workbook or across different workbooks. This valuable feature enables developers to create an exact replica of the original worksheet effortlessly, including its structure, formatting, data, formulas, charts, and other objects without any mistake. It proves especially beneficial when dealing with extensive data files, as it significantly reduces time and effort required for backing up files and creating templates. In this article, we will introduce how to copy worksheets in Excel using Spire.XLS for Java. With this method, all the cell formats in the original Excel worksheets will be completely remained.

• Copy Worksheets between Workbooks
• Copy Worksheets within Workbooks

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's 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>14.11.0</version>
    </dependency>
</dependencies>
    

Copy Worksheets between Workbooks

Spire.XLS for Java library allows you copy worksheets from one workbook to another file easily by using Worksheet.copyFrom() method. The following are detailed steps.

• Create a new Workbook object.
• Load the source Excel file from disk using Workbook.loadFromFile() method.
• Get the first worksheet of the source file by using Workbook.getWorksheets().get() method.
• Create an another Workbook object.
• Load the target file from disk using Workbook.loadFromFile() method.
• Add a new sheet to the target file using Workbook.getWorksheets().add() method.
• Copy the first worksheet of the source file to the new added sheet of the target file through Worksheet.copyFrom() method.
• Finally, specify the output path and save the target file using Workbook.saveToFile() method.

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

Copy Worksheets within Workbooks

You can also copy a worksheet within the same workbook by adding a new worksheet to this workbook and then copying the desired sheet to the new one. The following are the steps to duplicate worksheets within an Excel workbook.

• Create a new Workbook object.
• Load the source Excel file from disk using Workbook.loadFromFile() method.
• Get the first worksheet by using Workbook.getWorksheets().get() method and add a new sheet called "MySheet" using Workbook.getWorksheets().add() method.
• Copy the first worksheet to the second one through Worksheet.copyFrom() method;
• Finally, specify the output path and save the result file using Workbook.saveToFile() method.

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

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.