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