스프링프레임웍에서 POI를 사용한 대용량 엑셀 다운로드

업무중에 데이터를 엑셀파일로 내려 받아야 할 경우가 많이 있습니다. 이럴때 데이터량이 적을 경우는 문제가 되지 않지만, 다운 받아야 할 데이터가 수만건에서 수십만건 이상 된다면 여러가지 문제가 발생합니다.

 

첫 번째로 엑셀 자체의 한계가 있습니다. xls 형식의 엑셀 파일은 한 시트에 최대 65,535행의 데이터를 가질 수 있습니다. xlsx 형태의 엑셀 파일은 한 시트에 최대 1,048,576행의 데이터를 가질 수 있습니다. 현업에서 쓰고 있는 엑셀 버전과 다운로드 되는 최대 데이터량에 따라 어떤 방식을 사용할지 결정하게 해야 하겠습니다.

 

두 번째로 엑셀 라이브러리를 사용해서 엑셀 파일을 생성할 때 메모리 상에서 작업을 하게 되면 대용량 다운로드 중에 메모리 부족상황에 빠지거나 시스템이 현저히 느려지는 경우가 발생할 수 있습니다.

 

세 번째는 역시 메모리 관련된 상황인데, 데이터베이스에서 대용량 데이터를 조회해서 List 등에 담아두고 엑셀 파일 생성 작업을 하게 된다면 메모리 부족상황에 빠질 수 있습니다.

 

네 번째는 사용자 경험과 관련된 것으로, 대용량 데이터 다운로드시 엑셀 파일을 생성하는데 시간이 많이 소요되고, 생성된 파일을 다운로드 받는데도 시간이 걸립니다. 파일을 다운로드 받기 시작하면 대부분 브라우저가 파일을 저장할지, 바로 열지 물어본 후 다운로드를 하고 진행 상황을 보여줍니다. 문제는 다운로드 요청을 받아 파일이 생성을 시작해서 다운로드가 시작되기전까지의 시간이 많이 걸리는 상황입니다. 이때는 웹의 특성상 진행 상황을 보여주기가 힘듭니다.

 

 

스프링프레임웍 + MyBatis + Oracle 환경에서 대용량 엑셀 다운로드를 구현하는 방법에 대해서 알아보도록 하겠습니다.

 

1. 엑셀 파일은 xlsx 를 사용해서 최대한의 데이터를 표현할 수 있도록 합니다.

 

2. 엑셀 라이브러리는 POI를 사용합니다. POI 에 있는 SXSSFWorkbook은 엑셀 파일 생성시 메모리상에 지정된 숫자의 행만을 유지하고, 초과되는 행은 파일에 적어 메모리 사용량을 줄일 수 있게 해줍니다. 단점은 현재 메모리에 있지 않고 파일에 적혀 버린 행에 대해서는 액세스를 할 수 없습니다. 총계 등을 엑셀 파일 앞단에 적어야할 경우 미리 계산해서 앞에서 적어야만 합니다.

 

3. MyBatis를 통해서 데이터베이스에서 데이터를 꺼낼 때 한번에 데이터를 꺼내는 fetch 사이즈를 지정하여 메모리 사용량을 줄입니다. 이 방법은 데이터베이스에 따라 동작하지 않거나, 설정하는 값이 달라질 수도 있습니다.

 

4. 파일이 생성되기까지 화면에 진행막대를 보여주기 위해서 jQuery UI 와 jQuery File Download Plugin 을 사용합니다. 이 진행막대도 진행율을 보여주지는 못합니다. jQuery File Download 플러그인은 쿠키를 사용해서 클라이언트에게 파일생성이 끝나고 다운로드가 시작됨을 알립니다. (https://github.com/johnculviner/jquery.fileDownload)

 

 

이제 소스코드를 보도록 하겠습니다. 샘플 프로젝트는 "스프링 프레임웍에서 MyBatis, Oracle 사용하기" 에서 사용된 프로젝트를 기반으로 하고 있습니다.  전체소스는 이 글 하단에 첨부되어 있습니다.

 

 

1. POI 엑셀 라이브러리를 사용하기 위한 의존성을 추가합니다. pom.xml 파일 입니다.

 

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

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.16</version>
</dependency>

 

2. MyBatis용 sqlmap 파일 입니다.  Tb_Board_SQL.xml 파일 입니다.

 

fetchSize="100" 부분이 데이터베이스에서 한번에 100행씩 fetch 하도록 지정하는 부분입니다. 

 

<select id="selectExcelList" fetchSize="100" resultType="boardVO">
    SELECT NUM
         , NAME
         , TITLE
         , CONTENT
         , READ_COUNT
         , WRITE_DATE
      FROM TB_BOARD
     WHERE TITLE LIKE '%'||#{word}||'%'
     ORDER BY NUM DESC
</select>

 

3. 엑셀 다운로드 클라이언트측 소스코드 입니다. home.jsp 파일 입니다.

 

- jQuery UI, jQuery, File Download Plugin을 포함시키는 부분입니다.

 

<link rel="stylesheet" href="<c:url value='/js/jquery-ui-1.12.0/jquery-ui.min.css'/>"/>
<script src="<c:url value="/js/jquery-3.1.0.min.js"/>"></script>
<script src="<c:url value="/js/jquery-ui-1.12.0/jquery-ui.min.js"/>"></script>
<script src="<c:url value="/js/jquery.fileDownload.js"/>"></script>

 

- "엑셀 다운로드" 버튼 클릭시 진행막대 다이얼로그를 띄우고 다운로드 요청 후 응답에 따라 성공시 다운로드가 진행되고, 실패시 에러메세지 다이얼로그를 띄웁니다.

 

<script type="text/javascript">
//<![CDATA[
$(function() {
  $("#btn-excel").on("click", function () {
    var $preparingFileModal = $("#preparing-file-modal");
    $preparingFileModal.dialog({ modal: true });
    $("#progressbar").progressbar({value: false});
    $.fileDownload("/pentode/excel.do", {
      successCallback: function (url) {
        $preparingFileModal.dialog('close');
      },
      failCallback: function (responseHtml, url) {
        $preparingFileModal.dialog('close');
        $("#error-modal").dialog({ modal: true });
      }
    });

    // 버튼의 원래 클릭 이벤트를 중지 시키기 위해 필요합니다.
    return false;
  });
});
//]]>
</script>

 

4. 엑셀 다운로드 버튼과 필요한 다이얼로그를 생성할 요소들 입니다.

 

<button id="btn-excel">엑셀 다운로드</button>

<!-- 파일 생성중 보여질 진행막대를 포함하고 있는 다이얼로그 입니다. -->
<div title="Data Download" id="preparing-file-modal" style="display: none;">
	<div id="progressbar" style="width: 100%; height: 22px; margin-top: 20px;"></div>
</div>

<!-- 에러발생시 보여질 메세지 다이얼로그 입니다. -->
<div title="Error" id="error-modal" style="display: none;">
	<p>생성실패.</p>
</div>

 

5. POI의 SXSSFWorkbook를 사용해서 엑셀을 생성해서 다운로드하는 서비스 소스코드 입니다. BoardServiceImpl.java 파일 입니다.

 

@Autowired
private SqlSessionFactory sqlSessionFactory;

@Override
@Transactional
public void selectExcelList(HttpServletResponse response) {

    SqlSession sqlSession = sqlSessionFactory.openSession();

    // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다.
    SXSSFWorkbook wb = new SXSSFWorkbook(100);
    Sheet sheet = wb.createSheet();

    try {
        sqlSession.select("selectExcelList", "게시물", new ResultHandler<BoardVO>() {
            @Override
            public void handleResult(ResultContext<? extends BoardVO> context) {
                BoardVO vo = context.getResultObject();
                Row row = sheet.createRow(context.getResultCount() - 1);
                Cell cell = null;
                cell = row.createCell(0);
                cell.setCellValue(vo.getNum().toString());
                cell = row.createCell(1);
                cell.setCellValue(vo.getTitle());
                cell = row.createCell(2);
                cell.setCellValue(vo.getContent());
            }
        });

        response.setHeader("Set-Cookie", "fileDownload=true; path=/");
        response.setHeader("Content-Disposition", String.format("attachment; filename=\"test.xlsx\""));
        wb.write(response.getOutputStream());

    } catch(Exception e) {

        response.setHeader("Set-Cookie", "fileDownload=false; path=/");
        response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
        response.setHeader("Content-Type","text/html; charset=utf-8");

        OutputStream out = null;
        try {
            out = response.getOutputStream();
            byte[] data = new String("fail..").getBytes();
            out.write(data, 0, data.length);
        } catch(Exception ignore) {
            ignore.printStackTrace();
        } finally {
            if(out != null) try { out.close(); } catch(Exception ignore) {}
        }

    } finally {
        sqlSession.close();

        // 디스크 적었던 임시파일을 제거합니다.
        wb.dispose();
        try { wb.close(); } catch(Exception ignore) {}
    }
}

 

- 엑셀 파일 생성을 위해 SXSSFWrokbook 객체를 생성합니다. 생성자의 인자로 메모리에 유지할 행의 수를 지정합니다.

 

SXSSFWorkbook wb = new SXSSFWorkbook(100);

 

- 데이터 조회를 위해 SqlSession 객체의 select 메소드를 사용합니다. 기존의 DAO를 사용하지 않는 이유는 ResultHandler를 사용해서 데이터베이스에서 행을 패치하면서 바로 엑셀 생성을 처리하기 위해서 입니다.

 

select 메서드의 첫번째 인자는 sqlmap 내의 id 값입니다. 두 번째 인자는 쿼리로 보낼 데이터 입니다. 여기서는 조회시 like 조건에 사용할 String을 보내고 있지만, VO 객체, Map 객체 등을 사용해서 여러개의 데이터를 보낼 수 있습니다. 세 번째 인자가 ResultHandler 입니다. 여기에 보내진 객체의 handleResult 메소드에서 엑셀 생성 작업을 한 행씩 수행합니다. 여기서는 익명 객체를 사용했지만 별도로 클래스를 만들어도 됩니다.

 

sqlSession.select("selectExcelList", "게시물", new ResultHandler<BoardVO>() {     @Override     public void handleResult(ResultContext<? extends BoardVO> context) {          // 데이터베이스에서 패치된 객체         BoardVO vo = context.getResultObject();         // 현재 처리중인 행번호(1부터 시작됨.)         Row row = sheet.createRow(context.getResultCount() - 1);     } });

 

- 생성 성공시 처리부분입니다. 쿠키로 fileDownload 값을 true로 주면 jQuery File Download 플러그인 에서 성공으로 인식합니다. SXSSFWorkbook의 write 메소드를 사용해서 생성된 파일을 클라이언트에게 보냅니다.

 

response.setHeader("Set-Cookie", "fileDownload=true; path=/"); response.setHeader("Content-Disposition", String.format("attachment; filename=\"test.xlsx\"")); wb.write(response.getOutputStream());

 

- 파일 생성 실패시의 처리 입니다. 쿠키로 fileDownload 의 값을 true 이외의 값을 주면 jQuery File Download 플러그인이 실패로 인식해서 에러메세지 다이얼로그를 보여줍니다.

 

response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8");

 

- SXSSFWorkbook을 dipose 하면 엑셀파일 생성을 위해서 임시로 만들어 졌던 파일을 제거합니다.

 

wb.dispose();

 

 

6. 실행결과 입니다.

 

 

 

※ 전체소스

spring_excel.zip

다운로드