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

안녕하세요. limky 입니다. 

오늘은 POI라이브버리 + AJax + myBatis resultHandler 를 통한 대용량 excel 다운로드 기능 2편에 대한 내용입니다. 지난 1편에선 excel 다운로드 기능 구현에 고려할 사항 3가지가 무  POI 라이브버리 설정과 화면단 소스에 대해서 다뤘습니다. 

 

이번 2편에서는 서버 로직에 대해서 이야기 하고자 합니다.

우선 대용량 excel 다운로드 기능을 구현하기 위해 백단에서는 POI 라이브러리로 excel 다운로드 기능이 동작되도록 개발했고, 확장성을 고려한 추상화 작업, 그리고 OOM 방지를 위한 myBatis resultHandler 적용에 대한 작업을 했습니다. 다시 정리하면 아래 3가지 step입니다.

 

1. POI 라이브러리를 활용한 excel 다운로드 기능 구현

2. 향후 확장성을 고려한 추상화 작업

3. 대용량 데이터건을 건by건 처리하여 OOM방지 (myBatis resultHandler 활용)

 

소스를 보면서 설명드리겠습니다.

STEP01. mybatis의 ResultHandler를 구현한 추상클래스

import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public abstract class ExcelHandler implements ResultHandler{
	
	String[] header;
	String fileName;
	String sheetName;
 	int ROW_ACCESS_WINDOW_SIZE;
	XSSFWorkbook xssfWorkbook;
   	SXSSFWorkbook sxssfWorkbook;
   	
	SXSSFSheet objSheet = null;
	SXSSFRow objRow = null;
	SXSSFCell objCell = null; 
	
	int rowNum = 1; //row 카운트
	int dataIdx = 1; //data 순번
	
	public void ExcelHandler(String[] header, String fileName, String sheetName, int size){
		this.header = header;
		this.fileName = fileName;
		this.sheetName = sheetName;
		this.ROW_ACCESS_WINDOW_SIZE = size;
		this.xssfWorkbook = new XSSFWorkbook();
		this.sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook, ROW_ACCESS_WINDOW_SIZE);
		this.objSheet = sxssfWorkbook.createSheet(this.sheetName); // sheet 생성
		
	}
	
	public void createExcelHeader(){
    	// 0번째 부터 시작
    	for(int i=0; i<1; i++) {
    		objRow = objSheet.createRow(0);
    		for(int j=0; j <header.length; j++) {
    			objCell = objRow.createCell(j);
    			objCell.setCellValue(header[j]);
    		}
    	}
	}
	
	public void setExcelCell(int num, Object value){
	  	objCell = objRow.createCell(num);//cell 생성
    		objCell.setCellValue(String.valueOf(value));//생성된 cell에 값 매핑
	}
	
	public void writeExcelFile(HttpServletResponse response) throws IOException{
    	String excelYYMMDD = DateUtil.getFormattedDate("YYYYMMDDHH24MISS");
    	response.setContentType("application/xlsx");
    	response.setHeader("Content-Disposition", "ATTachment; Filename=" + this.fileName +"_"+ excelYYMMDD + ".xlsx");

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

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

    	sxssfWorkbook.dispose(); 
	}
	
	public abstract void createExcelBody(HashMap<String,String> vo);

	@Override
	public void handleResult(ResultContext resultContext) {
		
		HashMap<String,String> data = (HashMap<String, String>) resultContext.getResultObject();

		createExcelBody(data);
		
	}
}

ExelHandler라는 추상클래스를 생성했고 해당 추상클래스는 resultHandler 인터페이스를 상속받아 handleResult 메서드를 오버라이딩했습니다. handleResult 메서드는 myBatis에서 db로 직접 데이터를 row건별로 읽어올때 호출되는 메서드라서 건by건으로 데이터값을 불러옵니다. 따라서 list로 한꺼번에 데이터를 받아오는 것이 아닌 hashMap 형태로 건별로 가져오는 것을 확인할 수 있고 추상 메서드인 createExcelBody 를 호출하여 엑셀을 생성하게끔 하였습니다. 

 

나머지 메서드들도 살펴보면 ExcelHandler 생성자에선 인자로 액셀 헤더(컬럼명), 엑셀파일명, 엑셀sheet명, 버퍼사이즈 정보를 가져오게끔 설계했고,  각종 초기화 및 SXSSFWorkBook 생성과 sheet 생성을 하여 엑셀을 만들 준비를 하였습니다. (sxxsf 설명은 poi 라이브버리 관련 글들이 많기 때문에 여기서는 다루지 않겠습니다.) 

 

그 다음 createHeader, createExcelBody, setExcelCell, writeExcelFile 메서드를 만들었고, 여기서 중요한 것은 createExcelBody는 추상메서드로 두어, ExcelHandler를 상속받은 구현클래스에서 직접 엑셀 요구사항에 맞는(필요에 맞는) 형태도 오버라이딩해서 쓰도록 설계했습니다. 참고로 createExcelBody에서 setExcelCell메서드를 호출하여 각 cell생성 및 cell에 데이터 값을 매핑하도록 하였습니다. 아울러, createExcelHeader는 말그대로 엑셀 헤더(컬럼명)의 row와 cell을 생성하고 각 컬럼명을 매핑하는 역할을 담당합니다. 

마지막으로, writeExcelBody는 화면단에 넘겨줄 http response에 엑셀파일 정보를 header값에 세팅하고, OutputStrem에 파일을 쓰고 stream 및 workbook을 close/dispose하도록 하였습니다.

 

STEP02. 추상클래스 ExcelHandler를 상속받은 구현클래스(추상화)

import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map.Entry;

public class AExcelHandler extends ExcelHandler{
	
	
	public AExcelHandler(String[] header, String fileName, String sheetName, int size) {
		super.ExcelHandler(header, fileName, sheetName, size);
	}

	@Override
	public void createExcelBody(HashMap<String, String> map) {
  	    
      	int cellNum = 0;
      	
  		//1. row생성
   	    this.objRow = objSheet.createRow(this.rowNum++);
    	
   	    //2-1. cell생성
   	    //2-2. 값매핑
   	    setExcelCell(cellNum++, this.dataIdx++);
  	    setExcelCell(cellNum++, map.get("KEY1"));
   	    setExcelCell(cellNum++, map.get("KEY2"));
   	    setExcelCell(cellNum++, map.get("KEY3"));
   	    // ......(각자 요구사항에 맞게 데이터 매핑)
   	    setExcelCell(cellNum++, map.get("KEY20"));
   	    setExcelCell(cellNum++, map.get("KEY21"));
   	    setExcelCell(cellNum++, map.get("KEY22"));


	}
}

위에서 설명드린 추상클래스 ExcelHandler를 AExcelHandler가 상속받아 createExcelBody 추상메서드를 자신의 스타일에 맞게 구현하였습니다. 여기서 각자 비즈니스 로직에 맞게 데이터를 가공하는 로직을 짜시면 됩니다.

 

저의 요구사항은 소스에는 없지만 예를들어 KEY3 값은 KEY7의 값이 A인 경우 1이고 아닌 경우 0으로 넣어달라는 등등의 요구사항이 있었기 때문에 처음부터 순서를 보장해서 데이터값을 가져오고 익명클래스로 구현하는 방식을 적용하기엔 적합하지 않을 것 같단 생각이 들었습니다...

 

따라서, HashMap으로 가져오고 키값으로 데이터를 조회 및 매핑하도록 하였고, 이로 인해 얻을 수 있는 이점은 소스가 명확하고 직관성이 있다는 점, 그리고 어느 요구사항이던간에 순서 상관없이 엑셀에 매핑되는 키값이 아니더라도 유연하게 기준값으로 사용할 수 있다는 점 등등....다만!! 단점은 순서가 보장되지 않게 데이터 값을 가져오고 키값을 하나하나 조회하여 cell을 생성해야한다는 점(컬럼이 많은 경우 노가다 발생), 또 추상클래스 ExcelHandler 상속받아 처리하는 구현클래스가 엑셀 다운로드 기능이 있는 화면마다 생성되어야 한다는 점 등은 비효율적입니다.

 

아무튼 여기서 핵심은! 추상메서드 createExcelBody에 여러분이 조회한 hashMap 데이터를 키값으로 조회하여 cell을 생성하고 데이터값을 매핑하는 setExcelCell메서드를 호출하면 된다는 사실입니다.

 

STEP03. Controller호출부와 DB조회시 호출되는 ResultHandler의 handleResult

//고객관리분석 > 사용자단계별 이력 > 엑셀다운
  @RequestMapping(value = { "/exceldownload"})
  public void exceldownloadController(final Model model, final HttpServletRequest request, HttpServletResponse response ) throws SQLException, IOException {

    // .... 생략.....
      
    LOGGER.info("*****************poi 라이브러리를 활용한 대용량 엑셀다운로드 로직 START*****************");

	//엑셀생성(header 생성, 액셀헤더생성 -> 액셀바디생성 -> 액셀파일쓰기)
	String[] header = {"번호","구분","컬럼1","컬럼2","컬럼3","컬럼4"};
	 
	ExcelHandler excelHandler = new AExcelHandler(header, "excelfilename","sheetname",500);
	excelHandler.createExcelHeader();
	selectDbService(excelHandler); //각자 db 조회하는 서비스..(DAO, Mapper 등등)
    excelHandler.writeExcelFile(response);
   
    LOGGER.info("*****************poi 라이브러리를 활용한 대용량 엑셀다운로드 로직 END*****************");

  }

 

마지막으로... Controller 부분인데요.. 많은 부분 생략을 했는데 중요한 부분만 설명하겠습니다. 

우선, header 에 구성할 엑셀파일 헤더 즉, 컬럼명을 정의해줍니다. 그리고 ExcelHandler 추상클래스를 구현한 AExcelHandler 의 객체를 생성하고 createExcelHeader 생성을 통해 기본적인 엑셀sheet생성, 엑셀헤더 row생성, 엑셀헤더 컬럼명 매핑까지 처리합니다. 이제 여기서 중요한 소스 한줄.... 저는 selectDbService 메서드를 호출하지만 여러분만의 db조회를 할 수 있는 service가 분명 있을겁니다. 그것이 dao가 됫던, mapper가 되었던 분명 db 호출하는 부분이 있을텐데 해당 메서드 인자값으로 excelHandler를 넘겨줍니다. 그럼 자동으로 resultHandler를 통해 row건별로 data를 읽어 각 데이터 row별로 cell생성 및 cell에 data값을 매핑할 겁니다.

 

근데 뭔가 이상할 겁니다. createExcelBody를 직접 호출하는 부분이 없는데 어떻게 엑셀이 생성되냐구요..? 

 

자...메서드 호출 메커니즘에 대해 정리해보겠습니다.

DB조회시 myBatis resultHandler를 통해!! handleResult 메서드가 호출되고 해당 메서드에서 createExcelBody메서드를 호출한다. createExcelBody 메서드는 추상메서드로 구현클래스의 createExcelBody 메서드를 호출하게 되고 결국 AExcelHandler에서 구현한 createExcelBody를 호출하고 해당 메서드에서 row건별로 전달받은 데이터를 비즈니스 요구에 맞게 데이터를 가공하여 setExcelCell 메서드를 호출하여 엑셀을 그리게 한다.

 

자...드디어 대용량 엑셀다운로드 기능이 구현되었습니다. 보안상 소스를 전체공개할 수 없기 때문에 아쉽습니다만...

동작시키면, 우리가 엑셀다운로드 기능을 요청한 브라우저에서 서버로 부터 응답을 받으면 브라우저 하단에 우리가 설정한 엑셀파일명과 함께 다운로드하시겠습니까? 라는 창이 뜹니다. 만약 서버가 요청 처리중에 또 엑셀다운로드 버튼을 누르면 alert창(기다리라는)이 뜨구요..아! 그리고 row건별로 엑셀을 그리기 때문에 OOM 발생가능성도 줄이게 되었습니다.

 

1편 서론에서 말씀드렸지만,,,일 데이터가 3만건 이상 적재되는 상황이라.. 사용자가 한달치를 조회하는 경우 100만건 가까운 엑셀 데이터를 가져오고 엑셀을 그려야하기 때문에 위와같은 방법을 고려해서 기능구현을 하였습니다..

 

저도 구현을 하면서, 여러가지로 삽질을 하고 새로 공부한 것도 많은데요.. 아직도 추상화 부분은 뭔가 아쉬운 부분이 남습니다. 혹시 더 좋은 추상화가 있다면 알려주세요~~!! 혹, 추상화 부분이 더 깔끔하게 되면 업데이트할 예정입니다. 긴글 읽어주셔서 감사합니다~!!