JAVA关于EsayPoi导出Excel表格，自定义表头+固定表头处理

   纯记录！！！

自定义表头
List headList = setHeadRows(dynamicHeadList, orderExcelList, list);
Workbook workbook = ExcelExportUtil.exportExcel(params,headList, list);

固定表头
Workbook workbook = ExcelExportUtil.exportExcel(params, OpenActivityOrderExcelOut.class, orderExcelList);
 
一、导出方法

    /**
     * 导出excel
     *
     * @return
     */
    @PostMapping("exportOrderList")
    public Object exportOrderList(@RequestBody OpenActivityOrderQueryIn in, HttpServletResponse response) {

        List<OpenActivityOrderExcelOut> orderExcelList = new ArrayList<>();
        FileOutputStream fos = null;
        try {
            Page<OpenActivityOrderOut> retPage = userBuyOpenActivityOrderService.getOpenCoursesOrderPage(in);
            List<OpenActivityOrderOut> orderList = retPage.getRecords();
            Optional.ofNullable(orderList).filter(o -> o.size() > 0).orElseThrow(() -> new BusinessException("数据为空，无法导出"));
            List<String> dynamicHeadList = new ArrayList<>();
            for (OpenActivityOrderOut out : orderList) {
                OpenActivityOrderExcelOut orderExcelOut = new OpenActivityOrderExcelOut();
                BeanUtils.copyProperties(out, orderExcelOut);

                orderExcelOut.setUnitPrice((out.getUnitPrice().setScale(2)).toString());
                orderExcelOut.setBuyNum(String.valueOf(out.getBuyNum()));
                orderExcelOut.setOrderAmount((out.getOrderAmount().setScale(2)).toString());


                /** 设置标签 */
                CommunityUserBuyOpenActivityOrderTagIn tagIn = new CommunityUserBuyOpenActivityOrderTagIn();
                tagIn.setActivityOperationId(out.getOpenCoursesOperationId());
                tagIn.setOrderOperationId(out.getOperationId());
                tagIn.setUserOperationId(out.getUserOperationId());
                tagIn.setTagStyle(BDic.INPUT);
                List<CommunityUserBuyOpenActivityOrderTagOut> activityInputTagList = communityUserBuyOpenActivityOrderTagService.getByOrder(tagIn);
                Optional.ofNullable(activityInputTagList).filter(a -> a.size() > 0).ifPresent(a -> {
                    orderExcelOut.setActivityInputTagList(a);
                });
                tagIn.setTagStyle(BDic.CHECKBOX);
                List<CommunityUserBuyOpenActivityOrderTagOut> activityCheckBoxTagList = communityUserBuyOpenActivityOrderTagService.getByOrder(tagIn);
                Optional.ofNullable(activityCheckBoxTagList).filter(a -> a.size() > 0).ifPresent(a -> {
                    orderExcelOut.setActivityCheckBoxTagList(a);
                });


                orderExcelOut.setCreateTime(out.getCreateTime() != null ? DUtils.fmtYMDHMSSTR(out.getCreateTime()) : "");
                orderExcelOut.setCancelTime(out.getCancelTime() != null ? DUtils.fmtYMDHMSSTR(out.getCancelTime()) : "");
                orderExcelOut.setPaymentTime(out.getPaymentTime() != null ? DUtils.fmtYMDHMSSTR(out.getPaymentTime()) : "");
                orderExcelOut.setOrderStatus(OpenCoursesOrderStatusEnum.getEnumByCode(out.getOrderStatus()).getName());

                orderExcelList.add(orderExcelOut);
            }


            List<Map<String, Object>> list = new ArrayList<>();
            ExportParams params = new ExportParams(null, "报名订单");
            /** 自定义表头 */
            List headList = setHeadRows(dynamicHeadList, orderExcelList, list);
            Workbook workbook = ExcelExportUtil.exportExcel(params,headList, list);

            /** 固定表头 */
            Workbook workbook = ExcelExportUtil.exportExcel(params, OpenActivityOrderExcelOut.class, orderExcelList);

            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("UTF-8");
            String filename = "报名订单" + DUtils.fmtYMDHMS_Str(new Date()) + ".xls";
            response.setHeader("Content-disposition", "attachment;filename=\"" + java.net.URLEncoder.encode(filename, "UTF-8") + "\"");
            response.flushBuffer();
            workbook.write(response.getOutputStream());

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return MessageBox.build(com.zy.core.model.BDic.FAIL, "导出失败，请联系管理员");
        } finally {
            try {
                if (null != fos) {
                    fos.flush();
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return MessageBox.build(com.zy.core.model.BDic.SUCCESS, "Excel导出成功");
    }


    /**
     * 自定义表头
     *
     * @return
     */
    public static List<ExcelExportEntity> setHeadRows(List<String> dynamicHeadList, List<OpenActivityOrderExcelOut> valueList, List<Map<String, Object>> mapList) {
        List<ExcelExportEntity> headList = new ArrayList<>();

        ExcelExportEntity orderNo = new ExcelExportEntity("订单编号", "orderNo");
        orderNo.setOrderNum(1);
        orderNo.setWidth(30);
        headList.add(orderNo);

        ExcelExportEntity title = new ExcelExportEntity("活动名称", "title");
        title.setOrderNum(1);
        title.setWidth(30);
        headList.add(title);

        ExcelExportEntity unitPrice = new ExcelExportEntity("单价（元）", "unitPrice");
        unitPrice.setOrderNum(1);
        unitPrice.setWidth(30);
        headList.add(unitPrice);

        ExcelExportEntity buyNum = new ExcelExportEntity("下单数量", "buyNum");
        buyNum.setOrderNum(1);
        buyNum.setWidth(30);
        headList.add(buyNum);

        ExcelExportEntity orderAmount = new ExcelExportEntity("实付金额（元）", "orderAmount");
        orderAmount.setOrderNum(1);
        orderAmount.setWidth(30);
        headList.add(orderAmount);


        ExcelExportEntity userName = new ExcelExportEntity("下单用户", "userName");
        userName.setOrderNum(1);
        userName.setWidth(30);
        headList.add(userName);

        ExcelExportEntity userPhone = new ExcelExportEntity("下单用户手机号", "userPhone");
        userPhone.setOrderNum(1);
        userPhone.setWidth(30);
        headList.add(userPhone);

        ExcelExportEntity affiliatedBusinessManager = new ExcelExportEntity("所属员业务", "affiliatedBusinessManager");
        affiliatedBusinessManager.setOrderNum(1);
        affiliatedBusinessManager.setWidth(30);
        headList.add(affiliatedBusinessManager);

        List<List<CommunityUserBuyOpenActivityOrderTagOut>> inputList = valueList.stream().filter(v -> v.getActivityInputTagList() != null && v.getActivityInputTagList().size() > 0).map(OpenActivityOrderExcelOut::getActivityInputTagList).collect(Collectors.toList());
        Integer maxInputSize = 0;
        if (inputList != null && inputList.size() > 0) {
            Optional<List<CommunityUserBuyOpenActivityOrderTagOut>> inputLst = inputList.stream().max((u1, u2) -> Integer.valueOf(u1.size()).compareTo(u2.size()));
            maxInputSize = inputLst.get().size();
            if (maxInputSize != null && maxInputSize > 0) {
                for (int i = 0; i < maxInputSize; i++) {
                    ExcelExportEntity createTime = new ExcelExportEntity("输入框标签" + (i + 1), "input" + (i + 1));
                    createTime.setOrderNum(1);
                    createTime.setWidth(30);
                    headList.add(createTime);
                }
            }
        }

        Integer maxCheckboxSize = 0;
        List<List<CommunityUserBuyOpenActivityOrderTagOut>> checkboxList = valueList.stream().filter(v -> v.getActivityCheckBoxTagList() != null && v.getActivityCheckBoxTagList().size() > 0).map(OpenActivityOrderExcelOut::getActivityCheckBoxTagList).collect(Collectors.toList());
        if (checkboxList != null && checkboxList.size() > 0) {
            Optional<List<CommunityUserBuyOpenActivityOrderTagOut>> checkboxLst = checkboxList.stream().max((u1, u2) -> Integer.valueOf(u1.size()).compareTo(u2.size()));
            maxCheckboxSize = checkboxLst.get().size();
            if (maxCheckboxSize != null && maxCheckboxSize > 0) {
                for (int i = 0; i < maxCheckboxSize; i++) {
                    ExcelExportEntity createTime = new ExcelExportEntity("复选框标签" + (i + 1), "checkbox" + (i + 1));
                    createTime.setOrderNum(1);
                    createTime.setWidth(30);
                    headList.add(createTime);
                }
            }
        }

        ExcelExportEntity createTime = new ExcelExportEntity("创建时间", "createTime");
        createTime.setOrderNum(1);
        createTime.setWidth(30);
        headList.add(createTime);

        ExcelExportEntity paymentTime = new ExcelExportEntity("付款时间", "paymentTime");
        paymentTime.setOrderNum(1);
        paymentTime.setWidth(30);
        headList.add(paymentTime);

        ExcelExportEntity cancelTime = new ExcelExportEntity("取消时间", "cancelTime");
        cancelTime.setOrderNum(1);
        cancelTime.setWidth(30);
        headList.add(cancelTime);


        ExcelExportEntity status = new ExcelExportEntity("订单状态", "status");
        status.setOrderNum(1);
        status.setWidth(30);
        headList.add(status);

        Integer finalMaxInputSize = maxInputSize;
        Integer finalMaxCheckboxSize = maxCheckboxSize;
        valueList.forEach(v -> {
            Map<String, Object> valueMap = new HashMap<>();
            valueMap.put("orderNo", v.getOrderNo());
            valueMap.put("title", v.getTitle());
            valueMap.put("unitPrice", new BigDecimal(v.getUnitPrice()).setScale(2).toString());
            valueMap.put("buyNum", v.getBuyNum());
            valueMap.put("orderAmount", v.getOrderAmount());
            valueMap.put("userName", v.getPaymentUserName());
            valueMap.put("userPhone", v.getPaymentUserPhone());
            valueMap.put("affiliatedBusinessManager", v.getAffiliatedBusinessManager());

            try {

                for (int i = 0; i < finalMaxInputSize; i++) {
                    if (v.getActivityInputTagList() != null && v.getActivityInputTagList().size() > i && v.getActivityInputTagList().get(i) != null && v.getActivityInputTagList().get(i).getTagDetailList() != null && v.getActivityInputTagList().get(i).getTagDetailList().size() > 0) {
                        valueMap.put("input" + (i + 1), v.getActivityInputTagList().get(i).getTagName() + "：" + v.getActivityInputTagList().get(i).getTagDetailList().get(0).getTagDetailValue());
                    } else {
                        valueMap.put("input" + (i + 1), " -");
                    }
                }

                for (int i = 0; i < finalMaxCheckboxSize; i++) {
                    if (v.getActivityCheckBoxTagList() != null && v.getActivityCheckBoxTagList().size() > i && v.getActivityCheckBoxTagList().get(i).getTagDetailList() != null && v.getActivityCheckBoxTagList().get(i).getTagDetailList().size() > 0) {
                        List<CommunityUserBuyOpenActivityOrderTagDetailOut> checkBoxDetailList = v.getActivityCheckBoxTagList().get(i).getTagDetailList();
                        List<String> checkBoxDetailStrList = checkBoxDetailList.stream().map(CommunityUserBuyOpenActivityOrderTagDetailOut::getTagDetailValue).collect(Collectors.toList());
                        StringBuffer sb = new StringBuffer();
                        sb.append(v.getActivityCheckBoxTagList().get(i).getTagName() + "：");
                        checkBoxDetailStrList.forEach(s -> {
                            sb.append(s);
                            sb.append("、");
                        });
                        sb.deleteCharAt(sb.length() - 1);
                        valueMap.put("checkbox" + (i + 1), sb.toString());
                    } else {
                        valueMap.put("checkbox" + (i + 1), " -");
                    }
                }
            } catch (Exception e) {
                log.error("标签导出异常：", e);
            }
            valueMap.put("createTime", v.getCreateTime());
            valueMap.put("paymentTime", v.getPaymentTime());
            valueMap.put("cancelTime", v.getCancelTime());
            valueMap.put("status", v.getOrderStatus());
            mapList.add(valueMap);
        });

        return headList;
    }

 

二、实体类

@Data
public class OpenActivityOrderExcelOut {


    @Excel(name = "订单编号",width = 30)
    private String orderNo;

    @Excel(name = "活动名称",width = 30)
    private String title;

    @Excel(name = "单价（元）",width = 30)
    private String unitPrice;

    @Excel(name = "下单数量",width = 30)
    private String buyNum;

    @Excel(name = "实付金额（元）",width = 30)
    private String orderAmount;

    @Excel(name = "下单用户",width = 30)
    private String paymentUserName;

    @Excel(name = "下单用户手机号",width = 30)
    private String paymentUserPhone;

    @Excel(name = "所属业务员",width = 30)
    private String affiliatedBusinessManager;

    @Excel(name = "创建时间",width = 30)
    private String createTime;

    @Excel(name = "付款时间",width = 30)
    private String paymentTime;

    @Excel(name = "取消时间",width = 30)
    private String cancelTime;

    @Excel(name = "订单状态",width = 30)
    private String orderStatus;

    @Excel(name = "标签",width = 30)
    List<CommunityUserBuyOpenActivityOrderTagOut> activityInputTagList;

    @Excel(name = "标签",width = 30)
    List<CommunityUserBuyOpenActivityOrderTagOut> activityCheckBoxTagList;
    
}