POI라이브러리 + AJax + myBatis resultHandler 이용한 excel 대용량 다운로드 기능 구현(심플버전)_1편

안녕하세요. limky 입니다. 

최근 업무 중에 대용량 excel 다운로드를 구현할 일이 생겼는데요. 

제가 개인적으로 삽질하면서 구현한 excel 대용량 다운로드 기능을 어떻게 개발했는지 공유하고자 합니다.

 

우선,,,개발하게된 배경을 설명드리면, 기존에는 단순히 jsp에 table을 그리고 해당 table에 값을 매핑하여 excel 다운로드를 하였는데요. 최근들어 신규 상품 또는 신규 서비스가 출시되면서 이전과 달리 데이터가 많이 적재되었습니다. (하루에 3만건이상 적재됨...) 이로 인해 한꺼번에 많은 데이터를 읽고 쓰는 과정에서 서버에 과부하를 주었고 OOM을 발생시켜 심지어 서버가 다운되는 사태까지 일어났습니다. 이를 방지하고자 아래와 같은 3가지 해결책을 생각해보았습니다.

 

1. 화면에 있는 엑셀다운로드 버튼을 여러번 클릭하지 못하게 막자.

  -사용자가 실제로 여러번 버튼을 눌러서 다중 요청이 발생해 서버가 터졌습니다;;

  -엑셀 다운로드 요청을 받고 응답이 올때까지 버튼 클릭을 방지

 

2. 기존 Form-submit 및 jsp-table 방식의 엑셀다운로드가 아닌 Ajax 및 POI 라이브러리를 통해 처리하자.

  -굳이 Table을 그릴 필요가 없으며, 서버 응답을 받아 처리할 수 있도록 Ajax를 사용하자.

 

3. myBatis resultHandler를 통해 한번에 DB에서 많은 데이터를 읽지 않고, Row마다 읽고 처리하도록 구현하자.

  -한번에 DB에서 읽고 LIST에 담으면 OOM 발생이 높다.

  -myBatis에서 제공하는 resultHandler를 통해 row건별로 처리하여 OOM을 방지하자.

 

참고로, POI 라이브러리는 자바에서 엑셀다운로드 기능을 제공해주고, 크게 3가지 방식이 있습니다.

저는 가장 최근에 나온 SXSSF 를 사용하였습니다.

HSSF : 엑셀 97 ~ 2003버전, 65535 라인까지 사용가능
XSSF : 엑셀 2007버전이상, 65535 라인 이상 사용 가능

SXSSF : 가장 최근에 나온 성능개선버전

           XSSF의 Streaming Version으로 메모리를 적게 사용, 65535 라인 이상 사용 가능 

 

자.. 그럼 위 3가지 해결책을 어떻게 구현하였는지 하나씩 확인해보겠습니다.

 

STEP1. 라이브러리 추가(POI, XMLBeans, Collection4)

우선, 필요한 라이브러리를 직접 다운받아서 Maven에 추가했습니다. (제가 근무하는 환경이 폐쇄적인 환경이라..)

아래 아파치 공식사이트에서 Poi 라이브러리를 다운받으시고 pom.xml에 추가하시면 됩니다.

(다운받은 라이브러리는 /src/main/webapp/WEB-INF/lib/ 경로 아래 두었습니다.)

저는 자바 버전이 낮아서, poi 3.13을 다운로드 받았고, 해당 버전에서는 xmlbeans와 commons-collection4도 추가로 필요하여 함께 추가했으니 참고부탁드립니다.

 

https://archive.apache.org/dist/poi/release/bin/

Index of /dist/poi/release/bin

https://xmlbeans.apache.org/download/index.html

XMLBeans Download

https://commons.apache.org/proper/commons-collections/download_collections.cgi

Collections – Download Apache Commons Collections

 

Pom.xml  (참고 : webcontent-dir 은 /src/main/webapp/WEB-INF/lib 경로로 설정)

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.13</version>
            <systemPath>${webcontent-dir}/poi-ooxml-3.13-20150929.jar</systemPath>   
        </dependency> 
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.13</version>
            <systemPath>${webcontent-dir}/poi-ooxml-3.13-20150929.jar</systemPath>   
        </dependency> 
        
       <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.13</version>
            <systemPath>${webcontent-dir}/poi-ooxml-schemas-3.13-20150929.jar</systemPath>   
        </dependency> 
        
      <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>2.6.0</version>
            <systemPath>${webcontent-dir}/xmlbeans-2.6.0.jar</systemPath>   
        </dependency> 
        
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.4.1</version>
            <systemPath>${webcontent-dir}/commons-collections4-4.1.jar</systemPath>   
        </dependency>

 

STEP2. Ajax(XMLHttpRequest)를 통해서 서버에 엑셀다운로드 요청하기

여기서 할말이 많은데...사실 Ajax를 통해 poi 라이브러리에서 처리한 데이터 값을 응답받는것에 실패하였습니다.^^;; 그래서 여기저기 삽질하다보니... Ajax를 까고보면 결국 xmlHttpRequest 객체를 활용해서 요청/응답을 처리한다는 사실을 알게되었고, xmlHttpRequest를 활용해서 서버에 요청 및 응답을 처리하니 성공하였습니다.. (혹시 ajax로 성공하신분 댓글좀..)

 

코드설명을 간단히 하면,,,

excelDownloadState 변수 : 액셀 다운로드 진행상태 flag 역할

 

참고로, 저는 Json 포맷이 아닌 form 데이터 형식으로 서버에 요청해야했기 때문에 form 데이터를 만들었습니다.

frm은 form 태그의 id 값입니다.

new XMLHttpRequest(); 를 통해서 request 객체에 담고 open함수를 통해 http통신 방법 및 url을 설정합니다. 여기서 서버요청 URL에 본인들에게 맞는 서버 URL 주소를 기입하면 됩니다.

또 여기서 중요한게, excel 데이터를 반드시 blob 타입으로 받아야하기 때문에 responseType에는 'blob'으로 설정했습니다. 그리고 서버 응답을 받기 위한 onload 함수를 구현하여 this.status 값이 200 정상응답인 경우 정상처리에 대한 로직 아닌경우 예외처리를 하도록 로직을 잡으시면 되고, send 함수 즉, 요청을 하는 함수 인자값으로 formData를 전달하였습니다.

 

소스는 일부만 발췌하였기 때문에 자신에게 맞는 소스로 수정하셔야합니다.ㅎㅎ..

var excelDownloadState = false;

$("#downloadExcel").on("click", function() {

 if(excelDownloadState == false){
       

        excelDownloadState = true;

        //input data setting.
        var form = document.getElementById('frm');
        var formData = new FormData(form);

        //xmlhttprequest 통신.
        var request = new XMLHttpRequest();
        request.open('POST', '서버요청URL', true);
        //request.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded; charset=UTF-8');
        request.responseType = 'blob';
        request.onload = function(e) {

        excelDownloadState = false;

            if (this.status === 200) {
            //alert("응답 성공");
            var blob = this.response;
            var fileName = request.getResponseHeader('Content-Disposition').split('Filename=')[1].split(';')[0];
                if(window.navigator.msSaveOrOpenBlob) {
                    window.navigator.msSaveBlob(blob, fileName);
                }else{
                    var downloadLink = window.document.createElement('a');
                    var contentTypeHeader = request.getResponseHeader("Content-Type");
                    downloadLink.href = window.URL.createObjectURL(new Blob([blob], { type: contentTypeHeader }));
                    downloadLink.download = fileName;
                    document.body.appendChild(downloadLink);
                    downloadLink.click();
                    document.body.removeChild(downloadLink);
               }
           }else{
               alert("엑셀파일생성에 실패하였습니다.");
           }
       };
       request.send(formData);

     
	}else{
		alert("엑셀파일생성 중입니다. 잠시만 기다려주세요.");	
	}
		
});

 

긴글 방지를 위해, 개발 배경, 라이브러리 추가, 화면단 소스 구현까지하였습니다.

 

한텀 쉬고 다음 2편에서는 POI라이브러리를 활용한 엑셀다운로드 기능 구현(가장심플버전), JAVA 약간의 추상화, 대망의 myBatis resultHandler 적용까지 알아보겠습니다.