Invalid column index (256). Allowable column range for BIFF8 is (0..255) or (‘A‘..‘IV‘)异常，处理方法记录

最新推荐文章于 2024-04-03 14:43:33 发布

yanyundi

最新推荐文章于 2024-04-03 14:43:33 发布

阅读量7.6k

点赞数

分类专栏：日常异常处理文章标签： excel poi java

本文链接：https://blog.csdn.net/yanyundi/article/details/110521826

版权

日常异常处理专栏收录该内容

3 篇文章 0 订阅

订阅专栏

问题出现原因

HSSFWorkbook对于单元格提供下拉选项的个数限制了255长度问题，这个时候可以使用XSSFWorkbook并把候选项以列显示可以解决了
在这里插入图片描述

解决代码

//导出execel
 public void exportComboxExcel(OutputStream fos) {
        try {
            workbook = new XSSFWorkbook();//excel文件对象
            XSSFSheet sheet1 = workbook.createSheet("sheet1");//工作表对象
            //设置列头样式  
            this.setTitleCellStyles(workbook, sheet1);
            //设置数据样式  
            this.setDataCellStyles(workbook, sheet1);
            //创建一个隐藏页和隐藏数据集  
            List<Map<String, Object>> list = createHideSheet();
            //创建一行列头数据  
            this.creatAppRowHead(sheet1, 1);

            //创建一行数据  
            for (int i = 2; i < 3; i++) {
                //this.creatAppRow(sheet1, i, i);
                this.creatAppRow(sheet1, i, i, 0,list);
            }
            workbook.write(fos);
            fos.flush();
            fos.close();

        } catch (FileNotFoundException e) {
            log.error(e.getMessage(), e);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }

 //execel头信息
  public void creatAppRowHead(XSSFSheet userinfosheet1, int naturalRowIndex) {
        XSSFRow row = userinfosheet1.createRow(naturalRowIndex - 1);

        //0.字段名
        XSSFCell usernameCell = row.createCell(0);
        usernameCell.setCellValue("字段名");
        usernameCell.setCellStyle(titleStyle);

        //1.字段名
        XSSFCell loginidCell = row.createCell(1);
        loginidCell.setCellValue("字段名");
        loginidCell.setCellStyle(titleStyle);

        //2.字段名 
        XSSFCell titleTimeCell = row.createCell(2);
        titleTimeCell.setCellValue("字段名");
        titleTimeCell.setCellStyle(titleStyle);

        //3.字段名
        XSSFCell mobilCell = row.createCell(3);
        mobilCell.setCellValue("字段名");
        mobilCell.setCellStyle(titleStyle);

        //4.字段名
        XSSFCell emailCell = row.createCell(4);
        emailCell.setCellValue("字段名");
        emailCell.setCellStyle(titleStyle);

        //5.字段名
        XSSFCell orgCell = row.createCell(5);
        orgCell.setCellValue("字段名");
        orgCell.setCellStyle(titleStyle);

        //6.字段名
        XSSFCell roleCell = row.createCell(6);
        roleCell.setCellValue("字段名");
        roleCell.setCellStyle(titleStyle);

        //7.字段名
        XSSFCell areaCell = row.createCell(7);
        areaCell.setCellValue("字段名");
        areaCell.setCellStyle(titleStyle);

        //8.字段名
        XSSFCell vipCell = row.createCell(8);
        vipCell.setCellValue("字段名");
        vipCell.setCellStyle(titleStyle);

        //9 字段名
        XSSFCell wxCell = row.createCell(9);
        wxCell.setCellValue("字段名");
        wxCell.setCellStyle(titleStyle);
        //9 字段名
        XSSFCell qqCell = row.createCell(10);
        qqCell.setCellValue("字段名");
        qqCell.setCellStyle(titleStyle);
        //9 字段名
        XSSFCell idCardCell = row.createCell(11);
        idCardCell.setCellValue("字段名");
        idCardCell.setCellStyle(titleStyle);

        //10.字段名
        XSSFCell personTypeCell = row.createCell(12);
        personTypeCell.setCellValue("字段名");
        personTypeCell.setCellStyle(titleStyle);
    }

/**
     * 创建一列应用数据
     *
     * @param userinfosheet1
     * @param userName
     */
    public void creatAppRow(XSSFSheet userinfosheet1, int num, int naturalRowIndex, int cellIndex, List<Map<String, Object>> selectIndexList) {
        //构造一个信息输入表单，用户姓名，出生省份，出生城市
        //要求省份是可以下拉选择的，出生城市根据所选择的省份级联下拉选择
        //在第一行第一个单元格，插入下拉框
        XSSFRow row = userinfosheet1.createRow(naturalRowIndex - 1);

        //0.字段名
        XSSFCell serialNumberCell = row.createCell(0);
        serialNumberCell.setCellValue("张三");
        serialNumberCell.setCellStyle(dataStyle);

        //1.字段名
        XSSFCell loginidCell = row.createCell(1);
        loginidCell.setCellValue("zhangs");
        loginidCell.setCellStyle(dataStyle);

        //2.字段名
        XSSFCell titelCell = row.createCell(2);
        titelCell.setCellValue("zhangs");
        titelCell.setCellStyle(dataStyle);

        //3.字段名
        XSSFCell mobilCell = row.createCell(3);
        mobilCell.setCellValue("15888888888");
        mobilCell.setCellStyle(dataStyle);

        //4.字段名
        XSSFCell emailCell = row.createCell(4);
        emailCell.setCellValue("zhangs123@163.com");
        emailCell.setCellStyle(dataStyle);

        //5.字段名
        XSSFCell orgCell = row.createCell(5);
        orgCell.setCellValue("");
        orgCell.setCellStyle(dataStyle);

        //6.字段名
        XSSFCell roleCell = row.createCell(6);
        roleCell.setCellValue("");
        roleCell.setCellStyle(dataStyle);

        //7.字段名
        XSSFCell areaCell = row.createCell(7);
        areaCell.setCellValue("");
        areaCell.setCellStyle(dataStyle);

        //7.字段名
        XSSFCell vipCell = row.createCell(8);
        vipCell.setCellValue("");
        vipCell.setCellStyle(dataStyle);

        //8.字段名
        XSSFCell weChatCell = row.createCell(9);
        weChatCell.setCellValue("");
        weChatCell.setCellStyle(dataStyle);
        //9.字段名
        XSSFCell qqNumCell = row.createCell(10);
        qqNumCell.setCellValue("");
        qqNumCell.setCellStyle(dataStyle);
        //10.字段名
        XSSFCell idCardCell = row.createCell(11);
        idCardCell.setCellValue("");
        idCardCell.setCellStyle(dataStyle);

        //10.字段名
        XSSFCell personTypeCell = row.createCell(12);
        personTypeCell.setCellValue("");
        personTypeCell.setCellStyle(dataStyle);

        try {
            if(selectIndexList != null && selectIndexList.size() > 0){
                //绑定下拉数据
                for(Map<String,Object> map : selectIndexList){
                    //得到验证对象, 工作表添加验证数据
                    //从1开始下拉框处于第几列);
                    addDataValidation (userinfosheet1, map, naturalRowIndex);
                   
                }
            }
        }catch (Exception e){
            log.error("绑定数据有误");
            log.error(e.getMessage(), e);
        }

    }

在这里插入图片描述

 /***
     *创建隐藏sheet,存储下拉候选值
     */
     
    public List<Map<String,Object>> createHideSheet(){
        XSSFSheet orgdataSheet = workbook.createSheet("orgdata");
        //设置隐藏页标志
        workbook.setSheetHidden(workbook.getSheetIndex(orgdataSheet), true);
         List<Map<String, Object>> list = new ArrayList<>();
     	 Map<String, Object> map = new HashMap<>();
            map.put("key", "orgdata");
            map.put("index", 6);
            /*!$A$1:$A$127, 这个是excel纵向列的单元格排序规则，
			 根据自身的需要，自行遍历更新拼接
             */
            map.put("record","!$A$1:$A$127");
            map.put("obj", orgdataSheet);
            list.add(map);
        return list;

    }
        private String doHandle(final int num) {
        String[] charArr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M"
                , "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
        return charArr[num - 1].toString();
    }

//把下拉候选项与导出表格中的单元格进行绑定
 public void addDataValidation(XSSFSheet sheet, Map<String, Object> map, int rowIndex) {
        String key = MapUtils.getString(map, "key");
        int naturalRowIndex =  MapUtils.getIntValue(map, "index");
        String record = MapUtils.getString(map, "record");
        XSSFSheet hideSheet = (XSSFSheet) MapUtils.getObject(map, "obj");
        String strFormula = hideSheet.getSheetName()+record;
        XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
        //设置数据有效性加载在哪个单元格上。
        //四个参数分别是：起始行、终止行、起始列、终止列
        int firstRow = rowIndex - 1;
        int lastRow = rowIndex - 1;
        int firstCol = naturalRowIndex - 1;
        int lastCol = naturalRowIndex - 1;
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 数据有效性对象
        DataValidationHelper help = new XSSFDataValidationHelper(sheet);
        DataValidation validation = help.createValidation(constraint, regions);
        sheet.addValidationData(validation);

    }