CODEWITHSUNDEEP
javaapache-poi
Benoit Goderre
Benoit Goderre

Reputation: 537

POI - Multiple data validation ranges on same sheet

I am creating code that retrieves a list of names from a database and adds them to a Excel file, in drop-down lists for the user to select.

The problem I have is the same excel file contains multiple distinct list of names and I don't know how put them all in the same (hidden) sheet.

Code

public void write()
{
    ArrayList<ArrayList<String>> listsPlayers;

    // Fill the list of players array lists from the database

    // Set the address lists objects

    for (int k = 0; k < Gender.values().length; k++)
    {
        createHiddenSheet(Category.values()[i].text() + "_" + Gender.values()[k].text(),
        listsPlayers.get(k));

        DataValidation validation = validationHelper.createValidation(listConstraints.get(constraintIndexes[k]), addressList[k]);

        sheet.addValidationData(validation);
    }
}

public void createHiddenSheet(String sheetName, ArrayList<String> listPlayerNames)
{
    DataValidationConstraint constraint = null;
    DataValidationHelper validationHelper = null;

    // Creates and hides a sheet
    Sheet hiddenSheet = workbook.createSheet(sheetName);
    workbook.setSheetHidden(workbook.getSheetIndex(sheetName), Workbook.SHEET_STATE_VERY_HIDDEN);
    hiddenSheet.protectSheet("Test");

    Name namedCell = workbook.createName();
    namedCell.setNameName(sheetName);

    // Adds the list of player names in the sheet
    for (int i = 0; i <= listPlayerNames.size(); i++)
    {
        String name = listPlayerNames.get(i);

        Row row = hiddenSheet.createRow(i);
        Cell cell = row.createCell(0);
        cell.setCellValue(name);
    }

    namedCell.setRefersToFormula(sheetName + "!$A$1:$A$" + (listPlayerNames.size() + 1));

    validationHelper = hiddenSheet.getDataValidationHelper();
    constraint = validationHelper
            .createFormulaListConstraint(sheetName + "!$A$1:$A$" + (listPlayerNames.size() + 1));

    listConstraints.add(constraint);
}

My problem is that since there are three categories and two genders, this code will create six hidden sheets to pull the validation from. I wish to create a cleaner solution where only one hidden sheet is created.

When I tried to use six columns in one sheet (instead of six sheets with one column), I ran into multiple problems with the named cell object.

Thanks in advance!

Upvotes: 1

Views: 1696

Answers (1)

Benoit Goderre
Benoit Goderre

Reputation: 537

After AxelRichter's comment, I realized that my code did not need the named range object at all.

Here is the modified working code:

public void populateHiddenSheet(int categoryNumber, int genderNumber, ArrayList<Player> players,
        ArrayList<Player> overankedPlayers)
{
    DataValidationConstraint constraint = null;
    DataValidationHelper validationHelper = null;
    String name = new String();

    Sheet hiddenSheet = workbook.getSheet(HIDDEN_SHEET_NAME);

    // Creates the hidden sheet if it does not exist
    if (hiddenSheet == null)
    {
        hiddenSheet = workbook.createSheet(HIDDEN_SHEET_NAME);
        workbook.setSheetHidden(workbook.getSheetIndex(HIDDEN_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN);

    }

    // Adds the list of player names in the sheet
    for (int i = 0; i < players.size(); i++)
    {
        name = players.get(i).getName();

        POIExcelFileProcessor.createCell(hiddenSheet, columnNumber, i, NAME_PREFIX + name);
    }

    // Sets the validation for the drop-down lists
    validationHelper = hiddenSheet.getDataValidationHelper();
    constraint = validationHelper.createFormulaListConstraint(HIDDEN_SHEET_NAME + "!$" + (char) (columnNumber + 65)
            + "$1:$" + (char) (columnNumber + 65) + "$" + (players.size() + overankedPlayers.size() + 1));

    listConstraints.add(constraint);
}

Upvotes: 1

Related Questions