Excel Download With POI

1. POI Library

종류

1. HSSF : 라인수 65,535 제한, 빠름, xls 지원, 설치 간단
2. XSSF : 라인수 제한 없음, 느림, xlsx 지원, 설치 간단
3. SXSSF : 라인수 제한 없음, 빠름, xlsx 지원, 설치 복잡

 

2. XSSF와 SXSSF의 차이점

XSSF은 XML 기반으로 행 쓰기를 할 때, 메모리를 비우지 않아 메모리 부족의 이슈가 있다.

데이터가 많지 않을때는 유용하겠지만, 데이터가 많아질수록 메모리 부족 이슈의 가능성이 커진다.

 

SXSSF는 행 쓰기 시에 임시 파일을 생성하여 행 단위로  flush를 하기 때문에 메모리를 매우 적게 사용한다.

이에 XSSF의 Out of Memory 방지용으로 SXSSF를 사용하기도 한다.

SXSSF는 쓰기 전용이라 빈 행에 getRow를 사용하면 NPE(Null Point Exception)이 발생된다.

또한 비어있지 않은 행에 행 쓰기를 진행하려 할때도, Exception이 발생된다.

 

3. Maven Repository

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

 

4. SXSSF 코드

여기 프로젝트에서는 Response를 Filter에서 Handler라는 클래스들로 관리를 하기때문에 분리를 해놨지만.

이런 경우가 아니라면 굳이 분리를 하지않고 한번에 코드를 작성하면 된다.

파라미터인 data는 엑셀로 변환할 Api에서 가져온 Data List 이다.

public class Excel {
    static SXSSFSheet sheet = null;
    static int row_size = 500;

    public static ExcelDownload excelDownload(JArray data){
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook, row_size);
        sheet = sxssfWorkbook.createSheet();
        CellStyle cellStyle = sxssfWorkbook.createCellStyle();

        createHeader(data, cellStyle);
        addRowData(data);

        return new ExcelDownload(sxssfWorkbook);
    }

    public static void createHeader(JArray data, CellStyle cellStyle){
        SXSSFRow row = sheet.createRow(0);

        // 배경색상 지정
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        AtomicInteger columnCount = new AtomicInteger();
        data.getKeys().forEach(item -> {
            SXSSFCell cell = row.createCell(columnCount.getAndIncrement());
            cell.setCellStyle(cellStyle);
            cell.setCellValue(item);
        });
    }

    public static void addRowData(JArray data){
        try {
            AtomicInteger rowCount = new AtomicInteger(1);
            data.eachObject(item -> {
                SXSSFRow row = sheet.createRow(rowCount.getAndIncrement());

                AtomicInteger columnCount = new AtomicInteger();
                item.getKeys().forEach(key -> {
                    SXSSFCell cell = row.createCell(columnCount.getAndIncrement());
                    cell.setCellValue(item.getString(key));
                    
                    try {
                        if(columnCount.get() % row_size == 0) sheet.flushRows(row_size);
                    } catch (IOException e) {
                        throw new RuntimeException(e);
                    }
                });
            });
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}

 

public class ExcelDownloadResponseHandler implements ResponseHandler {
    @Override
    public void handle(HttpServletResponse response, Object object) {
        ExcelDownload excelDownload = (ExcelDownload) object;
        SXSSFWorkbook sxssfWorkbook = excelDownload.getSxssfWorkbook();

        try {
            response.setHeader("Content-Disposition", "attachment; filename=\"excel.xlsx\";");
            response.setContentType("application/msexcel");

            OutputStream outputStream = response.getOutputStream();
            sxssfWorkbook.write(outputStream);
            outputStream.close();

            response.getOutputStream().flush();
            response.getOutputStream().close();

            sxssfWorkbook.dispose();
        }catch (Exception e){
            throw new RuntimeException(e);
        }

    }
}