POI 绑定单选下拉框（下拉框数据可以超过256字符）

r囧r小猫 2023-09-29 10:27 17阅读 0赞

**目录**

1 Maven依赖

2 实现代码

3 调试代码

4 调试结果

注：

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

## 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 实现代码 ##

/**
         * 设置下拉框数据
         *
         * @param workbook         workbook对象
         * @param sheet            sheet页对象
         * @param startRowIndex    开始行索引
         * @param endRowIndex      结束行索引
         * @param startColumnIndex 开始列索引
         * @param endColumnIndex   结束列索引
         * @param spinnerData      下拉框数据
         */
        public static void setSpinnerData(Workbook workbook, Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex
                , int endColumnIndex, String[] spinnerData) {
            if (spinnerData == null || spinnerData.length == 0) {
                return;
            }
            if (startRowIndex < POIExcelConstant.ROW_INDEX_MIN) {
                startRowIndex = POIExcelConstant.ROW_INDEX_MIN;
            }
            if (endRowIndex > POIExcelConstant.ROW_INDEX_MAX) {
                endRowIndex = POIExcelConstant.ROW_INDEX_MAX;
            }
            if (startColumnIndex < POIExcelConstant.COL_INDEX_MIN) {
                startColumnIndex = POIExcelConstant.COL_INDEX_MIN;
            }
            int spinnerDataCharCount = 0;
            if (spinnerData.length >= 256) {
                //大于256字符设置下拉框
                setSpinnerDataGt256(workbook, sheet, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, spinnerData);
                return;
            }
            for (String spinnerStr : spinnerData) {
                int chineseCount = MatchUtil.matchChineseCount(spinnerStr);
                spinnerDataCharCount += chineseCount * 2 + spinnerStr.length() - chineseCount;
                if (spinnerDataCharCount > 256) {
                    //大于256字符设置下拉框
                    setSpinnerDataGt256(workbook, sheet, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, spinnerData);
                    return;
                }
            }
            //小于等于256字符设置下拉框
            setSpinnerDataLe256(sheet, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex, spinnerData);
        }
    
        /**
         * 设置下拉框数据（字符数<=256）
         *
         * @param sheet            sheet页对象
         * @param startRowIndex    开始行索引
         * @param endRowIndex      结束行索引
         * @param startColumnIndex 开始列索引
         * @param endColumnIndex   结束列索引
         * @param spinnerData      下拉框数据
         */
        public static void setSpinnerDataLe256(Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex
                , int endColumnIndex, String[] spinnerData) {
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(spinnerData);
            //设置单元格范围
            CellRangeAddressList addressList = new CellRangeAddressList(startRowIndex, endRowIndex
                    , startColumnIndex, endColumnIndex);
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        }
    
        /**
         * 设置下拉框数据（字符数>=256）下拉框数据先写入隐藏Sheet页中，再从隐藏Sheet页读取
         *
         * @param workbook         workbook对象
         * @param sheet            sheet页对象
         * @param startRowIndex    开始行索引
         * @param endRowIndex      结束行索引
         * @param startColumnIndex 开始列索引
         * @param endColumnIndex   结束列索引
         * @param spinnerData      下拉框数据
         */
        public static void setSpinnerDataGt256(Workbook workbook, Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex
                , int endColumnIndex, String[] spinnerData) {
            //设置单元格范围
            CellRangeAddressList addressList = new CellRangeAddressList(startRowIndex, endRowIndex
                    , startColumnIndex, endColumnIndex);
            String hiddenSheetName = "hiddenSheet" + cn.hutool.core.date.DateUtil.format(new Date(), "yyyyMMddHHmmssSSS");
            Sheet hiddenSheet = workbook.getSheet(hiddenSheetName);
            if (hiddenSheet == null) {
                hiddenSheet = workbook.createSheet(hiddenSheetName);
                //写入下拉数据到新的sheet页中
                for (int i = 0; i < spinnerData.length; i++) {
                    Row row = hiddenSheet.createRow(i);
                    Cell cell = row.createCell(0);
                    cell.setCellValue(spinnerData[i]);
                }
                //将新建的sheet页隐藏掉
                workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheetName), true);
            }
            DataValidationHelper helper = sheet.getDataValidationHelper();
            //获取隐藏sheet页内容
            String strFormula = "=" + hiddenSheetName + "!$A$1:$A$65535";
            DataValidationConstraint constraint = helper.createFormulaListConstraint(strFormula);
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        }

## 3 调试代码 ##

/**
         * 测试下拉框
        */
        @Test
        public void testSpinner(){
            try {
                //读文件
                XSSFWorkbook workbook = new XSSFWorkbook();
                Sheet sheet = workbook.createSheet();
                File file = new File("D:/poi/testSpinner.xlsx");
                FileUtil.createNewFile(file);
                String[] spinnerData1 = new String[]{"是", "否"};
                int dataCount = 128;
                String[] spinnerData2 = new String[dataCount];
                for (int i = 0; i < dataCount; i++) {
                    spinnerData2[i] = i + "页";
                }
                POIExcelUtil.setSpinnerData(workbook,sheet,1, POIExcelConstant.ROW_INDEX_MAX,1,1,spinnerData1);
                POIExcelUtil.setSpinnerData(workbook,sheet,1, POIExcelConstant.ROW_INDEX_MAX,2,2,spinnerData2);
                workbook.write(new FileOutputStream(file));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

## 4 调试结果 ##

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_13_color_FFFFFF_t_70_g_se_x_16][]

## 注： ##

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

[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_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_13_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/a304d033f3314590ac121088a1f63f2e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5pet5Lic5oCq,size_13,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/