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

目录

1 Maven依赖

2 实现代码 

3 调试代码

4 调试结果

 注：

---

1 Maven依赖

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- EasyExcel文档处理工具 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>
        <!--    hutool工具包    -->
        <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 调试结果

 注：

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

xudongbase: 主要是项目中可以用到的共通方法 - Gitee.comhttps://gitee.com/xudong_master/xudongbase/tree/easyexcel/