一. DemoWriteHandler(样式处理器)

import cn.hutool.core.map.MapUtil;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

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

public class DemoWriteHandler implements SheetWriteHandler,CellWriteHandler,RowWriteHandler {


    // 下拉框数据
    private Map<Integer, String[]> mapDropDown;
    // 备注
    private Map<Integer, String> headerRemarkMap;

    public DemoWriteHandler(){
        // 初始化数据
        initMapDropDown();
    }

    /**
     * 初始化数据
     */
    private void initMapDropDown() {
        //下拉选 (给第二列增加下拉)
        mapDropDown = new HashMap<>();
        mapDropDown.put(1,new String[]{"小明","小红","小黑"});

        // 备注 (给第一列增加备注,内容:我是备注)
        headerRemarkMap = new HashMap<>();
        headerRemarkMap.put(0,"我是备注");
    }

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        if (context.getHead() == null) {
            return;
        }
        if (context.getHead()) {
            // 设置表头样式
            setHeadCellStyle(context);
        } else {
            // 设置行样式
            setContentCellStyle(context);
        }
    }

    @Override
    public void afterRowDispose(RowWriteHandlerContext context) {
        // 添加备注
        if (BooleanUtils.isTrue(context.getHead()) && MapUtil.isNotEmpty(headerRemarkMap)) {
            Sheet sheet = context.getWriteSheetHolder().getSheet();
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
            headerRemarkMap.forEach((index, value) -> {
                // 在第一行 第二列创建一个批注
                Comment comment =
                        drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, index.shortValue(), 0, (short)2, 1));
                // 输入批注信息
                comment.setString(new XSSFRichTextString(value));
                // 将批注添加到单元格对象中
                sheet.getRow(0).getCell(index).setCellComment(comment);
            });
        }
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //获取工作簿
        Sheet sheet = writeSheetHolder.getSheet();

        ///开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //设置下拉框
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
            /*起始行、终止行、起始列、终止列  起始行为1即表示表头不设置**/
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
            /*设置下拉框数据**/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        }
    }

    /**
     * 设置表头样式
     * @param context
     */
    public void setHeadCellStyle(CellWriteHandlerContext context) {
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setWrapped(true);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setLocked(true);
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("仿宋_GB2312");
        headWriteFont.setFontHeightInPoints((short)14);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
        context.getRow().setHeightInPoints(40);
    }

    /**
     * 设置内容样式
     * @param context
     */
    public void setContentCellStyle(CellWriteHandlerContext context) {
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setWrapped(true);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);

        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setColor(IndexedColors.RED.getIndex());
        headWriteFont.setFontName("仿宋_GB2312");
        headWriteFont.setFontHeightInPoints((short)14);
        contentWriteCellStyle.setWriteFont(headWriteFont);
        WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
        context.getRow().setHeightInPoints(20);
    }
}

二. Demo

@Test
    @SneakyThrows
    public void demo() {
        final FileOutputStream fileOutputStream = new FileOutputStream(new File("/data/123.xlsx"));
        final List<String> heads = List.of("序号", "名字", "年龄");
        final List<String> data = List.of("1", "小明", "18");

        final List<List<String>> headList = heads.stream().map(List::of).collect(Collectors.toList());
        EasyExcel.write(fileOutputStream)
                .head(headList)
                .registerWriteHandler(new DemoWriteHandler())
                .sheet()
                .doWrite(List.of(data));
    }

三. 下拉值过多处理

@Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 获取工作簿
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet mainSheet = writeSheetHolder.getSheet();

        // 设置下拉框
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
            int columnIndex = entry.getKey(); // 列index
            String[] values = entry.getValue(); // 下拉值

            // 创建子工作表
            String newSheetName = "Dropdown"+columnIndex;
            Sheet subSheet = workbook.createSheet(newSheetName);
            // 开始设置下拉框
            DataValidationHelper helper = mainSheet.getDataValidationHelper();

            // 将值列表写入子工作表
            int startRow = 0; // 起始行
            for (int i = 0; i < values.length; i++) {
                Row row = subSheet.getRow(startRow + i);
                if (row == null) {
                    row = subSheet.createRow(startRow + i);
                }
                Cell cell = row.createCell(0); // 写入第一列
                cell.setCellValue(values[i]);
            }

            // 创建引用子工作表中的值列表的公式
            String formula = String.format("'%s'!$A$1:$A$",newSheetName) + values.length;

            // 创建数据验证约束
            DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);

            // 设置数据验证范围
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);

            // 创建数据验证
            DataValidation dataValidation = helper.createValidation(constraint, addressList);

            // 添加数据验证到主工作表
            mainSheet.addValidationData(dataValidation);
        }
    }