晋江网站有什么职业做,容桂手机网站建设,郑州网站建设维护公司,如何上传模板到网站引入pom依赖 dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactId/dependency实现功能 结合Vue前端#xff0c;实现浏览器页面直接导出日志文件实现文件的灵活导入文件导出 3. 实体类
实体类里有自定义转…引入pom依赖 dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactId/dependency实现功能 结合Vue前端实现浏览器页面直接导出日志文件实现文件的灵活导入文件导出 3. 实体类
实体类里有自定义转换器用于Java类型数据和Excel类型数据的转换非常使用。结合注解可以非常方便的进行Excel文件导出。/*** p* 操作日志信息* /p** home.php?modspaceuid686208 horse* home.php?modspaceuid441028 2020-09-08* 注意: 实体类中如果使用Accessorychaintrue,那么导入的数据无法填充到实例中,导出数据不受影响*/
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;ExcelIgnoreApiModelProperty(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.1 接口
OperatingLog(operType BlogConstants.EXPORT, operDesc 导出操作日志,写出到响应流中)ApiOperation(value 导出操作日志, hidden true)PostMapping(/oper/export)public void operLogExport(RequestBody ListString logIds, HttpServletResponse response) {operationalLogService.operLogExport(logIds, response);}4.2 具体实现 自定义导出策略HorizontalCellStyleStrategy自定义导出拦截器CellWriteHandler更加精确的自定义导出策略/*** 导出操作日志(可以考虑分页导出)** param logIds* param response*/Overridepublic void operLogExport(ListString logIds, HttpServletResponse response) {OutputStream outputStream null;try {ListOperationalLog operationalLogs;LambdaQueryWrapperOperationalLog queryWrapper new LambdaQueryWrapperOperationalLog().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);Overridepublic 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*/Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,Head head, Integer relativeRowIndex, Boolean isHead) {}Overridepublic 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*/Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,ListCellData 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 前端请求
前端在基于VueElement的基础上实现了点击导出按钮在浏览器页面进行下载。// 批量导出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;charsetutf-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 pdfUrllink.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 MyExcelImportConfigT extends AnalysisEventListenerT {private static Logger logger LoggerFactory.getLogger(MyExcelImportConfig.class);/*** 每次读取的最大数据条数*/private static final int MAX_BATCH_COUNT 10;/*** 泛型bean属性*/private T dynamicService;/*** 可接收任何参数的泛型List集合*/ListT list new ArrayList();/*** 构造函数注入bean(根据传入的bean动态注入)** param dynamicService*/public MyExcelImportConfig(T dynamicService) {this.dynamicService dynamicService;}/*** 解析每条数据都进行调用** param data* param context*/Overridepublic void invoke(T data, AnalysisContext context) {logger.info( 解析一条数据: {}, JacksonUtils.objToString(data));list.add(data);if (list.size() MAX_BATCH_COUNT) {// 保存数据saveData();// 清空listlist.clear();}}/*** 所有数据解析完成后,会来调用一次* 作用: 避免最后集合中小于 MAX_BATCH_COUNT 条的数据没有被保存** param context*/Overridepublic 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*/Overridepublic 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());}}}
读取测试 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(文件导入成功);}附上自定义属性转换器 转换器的属性内容转换需要根据自己的实际业务需求而定这里仅作为简单示例
/*** author Mr.Horse* version 1.0* description: 自定义excel转换器: 将操作日志的请求耗时加上单位 ms* date 2021/4/27 10:25*/public class CustomRequestTimeConverter implements ConverterLong {/*** 读取数据时: 属性对应的java数据类型** return*/Overridepublic ClassLong supportJavaTypeKey() {return Long.class;}/*** 写入数据时: excel内部的数据类型,因为请求耗时是long类型,对应excel是NUMBER类型,但是加上ms后对应的是STRING类型** return*/Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/*** 读取回调** param cellData* param contentProperty* param globalConfiguration* return* throws Exception*/Overridepublic 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));}Overridepublic CellDataLong convertToExcelData(Long value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {// 添加字符串: msreturn 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 ConverterDate {Overridepublic ClassDate supportJavaTypeKey() {return Date.class;}Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}Overridepublic Date convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {String value cellData.getStringValue();return DateUtil.parse(value, DatePattern.NORM_DATETIME_PATTERN);}Overridepublic CellDataDate convertToExcelData(Date value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return new CellData(DateUtil.format(value, DatePattern.NORM_DATETIME_PATTERN));}
}
