【Java】EasyExcel实现导出带下拉框的excel文件【复制可用】

一、直接看实际效果图

二、具体实现

        1pom.xml中导入easyexcel依赖

<!-- EasyExcel -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.2.7</version>
		</dependency>

三、Controller写导出的接口

/**
     * 测试导出Excel表格，带下拉框
     * 生成excel表格
     * @return
     */
    @GetMapping(value = "/exportExcelpulldownmenu")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
        try {
            String fileName = "模板.xls";

            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 设置背景颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            // 设置头字体
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)14);
            // 字体加粗
            headWriteFont.setBold(true);
            headWriteCellStyle.setWriteFont(headWriteFont);
            // 设置头居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            // 内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 设置内容字体
            WriteFont contentWriteFont = new WriteFont();
            contentWriteFont.setFontHeightInPoints((short)12);
            contentWriteFont.setFontName("宋体");
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            // 设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 设置 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置单元格格式为 文本
            contentWriteCellStyle.setDataFormat((short)49);

            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            // 假数据 实际开发中一般是从数据库中查询
            List<Employee> objects = new ArrayList<>();
            for (int i = 0; i < 3; i++)
            {
                Employee employee = new Employee();
                employee.setWeight(i + "10");
                employee.setSchool(i + "NB");
                objects.add(employee);
            }

            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            // 设置表名，引脚名，文件格式，list数据
            // SpinnerWriterHandler拦截器无参
//            EasyExcel.write(response.getOutputStream(), Employee.class)
//                    .registerWriteHandler(horizontalCellStyleStrategy)
//                    .registerWriteHandler(new SpinnerWriteHandler())
//                    .sheet("模板")
//                    .doWrite(objects);

            // SpinnerWriterHandler拦截器有参
            EasyExcel.write(response.getOutputStream(), Employee.class)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new SpinnerWriteHandler("10"))
                    .sheet("模板")
                    .doWrite(objects);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

四、Controller中实体类Employee

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
public class Employee {
    private String weight;
    private String school;
}

五、自定义一个类实现SheetWriteHandler接口即可

public class SpinnerWriteHandler implements SheetWriteHandler
{
    // 需要传递参数进来就定义一个变量和构造方法
    private String userId;


    public SpinnerWriteHandler() {
    }

    //传递参数
    public SpinnerWriteHandler(String userId) {
        this.userId = userId;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder)
    {
        
    }
    
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder)
    {
        SpinnerWriteHandler spinnerWriteHandler = new SpinnerWriteHandler(userId);


//        String[] ageTypes = new String[] {"0 - 14", "15 - 25", "26 - 50", "51 - ~"};
        String[] ageTypes = new String[10];

        for (int i = 0; i < 10; i++) {
            ageTypes[i] = userId+i; //拼接
        }
        String[] schoolTypes = new String[] {"清华大学", "北京大学", "郑州大学", "南京大学"};
        for (int i = 0; i < 4; i++) {
            schoolTypes[i] = userId+schoolTypes[i];
        }
        Map<Integer, String[]> mapDropDown = new HashMap<>();
        // 这里的key值 对应导出列的顺序 从0开始
        mapDropDown.put(0, ageTypes);
        mapDropDown.put(1, schoolTypes);
        // 获取到当前的sheet
        Sheet sheet = writeSheetHolder.getSheet();
        /// 开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();// 设置下拉框
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet())
        {
            /*** 起始行、终止行、起始列、终止列 **/
            CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, entry.getKey(), entry.getKey());
            /*** 设置下拉框数据 **/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            /*** 处理Excel兼容性问题 **/
            if (dataValidation instanceof XSSFDataValidation)
            {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            }
            else
            {
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);
        }
        
    }
}

  最终进行测试即可。