若依集成easyexcel实现excel表格增强（八十）
如果默认的excel注解已经满足不了你的需求，可以使用excel的增强解决方案easyexcel，它是阿里巴巴开源的一个excel处理框架，使用简单、功能特性多、以节省内存著称。
官网地址：https://easyexcel.opensource.alibaba.com/docs/current/
1、ruoyi-common\pom.xml模块添加整合依赖
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
2、ExcelUtil.java新增easyexcel导出导入方法
import com.alibaba.excel.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 AjaxResult exportEasyExcel(List<T> list, String sheetName)
{
String filename = encodingFilename(sheetName);
EasyExcel.write(getAbsoluteFile(filename), clazz).sheet(sheetName).doWrite(list);
return AjaxResult.success(filename);
}
3、模拟测试，以操作日志为例，修改相关类
SysOperlogController.java改为exportEasyExcel
@Log(title = "操作日志", businessType = BusinessType.EXPORT)
@RequiresPermissions("monitor:operlog:export")
@PostMapping("/export")
@ResponseBody
public AjaxResult export(SysOperLog operLog)
{
List<SysOperLog> list = operLogService.selectOperLogList(operLog);
ExcelUtil<SysOperLog> util = new ExcelUtil<SysOperLog>(SysOperLog.class);
return util.exportEasyExcel(list, "操作日志");
}
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
*
*/
@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();
}
}
添加字符串翻译内容：
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.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
/**
* 业务类型字符串处理
*
*/
@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(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration)
{
Integer value = 0;
String str = cellData.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 CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration)
{
String str = "其他";
if (1 == value)
{
str = "新增";
}
else if (2 == value)
{
str = "修改";
}
else if (3 == value)
{
str = "删除";
}
else if (4 == value)
{
str = "授权";
}
else if (5 == value)
{
str = "导出";
}
else if (6 == value)
{
str = "导入";
}
else if (7 == value)
{
str = "强退";
}
else if (8 == value)
{
str = "生成代码";
}
else if (9 == value)
{
str = "清空数据";
}
return new CellData(str);
}
}
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.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
/**
* 操作类别字符串处理
*
*/
@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(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration)
{
Integer value = 0;
String str = cellData.getStringValue();
if ("后台用户".equals(str))
{
value = 1;
}
else if ("手机端用户".equals(str))
{
value = 2;
}
return value;
}
@Override
public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration)
{
String str = "其他";
if (1 == value)
{
str = "后台用户";
}
else if (2 == value)
{
str = "手机端用户";
}
return new CellData(str);
}
}
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.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
/**
* 状态字符串处理
*
*/
@SuppressWarnings("rawtypes")
public class StatusConverter implements Converter<Integer>
{
@Override
public Class supportJavaTypeKey()
{
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey()
{
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration)
{
return "正常".equals(cellData.getStringValue()) ? 1 : 0;
}
@Override
public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration)
{
return new CellData(0 == value ? "正常" : "异常");
}
}