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;/*** 忽略这个字段*/@ExcelIgnoreprivate 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;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic 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. 表格生成

@Testpublic 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);}