java excel download poi 대용량 처리

package kr.go.safepeople.config.component;
 
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;
 
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import kr.go.safepeople.config.exception.SafePeopleException;
import kr.go.safepeople.config.util.DataMap;
 
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.AbstractView;
 
@Component("excelDownloadView")
public class ExcelDownloadView extends AbstractView{
    
    public ExcelDownloadView() {
        // TODO Auto-generated constructor stub
        setContentType("application/download; charset=utf-8");
    }
 
    @Override
    protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws SafePeopleException {
        // TODO Auto-generated method stub
        try{
            String excelFileName = (String)model.get(ExcelConstant.EXCEL_FILE_NAME);
            StringBuffer fileName = new StringBuffer();
            ServletOutputStream out = response.getOutputStream();
            
            fileName.append(excelFileName);
            fileName.append("_");
            fileName.append(currentDate("yyyyMMdd_HHmmss"));
            List<DataMap> excelDataList = (List<DataMap>)model.get(ExcelConstant.EXCEL_DATA_LIST);
            if(excelDataList.size()==0){
                out.print("NODATA");
            }else{
                Workbook workbook = new SXSSFWorkbook(500);
                buildExcelDocument(model, workbook);
                fileName.append(".xlsx");
                setResponse(request,response,fileName.toString());
                workbook.write(out);
            }
            if(out != null){out.close();}
        } catch (Exception e) {
            logger.debug(e.getMessage(), e);
            throw new SafePeopleException(e);
        }
    }
 
    private void setResponse(HttpServletRequest request, HttpServletResponse response, String fileName) throws UnsupportedEncodingException{
        String userAgent = request.getHeader("User-Agent");
        String downFileName = "";
        if(fileName!=null){
            downFileName = fileName;
        }
        if (userAgent.indexOf("MSIE 5.5") > -1) { // MS IE 5.5 이하
            response.setHeader("Content-Disposition", "filename=" + URLEncoder.encode(downFileName, "UTF-8").replaceAll("\\+", "\\ ") + ";");
        } else if (userAgent.indexOf("MSIE") > -1) { // MS IE (보통은 6.x 이상 가정)
            response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(downFileName, "UTF-8").replaceAll("\\+", "\\ ") + ";");
        } else if (userAgent.indexOf("Trident") > -1) { // MS IE 11
            response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(downFileName, "UTF-8").replaceAll("\\+", "\\ ") + ";");
        } else { // 모질라나 오페라
            downFileName = URLEncoder.encode(downFileName,"UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=\""+downFileName.replaceAll("\\+", "\\ ")+"\";");
        }
    }
    
    private String currentDate(String format){
        SimpleDateFormat sd = new SimpleDateFormat(format, Locale.KOREA);
        return sd.format(new Date()); 
    }
    
    private void buildExcelDocument(Map<String, Object> model, Workbook workbook) {
        // TODO Auto-generated method stub
        
        // init
        String excelTitle = ExcelConstant.EXCEL_TITLE_EMPTY;
        if(model.get(ExcelConstant.EXCEL_TITLE) != null){
            excelTitle = (String)model.get(ExcelConstant.EXCEL_TITLE);
        }
        String excelSheetName = ExcelConstant.EXCEL_SHEET_NAME_EMPTY;
        if(model.get(ExcelConstant.EXCEL_SHEET_NAME) != null){
            excelSheetName = (String)model.get(ExcelConstant.EXCEL_SHEET_NAME);
        }
        List<DataMap> excelDataList = (List<DataMap>)model.get(ExcelConstant.EXCEL_DATA_LIST);
        DataMap headerDataMap = excelDataList.get(0);
        
        int rowTitleNum = 0;
        int rowTitleSubNum = 2;
        int rowHeaderNum = 3;
        int rowDataNum = 4;
        
        String subTitle = "작성일자 :"+currentDate("YYYY년 MM월 d일 HH:mm:ss");
        
        // create title(style,font)
        XSSFFont titleFont =  (XSSFFont)workbook.createFont();
        titleFont.setFontHeightInPoints((short)16);
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        
        XSSFCellStyle  titleCellStyle = (XSSFCellStyle)workbook.createCellStyle();
        titleCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);              
        titleCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);   
        titleCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);   
        titleCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); 
        titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        titleCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
        titleCellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleCellStyle.setFont(titleFont);
 
        XSSFCellStyle  titleSubCellStyle = (XSSFCellStyle)workbook.createCellStyle();
        titleSubCellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        titleSubCellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        
        // create hearder(style,font)
        XSSFFont hearderFont =  (XSSFFont)workbook.createFont();
        hearderFont.setFontHeightInPoints((short)10);
        
        XSSFCellStyle  headerCellStyle = (XSSFCellStyle)workbook.createCellStyle();
        headerCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);              
        headerCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);   
        headerCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);   
        headerCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);   
        headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        headerCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        headerCellStyle.setFont(hearderFont);
        
        // create data(style,font)
        XSSFFont dataFont =  (XSSFFont)workbook.createFont();
        dataFont.setFontHeightInPoints((short)10);
        
        XSSFCellStyle  dataCellStyle = (XSSFCellStyle)workbook.createCellStyle();
        dataCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);              
        dataCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);   
        dataCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);   
        dataCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);   
        dataCellStyle.setFont(dataFont);
        
        // create sheet
        Sheet sheet = workbook.createSheet(excelSheetName);
                
        // create title row
        Row titleRow = sheet.createRow(rowTitleNum);
        titleRow.setHeightInPoints(34);
        Cell titleCell;
        for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
            titleCell = titleRow.createCell(i);
            if(i == 0){
                titleCell.setCellValue(excelTitle);
            }
            titleCell.setCellStyle(titleCellStyle);
        }
        sheet.addMergedRegion(new CellRangeAddress(rowTitleNum,rowTitleNum,0,(headerDataMap.keyList().size()-1)));
        
        // create titleSub row
        Row titleSubRow = sheet.createRow(rowTitleSubNum);
        Cell titleSubCell;
        for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
            titleSubCell = titleSubRow.createCell(i);
            if(i == 0){
                titleSubCell.setCellValue(subTitle);
            }
            titleSubCell.setCellStyle(titleSubCellStyle);
        }
        sheet.addMergedRegion(new CellRangeAddress(rowTitleSubNum,rowTitleSubNum,0,(headerDataMap.keyList().size()-1)));
        
        // create header row
        Row headerRow = sheet.createRow(rowHeaderNum);
        Cell hearderCell;
        for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
            hearderCell = headerRow.createCell(i);
            hearderCell.setCellStyle(headerCellStyle);
            hearderCell.setCellValue((String)headerDataMap.get(i));
        }
        
        // create data row
        Row row = null;
        Cell cell = null;
        for(int rowIdx = 0 ; rowIdx < excelDataList.size() ; rowIdx++){
            row = sheet.createRow(rowDataNum+rowIdx);
            DataMap rowData = excelDataList.get(rowIdx);
            for(int cellIdx = 0 ; cellIdx < headerDataMap.keyList().size() ; cellIdx++){
                cell = row.createCell(cellIdx);
                String cellKey = (String)headerDataMap.keyList().get(cellIdx);
                Object cellData = rowData.get(cellKey);
                if(cellData instanceof String){
                    cell.setCellValue((String)cellData);
                }else if(cellData instanceof Integer){
                    cell.setCellValue((Integer)cellData);
                }else if(cellData instanceof Long){
                    cell.setCellValue((Long)cellData);
                }else if(cellData instanceof Double){
                    cell.setCellValue((Double)cellData);
                }else{ 
                    if(cellData != null){
                        cell.setCellValue(cellData.toString());
                    }
                }
                cell.setCellStyle(dataCellStyle);
            }
        }
        // create cell size 
        for(int i = 0 ; i < headerDataMap.keyList().size() ; i++){
          sheet.autoSizeColumn(i,true);
        }
    }
}