依赖
<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("上传文件不能为空");
}
}
}