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());
}