EaseExcel的导入导出
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。我使用的业务场景:需要把一个十万条数据左右的Excel导入数据库,在使用POI的时候,会报OOM,而easyExcel可以避免。参考链接:https://www.yuque.com/easyexcel/doc/write#591ee418
1. 读Excel
1)导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
2)创建接收Excel的实体类UserVo
@Data
public class UserVo {
/**
* 需要在属性上添加@ExcelProperty注解,这个有两种使用方式:
* ①@ExcelProperty(value = "USERID")②@ExcelProperty(index = 0),
* 如果列比较多,且不会出现相同的列名,就使用value,value的值必须和Excel的表头一致,
* 如果列比较少或者有相同的列名,就使用index
*/
// @ExcelProperty(index = 0)
@ExcelProperty(value = "USERID")
private String USERID;
// @ExcelProperty(index = 2)
@ExcelProperty(value = "USERNAME")
private String USERNAME;
// @ExcelProperty(index = 3)
@ExcelProperty(value = "AREAID")
private Double AREAID;
// @ExcelProperty(index = 7)
@ExcelProperty(value = "OPENACCTIME")
private String OPENACCTIME;
// @ExcelProperty(index = 9)
@ExcelProperty(value = "STBID")
private String STBID;
// @ExcelProperty(index = 64)
@ExcelProperty(value = "STATE")
private String STATE;
}
3)创建监听器
/**
* @program:
* @description: 监听批量处理数据
* UserVo和SxuserService 替换成自己的
**/
public class MyExcelListener extends AnalysisEventListener<UserVo> {
private static final Logger LOGGER = LoggerFactory.getLogger(MyExcelListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<UserVo> list = new ArrayList<UserVo>();
private SxuserService sxuserService;
public MyExcelListener() {
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param sxuserService
*/
public MyExcelListener(SxuserService sxuserService) {
this.sxuserService = sxuserService;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(UserVo data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
sxuserService.addUserInfo(list);
LOGGER.info("存储数据库成功!");
}
}
4)web读
@Autowired
private SxuserService sxuserService;
@PostMapping("/import")
public String importUseListener(@RequestParam("file") MultipartFile file) throws Exception {
//headRowNumber(1):如果表头只有一行,可以省略不写,因为headRowNumber默认就是1
EasyExcel.read(file.getInputStream(), UserVo.class, new MyExcelListener(sxuserService)).sheet().headRowNumber(1).doRead();
return "插入成功";
}
2.写Excel
1)导入依赖
2)创建实体类
@Data
public class Sxuser {
/**
* 走过的坑:字段的命名一定要遵循驼峰命名原则,不然实体类属性列有值,但Excel中该列无法进行回显
* ①该属性列首字母不能为大写字母。
* ②该属性列的第二个字母不能为大写。总结来说,即属性名称和该属性的get,set方法都必须遵循驼峰命名格式,
* 不然会导致一些JSON序列化工具和模板标签语言解析不出来,包括导致EaseExcel无法进行解析。
* 错误示例:
* 属性名:fLinkMan(第二个字母大写)
* 可以改为:linkMan
*/
//表头的各种操作,可以参考easyExcel文档:https://www.yuque.com/easyexcel/doc/write#cac25459
@ExcelIgnore //这个注解表示 该属性不需要在Excel中展示
private Integer id;
@ExcelProperty(value = "ServiceId",index = 0) //这个注解中的value用来显示表头,index表示字段在Excel中的哪一列
private String serviceId;
@ExcelProperty(value = "BoxCode",index = 1)
private String boxCode;
@ExcelProperty(value = "AcceptCity",index = 2)
private String acceptCity;
@ExcelProperty(value = "FLinkMan",index = 3)
private String finkMan;
@ExcelProperty(value = "ContactPhone",index = 4)
private String contactPhone;
@ExcelProperty(value = "ContactAddress",index = 5)
private String contactAddress;
@ExcelProperty(value = "OperCode",index = 6)
private Integer operCode;
@ExcelProperty(value = "OldBosCode",index = 7)
private String oldBosCode;
@ExcelProperty(value = "ProdId",index = 8)
private String prodId;
@ExcelProperty(value = "CreateTime",index = 9)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss") //通过这个注解可以转换需要的日期格式
private Date createtime;
}
3)web写
@Autowired
private SxuserService sxuserService;
@GetMapping("downloadFailedUsingJson")
public void downloadFailedUsingJson(@RequestParam(value = "year",required = false) Integer year,HttpServletResponse response) throws IOException {
// 这里注意 使用swagger 会导致各种问题,请直接用浏览器或者用postman
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("文件名", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), Sxuser.class).autoCloseStream(Boolean.FALSE).sheet("sheet名")
.doWrite(sxuserService.getAllData(year));
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}