导出带下拉选项的Excel基于EasyExcel实现

前言

       今天就是记录一个计算点而已，帮小组成员搽屁股改bug。场景就是导出Excel的模板，希望枚举字段有下拉选择。

---

一、技术选型场景

       我们这里用的是阿里开源的EasyExcel，导出模板是后端动态生成的。

二、使用步骤

1.下载模板示例

/**
     * 下载导入模板
     *
     * @param response
     * @throws IOException
     */
    @GetMapping("/downloadExcelTemplate")
    public void downloadExcelTempplate(HttpServletResponse response) throws IOException {
        List<RecordsImportTemplate> list = new ArrayList<>();

        //定义一个map key是需要添加下拉框的列的index value是下拉框数据
        Map<Integer, String[]> mapDropDown = new HashMap<>();
        //设置是否车主下拉框数据
        String[] belongOwner = {"是","否"};

        //地区
        String[] carColor = {"白色","黑色","银色","红色","蓝色","金色","灰色","绿色","棕色","粉色","其它"};

        //等级下拉选
        String[] carType = {"内部车辆","临时车辆"};

        //下拉选在Excel中对应的列
        mapDropDown.put(2,belongOwner);
        mapDropDown.put(6,carColor);
        mapDropDown.put(7,carType);


        RecordsImportTemplate bean = new RecordsImportTemplate();
        bean.setApplicantName("示例:张三");
        bean.setPhone("18808080808");
        bean.setBelongOwner("是");
        bean.setCompany("慧联无限");
        bean.setCarNo("鄂A35569");
        bean.setBrand("奥迪A6");
        bean.setCarColor("白色");
        bean.setCard1("");
        bean.setCard2("");
        bean.setCarType("内部车辆");
        bean.setRelateParkType("全部");
        Calendar calendar = Calendar.getInstance();
        // 时分秒毫秒 设置为零
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);
        bean.setEffectiveDate(calendar.getTime());
        bean.setInvalidDate(calendar.getTime());
        list.add(bean);

        bean = new RecordsImportTemplate();
        bean.setApplicantName("示例:张三");
        bean.setPhone("18808080808");
        bean.setBelongOwner("否");
        bean.setCompany("慧联无限");
        bean.setCarNo("鄂A35569");
        bean.setBrand("奥迪A6");
        bean.setCarColor("白色");
        bean.setCard1("");
        bean.setCard2("");
        bean.setCarType("内部车辆");
        bean.setRelateParkType("停车场1,停车场2");
        // 时分秒毫秒 设置为零，这里有很好的工具包，为啥还自己操作Calendar？不能理解小伙伴为啥。。。
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);
        bean.setEffectiveDate(calendar.getTime());
        bean.setInvalidDate(calendar.getTime());
        list.add(bean);

        // 这里URLEncoder.encode可以防止中文乱码 easyexcel没有关系
        String fileName = URLEncoder.encode("车辆数据表格模板", "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        //需要下拉框处理，关键是registerWriteHandler
        EasyExcel.write(response.getOutputStream(), RecordsImportTemplate.class).sheet("模板").registerWriteHandler(new CustomSheetWriteHandler(mapDropDown)).doWrite(list);
    }

2.自定义handler

代码如下（示例）：

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Map;

/**
 * @author zhengwen
 */
public class CustomSheetWriteHandler implements SheetWriteHandler {

    private Map<Integer, String[]> mapDropDown;

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

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

    }

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

        //获取工作簿
        Sheet sheet = writeSheetHolder.getSheet();
        ///开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //设置下拉框
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
            /*起始行、终止行、起始列、终止列  起始行为1即表示表头不设置**/
            //这里设置65535可能又问题，因为这个是excel的最大行数，如果数据量超过这个数，就会报错
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
            /*设置下拉框数据**/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);

            //阻止输入非下拉选项的值
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            dataValidation.setShowErrorBox(true);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("提示", "输入值与单元格定义格式不一致");
            dataValidation.createPromptBox("填写说明", "填写内容只能为下拉数据集中的类型");

            sheet.addValidationData(dataValidation);
        }
    }
}

       注释很清楚，应该很好理解。

3.遗留问题

1、下拉选项超过50报错
       博友已解：https://blog.csdn.net/m0_67402588/article/details/123482034
2、多选下拉框
       暂未解决，我现在暂不需要，还有其他任务，所以也没有细究，欢迎分享

---

总结

• easyExcel开源的，基本够用
• 图片导入、导出，easyExcel支持有限，还是得原生处理
• 遗留问题我后面有时间再分享，暂时先赶进度，如果有博友处理了，欢迎评论私信分享
         就到这里，还要赶进度哦，希望能帮到大家。