[Spring Boot] 엑셀 다운로드

<a href="javascript:fn_excelDown()" >엑셀다운로드</a>

 

function fn_excelDown(){
	if(confirm("다운받으시겠습니까?")){
		document.frm.action = "<c:url value='/user/loginLogList/excelDownload.do'/>";
		document.frm.submit();
	}
}

 

 

controller

@SuppressWarnings("unchecked")
@RequestMapping(value = "/user/loginLogList/excelDownload.do", produces = "application/x-msdownload;charset=utf-8")
public void itemExcelDownload(@ModelAttribute("searchVO") UserVO vo, HttpServletRequest request,
		HttpServletResponse response) throws Exception {
	logger.info("Call /user/loginLogList/excelDownload.do");
	final String fileExt = ".xlsx";

	BufferedOutputStream out = null;
	XSSFWorkbook workbook = null;

	try {
		boolean check = true;
		// 권한체크
		if (check) {
			// 엑셀 다운로드 목록
			Map result = loginLogService.selectExcelList(vo);

			// 데이터
			List excelList = (List) result.get("excelList");

			// 헤더
			List<String> headerList = (List) result.get("headerList");

			// 총계 (bottom)
			List bottomList = (List) result.get("bottomList");

			// 타입
			List<String> typeList = (List) result.get("typeList");

			// 현재 시간
			String currentTime = DateUtil.getCurrentDateTime();
			String title = "carforu_로그인이력";
			String fileNm = title;
			// 다운로드 파일명fileNm은 title + _현재시간
			fileNm += "_".concat(currentTime);
			excelDownloadService.setDisposition(fileNm.concat(fileExt), request, response);
			workbook = excelDownloadService.downloadExcel(title, headerList, bottomList, excelList, typeList);

			if (workbook != null) {
				out = new BufferedOutputStream(response.getOutputStream());
				workbook.write(out);
			}
		}
	} catch (NullPointerException e) {
		// 2023-07 (SW 보안취약점 조치)
		logger.error(e.getMessage(), e);
	} catch (Exception | Error e) {
		logger.error(e.getMessage(), e);
	} finally {
		if (workbook != null) {
			workbook.close();
		}
		if (out != null) {
			out.close();
		}
	}
}

 

 

 

service 

public XSSFWorkbook downloadExcel(String sheetNm, List<String> headerList, List<Map<String, Object>> bottomList, 
		List<Map<String, Object>> excelList, List<String> typeList) throws Exception {
	int baseRowCnt = 0;
	int baseCellCnt = 0;
	XSSFWorkbook workbook = null;
	try {
		workbook = new XSSFWorkbook();
		// sheet생성
		XSSFSheet sheet = workbook.createSheet(sheetNm);
		sheet.setColumnWidth((short)0, (short)500); // 1000이 열 너비 3.14
		
		// 다운로드 시간 row 스타일 설정
		XSSFCellStyle currentTimeStyle = getCellStyle("1", workbook);
		// header row 스타일 설정
		XSSFCellStyle headerStyle = getCellStyle("2", workbook);
		// header row 주황색 스타일
		XSSFCellStyle headerStyleOrange = getCellStyle("6", workbook);
		// header row 하늘색 스타일
		XSSFCellStyle headerStyleBlue = getCellStyle("7", workbook);
		// header row 숫자 스타일 설정(오른쪽 정렬)
		XSSFCellStyle headerIntStyle = getCellStyle("3", workbook);
		// row 스타일 설정
		XSSFCellStyle cellStyle = getCellStyle("4", workbook);
		
		// row 숫자 스타일(오른쪽 정렬)
		XSSFCellStyle cellIntStyle = getCellStyle("5", workbook);

		// 첫번째 줄 시간표시 row 생성
		XSSFRow row = sheet.createRow(baseRowCnt++);
		XSSFCell cell = row.createCell(baseCellCnt);
		
		sheet.setColumnWidth(0, 10 * 265);
		sheet.setColumnWidth(1, 16 * 265);
		sheet.setColumnWidth(2, 16 * 265);
		sheet.setColumnWidth(3, 15 * 265);
		sheet.setColumnWidth(4, 15 * 265);
		sheet.setColumnWidth(5, 10 * 265);
		sheet.setColumnWidth(6, 15 * 265);
		sheet.setColumnWidth(7, 15 * 265);
		
		cell = row.createCell(0);
		cell.setCellStyle(currentTimeStyle);
		cell.setCellValue("다운로드 일시");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
		cell = row.createCell(2);
		cell.setCellValue(DateUtil.getCurrentDateTimeView());
		
		row = sheet.createRow(baseRowCnt++);
		row.setHeight((short) 400);
		for (int headCellCnt = 0; headCellCnt < headerList.size(); headCellCnt++) {
			cell = row.createCell(headCellCnt);
			cell.setCellStyle(headerStyleBlue);
			cell.setCellValue(String.valueOf(headerList.get(headCellCnt)));
		}
		
		DecimalFormat formatter = new DecimalFormat("###,###,###,###");
		for (int rowCnt = 0; rowCnt < excelList.size(); rowCnt++) {
			row = sheet.createRow(baseRowCnt++);
			row.setHeight((short) 400);
			Map<String, Object> excelMap = excelList.get(rowCnt);
			int cellCnt = 0;
			for (int i = 0; i < excelMap.size(); i++) {
				cell = row.createCell(cellCnt++);
				String value = this.getMapToKey(excelMap, "cell"+(i+1));
				
				if(typeList != null && typeList.size() > 0) {
					String type = "";
					for (int j = 0; j < typeList.size(); j++) {
						type = typeList.get(i);
					    if( isNumeric( value ) && "number".equalsIgnoreCase(type) && !"String".equalsIgnoreCase(type)) {
							cell.setCellStyle(cellIntStyle);
							value = formatter.format(Double.parseDouble(value));
							cell.setCellType(CellType.NUMERIC);
						}else {
							cell.setCellStyle(cellStyle);
						}
					}
				    
				}else {
					if( isNumeric( value )) {
						value = formatter.format(Double.parseDouble(value));
						cell.setCellStyle(cellIntStyle);
					}else {
						cell.setCellStyle(cellStyle);
					}
				}
				
				cell.setCellValue(value);
			}
		}
		
	} catch (NullPointerException e) {
		// 2023-07 (SW 보안취약점 조치)
		logger.error(e.getMessage(), e);
	} catch (Exception|Error e) {
		logger.error(e.getMessage(), e);
	}
	return workbook;
}



public static String getMapToKey(Map<?,?> map, String key) {
	return (map != null)?((map.get(key)!=null)?(map.get(key).toString().trim()):""):"";
}