여러 엑셀파일을 하나의 파일로 통합하기

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.util.*;

public class ExcelMerger {

    private Workbook mergedWorkbook;

    public ExcelMerger() {
        mergedWorkbook = new XSSFWorkbook(); // 항상 결과는 .xlsx로 만듦
    }

    public void mergeExcelFiles(List<File> excelFiles) throws Exception {
        for (File file : excelFiles) {
            try (InputStream is = new FileInputStream(file)) {
                Workbook srcWorkbook = WorkbookFactory.create(is);
                for (int i = 0; i < srcWorkbook.getNumberOfSheets(); i++) {
                    Sheet srcSheet = srcWorkbook.getSheetAt(i);
                    String newSheetName = getUniqueSheetName(srcSheet.getSheetName());
                    Sheet destSheet = mergedWorkbook.createSheet(newSheetName);

                    copySheet(srcSheet, destSheet);
                }
            }
        }
    }

    private String getUniqueSheetName(String baseName) {
        String name = baseName;
        int counter = 1;
        while (mergedWorkbook.getSheet(name) != null) {
            name = baseName + "_" + counter++;
        }
        return name;
    }

    private void copySheet(Sheet srcSheet, Sheet destSheet) {
        Map<Integer, CellStyle> styleMap = new HashMap<>();

        for (int rowNum = 0; rowNum <= srcSheet.getLastRowNum(); rowNum++) {
            Row srcRow = srcSheet.getRow(rowNum);
            if (srcRow == null) continue;
            
            // 열 너비 복사
            Row firstRow = srcSheet.getRow(0);
            if (firstRow != null) {
                for (int col = 0; col <= firstRow.getLastCellNum(); col++) {
                    destSheet.setColumnWidth(col, srcSheet.getColumnWidth(col));
                }
            }
            
            Row destRow = destSheet.createRow(rowNum);
            destRow.setHeight(srcRow.getHeight());

            for (int colNum = 0; colNum < srcRow.getLastCellNum(); colNum++) {
                Cell srcCell = srcRow.getCell(colNum);
                if (srcCell == null) continue;

                Cell destCell = destRow.createCell(colNum);
                copyCell(srcCell, destCell, styleMap);
            }
        }

        // 복사 병합 영역
        for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) {
            CellRangeAddress merged = srcSheet.getMergedRegion(i);
            destSheet.addMergedRegion(merged);
        }

        // 복사 이미지 (XSSF만 해당)
        if (srcSheet instanceof XSSFSheet && destSheet instanceof XSSFSheet) {
            try {
                copyPictures((XSSFSheet) srcSheet, (XSSFSheet) destSheet, (XSSFWorkbook) mergedWorkbook);
            } catch (IOException e) {
                System.err.println("이미지 복사 중 오류: " + e.getMessage());
            }
        }
    }

    private void copyCell(Cell srcCell, Cell destCell, Map<Integer, CellStyle> styleMap) {
        Workbook destWb = destCell.getSheet().getWorkbook();
        CellStyle newStyle = null;

        // 스타일 복사 (단, XSSFCellStyle일 때만)
        if (srcCell.getCellStyle() != null &&
            srcCell.getCellStyle().getClass().equals(destWb.createCellStyle().getClass())) {

            int styleHash = srcCell.getCellStyle().hashCode();
            newStyle = styleMap.computeIfAbsent(styleHash, k -> {
                CellStyle cloned = destWb.createCellStyle();
                cloned.cloneStyleFrom(srcCell.getCellStyle());
                return cloned;
            });
            destCell.setCellStyle(newStyle);
        } else {
            // 서로 다른 스타일 클래스(HSSF ↔ XSSF)이므로 새 기본 스타일 사용
            destCell.setCellStyle(destWb.createCellStyle());
        }

        // 값 복사
        switch (srcCell.getCellType()) {
            case STRING:
                destCell.setCellValue(srcCell.getStringCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    destCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    destCell.setCellValue(srcCell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                destCell.setCellValue(srcCell.getBooleanCellValue());
                break;
            case FORMULA:
                try {
                    FormulaEvaluator evaluator = srcCell.getSheet().getWorkbook()
                            .getCreationHelper().createFormulaEvaluator();
                    CellValue evaluatedValue = evaluator.evaluate(srcCell);
                    if (evaluatedValue != null) {
                        switch (evaluatedValue.getCellType()) {
                            case NUMERIC:
                                destCell.setCellValue(evaluatedValue.getNumberValue());
                                break;
                            case STRING:
                                destCell.setCellValue(evaluatedValue.getStringValue());
                                break;
                            case BOOLEAN:
                                destCell.setCellValue(evaluatedValue.getBooleanValue());
                                break;
                            case ERROR:
                                destCell.setCellErrorValue(evaluatedValue.getErrorValue());
                                break;
                            case BLANK:
                                destCell.setBlank();
                                break;
                        }
                    }
                } catch (Exception e) {
                    destCell.setBlank(); // 안전하게 처리
                }
                break;
            case ERROR:
                destCell.setCellErrorValue(srcCell.getErrorCellValue());
                break;
            case BLANK:
                destCell.setBlank();
                break;
        }
    }


    private void copyPictures(XSSFSheet srcSheet, XSSFSheet destSheet, XSSFWorkbook destWorkbook) throws IOException {
        XSSFDrawing srcDrawing = srcSheet.getDrawingPatriarch();
        if (srcDrawing == null) return;

        XSSFDrawing destDrawing = destSheet.createDrawingPatriarch();

        for (XSSFShape shape : srcDrawing.getShapes()) {
            if (shape instanceof XSSFPicture) {
                XSSFPicture srcPicture = (XSSFPicture) shape;
                XSSFPictureData picData = srcPicture.getPictureData();

                int pictureIndex = destWorkbook.addPicture(picData.getData(), picData.getPictureType());

                XSSFClientAnchor anchor = (XSSFClientAnchor) srcPicture.getAnchor();
                XSSFClientAnchor newAnchor = new XSSFClientAnchor(
                    anchor.getDx1(), anchor.getDy1(), anchor.getDx2(), anchor.getDy2(),
                    anchor.getCol1(), anchor.getRow1(), anchor.getCol2(), anchor.getRow2()
                );

                destDrawing.createPicture(newAnchor, pictureIndex);
            }
        }
    }

    public void saveMergedFile(String outputPath) throws IOException {
        try (FileOutputStream fos = new FileOutputStream(outputPath)) {
            mergedWorkbook.write(fos);
        }
    }
    
    public static void main(String[] args) throws Exception {
        ExcelMerger merger = new ExcelMerger();

        merger.mergeExcelFiles(Arrays.asList(
            new File("C:\\Users\\subinto\\Downloads\\AAA.xlsx"),
            new File("C:\\Users\\subinto\\Downloads\\BBB.xls"),
            new File("C:\\Users\\subinto\\Downloads\\CCC.xlsx")
        ));

        merger.saveMergedFile("C:\\Users\\subinto\\Downloads\\merged_result.xlsx");
        System.out.println("############### end");
    }
}