使用easyexcel实现单元格样式设置和下拉框设置

1.单元格样式设置

1.1实体类

public class DemoData {@ExcelProperty("PK")private String name;@ExcelProperty("年龄")private int age;// 必须提供无参构造方法public DemoData() {}public DemoData(String name, int age) {this.name = name;this.age = age;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}
// 省略 getter/setter
}

1.2启动类

public class ExcelExporter {public static void main(String[] args) {String fileName = "D:\\idea代码\\demo1\\src\\main\\resources\\example3.xlsx";List<com.example.demo1.com.liyingjie.mapper.ExcelExporter.DemoData> dataList = new ArrayList<>();dataList.add(new com.example.demo1.com.liyingjie.mapper.ExcelExporter.DemoData("姓名", 20));dataList.add(new com.example.demo1.com.liyingjie.mapper.ExcelExporter.DemoData("姓名", 30));EasyExcel.write(fileName, DemoData.class).registerWriteHandler(new HeaderBackgroundColorHandler()).sheet("Sheet1").doWrite(dataList);}
}

1.3自定义拦截器

public class HeaderBackgroundColorHandler implements CellWriteHandler {private  Set<String> targetHeaders;  // 目标表头集合private  String targetHeader1="PK";private  String targetHeader2="主";@Overridepublic void afterCellDispose(CellWriteHandlerContext context) {boolean isHead = context.getHead() != null && context.getHead();// 处理表头行if (isHead) {String headerValue = context.getFirstCellData().getStringValue();if (headerValue.trim().contains(targetHeader1) || headerValue.trim().contains(targetHeader2)) {// 获取或创建样式WriteCellStyle writeCellStyle = context.getFirstCellData().getOrCreateStyle();// 设置背景色writeCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 设置字体样式WriteFont writeFont = new WriteFont();writeFont.setBold(true);  // 加粗writeFont.setColor(IndexedColors.BLACK.getIndex());  // 字体颜色writeCellStyle.setWriteFont(writeFont);}}}}

2 下拉框设置

2.1实体类

public class Employee {@ExcelProperty("姓名")private String name;// 姓名@ExcelProperty("部门")private String department; // 部门@ExcelProperty("职位")private String position; // 职位// 无参构造函数public Employee() {}// 全参构造函数public Employee(String name, String department, String position) {this.name = name;this.department = department;this.position = position;}// Getter和Setter方法public String getName() {return name;}public void setName(String name) {this.name = name;}public String getDepartment() {return department;}public void setDepartment(String department) {this.department = department;}public String getPosition() {return position;}public void setPosition(String position) {this.position = position;}// toString方法@Overridepublic String toString() {return "Employee{" +"name='" + name + '\'' +", department='" + department + '\'' +", position='" + position + '\'' +'}';}
}

public class Department {@ExcelProperty(value = "部门")private String departmentName; // 部门名称@ExcelProperty(value = "职位")private String position; // 职位// 无参构造函数public Department() {}// 全参构造函数public Department(String departmentName, String position) {this.departmentName = departmentName;this.position = position;}// Getter和Setter方法public String getDepartmentName() {return departmentName;}public void setDepartmentName(String departmentName) {this.departmentName = departmentName;}public String getPosition() {return position;}public void setPosition(String position) {this.position = position;}// toString方法@Overridepublic String toString() {return "Department{" +"departmentName='" + departmentName + '\'' +", position='" + position + '\'' +'}';}
}

2.2自定义拦截器

public class DynamicDropDownHandler implements SheetWriteHandler {// 字典数据格式: Map<表头名称, Map<Key, Value>> private final Map<String, Map<Integer, String>> dictDataMap;// 新增：传入的表头数据private final List<List<String>> listHead;public DynamicDropDownHandler(Map<String, Map<Integer, String>> dictDataMap, List<List<String>> listHead) {this.dictDataMap = dictDataMap;this.listHead = listHead;}@Overridepublic void afterSheetCreate(SheetWriteHandlerContext context) {Sheet sheet = context.getWriteSheetHolder().getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();// 1. 直接使用传入的表头数据，跳过映射逻辑Map<String, Integer> headerMap = new HashMap<>();for (int i = 0; i < listHead.size(); i++) {List<String> headers = listHead.get(i);if (!headers.isEmpty()) {headerMap.put(headers.get(0), i); // 取每个 List<String> 的第一个元素作为表头名称}}// 2. 遍历字典数据，设置下拉框for (Map.Entry<String, Map<Integer, String>> entry : dictDataMap.entrySet()) {String headerName = entry.getKey();Map<?, ?> optionsMap = entry.getValue();// 如果当前 Sheet 存在该表头，则设置下拉框if (headerMap.containsKey(headerName)) {// 提取下拉选项（假设使用 Map 的 Value 作为选项）List<String> options = new ArrayList<>();for (Object value : optionsMap.values()) {options.add(value.toString());}// 设置下拉框setDropDownForColumn(sheet, helper, headerMap.get(headerName), options);} else {System.out.println("未找到匹配的表头: " + headerName);}}}/*** 为指定列设置下拉框*/private void setDropDownForColumn(Sheet sheet,DataValidationHelper helper,int columnIndex,List<String> options) {if (options.isEmpty()) return;// 设置应用范围（从第二行开始，默认设置100行）int lastRow = 100; // 默认设置100行CellRangeAddressList range = new CellRangeAddressList(1, lastRow, columnIndex, columnIndex);// 创建数据验证约束DataValidationConstraint constraint = helper.createExplicitListConstraint(options.toArray(new String[0]));// 创建并应用验证DataValidation validation = helper.createValidation(constraint, range);validation.setSuppressDropDownArrow(true);sheet.addValidationData(validation);}
}

2.3启动类

public class Main {public static void main(String[] args) {// 定义字典数据格式: Map<表头名称, Map<Key, Value>>Map<String, Map<Integer, String>> dictData = new LinkedHashMap<>();// 部门下拉选项（Key 可忽略）Map<Integer, String> departmentOptions = new HashMap<>();departmentOptions.put(1, "技术部");departmentOptions.put(2, "市场部");departmentOptions.put(3, "财务部");dictData.put("部门", departmentOptions);// 职位下拉选项Map<Integer, String> positionOptions = new HashMap<>();positionOptions.put(1, "开发工程师");positionOptions.put(2, "销售经理");positionOptions.put(3, "会计");dictData.put("职位", positionOptions);Employee employee1 = new Employee("张三", "技术部", "开发工程师");Employee employee2 = new Employee("李四", "市场部", "销售经理");ArrayList<Employee> list = new ArrayList<>();list.add(employee1);list.add(employee2);ArrayList<Department> list1 = new ArrayList<>();Department department = new Department("技术部", "技术经理");Department department1=  new Department("技术部", "技术经理");list1.add(department);list1.add(department1);List<List<String>> listHead = new ArrayList<>();listHead.add(new ArrayList<String>() {{add("姓名");
}});
listHead.add(new ArrayList<String>() {{add("部门");
}});
listHead.add(new ArrayList<String>() {{add("职位");
}});// 写入 ExcelString fileName = "D:\\idea代码\\demo1\\src\\main\\resources\\output24.xlsx";ExcelWriter build = EasyExcel.write(fileName).build();// 注册处理器WriteSheet dic = EasyExcel.writerSheet("字典项页").head(Department.class).build();build.write(list1, dic);WriteSheet data = EasyExcel.writerSheet("数据页").head(listHead).registerWriteHandler(new DynamicDropDownHandler(dictData,listHead)).build();// 写入数据build.write(list, data);build.finish();}
}