一,导入到数据库
1.导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
2.准备要上传文件所对应的实体类
@TableName(value ="account")
@Data
public class Account implements Serializable {
@ExcelIgnore
@TableId(type = IdType.AUTO)
private Integer accid;
@ExcelProperty("账户名称")
private String accname;
@ExcelProperty("账户密码")
private String accpass;
@ExcelProperty("状态")
private String accstate;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
}
3.上传
@PostMapping("/upload")
@ResponseBody
public R upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), AccountImportData.class, new AccountDataListener(accountService)).sheet().doRead();
return new R(200,"导入成功",null);
}
4.创建监听器(把服务层或者持久层对象传进去)
@Slf4j
public class AccountDataListener implements ReadListener<AccountImportData> {
private static final int BATCH_COUNT = 100;
private List<Account> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private AccountService accountService;
public AccountDataListener() {
}
public AccountDataListener(AccountService accountService) {
this.accountService = accountService;
}
@Override
public void invoke(AccountImportData data, AnalysisContext context) {
log.info("解析到一条数据:{}", data);
Account account = new Account();
account.setAccstate("0");
BeanUtil.copyProperties(data,account);
cachedDataList.add(account);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
log.info("所有数据解析完成!");
}
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
accountService.saveBatch(cachedDataList);
log.info("存储数据库成功!");
}
}
二,导出到本地
1. 直接实现接口
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
List<Account> list = accountService.queryList();
EasyExcel.write(response.getOutputStream(), Account.class).sheet("模板").doWrite(list);
}