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.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Datavalidation-in-Excel-using-POI-tp5632742p5634760.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
