[Java知识库]EasyExcel 在java中实现从数据库导入导出

先引入依赖

<dependencies>
    <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>
</dependencies>

导出

@Override
    public void exportDictData(HttpServletResponse response) {
        try {
            //设置下载信息
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("数据字典", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");

            //查询数据库
            List<Dict> dictList = dictMapper.selectList(null);

            //Dict-->DictEeVo
            List<DictEeVo> dictEeVoList=new ArrayList<>();

            for (Dict dict:dictList){
                DictEeVo dictEeVo=new DictEeVo();
                dictEeVo.setId(dict.getId());
                BeanUtils.copyProperties(dict,dictEeVo);
                dictEeVoList.add(dictEeVo);
            }

            //调用方法进行写操作
            EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictEeVoList);
        }catch (IOException e){
            e.printStackTrace();
        }

导入

 @Override
    public void importDictData(MultipartFile file) {
        try {
            EasyExcel.read(file.getInputStream(),DictEeVo.class,new DictListener(dictMapper)).sheet().doRead();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

public class DictListener extends AnalysisEventListener<DictEeVo> {

    private DictMapper dictMapper;

    /**
     * 因为AnalysisEventListener不能被Spring容器管理，所以需要有参构造器注入dictMapper
     * @param dictMapper
     */
    public DictListener(DictMapper dictMapper){
        this.dictMapper=dictMapper;
    }

    /**
     * 一行一行读取
     * @param data
     * @param context
     */
    @Override
    public void invoke(DictEeVo data, AnalysisContext context) {
        //调用方法添加到数据库
        Dict dict=new Dict();
        //DictEeVo->Dict
        BeanUtils.copyProperties(data,dict);
        dictMapper.insert(dict);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }
}