需求背景:对于有些表头需要根据配置动态生成,做以下记录。
动态导出模板
测试工具类
package com.alibaba.easyexcel.test.demo.write;
import com.alibaba.easyexcel.test.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.stream.Collectors;
/**
* @description:
* @author: root
* @date: 2022-11-16
*/
public class DynamicWriteTest {
/**
* 测试方法
* @param
* @return
* @author root
* @date 2022-11-16
*/
@Test
public void writeDynamicHead() {
// 表头
List<List<String>> headers = prepareHeaders();
// 单元格下拉
HashMap<Integer, List<String>> dropCells = prepareDropCells();
// 模板导出
String fileName = TestFileUtil.getPath() + "customHandlerWrite" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName).head(headers).registerWriteHandler(new DynamicSheetWriteHandler(dropCells)).sheet("模板").doWrite(new ArrayList<>());
}
/**
* 测试数据
* @param
* @return {@link List< DemoData>}
* @author root
* @date 2022-11-16
*/
private List<DemoData> data() {
List<DemoData> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
private static final List<String> headers = Arrays.asList("动态表头1", "动态表头2", "动态表头3");
private static final List<String> dropCells = Arrays.asList("测试1", "测试2");
/**
* 准备动态表头数据
* @param
* @return {@link List< List< String>>}
* @author root
* @date 2022-11-16
*/
private List<List<String>> prepareHeaders() {
List<List<String>> list = headers.stream().map(item -> Arrays.asList(item)).collect(Collectors.toList());
return list;
}
/**
* 准备单元格下拉数据
* @param
* @return {@link HashMap< Integer, List< String>>}
* @author root
* @date 2022-11-16
*/
private HashMap<Integer, List<String>> prepareDropCells() {
HashMap<Integer, List<String>> dropCellMap = new HashMap<>();
for(int i = 0; i < headers.size(); i++) {
if(headers.get(i).equals("动态表头1")) {
dropCellMap.put(i, dropCells);
}
}
return dropCellMap;
}
}
自定义拦截器
package com.alibaba.easyexcel.test.demo.write;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.HashMap;
import java.util.List;
/**
* 自定义拦截器. 新增单元格下拉框数据
*
* @author root
*/
@Slf4j
public class DynamicSheetWriteHandler implements SheetWriteHandler {
private HashMap<Integer, List<String>> dropCellMap;
public DynamicSheetWriteHandler(HashMap<Integer, List<String>> dropCellMap) {
this.dropCellMap = dropCellMap;
}
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
log.info("第{}个Sheet写入成功。", context.getWriteSheetHolder().getSheetNo());
dropCellMap.forEach((k, v) -> {
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, k, k);
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(v.toArray(new String[0]));
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
//设置约束
if(dataValidation instanceof HSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(false);
} else {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}
// 禁止输入非下拉框的内容
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("提示", "此值与单元格下拉不符");
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
});
}
}
结果展示: