엑셀 다운로드를 위한 엑셀 생성 로직 리팩토링 : 시트 자동 생성 및 처리 속도 개선

https://cdaosldk.tistory.com/346

엑셀 다운로드를 위한 엑셀 생성 로직 리팩토링 : 속도 개선

 

이전에 작성했던 로직에서 개선한 부분을 정리해본다

0. 개선한 전체 코드

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;
import project.document.domain.excel.annotation.ExcelColumn;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcelService {

    private static final int MAX_ROWS_PER_SHEET = 1048500;
    private static final int ROW_ACCESS_WINDOW_SIZE = 100;
    private static final String STYLE_INTEGER = "integer";
    private static final String STYLE_DECIMAL = "decimal";
    private static final String STYLE_HEADER = "header";

    public <T> byte[] generateExcelFile(String sheetName, int minWidth, List<T> dataList) throws IOException, IllegalAccessException {
        if (dataList == null || dataList.isEmpty()) {
            throw new IllegalArgumentException("데이터 목록이 비어있습니다.");
        }

        // 1. 대상 클래스의 필드 중 @ExcelColumn 애너테이션이 붙은 필드 추출 및 정렬
        Class<?> clazz = dataList.getFirst().getClass();
        List<Field> excelFields = Arrays.stream(clazz.getDeclaredFields())
                .filter(field -> field.isAnnotationPresent(ExcelColumn.class))
                .sorted(Comparator.comparingInt(field -> field.getAnnotation(ExcelColumn.class).order()))
                .toList();

        try (SXSSFWorkbook workbook = new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE);
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            workbook.setCompressTempFiles(true);
            Map<String, CellStyle> styles = createCommonCellStyles(workbook);

            Sheet currentSheet = null;
            int currentSheetIndex = 1;
            int currentRowNum = 0;

            for (T data : dataList) {
                if (currentSheet == null || currentRowNum >= MAX_ROWS_PER_SHEET) {
                    if (currentSheet != null) {
                        adjustColumnWidth(excelFields, minWidth, currentSheet);
                    }

                    String sheetNameNew = currentSheetIndex == 1 ? sheetName : sheetName + "_" + currentSheetIndex;
                    currentSheet = workbook.createSheet(sheetNameNew);

                    createHeaderRow(currentSheet, excelFields, styles.get(STYLE_HEADER));
                    currentRowNum = 1;
                    currentSheetIndex++;
                }

                createDataRow(currentSheet, currentRowNum++, data, excelFields, styles);
            }

            if (currentSheet != null) {
                adjustColumnWidth(excelFields, minWidth, currentSheet);
            }

            workbook.write(out);
            return out.toByteArray();
        }
    }

    private Map<String, CellStyle> createCommonCellStyles(Workbook workbook) {
        Map<String, CellStyle> styles = new HashMap<>();

        // Header Style
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        styles.put(STYLE_HEADER, headerStyle);

        // Integer Style
        CellStyle integerStyle = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        integerStyle.setDataFormat(format.getFormat("#,##0"));
        styles.put(STYLE_INTEGER, integerStyle);

        // Decimal Style
        CellStyle decimalStyle = workbook.createCellStyle();
        decimalStyle.setDataFormat(format.getFormat("#,##0.00")); // Example format for decimals
        styles.put(STYLE_DECIMAL, decimalStyle);

        return styles;
    }

    private void createHeaderRow(Sheet sheet, List<Field> fields, CellStyle headerStyle) {
        Row headerRow = sheet.createRow(0);
        int cellIndex = 0;
        for (Field field : fields) {
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            Cell cell = headerRow.createCell(cellIndex++);
            cell.setCellValue(annotation.headerName());
            cell.setCellStyle(headerStyle);
        }
    }

    private <T> void createDataRow(Sheet sheet, int rowNum, T data, List<Field> fields, Map<String, CellStyle> styles) throws IllegalAccessException {
        Row dataRow = sheet.createRow(rowNum);
        int cellIndex = 0;
        for (Field field : fields) {
            Cell cell = dataRow.createCell(cellIndex++);
            Object value = field.get(data); // 리플렉션을 활용해 런타임에 필드값 획득
            setCellValue(cell, value, styles);
        }
    }

    private void setCellValue(Cell cell, Object value, Map<String, CellStyle> styles) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String stringValue) {
            cell.setCellValue(stringValue);
        } else if (value instanceof Number numValue) {
            if (value instanceof Integer || value instanceof Long) {
                cell.setCellValue(numValue.longValue());
                cell.setCellStyle(styles.get(STYLE_INTEGER));
            } else {
                cell.setCellValue(numValue.doubleValue());
                cell.setCellStyle(styles.get(STYLE_DECIMAL));
            }
        } else {
            cell.setCellValue(value.toString());
        }
    }

    private void adjustColumnWidth(List<Field> fields, int minWidth, Sheet sheet) {
        int colIndex = 0;
        for (Field field : fields) {
            String headerName = field.getAnnotation(ExcelColumn.class).headerName();
            int calculatedWidth = Math.max(minWidth, headerName.length() * 2 + 5);
            sheet.setColumnWidth(colIndex, calculatedWidth * 256);
            colIndex++;
        }
    }
}

1. 시트의 최대 데이터 크기를 초과한 경우 : 새로운 시트 생성

...
private static final int MAX_ROWS_PER_SHEET = 1048500;
...
    Sheet currentSheet = null;
    int currentSheetIndex = 1;
    int currentRowNum = 0;
    if (currentSheet == null || currentRowNum >= MAX_ROWS_PER_SHEET) {
        if (currentSheet != null) {
            adjustColumnWidth(excelFields, minWidth, currentSheet);
        }

        String sheetNameNew = currentSheetIndex == 1 ? sheetName : sheetName + "_" + currentSheetIndex;
        currentSheet = workbook.createSheet(sheetNameNew);

        createHeaderRow(currentSheet, excelFields, styles.get(STYLE_HEADER));
        currentRowNum = 1;
        currentSheetIndex++;
}
...

데이터의 로우 수가 한 시트의 최대 값을 넘기는 경우, 새로운 시트를 생성하는 로직을 추가

2. ExcelColumn 커스텀 인터페이스를 통한 엑셀 컬럼 관리 개선

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
    String headerName();
    int order() default 0;
}

커스텀 인터페이스를 생성하고,

 

import project.document.domain.excel.annotation.ExcelColumn;

public record UserResponseDto
    (
    @ExcelColumn(headerName = "사용자 ID", order = 1)
    Long id,
    @ExcelColumn(headerName = "사용자 이름", order = 2)
    String userName,
    @ExcelColumn(headerName = "사용자 이메일", order = 3)
    String email)
{}

이런 방식으로 적용해주면, 별도의 map 없이도 엑셀에 어떤 데이터를 보여줄지 관리가 가능하다

 

실제 작동 코드

public <T> byte[] generateExcelFile(String sheetName, int minWidth, List<T> dataList) throws IOException, IllegalAccessException {
        if (dataList == null || dataList.isEmpty()) {
            throw new IllegalArgumentException("데이터 목록이 비어있습니다.");
        }

        // 1. 대상 클래스의 필드 중 @ExcelColumn 애너테이션이 붙은 필드 추출 및 정렬
        Class<?> clazz = dataList.getFirst().getClass();
        List<Field> excelFields = Arrays.stream(clazz.getDeclaredFields())
                .filter(field -> field.isAnnotationPresent(ExcelColumn.class))
                .sorted(Comparator.comparingInt(field -> field.getAnnotation(ExcelColumn.class).order()))
                .toList();
...
private void createHeaderRow(Sheet sheet, List<Field> fields, CellStyle headerStyle) {
        Row headerRow = sheet.createRow(0);
        int cellIndex = 0;
        for (Field field : fields) {
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            Cell cell = headerRow.createCell(cellIndex++);
            cell.setCellValue(annotation.headerName());
            cell.setCellStyle(headerStyle);
        }
    }
    
private <T> void createDataRow(Sheet sheet, int rowNum, T data, List<Field> fields, Map<String, CellStyle> styles) throws IllegalAccessException {
    Row dataRow = sheet.createRow(rowNum);
    int cellIndex = 0;
    for (Field field : fields) {
        Cell cell = dataRow.createCell(cellIndex++);
        Object value = field.get(data); // 리플렉션을 활용해 런타임에 필드값 획득
        setCellValue(cell, value, styles);
    }
}

1) 리플렉션으로 dataList에 담겨있는 클래스의 정보를 가져온다

2) 그 클래스에서 커스텀 인터페이스로 지정된 컬럼의 정보를 가져온다

3) 컬럼의 정보를 가진 fields 리스트에 맞춰 헤더와 cell 데이터를 각 cell로 생성 및 데이터 형식에 맞게 편집한다

3. workbook 방식 정리 : XSSF vs SXSSF

출처 : https://ghan2.tistory.com/71

 

1. XSSF

쓰기: XSSF는 엑셀 파일을 생성하거나 수정할 때, 모든 데이터를 메모리에 로드한 상태에서 작업한다. 이 말은 파일의 모든 행과 셀을 메모리에 올려두고 필요에 따라 수정하거나 새로운 데이터를 추가한다는 뜻이다. 메모리에 모든 데이터를 유지하기 때문에, 파일 크기가 크면 메모리 사용량이 매우 높아질 수 있다.

읽기: XSSF는 전체 엑셀 파일을 메모리에 로드하기 때문에, 파일의 모든 셀과 행에 빠르게 접근할 수 있다. 이 점은 유용하지만 파일이 클수록 메모리 사용량이 많아지고, 이로 인해 성능이 저하될 수 있다

 

2. SXSSF

쓰기: SXSSF는 XSSF와 달리 데이터를 메모리에 한꺼번에 올리지 않고, 슬라이딩 윈도우 방식으로 올리기 때문에 메모리 사용량을 크게 줄일 수 있다. 하지만 기존에 작업한 데이터가 슬라이딩 윈도우에서 벗어나면 더 이상 수정할 수 없고, 디스크에 기록되어 저장한다. 즉, 한번쓴 데이터를 다시 수정하려면, 이미 기록된 파일을 읽어야 한다. 

읽기: SXSSF는 기본적으로 대용량 파일의 쓰기에 최적화된 라이브러리로 설계되었기 때문에, 읽기 작업은 잘 지원하지 않는다. SXSSF로 작성한 파일을 읽으려면, 파일을 디스크에 기록한 후 XSSF로 다시 읽어들여야 한다. 그래서 SXSSF는 읽기 작업보다는 쓰기 작업에 적합하다. 

 

이 때문에, SXSSF는 메모리 제약이 있는 환경에서 대용량 엑셀 파일을 작성할 때 주로 사용하고, XSSF는 메모리 사용량이 문제가 되지 않거나, 파일을 읽고 수정하는 작업이 필요한 경우에 사용한다