最近项目中需要用到Excel表格的上传下载功能,于是选择了EasyExcel这款工具,总的来说非常的好用,下面就做一个简单的演示。
官方文档地址
一、依赖
- EasyExcel · 语雀
- Alibaba Easy Excel - 简单、省内存的Java解析Excel工具 | 首页
pom.xml 中需要添加的依赖
二、创建实体类:和模板一致对应com.alibaba easyexcel2.2.7
这里用到了 @ExcelProperty 注解,这个注解很重要必要的一个注解,注解中的两个参数value,index分别代表列名,列序号
1.value 通过标题文本对应
2.index 通过文本行号对应
package com.sinosoft.springbootplus.org.param;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
@Data
@ContentRowHeight(21)
@HeadRowHeight(30)
@ColumnWidth(25)
public class JgjgPersonAttendanceImport {
@ExcelProperty(value = {"地区"},index = 0)
private String areaName;
@ExcelProperty(value = {"处室"},index = 1)
private String officeName;
@ExcelProperty(value = {"姓名"},index = 2)
private String personName;
@ExcelProperty(value = {"请假类型"},index = 3)
private String leaveType;
@ExcelProperty(value = {"开始时间"},index = 4)
private Date startTime;
@ExcelProperty(value = {"结束时间"},index = 5)
private Date endTime;
@ExcelProperty(value = {"时长"},index = 6)
private Integer time;
@ExcelProperty(value = {"天数"},index = 7)
private BigDecimal day;
}
三、监听器(easyexcel是在监听器层完成excel数据读取):
由于读取excel需要实现监听器,并且该监听器不能被spring容器管理,所以我们spring的注解不能在里面用,所以我们需要将service层的接口传入该监听器,实现将excel的内容存储至数据库中。 有个很重要的点 监听器不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
package com.sinosoft.springbootplus.org.domain.entity; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSON; import com.sinosoft.springbootplus.org.convert.JgjgPersonAttendanceConvert; import com.sinosoft.springbootplus.org.domain.service.JgjgPersonAttendanceDomain; import com.sinosoft.springbootplus.org.param.JgjgPersonAttendanceImport; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.ArrayList; import java.util.List; public class JgjgPersonAttendanceListener extends AnalysisEventListener{ private static final Logger LOGGER = LoggerFactory.getLogger(JgjgPersonAttendanceListener.class); private static final int BATCH_COUNT = 5; List list = new ArrayList (); private JgjgPersonAttendanceDomain jgjgPersonAttendanceDomain; public JgjgPersonAttendanceListener() { } public JgjgPersonAttendanceListener(JgjgPersonAttendanceDomain jgjgPersonAttendanceDomain) { this.jgjgPersonAttendanceDomain = jgjgPersonAttendanceDomain; } @Override public void invoke(JgjgPersonAttendanceImport jgjgPersonAttendanceImport, AnalysisContext context) { LOGGER.info("解析到一条数据:{}", JSON.toJSONString(jgjgPersonAttendanceImport)); list.add(jgjgPersonAttendanceImport); // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据,确保最后遗留的数据也存储到数据库 saveData(); LOGGER.info("所有数据解析完成!"); } private void saveData() { LOGGER.info("{}条数据,开始存储数据库!", list.size()); for (JgjgPersonAttendanceImport jgjgPersonAttendanceImport : list) { //判断状态重新赋值 if (jgjgPersonAttendanceImport.getLeaveType().equals("病假")){ jgjgPersonAttendanceImport.setLeaveType("0"); }else if (jgjgPersonAttendanceImport.getLeaveType().equals("事假")){ jgjgPersonAttendanceImport.setLeaveType("1"); }else if (jgjgPersonAttendanceImport.getLeaveType().equals("产假")){ jgjgPersonAttendanceImport.setLeaveType("2"); }else if (jgjgPersonAttendanceImport.getLeaveType().equals("婚假")){ jgjgPersonAttendanceImport.setLeaveType("3"); }else if (jgjgPersonAttendanceImport.getLeaveType().equals("丧假")){ jgjgPersonAttendanceImport.setLeaveType("4"); }else if (jgjgPersonAttendanceImport.getLeaveType().equals("其他")){ jgjgPersonAttendanceImport.setLeaveType("5"); } JgjgPersonAttendance jgjgPersonAttendance = JgjgPersonAttendanceConvert.INSTANCE.jgjgPersonAttendanceImportToJgjgPersonAttendance(jgjgPersonAttendanceImport); jgjgPersonAttendanceDomain.saveJgjgPersonAttendance(jgjgPersonAttendance); } LOGGER.info("存储数据库成功!"); } }
四、controller层
//导入
@PostMapping("/insertList")
@ApiOperation(value = "获取JgjgPersonAttendance导入", notes = "监管机构系统-导入")
public void insetJgjgPersonAttendanceImport(MultipartFile file){
jgjgPersonAttendanceServiceImpl.insetJgjgPersonAttendanceImport(file);
}
五、service层
//导入
public void insetJgjgPersonAttendanceImport(MultipartFile file){
try {
InputStream inputStream = file.getInputStream();
EasyExcel.read(inputStream, JgjgPersonAttendanceImport.class, new JgjgPersonAttendanceListener(jgjgPersonAttendanceDomain)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}



