Copy Worksheets in Excel using Java codes

When working in Excel, you’ll sometimes need to create one or more copies of your spreadsheet. Here I’ll introduce how to use Java codes to copy a specified worksheet within an Excel workbook or between two different workbooks.

BEFORE CODING

I used a free third-party library called Free Spire.XLS for Java in this tutorial. Before coding, we need to add a Jar file named Spire.Xls.jar to IDEA, and there are two methods to do it. One is that downloading the package of the free library from the link, finding Spire.Xls.jar in the “lib” folder and then manually adding it to IDEA. The other is that creating a Maven project in IDEA, typing the following codes in the pom.xml file and finally clicking the button “import changes”.

<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.free</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>

USING THE CODE
How to copy a worksheet within an Excel 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\\Test1\\Desktop\\Sample.xlsx");

        //Get the first worksheet
        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("output/CopySheetWithinWorkbook.xlsx");
    }
}
Output


How to copy a worksheet from an Excel workbook to another workbook
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class CopySheetBetweenTwoWorkbooks {
    public static void main(String[] args) {
        //Create a Workbook object to load the source document
        Workbook wb1 = new Workbook();
        wb1.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample1.xlsx");
       //Get the first worksheet to copy
        Worksheet sheet1 = wb1.getWorksheets().get(0);

        //Create another Workbook object to load the destination document
        Workbook wb2 = new Workbook();
        wb2.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample2.xlsx");
        //Add the copy of selected sheet to destination document
        Worksheet sheet2 = wb2.getWorksheets().get(0);
        sheet2.setName("Copied");
        sheet2.copyFrom(sheet1);

        //Save to another file
        wb2.saveToFile("output/CopySheetBetweenTwoWorkbooks.xlsx", FileFormat.Version2013);
    }
}
Output