项目中使用EasyExcel实现导入导出功能

poi导出数据

相当于一次性把excel拿过来，分配树形结构进行操作，若文件大可能会产生内存溢出

因为是树形结构，对于添加删除比较方便

esayexcel

相当于一行一行拿过来进行操作，操作之后再扔出内存

使用，添加依赖

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

编写service层方法

//导出
    @Override
    public void exportData(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 = baseMapper.selectList(null);
            //因为自己封装了返回类，与实体类字段不匹配
            List<DictEeVo> dictVoList = new ArrayList<>(dictList.size());
            for(Dict dict : dictList) {
                //dict对象 转成vo对象
                DictEeVo dictVo = new DictEeVo();
                BeanUtils.copyProperties(dict,dictVo);
                dictVoList.add(dictVo);
            }
            EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictVoList);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

控制层

@ApiOperation(value="导出")
@GetMapping(value = "/exportData")
public void exportData(HttpServletResponse response) {
 dictService.exportData(response);
}

vue页面添加按钮

<div class="el-toolbar">
 <div class="el-toolbar-body" style="justify-content: flex-start;">
 <el-button type="text" @click="exportData"><i class="fa fa-plus"/> 导出</el-b
 </div>
</div>

添加方法

methods: {
     //导出
     exportData(){
         window.open("http://localhost:8202/admin/cmn/dict/exportData")
     },

导入功能，页面添加按钮

  <el-button type="text" @click="importData"><i class="fa fa-plus"/> 导入</el-button>

控制层

@ApiOperation(value = "导入")
    @PostMapping("importData")
    public R importData(MultipartFile file) {
        //获取选择之后的文件
        dictService.importDictData(file);
        return R.ok();
    }

添加监听器

@Component
public class DictListener extends AnalysisEventListener<DictEeVo> {
    @Autowired
    private DictMapper dictMapper;
    //一行一行读取
    @Override
    public void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {
        //调用方法添加数据库
        Dict dict = new Dict();
        BeanUtils.copyProperties(dictEeVo,dict);
        dictMapper.insert(dict);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

service层

	@Autowired
    private DictListener dictListener;
    //导入
    @Override
    public void importDictData(MultipartFile file) {
        //获取文件内容 将内容加入数据库
        try {
            EasyExcel.read(file.getInputStream(),DictEeVo.class,dictListener).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

前端页面

<el-dialog title="导入":visible.sync="dialogImportVisible"width="480px">
 <el-form label-position="right" label-width="170px">
 <el-form-item label="文件">
 <el-upload
 :multiple="false" 
 :on-success="onUploadSuccess"
 :action="'http://localhost:8202/admin/cmn/dict/importData'"
 class="upload-demo">
 <el-button size="small" type="primary">点击上传</el-button>
 <div slot="tip" class="el-upload__tip">只能上传xls文件</div>
 </el-upload>
 </el-form-item>
 </el-form>
 <div slot="footer" class="dialog-footer">
 <el-button @click="dialogImportVisible = false">取消</el-button>
 </div>
</el-dialog>

方法

export default {
    data() {
    return {
    dialogImportVisible:false, //弹框
    }
 },
 created() {
    this.getDictList(1)
 },
 methods: {
     //导入
     importData() {
        this.dialogImportVisible = true
        },
        onUploadSuccess(response, file) {
        this.$message.info('上传成功')
        this.dialogImportVisible = false
        this.getDictList(1)
        },