[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
