Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题,就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。easyexcel重写了poi对07版Excel的解析,使得原本一个3M的excel用POI sax依然需要100M左右内存降低到几M,并且再大的excel不会出现内存溢出,03版依赖POI的sax模式。在上层做了模型转换的封装,让使用者更加简单方便。
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
文档地址:https://alibaba-easyexcel.github.io/index.html
github地址:https://github.com/alibaba/easyexcel
1、导出示例
示例链接:https://alibaba-easyexcel.github.io/quickstart/write.html
2、导入示例
示例链接:https://alibaba-easyexcel.github.io/quickstart/read.html
3、EasyExcel集成
(1)添加依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
</dependencies>
(2)定义实体类对象
@Data
public class DictEeVo {
@ExcelProperty(value = "id",index = 0)
private Long id;
@ExcelProperty(value = "上级id",index = 1)
private Long parentId;
@ExcelProperty(value = "名称",index = 2)
private String name;
@ExcelProperty(value = "值",index = 3)
private String value;
@ExcelProperty(value = "编码",index = 4)
private String dictCode;
}
(3)导出数据到excel操作:
service层:在DictService类添加接口
/**
* 导出
* @param response
*/
void exportData(HttpServletResponse response);
在DictServiceImpl类添加接口实现类
@Override
public void exportData(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("数据字典", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
List<Dict> dictList = dictMapper.selectList(null);
List<DictEeVo> dictVoList = new ArrayList<>(dictList.size());
for(Dict dict : dictList) {
DictEeVo dictVo = new DictEeVo();
BeanUtils.copyBean(dict, dictVo, DictEeVo.class);
dictVoList.add(dictVo);
}
EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictVoList);
} catch (IOException e) {
e.printStackTrace();
}
}
controller层:在DictController类添加方法
@ApiOperation(value="导出")
@GetMapping(value = "/exportData")
public void exportData(HttpServletResponse response) {
dictService.exportData(response);
}
(4)从excel中导入数据到数据库
创建回调监听器
public class DictListener extends AnalysisEventListener<DictEeVo> {
private DictMapper dictMapper;
public DictListener(DictMapper dictMapper) {
this.dictMapper = dictMapper;
}
//一行一行读取
@Override
public void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {
//调用方法添加数据库
Dict dict = new Dict();
BeanUtils.copyProperties(dictEeVo,dict);
dictMapper.insert(dict);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
service层
//导入数据字典
@Override
public void importDictData(MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(),DictEeVo.class,new DictListener(baseMapper)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}
controller层
@ApiOperation(value = "导入")
@PostMapping("importData")
public Result importData(MultipartFile file) {
dictService.importData(file);
return Result.ok();
}