EasyExcel按模板出使用自定义策略

EasyExcel按模板出使用自定义策略

目标

要完成下面格式的Excel导出，在实体类上使用注解标注字段已经不好使了

前面的表头有多行，还有单元格合并，下面表格的行数也不是固定的

这种情况可以使用EasyExcel中的模板，下面的的表格使用填充导出

EasyExcel填充官方文档：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/fill

实体类

表头字段

public class HbInvoiceExportForm {

    @ApiModelProperty(value = "信息平台编码")
    private String code3;

    @ApiModelProperty(value = "客户名称")
    private String bdCustomerName;

    @ApiModelProperty(value = "订单号")
    private String orderNo;

    @ApiModelProperty(value = "公司部门")
    private String corpDept;

    @ApiModelProperty(value = "业务员")
    private String salesman;

    @ApiModelProperty(value = "废危税率")
    private String wasteTaxRate;

    @ApiModelProperty(value = "运费税率")
    private String freightRate;

    @ApiModelProperty(value = "备注")
    private String remark;

    @ApiModelProperty(value = "行信息")
    private List<HbInvoiceExportRowForm> rows;

    @ApiModelProperty(value = "价税合计总共")
    private String totalAmount;

    @ApiModelProperty(value = "数量总共")
    private String totalNum;

    @ApiModelProperty(value = "开票员")
    private String drawer;

    @ApiModelProperty(value = "excel导出名称")
    private String text;
}

表体字段

public class HbInvoiceExportRowForm {
    @ApiModelProperty(value = "装运日期")
    private String shipmentDate;

    @ApiModelProperty(value = "联单号")
    private String systemManifestNo;

    @ApiModelProperty(value = "危废名称")
    private String wasteName;

    @ApiModelProperty(value = "主计量")
    private String mainMeasure;

    @ApiModelProperty(value = "数量")
    private String num;

    @ApiModelProperty(value = "报价")
    private String quote;

    @ApiModelProperty(value = "价税合计")
    private String totalAmount;

    @ApiModelProperty(value = "备注")
    private String remark;

    @ApiModelProperty(value = "空白字段，用来防止execl导出表格合并没有边框")
    private final String blankStr = "";

}

模板Excel

其中,{}中的是填充实体类变量名称，而data是多行模板填充时指定的变量名称

在模板中需要合并的单元格，拆分成每一个最小单元格，后面使用自定义策略合并，需要合并的第一列中绑定数值，后面需要合并的单元格绑定空字符串(data.blank是空字符串）

否则单元格合并会造成样式问题

实现代码

    InputStream template = null;
        try {
            template = new PathMatchingResourcePatternResolver()
                    .getResource("template-excel/HbInvoiceExport.XLSX").getInputStream();
        } catch (IOException e) {
            throw new RuntimeException("找不模板文件[" + "template-excel/HbInvoiceExport.XLSX" + "]");
        }
        //设置响应体，设置文件名称
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        String encodeName = null;
        try {
            encodeName = URLEncoder.encode(form.getText(), "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException("导出文件名称格式错误");
        }
        response.setHeader("Content-disposition", "attachment; filename=" + encodeName + ".xlsx");
        // 创建导出
        try (OutputStream out = response.getOutputStream();
             BufferedOutputStream bos = new BufferedOutputStream(out);
             ExcelWriter excelWriter = EasyExcel.write(bos)
                     .excelType(ExcelTypeEnum.XLSX)
                     //按照模板导出
                     .withTemplate(template)
                     //合并列 从第三列开始， 合并本列和右边两列
                     .registerWriteHandler(new LoopMergeStrategy(1, 3, 2))
                     .registerWriteHandler(new CellStyleStrategy())
                     .build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(true).autoStyle(true).direction(WriteDirectionEnum.VERTICAL).build();
            // 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 data1，然后多个list必须用 FillWrapper包裹
            excelWriter.fill(new FillWrapper("data", form.getRows()), fillConfig, writeSheet);
            excelWriter.fill(form, writeSheet);
            bos.flush();
        } catch (IOException e) {
            log.error("导出异常", e);
            throw new RuntimeException("导出异常;" + e.getMessage());
        }

合并单元格策略

LoopMergeStrategy 是EasyExcel提供的，只会对循环填充的单元格生效

new LoopMergeStrategy(int eachRow, int columnExtend, int columnIndex)

• eachRow：每一行，传1每一行都执行

• columnExtend: 合并扩展列， 向右合并列

• columnIndex： 列索引,从零开始，第columnIndex列开始执行合并，往右合并columnExtend列

数值格式设置策略

继承CellWriteHandler，重写afterCellDispose方法

指定单元格，将字符串转化为数值，否则数值在单元格上显示的还是字符串类型的

@Data
public class CellStyleStrategy implements CellWriteHandler {
    /**
     * 在单元格上的所有操作完成后调用
     * 每个单元格都会执行，Cell cell对象就是单元格对象
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList,
                                 Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //当前行和列序号
        int curRowIndex = cell.getRowIndex();
        int curColIndex = cell.getColumnIndex();
        //数值列更改数值类型
        if (curRowIndex >= 6 && (curColIndex >= 6 && curColIndex <= 8)) {
            String value = cell.toString();
            try {
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(Double.parseDouble(value));
            } catch (Exception ignore) {

            }
        }
    }
}