apache poi xssf dropdowns not referencing the right cells

Question

I am having a strange problem referencing the right set of cells to display as dropdowns. My specific scenario is a visible sheet which should contain many various dropdowns and a hidden sheet which contains the various cells that house the dropdown selections.

So, for example, visibleSheet row 1 (cell A1) uses the values in hiddenSheet A1:x1 (x is variable), and visibleSheet row 2 (A2) uses the values in hiddenSheet A2:x2, etc...

But when I open the workbook in Excel (or Office Calc) only the first half (literally) of the dropdowns on the visibleSheet contain any selections at all. I can see that the hiddenSheet has correctly created all the cells. The visibleSheet, however, appears to be skipping every other set of values for the dropdowns....

visibleSheet row 1 (cell A1) displays the values from the hiddenSheet row 1 (correctly), visibleSheet row 2 (cell A2) is displaying the values from hiddenSheet row 3 (NOT 2!), visibleSheet row 3 (cell A3) is displaying hiddenSheet row 5 (not 3), etc..

I have been banging my head against the wall for hours trying to figure out what is wrong in my code. Hopefully someone who knows more about Excel/POI than me can help (I'm certainly no expert)....

EDIT: Here is a better code snippet that illustrates what I'm seeing - it can be pasted without any dependencies (except poi of course) Thanks for anyone taking the time! :)

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);
    }
}

user3241461 user3241461 · Accepted Answer · 2014-07-25T19:22:19

Found the problem... the namedCell needs to have the row/column literals preceded with a '$'. So in my case the rangeTemplate definition should look like this:

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

apache poi xssf dropdowns not referencing the right cells

1 Answers