导入数据
实体类
package net.rjgf.wbwf.nanning.domain;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import net.rjgf.common.domain.PageEntity;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
import java.util.Date;
/**
*
*
* @author
* @date 2022-08-12
*/
public class ImportDedicatedLine implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelIgnore
private PageEntity page;
/**
* 主键
*/
@ExcelIgnore
private Long id;
/**
* 业务主键
*/
@NotNull(message = "业务标识*不能为空")
@ExcelProperty(value = "业务标识*")
private String businessId;
/**
* 专线类型
*/
@NotNull(message = "专线类型*不能为空")
@ExcelProperty(value = "专线类型*")
private String type;
}
控制器层
/**
* 上传数据
*
* @param file 支持.xlsx
* @return 提示消息
*/
@PostMapping("/import")
public Message importDedicatedLine(MultipartFile file) {
return new Message(importDedicatedLineService.importDedicatedLine(file));
}
业务层
/**
* 上传数据
*
* @param file 支持.xlsx
* @return 提示消息
*/
@Override
public List<String> importDedicatedLine(MultipartFile file) {
List<String> messageList = new ArrayList<String>();
try {
//文件流
EasyExcel.read(file.getInputStream(),
//实体类
ImportDedicatedLine.class,
//监听器
new ImportDedicatedLineListener(importDedicatedLineRepository, securityUtils))
.sheet("专线").doRead();
} catch (ExcelAnalysisException e) {
StringTokenizer st = new StringTokenizer(e.getMessage(), "|");
while (st.hasMoreElements()) {
messageList.add(st.nextElement().toString());
}
return messageList;
} catch (IOException e) {
e.printStackTrace();
}
return messageList;
}
监听器
package net.rjgf.wbwf.nanning.listen.easyexcel;
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 net.rjgf.common.util.SecurityUtils;
import net.rjgf.wbwf.nanning.domain.ImportDedicatedLine;
import net.rjgf.wbwf.nanning.repository.ImportDedicatedLineRepository;
import net.rjgf.wbwf.nanning.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.validation.annotation.Validated;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.constraints.NotNull;
import java.text.MessageFormat;
import java.util.*;
/**
*
*
* @author
* @date 2022-08-12
*/
public class ImportDedicatedLineListener extends AnalysisEventListener<ImportDedicatedLine> {
private ImportDedicatedLineRepository importDedicatedLineRepository;
private SecurityUtils securityUtils;
/**
* 读取五百条数据,批量插入一次。
*/
private static final int BATCH_COUNT = 500;
/**
* 记录Excel读取的行数
*/
private int count = 0;
/**
* 保存读取成功的Excel数据
*/
private List<ImportDedicatedLine> list = new ArrayList<>(BATCH_COUNT * 2);
/**
* 保存读取失败的Excel消息
*/
private List<String> errorMessage = new ArrayList<>();
/**
* 行头
*/
private Map headMap = new HashMap<>();
/**
* Sprinboot参数校验
*/
private static Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
/**
* 构造函数初始化监听器
*/
public ImportDedicatedLineListener(ImportDedicatedLineRepository importDedicatedLineRepository, SecurityUtils securityUtils) {
this.importDedicatedLineRepository = importDedicatedLineRepository;
this.securityUtils = securityUtils;
}
/**
* 获取表头名
*
* @param headMap
* @param context
*/
@Override
public void invokeHeadMap(Map headMap, AnalysisContext context) {
this.headMap = headMap;
}
//利用SpringBoot自带的注解进行参数校验
public void validate(ImportDedicatedLine importDedicatedLine) {
Set<ConstraintViolation<ImportDedicatedLine>> validate = validator.validate(importDedicatedLine);
if (validate.size() > 0) {
StringBuilder sb = new StringBuilder();
validate.forEach(it -> {
sb.append(it.getMessage());
sb.append("\t");
});
throw new ExcelAnalysisException(sb.toString());
} else {
importDedicatedLine.setCreateBy(securityUtils.getCurrentUser().getId().toString());
importDedicatedLine.setCreateTime(new Date());
this.count = this.count + 1;
}
}
/**
* 解析到一条数据,对每条数据进行操作
*/
@Override
public void invoke(ImportDedicatedLine importDedicatedLine, AnalysisContext analysisContext) {
validate(importDedicatedLine);
list.add(importDedicatedLine);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
/**
* 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
*
* @param exception
* @param context
* @throws Exception
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
String mes = null;
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();
mes = MessageFormat.format("错误发生在{0}行,{1}列,数据为:{2}", (row + 1), columnName, cellData);
}
// 自定义抛出的异常
else {
mes = MessageFormat.format("错误发生在{0}行,原因为:{1},", this.count, exception.getMessage());
}
errorMessage.add(mes);
}
/**
* 读取所有数据完毕进行的操作
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
if (list.size() > 0) {
saveData();
list.clear();
}
StringBuilder sb = new StringBuilder();
String countMessage = MessageFormat.format(
"一共{0}条数据,成功导入{1}条数据,重复数据不会插入:",
this.count, this.count - this.errorMessage.size());
sb.append(countMessage);
//将错误信息用字符串返回
if (this.errorMessage.size() > 0) {
errorMessage.forEach((it) -> {
sb.append("|");
sb.append(it);
});
}
throw new ExcelAnalysisException(sb.toString());
}
/**
* 具体执行读取到的excel数据进行存储的逻辑,比如写入到mysql等操作
*/
private void saveData() {
importDedicatedLineRepository.batchInsert(list);
}
}
导出数据
这里导出到服务器硬盘
@Override
public String exportDedicatedLine(ImportDedicatedLine importDedicatedLine) {
String fileName = "xxx" + DateUtil.getNowYYYYMMDDHHMMSS2() + ExcelTypeEnum.XLSX.getValue();
String filePath = uploadDir + fileName;
ExcelWriter excelWriter = EasyExcel.write(filePath).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0, "专线").head(ImportDedicatedLine.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
//获取总数据量
int count = importDedicatedLineRepository.getCount(importDedicatedLine);
//最大导出数量
int exportCount = 500;
if (count > exportCount) {
//分批查询数据库次数
int countTime = count / exportCount + 1;
for (int i = 0; i < countTime; i++) {
Page page = PageHelper.startPage(i + 1, exportCount);
List<ImportDedicatedLine> exportList = importDedicatedLineRepository
.selectImportDedicatedLineList(importDedicatedLine);
excelWriter.write(exportList, writeSheet);
}
} else {
List<ImportDedicatedLine> exportList = importDedicatedLineRepository
.selectImportDedicatedLineList(importDedicatedLine);
excelWriter.write(exportList, writeSheet);
}
//刷新流
excelWriter.finish();
return fileName;
}