【EasyExcel】动态替换表头内容并应用样式

1.定义实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.ContentStyle;
import com.alibaba.excel.metadata.BorderStyleEnum;
import com.alibaba.excel.metadata.VerticalAlignmentEnum;
import com.alibaba.excel.metadata.BooleanEnum;@Data
@HeadRowHeight(40)
@ContentRowHeight(30)
@ExcelIgnoreUnannotated
@ContentStyle(wrapped = BooleanEnum.TRUE,borderBottom = BorderStyleEnum.THIN,borderLeft = BorderStyleEnum.THIN,borderRight = BorderStyleEnum.THIN,borderTop = BorderStyleEnum.THIN,verticalAlignment = VerticalAlignmentEnum.CENTER,horizontalAlignment = HorizontalAlignmentEnum.CENTER
)
public class Product {@ColumnWidth(28)@ExcelProperty(value = {"${cellValue}经营统计表", "${cellWriterValue}", "产品名称"}, index = 0)private String productName;@ColumnWidth(28)@ExcelProperty(value = {"${cellValue}经营统计表", "${cellWriterValue}", "产品类别"}, index = 1)private String productCategory;@ColumnWidth(28)@ExcelProperty(value = {"${cellValue}经营统计表", "${cellWriterValue}", "价格"}, index = 2)private Double price;
}

2.自定义处理器

package com.example.util;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 org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.PropertyPlaceholderHelper;import java.util.List;
import java.util.Properties;public class DynamicValueCellWriteHandler implements CellWriteHandler {private String cellValue;private String cellWriterValue;private PropertyPlaceholderHelper propertyPlaceholderHelper = new PropertyPlaceholderHelper("${", "}");public DynamicValueCellWriteHandler(String cellValue, String cellWriterValue) {this.cellValue = cellValue;this.cellWriterValue = cellWriterValue;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {if (head != null) {List<String> headNameList = head.getHeadNameList();if (CollectionUtils.isNotEmpty(headNameList)) {Properties properties = new Properties();properties.setProperty("cellValue", cellValue);properties.setProperty("cellWriterValue", cellWriterValue);for (int i = 0; i < headNameList.size(); i++) {headNameList.set(i, propertyPlaceholderHelper.replacePlaceholders(headNameList.get(i), properties));}}}}
}

3.在写入时使用自定义处理器

 excelWriter.write(excelCategoryDataList, EasyExcel.writerSheet(sheetName).head(Product.class).registerWriteHandler(new DynamicValueCellWriteHandler(cellValue, cellWriterValue)).build());

封装成通用方法

public class DynamicValueCellWriteHandler implements CellWriteHandler {private Map<String, String> placeholders;private PropertyPlaceholderHelper propertyPlaceholderHelper = new PropertyPlaceholderHelper("${", "}");public DynamicValueCellWriteHandler(Map<String, String> placeholders) {this.placeholders = placeholders;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {if (head != null) {List<String> headNameList = head.getHeadNameList();if (CollectionUtils.isNotEmpty(headNameList)) {Properties properties = new Properties();for (Map.Entry<String, String> entry : placeholders.entrySet()) {properties.setProperty(entry.getKey(), entry.getValue());}headNameList.replaceAll(s -> propertyPlaceholderHelper.replacePlaceholders(s, properties));}}}
}

Map<String, String> placeholders = new HashMap<>();
placeholders.put("value", "someValue");
placeholders.put("cellValue", "someCellValue");
placeholders.put("cellWriterValue", "someWriterValue");
placeholders.put("additionalPlaceholder1", "additionalValue1");
placeholders.put("additionalPlaceholder2", "additionalValue2");.registerWriteHandler(new DynamicValueCellWriteHandler(placeholders ))