先在pom文件导入阿里巴巴excel的依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.3</version>
</dependency>
版本可以根据自己的需求在进行调整,我这里只需要做导入模块,所以我选择3.1.3.
导入依赖之后就是要写一个实体类(dto)来接收导入的字段,其中注意实体类里面的字段必须要和导入的字段一模一样,不然就会报错
在建一个包(BeanContext)用于后面做自动注入,因为我们要写监听器,去监听有没有上传execl,但是监听器里使用自动注入的注解会出现问题,所以这边我们要自己写一个自动注入的方法供后面自动注入使用。
@Component
public class BeanContext implements ApplicationContextAware {
private static ApplicationContext applicationContext;
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
BeanContext.applicationContext = applicationContext;
}
public static ApplicationContext getApplicationContext(){
return applicationContext;
}
@SuppressWarnings("unchecked")
public static <T> T getBean(String name) throws BeansException {
return (T)applicationContext.getBean(name);
}
public static <T> T getBean(Class<T> clz) throws BeansException {
return (T)applicationContext.getBean(clz);
}
}
(这里代码直接复制,不需要改)
选择我们来写监听器,开始转数据,并对得到的数据进行判断
**
* 节假日导入监听器
*/
@Service
@Transactional(readOnly = true)
public class HolidayExcelListener extends AnalysisEventListener<HolidayExcelAddDTO> {
HolidayService holidayService = BeanContext.getApplicationContext().getBean(HolidayService.class);
//批量处理阈值
private static final int BATCH_COUNT = 5;
List<HolidayExcelAddDTO> list = new ArrayList<>(BATCH_COUNT);
//解析第一条消息时调用
@Override
public void invoke(HolidayExcelAddDTO holidayExcelAddDTO, AnalysisContext analysisContext) {
list.add(holidayExcelAddDTO);
if (list.size() >= BATCH_COUNT) {
importItemInfo(list);
list.clear();
}
}
//解析最后一条消息时调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
importItemInfo(list);
}
//批量处理节假日
@Transactional(readOnly = false)
public void importItemInfo(List<HolidayExcelAddDTO> infoList) {
List<Holiday> list = new ArrayList<>();
try {
for (HolidayExcelAddDTO holidayExcelAddDTO : infoList) {
if (holidayExcelAddDTO.getHolidayName() == null) {
throw new RuntimeException("节假日名称不能为空");
}
if (holidayExcelAddDTO.getBeginDate() == null) {
throw new RuntimeException("开始时间不能为空");
}
if (holidayExcelAddDTO.getEndDate() == null) {
throw new RuntimeException("结束时间不能为空");
}
Holiday holiday = new Holiday();
//判断节假日名称是否存在
QueryWrapper<Holiday> queryWrapper = new QueryWrapper<>();
queryWrapper.select("holiday_id");
queryWrapper.eq("holiday_name", holidayExcelAddDTO.getHolidayName());
Holiday info = holidayService.getOne(queryWrapper);
if (!ObjectUtils.isEmpty(info)) {
throw new RuntimeException("节假日名称已存在");
}
//验证时间是否存在
holidayService.checkDateSize(holidayExcelAddDTO.getBeginDate(), holidayExcelAddDTO.getEndDate());
BeanUtils.copyProperties(holidayExcelAddDTO, holiday);
holiday.setBeginDate(LocalDate.parse(holidayExcelAddDTO.getBeginDate()));
holiday.setEndDate(LocalDate.parse(holidayExcelAddDTO.getEndDate()));
holiday.setCreateTime(LocalDateTime.now());
list.add(holiday);
}
//插入失败回退
holidayService.saveBatch(list);
} catch (Exception e) {
throw new RuntimeException("导入异常");
}
}
}
最后控制层进行返回
/**
* 节假日导入
*
* @param file
* @return
* @throws IOException
*/
@PostMapping("upload")
public ResultApi<?> upload(MultipartFile file) throws IOException {
HolidayExcelListener listener = new HolidayExcelListener();
EasyExcel.read(file.getInputStream(), HolidayExcelAddDTO.class, listener)
.sheet(0)
.headRowNumber(1)
.doRead();
return ResultApi.success();
}
(第一次写,可能没那么好理解,有不懂或者不对可以改进的地方,欢迎评论)