Bugzilla 53025: Updatad documentation and example on data validations

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1340042 13f79535-47bb-0310-9956-ffa450edef68

1 files changed, 228 insertions, 0 deletions

diff --git a/src/examples/src/org/apache/poi/ss/examples/LinkedDropDownLists.java b/src/examples/src/org/apache/poi/ss/examples/LinkedDropDownLists.java
new file mode 100755
index 0000000000..b0efc4f0ce
--- /dev/null
+++ b/src/examples/src/org/apache/poi/ss/examples/LinkedDropDownLists.java
@@ -0,0 +1,228 @@
+   /* ====================================================================

+   Licensed to the Apache Software Foundation (ASF) under one or more

+   contributor license agreements.  See the NOTICE file distributed with

+   this work for additional information regarding copyright ownership.

+   The ASF licenses this file to You under the Apache License, Version 2.0

+   (the "License"); you may not use this file except in compliance with

+   the License.  You may obtain a copy of the License at

+

+          http://www.apache.org/licenses/LICENSE-2.0

+

+   Unless required by applicable law or agreed to in writing, software

+   distributed under the License is distributed on an "AS IS" BASIS,

+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+   See the License for the specific language governing permissions and

+   limitations under the License.

+   ==================================================================== */

+

+package org.apache.poi.ss.examples;

+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). In this example, the value the user selects

+ * from the down list in cell A1 will affect the values displayed in the linked

+ * drop down list in cell B1. For the sake of simplicity, the data for the drop

+ * down lists is included on the same worksheet but this does not have to be the

+ * case; the data could appear on a separate sheet. If this were done, then the

+ * names for the regions would have to be different, they would have to include

+ * the name of the sheet.

+ * 

+ * 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 builds 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 [msb at apache.org]

+ * @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);

+            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");

+    }

+}