EasyExcel导出到多个sheet 学习笔记
基本流程
设置响应头
首先定义文件名,对响应请求设置contentType
、 CharacterEncoding
和header
String fileName = "学生信息列表" + System.currentTimeMillis() + ".xlsx";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8"));
创建Excel写入对象
对象创建时要注意导出位置以及导出模板类型
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), SysStudent.class).build();
循环写入多个Sheet
分别在每次循环中获得对应的数据,并写入sheet里
这里要注意,这个例子中是一个对象。如果要在不同的sheet中导入不同对象则需要在easyexcel.head()
修改模板
for (int i = 0; i < 2; i++) {
List<SysStudent> list = sysStudentService.list(Wrappers.<SysStudent>lambdaQuery()
.eq(SysStudent::getStudentSex, i));
WriteSheet writeSheet = EasyExcel.writerSheet(i == 0 ? "男" : "女").head(SysStudent.class).build();
excelWriter.write(list, writeSheet);
}
完成写操作
调用finish
方法完成写操作并关闭资源,如不关闭则可能导致数据无法完全写入excel中
excelWriter.write(list, writeSheet);
例子
导出所有学生信息到一个excel,但是将男生和女生分别放入一个sheet
@PostMapping("/exportDetail")
@ResponseBody
public void exportDetail(HttpServletResponse response) throws IOException {
// 定义文件名
String fileName = "学生信息列表" + System.currentTimeMillis() + ".xlsx";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''"
+ URLEncoder.encode(fileName, "UTF-8"));
+
// 创建excel对象
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), SysStudent.class).build();
//创建sheet
WriteSheet writeSheet;
// 每次循环获取一次数据,再执行写入
for (int i = 0; i < 2; i++){
List<SysStudent> list = sysStudentService.list(Wrappers.<SysStudent>lambdaQuery()
.eq(SysStudent::getStudentSex, i));
writeSheet = EasyExcel.writerSheet(i == 0 ? "男" : "女").head(SysStudent.class).build();
excelWriter.write(list, writeSheet);
}
excelWriter.finish();
}