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

프로그래밍/스프링프레임워크 2018. 4. 4. 21:47
반응형

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


첫 번째로 엑셀 자체의 한계가 있습니다. 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

반응형

댓글을 달아 주세요

  • #{word} 2019.07.14 12:40  댓글주소  수정/삭제  댓글쓰기

    쿼리에서 #{word} 용도가 멀까요
    그리고 다운로드해서 파일열어보면
    4kb 짜리 엑셀파일만 다운받아지고
    아무내용이없는데 어떤부분을 고쳐될까요..?

    • pentode 2019.07.14 20:30 신고  댓글주소  수정/삭제

      #{word} 는 마이바티스에서 쿼리에 파라미터를 넣는 방법입니다.

      sqlSession.select("selectExcelList", "게시물", new ResultHandler<BoardVO>() {

      위 코드에서 "게시물" 이 #{word}에 대체 됩니다. 쿼리로 파라미터를 보내는 예시들 든 것입니다.

      내용이 없다면 테이블에 데이터가 있는지 확인해보세요. 데이터 넣고 커밋 꼭 해주세요.

      성공하시길 바라겠습니다.^^

  • #{word} 2019.07.15 13:42  댓글주소  수정/삭제  댓글쓰기

    이제 잘나옵니다ㅎ

  • Resulthandler 2019.09.25 11:38  댓글주소  수정/삭제  댓글쓰기

    보드서비스임플 가서 적용하면 Resulthandler 오류가 나는데
    Resulthandler 가 무엇인가요???
    import되어있는데도 오류나네요.
    그리고 dao가인터페이스만 있는데어떻게 연결된거죠??
    저는 daoimpl까지만들었습니다.

    • pentode 2019.09.25 14:01 신고  댓글주소  수정/삭제

      ResultHandler는 mybatis에서 데이터베이스에서 조회된 행을 하나씩 처리하기 위해서 제공하는 인터페이스 입니다. 이 예제에서는 익명 클래스로 만들어서 사용하고 있습니다.

      mybatis에서는 dao 인터페이스만 있으면 구현객체는 직접 만들지 않아도 됩니다. dao를 등록하는 MapperSannerConfigurer가 처리해 줍니다.

      첨부된 소스를 다운로드 받아서 실행해 보시고, 자신의 소스와 비교해 보세요.

      성공하시길 바라겠습니다.

  • shinji 2020.06.08 23:53  댓글주소  수정/삭제  댓글쓰기

    진행하는 프로젝트에 해당 기능이 필요하여 적용하였습니다.

    1. 필요한 기능은 대용량 액셀다운로드 시 부하 줄이기

    2. 사용자가 다운로드가 되고 있는지 몰라 다운로드 버튼을 계속 눌러 서버에서 계속 데이터를 조회하여 엑셀을 만드는 문제

    설명해주신 기능이 딱 맟는 솔루션이라 생각되어 적용하였습니다.

    적용 결과

    다운로드 시 다운로드 창이 뜨기전에 로딩바가 사라지는 문제가 발생하였습니다.

    서버에서는 엑셀을 구성하는 데이터 sql 조회가 이루어지고 있는데 setHeader의 쿠키설정값이 전달되어 로딩바가 사라집니다.

    제공해주신 예제소스와 다른점은 mybatis 대신 ibatis 로 구성된 시스템이라 ResultHandler 대신 RowHandler를 사용했습니다.

    ResultHandler와 RowHandler핸들러의 차이점인지 궁금합니다.

    다운되는 엑셀은 정상적으로 만들어집니다.

    • pentode 2020.06.11 22:08 신고  댓글주소  수정/삭제

      음. 핸들러가 조회해서 엑셀파일을 만들고 있는중에 쿠키값이 전송되어 버린다는 것인가요?

      프로그램 구조상 쓰레드를 쓰지 않으면 발생할수가 없는 상황인데요. 쿼리해서 엑셀 파일을 만드는것을 쓰레드로 처리하고 있는것이 아닌지요.

      제 글에 있는 예제로 한번 테스트해 보시기 바랍니다.

      성공하시길 바랄께요.^^

  • krukru_lol 2021.03.22 09:57  댓글주소  수정/삭제  댓글쓰기

    혹시 다운로드 하면 브라우저 하단에 어떤 파일이 다운로드 되었고 경로를 따로 설정할 수 있는 상태바(?)를 표시 하고싶은데
    따로 설정해주어야 하나요?
    제가 만든 다운로드 로직은 제가 지정한 폴더에 바로 다운로드 되는 상태입니다

    • pentode 2021.03.25 00:04 신고  댓글주소  수정/삭제

      구현하고자 하는 기능은 웹 프로그램으로 구현할 수 없을 것 같습니다.

      웹브라우저의 상태표시줄에 액세스 하는 방법은 예전에는 많이 사용했었는데, 요즘 브라우저에서는 보안 문제로 인해서 사용할 수 없습니다.

      다운로드된 파일이 저장되는 폴더를 지정하는 것도 브라우저의 기능으로 사용자가 변경할 수 있는 것이지 웹 프로그램으로 사용자 브라우저의 다운로드 폴더는 변경할 수 없습니다.

      심각한 보안문제가 발생할 수 있으므로 웹 프로그램이 브라우저를 제어하는 기능은 없다고 생각하면 되겠습니다.