1626
No
[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>
댓글 ( 5)
댓글 남기기