Springboot+easyExcel 導(dǎo)出業(yè)務(wù)excel(利用自定義convert + minio)

在工作有個需求需要導(dǎo)出工單的列表信息，大致原型如下：


image.png

采用阿里開源的easyExcel(https://easyexcel.opensource.alibaba.com/)，
并進(jìn)行數(shù)據(jù)-中文枚舉轉(zhuǎn)換convert,
系統(tǒng)中已經(jīng)搭建了minio oss , 如果不需要可以用java 自帶的文件流處理
話不多說開干：

1.pom.xml

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

2.VO和相關(guān)的轉(zhuǎn)換類

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 = "工單創(chuàng)建時間", index = 0)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createdTime;

    /**
     * 投訴人姓名
     */
    @ExcelProperty(value = "消費(fèi)者姓名", index = 1)
    private String complainantName;

    /**
     * 電話號碼
     */
    @ColumnWidth(value = 20)
    @ExcelProperty(value = "消費(fèi)者聯(lián)系方式", index = 2)
    private String complainantPhone;


    /**
     * 工單編號
     */
    @ExcelProperty(value = "工單號", index = 3)
    private String workOrderCode;


    /**
     * 投訴類型 1消費(fèi)投訴,2消費(fèi)舉報
     */
    @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;

    /**
     * 產(chǎn)品類型(FW,APP,HW&ACC)
     */
    @ExcelProperty(value = "產(chǎn)品類型", index = 8)
    private String productType;


    /**
     * 產(chǎn)品價格
     */
    @ExcelProperty(value = "產(chǎn)品價格", 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;

    /**
     * 店鋪詳細(xì)信息(TM,JD,OS,DouYin,PDD,Comfirm APP,WeChat PLT,Other
     )
     */
    @ColumnWidth(value = 18)
    @ExcelProperty(value = "店鋪類型詳情", index = 13)
    private String storeInfo;

    /**
     * 店鋪詳細(xì)信息備注
     */
    @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;


    /**
     * 工單內(nèi)容
     */
    @ColumnWidth(value = 30)
    @ExcelProperty(value = "投訴內(nèi)容（工單內(nèi)容)", index = 17)
    private String workOrderContent;


    /**
     * 預(yù)計辦結(jié)時間
     */
    @ColumnWidth(value = 20)
    @ExcelProperty(value = "預(yù)計辦結(jié)時間", 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;


    /**
     * 問題細(xì)分
     */
    @ExcelProperty(value = "問題細(xì)分", index = 20)
    private String problemSubTypeName;


    /**
     * 工單狀態(tài)(1-處理中,2-暫辦結(jié),3-辦結(jié))
     */
    @ExcelProperty(value = "工單狀態(tài)", index = 21, converter = ComplaintStatusConvert.class)
    private Integer workOrderStatus;

    /**
     * 結(jié)案總結(jié)
     */
    @ColumnWidth(value = 30)
    @ExcelProperty(value = "結(jié)案總結(jié)", index = 22)
    private String closingSummary;

    /**
     * 實際辦結(jié)時間
     */
    @ColumnWidth(value = 25)
    @ExcelProperty(value = "工單實際辦結(jié)時間", index = 23)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime actualDealTime;


    /**
     * 辦結(jié)時長（h）
     */
    @ColumnWidth(value = 18)
    @ExcelProperty(value = "辦結(jié)時長（h）", index = 24)
    private Long finishedHours;

    /**
     * 辦結(jié)天數(shù)（d）
     */
    @ColumnWidth(value = 18)
    @ExcelProperty(value = "辦結(jié)天數(shù)（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;


    /**
     * 問題細(xì)分
     */
    @ColumnWidth(value = 0)
    private Long problemSubType;


    /**
     * 辦結(jié)時長
     */
    @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: 工單狀態(tài)轉(zhuǎn)換
 * <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 工單狀態(tài)枚舉

/**
 * Description: $
 * <br/>
 * $
 *
 * @author 泥石流
 * @date 2023/1/9 12:34
 */
@Getter
@AllArgsConstructor
public enum WorkOrderStatusEnum {


    /**
     * 處理中
     */
    DEALING(1, "處理中"),

    /**
     * 暫辦結(jié)
     */
    TENTATIVE_SETTLEMENT(2, "暫辦結(jié)"),

    /**
     * 辦結(jié)
     */
    CONCLUDE(3, "辦結(jié)");


    /**
     * 根據(jù)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";
    }

    /**
     * 根據(jù)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.業(yè)務(wù)代碼

-----------------------------------------------------------------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);
        //數(shù)據(jù)庫VO 和 需要的導(dǎo)出字段VO 轉(zhuǎn)換
        List<WorkOrderExcelVO> vos = WorkOrderConvert.INSTANCE.convertExcelList(workOrderVOS);

        if (ObjectUtils.isEmpty(vos)) {
            return null;
        }
        //轉(zhuǎn)換問題類型
        convertProblemType(vos);
        //轉(zhuǎn)換工單級別-數(shù)據(jù)轉(zhuǎn)換
        convertLevel(vos);
        //轉(zhuǎn)換辦結(jié)信息
        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服務(wù) 
        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調(diào)用

image.png

結(jié)果展示：

image.png

完成。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容