subinto의 개발노트

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");
    }
}

배열 aa 안에 문자열 bb 값이 포함되어 있는지 확인하려면 Arrays.asList(aa).contains(bb) 또는 Stream API를 사용할 수 있습니다.

1. Arrays.asList().contains() 사용

import java.util.Arrays;

public class Main {
    public static void main(String[] args) {
        String[] aa = {"AA", "BB", "CC"};
        String bb = "BB";

        boolean exists = Arrays.asList(aa).contains(bb);
        System.out.println("포함 여부: " + exists); // true 출력
    }
}

이 방법은 간단하지만 내부적으로 List 변환이 필요합니다.

2. Stream API 사용

import java.util.stream.Stream;

public class Main {
    public static void main(String[] args) {
        String[] aa = {"AA", "BB", "CC"};
        String bb = "BB";

        boolean exists = Stream.of(aa).anyMatch(s -> s.equals(bb));
        System.out.println("포함 여부: " + exists); // true 출력
    }
}

이 방법은 Stream을 사용하여 한 줄로 해결할 수 있습니다.

3. for 루프 사용 (전통적인 방법)

public class Main {
    public static void main(String[] args) {
        String[] aa = {"AA", "BB", "CC"};
        String bb = "BB";

        boolean exists = false;
        for (String s : aa) {
            if (s.equals(bb)) {
                exists = true;
                break;
            }
        }
        System.out.println("포함 여부: " + exists); // true 출력
    }
}

이 방법은 가장 기본적인 방법으로, 작은 배열에서는 성능 차이가 크지 않지만, 큰 배열에서는 Set을 사용하는 것이 효율적일 수 있습니다.

필요에 따라 적절한 방법을 선택하세요! 😊

📌 코드:

<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Input Tooltip</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            height: 200vh; /* 스크롤 테스트를 위해 높이 추가 */
            padding: 20px;
        }
        .tooltip {
            position: absolute;
            background: rgba(0, 0, 0, 0.8);
            color: white;
            padding: 8px;
            border-radius: 5px;
            font-size: 14px;
            max-width: 300px;
            word-wrap: break-word;
            display: none;
            z-index: 1000;
        }
        input {
            width: 300px;
            padding: 5px;
        }
    </style>
</head>
<body>

    <input type="text" id="inputField" value="이것은 너무 길어서 input 창에 다 안보이는 값입니다. 여기에 마우스를 올려보세요.">
    <div id="tooltip" class="tooltip"></div>

    <script>
        const inputField = document.getElementById("inputField");
        const tooltip = document.getElementById("tooltip");

        inputField.addEventListener("mouseover", function() {
            tooltip.textContent = inputField.value;
            tooltip.style.display = "block";
        });

        inputField.addEventListener("mousemove", function(event) {
            // pageX, pageY를 사용하여 스크롤 영향을 받지 않도록 조정
            tooltip.style.top = (event.pageY + 10) + "px";
            tooltip.style.left = (event.pageX + 10) + "px";
        });

        inputField.addEventListener("mouseout", function() {
            tooltip.style.display = "none";
        });
    </script>

</body>
</html>