EasyExcel 实现自定义单选下拉框

谁践踏了优雅 2023-10-07 17:18 8阅读 0赞

**目录**

1 Maven依赖

2 SpinnerModel

3 CustomSpinnerHandler

4 调试代码

5 调试结果

注：

--------------------

## 1 Maven依赖 ##

<dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.8</version>
            </dependency>
            
            <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.6.2</version>
            </dependency>

## 2 SpinnerModel ##

下拉框信息类。

package com.xudongbase.easyexcel.model;
    
    import cn.hutool.core.util.StrUtil;
    import com.xudongbase.common.poi.constant.POIExcelConstant;
    import com.xudongbase.easyexcel.model.common.SheetRangeModel;
    import lombok.Getter;
    
    /**
     * 下拉框信息类
     *
     * @author xudongmaster
     */
    @Getter
    public class SpinnerModel extends SheetRangeModel {
        /**
         * 下拉框数据
         */
        private String[] spinnerData;
    
        private SpinnerModel() {
        }
    
        /**
         * 生成下拉框信息
         *
         * @param sheetName     sheet页名称
         * @param colIndex      列索引
         * @param startRowIndex 开始行索引
         * @param spinnerData   下拉框数据
         * @return
         */
        public static SpinnerModel createSpinnerModel(String sheetName, Integer colIndex, Integer startRowIndex, String[] spinnerData) {
            return createSpinnerModel(sheetName, colIndex, colIndex, startRowIndex, POIExcelConstant.ROW_INDEX_MAX, spinnerData);
        }
    
        /**
         * 生成下拉框信息
         *
         * @param sheetName     sheet页名称
         * @param startColumnIndex 开始列索引
         * @param endColumnIndex   结束列索引
         * @param startRowIndex 开始行索引
         * @param endRowIndex   结束行索引
         * @param spinnerData   下拉框数据
         */
        public static SpinnerModel createSpinnerModel(String sheetName, Integer startColumnIndex, Integer endColumnIndex, Integer startRowIndex, Integer endRowIndex, String[] spinnerData) {
            SpinnerModel spinnerModel = new SpinnerModel();
            //sheet页名称
            spinnerModel.sheetName = StrUtil.isNotBlank(sheetName) ? sheetName : POIExcelConstant.SHEET_NAME_DEFAULT;
            //开始列索引
            startColumnIndex = startColumnIndex != null ? startColumnIndex : POIExcelConstant.COL_INDEX_MIN;
            spinnerModel.startColumnIndex = startColumnIndex;
            //结束列索引
            endColumnIndex = endColumnIndex != null ? endColumnIndex : startColumnIndex;
            spinnerModel.endColumnIndex = endColumnIndex;
            //开始行索引
            startRowIndex = startRowIndex != null ? startRowIndex : POIExcelConstant.ROW_INDEX_MIN;
            spinnerModel.startRowIndex = startRowIndex;
            //结束行索引
            endRowIndex = endRowIndex != null ? endRowIndex : POIExcelConstant.ROW_INDEX_MAX;
            spinnerModel.endRowIndex = endRowIndex;
            //下拉框数据
            spinnerData = spinnerData != null ? spinnerData : new String[]{};
            spinnerModel.spinnerData = spinnerData;
            return spinnerModel;
        }
    
    
    }

## 3 CustomSpinnerHandler ##

自定义下拉框处理器。

package com.xudongbase.easyexcel.handler;
    
    import cn.hutool.core.collection.CollUtil;
    import cn.hutool.core.collection.CollectionUtil;
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.excel.write.handler.SheetWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
    import com.xudongbase.common.poi.constant.POIExcelConstant;
    import com.xudongbase.common.poi.util.POIExcelUtil;
    import com.xudongbase.easyexcel.model.SpinnerModel;
    import org.apache.poi.ss.usermodel.DataValidationHelper;
    import org.apache.poi.ss.usermodel.Sheet;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.stream.Collectors;
    
    /**
     * 自定义下拉框处理器
     *
     * @author xudongmaster
     */
    public class CustomSpinnerHandler implements SheetWriteHandler {
        /**
         * 下拉框信息列表
         */
        List<SpinnerModel> spinnerList = new ArrayList<>();
        /**
         * sheet页名称列表
         */
        List<String> sheetNameList = new ArrayList<>();
    
        /**
         * 构造方法
         *
         * @param spinnerList 下拉框信息列表
         */
        public CustomSpinnerHandler(List<SpinnerModel> spinnerList) {
            this.spinnerList = CollectionUtil.isNotEmpty(spinnerList) ? spinnerList.stream().filter(x ->
                    StrUtil.isNotEmpty(x.getSheetName()) && x.getSpinnerData() != null
                            && x.getSpinnerData().length > 0
            ).collect(Collectors.toList()) : new ArrayList<>();
            this.sheetNameList = CollectionUtil.isNotEmpty(this.spinnerList) ? this.spinnerList.stream().map(x ->
                    x.getSheetName()).distinct().collect(Collectors.toList()) : new ArrayList<>();
        }
    
        /**
         * Sheet页创建之前
         *
         * @param writeWorkbookHolder
         * @param writeSheetHolder
         */
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    
        }
    
        /**
         * Sheet页创建之后
         *
         * @param writeWorkbookHolder
         * @param writeSheetHolder
         */
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            //不需要绑定下拉框，或者当前sheet页不需要绑定下拉框
            if (CollUtil.isEmpty(spinnerList) || sheetNameList.contains(sheet.getSheetName()) == false) {
                return;
            }
            //当前Sheet页的下拉框信息
            List<SpinnerModel> sheetSpinnerList = spinnerList.stream().filter(x ->
                    StrUtil.equals(x.getSheetName(), sheet.getSheetName())).collect(Collectors.toList());
            //当前sheet页不需要绑定下拉框
            if (CollUtil.isEmpty(sheetSpinnerList)) {
                return;
            }
            DataValidationHelper helper = sheet.getDataValidationHelper();
            //设置下拉框
            for (SpinnerModel spinnerModel : sheetSpinnerList) {
                //校验开始列索引大于结束列索引，或者开始行索引大于结束行索引
                if (spinnerModel.getStartColumnIndex() > spinnerModel.getEndColumnIndex() || spinnerModel.getStartRowIndex() > spinnerModel.getEndRowIndex()) {
                    continue;
                }
                //校验开始列索引和结束列索引是否小于最小列索引
                if (spinnerModel.getStartColumnIndex() < POIExcelConstant.COL_INDEX_MIN || spinnerModel.getEndColumnIndex() < POIExcelConstant.COL_INDEX_MIN) {
                    continue;
                }
                //校验开始行索引和结束行索引是否小于最小行索引，开始行索引和结束行索引是否大于最大行索引
                if (spinnerModel.getStartRowIndex() < POIExcelConstant.ROW_INDEX_MIN || spinnerModel.getEndRowIndex() < POIExcelConstant.ROW_INDEX_MIN
                        || spinnerModel.getStartRowIndex() > POIExcelConstant.ROW_INDEX_MAX || spinnerModel.getEndRowIndex() > POIExcelConstant.ROW_INDEX_MAX) {
                    continue;
                }
                //绑定下拉框数据
                POIExcelUtil.setSpinnerData(writeWorkbookHolder.getWorkbook(), sheet, spinnerModel.getStartRowIndex(), spinnerModel.getEndRowIndex()
                        , spinnerModel.getStartRowIndex(), spinnerModel.getEndColumnIndex(), spinnerModel.getSpinnerData());
            }
        }
    }

## 4 调试代码 ##

/**
         * 调试绑定下拉框方法
         *
         * @param response
         */
        @GetMapping("/testSpinner")
        public void testSpinner(HttpServletResponse response) throws IOException {
            //生成表格数据
            List<List<Object>> dataList = new ArrayList<>();
            dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));
            dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头1", "表头2", "表头3", "表头4"})));
            dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));
            List<SpinnerModel> spinnerList = new ArrayList<>();
            //下拉框数据
            String[] spinnerData = new String[]{"是", "否"};
            spinnerList.add(SpinnerModel.createSpinnerModel("模板", 1, 1, spinnerData));
            spinnerList.add(SpinnerModel.createSpinnerModel("模板", 2, 2, spinnerData));
            response.addHeader("Content-Disposition", "attachment;filename=test.xlsx");
            //设置类型，扩展名为.xlsx
            response.setContentType("application/octet-stream");
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(new CustomSpinnerHandler(spinnerList))
                    .build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(dataList, writeSheet);
            //千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        }

## 5 调试结果 ##

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_12_color_FFFFFF_t_70_g_se_x_16][]

## 注： ##

觉得这篇博客写的不错的可以前往Gitee点个Star，源码请查看Gitee的xudongbase项目easyexcel分支。

[xudongbase: 主要是项目中可以用到的共通方法 - Gitee.com![favicon-9007bd527d8a7851c8330e783151df58.ico][]https://gitee.com/xudong\_master/xudongbase/tree/easyexcel/][xudongbase_ _ - Gitee.com_favicon-9007bd527d8a7851c8330e783151df58.ico_https_gitee.com_xudong_master_xudongbase_tree_easyexcel]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_12_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/a9e2dfdd6a8f4c29b0225d40889b43fb.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5pet5Lic5oCq,size_12,color_FFFFFF,t_70,g_se,x_16
[favicon-9007bd527d8a7851c8330e783151df58.ico]: https://assets.gitee.com/assets/favicon-9007bd527d8a7851c8330e783151df58.ico
[xudongbase_ _ - Gitee.com_favicon-9007bd527d8a7851c8330e783151df58.ico_https_gitee.com_xudong_master_xudongbase_tree_easyexcel]: https://gitee.com/xudong_master/xudongbase/tree/easyexcel/