[Spring] Excel 파일 업로드/ import / 엑셀업로드 / 첨부파일 / 엑셀 값 읽기 / Java

출처: https://daydreamer-92.tistory.com/42 [아는게1도없다]

 

 

		
			function excelUpload() {
				$("#excelFrm").attr("action", "${HOME}/ajax/admin/user/pf/excel/create").ajaxForm({
					beforeSend: function () {
						$.blockUI();
					},
					type: "POST",
					dataType:"json",
					success:function(data){
						if (data.status == "200") {
							alert(data.success_cnt+$.i18n.prop("lang2009"));
							location.reload();
						} else if (data.status == "301") {//사번 중복검사
							alert(data.msg+$.i18n.prop("lang2133"));
						} else if (data.status == "302") {//이메일 중복검사
							alert(data.msg+$.i18n.prop("lang1995"));
						} else if (data.status == "303") {//아이디 중복검사
							alert(data.msg+$.i18n.prop("lang2011"));
						} else {
							alert($.i18n.prop("lang1355"));
							location.reload();
						}
					},
					error: function (jqXHR, textStatus, errorThrown) {
						document.write(xhr.responseText);
				    },
			        complete:function() {
			            $.unblockUI();
			        }
				}).submit();
			}

 

 

 

	//교수관리 교수 엑셀 일괄 등록
	@RequestMapping(value = "/ajax/admin/user/pf/excel/create", method = RequestMethod.POST)
	public String pfExcelCreate (HttpServletRequest request, @RequestParam HashMap<String, Object> param, Model model) throws Exception {
		Util.requiredCheck(param, new String[] {"uploadFile"});
		param.put("s_user_seq", request.getSession().getAttribute("S_USER_SEQ"));
		model.addAllAttributes(service.pfExcelCreate(request, param));
		return JSON_VIEW;
	}

 

 

	@SuppressWarnings("unchecked")
	public List<HashMap<String, Object>> getPfListForExcel(HttpServletRequest request) throws Exception {
		List<HashMap<String, Object>> pfInfoList = new ArrayList<HashMap<String, Object>>();
		MultipartHttpServletRequest mRequest = (MultipartHttpServletRequest) request;
		MultipartFile uploadFile = mRequest.getFile("uploadFile");
		if (uploadFile != null) {
			@SuppressWarnings("resource")
			XSSFWorkbook workBook = new XSSFWorkbook(uploadFile.getInputStream());
			XSSFSheet curSheet = null;
			XSSFRow curRow = null;
			XSSFCell curCell = null;
			DataFormatter formatter = new DataFormatter();
			HashMap<String, Object> pfInfo = null;
			//현재 sheet 반환
			curSheet = workBook.getSheetAt(0);
			//row 탐색 for문
			for (int rowIndex = 0; rowIndex < curSheet.getPhysicalNumberOfRows(); rowIndex++) {
				//0번째 row는 Header 정보이기 때문에 pass
				if (rowIndex != 0) {
					//현재 row 반환
					curRow = curSheet.getRow(rowIndex);
					pfInfo = new HashMap<String, Object>();
					for (int cellIndex = 0; cellIndex < 10; cellIndex++) {
						curCell = curRow.getCell(cellIndex);
						if (curCell != null) {
							String value = formatter.formatCellValue(curCell);
							switch (cellIndex) {	
								case 1:
									pfInfo.put("id", value);
									break;
								case 2:
									pfInfo.put("name", value);
									break;
								case 3:
									pfInfo.put("professor_id", value);
									break;
								case 4:
									pfInfo.put("position_code", value);
									break;
								case 5:
									pfInfo.put("department_code", value);
									break;
								case 6:
									pfInfo.put("specialty_code", value);
									break;
								case 7:
									pfInfo.put("tel", value);
									break;
								case 8:
									pfInfo.put("email", value);
									break;
								default:
									break;
							}
						}
					}
					String name = String.valueOf(pfInfo.get("name"));
					String email = String.valueOf(pfInfo.get("email"));
					if (!name.equals("") && !email.equals("")) {
						pfInfoList.add((HashMap<String, Object>)pfInfo.clone());
					}
				}
			}
		}
		return pfInfoList;
	}

 

 

 

@Transactional
	public ResultMap pfExcelCreate(HttpServletRequest request, HashMap<String, Object> param) throws Exception {
		ResultMap resultMap = new ResultMap();
		List<HashMap<String, Object>> pfInfoList = getPfListForExcel(request);
		int successCnt = 0;
		for (HashMap<String, Object> map : pfInfoList) {
			successCnt++;
			int cnt = 0;
			//사번 중복검사
			String professorId = String.valueOf(map.get("professor_id"));
			if (professorId != null && !professorId.equals("")) {
				cnt = dao.getExistProfessorIdCount(param);
				if (cnt > 0) {
					throw new RuntimeLogicException(String.valueOf(successCnt), "301");
				}
			}
			
			//이메일 중복검사
			String email = String.valueOf(map.get("email"));
			if (email != null && !email.equals("")) {
				cnt = dao.getExistUserEmailCount(map);
				if (cnt > 0) {
					throw new RuntimeLogicException(String.valueOf(successCnt), "302");
				}
			}
			
			//아이디 중복검사 : 입력된 아이디가 잇으면 아이디로 입력된 아이디가 없으면 이메일로
			String id = String.valueOf(map.get("id"));
			if (id.equals("null")) {
				id="";
			}
			cnt = dao.getExistUserIdCount(map);
			if (cnt > 0) {
				throw new RuntimeLogicException(String.valueOf(successCnt), "303");
			}
			
			HashMap<String, Object> userParam = new HashMap<String, Object>();
			userParam.put("name", map.get("name")); //이름 (필수)
			userParam.put("professor_id", map.get("professor_id")); //사번
			userParam.put("department_code", map.get("department_code")); //직위
			userParam.put("position_code", map.get("position_code")); //소속
			userParam.put("specialty_code", map.get("specialty_code")); //세부전공
			userParam.put("tel", map.get("tel")); //연락처
			userParam.put("email", map.get("email")); //이메일 (필수)
			userParam.put("s_user_seq", param.get("s_user_seq")); //이메일 (필수)
			if (id != null && !id.equals("")) {
				userParam.put("id", id); //아이디
			} else {
				userParam.put("id", map.get("email")); //아이디 값이 없으면 이메일 주소로 아이디 등록
			}
			userParam.put("pwd", Util.getSHA256(INIT_PASSWORD)); //초기비밀번호
			if (dao.insertProfessorInfo(userParam) < 1) {
				throw new RuntimeLogicException("QUERY_FAIL [ users insert query fail]", "004");
			}
		}
		resultMap.put("success_cnt", successCnt);
		return resultMap;
	}
	

 

<insert id="insertProfessorInfo" parameterType="hashmap">
		INSERT INTO users
			(
				id
				, pwd
				, name
				, tel
				, email
				, professor_id
				, department_code
				, position_code
				, specialty
				, attend_code
				, account_use_state
				, picture_path
				, picture_name
				, user_level
				, reg_date
				, reg_user_seq
				, use_flag
			)
		VALUES
			(
				#{id}
				, #{pwd}
				, #{name}
				, #{tel}
				, #{email}
				, #{professor_id}
				, #{department_code}
				, #{position_code}
				, #{specialty_code}
				, '00'
				, 'Y'
				, #{picture_path}
				, #{picture_name}
				, 3
				, NOW()
				, #{s_user_seq}::INTEGER
				, 'Y'
			)
	</insert>