easyExcel用于导入导出
1、添加依赖：
<!-- 现在已经更新到1.1.2-beta5 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.1</version>
</dependency>
导入：
2、添加监听：
package com.aikucun.goods.biz.easyexcel; import com.aikucun.goods.dao.model.vo.SkuModel; import com.aikucun.goods.dao.model.vo.SkuUploadFailModel; import com.aikucun.sc.common.utils.BeanUtils; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.google.common.collect.Lists; import org.apache.commons.lang3.StringUtils; import java.util.List; public class SkuUploadListener extends AnalysisEventListener { private List<SkuUploadFailModel> uploadFailList = Lists.newArrayList(); private List<SkuModel> skuModelList = Lists.newArrayList(); private int totalSize = 0; /** * 每解析一行，执行一次该方法 */ @Override public void invoke(Object object, AnalysisContext context) { totalSize++; SkuModel skuModel = (SkuModel) object; if (!checkData(skuModel)) { return; } skuModelList.add(skuModel); } @Override public void doAfterAllAnalysed(AnalysisContext context) { } private boolean checkData(SkuModel skuModel) { // 失败原因 String failMessage = ""; // itemCode String itemCode = skuModel.getItemCode(); if (null == skuModel) { failMessage = "数据为空"; } // 验证sku if (StringUtils.isEmpty(itemCode)) { failMessage = "itemCode为空!"; } else { if (skuModelList.contains(itemCode)) { failMessage = failMessage + "itemCode重复!"; } } //品牌名称 if (StringUtils.isEmpty(skuModel.getBrandName())) { failMessage = failMessage + "品牌名称为空！"; } //条码必填 if (StringUtils.isEmpty(skuModel.getBarCode())) { failMessage = failMessage + "条码为空！"; } if (StringUtils.isNotEmpty(failMessage)) { SkuUploadFailModel failModel = new SkuUploadFailModel(); //数量校验？？？ BeanUtils.convert(skuModel, failModel); failModel.setFailMessage(failMessage); uploadFailList.add(failModel); return false; } return true; } public List<SkuUploadFailModel> getUploadFailList(){ return uploadFailList; } public List<SkuModel> getSkuModelList(){ return skuModelList; } public int getTotalSize() { return totalSize; } }
3、导入商品controller：
@PostMapping("/import-sku-list-async")
@ApiOperation("批量导入商品（异步）")
public Result importSkuListAsync(@RequestBody MultipartFile file) {
return skuService.importSkuListAsync(file);
}
4、ServiceImpl
@Override public Result importSkuListAsync(MultipartFile file) { //1.参数校验 if (file == null || file.isEmpty()) { throw new GoodsException("导入文件为空"); } // 判断文件格式 String filename = file.getOriginalFilename(); String suffixName = filename.substring(filename.indexOf(".")); if (!".xlsx".equalsIgnoreCase(suffixName) && !".xls".equalsIgnoreCase(suffixName)) { throw new GoodsException("文件格式要求:.xlsx/.xls"); } dealDataAsync(file, suffixName); return Result.success(); } /** * 异步处理excel数据校验、落库、上传文件服务器等 * * @param file */ @Async public void dealDataAsync(MultipartFile file, String suffixName) { InputStream inputStream = null; try { inputStream = file.getInputStream(); } catch (Exception e) { e.printStackTrace(); } //1.注册任务 String dataFlag = System.currentTimeMillis() + ""; // 任务注册 Long id = reg("{\"service\":\"goods-web\"}", getUserName(), dataFlag, getRealName(), DataFileTaskTypeEnum.IMPORT.getType()); if (id == null) { log.error("【导入商品】 uploadSkuFile 数据版本号:" + dataFlag + ", 注册导入任务失败"); return; } //2.excel数据校验 SkuUploadListener listener = new SkuUploadListener(); dealExcel(listener, suffixName, id, dataFlag, inputStream); //3.把错误数据分装集合中，正确数据封装集合中 // 验证失败的数据 List<SkuUploadFailModel> uploadFailList = listener.getUploadFailList(); // 验证通过的数据 List<SkuModel> skuModeList = listener.getSkuModelList(); // 总数量 int totalSize = listener.getTotalSize(); //4.把成功集合插入数据库，错误数据上传文件服务器 try { for (SkuModel skuModel : skuModeList) { Sku sku = new Sku(); BeanUtils.copyProperties(skuModel, sku); saveOrUpdateSku(sku); } }catch (Exception e){ log.error("【导入商品】 数据版本号：{},保存采购单报错:{},错误详情：{}", dataFlag, e.getMessage(), e); finish(id, DataFileTaskStatusEnum.FAIL.getType(), "", totalSize, 0, "保存到数据库报错", dataFlag); return; } //5.完成任务(上传失败数据到文件服务器) // 导入状态 int status = DataFileTaskStatusEnum.SUCCESS.getType(); String fileUrl = ""; // 导入失败的数据，生成异常文件 if (!CollectionUtils.isEmpty(uploadFailList)) { if (CollectionUtils.isEmpty(skuModeList)) { status = DataFileTaskStatusEnum.FAIL.getType(); } else { status = DataFileTaskStatusEnum.PART_SUCCESS.getType(); } fileUrl = createErrFile(uploadFailList, dataFlag); } finish(id, status, fileUrl, totalSize, skuModeList.size(), "导入结束", dataFlag); } private void dealExcel(SkuUploadListener listener, String suffixName, Long id, String dataFlag, InputStream file) { ExcelTypeEnum excelTypeEnum; if (ExcelTypeEnum.XLSX.getValue().equalsIgnoreCase(suffixName)) { excelTypeEnum = ExcelTypeEnum.XLSX; } else if (ExcelTypeEnum.XLS.getValue().equalsIgnoreCase(suffixName)) { excelTypeEnum = ExcelTypeEnum.XLS; } else { log.error("【导入采购单】 uploadPurchaseFile 数据版本号：" + dataFlag + ",上传文件格式不是 " + ExcelTypeEnum.XLSX.getValue() + "/" + ExcelTypeEnum.XLS.getValue()); finish(id, DataFileTaskStatusEnum.FAIL.getType(), "", 0, 0, "文件格式不正确", dataFlag); return; } // 解析文件 try { ExcelReader excelReader = new ExcelReader(file, excelTypeEnum, null, listener); excelReader.read(new Sheet(1, 1, SkuModel.class)); } catch (Exception e) { log.error("【导入采购单】 uploadPurchaseFile 数据版本号：{},解析文件报错:{},错误详情：{}", dataFlag, e.getMessage(), e); finish(id, DataFileTaskStatusEnum.FAIL.getType(), "", 0, 0, "解析文件报错", dataFlag); return; } } /** * 导入商品，异常文件生成 * * @param modelList * @param dataFlag */ private String createErrFile(List<SkuUploadFailModel> modelList, String dataFlag) { // 生成文件类型 ByteArrayOutputStream out = null; String fileUrl = ""; try { out = new ByteArrayOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); if (modelList.size() > FILE_SIZE) { List<List<SkuUploadFailModel>> splitList = Lists.partition(modelList, FILE_SIZE); for (int i = 0; i < splitList.size(); i++) { //写一个sheet, Sheet sheet = new Sheet(i + 1, 0, SkuUploadFailModel.class); writer.write(splitList.get(i), sheet); } } else { //写一个sheet, Sheet sheet = new Sheet(1, 0, SkuUploadFailModel.class); writer.write(modelList, sheet); } writer.finish(); CloudStorageService oss = oSSFactory.build(); String path = oss.getDefaultPath("/导入商品异常反馈.xlsx"); fileUrl = oss.upload((out).toByteArray(), path); log.info("【导入商品】 createErrFile 数据版本号:" + dataFlag + ",生成文件url:" + fileUrl); } catch (Exception e) { log.error("【导入商品】 createErrFile 数据版本号:" + dataFlag + ",导出报错: " + e.getMessage(), e); } finally { try { if (out != null) { out.close(); } } catch (Exception e) { e.printStackTrace(); } } return fileUrl; } private Long reg(String param, String userName, String dataFlag, String realName, Integer type) { DataFileTaskDTO dataFileTaskDTO = new DataFileTaskDTO(); dataFileTaskDTO.setParams(param); dataFileTaskDTO.setDataType(SystemModuleEnum.DOWNLOAD_GOODS.getType()); dataFileTaskDTO.setType(type); dataFileTaskDTO.setRemark(SystemModuleEnum.DOWNLOAD_GOODS.getTypeName() + ",数据版本号：" + dataFlag); dataFileTaskDTO.setSource(SystemModuleEnum.DOWNLOAD_GOODS.getSystem()); dataFileTaskDTO.setCreateUser(realName); dataFileTaskDTO.setCreateUserJobNumber(userName); log.info("【导入商品】推送到磐石注册任务参数,dataFileTaskDTO:{}", JSONObject.toJSONString(dataFileTaskDTO)); Result ret = remote.reg(dataFileTaskDTO); log.info("【导入商品】推送到磐石注册任务返回结果,ret:{}", JSONObject.toJSONString(ret)); if (!CheckUtils.isNull(ret) && ret.isSuccess() && ret.getData() != null) { return Long.parseLong( ret.getData().toString()); } return null; } private Long finish(Long id, Integer taskStatus, String fileUrl, Integer total, Integer successTotal, String remark, String dataFlag) { DataFileTaskDTO taskDTO = new DataFileTaskDTO(); taskDTO.setId(id); taskDTO.setTaskStatus(taskStatus); taskDTO.setFileUrl(fileUrl); taskDTO.setTotal(total == null ? 0 : total); taskDTO.setSuccessTotal(successTotal == null ? 0 : successTotal); taskDTO.setRemark(remark + "，数据版本号:" + dataFlag); log.info("【导入商品】推送到磐石完成任务参数,dataFileTaskDTO:{}", JSONObject.toJSONString(taskDTO)); Result ret = remote.finsh(taskDTO); log.info("【导入商品】推送到磐石完成任务返回结果,ret:{}", JSONObject.toJSONString(ret)); if (!CheckUtils.isNull(ret) && ret.isSuccess()) { return (Long) ret.getData(); } return null; } /** * 获取用户名 * * @return */ private String getUserName() { // 获取当前用户 String userName = ""; LoginUserVo loginUserVo = UserVoThreadLocal.get(); if (Objects.nonNull(loginUserVo)) { userName = loginUserVo.getUserName(); } return userName; } /** * 获取真实姓名 * * @return */ private String getRealName() { // 获取当前用户 String realName = ""; LoginUserVo loginUserVo = UserVoThreadLocal.get(); if (Objects.nonNull(loginUserVo)) { realName = loginUserVo.getRealName(); } return realName; }
导出：
1、导出controller
/** * 导出维护记录 * @param vo */ @PostMapping("/download") @ApiOperation(value = "采购单下载") public Result<String> exportRecord(@RequestBody PurchaseOrderHeadVO vo) { purchaseManageService.exportRecord(vo); return Result.success("导出成功"); }
2、serviceImpl
@Async public void createRecordFile(PurchaseOrderHeadVO vo, String currentUserName, String realName,Long id,String dataFlag) { log.info("【导出采购单】 createRecordFile 开始，参数：{}，用户:{},开始时间：{} ,数据版本号:{}", JSON.toJSONString(vo), currentUserName, System.currentTimeMillis(), dataFlag); // 任务注册 if (id == null) { return; } // 生成文件类型 ByteArrayOutputStream out = null; Map<String, Object> map = getStringObjectMap(vo); // 根据条件查询 List<PurchaseOrderModel> list = purchaseOrderExtendMapper.listForEceport(map); if (CollectionUtils.isEmpty(list)) { log.info("【导出采购单】 createRecordFile 未查询到需要导出的数据,数据版本号:" + dataFlag); finish(id, DataFileTaskStatusEnum.FAIL.getType(), "", 0, 0, "查询数据为空", dataFlag); return; } try { // 批量生成文件，每个文件数据，最多 6万 List<PurchaseDownloadModel> modelList = new ArrayList<>(); for (PurchaseOrderModel entity : list) { PurchaseDownloadModel model = new PurchaseDownloadModel(); BeanUtils.copyProperties(entity, model); // 采购模型 model.setPurchaseMode(PurchaseModeDictEnum.getNameByCode(model.getPurchaseMode())); // 采购类型 model.setPurchaseType(PurchaseTypeDictEnum.getNameByCode(model.getPurchaseType())); // 采购状态 model.setStatus(PurchaseStatusEnum.getNameByCode(model.getStatus())); // 业务线 model.setBusinessLine(PurchaseBusinessDictEnum.getNameByCode(model.getBusinessLine())); modelList.add(model); } out = new ByteArrayOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); if (modelList.size() > FILE_SIZE) { List<List<PurchaseDownloadModel>> splitList = Lists.partition(modelList, FILE_SIZE); for (int i = 0; i < splitList.size(); i++) { //写一个sheet, Sheet sheet = new Sheet(i + 1, 0, PurchaseDownloadModel.class); writer.write(splitList.get(i), sheet); } } else { //写一个sheet, Sheet sheet = new Sheet(1, 0, PurchaseDownloadModel.class); writer.write(modelList, sheet); } writer.finish(); CloudStorageService oss = oSSFactory.build(); String path = oss.getDefaultPath("/采购单导出.xlsx"); String url = oss.upload((out).toByteArray(), path); finish(id, DataFileTaskStatusEnum.SUCCESS.getType(), url, list.size(), list.size(), "导出成功", dataFlag); log.info("【导出采购单】 createRecordFile 数据版本号:" + dataFlag + ",生成文件url:" + url); } catch (Exception e) { log.error("【导出采购单】 createRecordFile 数据版本号:" + dataFlag + ",导出报错: " + e.getMessage(), e); // 发生异常，删除文件 finish(id, DataFileTaskStatusEnum.FAIL.getType(), "", list.size(), 0, "导出报错，数版本号:" + dataFlag, dataFlag); } finally { try { if (out != null) { out.close(); } } catch (Exception e) { e.printStackTrace(); } } log.info("【导出采购单】 createRecordFile 数据版本号:" + dataFlag + ",结束,时间:" + System.currentTimeMillis()); }
另外建议参考：https://github.com/HowieYuan/easyexcel-encapsulation
