需求:生成Excel模板,指定用户录入的数据,用户填好后,上传读取,存入数据库中。
1. 生成Excel模板
首先是Excel的实体类
package com.sinosoft.lf.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import java.io.Serializable;
/**
* @Author 庭前云落
* @Date 2021/6/22 14:48
* @Description 三级终结导出Excel模板类
*/
@Data
public class VisitThreeEndTitleInfo implements Serializable{
@ColumnWidth(30)
@ExcelProperty({"三级终结人员身份证号"})
private String threeEndCardId;
@ColumnWidth(25)
@ExcelProperty({"三级终结人员姓名"})
private String threeEndName;
}
主要就是调用Service层的实现,注意生成的时候中文乱码问题,采用标准格式就不会有这个问题。
public void visitThreeEndExcelOut() {
try {
SimpleDateFormat dateToStr = new SimpleDateFormat("yyyyMMdd");
//采用标准格式 这样不会出现有汉字无法显示的问题
String fileName = new String(("三级终结人员导入模板_" + dateToStr.format(new Date()) + ".xls").getBytes("gb2312"), "iso8859-1");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
EasyExcel.write(response.getOutputStream(), VisitThreeEndTitleInfo.class).sheet("模板").doWrite(null);
} catch (IOException e) {
e.printStackTrace();
}
}
2. 读取已填写的Excel模板
这个实体类和上面用的是一个,然后就是创建一个Listener。
实体类里面存的就是两个字段,读取出来后,set进要添加的数据库实体类中。
/**
* @Author 庭前云落
* @Date 2021/6/22 17:40
* @Description
*/
public class VisitThreeEndInfoListener extends AnalysisEventListener<VisitThreeEndTitleInfo> {
private static final Logger LOGGER = LoggerFactory.getLogger(VisitThreeEndInfoListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
//这里一个是储存一个是用来计算总数的
List<VisitThreeEndTitleInfo> list = new ArrayList<VisitThreeEndTitleInfo>();
List<VisitThreeEndTitleInfo> sumList = new ArrayList<VisitThreeEndTitleInfo>();
//mapper查询数据库是否有重复数据
private VisitThreeEndInfoMapper visitThreeEndInfoMapper;
//request用户获取session中的数据,因为一些数据需要手动填充
private HttpServletRequest request;
//Listner里面不知道怎么返回,用response往请求头里面set
private HttpServletResponse response;
//这个是调用save方法,这个项目用的,不太清楚为什么是service
private IVisitThreeEndInfoService iVisitThreeEndInfoService;
//构造方法
public VisitThreeEndInfoListener(VisitThreeEndInfoMapper visitThreeEndInfoMapper, HttpServletRequest request, IVisitThreeEndInfoService iVisitThreeEndInfoService, HttpServletResponse response) {
this.visitThreeEndInfoMapper = visitThreeEndInfoMapper;
this.request = request;
this.iVisitThreeEndInfoService = iVisitThreeEndInfoService;
this.response = response;
}
/**
* 这个每一条数据解析都会来调用
*
* @param visitThreeEndTitleInfo
* @param analysisContext
*/
@Override
public void invoke(VisitThreeEndTitleInfo visitThreeEndTitleInfo, AnalysisContext analysisContext) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(visitThreeEndTitleInfo));
//记录总数
sumList.add(visitThreeEndTitleInfo);
//根据加密后的身份证号查询
VisitThreeEndInfo visitThreeEndInfo = visitThreeEndInfoMapper.selectOneByThreeEndCardId(Encrypt.encrypt(visitThreeEndTitleInfo.getThreeEndCardId()));
//数据库里没有重复数据,保存到集合
if (visitThreeEndInfo == null) {
list.add(visitThreeEndTitleInfo);
}
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
//if (list.size() >= BATCH_COUNT) {
// saveData();
// 存储完成清理 list
//list.clear();
//}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
response.setHeader("msg", "全部数据:" + sumList.size() + "条," + "已录入数据:" + list.size() + "条," + "重复数据:" + (sumList.size() - list.size()) + "条");
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
//获取当前登录用户信息
PersonInfo personInfo = (PersonInfo) request.getSession().getAttribute("personInfo");
for (VisitThreeEndTitleInfo visitThreeEndTitleInfo : list) {
VisitThreeEndInfo visitThreeEndInfo = new VisitThreeEndInfo();
//将读取的数据赋给visitThreeEndInfo
visitThreeEndInfo.setThreeEndCardId(Encrypt.encrypt(visitThreeEndTitleInfo.getThreeEndCardId()));
visitThreeEndInfo.setThreeEndName(Encrypt.encrypt(visitThreeEndTitleInfo.getThreeEndName()));
visitThreeEndInfo.setCreatorId(personInfo.getOid());
visitThreeEndInfo.setCreatorName(personInfo.getUserCname());
visitThreeEndInfo.setCreateDate(new Date());
visitThreeEndInfo.setIsValid("1");
iVisitThreeEndInfoService.save(visitThreeEndInfo);
}
LOGGER.info("存储数据库成功!");
}
}
这里官网写的有一点小区别,主要是我这里要返回一些数据,而且要先把数据判断一下才能保存,然后保存用的是封装好的,所以没有在xml遍历集合。
service里面直接调用就好
@Transactional(rollbackFor = Exception.class)这个注解,出现异常会回滚
/**
* @Author 庭前云落
* @Date 2021/6/23 18:00
* @Description
*/
@Service
@Transactional(rollbackFor = Exception.class)
public class VisitThreeEndInfoServiceImpl extends ServiceImpl<VisitThreeEndInfoMapper, VisitThreeEndInfo> implements IVisitThreeEndInfoService {
@Resource
VisitThreeEndInfoMapper mapper;
@Resource
private IVisitThreeEndInfoService iVisitThreeEndInfoService;
@Resource
private HttpServletResponse response;
/**
* 三级终结人员批量导入(就是读取Excel数据,往数据库里面批量新增)
* @param file
* @param request
*/
@Override
public void visitThreeEndInfoImport(MultipartFile file, HttpServletRequest request) {
try {
EasyExcel.read(file.getInputStream(), VisitThreeEndTitleInfo.class, new VisitThreeEndInfoListener(mapper,request,iVisitThreeEndInfoService,response)).sheet().doRead();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Controller
/**
* @Author 庭前云落
* @Date 2021/6/23 18:00
* @Description
*/
@Api(tags = "三级终结基本信息")
@RestController
@RequestMapping("/lf/visit-three-end-info")
public class VisitThreeEndInfoController {
@Resource
private IVisitThreeEndInfoService service;
@Resource
private HttpServletResponse response;
/**
* 三级终结批量导入
*/
@ApiOperation(value = "三级终结批量导入")
@PostMapping("/visitThreeEndInfoImport")
public Result visitThreeEndInfoImport(MultipartFile file, HttpServletRequest request) {
Result result = new Result();
try {
service.visitThreeEndInfoImport(file, request);
String msg = response.getHeader("msg");
result.setMsg(msg);
} catch (Exception e) {
e.printStackTrace();
result.setMsg(e.getMessage());
}
return result;
}
}