Spring Boot Excel Download 2편

다시 엑셀 다운로드를 만들어보자

---

엑셀 다운로드 1편 에서는 기본적인 엑셀 다운로드를 살펴보았습니다. 지금부터는 1편의 문제점을 어떻게 해결할 수 있을까? 라는 주제입니다. 해당 글은 개인적인 경험이니 피드백을 주시면 감사하겠습니다.

기존 엑셀 다운로드 API에 부하 테스트를 진행하는 도중 서버가 잠시동안 멈춰버리는 현상이 겪어 heap dump를 하여 구글링을 통해 알게된 내용을 공유합니다.

 

🤔 문제점을 어떻게 해결할 수 있을까?

 

• heap dump를 통해 구글링을 하던 중 비슷한 경험을 했던 블로그를 찾았는데 해당 글에서 애플리케이션의 메모리에 한꺼번에 많은 양을 가져오게 된다면 문제가 발생한다라는 내용을 알게되었습니다. 이 해결방안을 토대로 페이징을 적용하고 주기적으로 내용을 flush 해주면 괜찮아지겠지? 라는 생각을 토대로 작업을 진행하였으며, 다시 부하테스트를 했을 때 똑같은 현상은 발생하지 않고 정상적으로 서버가 작동함과 동시에 GC도 바쁘게 일을 하는것을 알 수 있었습니다.

 

💡 컨트롤러에 페이징을 적용하자.

 

• 기존 컨트롤러에 페이징을 적용합니다. 엑셀을 다운로드 받는 과정은 총 3단계에 걸쳐 이루어집니다.
• 1단계는 Excel과 커넥션을 맺습니다.
• 2단계는 페이징을 적용하기 위해 총 데이터의 갯수를 가져옵니다.
• 3단계는 하나의 시트당 5000개의 데이터를 그려주기 위해 반복문을 순회하며 데이터를 DB로부터 가져오고, Excel에 그려줍니다. 또한 GC가 데이터를 잘 수거할 수 있도록 clear 메서드를 호출합니다. (0 ~ 5000, 5000 ~ 5000, 10000 ~ 5000 ...)
• 4단계는 위 과정을 통해 그려준 엑셀을 다운로드할 수 있도록 합니다.

@RestController
@RequestMapping("/members")
@RequiredArgsConstructor
public class MemberController {

    private static final int MAX_ROW = 5000;

    private final ExcelUtilsV2 excelUtilsV2;
    private final MemberDao memberDao;
    
    @GetMapping(value = "/excel/downloadV2")
    public void excelDownloadV2(HttpServletResponse response) {
        int index = 0;
        
        // 1. Excel과 연결을 맺습니다.
        excelUtilsV2.connect(response);
        
        // 2. 페이징을 적용하기 위해 총 갯수를 가져옵니다.
        Long totalElementCount = memberDao.totalCount();
        
        // 3. 데이터를 5000개씩 페이징하여 엑셀을 그려줍니다.
        List<MemberExcelDownload> result = new ArrayList<>();
        for (int i = 0; i < totalElementCount; i += MAX_ROW) {
        
            // 페이징을 사용하여 데이터를 가져옴과 동시에 excel에 그려주고 GC가 수거를 잘 할 수 있도록 list를 clear합니다.
            result = memberDao.excelDownloadByPaging((index * MAX_ROW), MAX_ROW);
            excelUtilsV2.draw(index, MemberExcelDownload.class, result);
            index++;
            result.clear();
        }
        
        // 4. 위의 과정이 끝났으면 다운로드를 수행합니다.
        excelUtilsV2.download("download");
    }
}

 

💡 DB로부터 데이터를 가져오자.

 

• 컨트롤러로부터 offset과 limit를 전달받아 페이징을 적용하여 데이터를 가져옵니다. 반환받는 DTO 클래스는 1편에서 선언한겅화 동일합니다.

@Service
@Transactional(readOnly = true)
@RequiredArgsConstructor
public class MemberDao {

    private final JPAQueryFactory queryFactory;

    public List<MemberExcelDownload> excelDownloadByPaging(int offset, int limit) {
        return queryFactory
                .select(new QMemberExcelDownload(
                        member.name,
                        member.phone,
                        member.gender,
                        member.address.zipCode,
                        member.address.address,
                        member.address.addressDetail
                ))
                .from(member)
                .offset(offset)
                .limit(limit)
                .fetch();
    }

    public Long totalCount() {
        return queryFactory
                .select(member.count())
                .from(member)
                .fetchFirst();
    }
}

 

💡 엑셀 인터페이스에서 단계를 나눕니다.

 

• 컨트롤러에서 엑셀을 다운로드 하기 위해 연결단계, 그려주는 단계, 다운로드 단계로 구분을 하였습니다. 

public interface ExcelSupportV2 {

    void connect(HttpServletResponse response);
    void draw(int sheetNum, Class<?> clazz, List<?> data);
    void download(String fileName);
}

 

💡 엑셀 유틸 클래스를 구현하자.

 

• 아래는 엑셀 다운로드를 관리하는 유틸 클래스입니다. 지금 부토터 하나의 메서드마다 어떤 역할을 수행하는지 알아보겠습니다.
• 중요한 부분은 connect, draw, download, getWorkBook 메서드이므로 해당 메서드만 살펴보겠습니다.

@Slf4j
@Component
public final class ExcelUtilsV2 implements ExcelSupportV2 {

    private static final int MAX_ROW = 5000;
    private SXSSFWorkbook workbook;
    private HttpServletResponse response;

    @Override
    public void connect(HttpServletResponse response) {
        workbook = new SXSSFWorkbook(-1);
        this.response = response;
    }

    @Override
    public void draw(int sheetNum, Class<?> clazz, List<?> data) {
        try {
            getWorkBook(sheetNum, clazz, findHeaderNames(clazz), data);
            data.clear();
        } catch (IOException | IllegalAccessException e) {
            log.error("Excel Download Error Message = {}", e.getMessage());
            throw new RuntimeException(e);
        }
    }

    @Override
    public void download(String fileName) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

        try (ServletOutputStream outputStream = response.getOutputStream()) {
            workbook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    private SXSSFWorkbook getWorkBook(int sheetNum, Class<?> clazz, List<String> headerNames, List<?> data) throws IllegalAccessException, IOException {
        // 각 시트 당 MAX_ROW 개씩
        String sheetName = "Sheet" + (sheetNum + 1);

        Sheet sheet = ObjectUtils.isEmpty(this.workbook.getSheet(sheetName)) ? this.workbook.createSheet(sheetName) : workbook.getSheet(sheetName);
        sheet.setDefaultColumnWidth((short) 300);   // 디폴트 너비 설정
        sheet.setDefaultRowHeight((short) 500);     // 디폴트 높이 설정

        Row row = null;
        Cell cell = null;

        row = sheet.createRow(0);
        createHeaders(row, cell, headerNames);
        createBody(clazz, data, sheet, row, cell);

        // 주기적인 flush 진행
        ((SXSSFSheet) sheet).flushRows(MAX_ROW);
        return this.workbook;
    }

    private void createHeaders(Row row, Cell cell, List<String> headerNames) {
        /**
         * header font style
         */
        Font font = this.workbook.createFont();
        font.setColor((short) 255);

        /**
         * header cell style
         */
        CellStyle headerCellStyle = this.workbook.createCellStyle();
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);       // 가로 가운데 정렬
        headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 세로 가운데 정렬

        // 테두리 설정
        headerCellStyle.setBorderLeft(BorderStyle.MEDIUM);
        headerCellStyle.setBorderRight(BorderStyle.MEDIUM);
        headerCellStyle.setBorderTop(BorderStyle.MEDIUM);
        headerCellStyle.setBorderBottom(BorderStyle.MEDIUM);

        // 배경 설정
        headerCellStyle.setFillForegroundColor((short) 102);
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerCellStyle.setFont(font);

        for (int i = 0, size = headerNames.size(); i < size; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(headerCellStyle);
            cell.setCellValue(headerNames.get(i));
        }
    }

    private void createBody(Class<?> clazz, List<?> data, Sheet sheet, Row row, Cell cell) throws IllegalAccessException {
        int startRow = 0;
        for (Object o : data) {
            List<Object> fields = findFieldValue(clazz, o);
            row = sheet.createRow(++startRow);
            for (int i = 0, fieldSize = fields.size(); i < fieldSize; i++) {
                cell = row.createCell(i);
                cell.setCellValue(String.valueOf(fields.get(i)));
            }
        }
    }

    /**
     * 엑셀의 헤더 명칭을 찾는 로직
     */
    private List<String> findHeaderNames(Class<?> clazz) {
        return Arrays.stream(clazz.getDeclaredFields())
                .filter(field -> field.isAnnotationPresent(ExcelColumnName.class))
                .map(field -> field.getAnnotation(ExcelColumnName.class).name())
                .collect(Collectors.toList());
    }

    /**
     * 데이터의 값을 추출하는 메서드
     */
    private List<Object> findFieldValue(Class<?> clazz, Object obj) throws IllegalAccessException {
        List<Object> result = new ArrayList<>();
        for (Field field : clazz.getDeclaredFields()) {
            field.setAccessible(true);
            result.add(field.get(obj));
        }
        return result;
    }
}

 

    변수 설명

 

• 한 시트당 그려줄 데이터의 총 갯수를 의미하는 MAX_ROW가 있습니다.
• 컨트롤러에서 연결 단계에서 사용할 SXSSFWorkbook의 변수와 HttpServletResponse 변수가 있습니다.

@Slf4j
@Component
public final class ExcelUtilsV2 implements ExcelSupportV2{

    private static final int MAX_ROW = 5000;
    private SXSSFWorkbook workbook;
    private HttpServletResponse response;
}

 

    connect 메서드

 

• 해당 메서드는 유틸 클래스의 변수를 초기화해주는 역할을 수행합니다.

@Override
public void connect(HttpServletResponse response) {
    workbook = new SXSSFWorkbook(-1);
    this.response = response;
}

 

    draw 메서드

 

• 해당 메서드는 컨트롤러에서 엑셀을 그려주기 위해 호출되는 메서드입니다. 
• 내부 메서드인 getWorkBook 메서드에게 수행할 내용을 위임하고, 받아온 list를 clear하게 됩니다.

@Override
public void draw(int sheetNum, Class<?> clazz, List<?> data) {
    try {
        getWorkBook(sheetNum, clazz, findHeaderNames(clazz), data);
        data.clear();
    } catch (IOException | IllegalAccessException e) {
        log.error("Excel Download Error Message = {}", e.getMessage());
        throw new RuntimeException(e);
    }
}

 

    download 메서드

 

• 해당 메서드는 컨트롤러에서 최종적으로 엑셀을 다운로드하기 위해 호출되는 메서드입니다.
• 추가적으로 사용한 자원을 반납하게 됩니다.

@Override
public void download(String fileName) {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

    try (ServletOutputStream outputStream = response.getOutputStream()) {
        workbook.write(outputStream);
        outputStream.flush();
    } catch (IOException e) {
        throw new RuntimeException(e);
    } finally {
        try {
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}

 

    getWorkBook 메서드

 

• 해당 메서드는 시트의 헤더와 바디를 그려주는 메서드를 호출하고 주기적으로 flush를 진행하고 있습니다. 1편과 크게 차이점은 없지만 sheetName 변수를 만드는 과정이 살짝 다릅니다.

private SXSSFWorkbook getWorkBook(int sheetNum, Class<?> clazz, List<String> headerNames, List<?> data) throws IllegalAccessException, IOException {
    String sheetName = "Sheet" + (sheetNum + 1);

    Sheet sheet = ObjectUtils.isEmpty(this.workbook.getSheet(sheetName)) ? this.workbook.createSheet(sheetName) : workbook.getSheet(sheetName);
    sheet.setDefaultColumnWidth((short) 300);   // 디폴트 너비 설정
    sheet.setDefaultRowHeight((short) 500);     // 디폴트 높이 설정

    Row row = null;
    Cell cell = null;

    row = sheet.createRow(0);
    createHeaders(row, cell, headerNames);
    createBody(clazz, data, sheet, row, cell);

    // 주기적인 flush 진행
    ((SXSSFSheet) sheet).flushRows(MAX_ROW);
    return this.workbook;
}

 

깃허브

• https://github.com/kdg0209/excel-download