一. 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 {


    // 下拉框數(shù)據(jù)
    private Map<Integer, String[]> mapDropDown;
    // 備注
    private Map<Integer, String> headerRemarkMap;

    public DemoWriteHandler(){
        // 初始化數(shù)據(jù)
        initMapDropDown();
    }

    /**
     * 初始化數(shù)據(jù)
     */
    private void initMapDropDown() {
        //下拉選 (給第二列增加下拉)
        mapDropDown = new HashMap<>();
        mapDropDown.put(1,new String[]{"小明","小紅","小黑"});

        // 備注 (給第一列增加備注,內(nèi)容:我是備注)
        headerRemarkMap = new HashMap<>();
        headerRemarkMap.put(0,"我是備注");
    }

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        if (context.getHead() == null) {
            return;
        }
        if (context.getHead()) {
            // 設(shè)置表頭樣式
            setHeadCellStyle(context);
        } else {
            // 設(shè)置行樣式
            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) -> {
                // 在第一行 第二列創(chuàng)建一個(gè)批注
                Comment comment =
                        drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, index.shortValue(), 0, (short)2, 1));
                // 輸入批注信息
                comment.setString(new XSSFRichTextString(value));
                // 將批注添加到單元格對(duì)象中
                sheet.getRow(0).getCell(index).setCellComment(comment);
            });
        }
    }

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

        ///開(kāi)始設(shè)置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //設(shè)置下拉框
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
            /*起始行芳绩、終止行、起始列偷线、終止列  起始行為1即表示表頭不設(shè)置**/
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
            /*設(shè)置下拉框數(shù)據(jù)**/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        }
    }

    /**
     * 設(shè)置表頭樣式
     * @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);
    }

    /**
     * 設(shè)置內(nèi)容樣式
     * @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("序號(hào)", "名字", "年齡");
        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));
    }

三. 下拉值過(guò)多處理

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

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

            // 創(chuàng)建子工作表
            String newSheetName = "Dropdown"+columnIndex;
            Sheet subSheet = workbook.createSheet(newSheetName);
            // 開(kāi)始設(shè)置下拉框
            DataValidationHelper helper = mainSheet.getDataValidationHelper();

            // 將值列表寫(xiě)入子工作表
            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); // 寫(xiě)入第一列
                cell.setCellValue(values[i]);
            }

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

            // 創(chuàng)建數(shù)據(jù)驗(yàn)證約束
            DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);

            // 設(shè)置數(shù)據(jù)驗(yàn)證范圍
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);

            // 創(chuàng)建數(shù)據(jù)驗(yàn)證
            DataValidation dataValidation = helper.createValidation(constraint, addressList);

            // 添加數(shù)據(jù)驗(yàn)證到主工作表
            mainSheet.addValidationData(dataValidation);
        }
    }