Java POI 设置Excel级联菜单demo

参考博客：https://blog.csdn.net/readiay/article/details/52397419

Apache的相关类的文档说明：http://poi.apache.org/apidocs/dev/org/apache/poi/hssf/usermodel/package-summary.html

此博客以省市区为例，展示了下拉列表，有效性验证，级联操作的示例，很详细。

基本想法：

1、对所有的数据按照一定的规则放在一张辅助表单中

2、设置下拉列表，列表内容通过Excel函数操作辅助表单得到

代码示例以HSSF为例，针对07版以前的Excel进行操作。设置Excel第2行到第10行为下拉列表选项，市和区的选择都依据前一列的选择展示相应的选项。

    public static void testCasecade2003()
    {
        List<String> provNameList = new ArrayList<String>();
        provNameList.add("安徽省");
        provNameList.add("浙江省");

        Map<String, List<String>> siteMap = new HashMap<String, List<String>>();
        List<String> list = new ArrayList<String>();
        list.add("杭州市");
        list.add("宁波市");
        siteMap.put("浙江省", list);
        List<String> list1 = new ArrayList<String>();
        list1.add("芜湖市");
        list1.add("滁州市");
        siteMap.put("安徽省", list1);
        List<String> list2 = new ArrayList<String>();
        list2.add("戈江区");
        list2.add("三山区");
        siteMap.put("芜湖市", list2);
        List<String> list3 = new ArrayList<String>();
        list3.add("来安县");
        list3.add("凤阳县");
        siteMap.put("滁州市", list3);
        List<String> list4 = new ArrayList<String>();
        list4.add("上城区");
        list4.add("下城区");
        siteMap.put("杭州市", list4);
        List<String> list5 = new ArrayList<String>();
        list5.add("余姚区");
        list5.add("慈溪区");
        siteMap.put("宁波市", list5);

        //生成工作簿和存放对应数据的辅助表单
        Workbook book = new HSSFWorkbook();
        Sheet hideSheet = book.createSheet("site_sheet");
        

        int rowId = 0;
        Row proviRow = hideSheet.createRow(rowId++);
        proviRow.createCell(0).setCellValue("省列表");
        //在第一行添加省信息
        for (int i = 0; i < provNameList.size(); ++i)
        {
            Cell proviCell = proviRow.createCell(i+1);
            proviCell.setCellValue(provNameList.get(i));
        }

        Iterator<String> keyIterator = siteMap.keySet().iterator();
        //接下来每行都是第一个是父区域，后面跟着父区域的下一级子区域
        while (keyIterator.hasNext())
        {
            String key = keyIterator.next();
            List<String> son = siteMap.get(key);

            Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int i = 0; i < son.size(); ++i)
            {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(son.get(i));
            }

            //每一行都添加名称管理器
            String range = getRange(1, rowId, son.size());
            Name name = book.createName();
            name.setNameName(key);
            String formula = "site_sheet!" + range;
            name.setRefersToFormula(formula);
        }

        //生成和用户交互的表单
        Sheet sheet1 = book.createSheet("sheet1");
        Row row0 = sheet1.createRow(0);
        row0.createCell(0).setCellValue("省");
        row0.createCell(1).setCellValue("市");
        row0.createCell(2).setCellValue("区");

        //添加省的规则
        DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provNameList.toArray(new String[]{}));
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1,10,0,0);
        DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
        provinceDataValidation.createErrorBox("error", "请选择正确省份");
        sheet1.addValidationData(provinceDataValidation);

        //设置每一个单元格都以前一个单元格的选择作为标准
        for (int i = 2; i <= 12; ++i)
        {
            //市规则
            DVConstraint formula = DVConstraint.createFormulaListConstraint("INDIRECT($A$" + i + ")");
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1, i-1,1,1);
            DataValidation cacse = new HSSFDataValidation(rangeAddressList, formula);
            cacse.createErrorBox("error", "请选择正确的城市");
            sheet1.addValidationData(cacse);

            //区规则
            formula = DVConstraint.createFormulaListConstraint("INDIRECT($B$" + i + ")");
            rangeAddressList = new CellRangeAddressList(i-1, i-1,2,2);
            cacse = new HSSFDataValidation(rangeAddressList, formula);
            cacse.createErrorBox("error", "请选择正确的区");
            sheet1.addValidationData(cacse);
        }


        try
        {
            OutputStream os = new FileOutputStream("D:\\doudou.xls");
            ((HSSFWorkbook) book).write(os);
            os.close();
            book.close();
        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }

  
   public static String getRange(int offset, int rowId, int colCount)
    {
        char start = (char)('A' + offset);
        if (colCount < 25)
        {
            char end = (char)(start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        }
        else
        {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount-25) / 26 == 0 || colCount == 51)
            {
                if ((colCount - 25) / 26 == 0)
                {
                    endSuffix = (char)('A' + 25);
                }
                else
                {
                    endSuffix = (char)('A' + (colCount - 25) % 26 - 1);
                }
            }
            else
            {
                if ((colCount - 25) % 26 == 0)
                {
                    endSuffix = (char)('A' + 25);
                    endPrefix = (char)(endPrefix + (colCount - 25) / 26 - 1);
                }
                else
                {
                    endSuffix = (char)('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char)(endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }

需要注意的一点：辅助表单作为工作簿的第一个表单时，如果隐藏了辅助表单，会出现展示的表单的下拉列表不工作，所以创建表单时，先创建展示的表单，再创建辅助表单，这样，隐藏辅助表单，展示表单也能正常工作。