基于easypoi 导入demo
导入依赖
<!-- Excel导入导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
实体类
ExcelUtil
import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.ImportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.net.URLEncoder; import java.util.List; import java.util.Map; import java.util.NoSuchElementException; public class ExcelUtils { /** * excel 导出 * * @param list 数据 * @param title 标题 * @param sheetName sheet名称 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param isCreateHeader 是否创建表头 * @param response */ public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException { ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.HSSF); exportParams.setCreateHeadRows(isCreateHeader); defaultExport(list, pojoClass, fileName, response, exportParams); } /** * excel 导出 * * @param list 数据 * @param title 标题 * @param sheetName sheet名称 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response */ public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException { defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.HSSF)); } /** * excel 导出 * * @param list 数据 * @param title 标题 * @param sheetName sheet名称 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response */ public static void exportExcelXSSF(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException { defaultExportXSSF(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF)); } /** * excel 导出 * * @param list 数据 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response * @param exportParams 导出参数 */ public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException { defaultExport(list, pojoClass, fileName, response, exportParams); } /** * excel 导出 * * @param list 数据 * @param fileName 文件名称 * @param response */ public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException { defaultExport(list, fileName, response); } /** * 默认的 excel 导出 * * @param list 数据 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response * @param exportParams 导出参数 */ private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException { Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list); downLoadExcel(fileName, response, workbook); } /** * 默认的 excel 导出 * * @param list 数据 * @param pojoClass pojo类型 * @param fileName 文件名称 * @param response * @param exportParams 导出参数 */ private static void defaultExportXSSF(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException { Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list); downLoadExcelXSSF(fileName, response, workbook); } /** * 默认的 excel 导出 * * @param list 数据 * @param fileName 文件名称 * @param response */ public static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException { Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF); downLoadExcel(fileName, response, workbook); } /** * 下载 * * @param fileName 文件名称 * @param response * @param workbook excel数据 */ private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException { try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLS.getValue(), "UTF-8")); workbook.write(response.getOutputStream()); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * 下载 * * @param fileName 文件名称 * @param response * @param workbook excel数据 */ private static void downLoadExcelXSSF(String fileName, HttpServletResponse response, Workbook workbook) throws IOException { try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8")); workbook.write(response.getOutputStream()); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * excel 导入 * * @param filePath excel文件路径 * @param titleRows 标题行 * @param headerRows 表头行 * @param pojoClass pojo类型 * @param <T> * @return */ public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException { if (StringUtils.isBlank(filePath)) { return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); params.setNeedSave(true); params.setSaveUrl("/excel/"); try { return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params); } catch (NoSuchElementException e) { throw new IOException("模板不能为空"); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * excel 导入 * * @param file excel文件 * @param pojoClass pojo类型 * @param <T> * @return */ public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException { return importExcel(file, 1, 1, pojoClass); } /** * excel 导入 * * @param file excel文件 * @param titleRows 标题行 * @param headerRows 表头行 * @param pojoClass pojo类型 * @param <T> * @return */ public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException { return importExcel(file, titleRows, headerRows, false, pojoClass); } /** * excel 导入 * * @param file 上传的文件 * @param titleRows 标题行 * @param headerRows 表头行 * @param needVerfiy 是否检验excel内容 * @param pojoClass pojo类型 * @param <T> * @return */ public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException { if (file == null) { return null; } try { return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * excel 导入 * * @param inputStream 文件输入流 * @param titleRows 标题行 * @param headerRows 表头行 * @param needVerfiy 是否检验excel内容 * @param pojoClass pojo类型 * @param <T> * @return */ public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException { if (inputStream == null) { return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); params.setSaveUrl("/excel/"); params.setNeedSave(true); params.setNeedVerify(needVerfiy); try { return ExcelImportUtil.importExcel(inputStream, pojoClass, params); } catch (NoSuchElementException e) { throw new IOException("excel文件不能为空"); } catch (Exception e) { throw new IOException(e.getMessage()); } } /** * Excel 类型枚举 */ enum ExcelTypeEnum { XLS("xls"), XLSX("xlsx"); private String value; ExcelTypeEnum(String value) { this.value = value; } public String getValue() { return value; } public void setValue(String value) { this.value = value; } } }
导入之前先下载模板
导入
public List<String> userImport(MultipartFile file) throws IOException, FinanceException { List<UserManagementVo> list = null; try { list = ExcelUtils.importExcel(file, UserManagementVo.class); } catch (Exception e) { throw new FinanceException(111,"导入数据失败"); } List<UserManagement> users = new ArrayList<>(); List<String> errorInfo = new ArrayList<>(); if (!ObjectUtils.isEmpty(list)) { for (int i = 0; i < list.size(); i++) { int num = i + 3; UserManagementVo us = list.get(i); if (us!=null && !ObjectUtils.isEmpty(us.getJobNumber()) && !ObjectUtils.isEmpty(us.getUserName() )&& !ObjectUtils.isEmpty(us.getSexs()) && !ObjectUtils.isEmpty(us.getDepartmentName()) && !ObjectUtils.isEmpty(us.getPostName())&& !ObjectUtils.isEmpty(us.getBankCard()) && !ObjectUtils.isEmpty(us.getBankOfDeposit()) && !ObjectUtils.isEmpty(us.getPositionName()) && !ObjectUtils.isEmpty(us.getEducation()) && !ObjectUtils.isEmpty(us.getAcademicDegree()) && !ObjectUtils.isEmpty(us.getPhone()) && !ObjectUtils.isEmpty(us.getStatuss()) && !ObjectUtils.isEmpty(us.getRoleIdName()) ){ String pattern = "[\u4e00-\u9fa5]+"; if (us.getUserName().length()>10){ // throw new FinanceException(111,"第" + num + "行 姓名超过10字符"); String s = "第" + num + "行 姓名超过10字符"; log.info(s); errorInfo.add(s); continue; } String pattern1 = "^[0-9a-zA-Z]+$"; boolean isMatch4 = Pattern.matches(pattern1, us.getJobNumber()); if (isMatch4==false){ String s = "第" + num + "行 工号只能输入数字,字母及组合"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 工号只能输入数字,字母及组合"); } if (us.getJobNumber().length()>20){ String s = "第" + num + "行 工号超过20字符"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 工号超过20字符"); } if (us.getBankCard().length()<15){ String s = "第" + num + "行 银行卡号在16位和20位之间"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 银行卡号在16位和20位之间"); } if (us.getBankCard().length()>20){ String s = "第" + num + "行 银行卡号在16位和20位之间"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 银行卡号在16位和20位之间"); } boolean isMatch2 = Pattern.matches(pattern1, us.getBankCard()); if (isMatch2==false){ String s = "第" + num + "行 银行卡号只能输入数字"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 银行卡号只能输入数字"); } boolean isMatch3 = Pattern.matches(pattern, us.getBankOfDeposit()); if (isMatch3==false){ String s = "第" + num + "行 开户行只能输入汉字"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 开户行只能输入汉字"); } if (us.getBankOfDeposit().length()>20){ String s = "第" + num + "行 开户行超过20字符"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 开户行超过20字符"); } if (us.getPositionName().length()>15){ String s = "第" + num + "行 职称超过20字符"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 职称超过20字符"); } if (us.getPhone().length()!=11 ){ String s = "第" + num + "行 电话输入不正确"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 电话输入不正确"); } //判断 部门，岗位，学历，学位，兼职。角色是否在系统中存在 //判断工号是否重复 List<UserManagement> userManagement=userManagementMapper.queryJobNumbers(us.getJobNumber()); if (ObjectUtils.isEmpty(userManagement)){ //判断部门 DepartmentManagement depts=departmentManagementMapper.departmentNameFlag(us.getDepartmentName()); if (!ObjectUtils.isEmpty(depts)){ //判断岗位 PostManagement postManagement=postManagementMapper.queryByPostName(us.getPostName()); if (!ObjectUtils.isEmpty(postManagement)) { //判断学历 if (us.getEducation().equals("小学") || us.getEducation().equals("初中") || us.getEducation().equals("高中") || us.getEducation().equals("大专") || us.getEducation().equals("本科") || us.getEducation().equals("研究生") ){ //判断学位 if(us.getAcademicDegree().equals("学士") || us.getAcademicDegree().equals("硕士") || us.getAcademicDegree().equals("博士")){ //判断兼职部门 if (!ObjectUtils.isEmpty(us.getPartTimeJob())){ String[] depet=us.getPartTimeJob().split(","); for (String a:depet) { DepartmentManagement deptName = departmentManagementMapper.departmentNameFlag(a); if (ObjectUtils.isEmpty(deptName)){ String s ="第" + num + "行 输入的兼职部门不存在，请核实后在输入"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入的兼职部门不存在，请核实后在输入"); } } } //兼职部门 String deptId = ""; List<String> list3 = Arrays.asList(us.getPartTimeJob().split(",")); List<String> newlist2 = new ArrayList<String>(list3); HashSet<String> hashSet2 = new HashSet<>(newlist2); if (newlist2.size() != hashSet2.size()) { String s ="第" + num + "行 输入的兼职部门存在重复数据"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入的兼职部门存在重复数据"); } List<String> list2 = Arrays.asList(us.getPartPostJob().split(",")); List<String> newlist1 = new ArrayList<String>(list2); HashSet<String> hashSet1 = new HashSet<>(newlist1); if (newlist1.size() != hashSet1.size()) { String s ="第" + num + "行 输入的兼职岗位存在重复数据"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入的兼职岗位存在重复数据"); } //判断角色 List<String> list1 = Arrays.asList(us.getRoleIdName().split(",")); List<String> newlist = new ArrayList<String>(list1); HashSet<String> hashSet = new HashSet<>(newlist); if (newlist.size() != hashSet.size()) { String s ="第" + num + "行 输入的角色存在重复数据"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入的角色存在重复数据"); } if(!ObjectUtils.isEmpty(us.getPartPostJob())){ String[] post=us.getPartPostJob().split(","); for (String b:post) { PostManagement post2=postManagementMapper.queryByPostName(b); if (ObjectUtils.isEmpty(post2)){ String s ="第" + num + "行 输入的兼职岗位不存在，请核实后在输入"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入的兼职岗位不存在，请核实后在输入"); } } } UserManagement u = new UserManagement(); String[] role = us.getRoleIdName().split(","); String roleId=""; for (String r : role) { RoleList roleList = roleListMapper.queryByRoleNames(r); if (!ObjectUtils.isEmpty(roleList)) { roleId=roleId+","+roleList.getId(); u.setRoleId(String.valueOf(roleId.subSequence(1,roleId.length()))); }else { // throw new FinanceException(111, "第" + num + "行 输入的角色在系统中不存在"); String s ="第" + num + "行 输入的角色在系统中不存在"; log.info(s); errorInfo.add(s); continue; } } //判断手机号 String phone = us.getPhone(); List<UserManagement> queryphone = userManagementMapper.queryphones(phone); if (!ObjectUtils.isEmpty(queryphone)){ String s ="第" + num + "行 输入的手机号在系统中存在"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入的手机号在系统中存在"); } //判断状态 if (us.getStatuss().equals("在职") || us.getStatuss().equals("离职")) { u.setId(String.valueOf(snowFlake.nextId())); u.setUserName(us.getUserName()); if (us.getSexs().equals("男")) { u.setSex(1); } else if (us.getSexs().equals("女")){ u.setSex(2); }else{ String s ="第" + num + "行 性别只能输入男或女"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 性别只能输入男或女"); } u.setJobNumber(us.getJobNumber()); u.setDepartmentId(depts.getId()); u.setPostId(Long.valueOf(postManagement.getId())); u.setBankCard(us.getBankCard()); u.setBankOfDeposit(us.getBankOfDeposit()); u.setPositionName(us.getPositionName()); //学历 String education = us.getEducation(); if (education.equals("小学")) { u.setEducation("1"); } else if (education.equals("初中")) { u.setEducation("2"); } else if (education.equals("高中")) { u.setEducation("3"); } else if (education.equals("大专")) { u.setEducation("4"); } else if (education.equals("本科")) { u.setEducation("5"); } else if (education.equals("研究生")) { u.setEducation("6"); } else if (education.equals("无")) { u.setEducation("0"); } //学位 String academicDegree = us.getAcademicDegree(); if (academicDegree.equals("学士")) { u.setAcademicDegree("1"); } else if (academicDegree.equals("硕士")) { u.setAcademicDegree("2"); } else if (academicDegree.equals("博士")) { u.setAcademicDegree("3"); } else if (academicDegree.equals("无")) { u.setAcademicDegree("0"); } u.setPhone(us.getPhone()); //\ 导入人员时兼职岗位/部门与主岗位/部门重复时进行判断 //兼职岗位 String postId = ""; if (!ObjectUtils.isEmpty( us.getPartPostJob())){ String[] partPostJob = us.getPartPostJob().split(","); for (String q : partPostJob) { //查询岗位id PostManagement postManagement1 = postManagementMapper.queryByPostName(q); if(postManagement1.getId().equals(postManagement.getId())){ String s ="第" + num + "行 输入的兼职岗位与主岗位冲突"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111, "第" + num + "行 输入的兼职岗位与主岗位冲突"); } postId = postId + "," + postManagement1.getId(); } u.setPartPostJob(String.valueOf(postId.subSequence(1, postId.length()))); } if (!ObjectUtils.isEmpty(us.getPartTimeJob())){ String[] de = us.getPartTimeJob().split(","); for (String w : de) { //\ 导入人员时兼职岗位/部门与主岗位/部门重复时进行判断 //查询部门id DepartmentManagement management = departmentManagementMapper.departmentNameFlag(w); if(management.getId().equals(depts.getId())){ String s ="第" + num + "行 输入的兼职部门与主部门冲突"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111, "第" + num + "行 输入的兼职部门与主部门冲突"); } deptId = deptId + "," + management.getId(); } u.setPartTimeJob(String.valueOf(deptId.subSequence(1, deptId.length()))); } if (us.getStatuss().equals("在职")) { u.setStatus(1); } else { u.setStatus(2); } u.setAccount(us.getPhone()); String pwd = new Md5Hash("666666", us.getPhone()).toBase64(); u.setPassword(pwd); u.setAdminFlag(1); u.setDeleteFlag(0); u.setEnable(2); u.setCreateTime(new Date()); users.add(u); // userManagementMapper.insert(u); // return "第" + num + "行 导入成功"; } else{ String s ="第" + num + "行 输入的在职状态有误"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入的在职状态有误"); } }else{ String s ="第" + num + "行 输入学位有误"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入学位有误"); } }else{ String s ="第" + num + "行 输入学历有误"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入学历有误"); } }else{ String s ="第" + num + "行 系统中不存在该岗位"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 系统中不存在该岗位"); } }else{ String s ="第" + num + "行 系统中不存在该部门"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 系统中不存在该部门"); } }else{ String s ="第" + num + "行 输入的工号在系统中存在"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 输入的工号在系统中存在"); } }else{ String s ="第" + num + "行 系统中不存在或存在空格"; log.info(s); errorInfo.add(s); continue; // throw new FinanceException(111,"第" + num + "行 系统中不存在或存在空格"); } } if (errorInfo.size() < 1) { // 插入数据 userManagementMapper.insertList(users); return errorInfo; } else { // 错误行返回去 String string = JSONObject.toJSONString(errorInfo); return errorInfo; } }else{ throw new FinanceException(111,"未获取到导入数据"); } }
<insert id="insertList" parameterType="java.util.List">
INSERT INTO user_management (
`id`,
`user_name`,
`sex`,
`Job_number`,department_id,post_id,bank_card,bank_of_deposit,
position_name,education,academic_degree,phone,part_post_job,part_time_job,status,
account,role_id,enable,create_user_id,delete_flag,admin_flag,password
)
values
<foreach collection="users" item="item" index="index" separator=",">
(
#{item.id},#{item.userName}, #{item.sex}, #{item.jobNumber}, #{item.departmentId},
#{item.postId}, #{item.bankCard}, #{item.bankOfDeposit}, #{item.positionName},#{item.education},
#{item.academicDegree}, #{item.phone}, #{item.partPostJob},#{item.partTimeJob}, #{item.status},
#{item.account}, #{item.roleId},#{item.enable}, #{item.createUserId}, #{item.deleteFlag}, #{item.adminFlag}, #{item.password}
)
</foreach>
</insert>
