阿里巴巴开源了一个文件导出的工具easyexcel,可以做文件的上传和下载,简化了复杂的编码工作,下面介绍使如何使用easyexcel下载excel。
一、引入maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beat1</version>
</dependency>
二、定义excel表单内容实体
- 实体继承BaseRowModel
- 属性增加@ExcelProperty(value = "ID", index = 0)注解,value是excel文件的表头,index是列的序号
示例如下:
@Getter
@Setter
@ToString
public class QuestionExcelData extends BaseRowModel {
@ExcelProperty(value = "ID", index = 0)
private Long id;
/**
* 模块
*/
@ExcelProperty(value = "模块", index = 1)
private String system;
/**
* 具体问题
*/
@ExcelProperty(value = "反馈问题", index = 2)
private String question;
/**
* 问题是否已解决
*/
@ExcelProperty(value = "是否修复", index = 4)
private String repaired;
}
三、controller层响应流式下载
@RequestMapping("/export")
public void exporExcel() throws IOException {
log.info("反馈问题导出 -> 开始");
ExcelWriter writer = null;
OutputStream outputStream = response.getOutputStream();
try {
//添加响应头信息
String fileName = "商羚反馈问题列表-" + LocalDate.now().format(DateTimeFormatter.BASIC_ISO_DATE) +".xls";
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes(StandardCharsets.UTF_8),"ISO8859-1"));
response.setContentType("application/msexcel;charset=UTF-8");//设置类型
response.setHeader("Pragma", "No-cache");//设置头
response.setHeader("Cache-Control", "no-cache");//设置头
response.setDateHeader("Expires", 0);//设置日期头
//实例化 ExcelWriter
writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLS, true);
//实例化表单
Sheet sheet = new Sheet(1, 0, QuestionExcelData.class);
sheet.setSheetName("反馈问题");
//获取数据
List<QuestionExcelData> questionExcelDataList = fetchExcelData();
//输出
writer.write(questionExcelDataList, sheet);
writer.finish();
outputStream.flush();
log.info("反馈问题导出 -> 完成");
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
response.getOutputStream().close();
} catch (IOException e) {
e.printStackTrace();
//TODO 异常处理
}
}
}
/**
* 获取填充数据
* @return
*/
private List<QuestionExcelData> fetchExcelData() {
List<QuestionVO> dutyQuestionList = questionService.queryQuestion(null);
List<QuestionExcelData> questionExcelDataList = BeanCopierUtil.convertList(dutyQuestionList, QuestionExcelData.class);
log.info("反馈问题导出 -> 查询到数据:{}", JSON.toJSONString(questionExcelDataList));
return questionExcelDataList;
}