完整版EXCEL导入（大框架SpringCloud 主要业务逻辑还是SpringBoot一套）

也是EasyPoi

首先引入jar包

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;

<!-- excel -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>${easypoi.version}</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>${easypoi.version}</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>${easypoi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>
1.service接口层代码

/**
 * 导入模板
 * @param allStaffWorkersParamsList
 * @param sysId
 * @param userId
 * @return
 * @throws Exception
 */
Map<String,String> importAllStaffWorkers(List<AllStaffWorkersParams> allStaffWorkersParamsList,Long sysId,Long userId) throws Exception;

2.serviceImpl 实现类

@Override
@Transactional
public Map<String, String> importAllStaffWorkers(List<AllStaffWorkersParams> allStaffWorkersParamsList, Long sysId, Long userId) throws Exception {
    Map<String,String> map = new HashMap<String, String>();
    if (null == allStaffWorkersParamsList || allStaffWorkersParamsList.isEmpty()){
        errLog("importAllStaffWorkers", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "Excel为空");
        throw new AttemptException(ErrorCodeMsg.PARAMS_ISNULL_ERROR,"Excel为空");
    }
    int successNum = 0;
    int failureNum = 0;
    StringBuilder successMsg = new StringBuilder();
    StringBuilder failureMsg = new StringBuilder();
    for (int i =0;i<allStaffWorkersParamsList.size();i++){
        AllStaffWorkersParams allStaffWorkersParams = allStaffWorkersParamsList.get(i);
        if (allStaffWorkersParams == null){
            continue;
        }try {
        try {
            validImportStuInfo(allStaffWorkersParams);
        }catch (RuntimeException e){
            failureNum++;
            failureMsg.append("<br/>" + failureNum + "、第" + (i + 2) + "行,错误原因： " + e.getMessage());
            errLog("importAllStaffWorkers", ErrorCodeMsg.PARAM_ERROR.getMessage(), "参数错误");
            continue;
        }
        String workerName = allStaffWorkersParams.getWorkerName();
        AllStaffWorkers allStaffWorkers = new AllStaffWorkers();
        allStaffWorkers.setWorkerName(workerName);
        allStaffWorkers.setSysId(sysId);
        //未删除的
        allStaffWorkers.setIsDeleted(CmnConstants.IS_DELETED_NO);
        //有效的
        allStaffWorkers.setIsEnabled(CmnConstants.IS_ENABLED_YES);
        AllStaffWorkers allStaffWorkersDB = allStaffWorkersMapper.selectOne(allStaffWorkers);
        if (null != allStaffWorkersDB){
            failureNum++;
            failureMsg.append("<br/>" + failureNum + "、第" + (i + 2) + "行,错误原因： 员工" + workerName + "已存在");
            continue;
        }
        Example example = new Example(DictComm.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("isEnabled",CmnConstants.IS_ENABLED_YES);
        criteria.andEqualTo("isDeleted",CmnConstants.IS_DELETED_NO);
        criteria.andEqualTo("dictName",allStaffWorkersParams.getDictName());
        List<DictComm> dictCommList = dictCommMapper.selectByExample(example);
        if (dictCommList != null || dictCommList.size() != 0){

        AllStaffWorkers allStaffWorkers1 = new AllStaffWorkers();
        allStaffWorkers1.setWorkerName(allStaffWorkersParams.getWorkerName());
        allStaffWorkers1.setWorkerPhone(allStaffWorkersParams.getWorkerPhone());
        allStaffWorkers1.setWorkerCardNum(allStaffWorkersParams.getWorkerCardNum());
        allStaffWorkers1.setWorkerSex(allStaffWorkersParams.getWorkerSex());
        allStaffWorkers1.setSysId(sysId);
        allStaffWorkers1.setCreatedBy(userId);
        allStaffWorkers1.setCreatedTm(new Date());
        int num = allStaffWorkersMapper.insertSelective(allStaffWorkers1);

        AllStaffWorkerType allStaffWorkerType = new AllStaffWorkerType();
        allStaffWorkerType.setWorkerTypeName(allStaffWorkersParams.getWorkerTypeName());
        allStaffWorkerType.setSysId(sysId);
        allStaffWorkerType.setCreatedBy(userId);
        allStaffWorkerType.setCreatedTm(new Date());
        int b = allStaffWorkerTypeMapper.insertSelective(allStaffWorkerType);

        AllStaffOrg allStaffOrg = new AllStaffOrg();
        allStaffOrg.setOrgCode(allStaffWorkersParams.getOrgCode());
        allStaffOrg.setSysId(sysId);
        allStaffOrg.setCreatedBy(userId);
        allStaffOrg.setCreatedTm(new Date());
        int c = allStaffOrgMapper.insertSelective(allStaffOrg);
        if (num > 0 || c > 0 || b > 0){
            successNum++;
        }else {
            failureNum++;
            failureMsg.append("<br/>" + failureNum + "、第" + (i + 2) + "行,错误原因： 添加失败");
            continue;
        }
     }else {
            errLog("importAllStaffWorkers",ErrorCodeMsg.PARAM_ERROR.getMessage(),"状态错误，没有符合的状态");
            throw new RuntimeException("状态错误，没有符合的状态");
        }
    }catch (Exception e) {
            failureNum++;
            String msg = "<br/>" + failureNum + "、第" + (i + 2) + "行,导入失败：";
            failureMsg.append(msg + e.getMessage());
            errLog("importAllStaffWorkers", ErrorCodeMsg.SERVER_ERROR.getMessage(), "服务器错误");
        }
    }
    if (failureNum > 0) {
        failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
        map.put("data", "1");
        map.put("msg", failureMsg.toString());
        throw new Exception(failureMsg.toString());
    } else {
        successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条");
        map.put("data", "0");
        map.put("msg", successMsg.toString());
    }
    return map;
}
private void validImportStuInfo(AllStaffWorkersParams allStaffWorkersParams)throws Exception{
   //员工姓名
    if (StringUtils.isBlank(allStaffWorkersParams.getWorkerName())){
        errLog("validImportStuInfo", ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(), "员工姓名参数为空");
        throw new RuntimeException("员工姓名不能为空!");
    }
    //员工手机号
    if (StringUtils.isBlank(allStaffWorkersParams.getWorkerPhone())){
        errLog("validImportStuInfo",ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(),"员工手机号参数为空");
        throw new RuntimeException("员工手机号不能为空");
    }
    //员工身份证号
    if(StringUtils.isBlank(allStaffWorkersParams.getWorkerCardNum())){
        errLog("validImportStuInfo",ErrorCodeMsg.PARAMS_ISNULL_ERROR.getMessage(),"员工身份证号为空");
        throw new RuntimeException("员工身份证号为空");
    }
}
3.controller层代码

/**
 *导入
 * @param requestUser
 * @return
 * @throws Exception
 * @autho司
 */
@PostMapping("/importAllStaffWorkers")
public ResponseStruct importAllStaffWorkers(@ModelAttribute RequestUser requestUser)throws Exception{
    Long userId = requestUser.getGuid();
    Long sysId = requestUser.getSysid();
    ImportParams params = new ImportParams();
    params.setSaveUrl("/file");
    String filePath="http://dev.file.aq-100.com:80/group1/M00/00/03/wKgCt15eD2eAO2oTAABKAO6n4Rs15.xlsx";
    OkHttpUtil.download(filePath,"D://","员工2.xlsx");
    InputStream in = new FileInputStream(new File("D://员工2.xlsx"));
    List<AllStaffWorkersParams> allStaffWorkersParamsList = ExcelImportUtil.importExcel(in,AllStaffWorkersParams.class,params);
    return ResponseStruct.success(this.allStaffWorkersInterface.importAllStaffWorkers(allStaffWorkersParamsList,sysId,userId));
}