apache poi xssf下拉菜单没有引用正确的单元格

Question

我有一个奇怪的问题，引用正确的单元格集显示为下拉菜单。我的具体场景是一个可见的工作表，其中应该包含许多不同的下拉列表，以及一个隐藏的工作表，其中包含存放下拉列表选择的各种单元格。

因此，例如，visibleSheet第1行（单元格A1)使用hiddenSheet A1:x1(x是变量）中的值，visibleSheet第2行(A2)使用hiddenSheet A2:x2中的值，等等。

几个小时以来，我一直在用头撞墙，试图找出代码中的错误。希望比我更了解Excel/POI的人能帮上忙（我当然不是专家）....

编辑：这里有一个更好的代码片段，说明了我所看到的--它可以在没有任何依赖关系的情况下粘贴（当然除了poi)，感谢任何人花时间！：）

public void createDropdownXfs2() {

    String[][] testValues = {
        {"010", "020", "030", "040", "050", "060", "070", "080", "090"},
        {"110", "120", "130", "140", "150", "160", "170", "180", "190"},
        {"210", "220", "230", "240", "250", "260", "270", "280", "290"},
        {"310", "320", "330", "340", "350", "360", "370", "380", "390"},
        {"410", "420", "430", "440", "450", "460", "470", "480", "490"},
        {"510", "520", "530", "540", "550", "560", "570", "580", "590"},
        {"610", "620", "630", "640", "650", "660", "670", "680", "690"},
        {"710", "720", "730", "740", "750", "760", "770", "780", "790"},
        {"810", "820", "830", "840", "850", "860", "870", "880", "890"},
        {"910", "920", "930", "940", "950", "960", "970", "980", "990"}
    };

    try {
        Workbook workbook = new XSSFWorkbook();
        XSSFSheet sheet1 = (XSSFSheet) workbook.createSheet("mac_import");
        XSSFSheet hiddenSheet = (XSSFSheet) workbook.createSheet("hidden");

        //
        // our data validation objects
        //
        XSSFDataValidationHelper validationHelper;
        XSSFDataValidationConstraint constraint;
        CellRangeAddressList addressList;
        XSSFDataValidation dataValidation;

        //
        // create our ROW variables
        //
        Row row;
        Row currentHiddenRow;

        //
        // the range template string should end up 
        // looking something like "hidden!A1:D1"
        //
        String rangeTemplate = hiddenSheet.getSheetName() + "!" + "A%s" + ":" + "%s%s";

        for (int i = 0; i < testValues.length; i++) {
            //
            // create a new row in the visible sheet and in the hidden sheet
            //
            row = sheet1.createRow(i);
            currentHiddenRow = hiddenSheet.createRow(i);

            System.out.println("Create New Rows at 0-based-position: " + i);


            String[] listItems = testValues[i];

            //
            // create the cells with our list values on the hidden sheet
            //
            for (int j = 0; j < listItems.length; j++) {
                Cell cell = currentHiddenRow.createCell(j, Cell.CELL_TYPE_STRING);
                cell.setCellValue(listItems[j]);
            }

            //
            // create the namedcell/formula reference string
            //
            String formulaName = "formula" + i;

            //
            // prepare the excel-friendly range from the template
            //              
            String endColumnName = CellReference.convertNumToColString(listItems.length - 1);
            String rangeRow = String.valueOf(i + 1);
            String rangeString = String.format(rangeTemplate, rangeRow, endColumnName, rangeRow);

            //
            // create a named area on the hidden sheet that covers the area we just created
            //          
            Name namedCell = workbook.createName();
            namedCell.setNameName(formulaName);
            namedCell.setRefersToFormula(rangeString);

            System.out.println(String.format("Cell Range '%s' refers to Range String: '%s'",
                    namedCell.getNameName(), rangeString));


            validationHelper = new XSSFDataValidationHelper(sheet1);
            constraint = (XSSFDataValidationConstraint) validationHelper.createFormulaListConstraint(formulaName);
            addressList = new CellRangeAddressList(i, i, 0, 0);
            dataValidation = (XSSFDataValidation) validationHelper.createValidation(constraint, addressList);
            dataValidation.setSuppressDropDownArrow(true);
            sheet1.addValidationData(dataValidation);

        }

        FileOutputStream fileOut = new FileOutputStream("/eelboy/mac/test_xls/list.xlsx");
        workbook.write(fileOut);
        fileOut.close();


    } catch (Exception ex) {
        System.out.println(ex);
    }
}

Answer 1

发现了问题...namedCell需要在行/列文字前面加一个“$”。因此，在我的例子中，rangeTemplate定义应该如下所示：

String rangeTemplate = hiddenSheet.getSheetName() + "!" + "$A$%s" + ":" + "$%s$%s"