Quantcast

Datavalidation in Excel using POI

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Datavalidation in Excel using POI

theholychicken
Hello,
I am looking to generate an excel with dependent validation checks.
For instance, cell A1 would have options Colours and Fruits
Cell A2 will display values based on what was chosed in Cell A1. So if cell A1 is selected as Colours, then Cell A2 will have a list of colours, otherwise fruits.

It would be great if you can guide me further on this.

Thank you!
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Datavalidation in Excel using POI

Mark Beardsley
Its a lot more straightforward than you may expect. I wrote this to be included in the examples section of the site and it does something similar; on the radio here many, many years ago there used to be a panel game where the players had to guess what something was by asking questions and they always started by asking is it animal, vegetable or mineral. Similar to your description, the choice that can be made in A1 is Animal, Vegetable or Mineral; the choices offered in cell B1 change depending on this first selection. If you look at the Data Validations section in the Quick Guide, that will show you how to write the drop down's data onto a separate sheet.

import java.io.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

/**
 * Demonstrates one technique that may be used to create linked or dependent
 * drop down lists. This refers to a situation in which the selection made
 * in one drop down list affects the options that are displayed in the second
 * or subsequent drop down list(s).
 *
 * There are two keys to this technique. The first is the use of named area or
 * regions of cells to hold the data for the drop down lists and the second is
 * making use of the INDIRECT() function to convert a name into the addresses
 * of the cells it refers to.
 *
 * Note that whilst this class create just two linked drop down lists, there is
 * nothing to prevent more being created. Quite simply, use the value selected
 * by the user in one drop down list to determine what is shown in another and the
 * value selected in that drop down list to determine what is shown in a third,
 * and so on. Also, note that the data for the drop down lists is contained on
 * contained on the same sheet as the validations themselves. This is done simply
 * for simplicity and there is nothing to prevent a separate sheet being created
 * and used to hold the data. If this is done then problems may be encountered
 * if the sheet is opened with OpenOffice Calc. To prevent these problems, it is
 * better to include the name of the sheet when calling the setRefersToFormula()
 * method.
 *
 * @author Mark Beardsley
 * @version 1.00 30th March 2012
 */
public class LinkedDropDownLists {

    LinkedDropDownLists(String workbookName) {
        File file = null;
        FileOutputStream fos = null;
        Workbook workbook = null;
        Sheet sheet = null;
        DataValidationHelper dvHelper = null;
        DataValidationConstraint dvConstraint = null;
        DataValidation validation = null;
        CellRangeAddressList addressList = null;
        try {

            // Using the ss.usermodel allows this class to support both binary
            // and xml based workbooks. The choice of which one to create is
            // made by checking the file extension.
            if (workbookName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook();
            } else {
                workbook = new HSSFWorkbook();
            }
           
            // Build the sheet that will hold the data for the validations. This
            // must be done first as it will create names that are referenced
            // later.
            sheet = workbook.createSheet("Linked Validations");
            LinkedDropDownLists.buildDataSheet(sheet);

            // Build the first data validation to occupy cell A1. Note
            // that it retrieves it's data from the named area or region called
            // CHOICES. Further information about this can be found in the
            // static buildDataSheet() method below.
            addressList = new CellRangeAddressList(0, 0, 0, 0);
            dvHelper = sheet.getDataValidationHelper();
            dvConstraint = dvHelper.createFormulaListConstraint("CHOICES");
            validation = dvHelper.createValidation(dvConstraint, addressList);
            sheet.addValidationData(validation);
           
            // Now, build the linked or dependent drop down list that will
            // occupy cell B1. The key to the whole process is the use of the
            // INDIRECT() function. In the buildDataSheet(0 method, a series of
            // named regions are created and the names of three of them mirror
            // the options available to the user in the first drop down list
            // (in cell A1). Using the INDIRECT() function makes it possible
            // to convert the selection the user makes in that first drop down
            // into the addresses of a named region of cells and then to use
            // those cells to populate the second drop down list.
            addressList = new CellRangeAddressList(0, 0, 1, 1);
            //dvHelper = sheet.getDataValidationHelper();
            dvConstraint = dvHelper.createFormulaListConstraint(
                    "INDIRECT(UPPER($A$1))");
            validation = dvHelper.createValidation(dvConstraint, addressList);
            sheet.addValidationData(validation);
           
            file = new File(workbookName);
            fos = new FileOutputStream(file);
            workbook.write(fos);
        } catch (IOException ioEx) {
            System.out.println("Caught a: " + ioEx.getClass().getName());
            System.out.println("Message: " + ioEx.getMessage());
            System.out.println("Stacktrace follws:.....");
            ioEx.printStackTrace(System.out);
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                    fos = null;
                }
            } catch (IOException ioEx) {
                System.out.println("Caught a: " + ioEx.getClass().getName());
                System.out.println("Message: " + ioEx.getMessage());
                System.out.println("Stacktrace follws:.....");
                ioEx.printStackTrace(System.out);
            }
        }
    }

    /**
     * Called to populate the named areas/regions. The contents of the cells on
     * row one will be used to populate the first drop down list. The contents of
     * the cells on rows two, three and four will be used to populate the second
     * drop down list, just which row will be determined by the choice the user
     * makes in the first drop down list.
     *
     * In all cases, the approach is to create a row, create and populate cells
     * with data and then specify a name that identifies those cells. With the
     * exception of the first range, the names that are chosen for each range
     * of cells are quite important. In short, each of the options the user
     * could select in the first drop down list is used as the name for another
     * range of cells. Thus, in this example, the user can select either
     * 'Animal', 'Vegetable' or 'Mineral' in the first drop down and so the
     * sheet contains ranges named 'ANIMAL', 'VEGETABLE' and 'MINERAL'.
     *
     * @param dataSheet An instance of a class that implements the Sheet Sheet
     *        interface (HSSFSheet or XSSFSheet).
     */
    private static final void buildDataSheet(Sheet dataSheet) {
        Row row = null;
        Cell cell = null;
        Name name = null;

        // The first row will hold the data for the first validation.
        row = dataSheet.createRow(10);
        cell = row.createCell(0);
        cell.setCellValue("Animal");
        cell = row.createCell(1);
        cell.setCellValue("Vegetable");
        cell = row.createCell(2);
        cell.setCellValue("Mineral");
        name = dataSheet.getWorkbook().createName();
        name.setRefersToFormula("$A$11:$C$11");
        name.setNameName("CHOICES");

        // The next three rows will hold the data that will be used to
        // populate the second, or linked, drop down list.
        row = dataSheet.createRow(11);
        cell = row.createCell(0);
        cell.setCellValue("Lion");
        cell = row.createCell(1);
        cell.setCellValue("Tiger");
        cell = row.createCell(2);
        cell.setCellValue("Leopard");
        cell = row.createCell(3);
        cell.setCellValue("Elephant");
        cell = row.createCell(4);
        cell.setCellValue("Eagle");
        cell = row.createCell(5);
        cell.setCellValue("Horse");
        cell = row.createCell(6);
        cell.setCellValue("Zebra");
        name = dataSheet.getWorkbook().createName();
        name.setRefersToFormula("$A$12:$G$12");
        name.setNameName("ANIMAL");

        row = dataSheet.createRow(12);
        cell = row.createCell(0);
        cell.setCellValue("Cabbage");
        cell = row.createCell(1);
        cell.setCellValue("Cauliflower");
        cell = row.createCell(2);
        cell.setCellValue("Potato");
        cell = row.createCell(3);
        cell.setCellValue("Onion");
        cell = row.createCell(4);
        cell.setCellValue("Beetroot");
        cell = row.createCell(5);
        cell.setCellValue("Asparagus");
        cell = row.createCell(6);
        cell.setCellValue("Spinach");
        cell = row.createCell(7);
        cell.setCellValue("Chard");
        name = dataSheet.getWorkbook().createName();
        name.setRefersToFormula("$A$13:$H$13");
        name.setNameName("VEGETABLE");

        row = dataSheet.createRow(13);
        cell = row.createCell(0);
        cell.setCellValue("Bauxite");
        cell = row.createCell(1);
        cell.setCellValue("Quartz");
        cell = row.createCell(2);
        cell.setCellValue("Feldspar");
        cell = row.createCell(3);
        cell.setCellValue("Shist");
        cell = row.createCell(4);
        cell.setCellValue("Shale");
        cell = row.createCell(5);
        cell.setCellValue("Mica");
        name = dataSheet.getWorkbook().createName();
        name.setRefersToFormula("$A$14:$F$14");
        name.setNameName("MINERAL");
    }
}

PS There are more complex formulae that you can use which would allow the data sections to be dynamic, i.e. to reflect changes the user made whilst the workbook was open if this is what you require. Just search through the user list, this question has been asked and answered before.
Loading...