esayExcel导入示例

导入依赖

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>1.1.1</version>
    </dependency>

@ApiOperation(value = "导入审核员积分")
    @RequestMapping(value = "/importAuditScore", method = RequestMethod.POST)
    public ObjectRestResponse<String> importAuditScore(MultipartFile file) throws Exception{
        if (file == null) {
            throw new IOException("导入文件不能为空");
        }
        AuditScoreExcelListen auditScoreExcelListen = SpringUtil.getBean(AuditScoreExcelListen.class);
        EasyExcel.read(file.getInputStream(), AuditExcelScoreEntity.class,auditScoreExcelListen).sheet().doRead();
        return new ObjectRestResponse<>(auditScoreExcelListen.getMsg());
    }

package com.hikvision.sbm.comflowservice.bulletinboard.auditscore.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONObject;
import com.hikvision.sbm.comflowservice.bulletinboard.auditscore.entity.AuditScorePersonEntity;
import com.hikvision.sbm.comflowservice.bulletinboard.auditscore.service.AuditScorePersonService;
import com.hikvision.sbm.comflowservice.bulletinboard.excel.ExcelConstants;
import com.hikvision.sbm.comflowservice.common.helper.userhelper.UserManager;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Component
public class AuditScoreExcelListen extends AnalysisEventListener<AuditExcelScoreEntity> {
    @Autowired
    AuditScorePersonService auditScorePersonService;

    @Autowired
    private UserManager userManager;

    public List<AuditExcelScoreEntity> auditExcelScoreEntities = new ArrayList<>();

    public List<String> checkFailNameList = new ArrayList<>();

    private String msg = ExcelConstants.DEFAULT_MSG;

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    @Override
    public void invoke(AuditExcelScoreEntity data, AnalysisContext context) {
        log.info("获取导入数据:{}", JSONObject.toJSONString(data));
        // 校验审核员
        if(userManager.getByNotesName(data.getAuditPerson()) == null || StringUtils.isEmpty(userManager.getByNotesName(data.getAuditPerson()).getEmpId())) {
            checkFailNameList.add(data.getAuditPerson());
            return;
        }
        auditExcelScoreEntities.add(data);
    }

    /**
     * 所有excel 导入完成后处理
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        auditExcelScoreEntities.forEach(auditExcelScoreEntity -> {
            AuditScorePersonEntity auditScorePersonEntity = new AuditScorePersonEntity();
            auditScorePersonEntity.setScore(auditExcelScoreEntity.getScore());
            auditScorePersonEntity.setAuditPerson(auditExcelScoreEntity.getAuditPerson());
            auditScorePersonService.updateAuditScore(auditScorePersonEntity);
        });
        log.info("一共获取导入数据:{}条", auditExcelScoreEntities.size());
        log.warn("审核员校验失败审核员:{}" , checkFailNameList);
        if(checkFailNameList.size() > 0) {
            this.msg = checkFailNameList + ExcelConstants.PERSON_CHECK_FAIL_MSG;
        }
        auditExcelScoreEntities = new ArrayList<>();
        checkFailNameList = new ArrayList<>();
    }

}