java - Web development - POI exports Excel with drop-down boxes and solves the exception caused by too many arrays in the drop-down box

Question

1. If there are few drop-down arrays in Excel (about 0~20), you can export them in the following way:

            /**
             * Excel API
             */
            @SuppressWarnings("resource")
            HSSFWorkbook book = new HSSFWorkbook();  
            HSSFSheet sheet = book.createSheet("xxxx");  
            
            /**
             * 初始化参数
             */
            Map<String, String> map = new HashMap<String, String>(); // 查询时用的map 
            List<Object> list = null; 
            String[] strs = null; // 用于下拉的数组
            int startRow = 1; // 下拉的开始行 
            int endRow = 100; // 下拉的结束行
            CellRangeAddressList regions = null;
            DVConstraint constraint = null;
            CellRangeAddressList addressList = null;
            HSSFDataValidation validation = null; // 数据验证

            map.put("namespace", "xxxxxxxxxx.xxxxxxxxxx"); // 查询数据
            list = commonQueryService.queryList(map); 
            strs = StringUtil.mapListToStrs(list); // list转换为字符串数组
            cellNum = SpuEnu.CATEGORY_1.getNumber(); // 下拉的列
            
            regions = new CellRangeAddressList(startRow, endRow, cellNum, cellNum); // 开始行、结束行、开始列、结束列的下拉区域均被下拉替代 
            constraint = DVConstraint.createExplicitListConstraint(strs);  
            validation = new HSSFDataValidation(regions, constraint); // 绑定下拉框和作用区域   
            sheet.addValidationData(validation); 

2. The problem is that if there are too many drop-down arrays, the following exception message will appear in POI:

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

The solution to this problem is not easy to find online, so I will post the solution below

Answer 1

Here’s the solution:

            /**
             * Excel API
             */
            @SuppressWarnings("resource")
            HSSFWorkbook book = new HSSFWorkbook();  
            HSSFSheet sheet = book.createSheet("spu导入模板");  
            
            /**
             * 初始化参数
             */
            Map<String, String> map = new HashMap<String, String>(); // 查询时用的map 
            List<Object> list = null; 
            String[] strs = null; // 用于下拉的数组
            String hiddenSheet = null;
            int cellNum = 0;
            int startRow = 1; // 开始行 
            int endRow = 100; // 结束行
            DVConstraint constraint = null;
            CellRangeAddressList addressList = null;
            HSSFDataValidation validation = null; // 数据验证

            map.put("namespace", "xxxxxxx.xxxxx"); // 查询
            list = commonQueryService.queryList(map); 
            strs = StringUtil.mapListToStrs(list);
            hiddenSheet = "category1Hidden";
            cellNum = SpuEnu.CATEGORY_1.getNumber();
            
            HSSFSheet category1Hidden = book.createSheet(hiddenSheet); // 创建隐藏域
            for (int i = 0, length = strs.length; i < length; i++) { // 循环赋值（为了防止下拉框的行数与隐藏域的行数相对应来获取>=选中行数的数组，将隐藏域加到结束行之后）
                category1Hidden.createRow(endRow + i).createCell(cellNum).setCellValue(strs[i]);
            } 
            Name category1Name = book.createName(); 
            category1Name.setNameName(hiddenSheet); 
            category1Name.setRefersToFormula(hiddenSheet + "!A1:A" + (strs.length + endRow)); // A1:A代表隐藏域创建第?列createCell(?)时。以A1列开始A行数据获取下拉数组
            
            constraint = DVConstraint.createFormulaListConstraint(hiddenSheet); 
            addressList = new CellRangeAddressList(startRow, endRow, cellNum, cellNum); 
            validation = new HSSFDataValidation(addressList, constraint); 
            book.setSheetHidden(1, true); // 1隐藏、0显示
            sheet.addValidationData(validation);

Please pay attention to these two places above: