springboot项目利用easypoi导入导出（包括一对多导出的动态列选择，以及有错误数据导入返回错误信息）
因为项目只涉及到一对多的导出，以及普通的导入，所以，本文只会涉及这方面的使用
导入的时候，有校验，如果有错误数据，就会返回错误数据的所在行，以及错误信息（如果需要返回错误信息的所在的那几行数据以及错误信息的excel文件的话，可以看看第三个参考文章，这个项目最开始是做的返回excel文件，最后又取消了）
参考了一下文章（排名不分先后）：
- easypoi的官方文档
- 使用easypoi根据表头信息动态导出
- 微服务中EasyPoi上传Excel文件带校验并返回错误信息
- easyPOI基本用法
- Springboot 导入导出Excel ，一对多关系，复合表格、合并单元格数据
- SpringBoot实现Excel导入导出，好用到爆，POI可以扔掉了！
一. 前置准备
1. 在pom.xml中导入依赖
<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.1.0</version></dependency><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-validator</artifactId><version>5.2.4.Final</version></dependency>
2. application.yml配置环境
# mybatis yml文件配置mybatis:mapper-locations: classpath:mapper/*.xmlconfiguration:# 映射数据库中的下划线命名到 java 中的驼峰命名map-underscore-to-camel-case:true
3. 工具包
MyExcelUtils.java
packagecom.xxx.template.utils.recruitmentManagement;importjava.lang.reflect.*;importjava.util.List;importjava.util.Map;importcom.byd.template.log.Log;importcn.afterturn.easypoi.excel.annotation.Excel;importcn.afterturn.easypoi.excel.annotation.ExcelCollection;importcn.afterturn.easypoi.excel.annotation.ExcelEntity;importlombok.extern.slf4j.Slf4j;@Slf4jpublicclassMyExcelUtils{/**
* 修改fields上@Excel注解的name属性，不需要下载的列，name修改增加_ignore.
* 保存原来的@Excel注解name属性值，本次生成后用来恢复
* @Params
* headers：用户勾选，由前端传来的列名，列名的key必须和 Model字段对应
* clazz：model实体类
* excelMap：用来记录原值的map，因为用到了递归，这里返回值作为参数传入
* @return Map<String, String> 原实体类字段名和 @Excel注解 name属性值的映射关系<字段名，@Excel注解 name 属性值>
*/publicstaticMap<String,String>dynamicChangeAndSaveSourceAnnotation(List<String> headers,Class clazz,Map<String,String> excelMap){Field[] fields = clazz.getDeclaredFields();for(Field field : fields){// @Excel注解if(field.isAnnotationPresent(Excel.class)){boolean flag =true;if(headers ==null|| headers.size()==0){
flag =true;}else{for(int i =0; i < headers.size(); i++){String header = headers.get(i);if(field.getName().equals(header)){
flag =false;break;}}}// 下载列不包括该字段，进行隐藏，并记录原始值if(flag){Excel annotation = field.getAnnotation(Excel.class);// 保存注解
excelMap.put(field.getName(), annotation.name());InvocationHandler handler =Proxy.getInvocationHandler(annotation);String value = annotation.name().toString();changeAnnotationValue(handler, field.getName()+"_ignore");}// @ExcelCollection注解}elseif(field.isAnnotationPresent(ExcelCollection.class)&& field.getType().isAssignableFrom(List.class)){Type type = field.getGenericType();if(type instanceofParameterizedType){ParameterizedType pt =(ParameterizedType) type;Class collectionClazz =(Class) pt.getActualTypeArguments()[0];// 解决@ExcelCollection如果没有需要下载列的异常，java.lang.IllegalArgumentException: The 'to' col (15) must not be less than the 'from' col (16)// 如果没有需要下载列，将@ExcelCollection忽略Field[] collectionFields = collectionClazz.getDeclaredFields();boolean flag =false;
out:for(Field temp : collectionFields){if(!temp.isAnnotationPresent(Excel.class)){continue;}for(int i =0; i < headers.size(); i++){String header = headers.get(i);if(temp.getName().equals(header)){
flag =true;break out;}}}if(flag){dynamicChangeAndSaveSourceAnnotation(headers, collectionClazz, excelMap);}else{ExcelCollection annotation = field.getAnnotation(ExcelCollection.class);
excelMap.put(field.getName(), annotation.name());InvocationHandler handler =Proxy.getInvocationHandler(annotation);changeAnnotationValue(handler, field.getName()+"_ignore");}}// @ExcelEntity注解}elseif(field.isAnnotationPresent(ExcelEntity.class)){Class entityClazz = field.getType();dynamicChangeAndSaveSourceAnnotation(headers, entityClazz, excelMap);}}return excelMap;}publicstaticMap<String,String>dynamicChangeAndSaveSourceAnnotation(List<String> headers,List<String> otherHeaders,Class clazz,Map<String,String> excelMap){Field[] fields = clazz.getDeclaredFields();for(Field field : fields){// @Excel注解if( field.isAnnotationPresent(Excel.class)){boolean flag =true;if(headers ==null|| headers.size()==0){
flag =true;}else{for(int i =0; i < headers.size(); i++){String header = headers.get(i);if(field.getName().equals(header)){
flag =false;break;}}}// 下载列不包括该字段，进行隐藏，并记录原始值if(flag){Excel annotation = field.getAnnotation(Excel.class);// 保存注解
excelMap.put(field.getName(), annotation.name());InvocationHandler handler =Proxy.getInvocationHandler(annotation);String value = annotation.name().toString();changeAnnotationValue(handler, field.getName()+"_ignore");}// @ExcelCollection注解}elseif(field.isAnnotationPresent(ExcelCollection.class)&& field.getType().isAssignableFrom(List.class)){Type type = field.getGenericType();if(type instanceofParameterizedType){ParameterizedType pt =(ParameterizedType) type;Class collectionClazz =(Class) pt.getActualTypeArguments()[0];// 解决@ExcelCollection如果没有需要下载列的异常，java.lang.IllegalArgumentException: The 'to' col (15) must not be less than the 'from' col (16)// 如果没有需要下载列，将 @ExcelCollection忽略Field[] collectionFields = collectionClazz.getDeclaredFields();boolean flag =false;if(null== otherHeaders || otherHeaders.size()==0){ExcelCollection annotation = field.getAnnotation(ExcelCollection.class);
excelMap.put(field.getName(), annotation.name());InvocationHandler handler =Proxy.getInvocationHandler(annotation);changeAnnotationValue(handler, field.getName()+"_ignore");return excelMap;}
out:for(Field temp : collectionFields){if(!temp.isAnnotationPresent(Excel.class)){continue;}for(int i =0; i < otherHeaders.size(); i++){String header = otherHeaders.get(i);if(temp.getName().equals(header)){
flag =true;break out;}}}if(flag){dynamicChangeAndSaveSourceAnnotation(otherHeaders,collectionClazz, excelMap);}else{ExcelCollection annotation = field.getAnnotation(ExcelCollection.class);
excelMap.put(field.getName(), annotation.name());InvocationHandler handler =Proxy.getInvocationHandler(annotation);changeAnnotationValue(handler, field.getName()+"_ignore");}}// @ExcelEntity注解}elseif(field.isAnnotationPresent(ExcelEntity.class)){Class entityClazz = field.getType();dynamicChangeAndSaveSourceAnnotation(otherHeaders, entityClazz, excelMap);}}return excelMap;}// 改变注解属性值，抽取的公共方法privatestaticvoidchangeAnnotationValue(InvocationHandler handler,String propertyValue){try{Field field = handler.getClass().getDeclaredField("memberValues");
field.setAccessible(true);Map<String,Object> memberValues =(Map<String,Object>) field.get(handler);
memberValues.put("name", propertyValue);}catch(Exception e){Log.error("替换注解属性值出错！", e);}}/**
* * 递归恢复 @Excel原始的 name属性
*
*/publicstaticvoiddynamicResetAnnotation(Class clazz,Map<String,String> excelMap){if(excelMap.isEmpty()){return;}Field[] fields = clazz.getDeclaredFields();try{for(Field field : fields){if(field.isAnnotationPresent(Excel.class)){if(excelMap.containsKey(field.getName())){Excel annotation = field.getAnnotation(Excel.class);InvocationHandler handler =Proxy.getInvocationHandler(annotation);String sourceName = excelMap.get(field.getName());changeAnnotationValue(handler, sourceName);}}elseif(field.isAnnotationPresent(ExcelCollection.class)&& field.getType().isAssignableFrom(List.class)){// ExcelCollection修改过，才进行复原if(excelMap.containsKey(field.getName())){ExcelCollection annotation = field.getAnnotation(ExcelCollection.class);InvocationHandler handler =Proxy.getInvocationHandler(annotation);String sourceName = excelMap.get(field.getName());changeAnnotationValue(handler, sourceName);// ExcelCollection未修改过，递归复原泛型字段}else{Type type = field.getGenericType();if(type instanceofParameterizedType){ParameterizedType pt =(ParameterizedType) type;Class collectionClazz =(Class) pt.getActualTypeArguments()[0];dynamicResetAnnotation(collectionClazz, excelMap);}}}elseif(field.isAnnotationPresent(ExcelEntity.class)){Class entityClazz = field.getType();dynamicResetAnnotation(entityClazz, excelMap);}}}catch(Exception e){Log.error("解析动态表头，恢复注解属性值出错！", e);}}}
EasyPoiExcelUtil.java
packagecom.xxx.template.utils.recruitmentManagement;importcn.afterturn.easypoi.excel.ExcelExportUtil;importcn.afterturn.easypoi.excel.ExcelImportUtil;importcn.afterturn.easypoi.excel.entity.ExportParams;importcn.afterturn.easypoi.excel.entity.ImportParams;importcn.afterturn.easypoi.excel.entity.enmus.ExcelType;importcn.afterturn.easypoi.excel.entity.result.ExcelImportResult;importorg.apache.commons.lang3.StringUtils;importorg.apache.poi.ss.usermodel.Workbook;importorg.springframework.web.multipart.MultipartFile;importjavax.servlet.http.HttpServletResponse;importjava.io.File;importjava.io.IOException;importjava.io.InputStream;importjava.net.URLEncoder;importjava.util.List;importjava.util.Map;importjava.util.NoSuchElementException;publicclassEasyPoiExcelUtil{/**
* excel 导出
*
* @param list 数据列表
* @param title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass Excel 实体类
* @param fileName 导出时的excel文件名称
* @param isCreateHeader 是否创建表头
* @param response
*/publicstaticvoidexportExcel(List<?> list,String title,String sheetName,Class<?> pojoClass,String fileName,boolean isCreateHeader,HttpServletResponse response){ExportParams exportParams =newExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);defaultExport(list, pojoClass, fileName, response, exportParams);}/**
* excel 导出
*
* @param list 数据列表
* @param title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass Excel 实体类
* @param fileName 导出时的excel文件名称
* @param response
*/publicstaticvoidexportExcel(List<?> list,String title,String sheetName,Class<?> pojoClass,String fileName,HttpServletResponse response){defaultExport(list, pojoClass, fileName, response,newExportParams(title, sheetName));}/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass Excel 实体类
* @param fileName 导出时的excel名称
* @param response
* @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
*/privatestaticvoiddefaultExport(List<?> list,Class<?> pojoClass,String fileName,HttpServletResponse response,ExportParams exportParams){Workbook workbook =ExcelExportUtil.exportExcel(exportParams, pojoClass, list);if(workbook !=null);downLoadExcel(fileName, response, workbook);}/**
* 默认的 excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/publicstaticvoidexportExcel(List<Map<String,Object>> list,String fileName,HttpServletResponse response){defaultExport(list, fileName, response);}/**
* 默认的 excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/privatestaticvoiddefaultExport(List<Map<String,Object>> list,String fileName,HttpServletResponse response){Workbook workbook =ExcelExportUtil.exportExcel(list,ExcelType.HSSF);if(workbook !=null);downLoadExcel(fileName, response, workbook);}/**
* excel下载
*
* @param fileName 下载时的文件名称
* @param response
* @param workbook excel数据
*/privatestaticvoiddownLoadExcel(String fileName,HttpServletResponse response,Workbook workbook){try{//content-Type 的作用是让服务器告诉浏览器它发送的数据属于什么文件类型。// application/vnd.ms-excel 将查询结果导出到Excel
response.setHeader("content-Type","application/vnd.ms-excel;");//Content-Disposition 当Content-Type 的类型为要下载的类型时 , 这个信息头会告诉浏览器这个文件的名字和类型。/**
* Content-Disposition 指示如何处理响应内容。
*
* 一般有两种方式：
* inline：直接在页面显示
* attchment：以附件形式下载
* replace("+", "%20") 空格转码为 %20（已解决）
* replace("%3A", ":") %3A转码为: 但是下载的文件还是下划线 （未解决）
*/
response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode(fileName,"UTF-8").replace("+","%20"));
response.setCharacterEncoding("UTF-8");
workbook.write(response.getOutputStream());}catch(IOException e){//throw new NormalException(e.getMessage());}}/**
* 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
*
* @param file 上传的文件
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
* @return
*/publicstatic<T>List<T>importExcel(MultipartFile file,Integer titleRows,Integer headerRows,Class<T> pojoClass){if(file ==null){returnnull;}ImportParams params =newImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);List<T> list =null;try{
list =ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);}catch(NoSuchElementException e){// throw new NormalException("excel文件不能为空");}catch(Exception e){//throw new NormalException(e.getMessage());System.out.println(e.getMessage());}return list;}/**
* 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
*
* @param filePath excel文件路径（但是本系统不会用到）
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel 实体类
* @return
*/publicstatic<T>List<T>importExcel(String filePath,Integer titleRows,Integer headerRows,Class<T> pojoClass){if(StringUtils.isBlank(filePath)){returnnull;}ImportParams params =newImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);List<T> list =null;try{
list =ExcelImportUtil.importExcel(newFile(filePath), pojoClass, params);}catch(NoSuchElementException e){//throw new NormalException("模板不能为空");}catch(Exception e){
e.printStackTrace();//throw new NormalException(e.getMessage());}return list;}/**
* excel 导入
*
* @param inputStream 文件输入流
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass Excel 实体类
* @return
*/publicstatic<T>List<T>importExcel(InputStream inputStream,Integer titleRows,Integer headerRows,Class<T> pojoClass)throwsIOException{if(inputStream ==null){returnnull;}ImportParams params =newImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setSaveUrl("/excel/");
params.setNeedSave(true);try{returnExcelImportUtil.importExcel(inputStream, pojoClass, params);}catch(NoSuchElementException e){thrownewIOException("excel文件不能为空");}catch(Exception e){thrownewIOException(e.getMessage());}}/**
*
* @param file 导入的文件
* @param pojoClass 实体对象
* @param <T>
* @return
* @throws Exception
*/publicstatic<T>ExcelImportResult<T>importExcelMore(MultipartFile file,Class<T> pojoClass)throwsException{if(file ==null){returnnull;}ImportParams params =newImportParams();
params.setTitleRows(1);//表格内数据标题行
params.setHeadRows(1);//表头行//开启验证，代表导入这里是需要验证的（根据字段上的注解）
params.setNeedSave(true);
params.setNeedVerify(true);returnExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);}/**
* excel 导入
*
* @param inputStream 文件输入流
* @param pojoClass pojo类型
* @param <T>
* @return
*/privatestatic<T>ExcelImportResult<T>importExcelMore(InputStream inputStream,Class<T> pojoClass)throwsIOException{if(inputStream ==null){returnnull;}ImportParams params =newImportParams();
params.setTitleRows(1);//表格内数据标题行
params.setHeadRows(1);//表头行
params.setNeedSave(true);
params.setNeedVerify(true);//是否开启校验try{returnExcelImportUtil.importExcelMore(inputStream, pojoClass, params);}catch(NoSuchElementException e){thrownewIOException("excel文件不能为空");}catch(Exception e){thrownewIOException(e.getMessage());}}}
二. 实体类
有些字段我删除了，因为不导出它
OriginalDemand.java
packagecom.xxx.template.domain.recruitmentManagement;importcn.afterturn.easypoi.excel.annotation.Excel;importcn.afterturn.easypoi.excel.annotation.ExcelCollection;importcn.afterturn.easypoi.excel.annotation.ExcelIgnore;importcn.afterturn.easypoi.excel.annotation.ExcelTarget;importjava.time.LocalDateTime;importjava.util.List;@ExcelTarget("OriginalDemand")publicclassOriginalDemand{/**
* 主键id
*/@ExcelIgnoreprivateint id;/**
* 部门名称
*/@Excel(name ="部门", needMerge =true, width =20,height =8)privateString orgName;/**
* 岗位名称
*/@Excel(name ="岗位", needMerge =true, width =20,height =8)privateString postName;/**
* 岗位级别
*/@Excel(name ="级别", needMerge =true, width =20,height =8)privateString level;/**
* 招聘人数
*/@Excel(name ="人数", needMerge =true, width =20,height =8)privateint recruitmentQuantity;/**
* 工作地点
*/@Excel(name ="工作地点", needMerge =true, width =20,height =8)privateString workPlace;/**
* 剩余需求人数
*/@Excel(name ="剩余需求人数", needMerge =true, width =20,height =8)privateint remainingDemandNum;/**
* 已提需求
*/@ExcelCollection(name ="已提需求",orderNum="6")privateList<ProposedDemand> proposedDemand;//set,get方法，toString，构造器我就不写了
proposedDemand.java
packagecom.xxx.template.domain.recruitmentManagement;importcn.afterturn.easypoi.excel.annotation.Excel;importcn.afterturn.easypoi.excel.annotation.ExcelIgnore;importjava.time.LocalDateTime;/**
* 原始需求
*/publicclassProposedDemand{/**
* 主键id
*/@ExcelIgnoreprivateint id;/**
* 原始需求id
*/@ExcelIgnoreprivateint originalId;/**
* 科室名称
*/@Excel(name ="科室", width =20,height =8)privateString orgName;/**
* 班组名称
*/@Excel(name ="班组", width =20,height =8)privateString teamName;/**
* 岗位名称
*/@Excel(name ="岗位", width =20,height =8)privateString postName;/**
* 招聘编号
*/@Excel(name ="招聘编号", width =20,height =8)privateString recruitmentNo;/**
* 招聘人数
*/@Excel(name ="人数", width =20,height =8)privateint recruitmentQuantity;/**
* 备注
*/@Excel(name ="备注", width =20,height =8)privateString remark;//set,get方法，toString，构造器我就不写了
TalentPool.java
packagecom.xxx.template.domain.recruitmentManagement;importcn.afterturn.easypoi.handler.inter.IExcelDataModel;importcn.afterturn.easypoi.handler.inter.IExcelModel;importcn.afterturn.easypoi.excel.annotation.Excel;importcn.afterturn.easypoi.excel.annotation.ExcelIgnore;importcn.afterturn.easypoi.excel.annotation.ExcelTarget;importjavax.validation.constraints.NotNull;importjavax.validation.constraints.Pattern;importjava.io.Serializable;importjava.time.LocalDateTime;@ExcelTarget("TalentPool")publicclassTalentPoolimplementsSerializable,IExcelModel,IExcelDataModel{/**
* 主键id
*/@ExcelIgnoreprivateint id;/**
* 人才编号
*/@Excel(name ="人才编号", width =20, orderNum ="2")privateString talentId;/**
* 姓名
*/@Excel(name ="姓名", width =10, orderNum ="2")@NotNull(message ="姓名不能为空")@Pattern(regexp ="^([\\u4e00-\\u9fa5]{1,}|[a-zA-Z\\.\\s]{1,})$", message ="姓名必须为中文或者英文")privateString name;/**
* 性别
*/@Excel(name ="性别", width =10, orderNum ="2")@NotNull(message ="性别不能为空")@Pattern(regexp ="^[男女]$", message ="性别必须是男或者女")privateString gender;/**
* 邮箱
*/@Excel(name ="邮箱", width =20, orderNum ="2")@Pattern(regexp ="^\\w+([-+.]\\w+)*@\\w+([-.]\\w+)*\\.\\w+([-.]\\w+)*$", message ="邮箱格式必须正确")privateString email;/**
* 电话
*/@Excel(name ="电话", width =20, orderNum ="2")@Pattern(regexp ="^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$", message ="电话必须正确")privateString phoneNumber;/**
* 届别
*/@Excel(name ="届别", width =10, orderNum ="3")@NotNull(message ="届别不能为空")@Pattern(regexp ="^\\d{4}$", message ="届别必须是四位数字")privateString graduationTime;/**
* 学历
*/@Excel(name ="学历", width =15, orderNum ="3")@NotNull(message ="学历不能为空")privateString education;/**
* 学校
*/@Excel(name ="学校", width =20, orderNum ="3")@NotNull(message ="学校不能为空")privateString school;/**
* 专业
*/@Excel(name ="专业", width =20, orderNum ="3")@NotNull(message ="专业不能为空")privateString major;/**
* 简历文件名
*/@ExcelIgnoreprivateString fileName;/**
* 应聘部门code
*/@ExcelIgnoreprivateString orgCode;/**
* 应聘部门名称
*/@Excel(name ="部门", width =20, orderNum ="4")@NotNull(message ="部门不能为空")privateString orgName;/**
* 岗位code
*/@ExcelIgnoreprivateString postCode;/**
* 岗位名称
*/@Excel(name ="岗位", width =20, orderNum ="5")@NotNull(message ="岗位不能为空")privateString postName;/**
* 工作地点
*/@Excel(name ="工作地点", width =20, orderNum ="6")@NotNull(message ="工作地点不能为空")privateString workPlace;/**
* 一面评价
*/@Excel(name ="一面评价", width =20, orderNum ="7")privateString firstEvaluation;/**
* 一面结果
*/@Excel(name ="一面结果", width =20, orderNum ="8")privateString interviewResult;/**
* 备注
*/@Excel(name ="备注", width =20, orderNum ="9")privateString remark;privateString errorMsg;privateint rowNum;}
三. mapper层就省略了
四. service层只是调了一下mapper层，也就不写了
五. controller层
1. 一对多动态列表导出
/**
* excel导出
*
* @return
*/@PostMapping("/export")publicvoidexport(@RequestBodyJSONObject info ,HttpServletResponse response ,HttpServletRequest request)throwsException{HttpSession session = request.getSession();Map<String,String> excelMap=newHashMap<>();Map<String,String> stringStringMap =null;//json转mapMap<String,List> header =JSONObject.toJavaObject(info.getJSONObject("header"),Map.class);//原始需求的列名List excelHeaders =null;if("".equals(header.get("excel"))||(header.get("excel")==null)){}else{
excelHeaders = header.get("excel");}//已提需求的列名List excelCollectionHeaders =null;if("".equals(header.get("excelCollection"))||(header.get("excelCollection")==null)){}else{
excelCollectionHeaders = header.get("excelCollection");}//修改注解 @Excel中的 name值，
stringStringMap=MyExcelUtils.dynamicChangeAndSaveSourceAnnotation(excelHeaders, excelCollectionHeaders,OriginalDemand.class, excelMap);Map<String,Integer> parameter =newHashMap<>(2);List<OriginalDemand> data = recruitmentDemandService.selectByCondition(parameter,session);String time =LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));//导出excelEasyPoiExcelUtil.exportExcel(data,"招聘需求","招聘需求",OriginalDemand.class,"招聘需求_"+time+".xls", response);//导出完成恢复注解的原始值MyExcelUtils.dynamicResetAnnotation(OriginalDemand.class,stringStringMap);}
//请求的数据：{"header":{"excel":["orgName","postName","level"],"excelCollection":["remainingDemandNum","remark"]},"userName":"admin"}
2. 普通导入
/**
* 导入人才库信息
*
* @param file
* @param response
* @return
*/@PostMapping("/import")publicResponseEntityleadIn(@RequestParam(value ="file")MultipartFile file,@RequestParam(value ="userName")String userName,HttpServletResponse response){String data =null;try{ExcelImportResult<TalentPool> result =EasyPoiExcelUtil.importExcelMore(file,TalentPool.class);// 这是校验成功的数据List<TalentPool> successList = result.getList();//getFailWorkbook()和getFailList()里面的就是所有校验失败的excel数据List<TalentPool> failList = result.getFailList();for(TalentPool talentPool : successList){
talentPool.setCreatedBy(userName);
talentPool.setUpdatedBy(userName);
talentPoolService.insert(talentPool);}//导入校验存在失败则返回失败行 & 错误信息if(failList !=null&& failList.size()>0){
data ="人才库导入的excel中，";for(TalentPool faileds : failList){
data= data +"第"+(faileds.getRowNum()-1)+"行，"+faileds.getErrorMsg()+"；";}returnResponseEntity.failed(data);}returnResponseEntity.success("导入数据成功",null);}catch(Exception e){
e.printStackTrace();returnResponseEntity.failed("信息导入失败");}}
在导入的时候，也加入了唯一性判断，通过，姓名+性别+学校+专业进行判断，如果是重复的话，进行更新操作，否则进行新增操作
先在数据库给 姓名+性别+学校+专业 加上索引，
on duplicate key update
进行操作
<insertid="insert"parameterType="com.xxx.template.domain.recruitmentManagement.TalentPool">
insert into talent_pool
(name, talent_id, gender, email, phone_number, graduation_time, education, school, major, file_name, org_code, org_name,post_code,post_name,work_place,first_evaluation, interview_result, remark, is_delete, created_by,created_time, updated_by, updated_time)
VALUES (#{name},#{talentId},#{gender},#{email},#{phoneNumber},#{graduationTime},#{education},#{school},#{major},#{fileName},#{orgCode},#{orgName},#{postCode},#{postName},#{workPlace},#{firstEvaluation},#{interviewResult},#{remark},#{isDelete},#{createdBy},#{createdTime},#{updatedBy},#{updatedTime})
on duplicate key update
email = values(email),
phone_number = values(phone_number),
graduation_time = values(graduation_time),
education = values(education),
file_name = values(file_name),
org_code = values(org_code),
org_name = values(org_name),
post_code = values(post_code),
post_name = values(post_name),
work_place = values(work_place),
first_evaluation = values(first_evaluation),
interview_result = values(interview_result),
remark = values(remark),
is_delete = values(is_delete),
updated_by = values(updated_by),
updated_time = values(updated_time)
</insert>
人才库导入.xls
本文转载自: https://blog.csdn.net/qq_45090419/article/details/127110669
版权归原作者 陌年古城QAQ 所有， 如有侵权，请联系我们删除。
版权归原作者 陌年古城QAQ 所有， 如有侵权，请联系我们删除。