一、官方文档参考
官方文档:https://easyexcel.opensource.alibaba.com/docs/current/
二、EasyExcel特点
- Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并大量不大的话可能还行,但是一旦并发上来后一定会OOM或者JVM频繁的full gc。
- EasyExcel是阿里巴巴开源的excel处理框架,以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
- EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。
三、应用场景
- 数据导入:减轻录入工作量
- 数据导出:统计信息归档
- 数据传输:异构系统之间数据传输
异构系统是指跨越不同操作系统平台、不同数据库平台、不同应用服务器平台的系统集合,也可以理解为一种多技术环境的开发环境。它的特点是两个或多个独立的、不同技术的系统能够共同完成一种业务的处理,比如来自不同的客户端的数据可以通过不同的操作系统处理,经过不同的应用处理后,最后结果保存在不同的数据库之中。
四、读、写Excel
1、导入依赖
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency><dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-simple</artifactId>
<version>1.7.5</version>
</dependency><dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency><dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency><dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency></dependencies>
2、创建实体类
package com.atguigu.easyexcel.dto;
@Data
public class ExcelStudentDTO {@ExcelProperty("姓名")
private String name;@ExcelProperty("生日")
private Date birthday;@ExcelProperty("薪资")
private Double salary;
}
3、简单读写
3.1 写
public class ExcelWriteTest { @Test public void simpleWriteTestXlsx(){ // 写法1 JDK8+ String fileName = "D:\\studyproject\\EasyExcel\\simpleWrite.xlsx"; // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭 EasyExcel.write(fileName, ExcelStudentDTO.class) .sheet("模板") .doWrite(data()); } @Test public void simpleWriteTestXls(){ // 写法1 JDK8+ String fileName = "D:\\studyproject\\EasyExcel\\simpleWrite.xls"; // 要写入xls就需要再添加一个参数excelType() EasyExcel.write(fileName, ExcelStudentDTO.class) .excelType(ExcelTypeEnum.XLS) .sheet("模板") .doWrite(data()); } // 辅助方法 private List<ExcelStudentDTO> data() { List<ExcelStudentDTO> list = new ArrayList<>(); for (int i = 0; i < 10; i++) { ExcelStudentDTO data = new ExcelStudentDTO(); data.setName("字符串" + i); data.setBirthday(new Date()); data.setSalary(0.56); list.add(data); } return list; } }
注:Excel有两种后缀:
xlsx:Excel 2007及更高版本的默认文件格式
xls:Excel 97到Excel 2003的默认文件格式
写入大量数据:
xls 版本的Excel最多一次可写0 ...65535行
xlsx 版本的Excel最多一次可写0...1048575行
3.2 读
因为解析结果是以观察者模式通知处理,所以需要继承一个AnalysisEventListener监听器,实现两个抽象方法invoke和doAfterAllAnalysed
invoke:在解析一行数据的时候调用
doAfterAllAnalysed:在数据全部解析完成后调用
@Slf4j public class ExcelStudentDTOListener extends AnalysisEventListener<ExcelStudentDTO> { @Override public void invoke(ExcelStudentDTO data, AnalysisContext context) { log.info("解析到一条数据:" + data); } @Override public void doAfterAllAnalysed(AnalysisContext context) { log.info("所有数据解析完成!"); } }
public class ExcelReadTest { @Test public void simpleReadXlsx(){ String fileName = "D:\\studyproject\\EasyExcel\\simpleWrite.xlsx"; EasyExcel.read(fileName, ExcelStudentDTO.class,new ExcelStudentDTOListener()) .sheet() .doRead(); } @Test public void simpleReadXls(){ String fileName = "D:\\studyproject\\EasyExcel\\simpleWrite.xls"; EasyExcel.read(fileName, ExcelStudentDTO.class,new ExcelStudentDTOListener()) .excelType(ExcelTypeEnum.XLS) .sheet() .doRead(); } }
五、导入、导出案例
5.1导入
5.1.1 添加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
</dependency><dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
</dependency>
5.1.2 创建实体类
@Data public class ExcelDictDTO { @ExcelProperty("id") private Long id; @ExcelProperty("上级id") private Long parentId; @ExcelProperty("名称") private String name; @ExcelProperty("值") private Integer value; @ExcelProperty("编码") private String dictCode; }
5.1.3 创建监听器
@Slf4j @NoArgsConstructor public class ExcelDictDTOListener extends AnalysisEventListener<ExcelDictDTO> { private DictMapper dictMapper; // 数据列表 private List<ExcelDictDTO> list = new ArrayList<>(); // 每隔5条记录批量存储一次数据 private static final int BATCH_COUNT = 5; public ExcelDictDTOListener(DictMapper dictMapper) { this.dictMapper = dictMapper; } @Override public void invoke(ExcelDictDTO data, AnalysisContext context) { log.info("解析到一条记录:{}",data); // 将数据存入到数据列表 list.add(data); if (list.size() >= BATCH_COUNT){ saveData(); list.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { // 当最后剩余的数据记录数不足BATCH_COUNT时,最终一次性存储剩余数据 saveData(); log.info("所有数据解析完成!"); } private void saveData(){ log.info("{} 条数据被存储到数据库", list.size()); // 调用mapper的save方法: save list 对象 dictMapper.insertBatch(list); log.info("{} 条数据被存储到数据库成功!", list.size()); } }
5.1.4 mapper层批量插入
接口
void insertBatch(List<ExcelDictDTO> list);
xml
<insert id="insertBatch">
insert into dict (
id ,
parent_id ,
name ,
value ,
dict_code
) values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id} ,
#{item.parentId} ,
#{item.name} ,
#{item.value} ,
#{item.dictCode}
)
</foreach>
</insert>
5.1.5 service创建监听器实例
接口
void importData(InputStream inputStream);
实现类
// 一旦出现问题,全部回滚,重新导入,防止出现一半成功的数据一半失败的数据 @Transactional(rollbackFor = Exception.class) @Override public void importData(InputStream inputStream){ EasyExcel.read(inputStream, ExcelDictDTO.class, new ExcelDictDTOListener(baseMapper)).sheet().doRead(); log.info("excel导入成功"); }
5.1.6 controller层实现导入功能
@ApiOperation("Excel数据的批量导入") @PostMapping("import") public R batchImport( @ApiParam(value = "Excel数据字典文件", required = true) @RequestParam("file") MultipartFile file){ try { // 从上传的文件中取出inputStream,传递给importData service,通过监听器读取每一行的内容 InputStream inputStream = file.getInputStream(); dictService.importData(inputStream); return R.ok().message("数据字典数据批量导入成功"); } catch (Exception e) { throw new BusinessException(ResponseEnum.UPLOAD_ERROR,e); } }
5.2 导出
5.2.1 service实现解析Excel数据
@Override public List<ExcelDictDTO> listDictData() { // 从数据库中取出数据 List<Dict> dictList = baseMapper.selectList(null); // 创建ExcelDictDTO列表,将Dict列表转换成ExcelDictDTO列表 ArrayList<ExcelDictDTO> excelDictDTOList = new ArrayList<>(dictList.size()); dictList.forEach(dict ->{ ExcelDictDTO excelDictDTO = new ExcelDictDTO(); BeanUtils.copyProperties(dict, excelDictDTO); excelDictDTOList.add(excelDictDTO); }); return excelDictDTOList; }
5.2.2 controller实现导出
@ApiOperation("Excel数据导出") @GetMapping("/export") public void export(HttpServletResponse response) throws IOException { // 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman // 输出excel数据类型 编码方式 response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("mydict", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // write(文件,要封装的数据类型) // sheet(模板名称) // doWrite(数据列表) EasyExcel.write(response.getOutputStream(), ExcelDictDTO.class).sheet("数据字典").doWrite(dictService.listDictData()); }