准备工作
注意：点击查看官网Demo
1. 引入pom依赖
<!--easyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
</dependency>
2. 实现功能
- 结合Vue前端，实现浏览器页面直接导出日志文件
- 实现文件的导入
Excel文件下载
3. 日志实体类
实体类里有自定义转换器：用于Java类型数据和Excel类型数据的转换，非常使用。结合注解，可以非常方便的进行Excel文件导出。
@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，更加精确的自定义导出策略
@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);
}
}
自定义导出策略简单如下：
public class MyCellStyleStrategy {
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);
}
}
自定义导出拦截器简单如下：
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) {
}
@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) {
}
@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. 文件读取配置
本配置基于泛型的方式编写，可扩展性较强。
public class MyExcelImportConfig<T> extends AnalysisEventListener<T> {
private static Logger logger = LoggerFactory.getLogger(MyExcelImportConfig.class);
private static final int MAX_BATCH_COUNT = 10;
private T dynamicService;
List<T> list = new ArrayList<>();
public MyExcelImportConfig(T dynamicService) {
this.dynamicService = dynamicService;
}
@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();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
logger.info(" ==> 数据解析完成 <==");
}
private void saveData() {
logger.info(" ==> 数据保存开始: {}", list.size());
list.forEach(System.out::println);
logger.info(" ==> 数据保存结束 <==");
}
@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. 附上自定义属性转换器
转换器的属性内容转换，需要根据自己的实际业务需求而定，这里仅作为简单示例
public class CustomRequestTimeConverter implements Converter<Long> {
@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) 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"));
}
}
格式化时间
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的资料请关注编程网其它相关文章！
