Merging Excel files into single workbook

Question

I have a requirement to copy all the individual excel files to one single workbook separated by tabs where I'm using ASPOSE API. But its a paid one.

I have seen another API's which is cell-to-cell copying but its consuming time. I don't find any API to copy directly from the sheet.

Is there any way to copy directly from sheet to sheet?

Answer 1

Here's an example that assumes a directory containing files having the extension .xlsx and each one has a single sheet.

You will need the following imports:

import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

And in the example, read the code comments, please:

public static void main(String[] args) {
    // provide a path to a folder containing xlsx-workbooks
    Path folderWithWorkbooks = Paths.get("Y:\\our\\path\\to\\a\\folder\\with\\workbooks");
    // provide a workbook object to be written to
    Workbook resultWorkbook = new XSSFWorkbook();

    try {
        // get the file system objects in that folder
        Files.newDirectoryStream(folderWithWorkbooks).forEach(p -> {
            // and if one is an xlsx-workbook
            if (p.getFileName().toString().endsWith(".xlsx")) {
                // try to read its contents
                try (FileInputStream fis = new FileInputStream(p
                                                              .toAbsolutePath()
                                                              .toString())) {
                    // create the workbook to be parsed
                    Workbook currentWorkbook = new XSSFWorkbook(fis);
                    // get the FIRST sheet (adjust code here if you want more sheets)
                    Sheet sourceSheet = currentWorkbook.getSheetAt(0);
                    // create a new sheet in the result workbook, name pointing to its origin
                    Sheet resultSheet = resultWorkbook.createSheet("from "
                                                           + p.getFileName().toString());

                    // then classicly loop through the rows and cells and copy the contents
                    for (int r = 0; r < sourceSheet.getPhysicalNumberOfRows(); r++) {
                        Row sourceRow = sourceSheet.getRow(r);
                        Row resultRow = resultSheet.createRow(r);

                        for (int c = 0; c < sourceRow.getPhysicalNumberOfCells(); c++) {
                            Cell sourceCell = sourceRow.getCell(c);
                            Cell resultCell = resultRow.createCell(c);

                            // copy contents with respect to their types
                            switch (sourceCell.getCellType()) {
                            case NUMERIC:
                                resultCell.setCellValue(sourceCell.getNumericCellValue());
                                break;
                            case STRING:
                                resultCell.setCellValue(sourceCell.getStringCellValue());
                                break;
                            case FORMULA:
                                resultCell.setCellValue(sourceCell.getCellFormula());
                                break;
                            case BOOLEAN:
                                resultCell.setCellValue(sourceCell.getBooleanCellValue());
                                break;
                            case ERROR:
                                resultCell.setCellValue(sourceCell.getErrorCellValue());
                                break;
                            case BLANK:
                            case _NONE:
                                resultCell.setCellValue(sourceCell.getStringCellValue());
                                break;
                            }
                        }
                    }
                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        });

        // write the result workbook to the same folder
        FileOutputStream fos = new FileOutputStream(folderWithWorkbooks
                .resolve("result.xlsx")
                .toAbsolutePath()
                .toString());
        resultWorkbook.write(fos);
        fos.flush();
        fos.close();
        resultWorkbook.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

The result will be a workbook in the same directory with a name result.xlsx.

Please note that this does not copy any cell formatting or styles. You would have to add code for it in the section that copies the cell values.