Copying Sheets between Workbooks using Apache POI and Java

Copying spreadsheets between different Excel workbooks is a common task that Java developers often encounter. Apache POI is the go-to library that empowers Java applications to handle various Microsoft documents, including Excel formats. In this blog post, we will explore how developers can efficiently copy a sheet from one workbook to another using Apache POI with Java. This process can be instrumental in automating Excel data manipulation tasks.

The Main Challenge

The task at hand is how to effectively copy a sheet from one Excel workbook to another. This may seem straightforward, but it involves various considerations such as data formats, styles, and formulas present in the sheets. The solution needs to ensure that all these elements are correctly transferred without any loss or corruption of data.

Breakdown of Solutions

Multiple solutions can address the need to copy sheets between workbooks using Apache POI. Below, we’re detailing the most comprehensive approach shared by the developer community.

Step 1: Setting Up Apache POI

To begin with, you need to set up Apache POI in your Java project. Ensure that you have included the necessary Apache POI dependencies in your pom.xml file if you are using Maven:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version> <!-- or the latest version -->
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version> <!-- or the latest version -->
</dependency>

Step 2: Loading Workbooks in Java

First, load the source and target workbooks into your application. Apache POI provides a convenient way to handle both XLS and XLSX files.

FileInputStream inputStreamSrc = new FileInputStream("source.xlsx");
XSSFWorkbook sourceWorkbook = new XSSFWorkbook(inputStreamSrc);

FileInputStream inputStreamDest = new FileInputStream("destination.xlsx");
XSSFWorkbook destinationWorkbook = new XSSFWorkbook(inputStreamDest);

Step 3: Copying the Sheet

To copy a sheet from the source workbook to the destination workbook, iterate through the rows and cells of the source sheet and replicate those in the destination sheet.

XSSFSheet sourceSheet = sourceWorkbook.getSheetAt(0); // Select sheet at index 0
XSSFSheet destinationSheet = destinationWorkbook.createSheet("CopiedSheet");

for (int i = 0; i < src.getPhysicalNumberOfRows(); i++) {
    XSSFRow srcRow = sourceSheet.getRow(i);
    XSSFRow destRow = destinationSheet.createRow(i);

    for (int j = 0; j < srcRow.getPhysicalNumberOfCells(); j++) {
        XSSFCell srcCell = srcRow.getCell(j);
        XSSFCell destCell = destRow.createCell(j);

        destCell.setCellValue(srcCell.toString());
    }
}
inputStreamSrc.close();
inputStreamDest.close();

Step 4: Saving the Document

After copying is complete, the modified workbook should be saved to persist changes.

FileOutputStream outputStream = new FileOutputStream("modified_destination.xlsx");
destinationWorkbook.write(outputStream);

// Close resources
outputStream.close();
sourceWorkbook.close();
destinationWorkbook.close();

Conclusion

The outlined methodology provides a straightforward approach to copying sheets between Excel workbooks using Apache POI and Java. This process preserves the integrity of cell data, ensuring that text values, numbers, and even basic formulas are copied successfully. However, for more complex automation tasks or template-based data processing, further exploration into Apache POI's extensive features can be advantageous.

We encourage developers to experiment with these techniques and evolve them further to meet specific application requirements. Apache POI is a powerful tool that provides extensive customizability and support for Excel file operations.