반응형
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");
}
}
반응형
'개발 > JAVA' 카테고리의 다른 글
Java에서 String변수가 String[]에 포함되어 있는지 확인 (0) | 2025.04.01 |
---|---|
poi에서 A파일 sheet에 B파일 sheet 복사하기 (0) | 2025.03.04 |
Map의 다양한 출력 방법 (0) | 2025.02.12 |
Java에서 사용되는 연산자의 종류와 사용법 완벽 가이드 (0) | 2025.01.26 |
Java에서 형변환(Casting)의 모든 것: 기본형부터 참조형까지 (1) | 2025.01.26 |