若依集成Easyexcel 3.1.x版本自定义Converter

拉格朗日爱上了泰勒丶

已于 2022-11-25 19:06:19 修改

阅读量2.9k

点赞数 1

文章标签： java excel 开发语言

于 2022-11-25 19:04:35 首次发布

本文链接：https://blog.csdn.net/hyk1499449886/article/details/128042888

版权

1、ruoyi-common\pom.xml模块添加整合依赖

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

2、ExcelUtil.java新增easyexcel导出导入方法

/**
 * 对excel表单默认第一个索引名转换成list（EasyExcel）
 * 
 * @param is 输入流
 * @return 转换后集合
 */
public List<T> importEasyExcel(InputStream is) throws Exception
{
	return EasyExcel.read(is).head(clazz).sheet().doReadSync();
}

/**
 * 对list数据源将其里面的数据导入到excel表单（EasyExcel）
 * 
 * @param list 导出数据集合
 * @param sheetName 工作表的名称
 * @return 结果
 */
public void exportEasyExcel(HttpServletResponse response, List<T> list, String sheetName)
{
	try
	{
		EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(list);
	}
	catch (IOException e)
	{
		log.error("导出EasyExcel异常{}", e.getMessage());
	}
}

3、模拟测试，以操作日志为例，修改相关类。

@Log(title = "操作日志", businessType = BusinessType.EXPORT)
@PreAuthorize("@ss.hasPermi('monitor:operlog:export')")
@PostMapping("/export")
public void export(HttpServletResponse response, SysOperLog operLog)
{
	List<SysOperLog> list = operLogService.selectOperLogList(operLog);
	ExcelUtil<SysOperLog> util = new ExcelUtil<SysOperLog>(SysOperLog.class);
	//主要改这里
	util.exportEasyExcel(response, list, "操作日志");
}

4、SysOperLog.java修改为@ExcelProperty注解

package com.ruoyi.system.domain;

import java.util.Date;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.ruoyi.common.core.domain.BaseEntity;
import com.ruoyi.system.domain.read.BusiTypeStringNumberConverter;
import com.ruoyi.system.domain.read.OperTypeConverter;
import com.ruoyi.system.domain.read.StatusConverter;

/**
 * 操作日志记录表 oper_log
 * 
 * @author ruoyi
 */
@ExcelIgnoreUnannotated
@ColumnWidth(16)
@HeadRowHeight(14)
@HeadFontStyle(fontHeightInPoints = 11)
public class SysOperLog extends BaseEntity
{
    private static final long serialVersionUID = 1L;

    /** 日志主键 */
    @ExcelProperty(value = "操作序号")
    private Long operId;

    /** 操作模块 */
    @ExcelProperty(value = "操作模块")
    private String title;

    /** 业务类型（0其它 1新增 2修改 3删除） */
    @ExcelProperty(value = "业务类型", converter = BusiTypeStringNumberConverter.class)
    private Integer businessType;

    /** 业务类型数组 */
    private Integer[] businessTypes;

    /** 请求方法 */
    @ExcelProperty(value = "请求方法")
    private String method;

    /** 请求方式 */
    @ExcelProperty(value = "请求方式")
    private String requestMethod;

    /** 操作类别（0其它 1后台用户 2手机端用户） */
    @ExcelProperty(value = "操作类别", converter = OperTypeConverter.class)
    private Integer operatorType;

    /** 操作人员 */
    @ExcelProperty(value = "操作人员")
    private String operName;

    /** 部门名称 */
    @ExcelProperty(value = "部门名称")
    private String deptName;

    /** 请求url */
    @ExcelProperty(value = "请求地址")
    private String operUrl;

    /** 操作地址 */
    @ExcelProperty(value = "操作地址")
    private String operIp;

    /** 操作地点 */
    @ExcelProperty(value = "操作地点")
    private String operLocation;

    /** 请求参数 */
    @ExcelProperty(value = "请求参数")
    private String operParam;

    /** 返回参数 */
    @ExcelProperty(value = "返回参数")
    private String jsonResult;

    /** 操作状态（0正常 1异常） */
    @ExcelProperty(value = "状态", converter = StatusConverter.class)
    private Integer status;

    /** 错误消息 */
    @ExcelProperty(value = "错误消息")
    private String errorMsg;

    /** 操作时间 */
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "操作时间")
    private Date operTime;

    public Long getOperId()
    {
        return operId;
    }

    public void setOperId(Long operId)
    {
        this.operId = operId;
    }

    public String getTitle()
    {
        return title;
    }

    public void setTitle(String title)
    {
        this.title = title;
    }

    public Integer getBusinessType()
    {
        return businessType;
    }

    public void setBusinessType(Integer businessType)
    {
        this.businessType = businessType;
    }

    public Integer[] getBusinessTypes()
    {
        return businessTypes;
    }

    public void setBusinessTypes(Integer[] businessTypes)
    {
        this.businessTypes = businessTypes;
    }

    public String getMethod()
    {
        return method;
    }

    public void setMethod(String method)
    {
        this.method = method;
    }

    public String getRequestMethod()
    {
        return requestMethod;
    }

    public void setRequestMethod(String requestMethod)
    {
        this.requestMethod = requestMethod;
    }

    public Integer getOperatorType()
    {
        return operatorType;
    }

    public void setOperatorType(Integer operatorType)
    {
        this.operatorType = operatorType;
    }

    public String getOperName()
    {
        return operName;
    }

    public void setOperName(String operName)
    {
        this.operName = operName;
    }

    public String getDeptName()
    {
        return deptName;
    }

    public void setDeptName(String deptName)
    {
        this.deptName = deptName;
    }

    public String getOperUrl()
    {
        return operUrl;
    }

    public void setOperUrl(String operUrl)
    {
        this.operUrl = operUrl;
    }

    public String getOperIp()
    {
        return operIp;
    }

    public void setOperIp(String operIp)
    {
        this.operIp = operIp;
    }

    public String getOperLocation()
    {
        return operLocation;
    }

    public void setOperLocation(String operLocation)
    {
        this.operLocation = operLocation;
    }

    public String getOperParam()
    {
        return operParam;
    }

    public void setOperParam(String operParam)
    {
        this.operParam = operParam;
    }

    public String getJsonResult()
    {
        return jsonResult;
    }

    public void setJsonResult(String jsonResult)
    {
        this.jsonResult = jsonResult;
    }

    public Integer getStatus()
    {
        return status;
    }

    public void setStatus(Integer status)
    {
        this.status = status;
    }

    public String getErrorMsg()
    {
        return errorMsg;
    }

    public void setErrorMsg(String errorMsg)
    {
        this.errorMsg = errorMsg;
    }

    public Date getOperTime()
    {
        return operTime;
    }

    public void setOperTime(Date operTime)
    {
        this.operTime = operTime;
    }

    @Override
    public String toString() {
        return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE)
            .append("operId", getOperId())
            .append("title", getTitle())
            .append("businessType", getBusinessType())
            .append("businessTypes", getBusinessTypes())
            .append("method", getMethod())
            .append("requestMethod", getRequestMethod())
            .append("operatorType", getOperatorType())
            .append("operName", getOperName())
            .append("deptName", getDeptName())
            .append("operUrl", getOperUrl())
            .append("operIp", getOperIp())
            .append("operLocation", getOperLocation())
            .append("operParam", getOperParam())
            .append("status", getStatus())
            .append("errorMsg", getErrorMsg())
            .append("operTime", getOperTime())
            .toString();
    }
}

5、添加字符串翻译内容

主要是这个地方，若依官网是2.2.6版本，与3.0.x版本的converter写法有一定的改变
自定义converter路径：

（1）、ruoyi-system\com\ruoyi\system\domain\read\BusiTypeStringNumberConverter.java

package com.ruoyi.system.domain.read;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;


/**
 * 业务类型字符串处理
 *
 * @author ruoyi
 */
@SuppressWarnings("rawtypes")
public class BusiTypeStringNumberConverter implements Converter<Integer> {

    @Override
    public Class supportJavaTypeKey()
    {
        return Integer.class;
    }

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

    @Override
    public Integer convertToJavaData(ReadConverterContext context)
    {
        Integer value = 0;
        String str = context.getReadCellData().getStringValue();
        if ("新增".equals(str))
        {
            value = 1;
        }
        else if ("修改".equals(str))
        {
            value = 2;
        }
        else if ("删除".equals(str))
        {
            value = 3;
        }
        else if ("授权".equals(str))
        {
            value = 4;
        }
        else if ("导出".equals(str))
        {
            value = 5;
        }
        else if ("导入".equals(str))
        {
            value = 6;
        }
        else if ("强退".equals(str))
        {
            value = 7;
        }
        else if ("生成代码".equals(str))
        {
            value = 8;
        }
        else if ("清空数据".equals(str))
        {
            value = 9;
        }
        return value;
    }

    @Override
    public WriteCellData convertToExcelData(WriteConverterContext<Integer> context)
    {
        String str = "其他";
        if (1 == context.getValue())
        {
            str = "新增";
        }
        else if (2 == context.getValue())
        {
            str = "修改";
        }
        else if (3 == context.getValue())
        {
            str = "删除";
        }
        else if (4 == context.getValue())
        {
            str = "授权";
        }
        else if (5 == context.getValue())
        {
            str = "导出";
        }
        else if (6 == context.getValue())
        {
            str = "导入";
        }
        else if (7 == context.getValue())
        {
            str = "强退";
        }
        else if (8 == context.getValue())
        {
            str = "生成代码";
        }
        else if (9 == context.getValue())
        {
            str = "清空数据";
        }
        return new WriteCellData(str);
    }
}

（2）、ruoyi-system\com\ruoyi\system\domain\read\OperTypeConverter.java

package com.ruoyi.system.domain.read;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

/**
 * 操作类别字符串处理
 *
 * @author ruoyi
 */
@SuppressWarnings("rawtypes")
public class OperTypeConverter implements Converter<Integer>
{
    @Override
    public Class supportJavaTypeKey()
    {
        return Integer.class;
    }

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

    @Override
    public Integer convertToJavaData(ReadConverterContext context)
    {
        Integer value = 0;
        String str = context.getReadCellData().getStringValue();
        if ("后台用户".equals(str))
        {
            value = 1;
        }
        else if ("手机端用户".equals(str))
        {
            value = 2;
        }
        return value;
    }

    @Override
    public WriteCellData convertToExcelData(WriteConverterContext<Integer> context)
    {
        String str = "其他";
        if (1 == context.getValue())
        {
            str = "后台用户";
        }
        else if (2 == context.getValue())
        {
            str = "手机端用户";
        }
        return new WriteCellData(str);
    }
}

（3）、ruoyi-system\com\ruoyi\system\domain\read\StatusConverter.java

package com.ruoyi.system.domain.read;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

/**
 * 状态字符串处理
 *
 * @author ruoyi
 */
@SuppressWarnings("rawtypes")
public class StatusConverter implements Converter<Integer>
{
    @Override
    public Class supportJavaTypeKey()
    {
        return Integer.class;
    }

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

    @Override
    public WriteCellData convertToExcelData(WriteConverterContext<Integer> context)
    {
        return new WriteCellData(0 == context.getValue() ? "正常" : "异常");
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext cellData)
    {
        return "正常".equals(cellData.getReadCellData().getStringValue()) ? 1 : 0;
    }
}