Java - How To Copy Or Move Excel Worksheets

There might be situations where you need to copy worksheets inside a workbook or from one workbook to another. For instance, you have a ready-made template that you want to use for creating a report, you can create a copy of the template worksheet. Also, changing the positions of the worksheets in a workbook is also required in certain cases.

In order to perform the above-mentioned tasks programmatically, this article shows how to copy or move Excel worksheets using Spire.XLS for Java.

Installing Spire.Xls.jar

If you use Maven, you can easily import the jar in your application using the following configurations. For non-Maven projects, download the latest version of Spire.Xls.jar from this link and add it as a dependency in your Java program. 

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

Duplicate Worksheets within a Workbook

The following are the steps to duplicate worksheets within an Excel workbook.

• Initialize an instance of Workbook
• Load an Excel file using LoadFromFile() method.
• Get the specific worksheet from workbook using get() method.
• Add a new blank sheet to the workbook using add() method.
• Copy the original worksheet to the new sheet using copyFrom() method.
• Use saveToFile() method to save the changes to another file.

The following code snippet shows how to copy an Excel worksheet in a workbook.

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

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

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

        //Load the sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\source.xlsx");

        //Get the worksheet you want to copy
        Worksheet originalSheet = workbook.getWorksheets().get(0);

        //Add a new worksheet
        Worksheet newSheet = workbook.getWorksheets().add(originalSheet.getName()+" - Copy");

        //Copy the worksheet to new sheet
        newSheet.copyFrom(originalSheet);

        //Save to file
        workbook.saveToFile("DuplicateSheet.xlsx");
    }
}

Figure 1. Duplicate a worksheet within a workbook

Copy Worksheets from One Workbook to Another

To copy worksheets between two Excel files, you need to create two Workbook objects to load the source and destination Excel documents. Then, get the specific sheet from the source document, and add it as a copy in the destination document using addCopy() mothed.

In case the theme of the source document is different from that of the destination document, and you would like to maintain the original theme fonts and theme colors while duplicating sheets across different Excel files, then you must copy the theme as well by using Workbook.copyTheme() method.

The following is the sample code to copy a worksheet from one Excel workbook to another maintaining the original fonts and colors.

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

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

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

        //Get the specific worksheet to copy
        Worksheet originalSheet = srcWorkbook.getWorksheets().get(0);

        //Create another Workbook object to load the destination document
        Workbook destWorkbook = new Workbook();
        destWorkbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\destination.xlsx");

        //Add the copy of selected sheet to destination document
        Worksheet newSheet = destWorkbook.getWorksheets().addCopy(originalSheet);
        newSheet.setName(originalSheet.getName());

        //Copy the theme of source document to destination document
        destWorkbook.copyTheme(srcWorkbook);

        //Save to another file
        destWorkbook.saveToFile("CopySheetBetweenWorkbooks.xlsx");
    }
}

Figure 2. Copy a worksheet from one workbook to another

Move Worksheets from One Position to Another

After you get a specific worksheet from the document, you can move it to the desired position by using Worksheet.moveWorksheet() method.

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

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

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

        //Load the sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\source.xlsx");

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

        //Move the first sheet to the second position in the workbook
        sheet.moveWorksheet(1);

        //Save to file
        workbook.saveToFile("MoveWorksheets.xlsx");
    }
}

Conclusion

In this article, you have learned how to copy or remove worksheets using Spire.XLS for Java. You can explore many more features provided by this library by visiting the official documentation. In case you get any questions, just feel free to leave a comment below.