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/*.xml
configuration:
# 映射数据库中的下划线命名到 java 中的驼峰命名
map-underscore-to-camel-case: true
3. 工具包
MyExcelUtils.java
package com.xxx.template.utils.recruitmentManagement;
import java.lang.reflect.*;
import java.util.List;
import java.util.Map;
import com.byd.template.log.Log;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class MyExcelUtils {
/**
* 修改fields上@Excel注解的name属性，不需要下载的列，name修改增加_ignore.
* 保存原来的@Excel注解name属性值，本次生成后用来恢复
* @Params
* headers：用户勾选，由前端传来的列名，列名的key必须和 Model字段对应
* clazz：model实体类
* excelMap：用来记录原值的map，因为用到了递归，这里返回值作为参数传入
* @return Map<String, String> 原实体类字段名和 @Excel注解 name属性值的映射关系<字段名，@Excel注解 name 属性值>
*/
public static Map<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注解
} else if (field.isAnnotationPresent(ExcelCollection.class) && field.getType().isAssignableFrom(List.class)) {
Type type = field.getGenericType();
if (type instanceof ParameterizedType) {
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注解
} else if (field.isAnnotationPresent(ExcelEntity.class)) {
Class entityClazz = field.getType();
dynamicChangeAndSaveSourceAnnotation(headers, entityClazz, excelMap);
}
}
return excelMap;
}
public static Map<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注解
} else if (field.isAnnotationPresent(ExcelCollection.class) && field.getType().isAssignableFrom(List.class)) {
Type type = field.getGenericType();
if (type instanceof ParameterizedType) {
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注解
} else if (field.isAnnotationPresent(ExcelEntity.class)) {
Class entityClazz = field.getType();
dynamicChangeAndSaveSourceAnnotation(otherHeaders, entityClazz, excelMap);
}
}
return excelMap;
}
// 改变注解属性值，抽取的公共方法
private static void changeAnnotationValue(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属性
*
*/
public static void dynamicResetAnnotation(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);
}
} else if (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 instanceof ParameterizedType) {
ParameterizedType pt = (ParameterizedType) type;
Class collectionClazz = (Class) pt.getActualTypeArguments()[0];
dynamicResetAnnotation(collectionClazz, excelMap);
}
}
} else if (field.isAnnotationPresent(ExcelEntity.class)) {
Class entityClazz = field.getType();
dynamicResetAnnotation(entityClazz, excelMap);
}
}
} catch (Exception e) {
Log.error("解析动态表头，恢复注解属性值出错！", e);
}
}
}
EasyPoiExcelUtil.java
package com.xxx.template.utils.recruitmentManagement;
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 cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
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 EasyPoiExcelUtil {
/**
* excel 导出
*
* @param list 数据列表
* @param title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass Excel 实体类
* @param fileName 导出时的excel文件名称
* @param isCreateHeader 是否创建表头
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
String fileName, boolean isCreateHeader, HttpServletResponse response) {
ExportParams exportParams = new ExportParams(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
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
String fileName, HttpServletResponse response) {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass Excel 实体类
* @param fileName 导出时的excel名称
* @param response
* @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
*/
private static void defaultExport(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
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
/**
* 默认的 excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/
private static void defaultExport(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数据
*/
private static void downLoadExcel(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
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
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
*/
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(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
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, 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);
try {
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
*
* @param file 导入的文件
* @param pojoClass 实体对象
* @param <T>
* @return
* @throws Exception
*/
public static <T> ExcelImportResult<T> importExcelMore(MultipartFile file, Class<T> pojoClass) throws Exception {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(1);//表格内数据标题行
params.setHeadRows(1);//表头行
//开启验证，代表导入这里是需要验证的（根据字段上的注解）
params.setNeedSave(true);
params.setNeedVerify(true);
return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param pojoClass pojo类型
* @param <T>
* @return
*/
private static <T> ExcelImportResult<T> importExcelMore(InputStream inputStream, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(1);//表格内数据标题行
params.setHeadRows(1);//表头行
params.setNeedSave(true);
params.setNeedVerify(true); //是否开启校验
try {
return ExcelImportUtil.importExcelMore(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
}
二. 实体类
有些字段我删除了，因为不导出它
OriginalDemand.java
package com.xxx.template.domain.recruitmentManagement;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import java.time.LocalDateTime;
import java.util.List;
@ExcelTarget("OriginalDemand")
public class OriginalDemand {
/**
* 主键id
*/
@ExcelIgnore
private int id;
/**
* 部门名称
*/
@Excel(name = "部门", needMerge = true, width = 20,height = 8)
private String orgName;
/**
* 岗位名称
*/
@Excel(name = "岗位", needMerge = true, width = 20,height = 8)
private String postName;
/**
* 岗位级别
*/
@Excel(name = "级别", needMerge = true, width = 20,height = 8)
private String level;
/**
* 招聘人数
*/
@Excel(name = "人数", needMerge = true, width = 20,height = 8)
private int recruitmentQuantity;
/**
* 工作地点
*/
@Excel(name = "工作地点", needMerge = true, width = 20,height = 8)
private String workPlace;
/**
* 剩余需求人数
*/
@Excel(name = "剩余需求人数", needMerge = true, width = 20,height = 8)
private int remainingDemandNum;
/**
* 已提需求
*/
@ExcelCollection(name = "已提需求",orderNum="6")
private List<ProposedDemand> proposedDemand;
//set,get方法，toString，构造器我就不写了
proposedDemand.java
package com.xxx.template.domain.recruitmentManagement;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import java.time.LocalDateTime;
/**
* 原始需求
*/
public class ProposedDemand {
/**
* 主键id
*/
@ExcelIgnore
private int id;
/**
* 原始需求id
*/
@ExcelIgnore
private int originalId;
/**
* 科室名称
*/
@Excel(name = "科室", width = 20,height = 8)
private String orgName;
/**
* 班组名称
*/
@Excel(name = "班组", width = 20,height = 8)
private String teamName;
/**
* 岗位名称
*/
@Excel(name = "岗位", width = 20,height = 8)
private String postName;
/**
* 招聘编号
*/
@Excel(name = "招聘编号", width = 20,height = 8)
private String recruitmentNo;
/**
* 招聘人数
*/
@Excel(name = "人数", width = 20,height = 8)
private int recruitmentQuantity;
/**
* 备注
*/
@Excel(name = "备注", width = 20,height = 8)
private String remark;
//set,get方法，toString，构造器我就不写了
TalentPool.java
package com.xxx.template.domain.recruitmentManagement;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Pattern;
import java.io.Serializable;
import java.time.LocalDateTime;
@ExcelTarget("TalentPool")
public class TalentPool implements Serializable, IExcelModel, IExcelDataModel {
/**
* 主键id
*/
@ExcelIgnore
private int id;
/**
* 人才编号
*/
@Excel(name = "人才编号", width = 20, orderNum = "2")
private String talentId;
/**
* 姓名
*/
@Excel(name = "姓名", width = 10, orderNum = "2")
@NotNull(message = "姓名不能为空")
@Pattern(regexp = "^([\\u4e00-\\u9fa5]{1,}|[a-zA-Z\\.\\s]{1,})$", message = "姓名必须为中文或者英文")
private String name;
/**
* 性别
*/
@Excel(name = "性别", width = 10, orderNum = "2")
@NotNull(message = "性别不能为空")
@Pattern(regexp = "^[男女]$", message = "性别必须是男或者女")
private String gender;
/**
* 邮箱
*/
@Excel(name = "邮箱", width = 20, orderNum = "2")
@Pattern(regexp = "^\\w+([-+.]\\w+)*@\\w+([-.]\\w+)*\\.\\w+([-.]\\w+)*$", message = "邮箱格式必须正确")
private String 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 = "电话必须正确")
private String phoneNumber;
/**
* 届别
*/
@Excel(name = "届别", width = 10, orderNum = "3")
@NotNull(message = "届别不能为空")
@Pattern(regexp = "^\\d{4}$", message = "届别必须是四位数字")
private String graduationTime;
/**
* 学历
*/
@Excel(name = "学历", width = 15, orderNum = "3")
@NotNull(message = "学历不能为空")
private String education;
/**
* 学校
*/
@Excel(name = "学校", width = 20, orderNum = "3")
@NotNull(message = "学校不能为空")
private String school;
/**
* 专业
*/
@Excel(name = "专业", width = 20, orderNum = "3")
@NotNull(message = "专业不能为空")
private String major;
/**
* 简历文件名
*/
@ExcelIgnore
private String fileName;
/**
* 应聘部门code
*/
@ExcelIgnore
private String orgCode;
/**
* 应聘部门名称
*/
@Excel(name = "部门", width = 20, orderNum = "4")
@NotNull(message = "部门不能为空")
private String orgName;
/**
* 岗位code
*/
@ExcelIgnore
private String postCode;
/**
* 岗位名称
*/
@Excel(name = "岗位", width = 20, orderNum = "5")
@NotNull(message = "岗位不能为空")
private String postName;
/**
* 工作地点
*/
@Excel(name = "工作地点", width = 20, orderNum = "6")
@NotNull(message = "工作地点不能为空")
private String workPlace;
/**
* 一面评价
*/
@Excel(name = "一面评价", width = 20, orderNum = "7")
private String firstEvaluation;
/**
* 一面结果
*/
@Excel(name = "一面结果", width = 20, orderNum = "8")
private String interviewResult;
/**
* 备注
*/
@Excel(name = "备注", width = 20, orderNum = "9")
private String remark;
private String errorMsg;
private int rowNum;
}
三. mapper层就省略了
四. service层只是调了一下mapper层，也就不写了
五. controller层
1. 一对多动态列表导出
/**
* excel导出
*
* @return
*/
@PostMapping("/export")
public void export(@RequestBody JSONObject info , HttpServletResponse response , HttpServletRequest request) throws Exception {
HttpSession session = request.getSession();
Map<String, String> excelMap=new HashMap<>();
Map<String, String> stringStringMap =null;
//json转map
Map<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 = new HashMap<>(2);
List<OriginalDemand> data = recruitmentDemandService.selectByCondition(parameter,session);
String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
//导出excel
EasyPoiExcelUtil.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")
public ResponseEntity leadIn(@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()+"；";
}
return ResponseEntity.failed(data);
}
return ResponseEntity.success("导入数据成功", null);
} catch (Exception e) {
e.printStackTrace();
return ResponseEntity.failed("信息导入失败");
}
}
在导入的时候，也加入了唯一性判断，通过，姓名+性别+学校+专业进行判断，如果是重复的话，进行更新操作，否则进行新增操作
先在数据库给 姓名+性别+学校+专业 加上索引， on duplicate key update 进行操作文章来源：https://www.toymoban.com/news/detail-419875.html
<insert id="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://www.toymoban.com/news/detail-419875.html
到了这里，关于springboot项目利用easypoi导入导出（包括一对多导出的动态列选择，以及普通导入）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
