EasyExcel相较于传统Excel操作或者解析都是利用Apach POI进行操作,EasyExcel更加便捷,少了很多繁琐步骤
https://easyexcel.opensource.alibaba.com/docs/current/
依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
/**
* 基础数据类
*
* @author Jiaju Zhuang
**/
@Getter
@Setter
@EqualsAndHashCode
public class DownloadData {
//value 对应标题 index 对应列的顺序 一般用在文件下载上
@ExcelProperty(value = "字符串标题",index =0)
private String string;
@ExcelProperty(value = "日期标题",index =1)
private Date date;
@ExcelProperty(value = "数字标题",index =2)
private Double doubleData;
// @ExcelIgnore 注解用来注释需要忽略类中不需要处理的字段
@ExcelIgnore
private String test;
}
文件下载
/**
* 文件下载(失败了会返回一个有部分数据的Excel)
* <p>
* 1. 创建excel对应的实体对象 参照{@link DownloadData}
* <p>
* 2. 设置返回的 参数
* <p>
* 3. 直接写,这里注意,finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
*/
@PostMapping("/exportDetail")
public void exportDetail(HttpServletResponse response) throws LogicalException, IOException {
//查询需要导出的数据集合
List<DownloadData> list = tenantCarService.exportDetail();
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
try {
//设置头居中
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//设置内容居中
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("文件名称", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), DownloadData .class)
.autoCloseStream(Boolean.FALSE)
.registerWriteHandler(horizontalCellStyleStrategy)
.sheet("模板")
//要导出的数据集合
.doWrite(list);
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
文件上传
/**
* 基础数据类
*
* @author Jiaju Zhuang
**/
@Getter
@Setter
@EqualsAndHashCode
public class UploadData {
//value 对应标题 index 对应列的顺序 一般用在文件下载上
@ExcelProperty(value = "字符串标题",index =0)
private String string;
@ExcelProperty(value = "日期标题",index =1)
private Date date;
@ExcelProperty(value = "数字标题",index =2)
private Double doubleData;
}
/**
* 文件上传
* <p>
* 1. 创建excel对应的实体对象 参照{@link UploadData}
* <p>
* 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link UploadDataListener}
* <p>
* 3. 直接读即可
*/
@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
List<UploadData> list = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UploadData.class).sheet().doReadSync();
//之后做入库操作
// .....
return "success";
}