Java Easyexcel 设置单元格下拉选

import com.alibaba.excel.metadata.CellData;
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 excel.util.EasyExcelUtil;
import excel.util.Pointer;
import org.apache.poi.ss.usermodel.*;

import java.util.Arrays;
import java.util.List;

/**
 * @author Dell
 */
public class CommentHandler implements CellWriteHandler {
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder,
                                 WriteTableHolder writeTableHolder,
                                 List<CellData> cellDataList,
                                 Cell cell,
                                 Head head,
                                 Integer relativeRowIndex,
                                 Boolean isHead) {

        if(isHead) {
            Pointer.lg(cell, cell.getStringCellValue());

            EasyExcelUtil.ExcelCommentBuilder commentBuilder
                    = new EasyExcelUtil.ExcelCommentBuilder(
              writeSheetHolder
            );
            Comment comment = commentBuilder.createCellComment(cell,"测试！！！提示！！！");
            cell.setCellComment(comment);

            EasyExcelUtil.ExcelDropdownListDataValidationBuilder
                    dataValidationBuilder
                    = new EasyExcelUtil.ExcelDropdownListDataValidationBuilder(writeSheetHolder);
            DataValidation dataValidation = dataValidationBuilder
                    .createWholeColumnDataValidationAfterHead(
                            cell,
                    Arrays.asList("AAA","BBB","CCC","DDD","EEE")
                    );

            EasyExcelUtil.ExcelDefaultDropdownListDataValidationSetter
                    dataValidationSetter
                    = new EasyExcelUtil.ExcelDefaultDropdownListDataValidationSetter(dataValidation);

            dataValidationSetter
                    .init()
                    .error("errorTile","errorContent")
                    .prompt("promptTile","promptContent");

            EasyExcelUtil.getSheet(writeSheetHolder).addValidationData(dataValidation);
        }


    }
}

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.lang.reflect.Field;
import java.util.List;
import java.util.Objects;

/**
 * @author Dell
 */
public class EasyExcelUtil {

    private final static char[] letters = {
            'A', 'B', 'C', 'D', 'E', 'F', 'G',
            'H', 'I', 'J', 'K', 'L', 'M', 'N',
            'O', 'P', 'Q', 'R', 'S', 'T', 'U',
            'V', 'W', 'X', 'Y', 'Z'
    };

    public static <T> List<String> getHeadersFromHeadClass(Class<T> clz) {
        List<String> headers = Pointer.list();
        Field[] comHeaders = clz.getDeclaredFields();
        for (Field header : comHeaders) {
            if (header.isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty excelProperty = header.getDeclaredAnnotation(ExcelProperty.class);
                String[] values = excelProperty.value();
                if (values.length == 1) {
                    headers.add(values[0]);
                }
            }
        }
        return headers;
    }

    public static <T> List<T> readAll(String path, Class<T> clz) {
        return EasyExcel.read(path).head(clz).sheet().doReadSync();
    }

    public static Sheet getSheet(WriteSheetHolder writeSheetHolder) {
        return writeSheetHolder.getSheet();
    }

    public static Workbook getWorkbook(WriteSheetHolder writeSheetHolder) {
        return getSheet(writeSheetHolder).getWorkbook();
    }

    public static CreationHelper getCreationHelper(WriteSheetHolder writeSheetHolder) {
        return getWorkbook(writeSheetHolder).getCreationHelper();
    }

    public static ClientAnchor createClientAnchor(WriteSheetHolder writeSheetHolder) {
        return getCreationHelper(writeSheetHolder).createClientAnchor();
    }

    public static Drawing createDrawing(WriteSheetHolder writeSheetHolder) {
        return getSheet(writeSheetHolder).createDrawingPatriarch();
    }


    public static DataValidationHelper getDataValidationHelper(WriteSheetHolder writeSheetHolder) {
        return getSheet(writeSheetHolder).getDataValidationHelper();
    }
    
    public static String convertToStringColumn(int column){
        StringBuffer sb = new StringBuffer();
        int columnSize = column + 1;

        for (int letterLen = 1; ; letterLen++) {
            //-26-26*26-26*26*26-...
            // Z   ZZ   ZZZ
            //其实 A=0 AA=00 AAA=000 其他长度同理
            //所以每一个都是从0开始的计算的
            //所以要把前面的26倍数的数减去，然后进行进制计算
            int surplus = columnSize - Pointer.pow(letters.length, letterLen);
            if (surplus <= 0) {
                //列的索引也是从0开始的，与转换后的进制同索引，A是0，B是1 重要，
                //想想数字的进制转换
                int columnIdx = columnSize - 1;
                //其实 A=0 AA=00 AAA=000 其他长度同理
                //所以每一个都是从0开始的计算的
                //26进制计算
                do {
                    int remainder = columnIdx % letters.length;
                    sb.append(letters[remainder]);
                    columnIdx = columnIdx / letters.length;
                } while (columnIdx > 0);
                //字母长度不够，要用第一位填充，也就是A
                int diffLen = letterLen - sb.length();
                if (diffLen > 0) {
                    for (int i = 0; i < diffLen; i++) {
                        sb.append(letters[0]);
                    }
                }
                sb.reverse();
                break;
            }
            columnSize = surplus;
        }
        return sb.toString();
    }


    public static class ExcelDropdownListValuesCreation {

        private Workbook workbook;
        private String hiddenSheet;
        private String[] values;
        private int listColumn;
        private Boolean isNumber;

        public ExcelDropdownListValuesCreation(Workbook workbook) {
            this.workbook = workbook;
        }

        public ExcelDropdownListValuesCreation setHiddenSheet(String hiddenSheet) {
            this.hiddenSheet = hiddenSheet;
            return this;
        }

        public ExcelDropdownListValuesCreation setValues(String[] values) {
            this.values = values;
            return this;
        }

        public ExcelDropdownListValuesCreation setListColumn(int listColumn) {
            this.listColumn = listColumn;
            return this;
        }

        public ExcelDropdownListValuesCreation isNumber(Boolean isNumber) {
            this.isNumber = isNumber;
            return this;
        }

        public void create() {
            Sheet sheet = workbook.getSheet(hiddenSheet);
            if (Objects.isNull(sheet)) {
                sheet = workbook.createSheet(hiddenSheet);
            }
            int sheetIdx = workbook.getSheetIndex(sheet);
            SheetVisibility visibility = workbook.getSheetVisibility(sheetIdx);
            if (visibility == SheetVisibility.VISIBLE) {
                workbook.setSheetHidden(sheetIdx, true);
            }
            for (int i = 0; i < values.length; i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row)) {
                    row = sheet.createRow(i);
                }
                Cell colCell = row.createCell(listColumn);
                if (Objects.nonNull(isNumber) && isNumber) {
                    colCell.setCellType(CellType.NUMERIC);
                    colCell.setCellValue(Double.parseDouble(values[i]));
                } else {
                    colCell.setCellValue(values[i]);
                }

            }
        }

        public String getListFormula() {
            String columnLetters = convertToStringColumn(listColumn);
            return hiddenSheet + "!$" + columnLetters + "$1:$" + columnLetters + "$" + values.length;
        }


    }

    public static class ExcelDropdownListDataValidationBuilder {

        private DataValidationHelper dataValidationHelper;

        public ExcelDropdownListDataValidationBuilder(WriteSheetHolder writeSheetHolder) {
            this(getDataValidationHelper(writeSheetHolder));
        }

        public ExcelDropdownListDataValidationBuilder(DataValidationHelper dataValidationHelper) {
            this.dataValidationHelper = dataValidationHelper;
        }

        public DataValidation createDataValidation(int firstRow, int lastRow, int firstCol, int lastCol, List<String> dropdownList) {
            return createDataValidation(firstRow, lastRow, firstCol, lastCol, dropdownList.toArray(new String[0]));
        }

        public DataValidation createDataValidation(int firstRow, int lastRow, int firstCol, int lastCol, String listFormula) {
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            DataValidationConstraint dataValidationConstraint = this.dataValidationHelper.createFormulaListConstraint(listFormula);
            return this.dataValidationHelper.createValidation(dataValidationConstraint, cellRangeAddressList);
        }

        public DataValidation createDataValidation(int firstRow, int lastRow, int firstCol, int lastCol, String[] dropdownList) {
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            DataValidationConstraint dataValidationConstraint = this.dataValidationHelper.createExplicitListConstraint(dropdownList);
            return this.dataValidationHelper.createValidation(dataValidationConstraint, cellRangeAddressList);
        }

        public DataValidation createWholeColumnDataValidationAfterHead(Cell headCell, List<String> dropdownList) {
            return createWholeColumnDataValidationAfterHead(headCell, dropdownList.toArray(new String[0]));
        }

        public DataValidation createWholeColumnDataValidationAfterHead(Cell headCell, String listFormula) {
            return this.createDataValidation(
                    headCell.getRowIndex() + 1,
                    SpreadsheetVersion.EXCEL2007.getMaxRows() - 1,
                    headCell.getColumnIndex(),
                    headCell.getColumnIndex(),
                    listFormula
            );
        }

        public DataValidation createWholeColumnDataValidationAfterHead(Cell headCell, String[] dropdownList) {
            return this.createDataValidation(
                    headCell.getRowIndex() + 1,
                    SpreadsheetVersion.EXCEL2007.getMaxRows() - 1,
                    headCell.getColumnIndex(),
                    headCell.getColumnIndex(),
                    dropdownList
            );
        }
    }


    public static class ExcelCommentBuilder {

        private Drawing drawing;
        private CreationHelper creationHelper;

        public ExcelCommentBuilder(WriteSheetHolder writeSheetHolder) {
            this(createDrawing(writeSheetHolder), getCreationHelper(writeSheetHolder));
        }

        public ExcelCommentBuilder(Drawing drawing,
                                   CreationHelper creationHelper) {
            this.drawing = drawing;
            this.creationHelper = creationHelper;
        }

        private ClientAnchor createAnchor(int row1, int col1, int row2, int col2) {
            ClientAnchor anchor = creationHelper.createClientAnchor();
            anchor.setRow1(row1);
            anchor.setRow2(row2);
            anchor.setCol1(col1);
            anchor.setCol2(col2);
            return anchor;
        }

        public Comment createComment(int row1, int col1, int row2, int col2, String message) {
            ClientAnchor anchor = createAnchor(row1, col1, row2, col2);
            Comment comment = drawing.createCellComment(anchor);
            RichTextString richTextString = creationHelper.createRichTextString(message);
            comment.setString(richTextString);
            return comment;
        }

        public Comment createCellComment(Cell cell, String message) {
            return createComment(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex(), message);
        }


    }


    public static class ExcelDefaultDropdownListDataValidationSetter {

        private DataValidation dataValidation;

        public ExcelDefaultDropdownListDataValidationSetter(DataValidation dataValidation) {
            this.dataValidation = dataValidation;
        }

        public ExcelDefaultDropdownListDataValidationSetter init() {
            showDropDown(true);
            showError(true);
            showPrompt(true);
            errorStyle(DataValidation.ErrorStyle.WARNING);
            return this;
        }

        public ExcelDefaultDropdownListDataValidationSetter showDropDown(boolean b) {
            dataValidation.setSuppressDropDownArrow(b);
            return this;
        }

        public ExcelDefaultDropdownListDataValidationSetter showError(boolean b) {
            dataValidation.setShowErrorBox(b);
            return this;
        }

        public ExcelDefaultDropdownListDataValidationSetter showPrompt(boolean b) {
            dataValidation.setShowPromptBox(b);
            return this;
        }

        public ExcelDefaultDropdownListDataValidationSetter errorStyle(int style) {
            dataValidation.setErrorStyle(style);
            return this;
        }

        public ExcelDefaultDropdownListDataValidationSetter prompt(String title, String content) {
            dataValidation.createPromptBox(title, content);
            return this;
        }

        public ExcelDefaultDropdownListDataValidationSetter error(String title, String content) {
            dataValidation.createErrorBox(title, content);
            return this;
        }

        public DataValidation get() {
            return this.dataValidation;
        }
    }


}

 EasyExcel.write(outPath)
                .head(head)
                .sheet("sheet1")
                .registerWriteHandler(new CommentHandler())
                .doWrite(data);