요구사항

매장별 입출고 재고 보고서를 내보내야 하는 프로젝트 상황이 있습니다. 각 매장 템플릿은 다음과 같습니다.

그러나 이번 요구사항에서는 여러 매장을 포함한 보고서를 하나의 시트에 표시해야 하므로, 구현 방식이 더 복잡해졌습니다.

요구사항 분석

분석 결과: 처음으로 이와 같은 내보내기 요구사항을 접했기 때문에 참고할 자료가 없어 단계적으로 기능을 분석하고 구현했습니다. 먼저 단일 매장에 대한 복잡한 템플릿 내보내기는 이미 완료되어 있으며, 관련 내용은 이전 글을 참조하세요: Java에서 easyexecl을 사용한 복잡한 파일 내보내기

여러 매장을 하나의 시트에 내보내는 방법은 세 단계로 구현했습니다. 첫 번째로 템플릿을 복사하여 각 매장별 시트를 생성합니다(매장 수만큼). 두 번째로 각 매장 시트에 데이터를 채웁니다. 세 번째로 모든 시트를 새로운 시트 파일로 병합합니다.

코드 직접 작성

public void exportStoreReport() {
    String templateFileName = "단일 매장 템플릿.xlsx";
    String fileName = "내보낸 파일.xlsx";
    
    // 매장 목록을 가져오는 데이터 인터페이스封装
    List<Map> storeReportList = storeReportSearch();
    
    // 템플릿을 기반으로 매장 시트를 복제하고 데이터를 템플릿으로 사용
    byte[] newExcelStream = getNewExcelStream(templateFileName, storeReportList.size());
    InputStream asInputStream = new ByteArrayInputStream(newExcelStream);

    // 데이터 채우기
    ByteArrayOutputStream byteArray = new ByteArrayOutputStream();
    try (ExcelWriter excelWriter = EasyExcel.write(byteArray).excelType(ExcelTypeEnum.XLSX).withTemplate(asInputStream).build();) {
        // 매장 목록을 순회하며 데이터 채우기 로직은 이전 글 참조
        for (int i = 0; i < storeReportList.size(); i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet(i).build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            
            // 데이터 채우기
            ...
        }
        excelWriter.finish();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }

    // 시트 병합 후 바이트 스트림 반환 (각자 필요에 따라 변환)
    ByteArrayOutputStream mergeOutputStream = mergeSheets(byteArray, storeReportList.size());
}

// 매장 시트 복제 메서드
private static byte[] getNewExcelStream(String fileName, int sheetCount) {
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    try (Workbook workbook = new XSSFWorkbook(new File(fileName))) {
        for (int i = 0; i < sheetCount; i++) {
            String sheetName = "Sheet" + i;
            if (i != 0) {
                Sheet templateSheet = workbook.getSheetAt(0);
                Sheet newSheet = workbook.cloneSheet(workbook.getSheetIndex(templateSheet));
                workbook.setSheetName(workbook.getSheetIndex(newSheet), sheetName);
            }
        }
        workbook.write(outputStream);
        outputStream.flush();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return outputStream.toByteArray();
}

POI는 여러 시트를 하나의 시트로 병합하는 기능을 제공하지 않기 때문에 직접 구현해야 합니다. 기본적인 로직은 새로운 대상 시트를 생성하고, 원본 시트의 내용을 반복해서 복사하는 것입니다. 복사되는 내용에는 셀 값, 스타일(폰트, 배경색, 테두리 등), 병합 영역, 조건 형식 규칙이 포함됩니다. 아래는 해당 코드입니다:

public ByteArrayOutputStream mergeSheets(ByteArrayOutputStream inputStream, int sheetCount) {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(inputStream.toByteArray());
Workbook workbook = new XSSFWorkbook(byteArrayInputStream);
Workbook targetWorkbook = new XSSFWorkbook();) {

Sheet storeReportSheet = targetWorkbook.createSheet("매장 보고서");
int targetRowNum = 0;
Map maxColumnWidths = new HashMap<>();

for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
Sheet sourceSheet = workbook.getSheetAt(s);
int startRow = 0;
int sheetStartTargetRow = targetRowNum;

for (int r = startRow; r <= sourceSheet.getLastRowNum(); r++) {
Row sourceRow = sourceSheet.getRow(r);
if (sourceRow == null) continue;

Row targetRow = storeReportSheet.createRow(targetRowNum++);
targetRow.setHeight(sourceRow.getHeight());

for (int c = 0; c <= sourceRow.getLastCellNum(); c++) {
Cell sourceCell = sourceRow.getCell(c);
if (sourceCell == null) continue;

Cell targetCell = targetRow.createCell(c);
copyCell(sourceCell, targetCell, targetWorkbook);

int columnWidth = sourceSheet.getColumnWidth(c);
maxColumnWidths.put(c, Math.max(columnWidth, maxColumnWidths.getOrDefault(c, 0)));
}
}

// 병합 영역 처리
List mergedRegions = sourceSheet.getMergedRegions();
for (CellRangeAddress mergedRegion : mergedRegions) {
int firstRow = mergedRegion.getFirstRow();
if (firstRow < startRow) continue;

int newFirstRow = sheetStartTargetRow + (firstRow - startRow);
int newLastRow = sheetStartTargetRow + (mergedRegion.getLastRow() - startRow);
CellRangeAddress newRegion = new CellRangeAddress(
newFirstRow,
newLastRow,
mergedRegion.getFirstColumn(),
mergedRegion.getLastColumn()
);
storeReportSheet.addMergedRegion(newRegion);
}

// 조건 형식 처리
SheetConditionalFormatting sourceCF = sourceSheet.getSheetConditionalFormatting();
SheetConditionalFormatting targetCF = storeReportSheet.getSheetConditionalFormatting();
for (int i = 0; i < sourceCF.getNumConditionalFormattings(); i++) {
ConditionalFormatting formatting = sourceCF.getConditionalFormattingAt(i);
CellRangeAddress[] ranges = formatting.getFormattingRanges();

CellRangeAddress[] adjustedRanges = new CellRangeAddress[ranges.length];
for (int j = 0; j < ranges.length; j++) {
CellRangeAddress oldRange = ranges[j];
adjustedRanges[j] = new CellRangeAddress(
oldRange.getFirstRow() + sheetStartTargetRow,
oldRange.getLastRow() + sheetStartTargetRow,
oldRange.getFirstColumn(),
oldRange.getLastColumn()
);
}

ConditionalFormattingRule[] adjustedRules = new ConditionalFormattingRule[formatting.getNumberOfRules()];
for (int i1 = 0; i1 < formatting.getNumberOfRules(); i1++) {
adjustedRules[i1] = formatting.getRule(i1);
}

targetCF.addConditionalFormatting(adjustedRanges, adjustedRules);
}
}

maxColumnWidths.forEach((col, width) -> storeReportSheet.setColumnWidth(col, width));
targetWorkbook.write(outputStream);
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
return outputStream;
}

private static void copyCell(Cell sourceCell, Cell targetCell, Workbook targetWorkbook) {
switch (sourceCell.getCellType()) {
case STRING:
targetCell.setCellValue(sourceCell.getStringCellValue());
break;
case NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case FORMULA:
targetCell.setCellFormula(sourceCell.getCellFormula());
break;
case BLANK:
targetCell.setBlank();
break;
default:
targetCell.setCellValue("");
}

CellStyle sourceStyle = sourceCell.getCellStyle();
CellStyle targetStyle = targetWorkbook.createCellStyle();
cloneCellStyle(sourceStyle, targetStyle, sourceCell.getSheet().getWorkbook(), targetWorkbook);
targetCell.setCellStyle(targetStyle);
}

private static void cloneCellStyle(CellStyle sourceStyle, CellStyle targetStyle, Workbook sourceWorkbook, Workbook targetWorkbook) {
targetStyle.cloneStyleFrom(sourceStyle);

Font sourceFont = sourceWorkbook.getFontAt(sourceStyle.getFontIndex());
Font targetFont = findOrCreateFont(targetWorkbook, sourceFont);
targetStyle.setFont(targetFont);

targetStyle.setAlignment(sourceStyle.getAlignment());
targetStyle.setVerticalAlignment(sourceStyle.getVerticalAlignment());
targetStyle.setBorderTop(sourceStyle.getBorderTop());
targetStyle.setBorderRight(sourceStyle.getBorderRight());
targetStyle.setBorderBottom(sourceStyle.getBorderBottom());
targetStyle.setBorderLeft(sourceStyle.getBorderLeft());
targetStyle.setTopBorderColor(sourceStyle.getTopBorderColor());
targetStyle.setRightBorderColor(sourceStyle.getRightBorderColor());
targetStyle.setBottomBorderColor(sourceStyle.getBottomBorderColor());
targetStyle.setLeftBorderColor(sourceStyle.getLeftBorderColor());
targetStyle.setFillPattern(sourceStyle.getFillPattern());

Color sourceFgColor = sourceStyle.getFillForegroundColorColor();
if (!ObjectUtils.isEmpty(sourceFgColor)) {
byte[] rgb = ((XSSFColor) sourceFgColor).getRGB();
((XSSFCellStyle) targetStyle).setFillForegroundColor(new XSSFColor(rgb, null));
}
}

private static Font findOrCreateFont(Workbook targetWorkbook, Font sourceFont) {
Font font = targetWorkbook.findFont(
sourceFont.getBold(),
sourceFont.getColor(),
sourceFont.getFontHeight(),
sourceFont.getFontName(),
sourceFont.getItalic(),
sourceFont.getStrikeout(),
sourceFont.getTypeOffset(),
sourceFont.getUnderline()
);
if (font == null) {
font = targetWorkbook.createFont();
font.setBold(sourceFont.getBold());
font.setColor(sourceFont.getColor());
font.setFontHeight(sourceFont.getFontHeight());
font.setFontName(sourceFont.getFontName());
font.setItalic(sourceFont.getItalic());
font.setStrikeout(sourceFont.getStrikeout());
font.setTypeOffset(sourceFont.getTypeOffset());
font.setUnderline(sourceFont.getUnderline());
}
return font;
}

static class FontKey {
private final short fontIndex;

FontKey(short fontIndex) {
this.fontIndex = fontIndex;
}

@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
FontKey fontKey = (FontKey) o;
return fontIndex == fontKey.fontIndex;
}

@Override
public int hashCode() {
return Objects.hash(fontIndex);
}
}