EasyExcel导出 -- 学习记录

惬意か

已于 2024-05-10 11:57:53 修改

阅读量198

点赞数 1

文章标签：学习 java excel

于 2024-05-10 11:54:45 首次发布

本文链接：https://blog.csdn.net/cth1224/article/details/138659042

版权

<!-- easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

开始时，用的时2.x的版本。导出时自定义Converter转换，老是出错。于是决定换成高版本。

实体类

@ExcelIgnoreUnannotated
@ColumnWidth(16)
@HeadRowHeight(14)
@HeadFontStyle(fontHeightInPoints = 11)
@Data
public class LotteryRecordAliExcel implements Serializable {

    @ExcelProperty(value = "手机号" ,index = 0)
    private String mobile;

    @ExcelProperty(value = "昵称",index = 1)
    private String name;

    @ExcelProperty(value = "ureid",index = 2)
    private String userId;

    @ExcelProperty(value = "活动名称",index = 3)
    private String actTitle;

    @ExcelProperty(value = "活动类型" ,index = 4)
    private String actType;

    @ExcelProperty(value = "卡券批次",index = 5)
    private String code;

    @ExcelProperty(value = "优惠金额" , index = 6)
    private BigDecimal money;

    @ExcelProperty(value = "订单金额",index = 7)
    private BigDecimal orderMoney;

    @ExcelProperty(value = "订单号",index = 8)
    private String orderNo;

    @ExcelProperty(value = "奖品状态",index = 9,converter = GiftStatusConverter.class)
    private Integer giftStatus;

    @ExcelProperty(value = "核销商户",index = 10)
    private String receivedId;

    @ColumnWidth(30)
    @ExcelProperty(value = "发放时间" , index = 11,converter = DateConverter.class)
    private Date createdAt;

    @ColumnWidth(30)
    @ExcelProperty(value = "核销时间" , index = 12,converter = DateConverter.class)
    private Date receivedAt;

    @ExcelProperty(value = "领取厂房",index = 13 ,converter = TenantConverter.class)
    private Integer tenantId;

}

自定义转换

时间

@DateTimeFormat("yyyy-MM-dd HH:mm:ss")

输出的结果： #########

采用自定义时间类型转换

public class DateConverter implements Converter<Date> {
    private static final String PATTERN_YYYY_MM_DD = "yyyy-MM-dd HH:mm:ss";

    @Override
    public Class<Date> supportJavaTypeKey() {
        return Date.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * EasyExcel导出数据类型转换
     * @param cellData
     * @param contentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public Date convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String value = cellData.getStringValue();
        SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_YYYY_MM_DD);
        Date parse = sdf.parse(value);
        return parse;
    }

    @Override
    public WriteCellData<?> convertToExcelData(Date date, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if (date == null) {
            return null;
        }
        SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_YYYY_MM_DD);
        return new WriteCellData<>(sdf.format(date));
    }

}

other

@SuppressWarnings("rawtypes")
public class GiftStatusConverter implements Converter<Integer>{

    @Override
    public Class supportJavaTypeKey()
    {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey()
    {
        return CellDataTypeEnum.STRING;
    }
    /**
     * -1=过期,0=待激活,1=激活,2=使用中,3=下架,4=上架
     */
    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
                                     GlobalConfiguration globalConfiguration)
    {
        Integer value = 0;
        String str = cellData.getStringValue();
        if ("过期".equals(str))
        {
            value = -1;
        }
        else if ("激活".equals(str))
        {
            value = 1;
        }
        else if ("使用中".equals(str))
        {
            value = 2;
        }
        else if ("下架".equals(str))
        {
            value = 3;
        }
        else if ("上架".equals(str))
        {
            value = 4;
        }
        else if ("待激活".equals(str))
        {
            value = 0;
        }
        return value;
    }

    /***
     * 导入
     * @param value
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    /**
     * -1=过期,0=待激活,1=激活,2=使用中,3=下架,4=上架
     */
    @Override
    public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String str = "待激活";
        if (value.equals(-1))
        {
            str = "过期";
        }
        else if (value.equals(1))
        {
            str = "激活";
        }
        else if (value.equals(2))
        {
            str = "使用中";
        }
        else if (value.equals(3))
        {
            str = "下架";
        }
        else if (value.equals(4))
        {
            str = "上架";
        }
        else if (value.equals(0)) {
            str = "待激活";
        }
        return new WriteCellData<>(str);
    }

}

EasyExcel 导出

    @PostMapping("/excel/record/list")
    public void recordExport(HttpServletResponse response,LotteryRecordExcelVo lotteryRecordExcelVo) throws IOException {
        //设置内容类型
        response.setContentType("application/vnd.ms-excel");
        //设置编码格式
        response.setCharacterEncoding("utf-8");
        //设置导出文件名称（避免乱码）
        String fileName = URLEncoder.encode("记录列表", "UTF-8");
        // 设置响应头
        response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
        LotteryRecordVo lotteryRecordVo = new LotteryRecordVo();
        BeanUtils.copyProperties(lotteryRecordExcelVo,lotteryRecordVo);
        AjaxResult resultTotal = recordFeign.getGiftList(lotteryRecordVo,null,null);
        Long total = 0L;
        if (resultTotal.get("data") != null) {
            Map<String, Object> resultMap = (Map<String, Object>) resultTotal.get("data");
            total = Long.valueOf(String.valueOf(resultMap.get("total")));
        } else {
            throw new RuntimeException(resultTotal.get("msg").toString());
        }
        ExcelWriter excelWriter = null;
        try {
            switch (lotteryRecordExcelVo.getType()){
                case 1:
                    //微信（自贩机）
                    excelWriter = EasyExcel.write(response.getOutputStream(), LotteryRecordWechatExcel.class).build();
                    break;
                case 2:
                    //支付宝
                    excelWriter = EasyExcel.write(response.getOutputStream(), LotteryRecordAliExcel.class).build();
                    break;
                case 3:
                    //第三方卡券
                    excelWriter = EasyExcel.write(response.getOutputStream(), LotteryRecordThirdExcel.class).build();
                    break;
                case 4:
                    //商城券
                    excelWriter = EasyExcel.write(response.getOutputStream(), LotteryRecordShopExcel.class).build();
                    break;
                case 5:
                    //自贩机核销卡券
                    excelWriter = EasyExcel.write(response.getOutputStream(), LotteryRecordReceiveExcel.class).build();
                    break;
                case 6:
                    //实物线下核销
                    excelWriter = EasyExcel.write(response.getOutputStream(), LotteryRecordOfflineExcel.class).build();
                    break;
                case 7:
                    //实物邮递
                    excelWriter = EasyExcel.write(response.getOutputStream(), LotteryRecordPostalExcel.class).build();
                    break;
                default:
                    break;
            }
            if (excelWriter == null) {
                throw new RuntimeException("EasyExcel创建失败！");
            }
            //创建excel 同一个只能创建一次 sheet
            WriteSheet writeSheet =new WriteSheet();
            writeSheet.setSheetName("记录列表");
            //调用接口获取数据
            Integer pageNum = 1;
            while (total != 0L) {
                BeanUtils.copyProperties(lotteryRecordExcelVo,lotteryRecordVo);
                AjaxResult result = recordFeign.getGiftList(lotteryRecordVo,100,pageNum);
                if (result.get("data") != null) {
                    Map<String, Object> resultMap = (Map<String, Object>) result.get("data");
                    Object rows = resultMap.get("rows");
                    if (rows instanceof List<?>) {
                        List<?> list = (List<?>) rows;
                        total = total - list.size();
                        //结果映射
                        switch (lotteryRecordExcelVo.getType()){
                            case 1:
                                //微信（自贩机）
                                List<LotteryRecordWechatExcel> wechatExcelList = wechatResultMap(list);
                                //excel表格输出
                                excelWriter.write(wechatExcelList, writeSheet);
                                break;
                            case 2:
                                //支付宝
                                List<LotteryRecordAliExcel> aliExcelList = aliResultMap(list);
                                excelWriter.write(aliExcelList, writeSheet);
                                break;
                            case 3:
                                //第三方卡券
                                List<LotteryRecordThirdExcel> thirdExcelList = thirdResultMap(list);
                                excelWriter.write(thirdExcelList, writeSheet);
                                break;
                            case 4:
                                //商城券
                                List<LotteryRecordShopExcel> shopExcelList = shopResultMap(list);
                                excelWriter.write(shopExcelList, writeSheet);
                                break;
                            case 5:
                                //自贩机核销卡券
                                List<LotteryRecordReceiveExcel> receiveExcelList = receiveResultMap(list);
                                excelWriter.write(receiveExcelList, writeSheet);
                                break;
                            case 6:
                                //实物线下核销
                                List<LotteryRecordOfflineExcel> offlineExcelList = offlineResultMap(list);
                                excelWriter.write(offlineExcelList, writeSheet);
                                break;
                            case 7:
                                //实物邮递
                                List<LotteryRecordPostalExcel> postalExcelList = postalResultMap(list);
                                excelWriter.write(postalExcelList, writeSheet);
                                break;
                            default:
                                break;
                        }
                    } else {
                        // obj不是List类型
                        total = 0L;
                        throw new RuntimeException("返回结果类型不匹配");
                    }
                    pageNum ++;
                }
            }
        } finally {
            excelWriter.finish();
        }
    }

根据分页循环，追加到excel表中，必须且只创建一个sheet。

wechatResultMap(list); -- 数据转换方法