EasyExcel增加下拉选择框

概述

本文将用示例介绍如何用EasyExcel导出下拉框

示例

编写样式处理类: TitleHandler

@Slf4j
public class TitleHandler implements SheetWriteHandler {

    /**
     * 下拉框值
     */
    private Map<Integer,String[]> dropDownMap;

    /**
     * 多少行有下拉
     */
    private final static Integer rowSize = 200;

    public TitleHandler(Map<Integer,String[]> dropDownMap) {
        this.dropDownMap = dropDownMap;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();

        dropDownMap.forEach((celIndex, strings) -> {
            // 区间设置
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, rowSize, celIndex, celIndex);
            // 下拉内容
            DataValidationConstraint constraint = helper.createExplicitListConstraint(strings);
            DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
            sheet.addValidationData(dataValidation);
        });
    }
}

编写工具类

public class EasyExcelUtil {
    public static <T> void writeExcelWithModel(OutputStream outputStream, Class<T> clazz, Map<Integer,String[]> dropDownMap) throws IOException {
        EasyExcel.write(outputStream, clazz).registerWriteHandler(new TitleHandler(dropDownMap)).sheet("模板").doWrite(ListUtil.empty());
    }
}

测试

public class Test{
    @Data
    @ColumnWidth(20) // 定义列宽
    public static class TestVO {
        @ExcelProperty(value = "*姓名", index = 0)
        private String name;
        @ExcelProperty(value = "*年龄", index = 1)
        private int age;
        @ExcelProperty(value = "学校", index = 2)
        private String school;
    }
    /**
     * 测试导出下拉款
     * @throws IOException
     */
    @Test
    public void testDropDown() throws IOException {
        // 输出流
        OutputStream outputStream = new FileOutputStream(new File("D:\\1.xlsx"));
        HashMap<Integer, String[]> dropDownMap = new HashMap<>();
        // 指定下拉框
        String[] school = {"一中","二中","三中"};
        dropDownMap.put(2,school);
        EasyExcelUtil.writeExcelWithModel(outputStream, TestVO.class, dropDownMap);
    }
}