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

站在最高处呐喊的男人!
于 2024-10-10 19:21:24 发布
阅读量236
点赞数 2
CC 4.0 BY-SA版权
文章标签： java 前端 String literals 255 characters more than
本文链接：https://blog.csdn.net/ainuser/article/details/142830086
# 情境
今天在导出excel模板（列包含单选下拉，值很长很长），所以出现了超长


# 思路
借鉴网上，先将值存在另外一个隐藏的sheet中，在需要的地方引用


# 重点代码块（涉及到hidden的不要改）
if(value.length() > 255){
                        // 处理超大文本下拉
                        Sheet hidden = workbook.createSheet("hidden");
                        Cell cell;
                        // 此处的efcOptions为超长多选下拉的值
                        for (int k = 0, length = efcOptions.length; k < length; k++){
                            String name = efcOptions[k];
                            Row hssfRow = hidden.createRow(k);
                            cell = hssfRow.createCell(0);
                            cell.setCellValue(name);
                        }

                        Name namedCell = workbook.createName();
                        namedCell.setNameName("hidden");
                        namedCell.setRefersToFormula("hidden!$A$1:$A$" + efcOptions.length);
                        //加载数据,将名称为hidden的
                        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");

                        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
                        CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, i,i);
                        HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
                        sheet.addValidationData(validation);

                        //将第二个sheet设置为隐藏（这个注释掉可以看到隐藏的sheet页存储的值，用于调试）
                        workbook.setSheetHidden(1, true);

                        // 我这里是设置导出后的下拉默认值，不需要可以不写
                        HSSFCell cell1 = row1.createCell(i);
                        sheet.setColumnWidth(i, 5000);
                        cell1.setCellValue(efcOptions[0]);

                    }


# show you all code
for (int j = 0; j < dynamicFiled.size(); j++) {
                EmpFieldConfigEntity fce = dynamicFiled.get(j);
                // 如果列名和需要设置多选下拉的字段名称相同，则获取对应的值设置多选
                if(fields1[i].replace("*","").equals(fce.getEfcName())){
                    String efcOption = fce.getEfcOption();
                    String[] efcOptions = efcOption.split("\\|");
                    String value = Arrays.asList(efcOptions).stream().map(n -> n.trim()).collect(Collectors.joining(","));

                    System.out.println("================>"+value.length());
                    if(value.length() > 255){
                        // 处理超大文本下拉
                        Sheet hidden = workbook.createSheet("hidden");
                        Cell cell;
                        for (int k = 0, length = efcOptions.length; k < length; k++){
                            String name = efcOptions[k];
                            Row hssfRow = hidden.createRow(k);
                            cell = hssfRow.createCell(0);
                            cell.setCellValue(name);
                        }

                        Name namedCell = workbook.createName();
                        namedCell.setNameName("hidden");
                        namedCell.setRefersToFormula("hidden!$A$1:$A$" + efcOptions.length);
                        //加载数据,将名称为hidden的
                        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");

                        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
                        CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, i,i);
                        HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
                        sheet.addValidationData(validation);

                        //将第二个sheet设置为隐藏
                        workbook.setSheetHidden(1, true);

                        HSSFCell cell1 = row1.createCell(i);
                        sheet.setColumnWidth(i, 5000);
                        cell1.setCellValue(efcOptions[0]);

                    } else {
                        CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, i, i);
                        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{value});
                        DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                        dataValidation.setSuppressDropDownArrow(false);
                        sheet.addValidationData(dataValidation);

                        HSSFCell cell = row1.createCell(i);
                        sheet.setColumnWidth(i, 5000);
                        cell.setCellValue(efcOptions[0]);
                    }

                }
            }