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模式。在上层做了模型转换的封装，让使用者更加简单方便
一、准备工作
使用mybatis-plus来批量保存一个表中的数据
1、新建表
CREATE TABLE `demo_excel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(30) DEFAULT NULL,
`content` varchar(256) DEFAULT NULL,
`page_num` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
)
2、创建批量插入的方法
在service中添加方法
@Override
public void saveList(List<DemoExcel> list) {
this.baseMapper.batchInsert(list);
}
在mapper中添加方法
/**
* 批量插入
* @param users
*/
void batchInsert(List<DemoExcel> users);
在mapper.xml中添加批量插入
<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
INSERT INTO `demo_excel`
(
title, content, page_num, create_time
)
VALUES
<foreach collection="list" item="item" separator=",">
(
#{item.title}, #{item.content}, #{item.pageNum}, #{item.createTime}
)
</foreach>
</insert>
3、测试
@Autowired
private DemoExcelService demoExcelService;
@Test
public void testSaveList() {
DemoExcel excel = new DemoExcel();
excel.setTitle("标题1");
excel.setContent("内容11111");
excel.setPageNum(3);
excel.setCreateTime(LocalDateTime.now());
List<DemoExcel> list = new ArrayList<>();
list.add(excel);
demoExcelService.saveList(list);
}
4、创建一张excel表
| 标题 | 内容 | 页码 | 创建时间 |
|---|---|---|---|
| 标题1 | 的路上风景萨拉丁解放啦电缆附件是，的附加赛决定弗拉 | 5 | 2019/4/24 16:00 |
| 标题2 | 的路上风景萨拉丁解放啦电缆附件是，的附加赛决定弗拉 | 5 | 2019/4/25 16:00 |
| 标题3 | 的路上风景萨拉丁解放啦电缆附件是，的附加赛决定弗拉 | 5 | 2019/4/26 16:00 |
| 标题4 | 的路上风景萨拉丁解放啦电缆附件是，的附加赛决定弗拉 | 5 | 2019/4/27 16:00 |
二、导入
1、pom.xml 中添加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
2、导入导出的对象
不建议直接在 demoExcel 实体类上加 excel 相关注解，所以我们创建了一个专门用于 excel 的类，属性类型这里全部用 String，防止类型不一致无法转换报异常
@Data
public class DemoEasyExcel {
@ExcelProperty("标题")
private String title;
@ExcelProperty("内容")
private String content;
@ExcelProperty("页码")
private String pageNum;
@ExcelProperty("创建时间")
private String createTime;
}
3、创建监听器
package com.vicente.vicenteboot.easyexcel;
public class DemoExcelListener extends AnalysisEventListener<DemoEasyExcel> {
private static final Logger LOGGER = LoggerFactory.getLogger(DemoExcelListener.class);
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<DemoEasyExcel> list = new ArrayList<DemoEasyExcel>();
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private DemoExcelService demoExcelService;
public DemoExcelListener() {
// 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
demoExcelService = new DemoExcelServiceImpl();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param demoExcelService
*/
public DemoExcelListener(DemoExcelService demoExcelService) {
this.demoExcelService = demoExcelService;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(DemoEasyExcel data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据，开始存储数据库！", list.size());
demoExcelService.saveExcelList(list);
LOGGER.info("存储数据库成功！");
}
}
这里有一个saveExcelList方法，主要就是将从excel中读取到的DemoEasyExcel 转换成数据库的类型DemoExcel
@Override
public void saveExcelList(List<DemoEasyExcel> list) {
List<DemoExcel> demoList = new ArrayList<>();
for (DemoEasyExcel easyExcel : list) {
DemoExcel demo = new DemoExcel();
demo.setTitle(easyExcel.getTitle());
demo.setContent(easyExcel.getContent());
demo.setPageNum(Integer.parseInt(easyExcel.getPageNum()));
LocalDateTime time = LocalDateTime.parse(easyExcel.getCreateTime(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
demo.setCreateTime(time);
demoList.add(demo);
}
this.saveList(demoList);
}
4、测试
测试读取excel，并将内容写入到数据库中
@Autowired
private DemoExcelService demoExcelService;
@Test
public void testReadExcel() {
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
// 写法1：
String fileName = "D://test_excel.xlsx";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, DemoEasyExcel.class, new DemoExcelListener(demoExcelService)).sheet().doRead();
}
查看数据库表，成功将excel中的数据写进去
读取第N个sheet
// 写法2：
String fileName = "D://test_excel.xlsx";
ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
ReadSheet readSheet = EasyExcel.readSheet(0).build();
excelReader.read(readSheet);
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
读取全部的sheet
// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
EasyExcel.read(fileName, DemoEasyExcel.class, new DemoExcelListener()).doReadAll();
读取部分的sheet
ExcelReader excelReader = EasyExcel.read(fileName).build();
// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
ReadSheet readSheet1 =
EasyExcel.readSheet(0).head(DemoEasyExcel.class).registerReadListener(new DemoExcelListener()).build();
ReadSheet readSheet2 =
EasyExcel.readSheet(1).head(DemoEasyExcel.class).registerReadListener(new DemoExcelListener()).build();
// 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
excelReader.read(readSheet1, readSheet2);
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
三、写出
1、生成写入数据
从数据库中获取到要写入到excel的数据
@Override
public List<DompRole> selectRoleForExcel() {
Wrapper<DompRole> queryWrapper = new QueryWrapper<DompRole>().lt("rid", "140382957");
List<DompRole> resList = this.baseMapper.selectList(queryWrapper);
return resList;
}
2、简单写入excel
@Test
public void testWriteRoleExcel() {
List<DompRole> list = dompRoleService.selectRoleForExcel();
System.out.println(list.size());
// 写法1
String fileName = "D://simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DompRole.class).sheet("模板").doWrite(list);
// 写法2
fileName = "D://ssimpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写
ExcelWriter excelWriter = EasyExcel.write(fileName, DompRole.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(list, writeSheet);
/// 千万别忘记finish 会帮忙关闭流
excelWriter.finish();
}
查找测试结果，数据可成功写入到excel文档中，表头数据为字段名称
3、修改表头为中文
修改DompRoleExcel，设置中文标题
@Data
public class DompRoleExcel {
@ExcelProperty(value = "角色ID",index = 0)
private Long roleId;
@ExcelProperty(value = "角色编号",index = 1)
private Long rid;
@ExcelProperty(value = "角色名称",index = 2)
private String roleName;
@ExcelProperty(value = "角色状态",index = 3)
private String status;
@ExcelProperty(value = "角色描述",index = 4)
private String roleDes;
@ExcelProperty(value = "创建时间",index = 5)
private String createDate;
@ExcelProperty(value = "修改时间",index = 6)
private String updateDate;
@ExcelProperty(value = "修改人",index = 7)
private String modifyUser;
}
修改方法selectRoleForExcel，使返回的是含有ExcelProperty的实体类，将DompRole转换成DompRoleExcel
public List<DompRoleExcel> selectRoleForExcel() {
Wrapper<DompRole> queryWrapper = new QueryWrapper<DompRole>().lt("rid", "140382957");
List<DompRole> roleList = this.baseMapper.selectList(queryWrapper);
List<DompRoleExcel> resList = transferDompRoleExcel(roleList);
return resList;
}
测试
List<DompRoleExcel> list = dompRoleService.selectRoleForExcel();
System.out.println(list.size());
// 写法1
String fileName = "D://simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DompRoleExcel.class).sheet("模板").doWrite(list);
4、复杂表头
修改DompRoleExcel，
@Data
public class DompRoleExcel {
@ExcelProperty(value = {"角色", "角色ID"})
private Long roleId;
@ExcelProperty(value = {"角色", "角色编号"})
private Long rid;
@ExcelProperty(value = {"角色", "角色名称"})
private String roleName;
@ExcelProperty(value = {"角色", "角色状态"})
private String status;
@ExcelProperty(value = {"角色", "角色描述"})
private String roleDes;
@ExcelProperty(value = "创建时间")
private String createDate;
@ExcelProperty(value = "修改时间")
private String updateDate;
@ExcelProperty(value = "修改人")
private String modifyUser;
}
四、填充
1、简单填充
填写一个模板表格simple.xlsx，内容如下：
| 姓名 | 数字 | 复杂 | 忽略 |
|---|---|---|---|
| {name} | {number} | {name}今年{number}岁了 | {name}忽略，{name} |
测试填充
// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
String templateFileName = "D://simple.xlsx";
// 方案2 根据Map填充
String fileName = "D://simpleFill" + System.currentTimeMillis() + ".xlsx";
// 这里 会填充到第一个sheet， 然后文件流会自动关闭
Map<String, Object> map = new HashMap<>();
map.put("name", "张三");
map.put("number", 5.2);
EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
2、列表填充
填充list 的时候还要注意 模板中{.} 多了个点 表示list
| 姓名 | 数字 | 复杂 | 忽略 |
|---|---|---|---|
| {.name} | {.number} | {.name}今年{.number}岁了 | {.name}忽略，{.name} |
测试列表
// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
// 填充list 的时候还要注意 模板中{.} 多了个点 表示list
String templateFileName = "D://simple.xlsx";
// 方案1 一下子全部放到内存里面 并填充
String fileName = "D://listFill" + System.currentTimeMillis() + ".xlsx";
// 这里 会填充到第一个sheet， 然后文件流会自动关闭
List<Map<String, Object>> list = new ArrayList();
for (int i=0;i<10;i++){
Map<String, Object> map = new HashMap<>();
map.put("name", "张三"+i);
map.put("number", 5.2+i);
list.add(map);
}
EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(list);
五、下载模板
1、下载数据模板
数据库模板类
@Data
public class DemoEasyExcel {
@ExcelProperty("标题")
private String title;
@ExcelProperty("内容")
private String content;
@ExcelProperty("页码")
private String pageNum;
@ExcelProperty("创建时间")
private String createTime;
}
生成模板数据
private List<DemoEasyExcel> data() {
List<DemoEasyExcel> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DemoEasyExcel data = new DemoEasyExcel();
data.setTitle("标题"+i);
data.setContent("内容" + i);
data.setPageNum("1"+i);
data.setCreateTime(LocalDateTime.now().format( DateTimeFormatter.BASIC_ISO_DATE ));
list.add(data);
}
return list;
}
controller下载模板
/**
* 文件下载（失败了会返回一个有部分数据的Excel）
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoEasyExcel}
* <p>
* 2. 设置返回的 参数
* <p>
* 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
*/
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
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");
EasyExcel.write(response.getOutputStream(), DemoEasyExcel.class).sheet("模板").doWrite(data());
}
/**
* 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
*
* @since 2.1.1
*/
@GetMapping("downloadFailedUsingJson")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
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");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), DemoEasyExcel.class).autoCloseStream(Boolean.FALSE).sheet("模板")
.doWrite(data());
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
2、异常解决方法
调用下载模板的方法，出现异常信息：getOutputStream() has already been called for this response
最后发现是因为使用了aop来打印日志导致的
@Pointcut("execution(* com.vicente.vicenteboot.controller..*.*(..))")
public void log(){
}
@Around("log()")
public Object handlerControllerMethod(ProceedingJoinPoint pjp) {
long startTime = System.currentTimeMillis();
log.info("args:"+ JSON.toJSONString(pjp.getArgs()));
ResultBean<?> result;
try {
result = (ResultBean<?>) pjp.proceed();
log.info(pjp.getSignature() + "use time:" + (System.currentTimeMillis() - startTime));
} catch (Throwable e) {
result = handlerException(pjp, e);
}
return result;
}
解决办法：新建一个新的包excelController，将对应的controller放到这个包中，这样aop就不会执行里面的方法。
