easyExcel如何实现自定义标题，前两个（多个）标题占满一行，最后标题有规律

最新推荐文章于 2024-06-14 16:42:55 发布

菜狗二哈

最新推荐文章于 2024-06-14 16:42:55 发布

阅读量2k

点赞数 3

文章标签： java

本文链接：https://blog.csdn.net/linyiwwy/article/details/132106830

版权

实现效果
在这里插入图片描述
实现方式
1，标题：

@Data
public class ExportPurchaseSortingCustDto {
    /** 序号 */
    @ExcelProperty(value ={"${bigHead}","${dateHead}","序号"},index = 0)
    @ApiModelProperty(value = "序号")
    private int sort;

    /** 商品条码 */
    @ExcelProperty(value ={"${bigHead}","${dateHead}","商品条码"},index = 1)
    @ApiModelProperty(value = "商品条码")
    private String itemCode;

    /** 商品名称 */
    @ExcelProperty(value ={"${bigHead}","${dateHead}","商品名称"},index = 2)
    @ApiModelProperty(value = "商品名称 ")
    private String itemName;

    /** 订货单位 */
    @ExcelProperty(value ={"${bigHead}","${dateHead}","商品单位"},index = 3)
    @ApiModelProperty(value = "商品单位")
    private String unitName;


    /** 订货数量（含赠送量） */
    @ExcelProperty(value ={"${bigHead}","${dateHead}","订货总数量"},index = 4)
    @ApiModelProperty(value = "订货总数量")
    private BigDecimal salesQty;

    /** 分拣数量 */
    @ExcelProperty(value ={"${bigHead}","${dateHead}","配送总数量"},index = 5)
    @ApiModelProperty(value = "配送总数量")
    private BigDecimal outQty;
}

2，标题处理，写个配置类

public class ExcelTitleHandler implements CellWriteHandler {
    /**
     错误信息处理时正则表达式的格式
     */
    private final String EXCEL_ERROR_REG = "^(.*)(\\(错误:)(.*)(\\))$";

    private String bigHead;

    private String dateHead;

    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");

    public ExcelTitleHandler( String bigHead,String dateHead) {
        this.bigHead = bigHead; //表头1
        this.dateHead = dateHead;  //表头2
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        // 动态设置表头字段
        if (!ObjectUtil.isEmpty(head)) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtil.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                properties.setProperty("bigHead", bigHead);
                properties.setProperty("dateHead", dateHead);
                for (int i = 0 ; i < headNameList.size() ; i++){
                    // 循环遍历替换
                    headNameList.set(i, placeholderHelper.replacePlaceholders(headNameList.get(i), properties));
                }
            }
        }
    }
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }
}

3：业务逻辑代码处理

@Override
    public void exportPurchaseItemSorting(HttpServletResponse response,sortingReq sortingReq){
        
        String purchaseNo = getSortingReq.getPurchaseNo();
        String deliverDate = "";
        List<ExportPurchaseSortingCustDto> list = new ArrayList<>();
        int sort =1;
      //各种业务处理
        String dateHead = "";
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + "采购商品汇总导出" + ".xlsx");
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            headWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
            headWriteCellStyle.setBorderTop(BorderStyle.THIN);
            headWriteCellStyle.setBorderRight(BorderStyle.THIN);
            headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            //设置头部标题居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            EasyExcel.write(response.getOutputStream(), ExportPurchaseSortingCustDto.class)
                    //插入数据
                    .sheet("汇总")
                    .registerWriteHandler(new ExcelTitleHandler("sss",dateHead))
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20)).registerWriteHandler(horizontalCellStyleStrategy)
                    .doWrite(list);
        }catch (Exception e){
            throw new BizException(BizExceptionEnum.CHECK_ERROR,"导出失败！");
        }
    }