EasyExcel导入

记录摸鱼的一天
技术栈：spring boot2.x+mybatis plus+easyExcel 2.2.6

生成简单的实体类等等等等

导入easyExcel的依赖

实体类

编写服务层

import com.csepdi.ledger.model.Information;
import com.baomidou.mybatisplus.extension.service.IService;
import com.github.pagehelper.PageInfo;

import java.util.List;

/**
 * <p>
 *  服务类
 * </p>
 *
 * @author xiaoWan
 * @since 2020-12-10
 */
public interface InformationService extends IService<Information> {

    void readExcel(List<Information> informationList);
}

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.csepdi.ledger.model.Information;
import com.csepdi.ledger.mapper.InformationMapper;
import com.csepdi.ledger.service.InformationService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author xiaoWan
 * @since 2020-12-10
 */
@Service
public class InformationServiceImpl extends ServiceImpl<InformationMapper, Information> implements InformationService {

    @Autowired
    InformationService informationService;

    @Override
    public void readExcel(List<Information> informationList) {
        //因为是一个list集合，用saveBatch直接批量添加到数据库
        informationService.saveBatch(informationList);
    }
}

编写监听工具类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.csepdi.ledger.model.Information;
import com.csepdi.ledger.service.InformationService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Component
@Scope("prototype")
public class ExcelImportUtils extends AnalysisEventListener<Information> {

    @Autowired
    InformationService informationService;


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

    @Override
    public void invoke(Information information, AnalysisContext analysisContext) {
        list.add(information);
        //判断有值就写入数据库中，凑齐十条数据就查看到数据库，比一个一个插入效率更高
        if (list.size() % 10 == 0){
            //调用实现层方法 将获取到的数据写入数据库中
            informationService.readExcel(list);
            //将list清空，避免重复注入
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            //模10取余的数据在这里插入数据库
            informationService.readExcel(list);
    }
}

控制层

    @ApiOperation("导入Excel")
    @PostMapping("/importExcel")  //MultipartFile是要求前端上传的文件数据
    public void importExcel(MultipartFile uploadExcel) throws IOException {
        //工作簿  uploadExcel=》文件名称获取输入流    Information=》实体类   excelImportUtils=》监听工具类
        ExcelReaderBuilder read = EasyExcel.read(uploadExcel.getInputStream(), Information.class, excelImportUtils);
        //工作表
        read.sheet().doRead();
    }

postman测试

完活