EasyExcel导出Excel时创建下拉框

最新推荐文章于 2025-09-30 21:00:00 发布

原创最新推荐文章于 2025-09-30 21:00:00 发布 · 1.3k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#excel

Java 同时被 2 个专栏收录

15 篇文章

订阅专栏

Excal

2 篇文章

订阅专栏

该代码示例展示了一个`SpinnerWriteHandler`类，用于在创建Excel工作表时设置数据验证，创建下拉框列表。在`exportTemplate`方法中，程序将数据转换为Excel格式并添加下拉框选项，如小学,初中,高中,大学以及语文,数学,英语，然后导出到Excel文件。

@Data
public class SpinnerWriteHandler implements SheetWriteHandler {
	//传入下拉框数组
    private String[] pidName;
    //传入的下拉框数组
    private String[] name;

    public SpinnerWriteHandler() {
    }

    public SpinnerWriteHandler(String[] pidName, String[] name) {
        this.pidName = pidName;
        this.name = name;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }
    
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    	//设置下拉框数据
        Map<Integer, String[]> map = new HashMap<>();
        map.put(1, this.pidName);
        map.put(2, this.name);
        
        Sheet sheet = writeSheetHolder.getSheet();
        // 开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        for (Map.Entry<Integer, String[]> entry : map.entrySet()) {
            //起始行、终止行、起始列、终止列 
            CellRangeAddressList addressList = new CellRangeAddressList(1, 3000, 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);
        }
    }
}

    public void exportTemplate(HttpServletResponse response) throws Exception {
    	//导出的数据(根据需要自行修改)
        List<GamsApplicationAssetcardDTO> list = new ArrayList<>();
        //下拉框数据(根据需要自行修改)
        String[] pidNames = {"小学", "初中", "高中", "大学"};
        //下拉框数据(根据需要自行修改)
        String[] names = {"语文", "数学", "英语"};

		//把导出数据的GamsApplicationAssetcardDTO实体类转换成导出的GamsApplicationAssetcardExcel类
        List targetList = new ArrayList<>(list.size());
        for (Object source : list) {
            Object target = GamsApplicationAssetcardExcel.class.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" +  fileName + ".xlsx");
        EasyExcel
                .write(response.getOutputStream(), GamsApplicationAssetcardExcel.class)
                .registerWriteHandler(new SpinnerWriteHandler(pidNames, names)) //设置下拉框数组
                .sheet("测试")
                .doWrite(list); //添加数据
    }