引入依赖
<!--easyexcel,推荐使用2.0 以上版本,功能更加完善-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
创建模板VO类
@ExcelProperty(value = "导出excel表头的标题")
@ExcelIgnore:忽略这个字段,不导出这个字段的数据
@Data
public class UserExcelVo {
@ExcelProperty(value = "姓名")
private String username;
@ExcelProperty(value = "手机号")
private String mobile;
@ExcelProperty(value = "创建时间")
private String saveDate;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
最好都是用String类型接收数据,接收到后另做转换
导出
/**
* 导出用户模板
*
* @param response
* @throws Exception
*/
@RequestMapping(value = "/fileOutExcel", method = {RequestMethod.POST, RequestMethod.GET})
public void fileOutExcel(HttpServletResponse response) throws Exception {
List<UserExcelVo> vos = new ArrayList<>();
UserExcelVo userExcelVo= new UserExcelVo();
userExcelVo.setUserName("张三");
userExcelVo.setMobile("15600000000");
userExcelVo.setSaveDate("2022.02.22 22:22");
vos.add(userExcelVo);
ExcelWrite.webWrite(response, "用户模板", UserExcelVo.class, "sheet1", vos );
}
工具类
public class ExcelWrite {
/**
* 单sheet下载
*
* 文件下载并且失败的时候返回json(默认失败了会返回一个有部分数据的Excel)
*
* @since 2.1.1
*/
public static <T> void webWrite(HttpServletResponse response, String fileName, Class c, String sheetName, List<T> data) throws IOException {
// 这里注意 有反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), c).autoCloseStream(Boolean.FALSE).sheet(sheetName)
.doWrite(data);
} catch (Exception e) {
e.printStackTrace();
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("code", "11");
map.put("msg", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
}