easyExcel关于导入导出的用法及校验
一、导入
（一）controller层
@DataLog(operationName = "导入了因私出国", operationDesc = "", methodType = MethodTypeEnum.IMPORT_TYPE)
@ApiOperation(value = "因私出国-导入")
@ApiOperationSupport(order = 5)
@GetMapping(Urls.Abroad.importAbroad)
public JsonObject<Object> importAbroad(String fileName, HttpServletRequest request) {
List<String> list = csAbroadService.importAbroad(fileName, request);
return new JsonSuccessObject<>(list, "导入成功");
}
请求方式用post或get都行，这里使用get是为了做校验下载异常数据文件。fileName是前端通过公共组件上传后的文件名，后端可以获取上传路径，拼一下就能取得上传后文件的绝对路径。
也可以使用multipart/form-data的请求形式上传，这时后端需要改变下接收方式：
@DataLog(operationName = "导入了离任审计", operationDesc = "", methodType = MethodTypeEnum.IMPORT_TYPE)
@ApiOperation(value = "离任审计-导入")
@ApiOperationSupport(order = 12)
@PostMapping(Urls.OffAudit.importAudit)
public JsonObject<Object> importAudit(MultipartFile file, HttpServletRequest request) throws IOException {
List<String> list = csOffAuditService.importAudit(file, request);
return new JsonSuccessObject<>(list, "导入成功");
}
（二）service层
/**
* 因私出国-导入
*
* @param fileName
* @param request
* @return
*/
@Override
@Transactional(rollbackFor = Exception.class)
public List<String> importAbroad(String fileName, HttpServletRequest request) {
List<String> recordIds = new ArrayList<>();
String userId = userSessionService.getCurrentUserId(request);
String nodeId = userSessionService.getCurrentUserNodeId(request);
String upload = fileConfigProperties.getUpload();
String filePath = "";
filePath = upload + File.separator + fileName;
// 读Excel
EasyExcel.read(filePath, CsA12Do.class,
new AbroadListener(recordIds, userId, nodeId, upload, this))
.sheet().doRead();
// 控制同步功能
if (recordIds.size() == 0) {
return null;
}
// by hansc 12.10 调用人员库接口
LambdaQueryWrapper<CsA12> wrapper = Wrappers.lambdaQuery();
wrapper.in(CsA12::getRecordid, recordIds);
List<CsA12> list = list(wrapper);
for (CsA12 item : list) {
csA01Service.insertExportInPerson(item.geta00a(), item.geta1250(), item.geta1251(), request);
}
// 同步A00
Integer a00 = csRecordService.synA00("a12", "A1250", "RECORDID",
nodeId, recordIds);
// 同步现任职务
/*Integer job = csRecordService.synJob("a12", "A1251", "RECORDID",
nodeId, recordIds);*/
// 同步高管类别
csA12Mapper.synGrade(recordIds, nodeId);
// 删除文件
// boolean b = DownloadUtil.deleteFile(filePath);
return recordIds;
}
filePath即为上传的文件的绝对路径。EasyExcel的read方法可以读取Excel的sheet页的内容。三个参数，分别是文件绝对路径，一个Class类和一个自定义的监听器：
1、 Class类对应的是读取的Excel文件的模板类，它与Excel表格列对应：
index是从0开始，CsA12Do模板类的属性和Excel表格列一一对应。
2、 自定义的监听器类需要继承easyExcel的一个父类AnalysisEventListener，详情下面继续说
3、 sheet()方法为指定读取哪个sheet页，参数可以填sheet页的页名或者序号，不写的话默认就是Sheet1：
（三）监听器
1、 easyExcel的原理是按行读取，即每次读取一行时新建一个监听器类，在监听器类中对读取到的内容进行处理。因为是通过new关键字新建监听器类，因此不能使用sprinboot的注入功能注入我们的bean对象，所以可以考虑使用构造器，将我们需要的bean对象以及一些自定义变量传进监听器中。
package com.bdsoft.cs.controller.utils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.bdsoft.cs.entity.CodeValue;
import com.bdsoft.cs.entity.CsA12;
import com.bdsoft.cs.query.CsA12Do;
import com.bdsoft.cs.service.daily.CsAbroadService;
import com.bdsoft.cs.utils.ConvertUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.transaction.annotation.Transactional;
import java.io.File;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
/**
* @author hanshaocong
* @since 2021-10-22
*/
public class AbroadListener extends AnalysisEventListener<CsA12Do> {
private static final int batchSize = 1000;
private LocalDateTime time = LocalDateTime.now();
private List<CsA12Do> list = new ArrayList<>(batchSize);
// 高管类别GGLB_02
// private List<CodeValue> listOfA1252;
// 前往国家/地区ZB01C
private List<CodeValue> listOfA1204;
// 出国(境)事由CGGL_DMB_01
private List<CodeValue> listOfZdyxa1202;
// 存储主键，返回给前端
private List<String> recordIds;
private String userId;
private String nodeId;
private String upload;
private CsAbroadService csAbroadService;
private static Boolean formatFlag = true; // 格式校验开关：true开，false关
private static Boolean repeatFlag = true; // 重复校验开关
public AbroadListener(List<String> recordIds, String userId, String nodeId, String upload, CsAbroadService csAbroadService) {
this.recordIds = recordIds;
this.userId = userId;
this.nodeId = nodeId;
this.upload = upload;
this.csAbroadService = csAbroadService;
// this.listOfA1252 = csAbroadService.getEntity("GGLB_02");
this.listOfA1204 = csAbroadService.getEntity("ZB01C");
this.listOfZdyxa1202 = csAbroadService.getEntity("CGGL_DMB_01");
}
@Override
public void invoke(CsA12Do csA12Do, AnalysisContext analysisContext) {
list.add(csA12Do);
if (list.size() >= batchSize) {
this.saveData();
list.clear();
}
}
/**
* 数据导入逻辑：
* 1、校验；2、导入
*/
@Transactional(rollbackFor = Exception.class)
void saveData() {
if (verify()) {
insert();
}
}
/**
* 校验
*
* @return 导入功能开关
*/
Boolean verify() {
String separator = File.separator;
String fileName = "因私出国_导入数据异常.xlsx"; // 报错提示文件名
String filePath = upload + separator + fileName; // 生成的文件的绝对路径
List<CsA12Do> formatList = new ArrayList<>(); // 格式错误的数据
List<CsA12Do> repeatList = new ArrayList<>(); // 重复的数据
// ① 日期校验
if (formatFlag) {
for (CsA12Do item : list) {
CsA12Do csA12Do = new CsA12Do();
String a1201 = item.getA1201(); // 出国日期
String a1203 = item.getA1203(); // 回国日期
csA12Do.setA1201(ConvertUtil.isNotTime(a1201) ? a1201 : null);
csA12Do.setA1203(ConvertUtil.isNotTime(a1203) ? a1203 : null);
csA12Do.setA00a(item.getA00a());
if (csA12Do.getA1201() != null || csA12Do.getA1203() != null) {
formatList.add(csA12Do);
}
}
}
// ② 重复性校验
if (repeatFlag) {
for (CsA12Do item : list) {
CsA12Do query = new CsA12Do();
BeanUtils.copyProperties(item, query);
query.setA1204(ConvertUtil.getDmcod(item.getA1204(), listOfA1204));
query.setZdyxa1202(ConvertUtil.getDmcod(item.getZdyxa1202(), listOfZdyxa1202));
LambdaQueryWrapper<CsA12> wrapper = Wrappers.lambdaQuery();
wrapper.eq(CsA12::geta00a, query.getA00a())
.eq(CsA12::geta1250, query.getA1250())
.eq(CsA12::geta1251, query.getA1251())
.eq(CsA12::geta1204, query.getA1204())
.apply("date_format(a1201,'%Y-%m-%d') = {0}", query.getA1201())
.apply("date_format(a1203,'%Y-%m-%d') = {0}", query.getA1203())
.eq(CsA12::getZdyxa1202, query.getZdyxa1202())
.eq(CsA12::getDeleteflag, 0);
List<CsA12> list = csAbroadService.list(wrapper);
if (list.size() > 0) {
repeatList.add(item);
}
}
}
return csAbroadService.downloadExcel(filePath, CsA12Do.class, formatList, repeatList, null);
}
/**
* 导入数据
*/
void insert() {
List<CsA12> csA12s = new ArrayList<>();
for (CsA12Do item : list) {
CsA12 csA12 = new CsA12();
String uuid = UUID.randomUUID().toString();
recordIds.add(uuid);
BeanUtils.copyProperties(item, csA12);
csA12.setRecordid(uuid);
csA12.setCreatetime(time);
csA12.setCreateuser(userId);
csA12.setUpdatetime(time);
csA12.setUpdateuser(userId);
csA12.setDeleteflag(0);
// 加权限
csA12.setNodeid(nodeId);
// 码表转换：前往国家/地区
csA12.seta1204Text(item.getA1204());
csA12.seta1204(ConvertUtil.getDmcod(item.getA1204(), listOfA1204));
// 高管类别，先存汉字再转义
/*csA12.seta1252Text(item.getA1252());
csA12.seta1252(ConvertUtil.getDmcod(item.getA1252(), listOfA1252));*/
// 出国事由
csA12.setZdyxa1202Text(item.getZdyxa1202());
csA12.setZdyxa1202(ConvertUtil.getDmcod(item.getZdyxa1202(), listOfZdyxa1202));
// 日期转换：出国日期
csA12.seta1201(ConvertUtil.stringToLocalDateTime(item.getA1201()));
// 回国日期
csA12.seta1203(ConvertUtil.stringToLocalDateTime(item.getA1203()));
csA12s.add(csA12);
}
// 排序
int count = csAbroadService.count() + 1;
for (int i = 0; i < csA12s.size(); i++, count++) {
csA12s.get(i).setPorder(BigDecimal.valueOf(count));
}
boolean b = csAbroadService.saveBatch(csA12s);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
this.saveData();
}
}
2、 代码中在监听器类中定义了需要自定义全局变量，新建监听器时通过调用含参的构造器，给新建的监听器类的对象的全局变量赋值，就可以在监听器对象中调用这些属性和类了。
3、 看类的目录结构可以发现，Listener接口被ReadListener接口继承，ReadListener接口又被AnalysisEventListener类实现，AnalysisEventListener类又被我们的业务类AbroadListener继承，所以在我们的自定义监听器类AbroadListener中，需要实现ReadListener接口的几个方法，见上图。
4、 invoke方法首先执行，读一行Excel新建一次监听器类，然后执行一次invoke方法。
private List<CsA12Do> list = new ArrayList<>(batchSize);
@Override
public void invoke(CsA12Do csA12Do, AnalysisContext analysisContext) {
list.add(csA12Do);
if (list.size() >= batchSize) {
this.saveData();
list.clear();
}
}
5、 这个全局变量list里面存的是我们读取到的Excel行数据。batchSize是我们自定义的读取容量，根据实际业务设置大小，比如设置为100就是读取100行然后执行saveData方法。
/**
* 数据导入逻辑：
* 1、校验；2、导入
*/
@Transactional(rollbackFor = Exception.class)
void saveData() {
if (verify()) {
insert();
}
}
6、 saveData方法中先执行校验方法verify()，没有问题后再执行写入数据操作insert()。
/**
* 校验
*
* @return 导入功能开关
*/
Boolean verify() {
String separator = File.separator;
String fileName = "因私出国_导入数据异常.xlsx"; // 报错提示文件名
String filePath = upload + separator + fileName; // 生成的文件的绝对路径
List<CsA12Do> formatList = new ArrayList<>(); // 格式错误的数据
List<CsA12Do> repeatList = new ArrayList<>(); // 重复的数据
// ① 日期校验
if (formatFlag) {
for (CsA12Do item : list) {
CsA12Do csA12Do = new CsA12Do();
String a1201 = item.getA1201(); // 出国日期
String a1203 = item.getA1203(); // 回国日期
csA12Do.setA1201(ConvertUtil.isNotTime(a1201) ? a1201 : null);
csA12Do.setA1203(ConvertUtil.isNotTime(a1203) ? a1203 : null);
csA12Do.setA00a(item.getA00a());
if (csA12Do.getA1201() != null || csA12Do.getA1203() != null) {
formatList.add(csA12Do);
}
}
}
// ② 重复性校验
if (repeatFlag) {
for (CsA12Do item : list) {
CsA12Do query = new CsA12Do();
BeanUtils.copyProperties(item, query);
query.setA1204(ConvertUtil.getDmcod(item.getA1204(), listOfA1204));
query.setZdyxa1202(ConvertUtil.getDmcod(item.getZdyxa1202(), listOfZdyxa1202));
LambdaQueryWrapper<CsA12> wrapper = Wrappers.lambdaQuery();
wrapper.eq(CsA12::geta00a, query.getA00a())
.eq(CsA12::geta1250, query.getA1250())
.eq(CsA12::geta1251, query.getA1251())
.eq(CsA12::geta1204, query.getA1204())
.apply("date_format(a1201,'%Y-%m-%d') = {0}", query.getA1201())
.apply("date_format(a1203,'%Y-%m-%d') = {0}", query.getA1203())
.eq(CsA12::getZdyxa1202, query.getZdyxa1202())
.eq(CsA12::getDeleteflag, 0);
List<CsA12> list = csAbroadService.list(wrapper);
if (list.size() > 0) {
repeatList.add(item);
}
}
}
return csAbroadService.downloadExcel(filePath, CsA12Do.class, formatList, repeatList, null);
}
7、 校验方法中对日期格式和数据重复性两个内容进行了校验，当日期格式不符合标准的正则时或者Excel表格的数据与数据库现有数据有重复时，将这些数据记录在Excel中并通过浏览器下载。
二、导出
1、 下载走的是我自定义的一个公共方法downloadExcel()
/**
* 下载异常Excel，可指定排除列
*
* @param filePath 模板路径
* @param clz 模板输出实体类
* @param formatList 格式异常数据集合
* @param repeatList 重复数据集合
* @param excludeColumn 指定不导出的列名
* @return
*/
@Override
public Boolean downloadExcel(String filePath, Class<?> clz, List<?> formatList, List<?> repeatList, List<String> excludeColumn) {
// 若异常数据源都为空，则导入数据无异常，正常执行导入
if (ConvertUtil.listIsNull(formatList) && ConvertUtil.listIsNull(repeatList)) {
return true;
}
Boolean flag = true; // 导入功能开关，默认开
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(filePath, clz).excludeColumnFiledNames(excludeColumn).build();
if (ConvertUtil.listIsNotNull(formatList)) {
WriteSheet sheet = EasyExcel.writerSheet("格式异常页").build(); // 一个Excel文件两个Sheet页
excelWriter.write(formatList, sheet);
}
if (ConvertUtil.listIsNotNull(repeatList)) {
WriteSheet sheet = EasyExcel.writerSheet("数据重复页").build();
excelWriter.write(repeatList, sheet);
}
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
try {
if (ConvertUtil.listIsNotNull(formatList) || ConvertUtil.listIsNotNull(repeatList)) { // 当数据源不为空时才下载
DownloadUtil.download(filePath);
flag = false; // 若下载了Excel，则不执行导入功能，导入功能开关关闭
}
} catch (IOException e) {
e.printStackTrace();
} finally {
DownloadUtil.deleteFile(filePath);
}
return flag;
}
2、下载Excel用的也是easyExcel的write方法，即导出方法。
excelWriter = EasyExcel.write(filePath, clz).excludeColumnFiledNames(excludeColumn).build();
write方法有两个参数，一个是生成的文件的绝对路径（含文件后缀），另一个是生成的表格对应的模板类，这里用的还是CsA12Do类，和导入共用了一个模板类：
@ColumnWidth(20)
这个注解控制Excel表格的列宽，
@ExcelProperty(index = 0, value = "高管姓名")
这个主键控制了表头列的位置和列名。
excludeColumnFiledNames(excludeColumn)
该方法指定哪些表头列不输出，参数为列名的集合（即CsA12Do的属性名）
WriteSheet sheet = EasyExcel.writerSheet("数据重复页").build();
writerSheet方法设置sheet页的名字
excelWriter.write(repeatList, sheet);
write方法将数据源和表格模板绑定，即填充数据
DownloadUtil.download(filePath);
自定义了一个下载方法，代码附在后面
3、verify方法校验完，如果有问题，则将问题数据导出到Excel中，通过浏览器下载下来；
校验完没有问题，则执行insert方法，也就是写入操作，写入操作不多说。
4、doAfterAllAnalysed方法为监听器类生成后最后执行的一个方法。
P.S.
1、附上下载方法：
public static void download(String filePath) throws IOException {
HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
download(filePath, response, request);
}
public static void download(String filePath, HttpServletResponse response, HttpServletRequest request) throws IOException {
ServletOutputStream os = null;
try {
if (response == null) {
response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
}
ByteArrayOutputStream out = null;
FileInputStream inStream = null;
BufferedInputStream bis = null;
File file = new File(filePath);
String fileName = file.getName();
if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") <= 0 && request.getHeader("User-Agent").indexOf("like Gecko") <= 0) {
fileName = new String(fileName.replaceAll(" ", "").getBytes("UTF-8"), "ISO8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF-8");
}
if (file.exists()) {
response.reset();
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
os = response.getOutputStream();
out = new ByteArrayOutputStream();
inStream = new FileInputStream(filePath);
bis = new BufferedInputStream(inStream);
for(int c = bis.read(); c != -1; c = bis.read()) {
out.write(c);
}
bis.close();
inStream.close();
os.write(out.toByteArray());
}
} catch (IOException var13) {
logger.error("异常信息", var13);
} finally {
if (os != null) {
os.close();
}
}
}
这个方法不能自定义下载的文件名，可以改造下：
/**
* 下载文件
*
* @param filePath 文件绝对路径
* @param templateName 文件名
* @throws IOException
*/
public static void download(String filePath, String templateName) throws IOException {
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
download(filePath, templateName, response, request);
}
/**
* 下载文件
*
* @param filePath 文件绝对路径
* @param templateName 文件名
* @param response
* @param request
* @throws IOException
*/
public static void download(String filePath, String templateName, HttpServletResponse response, HttpServletRequest request) throws IOException {
ServletOutputStream os = null;
try {
if (response == null) {
response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
}
ByteArrayOutputStream out = null;
FileInputStream inStream = null;
BufferedInputStream bis = null;
File file = new File(filePath);
String fileName = file.getName();
if (StringUtility.isNotNull(templateName)) {
fileName = templateName;
}
if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") <= 0 && request.getHeader("User-Agent").indexOf("like Gecko") <= 0) {
fileName = new String(fileName.replaceAll(" ", "").getBytes("UTF-8"), "ISO8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF-8");
}
if (file.exists()) {
response.reset();
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
os = response.getOutputStream();
out = new ByteArrayOutputStream();
inStream = new FileInputStream(filePath);
bis = new BufferedInputStream(inStream);
for (int c = bis.read(); c != -1; c = bis.read()) {
out.write(c);
}
bis.close();
inStream.close();
os.write(out.toByteArray());
}
} catch (IOException var13) {
var13.printStackTrace();
} finally {
if (os != null) {
os.close();
}
}
}
2、附上日期正则校验：
// 年月日正则，标准格式：2021-12-23, 2021-1-1
public static String regex = "^((((19|20)\\d{2})-(0?[13-9]|1[012])-(0?[1-9]|[12]\\d|30))|(((19|20)\\d{2})-(0?[13578]|1[02])-31)|(((19|20)\\d{2})-0?2-(0?[1-9]|1\\d|2[0-8]))|((((19|20)([13579][26]|[2468][048]|0[48]))|(2000))-0?2-29))$";
/**
* 日期格式校验：不是标准时间格式
*
* @param time
* @return
*/
public static boolean isNotTime(String time) {
return !isTime(time);
}
/**
* 日期格式正则校验：是标准时间格式
*
* @param time 日期格式：2021-12-21, 2021-1-1
* @return
*/
public static boolean isTime(String time) {
return time != null && Pattern.matches(regex, time);
}
以上