JXLS, POI JAVA에서 Excel 사용하는 구현 방법 및 종류 비교

 개발을 하다 보면 종종 운영에서 엑셀 다운로드 기능에 대한 요구가 있는데, 이번에 Java에서 엑셀 다운로드를 구현하는 방법에 대해 알아보겠습니다.
 Java에서 엑셀  다운로드 기능을 위해 자주 쓰이는 방식으로 JXLS 라이브러리를 이용한 방식과 POI 라이브러리를 이용한 방식이 있습니다. ( ‘제이엑셀’, ‘포이’ 라고들 읽죠 ㅎㅎ)
 
 
1.  JXLS 라이브러리를 이용한 엑셀 다운로드 기능 구현 방법
2.  티몬에서 JXLS를 이용했을 때의 장애발생 및 해결방안
3.  POI 라이브러리의 SXSSF를 이용한 엑셀 다운로드 기능 구현 방법

 

 

1. JXLS 라이브러리를 이용한 엑셀 다운로드

1-1) JXLS 이란 ?

-  JXLS은 개발자가 미리 만들어 놓은 엑셀 템플릿 파일을 토대로 데이터가 자동으로 쓰여지기 때문에 개발이 굉장히 용이한 방법입니다. JXLS 라이브러리에서 제공해주는 명령어들을 이용해 엑셀 템플릿 파일을 만들고 자바단의 모델 속성명과 엑셀 템플릿 파일내의 속성명을 일치 시켜주면, 엑셀에 모델속성들이 반복적으로 쓰여집니다.
-  JXLS은 데이터를 메모리에 계속 들고 있기 때문에 엑셀에 삽입할 데이터가 많으면 속도가 점점 저하되고, 서버에서 Out Of Memory 에러가 발생할 수 있습니다.

1-2) JXLS 구현방법

  1) 엑셀 템플릿 파일 생성

-  엑셀을 열고 새 통합문서에서 위와 같은 방식으로 메모를 추가하여 명령어를 입력하고, 각 셀에도 명령어를 입력합니다.

-  jx:area(lastCell=”셀위치”) : 명령어 영역의 마지막 셀을 나타냅니다. 즉, 위 사진에서는 B2셀까지만 명령어 영역이라는 것을 지정하는 것입니다. 만약, last Cell 이외의 곳(ex : C2셀)에서 명령어를 입력해도 명령이 실행되지 않습니다.

-  items=”컨텍스트 명” : 자바단에서 생성한 컬렉션 데이터를 갖는 컨텍스트 변수입니다. 자바단에서 JXLS을 위한 Context를 세팅할 때, context명과 items명을 반드시 맞추어야 정상적으로 동작합니다. 아래 소스에서 한번 더 짚고 넘어가겠습니다.

-  var=”모델명” : 각 셀에서 ‘$’문자와 함께 사용할 모델명입니다. 여기서의 모델명은 자바단과 상관없이 아무렇게나 지정해도 상관없고 엑셀의 각 셀에만 이 모델명을 제대로 입력하면 됩니다. 예를 들어, var=”tmon”으로 할 경우, 각 셀에 ${tmon.속성명}으로 작성하면 됩니다.

-  엑셀파일을 만들고 프로젝트내 적절한 위치에 저장합니다. (예제에서는 src/main/resource/template/excel 디렉토리 저장)

-  JXLS 참고 : http://jxls.sourceforge.net/reference/each_command.html

2) 엑셀 다운로드 수행

public void jxlsExcelDownloadTest(HttpServletResponse response) throws Exception {
    // 1. 데이터 생성 (예제를 위한 dummy 데이터 생성)
    List<ExcelDownloadModel> excelDataList = getExcelDownloadData();
 
    // 2. 미리 만들어둔 엑셀 템플릿 파일에 대한 InputStream 생성
    InputStream templateStream = resourceLoader.getResource("classpath:template/excel/jxslExcelTemplate.xlsx").getInputStream();
 
    // 3. 응답객체로부터 OutPutStream 생성
    OutputStream targetStream = response.getOutputStream();
 
    // 4. 컨텍스트 객체 생성 및 세팅
    // 4-1. context 속성에 컨텍스트명과 엑셀에 쓰일 데이터를 Key & Value로 세팅
    // 4-2. 여기서 contextName("excelDataList")은 엑셀 템플릿파일에서 items="컨텍스트명"과 반드시 일치
    Context context = new Context();
    context.putVar("excelDataList", excelDataList);
 
    // 5. 엑셀파일로 다운로드위한 response 객체 세팅
    response.setContentType("application/msexcel");
    response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s\"", URLEncoder.encode("downloadFileName.xlsx","UTF-8")));
 
    // 6. templateStream 으로 부터 템플릿을 읽어 들인 후 context를 targetStream에 씀
    JxlsHelper.getInstance().processTemplate(templateStream, targetStream, context);

 

-  위 소스코드에 주석으로 설명을 달아놨지만, 한번 더 짚고 가자면 “4-2”주석부분에 쓰여있는 것처럼, context.putVar(“컨텍스트명”, 데이터컬렉션); 이 부분의 컨텍스트명과 엑셀 템플릿 파일의 items=”컨텍스트명”을 꼭 일치시켜야 합니다. 

-  이 글을 쓰면서 다시 한 번 JXLS을 구현해보는데, 분명히 다 제대로 했는데 정상동작하지 않길래 삽질을 조금 했는데, 엑셀템플릿파일에서 메모에 명령어를 입력할 때, 복&붙으로 넣었는데 큰따옴표 encoding이 깨져서 정상 동작하지 않았습니다. 겉보기엔 큰따옴표가 제대로 들어간 것처럼 보였는데 말이죠... 정상동작하지 않는 경우에 한번 체크해보면 좋을 것 같습니다.

3) 결과

-  A, B셀에는 데이터가 제대로 들어갔지만, (1)의 과정에서 last Cell=”B2”로 지정했기 때문에 C2셀에 명령이 있어도 수행되지 않은 것을 볼 수 있습니다. 

 

 

2. 티몬에서 JXLS을 이용했을 때의 장애발생경험

2-1) 상황

 티몬에서 프로젝트를 진행할 때 요구사항에 어드민 페이지에서 데이터를 엑셀로 다운받을 수 있게 해달라는 내용이 있었습니다. JXLS과 POI가 있다는 것은 알았지만, 두 방식의 차이를 자세히 모르고 개발기간을 맞추기 위해 비교적 개발이 편리한 JXLS을 선택하여 개발했습니다. 그리고 엑셀 다운로드가 제대로 동작하는지만 테스트하고 운영에 배포가 되었는데 처음에는 별 문제가 없는 것처럼 보였습니다. 그런데 운영에서 데이터가 계속 쌓이자 엑셀 다운로드가 너무 느리다는 이야기가 나왔고, 재연을 해보려는 순간 서버가 다운 되어버렸습니다

2-2) 원인

 이 프로젝트가 신규프로젝트였기 때문에 데이터가 전혀 없는 상황이었고 개발 테스트를 할 때 고작 dummy데이터를 몇개 집어넣고 테스트를 해본 것이 실수였습니다.
 서버가 다운된 원인을 분석해보니, 다운로드하려는 엑셀 데이터가 약 1만 row가 넘자 Out Of Memory에러로 인해 서버가 다운된 것이었습니다.

2-3) 해결방안

 엑셀 다운로드 하는 방법에 대해 다시 조사를 해보았고, POI라이브러리 중 SXSSF방식이 있다는 것을 알았습니다. 아래에서 자세하게 설명하겠지만, SXSSF방식은 메모리에 있는 데이터를 디스크에 임시 파일로 옮기면서 처리하기 때문에 메모리를 적게 잡아먹는 방식입니다. 메모리에 데이터를 다 들고 있지 않아도 파일다운로드가 되기 때문에 SXSSF방식으로 변경함과 동시에 DB에서 데이터를 가져올 때 paging 처리를 하여 이 장애를 해결했습니다.

 

 

3. SXSSF 라이브러리를 이용한 엑셀 다운로드

3-1) POI란 ?

-  POI 라이브러리는 HSSF, XSSF, SXSSF방식으로 나누어져 있고 MultiSheet, CellStyle 등을 쉽게 구현할 수 있지만, 자바단에서 ExcelRow생성, 각 Cell에 값 주입 등을 일일이 해줘야 하는 번거로움이 있습니다. 

    1) HSSF : EXCEL 2007 이전 버전(.xls)에서 사용하는 방식
    2) XSSF : EXCEL 2007 이후 버전(2007포함 .xlsx)에서 사용하는 방식
    3) SXSSF : XSSF의 Streaming Version으로 메모리를 적게 사용하여 대용량 엑셀 다운로드에 주로 사용되는 방식

 3-2) SXSSF ? 메모리 ?

-  SXSSF방식을 사용하면 MsOffice 2007 OOXML 형태로 된 파일(.xml)을 디스크(서버)에 생성하여, 데이터를 메모리에 계속 가지고 있지 않고 임시로 이 파일에 기록한 후 메모리를 비워내는 방식으로 메모리를 적게 잡아먹도록 하는 것입니다. 또한 임시로 저장된 파일(.xml)도 지워주어야 디스크 용량이 낭비되지 않을 텐데, 이 임시파일을 지워주는 역할을 메소드도 제공되고 있습니다. 아래 구현방법에서 자세히 설명하겠습니다.

3-3) 구현방법

0) 엑셀 템플릿 파일 생성 (굳이 생성하지 않아도 됩니다)

-  이번에는 JXLS과는 다르게 단순히 모델 속성명만 적어놓은 템플릿입니다. 템플릿을 사용하지 않고, 빈 엑셀문서를 생성한 후 동적으로 모델속성명을 셀에 생성할 수도 있습니다. 예제에서는 모델 속성이 3개 뿐이지만, 실제 운영에서는 많게는 20~30개의 속성이 존재하기 때문에 티몬에서는 위와 같이 미리 템플릿을 만들어두고 사용하고 있습니다.

1) SXSSF Workbook 생성

 
// 1. SXSSF WorkBook 생성
public void initExcelTemplate(String templateFileName) throws IOException {
    InputStream templateFile = resourceLoader.getResource("classpath:template/excel/" + templateFileName).getInputStream();
 
    // 엑셀템플릿파일 지정 (지정안하고 빈 통합문서로도 가능)
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(templateFile);
 
    // 엑셀템플릿파일에 쓰여질 부분 검색
    Sheet originSheet = xssfWorkbook.getSheetAt(FIRST_SHEET_INDEX);
    rowNo = originSheet.getLastRowNum();
 
    // SXSSF 생성
    sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook, ROW_ACCESS_WINDOW_SIZE);
    sheet = sxssfWorkbook.getSheetAt(FIRST_SHEET_INDEX);

-  위에서 미리 만들어둔 엑셀 템플릿파일로부터 SXSSF Workbook을 생성합니다.

-  엑셀템플릿파일로부터 SXSSF Workbook을 생성하기 위해서는 XSSF를 이용하여 workbook을 만들고, SXSSF Workbook을 생성할 때 파라미터로 넣어주면 됩니다. 생성자의 두번째 매개변수는 디스크로 flush되기 전까지 메모리에 들고있는 행의 개수를 뜻합니다.
 
-  참고 : https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFWorkbook.html#SXSSFWorkbook-org.apache.poi.xssf.usermodel.XSSFWorkbook-int-

2) 엑셀에 데이터 삽입 및 메모리 flush

// 2. 엑셀파일에 데이터 삽입
public void addRowDataTest(List<ExcelDownloadModel> excelDataList) throws IOException {
    // 엑셀 row 생성
    for(ExcelDownloadModel model : excelDataList) {
        Row row = sheet.createRow(++rowNo);
 
        // 엑셀 cell 생성 및 값 주입
        Cell cell = row.createCell(0);
        cell.setCellValue(model.getModelSeqno());
        cell = row.createCell(1);
        cell.setCellValue(model.getModelTitle());
        cell = row.createCell(2);
        cell.setCellValue(model.getModelContents());
    }
 
    // 디스크로 flush
    ((SXSSFSheet)sheet).flushRows(excelDataList.size());

-  데이터를 엑셀 파일에 삽입하는 과정입니다. 위에서 언급했듯이 POI 라이브러리는 JXLS와는 다르게 row를 생성하고 각 셀에 일일이 데이터를 넣어주어야 합니다. (편의를 위해 Cell에 삽입하는 소스를 풀어서 작성하였습니다.) 여기서 중요한 부분은 마지막 라인의 flushRows메소드 입니다. flushRows 메소드를 통해 메모리에 있는 데이터를 디스크(임시파일)로 옮기고 메모리를 비워내는 것입니다.

- 참고 : https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFSheet.html#flushRows-int-

3) 엑셀파일 전송 및 임시파일 삭제

// 2. 엑셀파일에 데이터 삽입
public void addRowDataTest(List<ExcelDownloadModel> excelDataList) throws IOException {
    // 엑셀 row 생성
    for(ExcelDownloadModel model : excelDataList) {
        Row row = sheet.createRow(++rowNo);
 
        // 엑셀 cell 생성 및 값 주입
        Cell cell = row.createCell(0);
        cell.setCellValue(model.getModelSeqno());
        cell = row.createCell(1);
        cell.setCellValue(model.getModelTitle());
        cell = row.createCell(2);
        cell.setCellValue(model.getModelContents());
    }
 
    // 디스크로 flush
    ((SXSSFSheet)sheet).flushRows(excelDataList.size());

-  dispose 는 (2)번 과정에서 디스크에 임시로 저장해 두었던 파일을 삭제하는 메소드입니다. dispose메소드가 정상적으로 호출되지 않으면 디스크에 임시파일이 그대로 남아있게 되기때문에 flushRows메소드와 한쌍으로 사용하면 됩니다.

-  참고 : https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFWorkbook.html#dispose--

4) 결과

-  JXLS에서는 데이터를 제대로 넣었음에도 불구하고 엑셀 템플릿파일에서 last Cell을 잘못지정하면 데이터가 나오지 않는 것을 확인했고, 그 외의 명령어들을 엑셀 템플릿에 잘못 입력하게 되면 정상동작하지 않을 수 있기 때문에 종종 원인을 파악할 때 삽질을 하게 되는 경우가 있을 수 있습니다. ${excelData.modelSeqno}를 입력하는 과정에서 철자를 틀리게 되면 동작하지 않을 수 있습니다. 

-  반면 SXSSF에서는 자바단에서 모두 컨트롤하기 때문에 엑셀 템플릿파일과는 상관없이 제대로 엑셀에 제대로 데이터가 삽입되지 않을 경우 디버깅이 비교적 쉽습니다.

 

 

4. JXLS 와 SXSSF 테스트

 -  엑셀 다운로드 Row : 약 1만 건
 -  각 Row의 Column : 29 개
 -  JXLS 방식 10번 수행, SXSSF 10번 수행
 -  결과

JXLS	SXSSF
14.274 sec	4.281 sec
11.25 sec	2.024 sec
10.259 sec	1.793 sec
11.683 sec	1.816 sec
9.681 sec	1.403 sec
8.737 sec	2.074 sec
9.906 sec	2.486 sec
9.221 sec	1.619 sec
9.706 sec	1.657 sec
8.788 sec	1.82 sec
10.35 sec	2.097 sec

- JXLS 평균 수행시간 : 10.35 sec
- SXSSF 평균 수행시간 : 2.097 sec

마치며

 엑셀 다운로드 기능을 개발하는 방법은 여러가지 방법이 있으나 자주 사용되는 방법에 대해 알아보았습니다. 본인이 속한 팀에 따라 엑셀 다운로드 기능을 빈번하게 개발해야 할 수도 있고, 전혀 사용하지 않을 수도 있으나 통상적으로 굉장히 많이 사용되는 기능이 아니기 때문에 공유하면 좋을 것 같아 작성해보았습니다. 감사합니다.

JXLS -

Each-Command Introduction Each-Command is used to iterate through a collection and clone the command XLS area. It is an analogue of Java for operator. Command Attributes Each-Command has the following attributes var is a name of the variable in Jxls context to put each new collection item when itera

jxls.sourceforge.net

 

SXSSFSheet (POI API Documentation)

Field Summary Fields inherited from interface org.apache.poi.ss.usermodel. Sheet BottomMargin , FooterMargin , HeaderMargin , LeftMargin , PANE_LOWER_LEFT , PANE_LOWER_RIGHT , PANE_UPPER_LEFT , PANE_UPPER_RIGHT , RightMargin , TopMargin Constructor Summary Constructors Constructor and Descripti

poi.apache.org

[출처]  Java 대용량 엑셀 다운로드 기능 구현