springboot項目利用easypoi導(dǎo)入導(dǎo)出（包括一對多導(dǎo)出的動態(tài)列選擇，以及有錯誤數(shù)據(jù)導(dǎo)入返回錯誤信息）
因為項目只涉及到一對多的導(dǎo)出，以及普通的導(dǎo)入，所以，本文只會涉及這方面的使用
導(dǎo)入的時候，有校驗，如果有錯誤數(shù)據(jù)，就會返回錯誤數(shù)據(jù)的所在行，以及錯誤信息（如果需要返回錯誤信息的所在的那幾行數(shù)據(jù)以及錯誤信息的excel文件的話，可以看看第三個參考文章，這個項目最開始是做的返回excel文件，最后又取消了）
參考了一下文章（排名不分先后）：
- easypoi的官方文檔
- 使用easypoi根據(jù)表頭信息動態(tài)導(dǎo)出
- 微服務(wù)中EasyPoi上傳Excel文件帶校驗并返回錯誤信息
- easyPOI基本用法
- Springboot 導(dǎo)入導(dǎo)出Excel ，一對多關(guān)系，復(fù)合表格、合并單元格數(shù)據(jù)
- SpringBoot實現(xiàn)Excel導(dǎo)入導(dǎo)出，好用到爆，POI可以扔掉了！
一. 前置準(zhǔn)備
1. 在pom.xml中導(dǎo)入依賴
<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配置環(huán)境
# mybatis yml文件配置
mybatis:
mapper-locations: classpath:mapper/*.xml
configuration:
# 映射數(shù)據(jù)庫中的下劃線命名到 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屬性值，本次生成后用來恢復(fù)
* @Params
* headers：用戶勾選，由前端傳來的列名，列名的key必須和 Model字段對應(yīng)
* clazz：model實體類
* excelMap：用來記錄原值的map，因為用到了遞歸，這里返回值作為參數(shù)傳入
* @return Map<String, String> 原實體類字段名和 @Excel注解 name屬性值的映射關(guān)系<字段名，@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;
}
}
}
// 下載列不包括該字段，進(jìn)行隱藏，并記錄原始值
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;
}
}
}
// 下載列不包括該字段，進(jìn)行隱藏，并記錄原始值
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);
}
}
/**
* * 遞歸恢復(fù) @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修改過，才進(jìn)行復(fù)原
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未修改過，遞歸復(fù)原泛型字段
} 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("解析動態(tài)表頭，恢復(fù)注解屬性值出錯！", 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 導(dǎo)出
*
* @param list 數(shù)據(jù)列表
* @param title 表格內(nèi)數(shù)據(jù)標(biāo)題
* @param sheetName sheet名稱
* @param pojoClass Excel 實體類
* @param fileName 導(dǎo)出時的excel文件名稱
* @param isCreateHeader 是否創(chuàng)建表頭
* @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 導(dǎo)出
*
* @param list 數(shù)據(jù)列表
* @param title 表格內(nèi)數(shù)據(jù)標(biāo)題
* @param sheetName sheet名稱
* @param pojoClass Excel 實體類
* @param fileName 導(dǎo)出時的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 導(dǎo)出
*
* @param list 數(shù)據(jù)列表
* @param pojoClass Excel 實體類
* @param fileName 導(dǎo)出時的excel名稱
* @param response
* @param exportParams 導(dǎo)出參數(shù)（標(biāo)題、sheet名稱、是否創(chuàng)建表頭，表格類型）
*/
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);
}
/**
* 默認(rèn)的 excel 導(dǎo)出
*
* @param list 數(shù)據(jù)列表
* @param fileName 導(dǎo)出時的excel名稱
* @param response
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
/**
* 默認(rèn)的 excel 導(dǎo)出
*
* @param list 數(shù)據(jù)列表
* @param fileName 導(dǎo)出時的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數(shù)據(jù)
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
//content-Type 的作用是讓服務(wù)器告訴瀏覽器它發(fā)送的數(shù)據(jù)屬于什么文件類型。
// application/vnd.ms-excel 將查詢結(jié)果導(dǎo)出到Excel
response.setHeader("content-Type", "application/vnd.ms-excel;");
//Content-Disposition 當(dāng)Content-Type 的類型為要下載的類型時 , 這個信息頭會告訴瀏覽器這個文件的名字和類型。
/**
* Content-Disposition 指示如何處理響應(yīng)內(nèi)容。
*
* 一般有兩種方式：
* inline：直接在頁面顯示
* attchment：以附件形式下載
* replace("+", "%20") 空格轉(zhuǎn)碼為 %20（已解決）
* replace("%3A", ":") %3A轉(zhuǎn)碼為: 但是下載的文件還是下劃線 （未解決）
*/
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());
}
}
/**
* 功能描述：根據(jù)接收的Excel文件來導(dǎo)入Excel,并封裝成實體類
*
* @param file 上傳的文件
* @param titleRows 表標(biāo)題的行數(shù)
* @param headerRows 表頭行數(shù)
* @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;
}
/**
* 功能描述：根據(jù)接收的Excel文件來導(dǎo)入Excel,并封裝成實體類
*
* @param filePath excel文件路徑（但是本系統(tǒng)不會用到）
* @param titleRows 表標(biāo)題的行數(shù)
* @param headerRows 表頭行數(shù)
* @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 導(dǎo)入
*
* @param inputStream 文件輸入流
* @param titleRows 表格內(nèi)數(shù)據(jù)標(biāo)題行
* @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 導(dǎo)入的文件
* @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);//表格內(nèi)數(shù)據(jù)標(biāo)題行
params.setHeadRows(1);//表頭行
//開啟驗證，代表導(dǎo)入這里是需要驗證的（根據(jù)字段上的注解）
params.setNeedSave(true);
params.setNeedVerify(true);
return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
}
/**
* excel 導(dǎo)入
*
* @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);//表格內(nèi)數(shù)據(jù)標(biāo)題行
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());
}
}
}
二. 實體類
有些字段我刪除了，因為不導(dǎo)出它
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;
/**
* 招聘人數(shù)
*/
@Excel(name = "人數(shù)", needMerge = true, width = 20,height = 8)
private int recruitmentQuantity;
/**
* 工作地點
*/
@Excel(name = "工作地點", needMerge = true, width = 20,height = 8)
private String workPlace;
/**
* 剩余需求人數(shù)
*/
@Excel(name = "剩余需求人數(shù)", needMerge = true, width = 20,height = 8)
private int remainingDemandNum;
/**
* 已提需求
*/
@ExcelCollection(name = "已提需求",orderNum="6")
private List<ProposedDemand> proposedDemand;
//set,get方法，toString，構(gòu)造器我就不寫了
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;
/**
* 招聘人數(shù)
*/
@Excel(name = "人數(shù)", width = 20,height = 8)
private int recruitmentQuantity;
/**
* 備注
*/
@Excel(name = "備注", width = 20,height = 8)
private String remark;
//set,get方法，toString，構(gòu)造器我就不寫了
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 = "屆別必須是四位數(shù)字")
private String graduationTime;
/**
* 學(xué)歷
*/
@Excel(name = "學(xué)歷", width = 15, orderNum = "3")
@NotNull(message = "學(xué)歷不能為空")
private String education;
/**
* 學(xué)校
*/
@Excel(name = "學(xué)校", width = 20, orderNum = "3")
@NotNull(message = "學(xué)校不能為空")
private String school;
/**
* 專業(yè)
*/
@Excel(name = "專業(yè)", width = 20, orderNum = "3")
@NotNull(message = "專業(yè)不能為空")
private String major;
/**
* 簡歷文件名
*/
@ExcelIgnore
private String fileName;
/**
* 應(yīng)聘部門code
*/
@ExcelIgnore
private String orgCode;
/**
* 應(yīng)聘部門名稱
*/
@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;
/**
* 一面結(jié)果
*/
@Excel(name = "一面結(jié)果", width = 20, orderNum = "8")
private String interviewResult;
/**
* 備注
*/
@Excel(name = "備注", width = 20, orderNum = "9")
private String remark;
private String errorMsg;
private int rowNum;
}
三. mapper層就省略了
四. service層只是調(diào)了一下mapper層，也就不寫了
五. controller層
1. 一對多動態(tài)列表導(dǎo)出
/**
* excel導(dǎo)出
*
* @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轉(zhuǎn)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"));
//導(dǎo)出excel
EasyPoiExcelUtil.exportExcel(data, "招聘需求", "招聘需求", OriginalDemand.class, "招聘需求_"+time+".xls", response);
//導(dǎo)出完成恢復(fù)注解的原始值
MyExcelUtils.dynamicResetAnnotation(OriginalDemand.class,stringStringMap);
}
//請求的數(shù)據(jù)：
{
"header": {
"excel": [
"orgName",
"postName",
"level"
],
"excelCollection": [
"remainingDemandNum",
"remark"
]
},
"userName": "admin"
}
2. 普通導(dǎo)入
/**
* 導(dǎo)入人才庫信息
*
* @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);
// 這是校驗成功的數(shù)據(jù)
List<TalentPool> successList = result.getList();
//getFailWorkbook()和getFailList()里面的就是所有校驗失敗的excel數(shù)據(jù)
List<TalentPool> failList = result.getFailList();
for (TalentPool talentPool : successList) {
talentPool.setCreatedBy(userName);
talentPool.setUpdatedBy(userName);
talentPoolService.insert(talentPool);
}
//導(dǎo)入校驗存在失敗則返回失敗行 & 錯誤信息
if (failList != null && failList.size() > 0) {
data = "人才庫導(dǎo)入的excel中，";
for (TalentPool faileds : failList) {
data= data + "第"+(faileds.getRowNum()-1)+"行，"+faileds.getErrorMsg()+"；";
}
return ResponseEntity.failed(data);
}
return ResponseEntity.success("導(dǎo)入數(shù)據(jù)成功", null);
} catch (Exception e) {
e.printStackTrace();
return ResponseEntity.failed("信息導(dǎo)入失敗");
}
}
在導(dǎo)入的時候，也加入了唯一性判斷，通過，姓名+性別+學(xué)校+專業(yè)進(jìn)行判斷，如果是重復(fù)的話，進(jìn)行更新操作，否則進(jìn)行新增操作
先在數(shù)據(jù)庫給 姓名+性別+學(xué)校+專業(yè) 加上索引， on duplicate key update 進(jìn)行操作文章來源：http://www.zghlxwxcb.cn/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>
人才庫導(dǎo)入.xls
文章來源地址http://www.zghlxwxcb.cn/news/detail-419875.html
到了這里，關(guān)于springboot項目利用easypoi導(dǎo)入導(dǎo)出（包括一對多導(dǎo)出的動態(tài)列選擇，以及普通導(dǎo)入）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
