String literals in formulas can’t be bigger than 255 characters ASCII

布满荆棘的人生 2021-11-17 09:26 220阅读 0赞

最近在项目中用到poi生成excel文件，其中需要将某一列限定为特定的值

原始的方法为：

private static HSSFDataValidation getDataValidationList(HSSFSheet sheet, short firstRow,
                                                                short firstCol, short endRow,
                                                                short endCol, List<String> strList)
        {
            LOGGER.info("dataList: {}.", strList);
            String[] dataArray = strList.toArray(new String[0]);
            HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
            DataValidationConstraint dvConstraint = (DataValidationConstraint)dvHelper
                .createExplicitListConstraint(dataArray);
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
                endCol);
            HSSFDataValidation validation = (HSSFDataValidation)dvHelper.createValidation(dvConstraint,
                addressList);
    
            if (null != validation)
            {
                sheet.addValidationData(validation);
            }
            return validation;
        }

但是当一个单元格的字符数大于255时，就报如下问题：

java.lang.IllegalArgumentException: String literals in formulas can’t be bigger than 255 characters ASCII

解决方法如下：

private static HSSFDataValidation getDataValidationList4Col(HSSFSheet sheet, short firstRow,
                                                                    short firstCol, short endRow,
                                                                    short endCol, List<String> colName,
                                                                    HSSFWorkbook wbCreat)
        {
            String[] dataArray = colName.toArray(new String[0]);
            HSSFSheet hidden = wbCreat.createSheet("hidden");
            HSSFCell cell = null;
            for (int i = 0, length = dataArray.length; i < length; i++)
            {
                String name = dataArray[i];
                HSSFRow row = hidden.createRow(i);
                cell = row.createCell(0);
                cell.setCellValue(name);
            }
    
            Name namedCell = wbCreat.createName();
            namedCell.setNameName("hidden");
            namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
            //加载数据,将名称为hidden的
            DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
    
            // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
                endCol);
            HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    
            //将第二个sheet设置为隐藏
            wbCreat.setSheetHidden(1, true);
    
            if (null != validation)
            {
                 sheet.addValidationData(validation);
             }
            return validation;
        }