[JAVA] 쿼리를 사용하여 엑셀다운로드 기능 구현하는 방법

ExceldownController.java 에서의 호출 url 맵핑

@RequestMapping(value={"/excelfiledown.do", "/buz/excelfiledown.do"},  method = RequestMethod.POST)
public ExcelDownloadSXSSFService downloadXL(HttpServletRequest request, ModelMap model) throws Exception 
{
    ArrayList<QueryResult> result = super.requestProcCall(request.getParameter("ac"), request);
    model.addAttribute("XL", result);
    model.addAttribute("request", request);
    model.addAttribute("commonDAO", commonDAO);
    model.addAttribute("logService", logService);

    return new ExcelDownloadSXSSFService();		
}

ExcelDownloadSXSSFService.java

/**
	@file	ExcelDownloadSXSSFService.java
*/

import java.io.IOException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.servlet.view.AbstractView;


public class ExcelDownloadSXSSFService extends AbstractView
{
	protected Logger log = LoggerFactory.getLogger(this.getClass());
	
	private static final String DB_WORK_WRAP_STR = "¿";
	private static final String EXCEL_WORK_WRAP_STR = "n";
	private static final String SPAN_GUBUN = "#";
	
	CommonDAO commonDAO;
	HttpServletRequest request;
	
	LogService logService;
	
	@Override
	@SuppressWarnings("unchecked")
	protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest req, HttpServletResponse res) throws IOException
	{
		ServletOutputStream out = null;
		
		ArrayList<ArrayList<CommonparamMap>> list = null;
		ArrayList<CommonparamMap> excelData = null;
		ArrayList<QueryResult> result = (ArrayList<QueryResult>)model.get("XL");
		
		this.request = (HttpServletRequest)model.get("request");
		this.commonDAO = (CommonDAO)model.get("commonDAO");
		this.logService = (LogService)model.get("logService");
		
		SXSSFWorkbook wb = new SXSSFWorkbook(500);
		wb.setCompressTempFiles(true);
		
		try
		{
			String privacyYn = request.getParameter("privacyYn") != null ? request.getParameter("privacyYn").toString() : "";
			String strFileName = request.getParameter("fileName").toString();
			if(!strFileName.isEmpty())
			{
				strFileName = new String(strFileName.getBytes("iso-8859-1"), "utf-8");
			}
			
			String excelName = URLDecoder.decode(strFileName, "utf-8");
			log.info(excelName + " with SXSSF");
			
			for(int iSheet = 0; iSheet < result.size(); iSheet++) 
			{
				list = (ArrayList<ArrayList<CommonparamMap>>)result.get(iSheet).get(ComnConst.RESULT_LIST);
				
				Sheet ds = wb.createSheet();
				
				// 헤더 정보와 데이터 정보만 있는 경우
				if (list.size() == 2)
				{				
					createColHeader(wb, ds, (ArrayList<CommonparamMap>)list.get(0), null);
					fillXLData(wb, ds, (ArrayList<CommonparamMap>)list.get(1), (ArrayList<CommonparamMap>)list.get(0));
					
					excelData = (ArrayList<CommonparamMap>)list.get(1);
				}
				// Colspan/Rowspan 정보화 헤더정보, 데이터 정보가 있는 경우
				else if (list.size() == 3)
				{
					createColHeader(wb, ds, (ArrayList<CommonparamMap>)list.get(1), (ArrayList<CommonparamMap>)list.get(0));
					fillXLData(wb, ds, (ArrayList<CommonparamMap>)list.get(2), (ArrayList<CommonparamMap>)list.get(1));
					
					excelData = (ArrayList<CommonparamMap>)list.get(2);
				}
			}
				
			res.setContentType("application/vnd.ms-excel; charset=UTF-8");
			res.setHeader("Content-Disposition", "attachment; filename="" + URLEncoder.encode(excelName, "utf-8") + "";");
			
			// 개인정보가 포함된 경우 로그를 쌓는다
			if ("Y".equals(privacyYn))
			{
				int logId = this.logService.insertResultLog(Utility.getSimpleRequestMap(request), excelData);
				logService.insertExcelReason(Utility.getSimpleRequestMap(request), excelData, logId);
			}
			
			out = res.getOutputStream();
			wb.write(out);
		}
		catch (Exception e)
		{
			log.error("Excel Download Error", e);
		}
		finally
		{
			list = null;

			if (out != null)
			{
				out.close();
			}

			wb.dispose();
		}
		
		
	}
	
	@SuppressWarnings("unchecked")
	private void createColHeader(SXSSFWorkbook wb, Sheet sheet, ArrayList<CommonparamMap> data, ArrayList<CommonparamMap> headerInfo)
	{
		List<String> keys = data.get(0).keyList();
		Row headRow = null;
		CellStyle cs = null;
		Cell hCell = null;	
		
		String value = null;
				
		for (int i = 0; i < data.size() - 1; i++)
		{
			headRow = sheet.createRow(i);
			headRow.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints() + 5));
					
			for(int j = 0; j < keys.size(); j++) 
			{
				value = data.get(i).get(keys.get(j)).toString();
				
				if ("".equalsIgnoreCase(value))
				{
					continue;
				}
				
				value = value.replaceAll(DB_WORK_WRAP_STR, EXCEL_WORK_WRAP_STR);
				
				hCell = headRow.createCell(j);	
				cs = wb.createCellStyle();
				cs.setWrapText(true);
				cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				
				hCell.setCellStyle(cs);
				hCell.setCellType(HSSFCell.CELL_TYPE_STRING);
				hCell.setCellValue(value);
				
				if (headerInfo == null)
				{
					continue;
				}
				else
				{
					setHeaderStyle(sheet, headerInfo, i, j);
				}
				
				sheet.setColumnWidth(j, 5000);
			}
		}
		
		headRow = null;
		keys = null;
		cs = null;
		hCell = null;
	}
	
	@SuppressWarnings("unchecked")
	private void setHeaderStyle(Sheet sheet, ArrayList<CommonparamMap> headerInfo, int i, int j)
	{
		List<String> keys = headerInfo.get(0).keyList();
		String value = headerInfo.get(i).get(keys.get(j)).toString();
		
		if (!"".equalsIgnoreCase(value))
		{
			String[] span = value.split(SPAN_GUBUN);
			
			int colspan = Integer.parseInt(span[0]) - 1;
			int rowspan = Integer.parseInt(span[1]) - 1;
			
			sheet.addMergedRegion(new CellRangeAddress(i, i + rowspan, j, j + colspan));
		}
	}
	
	@SuppressWarnings("unchecked")
	private void fillXLData(SXSSFWorkbook wb, Sheet sheet, ArrayList<CommonparamMap> data, ArrayList<CommonparamMap> header)
	{
		if(data.size() == 0) return;
		
		int headerSize = header.size() - 1;

		List<String> keys = data.get(0).keyList();
		
		Row dRow = null;
		Cell dCell = null;
		CellStyle style = null;
		
		String value = null;
		
		for(int i = 0; i < data.size(); i++)
		{
			dRow = sheet.createRow(i + headerSize);
			
			for(int j = 0; j < keys.size(); j++)
			{
				dCell = dRow.createCell(j);
				style = wb.createCellStyle();

				value = data.get(i).get(keys.get(j)).toString();
				
				if (value.length() > 0)
				{
					if(header.get(headerSize).get(keys.get(j)).toString().equals("numeric"))
					{				
						style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
						style.setAlignment(CellStyle.ALIGN_RIGHT);
						dCell.setCellStyle(style);
						dCell.setCellValue(Double.parseDouble(value));	
					}
					else
					{
						value = value.replaceAll(DB_WORK_WRAP_STR, EXCEL_WORK_WRAP_STR);
						
						style.setWrapText(true);
						style.setAlignment(CellStyle.ALIGN_LEFT);
						style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
						
						dCell.setCellStyle(style);		
						dCell.setCellType(HSSFCell.CELL_TYPE_STRING);
						dCell.setCellValue(value);						
					}
					
					sheet.setColumnWidth(j, 5000);
				}
			}
		}
		
		keys = null;		
		dRow = null;
		dCell = null;
		style = null;
	}
}

-- 자료 목록 엑셀 조회
IF @P_WORK_TP IN ( 'Q_LIB_EXCEL_LIST' ) 
BEGIN
-----------------------------------------------------------------------------------------------------------------------
    SELECT '번호'			AS RNUM
        , '제품'			AS PROD_NM
        , '관리번호'		AS LIB_MNG_NO
        , '카테고리'		AS LIB_CATE
        , '제목'			AS LIB_NM

        , '키워드'			AS KEYWORDS
        , '등록인'			AS MNG_EMP_NM
        , '등록일'			AS LIB_REG_YMD
        , '유효기간'		AS LIB_EXPIRED_YMD

    UNION ALL

    SELECT 'string','string','string','string'
        ,'string','string','string','string','string'
    ;

    SELECT
        RNUM = ROW_NUMBER() OVER(ORDER BY crmLib.LIB_EXPIRED_YMD DESC, crmLib.LIB_REG_YMD DESC, crmLib.LIB_NM)
        , PROD_NM = mstProd.PROD_NM
        , LIB_MNG_NO = crmLib.LIB_MNG_NO
        , LIB_CATE = commonCode.CODE_D_NM
        , LIB_NM = crmLib.LIB_NM
        , KEYWORDS = crmLib.KEYWORDS
        , (SELECT EMP_NM FROM MST_EMP WHERE DMN = @P_DMN AND USER_ID = crmLib.CRT_USER ) AS MNG_EMP_NM
        , LIB_REG_YMD = CONVERT(VARCHAR, CONVERT(DATE, crmLib.LIB_REG_YMD), 120)
        , LIB_EXPIRED_YMD = CONVERT(VARCHAR, CONVERT(DATE, crmLib.LIB_EXPIRED_YMD), 120)
    FROM TB_LIB crmLib
    INNER JOIN TB_LIB_PROD crmLibProd ON crmLibProd.DMN = crmLib.DMN AND crmLibProd.LIB_ID = crmLib.LIB_ID
    INNER JOIN TB_ITEM mstProd ON mstProd.DMN = crmLibProd.DMN AND mstProd.PROD_ID = crmLibProd.PROD_ID
    OUTER APPLY [dbo].[FN_COMP_CODE_NM](crmLib.DMN, 'LIB_CATEGORY', crmLib.LIB_CATE_CD, '') commonCode
    WHERE crmLib.DMN = @P_DMN_COND
    AND crmLib.LIB_CATE_CD != 'B01' --브로셔제외
    AND ('' = @P_LIB_CATE_CD OR crmLib.LIB_CATE_CD = @P_LIB_CATE_CD)
    AND ('' = @P_PROD_ID OR crmLibProd.PROD_ID = @P_PROD_ID)
    AND ('' = @P_KEYWORDS OR (crmLib.KEYWORDS LIKE '%' + @P_KEYWORDS + '%') OR (crmLib.LIB_NM LIKE '%' + @P_KEYWORDS + '%'))
    AND crmLib.STA_CD = 'O'
    ORDER BY crmLib.LIB_EXPIRED_YMD DESC, crmLib.LIB_REG_YMD DESC, crmLib.LIB_NM ASC

    -- 실행된레코드행수 받기 
    SELECT @P_ROW_COUNT = @@ROWCOUNT;

    IF @P_ROW_COUNT > 0
        SET @P_ERROR_CD = 'MSG0001';	-- 정상적으로 조회가 되었습니다 
    ELSE
        SET @P_ERROR_CD = 'MSG0006';	-- 조회된 자료가 없습니다
END

--셀 병합이 있는 테스트

-- 제품설명회 지출보고 엑셀 다운로드
IF @P_WORK_TP = 'Q_EXPENDITURE_EXL'
BEGIN

    -- Colspan / Rowspan
    SELECT '1#2'				AS ROW_NUM
            , '1#2'				AS PROD_NM

            , '2#1'				AS CONT_NM
            , ''				AS COMP_NM

            , '4#1'				AS TRAN_AMT
            , ''				AS GIFT_AMT
            , ''				AS STAY_AMT
            , ''				AS FNB_AMT
            , '1#2'				AS DM_LOC
            , '1#2'				AS START_DT
    UNION ALL
    SELECT	''				AS ROW_NUM
            , ''			AS PROD_NM

            , ''			AS CONT_NM
            , ''			AS COMP_NM

            , ''			AS TRAN_AMT
            , ''			AS GIFT_AMT
            , ''			AS STAY_AMT
            , ''			AS FNB_AMT
            , ''			AS DM_LOC
            , ''			AS START_DT

    -- 컬럼 헤더 정보
    SELECT '연번'				AS ROW_NUM
            , '제품명(표준코드명칭)' AS PROD_NM

            , '의료인 정보'		AS CONT_NM
            , ''				AS COMP_NM

            , '지원금액'		AS TRAN_AMT
            , ''				AS GIFT_AMT
            , ''				AS STAY_AMT
            , ''				AS FNB_AMT

            , '장소'			AS DM_LOC
            , '일시'			AS START_DT
    UNION ALL
    SELECT	''					AS ROW_NUM
            , ''				AS PROD_NM

            , '성명'			AS CONT_NM
            , '소속'			AS COMP_NM
            , '식음료비'		AS FNB_AMT
            , '기념품비'		AS GIFT_AMT
            , '교통비'			AS TRAN_AMT
            , '숙박비'			AS STAY_AMT

            , ''				AS DM_LOC
            , ''				AS START_DT

    UNION ALL

    SELECT 'string','string','string','string','string'
            ,'string','string','string','string','string'
    ;


    SELECT 
        ROW_NUMBER() OVER(ORDER BY cont.CPR_ID, cont.SUB_SEQ ) AS ROW_NUM 
        , CASE WHEN tb_item.STANDARD_CD IS NULL OR tb_item.STANDARD_CD = '' 
            THEN tb_item.PROD_NM ELSE  tb_item.PROD_NM + '('+ tb_item.STANDARD_CD +')' 
            END AS  PROD_NM
        , cont.CONT_NM	
        , cont.COMP_NM						
        , FORMAT(cont.FNB_AMT, '#,#') +'원' AS FNB_AMT 
        , FORMAT(cont.GIFT_AMT, '#,#') +'원' AS GIFT_AMT 
        , FORMAT(cont.TRAN_AMT, '#,#') +'원' AS TRAN_AMT 
        , FORMAT(cont.STAY_AMT, '#,#') +'원' AS STAY_AMT 
        , mst.DM_LOC
        , CONVERT(VARCHAR, CONVERT(DATE, mst.START_DT, 120)) +' ' + START_DT_HOUR  + ':' + START_DT_MN AS START_DT
     FROM TB_TEST_CONT cont
     INNER JOIN TB_TEST mst ON mst.DMN = cont.DMN AND mst.CPR_ID = cont.CPR_ID
     INNER JOIN TB_TEST_PROD prod ON prod.DMN = mst.DMN AND prod.CPR_ID = mst.CPR_ID
     INNER JOIN TB_ITEM tb_item ON tb_item.DMN = mst.DMN AND tb_item.PROD_ID = prod.PROD_ID
     WHERE mst.DMN = @P_DMN_COND
     AND mst.CPR_ID = @P_CPR_ID
     AND mst.STA_CD = 'O'
    ; 
END