Springboot+easyExcel 导出业务excel(利

作者: 代码届的泥石流sc | 来源:发表于2023-02-16 11:29 被阅读0次

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

image.png

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

1.pom.xml

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.1.3</version>
</dependency>

2.VO和相关的转换类

2.1 工单VO

/**
 * Description: $
 * <br/>
 * $
 *
 * @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: 工单状态转换
 * <br/>
 * $
 *
 * @author 泥石流
 * @date 2023/1/9 12:31
 */
public class ComplaintStatusConvert implements Converter<Integer> {

    @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<Integer> context) {
        return new WriteCellData<String>(WorkOrderStatusEnum.getValue(context.getValue()));
    }
}

2.3 工单状态枚举

/**
 * Description: $
 * <br/>
 * $
 *
 * @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<WorkOrderVO> workOrderVOS = workOrderMapper.queryList(dto);
        //数据库VO 和 需要的导出字段VO 转换
        List<WorkOrderExcelVO> 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

完成。

Springboot+easyExcel 导出业务excel(利

1.pom.xml

2.VO和相关的转换类

2.1 工单VO

2.2 convert

3.业务代码

4.利用postman调用

结果展示：

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读