一、 EasyExcel 实体类注解 - 数据的导入/导出

大美沐沐森

已于 2024-07-04 09:44:52 修改

阅读量2k

点赞数 9

文章标签： java

于 2024-03-06 13:38:19 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_37766721/article/details/136503580

版权

一、 EasyExcel 实体类注解 - 数据的导入/导出

（1）jar 的maven坐标配置

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>${easyexcel.version}</version>
    </dependency>

(2) 字典实体类

① SysDictData

package com.rocia.common.core.domain.entity;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.rocia.common.annotation.Excel;
import com.rocia.common.annotation.Excel.ColumnType;
import com.rocia.common.constant.UserConstants;
import com.rocia.common.core.domain.BaseEntity;

/**
 * 字典数据表 sys_dict_data
 */
@ApiModel(value = "数据字典")
public class SysDictData {
	/** 字典编码 */
	@ApiModelProperty(name = "dictCode",value = "字典编码")
	private Long dictCode;
	
	/** 字典排序 */
	@ApiModelProperty(name = "dictSort",value = "字典排序")
	private Long dictSort;
	
	/** 字典标签 */
	@ApiModelProperty(name = "dictLabel",value = "名称",required = true)
	private String dictLabel;
	
	/** 字典键值 */
	@ApiModelProperty(name = "dictValue",value = "字典键值",required = true)
	private String dictValue;
	
	/** 字典类型 */
	@ApiModelProperty(name = "dictType",value = "字典类型",required = true)
	private String dictType;
	
	/** 样式属性（其他样式扩展） */
	@ApiModelProperty(name = "cssClass",value = "样式属性")
	private String cssClass;
	
	/** 表格字典样式 */
	@ApiModelProperty(name = "listClass",value = "表格字典样式")
	private String listClass;
	
	/** 是否默认（Y是 N否） */
	@ApiModelProperty(name = "isDefault",value = "Y=是,N=否")
	private String isDefault;
	
	/** 状态（0正常 1停用） */
	@ApiModelProperty(name = "status",value = "0=正常,1=停用")
	private String status;
 }

② SysDictType

package com.rocia.common.core.domain.entity;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.rocia.common.core.domain.BaseEntity;

/**
 * 字典类型表 sys_dict_type
 */
@ApiModel(value = "字典类型")
public class SysDictType {
    private static final long serialVersionUID = 1L;

    /** 字典主键 */
    @ApiModelProperty(name = "dictId" ,value = "字典主键")
    private Long dictId;

    /** 字典名称 */
    @ApiModelProperty(name = "dictName" ,value = "字典类型")
    private String dictName;

    /** 字典类型 */
    @ApiModelProperty(name = "dictType" ,value = "字典类型")
    private String dictType;

    @ApiModelProperty(name = "type" ,value = "数据类型(1=系统数据 2=业务数据")
    private Integer type;

    /** 状态（0正常 1停用） */
    @ApiModelProperty(name = "status" ,value = "数据类型(0=正常,1=停用)")
    private String status;
}

（3）导出实体类

** SysDictDateExcelBody**

package com.rocia.common.core.domain.model;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class SysDictDataExcelBody {

    /**
     * 字典类别
     */
    @ExcelProperty(index = 0, value = "字典类别描述") private String dictTypeName;

    /**
     * 字典类别
     */
    @ExcelProperty(index = 1, value = "字典类别") private String dictType;

    /**
     * 字典值
     */
    @ExcelProperty(index = 2, value = "字典值") private String dictValue;

    /**
     * 字典标签值
     */
    @ExcelProperty(index = 3, value = "字典标签值") private String dictLabel;

    /**
     * 系统功能类型
     */
    @ExcelProperty(index = 4, value = "系统功能类型") private String type;
}

Excel 导出

（1）实体类注解导出

    public void downloadTemplate(HttpServletResponse response) {
        String fileName = null;
        try {
            fileName = URLEncoder.encode( "sys-dictType-" + System.currentTimeMillis(), "UTF-8");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        try {
            List<SysDictDataExcelBody> objects = new ArrayList<>();
            // 生成示例数据到 excel 中
            SysDictDataExcelBody excelBody = new SysDictDataExcelBody();
            excelBody.setType("8");
            excelBody.setDictType("job_category");
            excelBody.setDictTypeName("工作种类");
            excelBody.setDictLabel("人事");
            excelBody.setDictValue("1");
            objects.add(excelBody);

            SysDictDataExcelBody excelBody1 = new SysDictDataExcelBody();
            excelBody1.setType("8");
            excelBody1.setDictType("job_category");
            excelBody1.setDictTypeName("工作种类");
            excelBody1.setDictLabel("销售");
            excelBody1.setDictValue("2");
            objects.add(excelBody1);

            EasyExcel.write(response.getOutputStream(), SysDictDataExcelBody.class).sheet("字典模板").doWrite(objects);
        } catch (IOException e) {
            throw new CustomException(fileName + "模板下载失败！");
        }
    }

Excel 导入

(1) excel 数据监听器定义 SysDictReadListener

package com.rocia.web.controller.excellistener;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.rocia.common.core.domain.entity.SysDictData;
import com.rocia.common.core.domain.entity.SysDictType;
import com.rocia.common.core.domain.model.SysDictDataExcelBody;
import com.rocia.system.service.ISysDictDataService;
import com.rocia.system.service.ISysDictTypeService;
import lombok.extern.slf4j.Slf4j;

import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Slf4j
public class SysDictReadListener implements ReadListener<SysDictDataExcelBody> {

    /**
     * 每隔50条存储数据库，实际使用中可以50条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 50;
    private List<SysDictDataExcelBody> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private Map<String, SysDictType> sysDictTypeMap = new HashMap<>();


    private ISysDictTypeService sysDictTypeService;

    private ISysDictDataService sysDictDataService;


    public SysDictReadListener(ISysDictTypeService sysDictTypeService, ISysDictDataService sysDictDataService) {
        this.sysDictTypeService = sysDictTypeService;
        this.sysDictDataService = sysDictDataService;
    }

    @Override
    public void invoke(SysDictDataExcelBody data, AnalysisContext context) {
        List<SysDictType> sysDictTypes = sysDictTypeService.selectDictTypeAll();
        if (CollectionUtil.isNotEmpty(sysDictTypes)){
            sysDictTypeMap = sysDictTypes.stream().collect(Collectors.toMap(SysDictType::getDictType, Function.identity()));
        }

        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

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


    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        if (CollectionUtil.isEmpty(cachedDataList)){
            return;
        }

        Map<String, List<SysDictDataExcelBody>> collectMap = cachedDataList.stream().collect(Collectors.groupingBy(SysDictDataExcelBody::getDictType));

        for (Map.Entry<String, List<SysDictDataExcelBody>> entry : collectMap.entrySet()) {
            List<SysDictDataExcelBody> excelBodies = entry.getValue();
            String dictType = entry.getKey();

            SysDictDataExcelBody sysDictDataExcelBody = excelBodies.get(0);
            SysDictType sysDictType = new SysDictType();

            if (!sysDictTypeMap.containsKey(dictType)){
                //新增Type
                sysDictType.setDictName(sysDictDataExcelBody.getDictTypeName());
                sysDictType.setDictType(dictType);
                sysDictType.setStatus("0");
                sysDictType.setType(Integer.parseInt(sysDictDataExcelBody.getType()));
                sysDictType.setCreateBy("admin");
                sysDictType.setCreateTime(new Date());
                sysDictTypeService.insertDictType(sysDictType);
            }else {
                sysDictType = sysDictTypeMap.get(dictType);
            }

            for (int i = 0; i < excelBodies.size(); i++) {
                SysDictDataExcelBody excelBody = excelBodies.get(i);
                SysDictData sysDictData = new SysDictData();
                sysDictData.setDictSort((long) i);
                sysDictData.setDictLabel(excelBody.getDictLabel());
                sysDictData.setDictValue(excelBody.getDictValue());
                sysDictData.setDictType(dictType);
                sysDictData.setIsDefault("N");
                sysDictData.setStatus("0");
                sysDictType.setCreateBy("admin");
                sysDictType.setCreateTime(new Date());
                sysDictDataService.insertDictData(sysDictData);
            }
        }
        log.info("存储数据库成功！");
    }
}

（2）Excel 数据上传导入，将自定义的读取监听器进行注册

    /**
     * 文件上传
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link SysDictDataExcelBody}
     * <p>
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link SysDictReadListener}
     * <p>
     * 3. 直接读即可
     */
    @PostMapping("/upload/excel")
    @ResponseBody
    public R uploadDictExcel(MultipartFile file){
        try {
            EasyExcel.read(file.getInputStream(), SysDictDataExcelBody.class, new SysDictReadListener(dictTypeService,dictDataService)).sheet(0).doRead();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return R.ok();
    }