SpringBoot 使用easypoi.excel实现导入解析数据,并结合数据字典实现对数据的校验
在日常开发工作中避免不了的功能需求:导入Excel文件,然而导入文件流操作、对数据的校验有是件麻烦事,自从接触了easypoi后,觉得封装的很好,很简洁。
使用的主要依赖如下:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.5.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.5.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.5.0</version>
</dependency>
controller不贴了,这里贴一下serviceImpl
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
@Autowired
private DictHandler dictHandler;
@Autowired
private Bd3OrderHandlerNew bd3OrderHandlerNew;
// Bd3OrderDetailExcelNew 是导入的承载实体类
private List<Bd3OrderDetailExcelNew> checkNewExcel(MultipartFile excel, IExcelVerifyHandler verifyHandler) {
ImportParams importParams = new ImportParams();
importParams.setNeedVerify(true);
// 这里是对表格字段一些不为空、正则的判断
importParams.setVerifyHandler(verifyHandler);
// 这里是数据字典的校验
importParams.setDictHandler(dictHandler);
// 这里是表格的表头 根据自己需求进行更改
String[] importFields = {"序号", "用户终端序列号", "终端用户类型", "使用区域", "使用人姓名", "使用人证件号", "使用人联系电话", "服务频度", "通讯等级",
"自建编组数量", "区域与全球是否互通", "系统回执", "频度接收系统调控", "北二与北三是否互通", "是否应急搜救用户", "搜救中心ID", "检测报告编号", "北斗卡号"};
importParams.setImportFields(importFields);
ExcelImportResult<Bd3OrderDetailExcelNew> excelResult;
InputStream in = null;
try {
in = excel.getInputStream();
excelResult = ExcelImportUtil.importExcelMore(in, Bd3OrderDetailExcelNew.class, importParams);
} catch (Exception e) {
e.printStackTrace();
throw new RdssException(MessageExceptionEnum.CUSTOM_CODE.getCode(), "表格内容错误,请重新上传正确格式的入网注册详表!");
} finally {
ThreadLocal<List<Bd3OrderDetailExcelNew>> threadLocal = bd3OrderHandlerNew.getThreadLocal();
if (threadLocal != null) {
threadLocal.remove();
}
IOUtils.closeQuietly(in);
}
List<Bd3OrderDetailExcelNew> failList = excelResult.getFailList();
if (ObjectUtil.isNotEmpty(failList)) {
throw new RdssException(MessageExceptionEnum.EXCEL_CODE.getCode(),
MessageExceptionEnum.EXCEL_CODE.getMessage(), failList);
}
List<Bd3OrderDetailExcelNew> resultList = excelResult.getList().stream()
.filter(bd3OrderDetailExcelNew -> bd3OrderDetailExcelNew.getApplyIndex() != null).collect(Collectors.toList());
if (resultList.isEmpty()) {
throw new RdssException(ResponseCodeEnum.FAIL.getCode(), "导入Excel内容为空");
}
return resultList;
}
对表格数据的校验
其中的IExcelVerifyHandler是对表格字段的校验,需要自己去实现,例如我的实现类为Bd3OrderHandler
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.rdss.bus.model.entity.ApplyOrderBd3DetailInfo;
import com.rdss.bus.model.entity.Terminal;
import com.rdss.bus.model.excel.Bd3OrderDetailExcelNew;
import com.rdss.bus.service.*;
import com.rdss.common.util.StringUtils1;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.lang.reflect.Field;
import java.util.LinkedList;
import java.util.List;
import java.util.StringJoiner;
@Slf4j
@Service
public class Bd3OrderHandlerNew implements IExcelVerifyHandler<Bd3OrderDetailExcelNew> {
private ThreadLocal<List<Bd3OrderDetailExcel>> threadLocal = ThreadLocal.withInitial(LinkedList::new);
@Autowired
private TermianlBdcardService termianlBdcardService;
@Autowired
private BdcardService bdcardService;
@Autowired
private SysUserService sysUserService;
@Autowired
private BdcardService tBusBdcardService;
@Autowired
private TermianlService termianlService;
@Autowired
private ApplyOrderBd3DetailInfoService aoBd3DetailInfoService;
@Override
public ExcelVerifyHandlerResult verifyHandler(Bd3OrderDetailExcelNewdetailExcel) {
StringJoiner joiner = new StringJoiner(",");
if(isAllFieldNull(detailExcel)){
return new ExcelVerifyHandlerResult(true);
}
List<Bd3OrderDetailExcelNew> detailExcelList = threadLocal.get();
if (detailExcelList == null) {
detailExcelList = new LinkedList<>();
}
if(detailExcel.getApplyIndex() == null){
joiner.add("[序号]不能为空");
}
if (StringUtils1.isEmpty(detailExcel.getTerminalSerialNumber())) {
joiner.add("[用户终端序列号]不能为空");
}
if(!detailExcel.getTerminalSerialNumber().matches("^[0-9]{14}([0-9]{2})?$")){
joiner.add("[终端序列号]格式错误");
}
if(detailExcel.getCardType() == null){
joiner.add("[北斗卡类型]不能为空");
}
if(StringUtils1.isEmpty(detailExcel.getUsageArea())){
joiner.add("[使用区域]不能为空");
}
if(StringUtils1.isEmpty(detailExcel.getUserName())){
joiner.add("[使用人姓名]不能为空");
}
// 添加本行数据对象到ThreadLocal中
detailExcelList.add(detailExcel);
threadLocal.set(detailExcelList);
if (joiner.length() != 0) {
return new ExcelVerifyHandlerResult(false, joiner.toString());
}
//查询数据库是否存在机构编号
if( detailExcel.getApplyIndex() != detailExcelList.size()){
joiner.add("[序号]错误");
}
String terminalSerialNumber = detailExcel.getTerminalSerialNumber();
if(termianlService.getByNum(terminalSerialNumber)!=null) {
Terminal terminal = termianlService.getByNum(terminalSerialNumber);
if (termianlBdcardService.terminalnumber(terminal.getId()) > 0) {
joiner.add("[终端号]已和北斗卡绑定");
}
}
List<ApplyOrderBd3DetailInfo> terminal00= aoBd3DetailInfoService.getListByTerminal(terminalSerialNumber);
if(terminal00.size()!=0){
joiner.add("[终端序列号]重复提交申请,请确认");
}
if (joiner.length() != 0) {
return new ExcelVerifyHandlerResult(false, joiner.toString());
}
return new ExcelVerifyHandlerResult(true);
}
/**
* 是否是空表?
*
*/
public boolean isAllFieldNull(Bd3OrderDetailExcel detailExcel) {
try {
for (Field f : detailExcel.getClass().getDeclaredFields()) {
f.setAccessible(true);
if(f.getName().equals("rowNum") || f.getName().equals("errorMsg")){
continue;
}
if (f.get(detailExcel) != null && org.apache.commons.lang3.StringUtils.isNotBlank(f.get(detailExcel).toString())) {
return false;
}
}
} catch (Exception e) {
log.error("程序异常,错误信息为{}", e);
}
return true;
}
public ThreadLocal<List<Bd3OrderDetailExcelNew>> getThreadLocal() {
return threadLocal;
}
public void removeThreadLocal () { threadLocal.remove(); }
数据字典的处理
针对导入表格单元格存在下拉框选项,获取对应的数值时,需要数据字典解决:
import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.rdss.bus.model.entity.DictionaryData;
import com.rdss.bus.service.DictionaryDataService;
import com.rdss.common.entity.Dictionary;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.HashOperations;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
@Slf4j
@Service
public class DictHandler implements IExcelDictHandler, InitializingBean {
@Autowired
private DictionaryDataService dictionaryDataService;
private HashOperations<String, Object, Object> dictHash;
@Autowired
public void setDictHash(RedisTemplate<String, Object> redisTemplate) {
this.dictHash = redisTemplate.opsForHash();
}
@Override
public String toName(String dict, Object obj, String name, Object value) {
if (value==null) {
return null;
}
Map<Object, Object> map = dictHash.entries(dict);
Set<Map.Entry<Object, Object>> entrySet = map.entrySet();
String key = "";
for(Map.Entry<Object, Object> entry : entrySet){
if(entry.getValue().equals(value)){
key = String.valueOf(entry.getKey());
}
}
return key;
}
@Override
public String toValue(String dict, Object obj, String name, Object value) {
if (value==null) {
return null;
}
Object dictType = dictHash.get(dict, value);
return String.valueOf(dictType);
}
@Override
public void afterPropertiesSet() {
List<DictionaryData> dictionaryDataList = dictionaryDataService.list(new QueryWrapper<DictionaryData>().lambda().eq(DictionaryData::getStatus, 1));
List<Dictionary> dictList = dictionaryDataService.getDictionaryList();
dictList.stream().forEach(str->{
Map<String,Object> tagMap=new HashMap<>(8);
dictionaryDataList.stream().filter(dict -> {
Integer id = str.getId();
Integer dictId = str.getId();
return (dictId!=null && id.equals(dictId));
}).forEach(dict->
tagMap.put(dict.getTag(),dict.getKey()+"")
);
dictHash.putAll(str.getType(),tagMap);
});
}
}
其中数据字典 是分类操作,比如字典标题是汽车颜色,对应子字典子集有红色、白色、黑色等
父字典:
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.rdss.common.vo.DictionaryData2;
import lombok.Data;
import lombok.experimental.Accessors;
import org.springframework.data.annotation.Transient;
import java.io.Serializable;
import java.sql.Timestamp;
import java.util.List;
@Data
@Accessors(chain = true)
@TableName("t_sys_dictionary")
public class Dictionary implements Serializable {
private static final long serialVersionUID = 1L;
/**
* ID
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 字典名称
*/
@TableField(insertStrategy = FieldStrategy.IGNORED,updateStrategy = FieldStrategy.IGNORED,whereStrategy = FieldStrategy.IGNORED)
private String name;
/**
* 字典值
*/
@TableField(insertStrategy = FieldStrategy.IGNORED,updateStrategy = FieldStrategy.IGNORED,whereStrategy = FieldStrategy.IGNORED)
private String type;
/**
* 更新时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
private Timestamp updateTime;
/**
*状态:0 正常 ,1 停用
*/
private Integer status;
/**
* 备注
*/
@TableField(insertStrategy = FieldStrategy.IGNORED,updateStrategy = FieldStrategy.IGNORED,whereStrategy = FieldStrategy.IGNORED)
private String remark;
@Transient
@TableField(exist = false)
private List<DictionaryData2> datas;
}
子字典:
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.time.LocalDateTime;
@Data
@TableName("t_sys_dictionary_data")
public class DictionaryData{
/**
* ID
*/
@TableId
private Integer id;
/**
* 字典主表id
*/
private Integer dictId;
/**
* 字典标签
*/
private String tag;
/**
* 字典标签-英文
*/
private String tagEn;
/**
* 字典键值
*/
private Integer key;
/**
* 状态:1-正常,2-停用
*/
private Integer status;
/**
* 更新时间
*/
private LocalDateTime updateTime;
/**
* 备注
*/
private String remark;
}
涉及导入表格的实体类
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import lombok.Data;
/**
* @author ztz
* @date 2023/9/4 13:16
*/
@Data
public class Bd3OrderDetailExcelNew implements IExcelDataModel, IExcelModel {
/**
* 行号
*/
private Integer rowNum;
/**
* 错误消息
*/
private String errorMsg;
/**
* 申请序号
*/
//@NotNull(message = "不能为空")
@Excel(name = "序号")
private Integer applyIndex;
/**
* 终端序列号
*/
//@NotBlank(message = "不能为空")
@Excel(name = "用户终端序列号")
private String terminalSerialNumber;
/**
* 北斗卡类型/终端用户类型(
1:民用智能卡、
2:民用指挥卡)
1.日常通信卡,2.数据监测卡,3.应急指挥卡,4.其他类型卡
*/
//@NotNull(message = "不能为空")
@Excel(name = "终端用户类型",dict = "terminal_user_type")
private Integer cardType;
/**
* 使用区域
*/
//@NotBlank(message = "不能为空")
@Excel(name = "使用区域")
private String usageArea;
/**
* 使用人姓名
*/
//@NotBlank(message = "不能为空")
@Excel(name = "使用人姓名")
private String userName;
/**
* 使用人证件号
*/
//@NotBlank(message = "不能为空")
@Excel(name = "使用人证件号")
private String userIdNumber;
/**
* 使用人联系电话
*/
//@NotBlank(message = "不能为空")
@Excel(name = "使用人联系电话")
private String userMobile;
/**
* 使用频度/服务频度
*/
//@NotNull(message = "不能为空")
@Excel(name = "服务频度",dict = "bdcard_frequency")
private Integer frequency;
@Excel(name = "通讯等级",dict = "bdcard_level")
private Integer cardLevel;
/**.
* 自建编组数量
*/
//@NotNull(message = "不能为空")
@Excel(name = "自建编组数量",dict = "group_number")
private Integer groupNumber;
/**
* 区域与全球是否互通 开通1 不开通0
*/
@Excel(name = "区域与全球是否互通",dict = "local_global_Inter_flow")
private Integer localGlobalInterflow;
/**
* 系统回执
*/
@Excel(name = "系统回执",dict = "sys_receipt")
private Integer sysReceipt;
/**
* 频度接收系统调控 0否1是
*/
@Excel(name = "频度接收系统调控",dict = "frequency_rec_sys")
private Integer frequencyRecSys;
/**
* 北二与北三是否互通
*/
@Excel(name = "北二与北三是否互通",dict = "bd2_bd3_lInter_flow")
private Integer bd2Bd3Interflow;
/**
* 是否应急搜救用户 0否1是
*/
@Excel(name = "是否应急搜救用户",dict = "emergency_search_rescue_user")
private Integer emergencySearchUser;
/**
* 搜救中心ID
*/
//@NotBlank(message = "不能为空")
@Excel(name = "搜救中心ID")
private String searchCenterId;
// /**
// * 通播号
// */
// @Excel(name = "通播号")
// private String broadcastNum;
//
/**
* 新申请到的北斗卡号
*/
@Excel(name = "北斗卡号")
private String cardNum;
@Excel(name = "检测报告编号")
private String testNumber;
// @Override
// public Integer getRowNum() {
// return null;
// }
//
@Override
public void setRowNum(Integer rowNum) {
this.rowNum = rowNum+1;
}
}
一些其他工具类 可以参考 可有可无代码
异常:
import lombok.Data;
@Data
public class RdssException extends RuntimeException{
private Integer code;
private String errorMessage;
private transient Object data;
public RdssException() {
}
public RdssException(String message) {
super(message);
this.errorMessage=message;
}
public RdssException(Integer code,String message) {
super(message);
this.code=code;
this.errorMessage=message;
}
public RdssException(Integer code,String message,Object data) {
super(message);
this.code=code;
this.errorMessage=message;
this.data=data;
}
public RdssException(MessageExceptionEnum exception) {
super(exception.getMessage());
this.code = exception.getCode();
this.errorMessage = exception.getMessage();
}
}
枚举:
package com.rdss.common.exception;
/**
* 消息服务异常集合
*
*/
public enum MessageExceptionEnum {
QUEUE_CANT_EMPTY(600, "消息队列不能为空"),
MESSAGE_ID_CANT_EMPTY(601, "消息id不能为空"),
MESSAGE_BODY_CANT_EMPTY(602, "消息body不能为空"),
CANT_FIND_MESSAGE(603, "查找不到消息"),
MESSAGE_NUMBER_WRONG(604, "消息数量错误"),
MESSAGE_QUEUE_ERROR(605, "消息队列服务器处理异常"),
MESSAGE_TYPE_ERROR(606, "消息接收到的格式错误,非TEXT类型"),
CUSTOM_CODE(4, "发生异常"),
EXCEL_CODE(5, "excel数据不正确"),
INVALIDTOKEN(500,"令牌失效"),
SERVER_NOTCOMPLETE(501,"服务未启动"),
USERNAME_PWD_ERROR(400, "用户名或密码错误");
MessageExceptionEnum(int code, String message) {
this.code = code;
this.message = message;
}
private Integer code;
private String message;
public Integer getCode() {
return code;
}
public void setCode(Integer code) {
this.code = code;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
}
原文地址:https://blog.csdn.net/qq_33651286/article/details/140682006
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!