[Excel POI] Mybatis ResultHandler 사용하여 대용량 데이터 추출

POI로 Excel 출력을 위해 ArrayList 등 List에 DB 데이터를

select 하여 20만 건 이상 넣었을 때 (컬럼 多) Out Of Memory 발생.

 

jsp list 표시 부분은 위해서는 jqGrid로 paging을 구성하였기 때문에 문제가 없었지만

Excel 백만 건 출력을 하기 위해 처리 방법이 필요하였다.

 

Mybatis ResultHander를 이용할 수 있다.

아래 방법으로 1,000,000건의 Excel 출력은 된다.

CPU와 Memory 부하도 많이 생기지 않는다.

하지만 시간이 많이 소요되고 (2~30분) Sheet 1장까지만 되는 코드라 추후에 수정해야 할 것 같다.

 

//DAO 

  @Repository    
  public class SysDAO extends BaseDAO {  
  @Autowired 
  private SqlSession sqlSession; 
  
   public void getSysListExcel(HashMap paramMap, ComExcelHandler handler) { 
		sqlSession.select("sys.dao.SysDAO.getSysList", paramMap, handler); 
	} 
}

 

//Service 

@Service 
public class SysService extends BaseService {
@Autowired 
private SysDAO sysDAO;

  public void getSysListExcel(HashMap paramMap, HttpServletRequest request, ComExcelHandler handler) {
      sysDAO.getSysListExcel(paramMap, handler);
  }
}

 

 

 

 

//Controller

 

@RequestMapping(value = "getSysListExcel.json", method = RequestMethod.POST)
	public String  getSysListExcel(Map<String, Object> model,HttpServletRequest request, 
        HttpServletResponse response,@RequestParam HashMap paramMap) {	
	HashMap hashmap = new HashMap<>();
	List sysList = null;
		
	//view단에서 로딩바 띄우기 위해 사용
	CookieGenerator cg = new CookieGenerator();
	cg.setCookieName("fileDownloadToken");
	cg.setCookiePath("/");
	cg.addCookie(response, "true");

	try {
	  String titleArr[] = new String[2];
	  titleArr[0] = "정보";
	  titleArr[1] = "번호";


	  String titleKeyArr[] = new String[2];
	  titleKeyArr[0] = "INFO";
	  titleKeyArr[1] = "NUM";

	  SXSSFWorkbook wb = new SXSSFWorkbook(10000);
	  ComExcelHandler handler 
          = new ComExcelHandler(wb, titleArr, titleKeyArr, fileName, "시스템", searchMap);
	  smsTransHisSysService.getSmsTransHisSysListExcel(paramMap, request,handler);

	  handler.setTitleArr(titleArr);
	  handler.setTitleKeyArr(titleKeyArr);
  	  handler.setExcelFileName("시스템_" + sdate);
	  handler.setSheetName("시스템");


	  // 엑셀 파일 명
	   response.setHeader("Content-Disposition", "attachment; filename=" 
           + new String(fileName.getBytes("euc-kr"), "8859_1") + ".xlsx");

	  ServletOutputStream stream = response.getOutputStream();
	  OutputStream out = new BufferedOutputStream(stream);
	  try {
	   response.resetBuffer();
	   response.setBufferSize(1024 * 4);
	   wb.write(out);
	  } catch(Exception e) {
	   out.flush();
	   out.close();
	   stream.close();
	  } finally {
	   out.flush();
	   out.close();
	   stream.close();
   }

   if (wb != null) {
	try {
	  ((SXSSFWorkbook) wb).dispose();
	 }  catch (Exception e) {
	  ((SXSSFWorkbook) wb).close();
	 } finally {
	  ((SXSSFWorkbook) wb).close();
	 }
   }
  ((SXSSFWorkbook) wb).close();
 } catch (Exception e) {
 } 
 return null;
}

 

 

 

//ExcelHandler

 

@Component
public class ComExcelHandler extends BaseExcelView implements ResultHandler {

	private String excelFileName;
	private String titleArr[];
	private String titleKeyArr[];
	private String sheetName;
	private HashMap searchList;
	
	private SXSSFWorkbook wb;
	private SXSSFSheet sheet;
	private SXSSFRow row;
	private int rowNum = 0; 
	private List returnList = new ArrayList() ;

	/**
	 * 엑셀 생성
	 */
	
	public ComExcelHandler() {
       
    }

  public ComExcelHandler(SXSSFWorkbook wb, String[] titleArr, String[] titleKeyArr
                              , String fileName, String sheetName,HashMap searchMap) {
	  
	  this.wb = wb;	
	  this.titleArr = titleArr;
	  this.titleKeyArr = titleKeyArr;
	  this.sheetName = sheetName;
	  this.searchList = searchMap;
	  
	  try {
      	createFirstSheet(wb);
        createTitle(sheet, rowNum+1, wb);
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


//ResultHandler 결과 받아서 작업 처리
    @Override
	public void handleResult(ResultContext resultContext) {
		Map data = (Map)resultContext.getResultObject();
		createBody(data);			
	}
	    

	/**
	 * sheet 생성
	 */
	private SXSSFSheet createFirstSheet(Workbook workbook) {
		sheet = (SXSSFSheet) workbook.createSheet(sheetName);
		
		if(titleArr!=null) {
			for (int i = 0; i < titleArr.length; i++) {
				sheet.setColumnWidth(i, (256 *13));
				sheet.setColumnWidth(i+1, (256 *13));
			}
		}
		return sheet;
	}
	
	private void createTitle(SXSSFSheet sheet, int rowN, Workbook wb) throws Exception {
		SXSSFCell cell = null;
		int cellIdx = 0;
		CellStyle cs = getBoldStyle(wb);
		
		if( titleArr != null && titleArr.length > 0 ){
			row = sheet.createRow(rowN);
			for(int j = 0; j < titleArr.length; j++){
				cell = row.createCell(cellIdx++);
				cell.setCellValue(titleArr[j]);   
				cell.setCellStyle(cs);
			}
		}
		rowNum = rowN + 1;

	}
	
	private void createBody(Map data) {
		SXSSFCell cell = null;  
		int cellIdx = 0;
		
		row = sheet.createRow(rowNum++);
		cellIdx = 0;
		
		if( titleKeyArr != null && titleKeyArr.length > 0 ){
			for(int j = 0; j < titleKeyArr.length; j++){
				cell = row.createCell(cellIdx++);
				cell.setCellValue(data.get(titleKeyArr[j]) == null ? "" 
				    : String.valueOf(data.get(titleKeyArr[j])));	
			}
		}
		
	}
}