准备工作
注意：点击查看官网demo
1. 引入pom依赖
<!--easyexcel-->
<dependency>
<groupid>com.alibaba</groupid>
<artifactid>easyexcel</artifactid>
</dependency>
2. 实现功能
- 结合vue前端，实现浏览器页面直接导出日志文件
- 实现文件的导入
excel文件下载
3. 日志实体类
实体类里有自定义转换器：用于java类型数据和excel类型数据的转换，非常使用。结合注解，可以非常方便的进行excel文件导出。
/**
* <p>
* 操作日志信息
* </p>
*
* @author horse
* @since 2020-09-08
* 注意: 实体类中如果使用@accessory（chain=true）,那么导入的数据无法填充到实例中,导出数据不受影响
*/
@data
@equalsandhashcode(callsuper = false)
@tablename("tb_operational_log")
@apimodel(value = "operationallog对象", description = "操作日志信息")
public class operationallog implements serializable {
private static final long serialversionuid = 1l;
@excelproperty({"操作日志", "日志id"})
@apimodelproperty(value = "日志id")
@tableid(value = "id", type = idtype.assign_id)
private string id;
@excelproperty({"操作日志", "操作类型"})
@apimodelproperty(value = "操作类型")
private string opertype;
@excelproperty({"操作日志", "操作描述"})
@apimodelproperty(value = "操作描述")
private string operdesc;
@excelproperty({"操作日志", "操作员id"})
@apimodelproperty(value = "操作员id")
private string operuserid;
@excelproperty({"操作日志", "操作员名称"})
@apimodelproperty(value = "操作员名称")
private string operusername;
@excelproperty({"操作日志", "操作方法"})
@apimodelproperty(value = "操作方法")
private string opermethod;
@excelproperty({"操作日志", "请求方法"})
@apimodelproperty(value = "请求方法")
private string operrequway;
@excelproperty(value = {"操作日志", "请求耗时：单位-ms"}, converter = customrequesttimeconverter.class)
@apimodelproperty(value = "请求耗时：单位-ms")
private long operrequtime;
@excelproperty({"操作日志", "请求参数"})
@apimodelproperty(value = "请求参数")
private string operrequparams;
@excelproperty({"操作日志", "请求body"})
@apimodelproperty(value = "请求body")
private string operrequbody;
@excelproperty({"操作日志", "请求ip"})
@apimodelproperty(value = "请求ip")
private string operrequip;
@excelproperty({"操作日志", "请求url"})
@apimodelproperty(value = "请求url")
private string operrequurl;
@excelproperty(value = {"操作日志", "日志标识"}, converter = customlogflagconverter.class)
@apimodelproperty(value = "日志标识: 1-admin,0-portal")
private boolean logflag;
@excelproperty({"操作日志", "操作状态"})
@apimodelproperty(value = "操作状态:1-成功,0-失败")
@tablefield(value = "is_success")
private boolean success;
@excelignore
@apimodelproperty(value = "逻辑删除 1-未删除， 0-删除")
@tablefield(value = "is_deleted")
@tablelogic(value = "1", delval = "0")
private boolean deleted;
@excelproperty(value = {"操作日志", "创建时间"}, converter = customtimeformatconverter.class)
@apimodelproperty(value = "创建时间")
private date gmtcreate;
}
4. 接口和具体实现
4.1 接口
@operatinglog(opertype = blogconstants.export, operdesc = "导出操作日志,写出到响应流中")
@apioperation(value = "导出操作日志", hidden = true)
@postmapping("/oper/export")
public void operlogexport(@requestbody list<string> logids, httpservletresponse response) {
operationallogservice.operlogexport(logids, response);
}
4.2 具体实现
- 自定义导出策略horizontalcellstylestrategy
- 自定义导出拦截器cellwritehandler，更加精确的自定义导出策略
/**
* 导出操作日志(可以考虑分页导出)
*
* @param logids
* @param response
*/
@override
public void operlogexport(list<string> logids, httpservletresponse response) {
outputstream outputstream = null;
try {
list<operationallog> operationallogs;
lambdaquerywrapper<operationallog> querywrapper = new lambdaquerywrapper<operationallog>()
.orderbydesc(operationallog::getgmtcreate);
// 如果logids不为null,按照id查询信息,否则查询全部
if (!collectionutils.isempty(logids)) {
operationallogs = this.listbyids(logids);
} else {
operationallogs = this.list(querywrapper);
}
outputstream = response.getoutputstream();
// 获取单元格样式
horizontalcellstylestrategy strategy = mycellstylestrategy.gethorizontalcellstylestrategy();
// 写入响应输出流数据
easyexcel.write(outputstream, operationallog.class).exceltype(exceltypeenum.xlsx).sheet("操作信息日志")
// .registerwritehandler(new longestmatchcolumnwidthstylestrategy()) // 自适应列宽(不是很适应,效果并不佳)
.registerwritehandler(strategy) // 注册上面设置的格式策略
.registerwritehandler(new customcellwritehandler()) // 设置自定义格式策略
.dowrite(operationallogs);
} catch (exception e) {
log.error(exceptionutils.getmessage(e));
throw new blogexception(resultcodeenum.excel_data_export_error);
} finally {
ioutil.close(outputstream);
}
}
自定义导出策略简单如下：
/**
* @author mr.horse
* @version 1.0
* @description: 单元格样式策略
* @date 2021/4/30 8:43
*/
public class mycellstylestrategy {
/**
* 设置单元格样式(仅用于测试)
*
* @return 样式策略
*/
public static horizontalcellstylestrategy gethorizontalcellstylestrategy() {
// 表头策略
writecellstyle headercellstyle = new writecellstyle();
// 表头水平对齐居中
headercellstyle.sethorizontalalignment(horizontalalignment.center);
// 背景色
headercellstyle.setfillforegroundcolor(indexedcolors.sky_blue.getindex());
writefont headerfont = new writefont();
headerfont.setfontheightinpoints((short) 14);
headercellstyle.setwritefont(headerfont);
// 自动换行
headercellstyle.setwrapped(boolean.false);
// 内容策略
writecellstyle contentcellstyle = new writecellstyle();
// 设置数据允许的数据格式,这里49代表所有可以都允许设置
contentcellstyle.setdataformat((short) 49);
// 设置背景色: 需要指定 fillpatterntype 为fillpatterntype.solid_foreground 不然无法显示背景颜色.头默认了 fillpatterntype所以可以不指定
contentcellstyle.setfillpatterntype(fillpatterntype.solid_foreground);
contentcellstyle.setfillforegroundcolor(indexedcolors.grey_40_percent.getindex());
// 设置内容靠左对齐
contentcellstyle.sethorizontalalignment(horizontalalignment.left);
// 设置字体
writefont contentfont = new writefont();
contentfont.setfontheightinpoints((short) 12);
contentcellstyle.setwritefont(contentfont);
// 设置自动换行
contentcellstyle.setwrapped(boolean.false);
// 设置边框样式和颜色
contentcellstyle.setborderleft(medium);
contentcellstyle.setbordertop(medium);
contentcellstyle.setborderright(medium);
contentcellstyle.setborderbottom(medium);
contentcellstyle.settopbordercolor(indexedcolors.red.getindex());
contentcellstyle.setbottombordercolor(indexedcolors.green.getindex());
contentcellstyle.setleftbordercolor(indexedcolors.yellow.getindex());
contentcellstyle.setrightbordercolor(indexedcolors.orange.getindex());
// 将格式加入单元格样式策略
return new horizontalcellstylestrategy(headercellstyle, contentcellstyle);
}
}
自定义导出拦截器简单如下：
/**
* @author mr.horse
* @version 1.0
* @description 实现cellwritehandler接口, 实现对单元格样式的精确控制
* @date 2021/4/29 21:11
*/
public class customcellwritehandler implements cellwritehandler {
private static logger logger = loggerfactory.getlogger(customcellwritehandler.class);
@override
public void beforecellcreate(writesheetholder writesheetholder, writetableholder writetableholder, row row,
head head, integer columnindex, integer relativerowindex, boolean ishead) {
}
/**
* 单元格创建之后(没有写入值)
*
* @param writesheetholder
* @param writetableholder
* @param cell
* @param head
* @param relativerowindex
* @param ishead
*/
@override
public void aftercellcreate(writesheetholder writesheetholder, writetableholder writetableholder, cell cell,
head head, integer relativerowindex, boolean ishead) {
}
@override
public void aftercelldataconverted(writesheetholder writesheetholder, writetableholder writetableholder,
celldata celldata, cell cell, head head, integer relativerowindex,
boolean ishead) {
}
/**
* 单元格处理后(已写入值): 设置第一行第一列的头超链接到easyexcel的官网(本系统的导出的excel 0,1两行都是头,所以只设置第一行的超链接)
* 这里再进行拦截的单元格样式设置的话,前面该样式将全部失效
*
* @param writesheetholder
* @param writetableholder
* @param celldatalist
* @param cell
* @param head
* @param relativerowindex
* @param ishead
*/
@override
public void aftercelldispose(writesheetholder writesheetholder, writetableholder writetableholder,
list<celldata> celldatalist, cell cell, head head, integer relativerowindex,
boolean ishead) {
// 设置超链接
if (ishead && cell.getrowindex() == 0 && cell.getcolumnindex() == 0) {
logger.info(" ==> 第{}行,第{}列超链接设置完成", cell.getrowindex(), cell.getcolumnindex());
creationhelper helper = writesheetholder.getsheet().getworkbook().getcreationhelper();
hyperlink hyperlink = helper.createhyperlink(hyperlinktype.url);
hyperlink.setaddress("https://github.com/alibaba/easyexcel");
cell.sethyperlink(hyperlink);
}
// 精确设置单元格格式
boolean bool = ishead && cell.getrowindex() == 1 &&
(cell.getstringcellvalue().equals("请求参数") || cell.getstringcellvalue().equals("请求body"));
if (bool) {
logger.info("第{}行，第{}列单元格样式设置完成。", cell.getrowindex(), cell.getcolumnindex());
// 获取工作簿
workbook workbook = writesheetholder.getsheet().getworkbook();
cellstyle cellstyle = workbook.createcellstyle();
font cellfont = workbook.createfont();
cellfont.setbold(boolean.true);
cellfont.setfontheightinpoints((short) 14);
cellfont.setcolor(indexedcolors.sea_green.getindex());
cellstyle.setfont(cellfont);
cell.setcellstyle(cellstyle);
}
}
}
4.3 前端请求
前端在基于vue+element的基础上实现了点击导出按钮，在浏览器页面进行下载。
// 批量导出
batchexport() {
// 遍历获取id集合列表
const logids = []
this.multipleselection.foreach(item => {
logids.push(item.id)
})
// 请求后端接口
axios({
url: this.base_api + '/admin/blog/log/oper/export',
method: 'post',
data: logids,
responsetype: 'arraybuffer',
headers: { 'token': gettoken() }
}).then(response => {
// type类型可以设置为文本类型，这里是新版excel类型
const blob = new blob([response.data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' })
const pdfurl = window.url.createobjecturl(blob)
const filename = 'horseblog操作日志' // 下载文件的名字
// 对于<a>标签，只有 firefox 和 chrome（内核）支持 download 属性
if ('download' in document.createelement('a')) {
const link = document.createelement('a')
link.href = pdfurl
link.setattribute('download', filename)
document.body.appendchild(link)
link.click()
window.url.revokeobjecturl(pdfurl) // 释放url 对象
} else {
// ie 浏览器兼容方法
window.navigator.mssaveblob(blob, filename)
}
})
}
测试结果：还行，基本实现了页面下载的功能
excel文件导入
5. 文件读取配置
本配置基于泛型的方式编写，可扩展性较强。
/**
* @author mr.horse
* @version 1.0
* @description: easyexcel文件读取配置(不能让spring管理)
* @date 2021/4/27 13:24
*/
public class myexcelimportconfig<t> extends analysiseventlistener<t> {
private static logger logger = loggerfactory.getlogger(myexcelimportconfig.class);
/**
* 每次读取的最大数据条数
*/
private static final int max_batch_count = 10;
/**
* 泛型bean属性
*/
private t dynamicservice;
/**
* 可接收任何参数的泛型list集合
*/
list<t> list = new arraylist<>();
/**
* 构造函数注入bean(根据传入的bean动态注入)
*
* @param dynamicservice
*/
public myexcelimportconfig(t dynamicservice) {
this.dynamicservice = dynamicservice;
}
/**
* 解析每条数据都进行调用
*
* @param data
* @param context
*/
@override
public void invoke(t data, analysiscontext context) {
logger.info(" ==> 解析一条数据: {}", jacksonutils.objtostring(data));
list.add(data);
if (list.size() > max_batch_count) {
// 保存数据
savedata();
// 清空list
list.clear();
}
}
/**
* 所有数据解析完成后,会来调用一次
* 作用: 避免最后集合中小于 max_batch_count 条的数据没有被保存
*
* @param context
*/
@override
public void doafterallanalysed(analysiscontext context) {
savedata();
logger.info(" ==> 数据解析完成 <==");
}
/**
* 保存数据: 正式应该插入数据库,这里用于测试
*/
private void savedata() {
logger.info(" ==> 数据保存开始: {}", list.size());
list.foreach(system.out::println);
logger.info(" ==> 数据保存结束 <==");
}
/**
* 在转换异常 获取其他异常下会调用本接口。我们如果捕捉并手动抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
*
* @param exception
* @param context
* @throws exception
*/
@override
public void onexception(exception exception, analysiscontext context) throws exception {
logger.error(" ==> 数据解析失败,但是继续读取下一行:{}", exception.getmessage());
// 如果是某一个单元格的转换异常 能获取到具体行号
if (exception instanceof exceldataconvertexception) {
exceldataconvertexception convertexception = (exceldataconvertexception) exception;
logger.error("第{}行，第{}列数据解析异常", convertexception.getrowindex(), convertexception.getcolumnindex());
}
}
}
6. 读取测试
@apioperation(value = "数据导入测试", notes = "操作日志导入测试[operationallog]", hidden = true)
@postmapping("/import")
public r excelimport(@requestparam("file") multipartfile file) throws ioexception {
easyexcel.read(file.getinputstream(), operationallog.class, new myexcelimportconfig<>(operationallogservice))
.sheet().doread();
return r.ok().message("文件导入成功");
}
7. 附上自定义属性转换器
转换器的属性内容转换，需要根据自己的实际业务需求而定，这里仅作为简单示例
/**
* @author mr.horse
* @version 1.0
* @description: 自定义excel转换器: 将操作日志的请求耗时加上单位 "ms"
* @date 2021/4/27 10:25
*/
public class customrequesttimeconverter implements converter<long> {
/**
* 读取数据时: 属性对应的java数据类型
*
* @return
*/
@override
public class<long> supportjavatypekey() {
return long.class;
}
/**
* 写入数据时: excel内部的数据类型,因为请求耗时是long类型,对应excel是number类型,但是加上"ms后对应的是string类型"
*
* @return
*/
@override
public celldatatypeenum supportexceltypekey() {
return celldatatypeenum.string;
}
/**
* 读取回调
*
* @param celldata
* @param contentproperty
* @param globalconfiguration
* @return
* @throws exception
*/
@override
public long converttojavadata(celldata celldata, excelcontentproperty contentproperty, globalconfiguration globalconfiguration) throws exception {
// 截取字符串: "ms",转换为long类型
string value = celldata.getstringvalue();
return long.valueof(value.substring(0, value.length() - 2));
}
@override
public celldata<long> converttoexceldata(long value, excelcontentproperty contentproperty, globalconfiguration globalconfiguration) throws exception {
// 添加字符串: "ms"
return new celldata<>(string.valueof(value).concat("ms"));
}
}
格式化时间
/**
* @author mr.horse
* @version 1.0
* @description: {description}
* @date 2021/4/27 14:01
*/
public class customtimeformatconverter implements converter<date> {
@override
public class<date> supportjavatypekey() {
return date.class;
}
@override
public celldatatypeenum supportexceltypekey() {
return celldatatypeenum.string;
}
@override
public date converttojavadata(celldata celldata, excelcontentproperty contentproperty, globalconfiguration globalconfiguration) throws exception {
string value = celldata.getstringvalue();
return dateutil.parse(value, datepattern.norm_datetime_pattern);
}
@override
public celldata<date> converttoexceldata(date value, excelcontentproperty contentproperty, globalconfiguration globalconfiguration) throws exception {
return new celldata<>(dateutil.format(value, datepattern.norm_datetime_pattern));
}
}
easyexcel简单使用，到此结束，打完收功。
以上就是springboot整合easyexcel实现文件导入导出的详细内容，更多关于springboot整合easyexcel的资料请关注www.887551.com其它相关文章！
黄山市民网：https://www.huangshanshimin.com/
