准备工作
查询结果类:ProjectVo
@Data
public class ProjectVo {
@ExcelProperty(value = "ID")
private Integer id;
@ExcelProperty(value = "项目代码")
private String code;
@ExcelProperty(value = "项目名称")
private String name;
@ExcelProperty(value = "token")
private String tokenStr;
@ExcelProperty(value = "描述信息")
private String remark;
}
复杂版本:Hutool
@ApiOperation(value = "32、export导出")
@GetMapping(value = "/exportExcel", produces = "application/octet-stream")
public void exportExcel(ProjectQuery query, HttpServletResponse response) throws IOException {
// 查询数据,正常的SpringBoot项目的分页查询操作
// 自己进行测试时可以采用自己构造一个List<Object>用来模拟数据库读出情况
List<ProjectVo> voList = this.projectService.listForExport(query);
List<Map<String, Object>> list = new ArrayList<>(voList.size());
for (ProjectVo projectVo : voList) {
Map<String, Object> row = new HashMap<>();
row.put("项目名称", projectVo.getName());
row.put("项目代码", projectVo.getCode());
row.put("ID", projectVo.getId());
row.put("tokenStr", projectVo.getTokenStr());
row.put("REMARK", projectVo.getRemark());
list.add(row);
}
ExcelWriter writer = ExcelUtil.getWriter(true);
//下面这五行其实可以不加,加了是为了确定导出后的Excel表的每一列的顺序
//否则可能会出现id在第五列这种情况
writer.addHeaderAlias("ID", "ID");
writer.addHeaderAlias("项目代码", "项目代码");
writer.addHeaderAlias("项目名称", "项目名称");
writer.addHeaderAlias("tokenStr", "tokenStr");
writer.addHeaderAlias("REMARK", "REMARK");
writer.write(list, true);
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=projectlist.xlsx");
ServletOutputStream outputStream = response.getOutputStream();
writer.flush(outputStream, true);
writer.close();
IoUtil.close(System.out);
}
简单版本:EasyExcel
是真的方便多了若要给每一行重命名则只需要在ProjectVo的数据上面加入@ExcelProperty(value = "自定义的名称")
@ApiOperation(value = "33、export导出")
@GetMapping(value = "/exportExcelNew", produces = "application/octet-stream")
public void exportExcelNew(ProjectQuery query, HttpServletResponse response) throws IOException {
// 查询数据
List<ProjectVo> voList = this.projectService.listForExport(query);
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
// String fileName= URLEncoder.encode("测试","UTF-8").replace("\\+","%20");
//上面那句话是用来进行中文表格命名的,由于最终没有使用,所以把它注释掉了
response.setHeader("Content-Disposition", "attachment;filename=projecttest.xlsx");
EasyExcel.write(response.getOutputStream()).head(ProjectVo.class).excelType(ExcelTypeEnum.XLSX).sheet("测试")
.doWrite(voList);
}
注意事项:
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=projectlist.xlsx");
这两句话一定要加,否则会报乱码setContentType可以上网搜一搜,有更适合xlsx的contentType
最后的Excel结果如图所示