使用EasyExcel导入导出数据字典

目录

1、导出excel

1.1、导入依赖

1.2、编写需要导出对象的实体类

1.3、编写service

1.4、编写controller

1.5、编写前端页面

1.6、测试

2、导入excel

2.1、创建回调监听器

2.2、创建接口

2.5、测试

---

1、导出excel

1.1、导入依赖

       
        
            com.alibaba
            easyexcel
            2.1.1
        

1.2、编写需要导出对象的实体类

原实体类Dict:

@Data
@ApiModel(description = "数据字典")
@TableName("dict")
public class Dict extends BaseEntity {
 
    private static final long serialVersionUID = 1L;
 
    @ApiModelProperty(value = "上级id")
    @TableField("parent_id")
    private Long parentId;
 
    @ApiModelProperty(value = "名称")
    @TableField("name")
    private String name;
 
    @ApiModelProperty(value = "值")
    @TableField("value")
    private String value;
 
    @ApiModelProperty(value = "编码")
    @TableField("dict_code")
    private String dictCode;
 
    @ApiModelProperty(value = "是否包含子节点")
    @TableField(exist = false)
    private boolean hasChildren;
 
}

导出实体类DictEeVo:

@Data
public class DictEeVo {
 
	@ExcelProperty(value = "id" ,index = 0)
	private Long id;
 
	@ExcelProperty(value = "上级id" ,index = 1)
	private Long parentId;
 
	@ExcelProperty(value = "名称" ,index = 2)
	private String name;
 
	@ExcelProperty(value = "值" ,index = 3)
	private String value;
 
	@ExcelProperty(value = "编码" ,index = 4)
	private String dictCode;
 
}
 

1.3、编写service

导出接口:

    /**
     * 导出
     * @param response
     */
    void exportData(HttpServletResponse response);

实现类:

   /**
     * excel导出
     * @param response
     */
    @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 dictList = dictMapper.selectList(null);
            //vo
            List dictVoList = new ArrayList<>(dictList.size());
            //将dict复制到dictVo
            for(Dict dict : dictList) {
                DictEeVo dictVo = new DictEeVo();
                BeanUtils.copyProperties(dict, dictVo, DictEeVo.class);
                dictVoList.add(dictVo);
            }
            EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictVoList);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

1.4、编写controller

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

1.5、编写前端页面

按钮:

    <el-button type="primary" @click="exportData">导出el-button>

导出方法:

 
    //导出excel
    exportData() {
      window.location.href = "http://localhost:8202/dict/exportData";
    },

1.6、测试

2、导入excel

2.1、创建回调监听器

package com.rk.cmn.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.rk.cmn.service.DictService;
import com.rk.hosp.model.cmn.Dict;
import com.rk.hosp.vo.cmn.DictEeVo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import java.util.ArrayList;
import java.util.List;
 
/**
 * @author :Rk.
 * @date : 2022/11/4
 */
// 有个很重要的点 DictEeVoListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去 构造方法注入
@Slf4j
public class DickListener extends AnalysisEventListener {
 
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
 
    /**
     * 缓存的数据
     */
    private List cachedDataList =new ArrayList<>(BATCH_COUNT);
 
    /**
     * 真正存储的集合
     */
    List list= new ArrayList<>();
 
    private DictService dictService;
    public DickListener(DictService dictService) {
        this.dictService = dictService;
    }
 
 
    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(DictEeVo dictEeVo, AnalysisContext context) {
        cachedDataList.add(dictEeVo);
 
        //需要将dictEeVo对象转换为dict 才能存储
        Dict dict=new Dict();
        BeanUtils.copyProperties(dictEeVo,dict);
        list.add(dict);
 
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList.clear();
            list.clear();
        }
    }
 
    /**
     * 所有数据解析完成了 都会来调用
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }
 
    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        dictService.saveBatch(list);
        log.info("存储数据库成功！");
    }
}

2.2、创建接口

    @ApiOperation(value = "导入")
    @PostMapping("importData")
    public Result importData(MultipartFile file) throws IOException {
        //0代表读取excel的sheet1
        EasyExcel.read(file.getInputStream(), DictEeVo.class,new DickListener(dictService)).sheet(0).doRead();
        return Result.ok();
    }

2.3、前端上传按钮和上传弹窗

按钮:

"success" @click="importData"><i class="fa fa-plus" /> 导入

弹窗:

 <el-dialog title="导入" :visible.sync="dialogImportVisible" width="480px">
      <el-form>
        <el-form-item label="文件">
          <el-upload
            class="upload-demo"
            :on-success="onUploadSuccess"
            :on-error="onUploadError"
            :show-file-list="false"
            action="http://localhost:8202/admin/cmn/dict/importData"
            :multiple="false"
          >
          <el-button size="small" type="primary">点击上传el-button>
           <div slot="tip" class="el-upload__tip">只能上传xls文件，且不超过500kbdiv>
          el-upload>
        el-form-item>
      el-form>
      <div slot="footer" class="dialog-footer">
        <el-button @click="dialogImportVisible = false"> 取消 el-button>
      div>
    el-dialog>

2.4、前端上传方法

    //导入ecxel
    importData(){
      //弹出上传窗口
      this.dialogImportVisible=true;
    },
 
    //文件上传成功调用
    onUploadSuccess(){
      this.$message({
            type: "success",
            message: "导入成功!",
          });
      this.dialogImportVisible = false
      this.getChlidData(1);
    },
 
    //文件上传失败调用
    onUploadError(){
      this.$message({
            type: "error",
            message: "导入失败!",
          });
      this.dialogImportVisible = false
      this.fetchData()
    },

2.5、测试

将下面的excel表格上传