Springboot+easyExcel 导出业务excel(利用自定义convert + minio)

#Springboot+easyExcel 导出业务excel(利用自定义convert + minio)| 来源: 网络整理| 查看: 265

在工作有个需求需要导出工单的列表信息，大致原型如下：

image.png

采用阿里开源的easyExcel(https://easyexcel.opensource.alibaba.com/)，并进行数据-中文枚举转换convert, 系统中已经搭建了minio oss , 如果不需要可以用java 自带的文件流处理话不多说开干：

1.pom.xml com.alibaba easyexcel 3.1.3 2.VO和相关的转换类 2.1 工单VO /** * Description: $ * * $ * * @author 泥石流 * @date 2023/1/4 15:12 */ @Data @ExcelSheet(name = "工单列表") @ColumnWidth(value = 15) @ContentRowHeight(value = 22) @ContentStyle(borderLeft = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN) @JsonInclude(JsonInclude.Include.NON_NULL) public class WorkOrderExcelVO { @ColumnWidth(value = 20) @ExcelProperty(value = "工单创建时间", index = 0) @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss") private LocalDateTime createdTime; /** * 投诉人姓名 */ @ExcelProperty(value = "消费者姓名", index = 1) private String complainantName; /** * 电话号码 */ @ColumnWidth(value = 20) @ExcelProperty(value = "消费者联系方式", index = 2) private String complainantPhone; /** * 工单编号 */ @ExcelProperty(value = "工单号", index = 3) private String workOrderCode; /** * 投诉类型 1消费投诉,2消费举报 */ @ExcelProperty(value = "投诉类型", index = 4, converter = ComplaintTypeConvert.class) private Integer complaintType; /** * 投诉来源(SH MSA,SZ MSA,SH ODR,CN ODR,SH 12315,Other) */ @ExcelProperty(value = "投诉来源", index = 5) private String complaintSource; /** * 来源备注 */ @ColumnWidth(value = 20) @ExcelProperty(value = "投诉来源备注", index = 6) private String complaintSourceRemark; /** * 货号 */ @ExcelProperty(value = "货号", index = 7) private String productCode; /** * 产品类型(FW,APP,HW&ACC) */ @ExcelProperty(value = "产品类型", index = 8) private String productType; /** * 产品价格 */ @ExcelProperty(value = "产品价格", index = 9) private BigDecimal productPrice; /** * 购买日期 */ @ColumnWidth(value = 20) @ExcelProperty(value = "购买日期", index = 10) @JsonFormat(pattern = "yyyy-MM-dd") @DateTimeFormat(pattern = "yyyy-MM-dd") private Date purchaseDate; /** * 订单号 */ @ExcelProperty(value = "投诉订单号", index = 11) private String orderCode; /** * 店铺类型(eCom,OR,Franchise,Other) */ @ExcelProperty(value = "店铺类型", index = 12) private String storeType; /** * 店铺详细信息(TM,JD,OS,DouYin,PDD,Comfirm APP,WeChat PLT,Other ) */ @ColumnWidth(value = 18) @ExcelProperty(value = "店铺类型详情", index = 13) private String storeInfo; /** * 店铺详细信息备注 */ @ColumnWidth(value = 25) @ExcelProperty(value = "店铺类型详情备注", index = 14) private String storeInfoRemark; /** * 工单级别(1-Normal,2-Urgent) */ @ExcelProperty(value = "工单级别", index = 15) private String workOrderLevelName; /** * 处理时限（d) */ @ColumnWidth(value = 18) @ExcelProperty(value = "处理时限（d)", index = 16) private Long dealDays; /** * 工单内容 */ @ColumnWidth(value = 30) @ExcelProperty(value = "投诉内容（工单内容)", index = 17) private String workOrderContent; /** * 预计办结时间 */ @ColumnWidth(value = 20) @ExcelProperty(value = "预计办结时间", index = 18) @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss") @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") private LocalDateTime expectDealTime; @ExcelProperty(value = "问题分类", index = 19) private String problemTypeName; /** * 问题细分 */ @ExcelProperty(value = "问题细分", index = 20) private String problemSubTypeName; /** * 工单状态(1-处理中,2-暂办结,3-办结) */ @ExcelProperty(value = "工单状态", index = 21, converter = ComplaintStatusConvert.class) private Integer workOrderStatus; /** * 结案总结 */ @ColumnWidth(value = 30) @ExcelProperty(value = "结案总结", index = 22) private String closingSummary; /** * 实际办结时间 */ @ColumnWidth(value = 25) @ExcelProperty(value = "工单实际办结时间", index = 23) @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss") @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") private LocalDateTime actualDealTime; /** * 办结时长（h） */ @ColumnWidth(value = 18) @ExcelProperty(value = "办结时长（h）", index = 24) private Long finishedHours; /** * 办结天数（d） */ @ColumnWidth(value = 18) @ExcelProperty(value = "办结天数（d）", index = 25) private Long finishedDays; /** * 超期时长（h） */ @ColumnWidth(value = 18) @ExcelProperty(value = "超期时长（h）", index = 26) private Long overHours; /** * 是否超期(0-否，1-是) */ @ColumnWidth(value = 0) // @ExcelProperty(value = "是否超期", index = 27, converter = ComplaintIsOverDateConvert.class) private Integer isOverDate; @ColumnWidth(value = 0) @ExcelProperty(value = "id") private Long id; /** * 客户ID */ @ExcelProperty("客户ID") @ColumnWidth(value = 0) private String consumerCode; /** * 工单级别(1-Normal,2-Urgent) */ @ColumnWidth(value = 0) @ExcelProperty(value = "工单级别level") private Integer workOrderLevel; /** * 问题分类 */ @ColumnWidth(value = 0) private Long problemType; /** * 问题细分 */ @ColumnWidth(value = 0) private Long problemSubType; /** * 办结时长 */ @ColumnWidth(value = 0) private Long dealHours; @ColumnWidth(value = 0) @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss") private LocalDateTime updatedTime; @ColumnWidth(value = 0) private String createdBy; @ColumnWidth(value = 0) private String updatedBy; } 2.2 convert /** * Description: 工单状态转换 * * $ * * @author 泥石流 * @date 2023/1/9 12:31 */ public class ComplaintStatusConvert implements Converter { @Override public Class supportJavaTypeKey() { return Integer.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public Integer convertToJavaData(ReadConverterContext context) { return WorkOrderStatusEnum.getCode(context.getReadCellData().getStringValue()); } @Override public WriteCellData convertToExcelData(WriteConverterContext context) { return new WriteCellData(WorkOrderStatusEnum.getValue(context.getValue())); } }

2.3 工单状态枚举

/** * Description: $ * * $ * * @author 泥石流 * @date 2023/1/9 12:34 */ @Getter @AllArgsConstructor public enum WorkOrderStatusEnum { /** * 处理中 */ DEALING(1, "处理中"), /** * 暂办结 */ TENTATIVE_SETTLEMENT(2, "暂办结"), /** * 办结 */ CONCLUDE(3, "办结"); /** * 根据code获取value * @param code * @return */ public static String getValue(Integer code) { for (WorkOrderStatusEnum en : WorkOrderStatusEnum.values()) { if (en.getCode().equals(code)) { return en.getValue(); } } return "error"; } /** * 根据value获取code * @param value * @return */ public static Integer getCode(String value){ for (WorkOrderStatusEnum en : WorkOrderStatusEnum.values()) { if (en.getValue().equals(value)) { return en.getCode(); } } return -1; } private Integer code; private String value; } 3.业务代码 -----------------------------------------------------------------exportWorkOrder--------------------------------------------------------------- @Override public WorkOrderExcelFileVO exportWorkOrder(LocalDateTime createTimeStart, LocalDateTime createTimeEnd) { WorkOrderQueryDTO dto = new WorkOrderQueryDTO(); dto.setCreateTimeStart(createTimeStart); dto.setCreateTimeEnd(createTimeEnd); dto.setSort("id desc"); List workOrderVOS = workOrderMapper.queryList(dto); //数据库VO 和需要的导出字段VO 转换 List vos = WorkOrderConvert.INSTANCE.convertExcelList(workOrderVOS); if (ObjectUtils.isEmpty(vos)) { return null; } //转换问题类型 convertProblemType(vos); //转换工单级别-数据转换 convertLevel(vos); //转换办结信息 convertFinishInfo(vos); ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); EasyExcel.write(byteArrayOutputStream, WorkOrderExcelVO.class) .sheet("work_order_list") .doWrite(vos); String fileName = System.currentTimeMillis() + ".xls"; MultipartFile file = new MockMultipartFile("file", fileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", byteArrayOutputStream.toByteArray()); // 上传到minio服务 String uploadFileUrl = awsService.uploadFile(file); // 返回文件url供前端下载 return WorkOrderExcelFileVO.builder() .fileName(fileName) .fileUrl(uploadFileUrl).build(); } -----------------------------------------------------------------uploadFile--------------------------------------------------------------- @Override @SneakyThrows(Exception.class) public String uploadFile(MultipartFile multiFile) { String type = null; if(multiFile.getOriginalFilename().contains(StringPool.DOT)){ type = multiFile.getOriginalFilename().substring(multiFile.getOriginalFilename().lastIndexOf(StringPool.DOT)+1); } String s3Key = StringConstants.PRODUCT_FILE + StringPool.SLASH + RandomUtil.generateNumber(2) + StringPool.SLASH + IdUtil.fastSimpleUUID()+ StringPool.DOT + type; amazonDefaultS3ClientProvider.getS3Client().putObject( PutObjectRequest.builder() .bucket(callCenterProperties.getS3BucketName()) .key(s3Key) .acl(ObjectCannedACL.PUBLIC_READ) .build(), RequestBody.fromInputStream(multiFile.getInputStream(), multiFile.getInputStream().available())); return callCenterProperties.getCloudFrontDomain() + s3Key; } -----------------------------------------------------------------AmazonDefaultS3ClientProvider--------------------------------------------------------------- public class AmazonDefaultS3ClientProvider { private final S3Client s3Client; private final S3AsyncClient s3AsyncClient; public AmazonDefaultS3ClientProvider(S3Client s3Client, S3AsyncClient s3AsyncClient) { this.s3Client = s3Client; this.s3AsyncClient = s3AsyncClient; } /** * 获取S3异步客户端 * * @return S3AsyncClient */ public S3AsyncClient getS3AsyncClient() { return this.s3AsyncClient; } /** * 获取S3同步客户端 * * @return S3Client */ public S3Client getS3Client() { return s3Client; } } 4.利用postman调用 image.png 结果展示： image.png

完成。

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章