EasyExcel 字典转换 BaseDictConverter

BaseDictConverter

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.cdhld.esp.api.entity.system.Dictionary;
import com.cdhld.esp.api.exception.ExcelErrorException;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

/**
 * Excel 字典导入时输入的是汉字，数据库存储的是字典值
 *
 * @author zph
 * @date 2024/4/25 19:00
 */
@Slf4j
@Setter
public abstract class BaseDictConverter implements Converter<Long> {

    //字典集合
    List<Dictionary> dicts = null;

    //匹配不到时 是否报错，还是置为null
    boolean throwError = true;

    @Override
    public Class<Long> supportJavaTypeKey() {
        return Long.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Long convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        //没有值，直接报错
        if (StrUtil.isNotBlank(cellData.getStringValue())) {
            if (CollUtil.isNotEmpty(dicts)) {
                for (Dictionary dictionary : dicts) {
                    if (dictionary.getRemark().equals(cellData.getStringValue())) {
                        return dictionary.getValue();
                    }
                }
            }
            if (throwError) {
                log.error("excel导入，字典转换失败：dicts：{} value：{}", dicts, cellData.getStringValue());
                throw new ExcelDataConvertException(cellData.getRowIndex(), cellData.getColumnIndex(), cellData, contentProperty, StrUtil.format("字典值'{}'不存在", cellData.getStringValue()));
            }
        }
        return null;
    }

    @Override
    public CellData<String> convertToExcelData(Long value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        if (CollUtil.isNotEmpty(dicts)) {
            for (Dictionary dictionary : dicts) {
                if (dictionary.getValue().equals(value)) {
                    return new CellData<>(dictionary.getRemark());
                }
            }
        }
        if (throwError) {
            log.error("excel导出，字典转换失败：dicts：{} value：{}", dicts, value);
            throw new ExcelErrorException("字典转换失败");
        }
        return new CellData<>("");
    }

}

字典对象

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.time.LocalDateTime;

/**
 *
 * @author zph
 * @date 2024/4/25
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("t_dictionary")
@ApiModel(value = "Dictionary对象", description = "字典表")
public class Dictionary implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId
    private Long id;

    @ApiModelProperty(value = "字典项名称")
    private String name;

    @ApiModelProperty(value = "字典项值")
    private Long value;

    @ApiModelProperty(value = "备注")
    private String remark;

    @ApiModelProperty(value = "父级id")
    private Long parentId;

    @ApiModelProperty(value = "逻辑删除")
    private Integer deleted;

    @ApiModelProperty(value = "created_time")
    private LocalDateTime createdTime;

    @ApiModelProperty(value = "updated_time")
    private LocalDateTime updatedTime;

}

导入\导出对象

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.cdhld.esp.api.converter.BaseDictConverter;
import com.cdhld.esp.api.entity.system.Dictionary;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 *
 * @author zph
 * @date 2024/4/25 13:54
 */
@Data
public class BusDataExcelDTO implements Serializable {

    private static final long serialVersionUID = 1L;

    @ExcelProperty(index = 0)
    @ApiModelProperty("序号")
    private Integer no;

    @ExcelProperty(index = 1, converter = TypeCoverter.class)
    @ApiModelProperty("类型")
    private Long type;


    public static class TypeCoverter extends BaseDictConverter {

        //字典集合
        public static List<Dictionary> dicts = null;

        //匹配不到时 是否报错，还是置为null
        boolean throwError = false;

        @Override
        public Long convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
            super.setDicts(dicts);
            super.setThrowError(throwError);
            return super.convertToJavaData(cellData, contentProperty, globalConfiguration);
        }

        @Override
        public CellData<String> convertToExcelData(Long value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
            super.setDicts(dicts);
            super.setThrowError(throwError);
            return super.convertToExcelData(value, contentProperty, globalConfiguration);
        }
    }

}

使用

    public void exportProject(HttpServletResponse response) {
        List<BusDataExcelDTO> dataList = CollUtil.newArrayList();
        //设置类型字典值
        BusDataExcelDTO.TypeCoverter.dicts = dictionarySrv.getList(100L);
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("数据列表", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), BusDataExcelDTO.class).sheet("数据列表").doWrite(dataList);
        } catch (Exception e) {
            log.error("导出异常:", e);
            throw new RuntimeException("导出异常");
        }
    }