EasyExcel导出替换列中的变量

基于easyexcel2.0版本
easyexcel官网：https://easyexcel.opensource.alibaba.com/docs/2.x/quickstart/write
测试代码地址：https://gitee.com/wangtianwen1996/cento-practice/blob/master/src/test/java/com/xiaobai/easyexcel/DynamicHeadTest.java

1. 需要自定义替换字符串变量的工具类

package com.xiaobai.java_core.string_practice;

import com.sun.org.apache.xml.internal.utils.StringBufferPool;
import org.springframework.lang.Nullable;

import java.util.Map;

/**
 * @author wangtw
 * @ClassName StringUtil
 * @description: 字符串工具类
 * @date 2023/9/1006:59
 */
public class StringUtil extends org.springframework.util.StringUtils{

    /**
     * 将字符串中特定模式的字符转换成map中对应的值
     * <p>
     * use: format("my name is ${name}, and i like ${like}!", {"name":"L.cm", "like": "Java"})
     *
     * @param message 需要转换的字符串
     * @param params  转换所需的键值对集合
     * @return 转换后的字符串
     */
    public static String format(@Nullable String message, @Nullable Map<String, ?> params) {
        // message 为 null 返回空字符串
        if (message == null) {
            return StringPool.EMPTY;
        }
        // 参数为 null 或者为空
        if (params == null || params.isEmpty()) {
            return message;
        }
        // 替换变量
        StringBuilder sb = new StringBuilder((int) (message.length() * 1.5));
        int cursor = 0;
        for (int start, end; (start = message.indexOf(StringPool.DOLLAR_LEFT_BRACE, cursor)) != -1 && (end = message.indexOf(StringPool.RIGHT_BRACE, start)) != -1; ) {
            sb.append(message, cursor, start);
            String key = message.substring(start + 2, end);
            Object value = params.get(StringUtil.trimWhitespace(key));
            sb.append(value == null ? StringPool.EMPTY : value);
            cursor = end + 1;
        }
        sb.append(message.substring(cursor));
        return sb.toString();
    }
}

2. 实体类

package com.xiaobai.easyexcel;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 基础数据类
 *
 **/
@Data
public class DynamicHeadData {
    @ExcelProperty("字符串标题")
    @ColumnWidth(20)
    private String string;
    @ExcelProperty("日期标题")
    @ColumnWidth(20)
    private Date date;
    @ExcelProperty("数字标题")
    @ColumnWidth(20)
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;

    @ExcelProperty({"子数据", "第一列${firstColumn}"})
    @ColumnWidth(30)
    private String childrenString;

    @ExcelProperty({"子数据", "第二列${secondColumn}"})
    @ColumnWidth(30)
    private Date childrenDate;

    @ExcelProperty({"子数据", "第三列${thirdColumn}"})
    @ColumnWidth(30)
    private File imageData;

    @ExcelProperty({"子数据", "第四列${fourthColumn}"})
    @ColumnWidth(30)
    private String stringData;
}

3. 数据准备

/**
     * 数据准备
     * @return
     */
    private List<DynamicHeadData> data() {
        List<DynamicHeadData> list = new ArrayList<DynamicHeadData>();
        for (int i = 0; i < 4; i++) {
            DynamicHeadData data = new DynamicHeadData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

4. 自定义拦截器

package com.xiaobai.easyexcel;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.xiaobai.java_core.string_practice.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author wangtw
 * @ClassName CustomCellWriterHandle
 * @description: 自定义EasyExcel拦截器
 * @date 2023/11/1222:31
 */
@Slf4j
public class CustomCellWriterHandle implements CellWriteHandler {

    private Map<String, String> dynamicMap = new HashMap<>();

    public CustomCellWriterHandle(Map<String, String> dynamicMap) {
        this.dynamicMap = dynamicMap;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder,
                                 WriteTableHolder writeTableHolder,
                                 List<CellData> list, Cell cell,
                                 Head head, Integer integer,
                                 Boolean isHead) {
        if (isHead) {
            String headName = cell.getStringCellValue();
            log.info("headName: {}", headName);
            headName = StringUtil.format(headName, dynamicMap);
            cell.setCellValue(headName);
        }
    }
}

5. 表格生成

@Test
    public void tableWrite() {
        String fileName = this.getClass().getResource("/").getPath() + "tableWrite" + System.currentTimeMillis() + ".xlsx";
        List<DynamicHeadData> demoDataList = data();
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        // 数据单元格策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置单元格边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 数据垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        Map<String, String> dynamicMap = new HashMap<>();
        dynamicMap.put("firstColumn", "aaaaaaaaa");
        dynamicMap.put("secondColumn", "bbbbbbbbbb");
        dynamicMap.put("thirdColumn", "cccccccccccc");
        dynamicMap.put("fourthColumn", "ddddddddddd");

        EasyExcel.write(fileName, DynamicHeadData.class)
                .registerWriteHandler(new CustomCellWriterHandle(dynamicMap))
                .registerWriteHandler(horizontalCellStyleStrategy)
                .sheet("模板").doWrite(demoDataList);
    }