实体类
/**
* 前端Excel导入数据库对应实体
* @author
* @date 2022-03-10
*/
@Data
@TableName("")
//一定要实现序列化,防止内存溢出
public class EquipmentExcel implements Serializable {
/**
* 设备编码
*/
@ExcelProperty(value = {"所属部门"})
@TableField("dept_guid")
private String deptGuid;
}
控制层
@ControllerEndpoint(operation = "上传设备数据", exceptionMessage = "导入Excel失败")
/*consumes: 指定处理请求的提交内容类型(Content-Type),
例如:
application/xml
application/json
application/x-www-form-urlencoded
multipart/form-data
application/octet-stream
text/plain
text/xml
text/html*/
@PostMapping(value = "/uploadExcel", consumes = "multipart/*", headers = "content-type=multipart/form-data")
@ResponseBody
public List<String> uploadEquipmentInfo(MultipartFile file) throws IOException {
return equipmentService.uploadEquipmentInfo(file,deviceCategory,deviceType,status,user);
}
业务层
try{
EasyExcel.read(file.getInputStream(), EquipmentExcel.class, new ExcelModelListener(baseMapper,deviceCategory)).sheet().doRead();
}catch (Exception e) {
StringTokenizer st = new StringTokenizer(e.getMessage(),"|");
while (st.hasMoreElements()) {
Message.add(st.nextElement().toString());
}
}finally {
return Message;
}
监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import java.text.MessageFormat;
import java.util.*;
public class ExcelModelListener extends AnalysisEventListener<EquipmentExcel> {
/**
* 每隔100条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
// 读取三百条数据,批量插入一次。
private static final int BATCH_COUNT = 300;
// 记录Excel读取的行数
private int COUNT = 0;
// 记录Excel重复的行数
private int duplicateCOUNT = 0;
//保存读取成功的Excel数据
private List<EquipmentExcel> list = new ArrayList<>();
//保存读取失败的Excel消息
private List<String> errorMessage = new ArrayList<>();
//DAO
private EquipmentMapper baseMapper;
// 设备类别(有线设备,NB设备,消防主机,用户传输装置)
private List<DictValue> deviceCategory = new ArrayList<>();
//初始化监听器
public ExcelModelListener(EquipmentMapper baseMapper, List<DictValue> deviceCategory, List<DictValue> deviceType, List<DictValue> status, CurrentUser user,List Tdept,List Tguiddata) {
this.baseMapper = baseMapper;
this.deviceCategory = deviceCategory;
}
//解析到一条数据,对每条数据进行 *** 作
@Override
public void invoke(EquipmentExcel data, AnalysisContext context) {
this.COUNT = this.COUNT + 1;
// 判断字段长度和字段为空
checkLengthAndNull(data);
// 检查自定义字符串格式,将字符串转化成字典id,绑定默认值
checkDiy(data);
// 映射字段
//添加到集合
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
/**
* 读取所有数据完毕进行的 *** 作
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
StringBuilder sb = new StringBuilder();
String countMessage = MessageFormat.format(
"一共{0}条数据,成功导入{1}条数据,重复{2}条数据,失败{3}条数据:",
this.COUNT, this.COUNT - this.duplicateCOUNT - this.errorMessage.size(),
duplicateCOUNT, this.errorMessage.size());
sb.append(countMessage);
//将错误信息用字符串返回
if (this.errorMessage.size() > 0) {
errorMessage.forEach((it) -> {
sb.append("|");
sb.append(it);
});
}
throw new ExcelAnalysisException(sb.toString());
}
/**
* 获取表头名
*
* @param headMap
* @param context
*/
@Override
public void invokeHeadMap(Map headMap, AnalysisContext context) {
this.headMap = headMap;
}
/**
* 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
*
* @param exception
* @param context
* @throws Exception
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
if (exception instanceof ExcelDataConvertException) {
//转换格式发生的异常,不会进入invoke解析,这里要++ 把解析失败数据也加上
this.COUNT=this.COUNT+1;
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
// 获取列名
String columnName = (String) this.headMap.get(excelDataConvertException.getColumnIndex());
// 获取行
int row = excelDataConvertException.getRowIndex();
// 获取出错的单元块
String cellData = excelDataConvertException.getCellData().toString();
String mes = MessageFormat.format("错误发生在{0}行,{1}列,数据为:{2}", (row+1), columnName, cellData);
errorMessage.add(mes);
}
// 自定义抛出的异常
else {
errorMessage.add(exception.getMessage());
}
}
/**
* 具体执行读取到的excel数据进行存储的逻辑,比如写入到mysql等 *** 作
*/
private void saveData() {
//一次性插入多条,自定义sql语句
if (!CollectionUtils.isEmpty(list)) {
//去重前数据条数
int originSize = list.size();
// 调用Excel内部数据去重方法
list = duplicateRemovalExcel(list);
//调用数据库内部数据去重方法
duplicateRemoval(list);
//去重后数据条数
int duplicateSize = list.size();
this.duplicateCOUNT = this.duplicateCOUNT + (originSize - duplicateSize);
}
//再判断一次,防止去重完毕List为空,插入数据库报错
if(!CollectionUtils.isEmpty(list)) {
//最后插入数据
//插入数据库要不要捕捉异常
baseMapper.insertexcel(list);
}
}
// 自定义异常
private void throwException(String message) {
String mes = MessageFormat.format("错误发生在{0}行:{1}", this.COUNT+1,message);
throw new ExcelAnalysisException(mes);
}
// 检查字段长度和字段为空
private void checkLengthAndNull(EquipmentExcel data) {
// 如果整行数据为空
if (data == null) {
throwException("整行数据为空");
}
//数据库中的数据去重
private List<EquipmentExcel> duplicateRemoval(List<EquipmentExcel> data) {
List<String> dbDeviceSource = baseMapper.getAllDeviceSource();
// 不能用foreach 迭代删除 java.util.ConcurrentModificationException
for (Iterator<EquipmentExcel> i = data.iterator(); i.hasNext(); ) {
EquipmentExcel equipmentExcel = i.next();
for (Iterator<String> j = dbDeviceSource.iterator(); j.hasNext(); ) {
String deviceSource = j.next();
if (equipmentExcel.getDeviceSource().equals(deviceSource)) {
i.remove();
}
}
}
return data;
}
// 先把Excel中重复的数据去除
private List<EquipmentExcel> duplicateRemovalExcel(List<EquipmentExcel> list) {
Set<EquipmentExcel> EquipmentExcelSet = new TreeSet<>((o1, o2) -> o1.getDeviceSource().compareTo(o2.getDeviceSource()));
EquipmentExcelSet.addAll(list);
return new ArrayList<>(EquipmentExcelSet);
}
}
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)