Java实现EasyExcel导入

 依赖

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

 项目结构

 

 excel表头对应实体类

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@ApiModel("销售人员上传VO")
@Data
public class SalesmanUploadVO {

    @ExcelProperty(value = "所属销售团队", index = 0)
    @ApiModelProperty("所属销售团队")
    private String team;

    @ApiModelProperty("所属销售团队编号")
    private String teamCode;

    @ExcelProperty(value = "销售角色", index = 1)
    @ApiModelProperty("销售角色")
    private String role;

    @ApiModelProperty("销售角色编号")
    private String roleCode;

    @ExcelProperty(value = "销售人员姓名", index = 2)
    @ApiModelProperty("销售人员姓名")
    private String name;

    @ExcelProperty(value = "销售人员手机号", index = 3)
    @ApiModelProperty("销售人员手机号")
    private String phone;

}

EasyExcel导入监听类，用于EasyExcel在读取数据时进行相关操作

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.slt.exception.BusinessException;
import com.slt.model.salesman.vo.SalesmanUploadVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


/**
 * @Author: CQian
 * @Date: 2024/2/28 16:10
 */

@Slf4j
public class SalesmanListener extends AnalysisEventListener<SalesmanUploadVO> {

    List<SalesmanUploadVO> list = new ArrayList<>();

    /**
     * 在这里进行模板的判断
     *
     * @param headMap 存放着导入表格的表头，键是索引，值是名称
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        /*
        验证表头：
        count 记录模板表头有几个，用以判断用户导入的表格是否和模板完全一致
        如果用户导入表格较模板的表头多，但其余符合模板，这样不影响则不需要
         */
        int count = 0;
        // 获取数据实体的字段列表
        Field[] fields = SalesmanUploadVO.class.getDeclaredFields();
        // 遍历字段进行判断
        for (Field field : fields) {
            // 获取当前字段上的ExcelProperty注解信息
            ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class);
            // 判断当前字段上是否存在ExcelProperty注解
            if (fieldAnnotation != null) {
                ++count;
                // 存在ExcelProperty注解则根据注解的index索引到表头中获取对应的表头名
                String headName = headMap.get(fieldAnnotation.index());
                // 判断表头是否为空或是否和当前字段设置的表头名不相同
                if (StringUtils.isEmpty(headName) || !headName.equals(fieldAnnotation.value()[0])) {
                    // 如果为空或不相同，则抛出异常不再往下执行
                    log.error("表头格式不正确，请下载模板进行参考");
                    throw new BusinessException("表头格式不正确，请下载模板进行参考");
                }
            }
        }

        // 判断用户导入表格的标题头是否完全符合模板
        if (count != headMap.size()) {
            log.error("表头格式不正确，请下载模板进行参考");
            throw new BusinessException("表头格式不正确，请下载模板进行参考");
        }
    }


    @Override
    public void invoke(SalesmanUploadVO salesman, AnalysisContext analysisContext) {
        //读取的每一行，这里可以对excel的每一行数据进行处理
        if (salesman == null || (StringUtils.isBlank(salesman.getTeam())
                && StringUtils.isBlank(salesman.getRole())
                && StringUtils.isBlank(salesman.getName())
                && StringUtils.isBlank(salesman.getPhone())
        )) {
            //过滤空数据
            return;
        }
        //去空格
        salesman.setTeam(salesman.getTeam() == null ? null : salesman.getTeam().replace(" ", ""));
        salesman.setRole(salesman.getRole() == null ? null : salesman.getRole().replace(" ", ""));
        salesman.setName(salesman.getName() == null ? null : salesman.getName().replace(" ", ""));
        salesman.setPhone(salesman.getPhone() == null ? null : salesman.getPhone().replace(" ", ""));
        //过滤空数据后的结果加入到集合
        list.add(salesman);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //验证通过后要做的业务
    }

    //返回过滤空数据后的结果
    public List<SalesmanUploadVO> getList() {
        return list;
    }

}

Controller

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.RequiredArgsConstructor;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

@RestController
@RequestMapping("/salesman")
@RequiredArgsConstructor
@Api(tags = "销售人员管理API")
public class SalesmanController {

    private final SalesmanService salesmanService;
	
	@ApiOperation("上传销售人员")
	@PostMapping("importSalesman")
	public BaseResponse<List<SalesmanUploadVO>> importSalesman(MultipartFile file) {
		List<SalesmanUploadVO> salesman = salesmanService.importSalesman(file);
		return BaseResponse.SucResponse(salesman);
	}
}

Service

import java.util.List;

/**
 * @Author: CQian
 * @Date: 2024/2/28 14:59
 */

public interface SalesmanService extends IService<SalesmanEntity> {
	List<SalesmanUploadVO> importSalesman(MultipartFile file);
}

ServiceImpl

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import com.utils.kit.JsonKit;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

@Service
@RequiredArgsConstructor
@Slf4j
public class SalesmanServiceImpl extends ServiceImpl<SalesmanMapper, SalesmanEntity> implements SalesmanService {

    private final SalesmanMapper salesmanMapper;

	@Override
	public List<SalesmanUploadVO> importSalesman(MultipartFile file) {
		if (file != null) {
			try {
				//读取数据
				SalesmanListener readListener = new SalesmanListener();
				EasyExcel.read(file.getInputStream(), SalesmanUploadVO.class, readListener).sheet(0).doRead();
				List<SalesmanUploadVO> salesmanUploadVOS = readListener.getList();
				//操作数据
				if (CollectionUtil.isNotEmpty(salesmanUploadVOS)) {
					if (salesmanUploadVOS.size() > 3000) {
						log.error("【销售人员导入】上传的销售人员不能超过3000条，当前{}条", salesmanUploadVOS.size());
						throw new BusinessException("上传的销售人员不能超过3000条，当前" + salesmanUploadVOS.size() + "条");
					}

					/**以下属于业务处理，可以根据自己的业务需求进行操作，例如：对数据进行入库或者将数据返回**/
					/**数据校验：校验数据是否符合业务需求**/
					//存放符合要求的数据
					List<SalesmanUploadVO> salesmanList = new ArrayList<>();

					//不存在空值的数据
					List<SalesmanUploadVO> salesmanNotNull = salesmanUploadVOS.stream().filter(it -> StringUtils.isNotBlank(it.getTeam()) && StringUtils.isNotBlank(it.getRole()) && StringUtils.isNotBlank(it.getName()) && StringUtils.isNotBlank(it.getPhone())).collect(Collectors.toList());

					//存放错误数据
					List<SalesmanUploadVO> salesmanError = new ArrayList<>();

					//校验团队和角色是否存在
					long t1 = System.currentTimeMillis();
					for (SalesmanUploadVO salesman : salesmanNotNull) {
						String teamValue = dictMapper.getValue("SALES_TEAM", salesman.getTeam());
						String roleValue = dictMapper.getValue("SALES_ROLE", salesman.getRole());
						if (StringUtils.isBlank(teamValue) || StringUtils.isBlank(roleValue)) {
							salesmanError.add(salesman);
						} else {
							salesman.setTeamCode(teamValue);
							salesman.setRoleCode(roleValue);
							salesmanList.add(salesman);
						}
					}
					log.info("【销售人员导入】(失败数据1/2)团队或角色错误的数据，条数：{}条，数据：{}", salesmanError.size(), JsonKit.bean2Json(salesmanError));
					long t2 = System.currentTimeMillis();
					log.info("【销售人员导入】校验团队和角色数据处理=>条数：{}条，耗时：{}秒", salesmanNotNull.size(), (t2 - t1) / 1000);

					//存在空值的数据
					List<SalesmanUploadVO> salesmanHasNull = salesmanUploadVOS.stream().filter(it -> StringUtils.isBlank(it.getTeam()) || StringUtils.isBlank(it.getRole()) || StringUtils.isBlank(it.getName()) || StringUtils.isBlank(it.getPhone())).collect(Collectors.toList());
					log.info("【销售人员导入】(失败数据2/2)存在空值的数据，条数：{}条，数据：{}", salesmanHasNull.size(), JsonKit.bean2Json(salesmanHasNull));

					log.info("【销售人员导入】(成功数据1/1)最终符合要求的数据，条数：{}条，数据：{}", salesmanList.size(), JsonKit.bean2Json(salesmanList));
					return salesmanList;
				} else {
					log.error("【销售人员导入】上传的销售人员Excel中无数据");
					throw new BusinessException("上传的销售人员Excel中无数据");
				}
			} catch (Exception e) {
				log.error("【销售人员导入】上传销售人员存在错误={}", e);
				throw new BusinessException("上传销售人员失败，" + e.getMessage());
			}
		} else {
			log.error("【销售人员导入】上传文件不能为空");
			throw new BusinessException("上传文件不能为空");
		}
	}
}