Java tutorial
/* * Copyright 2012-2014 sammyun.com.cn. All rights reserved. * Support: http://www.sammyun.com.cn * License: http://www.sammyun.com.cn/license */ package com.sammyun.service.impl; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import org.apache.commons.codec.digest.DigestUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service; import com.sammyun.Setting; import com.sammyun.controller.console.ExcelController; import com.sammyun.dao.ExcelDao; import com.sammyun.dao.MemberDao; import com.sammyun.dao.dict.DictClassDao; import com.sammyun.dao.dict.DictStudentDao; import com.sammyun.entity.ExcelMessage; import com.sammyun.entity.ExcelMessage.Status; import com.sammyun.entity.Member; import com.sammyun.entity.Member.MemberType; import com.sammyun.entity.course.CurriculumSchedule; import com.sammyun.entity.course.SchoolYearMng; import com.sammyun.entity.dict.DictClass; import com.sammyun.entity.dict.DictSchool; import com.sammyun.entity.dict.DictStudent; import com.sammyun.entity.dict.DictStudent.StudentStatus; import com.sammyun.service.AdminService; import com.sammyun.service.ExcelService; import com.sammyun.service.MemberService; import com.sammyun.util.EduUtil; import com.sammyun.util.SettingUtils; /** * Service - Excel * * @author Sencloud Team * @version 3.0 */ @Service("excelServiceImpl") public class ExcelServiceImpl implements ExcelService { /** */ private static final Logger logger = LoggerFactory.getLogger(ExcelController.class); @Resource(name = "memberDaoImpl") private MemberDao memberDao; @Resource(name = "dictClassDaoImpl") private DictClassDao dictClassDao; @Resource(name = "dictStudentDaoImpl") private DictStudentDao dictStudentDao; @Resource(name = "excelDaoImpl") private ExcelDao excelDao; @Resource(name = "adminServiceImpl") private AdminService adminService; @Resource(name = "memberServiceImpl") private MemberService memberService; private static HSSFWorkbook courseWorkbook; @Override public List<Member> getMembersByExcel(InputStream is) throws Exception { // TODO Auto-generated method stub HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<Member> members = new ArrayList<Member>(); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); Member member = new Member(); String username = null; String mobile = null; String realName = null; for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (i == 0) { // ?? username = memberCell.toString(); } if (i == 1) { // mobile = memberCell.toString(); } if (i == 2) { // ?? realName = memberCell.toString(); } } // ? if (username != null) { member.setUsername(username); } else { String errorString = "" + rowNum + "????"; return null; } if (mobile != null) { member.setMobile(mobile); } else { String errorString = "" + rowNum + "???"; return null; } if (realName != null) { member.setRealName(realName); } else { String errorString = "" + rowNum + "????"; return null; } members.add(member); } } return members; } @Override public List<Member> getMembers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.setIsUpdate(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } } // end?? members.add(member); } // endrow } // end return members; } @Override public ExcelMessage validateMembers(List<Member> members) { ExcelMessage ret = new ExcelMessage(); if (members.size() == 0) { ret.setStatus(Status.fail); ret.setError(""); return ret; } // ??? ExcelMessage selfValidate = excelDao.validateMemberSelf(members); if (selfValidate.getStatus().toString().equals("fail")) { return selfValidate; } // start ? for (int i = 0; i < members.size(); i++) { int num = i + 1; Member member = members.get(i); // ?? if (member.getUsername() != null) { if (memberDao.usernameExists(member.getUsername())) { ret.setStatus(Status.fail); ret.setError("" + num + "????"); return ret; } else { if (member.getUsername().length() > 100) { ret.setStatus(Status.fail); ret.setError("" + num + "???100?"); return ret; } // ??[a-zA-Z0-9_\-./ ] // Pattern pattern = Pattern.compile("[a-zA-Z0-9_-./ ]*"); // Matcher matcher = // pattern.matcher(member.getUsername()); // boolean b= matcher.matches(); // if(!b){ // ret.setStatus(Status.fail); // ret.setError("" + num + "??????"); // return ret; // } } } else { ret.setStatus(Status.fail); ret.setError("" + num + "?????"); return ret; } // ? if (member.getMobile() != null) { if (memberDao.mobileUnique(member.getMobile())) { ret.setStatus(Status.fail); ret.setError("" + num + "???"); return ret; } else { if (!EduUtil.isMobile(member.getMobile())) { ret.setStatus(Status.fail); ret.setError("" + num + "?????"); return ret; } } } else { ret.setStatus(Status.fail); ret.setError("" + num + "????"); return ret; } // ?? if (member.getRealName() == null) { ret.setStatus(Status.fail); ret.setError("" + num + "?????"); return ret; } } // end ? ret.setOk("?" + members.size() + "?"); ret.setStatus(Status.success); return ret; } /** * ?excel <??> * * @param in * @return * @throws IOException * @see [?#?#?] */ @Override public List<CurriculumSchedule> getCourses(SchoolYearMng schoolYearMng, DictClass dictClass) { List<CurriculumSchedule> courses = new ArrayList<CurriculumSchedule>(); // start for (int numSheet = 0; numSheet < courseWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = courseWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); CurriculumSchedule course = new CurriculumSchedule(); int week = 1; String teacherName = null; String courseName = null; String lessons = null; String classRoom = null; String startTime = null; String endTime = null; // start?? for (int i = 0; i < courseInfo.getLastCellNum(); i++) { HSSFCell courseCell = courseInfo.getCell(i); if (i == 0) { // String weekString = courseCell.toString(); week = Integer.parseInt(weekString); course.setWeek(week); } if (i == 1) { // ? teacherName = courseCell.toString(); course.setTeacherName(teacherName); } if (i == 2) { // ?? courseName = courseCell.toString(); course.setCourseName(courseName); } if (i == 3) { // lessons = courseCell.toString(); course.setLessons(lessons); } if (i == 4) { // if (courseCell != null) { classRoom = courseCell.toString(); course.setClassRoom(classRoom); } } if (i == 5) { // startTime = courseCell.toString(); if (startTime.contains("")) { startTime = startTime.replace('', ':'); } course.setStartTime(startTime); } if (i == 6) { // ? endTime = courseCell.toString(); if (endTime.contains("")) { endTime = endTime.replace('', ':'); } course.setEndTime(endTime); } } // end?? course.setDictClass(dictClass); course.setSchoolYearMng(schoolYearMng); courses.add(course); } // end Row } // end return courses; } /** * ?week <??> * * @param in * @return * @throws Exception * @see [?#?#?] */ @Override public ExcelMessage validateCourseWeekLessions(InputStream is) throws Exception { courseWorkbook = new HSSFWorkbook(is); ExcelMessage excelMessage = new ExcelMessage(); excelMessage.setStatus(Status.success); // start for (int numSheet = 0; numSheet < courseWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = courseWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // ? HSSFRow titleInfo = hssfSheet.getRow(0); ExcelMessage titleValidate = new ExcelMessage(); titleValidate.setStatus(Status.fail); titleValidate.setError("???"); if ((titleInfo.getCell(0) == null) || (!titleInfo.getCell(0).toString().equals(""))) { return titleValidate; } if ((titleInfo.getCell(1) == null) || (!titleInfo.getCell(1).toString().equals("?"))) { return titleValidate; } // start Row System.out.println(hssfSheet.getLastRowNum()); if (hssfSheet.getLastRowNum() == 0) { excelMessage.setStatus(Status.fail); excelMessage.setError("?"); is.close(); return excelMessage; } // week for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); if (courseInfo == null) { continue; } if (courseInfo.getCell(0) == null) { excelMessage.setStatus(Status.fail); excelMessage.setError("" + rowNum + "?"); is.close(); return excelMessage; } String weekString = courseInfo.getCell(0).toString(); try { int week = Integer.parseInt(weekString); if (week == 1) { continue; } else if (week == 2) { continue; } else if (week == 3) { continue; } else if (week == 4) { continue; } else if (week == 5) { continue; } else if (week == 6) { continue; } else if (week == 7) { continue; } else { excelMessage.setStatus(Status.fail); excelMessage.setError( "" + rowNum + "?1-7"); return excelMessage; } } catch (Exception e) { logger.error("" + rowNum + "?" + e.getMessage()); excelMessage.setStatus(Status.fail); excelMessage .setError("" + rowNum + "?1-7"); is.close(); return excelMessage; } } // lessons for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); if (courseInfo == null) { continue; } if (courseInfo.getCell(3) == null) { excelMessage.setStatus(Status.fail); excelMessage.setError("" + rowNum + "?"); is.close(); return excelMessage; } String lessonString = courseInfo.getCell(3).toString(); try { int lesson = Integer.parseInt(lessonString); if (lesson == 1) { continue; } else if (lesson == 2) { continue; } else if (lesson == 3) { continue; } else if (lesson == 4) { continue; } else if (lesson == 5) { continue; } else if (lesson == 6) { continue; } else if (lesson == 7) { continue; } else if (lesson == 8) { continue; } else { excelMessage.setStatus(Status.fail); excelMessage.setError( "" + rowNum + "?1-8"); is.close(); return excelMessage; } } catch (Exception e) { logger.error("" + rowNum + "?" + e.getMessage()); excelMessage.setStatus(Status.fail); excelMessage .setError("" + rowNum + "?1-8"); is.close(); return excelMessage; } } // start ??? for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); if (courseInfo == null) { continue; } if (courseInfo.getCell(2) == null) { excelMessage.setStatus(Status.fail); excelMessage.setError("" + rowNum + "???"); is.close(); return excelMessage; } } // end ??? } return excelMessage; } @Override public List<Member> getTeachers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.s(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } if (i == 2) { // ??-? String classTeacherMap = memberCell.toString(); classTeacherMap = classTeacherMap.trim(); member.setAddress(classTeacherMap); } } // end?? members.add(member); } // endrow } // end return members; } @Override public ExcelMessage validateTeachers(List<Member> members) { ExcelMessage ret = new ExcelMessage(); DictSchool dictSchool = adminService.getCurrentDictSchool(); if (members.size() == 0) { ret.setStatus(Status.fail); ret.setError(""); return ret; } // ??? ExcelMessage selfValidate = excelDao.validateMemberSelf(members); if (selfValidate.getStatus().toString().equals("fail")) { return selfValidate; } // start ? for (int i = 0; i < members.size(); i++) { int num = i + 1; Member member = members.get(i); // ?? if (member.getUsername() != null) { if (memberDao.usernameExists(member.getUsername())) { ret.setStatus(Status.fail); ret.setError("" + num + "????"); return ret; } else { if (member.getUsername().length() > 100) { ret.setStatus(Status.fail); ret.setError("" + num + "???100?"); return ret; } // ??[a-zA-Z0-9_\-./ ] // Pattern pattern = Pattern.compile("[a-zA-Z0-9_-./ ]*"); // Matcher matcher = // pattern.matcher(member.getUsername()); // boolean b= matcher.matches(); // if(!b){ // ret.setStatus(Status.fail); // ret.setError("" + num + "??????"); // return ret; // } } } else { ret.setStatus(Status.fail); ret.setError("" + num + "?????"); return ret; } // ? if (member.getMobile() != null) { if (memberDao.mobileUnique(member.getMobile())) { ret.setStatus(Status.fail); ret.setError("" + num + "???"); return ret; } else { if (!EduUtil.isMobile(member.getMobile())) { ret.setStatus(Status.fail); ret.setError("" + num + "?????"); return ret; } } } else { ret.setStatus(Status.fail); ret.setError("" + num + "????"); return ret; } // ?? if (member.getRealName() == null) { ret.setStatus(Status.fail); ret.setError("" + num + "?????"); return ret; } // ?? if (member.getAddress() != null) { ret.setStatus(Status.fail); String classNameString = member.getAddress(); classNameString = classNameString.replaceAll("", ","); String[] classNames = classNameString.split(","); for (String className : classNames) { List<DictClass> dictClasses = dictClassDao.getClassByName(className.trim(), dictSchool); if (dictClasses == null) { ret.setError("" + num + "? " + className.trim() + " ??"); return ret; } else if (dictClasses.size() == 0) { ret.setError("" + num + "? " + className.trim() + " ??"); return ret; } } } } // end ? ret.setOk("?" + members.size() + "?"); ret.setStatus(Status.success); return ret; } @Override public List<DictStudent> getDictStudents(InputStream inputStream, DictSchool dictSchool, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<DictStudent> dictStudents = new ArrayList<DictStudent>(); // start-- for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow studentInfo = hssfSheet.getRow(rowNum); if (studentInfo == null) { continue; } DictStudent dictStudent = new DictStudent(); dictStudent.setCreateDate(new Date()); dictStudent.setModifyDate(new Date()); dictStudent.setStudentStatus(StudentStatus.active); String studentName = null; String studentNo = null; DictClass dictClass = new DictClass(); // start_?? for (int i = 0; i < studentInfo.getLastCellNum(); i++) { HSSFCell studentCell = studentInfo.getCell(i); if (studentCell == null) { continue; } if (i == 0) { // ? studentNo = studentCell.toString(); studentNo = studentNo.trim(); dictStudent.setStudentNo(studentNo); } if (i == 1) { // ?? studentName = studentCell.toString(); studentName = studentName.trim(); dictStudent.setStudentName(studentName); } if (i == 2) { // ? String className = studentCell.toString(); className = className.trim(); List<DictClass> classes = dictClassDao.getClassByName(className, dictSchool); if (classes != null) { if (classes.size() > 0) { dictClass = classes.get(0); dictStudent.setDictClass(dictClass); } } } if (i == 3) { // ? String memberString = studentCell.toString(); memberString = memberString.trim(); memberString = memberString.replaceAll("", ","); dictStudent.setStuRmark(memberString); } } // end___?? dictStudents.add(dictStudent); } } // end return dictStudents; } @Override public ExcelMessage validateDictStudents(List<DictStudent> dictStudents) { ExcelMessage ret = new ExcelMessage(); DictSchool dictSchool = adminService.getCurrentDictSchool(); if (dictStudents.size() == 0) { ret.setStatus(Status.fail); ret.setError(""); return ret; } // ??? ExcelMessage selfValidate = excelDao.validateStudentSelf(dictStudents); if (selfValidate.getStatus().toString().equals("fail")) { return selfValidate; } // start ? for (int i = 0; i < dictStudents.size(); i++) { int num = i + 1; DictStudent dictStudent = dictStudents.get(i); // ? if (dictStudent.getStudentName() != null) { if (dictStudentDao.studentNoExists(dictStudent.getStudentNo(), dictSchool.getDictClasses())) { ret.setStatus(Status.fail); ret.setError("" + num + "???"); return ret; } else { } } else { ret.setStatus(Status.fail); ret.setError("" + num + "????"); return ret; } // ? if (dictStudent.getDictClass() == null) { ret.setStatus(Status.fail); ret.setError("" + num + "????"); return ret; } // ?? if (dictStudent.getStuRmark() != null) { ret.setStatus(Status.fail); String memberString = dictStudent.getStuRmark(); memberString = memberString.replaceAll("", ","); String[] memberUseNames = memberString.split(","); for (String memberUseName : memberUseNames) { // memberUseName.trim(); Member member = memberService.findByUsername(memberUseName.trim()); if (member == null) { ret.setError( "" + num + "? " + memberUseName.trim() + " ?"); return ret; } } } } // end ? ret.setOk("?" + dictStudents.size() + "?"); ret.setStatus(Status.success); return ret; } }