Excel

Spring boot, Java, Javascript를 활용한 엑셀 다운로드/업로드

pom.xml

POI dependency 추가

<!-- POI -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.2</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>	

Download

View

- html

<button type="button" id="btnDownExcel" class="btn btn-primary float-right" onclick="fnDownExcel()">엑셀다운로드</button>

<form id="frmExcelDown" method="GET">
	<input type="hidden" id="exMemberId" name="exMemberId">
	<input type="hidden" id="exMemberName" name="exMemberName">
</form>

- Javascript

function fnDownExcel() {
	let frm = $("#frmExcelDown");
	frm.attr("action", "/rest/admin/memberExcelDown.do");
	frm.submit();
}

Controller

@GetMapping(value="/memberExcelDown.do")
public void memberExcelDown(
	HttpServletResponse response,
	@RequestParam String exMemberId,
	@RequestParam String exMemberName) throws Exception
{
	Map<String, Object> paramsMap = new HashMap<>();
	paramsMap.put("exMemberId", exMemberId);
	paramsMap.put("exMemberName", exMemberName);
    
	Workbook wb = adminService.memberExcelDown(paramsMap);
	response.setContentType("ms-vnd/excel");
	response.setHeader("Content-Disposition", "attachment;filename=ExcelDown1.xlsx"); // 저장될 파일명 지정
	wb.close();
}

Service

public  Workbook memberExcelDown(Map<String, Object> paramsMap) throws Exception, IOException  {
	Workbook wb = new XSSFWorkbook();
    	Sheet sheet = wb.createSheet("사용자목록");
    	Row row = null;
    	Cell cell = null;
	int rowNum = 0;
	    
        // 헤더 스타일 지정
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        // 셀 배경색 지정
        cellStyle.setFillForegroundColor(HSSFColorPredefined.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        row = sheet.createRow(rowNum++); // 행 생성
        cell = row.createCell(0); // 셀 생성
        cell.setCellStyle(cellStyle); // 셀 스타일 지정
        cell.setCellValue("Id"); // 셀에 표시될 문구 지정
        // 필요한만큼 반복
        cell = row.createCell(1);
	cell.setCellValue("Name");
    
    	row = sheet.createRow(rowNum++);
	cell = row.createCell(0);
	cell.setCellValue(paramsMap.get("exMemberId"));
	cell = row.createCell(1);
	cell.setCellValue(paramsMap.get("exMemberName"));
	    
     	return wb;
}

Upload

사용자가 첨부한 엑셀파일을 DB에 Insert

View

- html

 <!-- 엑셀 업로드 form -->
<form id="frmAttachedFiles" class="form-horizontal" enctype="multipart/form-data">
	<div class="btn btn-primary btn-file">
		엑셀업로드
		<input type="file" id="btnUploadExcel" name="btnUploadExcel">
	</div>
</form>	

- Javascript

javascript에서 필수값/정규식 체크를 진행하고 java로 넘겨서 처리해주었다.

// 파일 선택(업로드) 이벤트
$("#btnUploadExcel").on("change", function() {
	fnUploadExcelRegChk();
});

//엑셀업로드 체크
function fnUploadExcelRegChk() {
	let msg = "";
	let input = event.target;
	let reader = new FileReader();
	reader.onload = function() {
		let fdata = reader.result;
		let read_buffer = XLSX.read(fdata, {type : 'binary'});
		read_buffer.SheetNames.forEach(function(sheetName) {
			let rowdata =XLSX.utils.sheet_to_json(read_buffer.Sheets[sheetName]); // Excel 입력 데이터
			// 행 수 만큼 반복
			for(let i=0;i<rowdata.length;i++) {
				// 필수값 체크
				if(rowdata[i].id == null)
					msg += 'id 값이 존재하지 않습니다.';
					console.log(msg);
					return false;
				}
				
				// 정규식 체크
				let keys = Object.keys(rowdata[i]);
				let re=/[^ㄱ-ㅎ가-힣a-zA-Z0-9\-\_\.\@]/gi;
				let reNum=/[^0-9]/gi; 
				for(let j=0;j<keys.length;j++) {
					let data = rowdata[i][keys[j]];
					if(keys[j] == 'id') {
						if(reNum.test(data)) {
							msg = keys[j] + '은 숫자만 입력 가능합니다.';
							console.log(msg);
							return false;
						}
					}
					else {
						if(re.test(data)) {
							msg = keys[j] + '에 허용되지않는 문자가 포함되어있습니다.';
							gfnFailAlert("", msg, gDelay2);
							return false;
						}
					}
				}
			}
			//console.log(JSON.stringify(rowdata));
			fnUploadExcel();
		})
	};
	reader.readAsBinaryString(input.files[0]);
}

// 엑셀업로드
function fnUploadExcel() {
	let apiUrl = "/rest/admin/memberExcelUp.do";
	$.ajax({
		url : apiUrl,
		type : "POST",
		data : new FormData($("#frmAttachedFiles")[0]),
		dataType: "json",
		processData: false,
		contentType: false,
		success: function(result) {
			if (result.resultCode == "SUCCESS") {
				console.log('업로드 성공');
			}
			else {
				console.log('업로드 실패');
			}
		}
	});
}

Controller

@PostMapping(value="/memberExcelUp.do")
public ResponseEntity<RestResultVO> memberExcelUp(MultipartHttpServletRequest request, HttpServletResponse response)
{
	RestResultVO rrVO = new RestResultVO();
	response.setCharacterEncoding("UTF-8");
	try {
		MultipartFile file = null;
		Iterator<String> iterator = request.getFileNames();
		// Excel 파일 가져오기
		if (iterator.hasNext()) {
			file = request.getFile(iterator.next());
		}
		rrVO = adminService.memberExcelUp(file, sessionUser);
		return new ResponseEntity<>(rrVO, HttpStatus.OK);
        } 
        catch (Exception e)
        {
		logger.debug(e.toString());
                return new ResponseEntity<>(rrVO, HttpStatus.OK);
        }
}

Service

public RestResultVO memberExcelUp(MultipartFile excelFile) throws Exception {
	RestResultVO rrVO = new RestResultVO();
	try {
		OPCPackage opcPackage = OPCPackage.open(excelFile.getInputStream()); // 파일 읽어옴
		XSSFWorkbook workbook =  new XSSFWorkbook(opcPackage); 
			
		XSSFSheet sheet = workbook.getSheetAt(0);
		int resultCnt = 0; // DB에 반영된 결과 수 체크용
			
		// 입력된 행의 수만큼 반복
		for(int i=1;i<=sheet.getLastRowNum();i++) {
			MemberVO memberVO = new MemberVO();
			XSSFRow row = sheet.getRow(i); // i번째 행 가져옴
			XSSFCell cell = null;
				
			if(row == null) continue;
			
			// 0번째 열
			cell = row.getCell(0);
			// Cell 값이 null 일 수도 있으므로 체크
			if(cell != null) 
			{
				cell.setCellType(CellType.STRING); // 숫자만 입력받는 경우를 대비해 STRING 처리
				memverVO.setId(cell.getStringCellValue().replace(" ", "")); // 공백처리
			}
            
			// 열의 수만큼 반복
			// 1번째 열
			cell = row.getCell(1);
			if(cell != null) memberVO.setName(cell.getStringCellValue().replace(" ", "")); 
`			
				
			int result = memberMapper.InsertMember(paramsMap); // DB에 반영
				
			// 반영되었는지 체크
			if (result > 0) {
				resultCnt++;
			}
			else {
				throw new Exception();
			}
		}
			
		// 모든 Row가 반영되었는지 체크
		if(resultCnt == sheet.getLastRowNum()) {
			rrVO.setResultCode(C.SUCCESS);
			rrVO.setResultMsg(C.SUCCESS_MSG);
			rrVO.setQueryResult(result);
		}
			
	}
	catch (Exception e) {
		throw e;
	}
	return rrVO;
}