融易宝项目之EasyExcel和数据字典的使用

融易宝项目之EasyExcel和数据字典的使用

欢迎关注微信公众号：序辑

一、Alibaba EasyExcel

1.EasyEscel简介

1.1Excel导入导出的应用场景

数据导入 减轻录入工作量
数据导出 统计信息归档
数据传输 异构系统之间数据传输

1.2EasyExcel简介

常见excel分解框架：POL、EasyExcel

1.2.1官方网站

https://github.com/alibaba/easyexcel
快速开始：
https://www.yuque.com/easyexcel/doc/easyexcel

1.2.2EasyExcel特点

1.Java领域解析、生成Excel 比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc。

2.EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
3.EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。

2.读写Excel

2.1创建项目

2.1.1创建一个普通的maven项目

项目名：alibaba-easyexcel

2.1.2pom中引入xml相关依赖

<dependencies>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.7</version>
    </dependency>

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <version>1.7.5</version>
    </dependency>

    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>3.1.0</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
    </dependency>

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>

</dependencies>

2.2写

2.2.1创建实体类

package com.aojiaoge.easyexcel.dto;

@Data
public class ExcelStudentDTO {

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("生日")
    private Date birthday;

    @ExcelProperty("薪资")
    private Double salary;
}

2.2.2最简单的写

package com.aojiaoge.easyexcel;
public class ExcelWriteTest {
    @Test
    public void simpleWriteXlsx() {
        String fileName = "d:/excel/simpleWrite.xlsx"; //需要提前新建目录
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, ExcelStudentDTO.class).sheet("模板").doWrite(data());
    }
    
    //辅助方法
    private List<ExcelStudentDTO> data(){
        List<ExcelStudentDTO> list = new ArrayList<>();

        //算上标题，做多可写65536行
        //超出：java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
        for (int i = 0; i < 65535; i++) {
            ExcelStudentDTO data = new ExcelStudentDTO();
            data.setName("Helen" + i);
            data.setBirthday(new Date());
            data.setSalary(123456.1234);
            list.add(data);
        }

        return list;
    }
}

2.2.3不同版本的写

@Test
public void simpleWriteXls() {

    String fileName = "d:/excel/simpleWrite.xls";
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, ExcelStudentDTO.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(data());
}

2.2.4写入大数据量

xls版本的Excel最多一次可写0-65535行
xlsx版本的Excel最多一次可写0-104875行

2.3读

2.3.1创建监听器

package com.aojiaoge.easyexcel.listener;

@Slf4j
public class ExcelStudentDTOListener extends AnalysisEventListener<ExcelStudentDTO> {
    
    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(ExcelStudentDTO data, AnalysisContext context) {
        log.info("解析到一条数据:{}", data);

    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据解析完成！");
    }
}

2.3.2测试用例

package com.aojiaoge.easyexcel;

public class ExcelReadTest {

    /**
     * 最简单的读
     */
    @Test
    public void simpleReadXlsx() {

        String fileName = "d:/excel/simpleWrite.xlsx";
        // 这里默认读取第一个sheet
        EasyExcel.read(fileName, ExcelStudentDTO.class, new ExcelStudentDTOListener()).sheet().doRead();
    }

    @Test
    public void simpleReadXls() {

        String fileName = "d:/excel/simpleWrite.xls";
        EasyExcel.read(fileName, ExcelStudentDTO.class, new ExcelStudentDTOListener()).excelType(ExcelTypeEnum.XLS).sheet().doRead();
    }
}

二、数据字典

1.数据字典的设计

1.1什么是数据字典

何为数据字典？数据字典负责管理系统常用的分类数据或者一些固定数据，例如：省市区三级联动数据、民族数据、行业数据、学历数据等，数据字典帮助我们方便的获取和适用这些通用数据。

1.2数据字典的设计

1.parent_id：上级id，通过id与parent_id构建上下级关系，例如：我们要获取所有行业数据，那么只需要查询parent_id=20000的数据
2.name： 名称，例如：填写用户信息，我们要select标签选择民族，“汉族”就是数据字典的名称
3.value： 值，例如：填写用户信息，我们要select标签选择民族，“1”（汉族的标识）就是数据字典的值
4.dict_code： 编码，编码是我们自定义的，全局唯一，例如：我们要获取行业数据，我们可以通过parent_id获取，但是parent_id是不确定的，所以我们可以根据编码来获取行业数据

2.Excel数据批量导入

2.1后端接口

2.1.1添加依赖

core中添加以下依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
</dependency>

<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
</dependency>

2.1.2创建Excel实体类

package com.aojiaoge.srb.core.pojo.dto;
@Data
public class ExcelDictDTO {

    @ExcelProperty("id")
    private Long id;

    @ExcelProperty("上级id")
    private Long parentId;

    @ExcelProperty("名称")
    private String name;

    @ExcelProperty("值")
    private Integer value;

    @ExcelProperty("编码")
    private String dictCode;
}

2.1.3创建监听器

package com.aojaioge.srb.core.listener;

@Slf4j
//@AllArgsConstructor //全参
@NoArgsConstructor //无参
public class ExcelDictDTOListener extends AnalysisEventListener<ExcelDictDTO> {

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<ExcelDictDTO> list = new ArrayList();

    private DictMapper dictMapper;

	//传入mapper对象
    public ExcelDictDTOListener(DictMapper dictMapper) {
        this.dictMapper = dictMapper;
    }

    /**
     *遍历每一行的记录
     * @param data
     * @param context
     */
    @Override
    public void invoke(ExcelDictDTO data, AnalysisContext context) {
        log.info("解析到一条记录: {}", data);
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        dictMapper.insertBatch(list);  //批量插入
        log.info("存储数据库成功！");
    }
}

2.1.4Mapper层批量插入

接口：DictMapper

void insertBatch(List<ExcelDictDTO> list);

xml：DictMapper.xml

<insert id="insertBatch">
    insert into dict (
    id ,
    parent_id ,
    name ,
    value ,
    dict_code
    ) values
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.id} ,
        #{item.parentId} ,
        #{item.name} ,
        #{item.value} ,
        #{item.dictCode}
        )
    </foreach>
</insert>

2.1.5Service层创建监听器实例

接口：DictService

void importData(InputStream inputStream);

实现：DictServiceImpl
注意：此处添加了事务处理，默认情况下 rollbackFor = RuntimeException.class

@Transactional(rollbackFor = {Exception.class})
@Override
public void importData(InputStream inputStream) {
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(inputStream, ExcelDictDTO.class, new ExcelDictDTOListener(baseMapper)).sheet().doRead();
    log.info("importData finished");
}

2.1.6Controller层接收客户端上传

AdminDictController

package com.aojiaoge.srb.core.controller.admin;

@Api(tags = "数据字典管理")
@RestController
@RequestMapping("/admin/core/dict")
@Slf4j
@CrossOrigin
public class AdminDictController {

    @Resource
    private DictService dictService;

    @ApiOperation("Excel批量导入数据字典")
    @PostMapping("/import")
    public R batchImport(
            @ApiParam(value = "Excel文件", required = true)
            @RequestParam("file") MultipartFile file) {

        try {
            InputStream inputStream = file.getInputStream();
            dictService.importData(inputStream);
            return R.ok().message("批量导入成功");
        } catch (Exception e) {
            //UPLOAD_ERROR(-103, "文件上传错误"),
            throw new BusinessException(ResponseEnum.UPLOAD_ERROR, e);
        }
    }
}

2.1.7添加mapper发布配置

注意：因为maven工程在默认情况下src/main/java目录下的所有资源文件是不发布到target目录下的，因此我们需要在pom.xml中添加xml配置文件发布配置

<build>
    <!-- 项目打包时会将java目录中的*.xml文件也进行打包 -->
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
    </resources>
</build>

测试

2.2前端调用

2.2.1创建页面插件

创建 src/views/core/dict/list.vue

<template>
  <div class="app-container">
    
  </div>
</template>

<script>
export default {
  
}
</script>

2.2.2配置路由

{
    path: '/core',
    component: Layout,
    redirect: '/core/dict/list',
    name: 'coreDict',
    meta: { title: '系统设置', icon: 'el-icon-setting' },
    alwaysShow: true,
    children: [
      {
        path: 'dict/list',
        name: '数据字典',
        component: () => import('@/views/core/dict/list'),
        meta: { title: '数据字典' }
      }
    ]
}

2.2.3实现数据导入

<template>
  <div class="app-container">
    <div style="margin-bottom: 10px;">
      <el-button
        @click="dialogVisible = true"
        type="primary"
        size="mini"
        icon="el-icon-download"
      >
        导入Excel
      </el-button>
    </div>

    <el-dialog title="数据字典导入" :visible.sync="dialogVisible" width="30%">
      <el-form>
        <el-form-item label="请选择Excel文件">
          <el-upload
            :auto-upload="true"
            :multiple="false"
            :limit="1"
            :on-exceed="fileUploadExceed"
            :on-success="fileUploadSuccess"
            :on-error="fileUploadError"
            :action="BASE_API + '/admin/core/dict/import'"
            name="file"
            accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
          >
            <el-button size="small" type="primary">点击上传</el-button>
          </el-upload>
        </el-form-item>
      </el-form>                                                                                                                                                                                           
      <div slot="footer" class="dialog-footer">
        <el-button @click="dialogVisible = false">
          取消
        </el-button>
      </div>
    </el-dialog>
  </div>
</template>

<script>
export default {
  // 定义数据
  data() {
    return {
      dialogVisible: false, //文件上传对话框是否显示
      BASE_API: process.env.VUE_APP_BASE_API //获取后端接口地址
    }
  },

  methods: {
    // 上传多于一个文件时
    fileUploadExceed() {
      this.$message.warning('只能选取一个文件')
    },

    //上传成功回调
    fileUploadSuccess(response) {
      if (response.code === 0) {
        this.$message.success('数据导入成功')
        this.dialogVisible = false
      } else {
        this.$message.error(response.message)
      }
    },

    //上传失败回调
    fileUploadError(error) {
      this.$message.error('数据导入失败')
    }
  }
}
</script>

3.Excel数据批量导出

3.1后端接口

3.1.1Service层解析Excel数据

接口：DictService

List<ExcelDictDTO> listDictData();

实现：DictServiceImpl

@Override
public List<ExcelDictDTO> listDictData() {

    List<Dict> dictList = baseMapper.selectList(null);
    //创建ExcelDictDTO列表，将Dict列表转换成ExcelDictDTO列表
    ArrayList<ExcelDictDTO> excelDictDTOList = new ArrayList<>(dictList.size());
    dictList.forEach(dict -> {

        ExcelDictDTO excelDictDTO = new ExcelDictDTO();
        BeanUtils.copyProperties(dict, excelDictDTO);
        excelDictDTOList.add(excelDictDTO);
    });
    return excelDictDTOList;
}

3.1.2Controller层接收客户端请求

@ApiOperation("Excel数据的导出")
@GetMapping("/export")
public void export(HttpServletResponse response){
    
    try {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("mydict", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ExcelDictDTO.class).sheet("数据字典").doWrite(dictService.listDictData());

    } catch (IOException e) {
        //EXPORT_DATA_ERROR(104, "数据导出失败"),
        throw  new BusinessException(ResponseEnum.EXPORT_DATA_ERROR, e);
    }    
}

3.2前端调用

3.2.1页面添加导出按钮

<el-button
    @click="exportData"
    type="primary"
    size="mini"
    icon="el-icon-upload2" >导出Excel</el-button>

3.2.2添加导出方法

//Excel数据导出
exportData() {
    window.location.href = this.BASE_API + '/admin/core/dict/export'
}

4.数据字典列表展示

4.1后端接口

4.1.1实体类添加属性

Dict中添加属性：

@ApiModelProperty(value = "是否包含子节点")
@TableField(exist = false)//在数据库表中忽略此列
private boolean hasChildren;

4.1.2Service层实现数据查询

接口：DictService

List<Dict> listByParentId(Long parentId);

实现：DcitServiceImpl

@Override
public List<Dict> listByParentId(Long parentId) {
    List<Dict> dictList = baseMapper.selectList(new QueryWrapper<Dict>().eq("parent_id", parentId));
    dictList.forEach(dict -> {
        //如果有子节点，则是非叶子节点
        boolean hasChildren = this.hasChildren(dict.getId());
        dict.setHasChildren(hasChildren);
    });
    return dictList;
}

/**
     * 判断该节点是否有子节点
     */
private boolean hasChildren(Long id) {
    QueryWrapper<Dict> queryWrapper = new QueryWrapper<Dict>().eq("parent_id", id);
    Integer count = baseMapper.selectCount(queryWrapper);
    if(count.intValue() > 0) {
        return true;
    }
    return false;
}

4.1.3Controller层接收前端请求

@ApiOperation("根据上级id获取子节点数据列表")
@GetMapping("/listByParentId/{parentId}")
public R listByParentId(
    @ApiParam(value = "上级节点id", required = true)
    @PathVariable Long parentId) {
    List<Dict> dictList = dictService.listByParentId(parentId);
    return R.ok().data("list", dictList);
}

4.2前端调用

4.2.1api

创建src/api/core/dict.js

import request from '@/utils/request'
export default {
  listByParentId(parentId) {
    return request({
      url: `/admin/core/dict/listByParentId/${parentId}`,
      method: 'get'
    })
  }
}

4.2.2组件脚本

在views/core/dict/list.vue
定义data：

list: [], //数据字典列表

生命周期函数：

created() {
    this.fetchData()
},

获取数据的方法：

import dictApi from '@/api/core/dict'

// 调用api层获取数据库中的数据
fetchData() {
  dictApi.listByParentId(1).then(response => {
    this.list = response.data.list
  })
},

//延迟加载子节点
load(row, treeNode, resolve) {
  dictApi.listByParentId(row.id).then(response => {
    //负责将子节点数据展示在展开的列表中  
    resolve(response.data.list)
  })
},

4.2.3组件模板

<el-table :data="list" border row-key="id" lazy :load="load">
    <el-table-column label="名称" align="left" prop="name" />
    <el-table-column label="编码" prop="dictCode" />
    <el-table-column label="值" align="left" prop="value" />
</el-table>

4.2.4流程优化

数据导入后刷新页面的数据列表

//上传成功回调
fileUploadSuccess(response) {
    if (response.code === 0) {
        this.$message.success('数据导入成功')
        this.dialogVisible = false
        this.fetchData()
    } else {
        this.$message.error(response.message)
    }
},

})
},

//延迟加载子节点
load(row, treeNode, resolve) {
dictApi.listByParentId(row.id).then(response => {
//负责将子节点数据展示在展开的列表中
resolve(response.data.list)
})
},

##### 4.2.3组件模板

```vue
<el-table :data="list" border row-key="id" lazy :load="load">
    <el-table-column label="名称" align="left" prop="name" />
    <el-table-column label="编码" prop="dictCode" />
    <el-table-column label="值" align="left" prop="value" />
</el-table>

4.2.4流程优化

数据导入后刷新页面的数据列表

//上传成功回调
fileUploadSuccess(response) {
    if (response.code === 0) {
        this.$message.success('数据导入成功')
        this.dialogVisible = false
        this.fetchData()
    } else {
        this.$message.error(response.message)
    }
},