In our actual program development, we often need to import data system from excel. In order to prevent users from entering text randomly in excel, some places need to use drop-down selection, we need to generate templates dynamically from the program. This example briefly explains how to generate cascade drop-down selections.

Effect drawing: (Choose the car as the drop-down selection, and the value of the fruit drop-down selection changes dynamically)

The implementation steps of cascade drop-down selection are as follows:

1. In another sheet page in excel, the drop-down value is saved.

2. Create a name for the data in the drop-down (similar to operating the naming manager in excel)

3. Use the INDIRECT() function to get the cascade drop-down value.

4. Description of creating cascade drop-down selection on POI official website

In this example, a simple way to generate dynamic excel templates is presented.

Note: Each template field is an ExportDefinition object. If it is a drop-down type, the mainDict field has a value. According to its value, the drop-down value is loaded from the DictData. If a cascade operation exists to select it, the subDict and subField fields have values. SubDict also loads data from DictData, and subField represents cascaded fields

Ideas:

1. According to a set of Export Definition defining objects, the export template of excel is generated. At this time, only one line to the output data is exported from the template.

2. Create a data dictionary page, which stores the data needed for dropdown selection.

3. If the main drop-down is selected, a name management is created for the main drop-down.

4. If the main drop-down selection is associated with the cascade drop-down selection, then the data of the sub-drop-down selection needs to be loaded at this time, and a name management is created according to each item selected by the main drop-down selection, and the value is the value of the associated sub-drop-down selection.

5. Use INDIRECT function, set the validity of data, etc.

Steps:

Download Click

1. Create drop-down values

1. public class DictData {  
2.     public static Map<String, Object> dict = null;  
3.     static {  
4.         dict = new HashMap<>();  
5.         List<String> list = new ArrayList<>();  
6.         list.add("automobile");  
7.         list.add("Fruits");  
8.         dict.put("car-dict", list);  
9.         Map<String, List<String>> subMap = new HashMap<>();  
10.         list = new ArrayList<>();  
11.         list.add("BMW");  
12.         list.add("Public");  
13.         subMap.put("automobile", list);  
14.         list = new ArrayList<>();  
15.         list.add("Apple");  
16.         list.add("Pear");  
17.         subMap.put("Fruits", list);  
18.         dict.put("fruit-dict", subMap);  
19.         list = new ArrayList<>();  
20.         list.add("automobile-1");  
21.         list.add("Fruits-1");  
22.         dict.put("t-dict", list);  
23.     }  
24.   
25.     /** Get the values in the data dictionary*/  
26.     public static Object getDict(String dict) {  
27.         return DictData.dict.get(dict);  
28.     }  
29. }  

2. Create a RowCellIndex object for maintenance, currently creating the row and column of the drop-down selection

1. public class RowCellIndex {  
2.     /** Row index of cells*/  
3.     private int rowIndex;  
4.     /** Column index of cells*/  
5.     private int cellIndex;  
6.     public RowCellIndex(int rowIndex, int cellIndex) {  
7.         this.rowIndex = rowIndex;  
8.         this.cellIndex = cellIndex;  
9.     }  
10.     public int getRowIndex() {  
11.         return rowIndex;  
12.     }  
13.     public void setRowIndex(int rowIndex) {  
14.         this.rowIndex = rowIndex;  
15.     }  
16.     public int getCellIndex() {  
17.         return cellIndex;  
18.     }  
19.     public void setCellIndex(int cellIndex) {  
20.         this.cellIndex = cellIndex;  
21.     }  
22.     public int incrementRowIndexAndGet() {  
23.         this.rowIndex++;  
24.         return this.getRowIndex();  
25.     }  
26.     public int incrementCellIndexAndGet() {  
27.         this.cellIndex++;  
28.         return this.getCellIndex();  
29.     }  
30. }  

3. Create an export definition object for an excel column, omitting some getter and setter methods

1. public class ExportDefinition {  
2.     private String title; //Title  
3.     private String field; //Field  
4.     private int rowIndex; //The row in which it is located  
5.     private int cellIndex; //The column in which it is located  
6.     private String mainDict; //Master Dictionary - Data for Loading Master Dictionary  
7.     private String subDict; //Subdictionary - Data used to load subField  
8.     private String subField; //That is, a cascaded dictionary is required  
9.     private String refName; //Location of the main field  
10.     private String point; //Coordinates of headings  
11.     private boolean validate;//Whether to set the limitation of data  
12.     public ExportDefinition(String title, String field, String mainDict, String subDict, String subField) {  
13.         this.title = title;  
14.         this.field = field;  
15.         this.mainDict = mainDict;  
16.         this.subDict = subDict;  
17.         this.subField = subField;  
18.     }  

4. Implementing export

Download Click

1. package com.huan.excel.ex2;  
2.   
3. import java.io.FileOutputStream;  
4. import java.io.IOException;  
5. import java.io.OutputStream;  
6. import java.util.ArrayList;  
7. import java.util.List;  
8. import java.util.Map;  
9. import java.util.Map.Entry;  
10. import java.util.Objects;  
11.   
12. import org.apache.poi.hssf.usermodel.DVConstraint;  
13. import org.apache.poi.hssf.usermodel.HSSFDataValidation;  
14. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
15. import org.apache.poi.hssf.util.CellReference;  
16. import org.apache.poi.ss.usermodel.DataValidation;  
17. import org.apache.poi.ss.usermodel.Name;  
18. import org.apache.poi.ss.usermodel.Row;  
19. import org.apache.poi.ss.usermodel.Sheet;  
20. import org.apache.poi.ss.usermodel.Workbook;  
21. import org.apache.poi.ss.util.CellRangeAddressList;  
22.   
23. /** 
24.  * Generate cascade drop-down selection 
25.  *  
26.  * @describe 
27.  * @Author Huang 
28.  * @Time: 31 March 2017 - 9:14:43 p.m. 
29.  */  
30. public class DropDownListTest {  
31.   
32.     private static final String DICT_SHEET = "DICT_SHEET";  
33.   
34.     public static void main(String[] args) throws IOException {  
35.         //1. Preparing the data needed to generate excel templates  
36.         List<ExportDefinition> edList = new ArrayList<>(2);  
37.         edList.add(new ExportDefinition("Articles for daily use", "xx", null, null, null));  
38.         edList.add(new ExportDefinition("automobile", "cat", "car-dict", "fruit-dict", "fruit"));  
39.         edList.add(new ExportDefinition("Fruits", "fruit", "fruit-dict", "", ""));  
40.         edList.add(new ExportDefinition("test", "yy", "t-dict", null, null));  
41.   
42.         //2. Generating export templates  
43.         Workbook wb = new HSSFWorkbook();  
44.         Sheet sheet = createExportSheet(edList, wb);  
45.   
46.         //3. Create sheet pages of data dictionary  
47.         createDictSheet(edList, wb);  
48.   
49.         //4. Setting up data validity  
50.         setDataValidation(edList, sheet);  
51.   
52.         //5. Save excel locally  
53.         OutputStream os = new FileOutputStream("d:/4.xls");  
54.         wb.write(os);  
55.   
56.         System.out.println("Successful template generation.");  
57.     }  
58.   
59.     /** 
60.      * @param edList 
61.      * @param sheet 
62.      */  
63.     private static void setDataValidation(List<ExportDefinition> edList, Sheet sheet) {  
64.         for (ExportDefinition ed : edList) {  
65.             if (ed.isValidate()) {//Explanation is drop-down  
66.                 DVConstraint constraint = DVConstraint.createFormulaListConstraint(ed.getField());  
67.                 if (null == ed.getRefName()) {//Explanation is a first-level drop-down  
68.                     createDataValidate(sheet, ed, constraint);  
69.                 } else {//Explanation is second-level drop-down  
70.                     constraint = DVConstraint.createFormulaListConstraint("INDIRECT(" + ed.getRefName() + ")");  
71.                     createDataValidate(sheet, ed, constraint);  
72.                 }  
73.             }  
74.         }  
75.     }  
76.   
77.     /** 
78.      * @param sheet 
79.      * @param ed 
80.      * @param constraint 
81.      */  
82.     private static void createDataValidate(Sheet sheet, ExportDefinition ed, DVConstraint constraint) {  
83.         CellRangeAddressList regions = new CellRangeAddressList(ed.getRowIndex() + 1, ed.getRowIndex() + 100, ed.getCellIndex(), ed.getCellIndex());  
84.         DataValidation dataValidation = new HSSFDataValidation(regions, constraint);  
85.         dataValidation.setSuppressDropDownArrow(false);  
86.         //Setting prompt information  
87.         dataValidation.createPromptBox("Operational tips", "Please select the value selected by dropdown");  
88.         //Setting input error information  
89.         dataValidation.createErrorBox("Error prompt", "Choose from the drop-down and don't type in casually.");  
90.         sheet.addValidationData(dataValidation);  
91.     }  
92.   
93.     /** 
94.      * @param edList 
95.      * @param wb 
96.      */  
97.     private static void createDictSheet(List<ExportDefinition> edList, Workbook wb) {  
98.         Sheet sheet = wb.createSheet(DICT_SHEET);  
99.         RowCellIndex rci = new RowCellIndex(0, 0);  
100.         for (ExportDefinition ed : edList) {  
101.             String mainDict = ed.getMainDict();  
102.             if (null != mainDict && null == ed.getRefName()) {//The first drop-down  
103.                 List<String> mainDictList = (List<String>) DictData.getDict(mainDict);  
104.                 String refersToFormula = createDictAndReturnRefFormula(sheet, rci, mainDictList);  
105.                 //Create naming management  
106.                 createName(wb, ed.getField(), refersToFormula);  
107.                 ed.setValidate(true);  
108.             }  
109.             if (null != mainDict && null != ed.getSubDict() && null != ed.getSubField()) {//Load the data of ed.getSubField() when interacting  
110.                 ExportDefinition subEd = fiterByField(edList, ed.getSubField());//Get the field that needs to be cascaded  
111.                 if (null == subEd) {  
112.                     continue;  
113.                 }  
114.                 subEd.setRefName(ed.getPoint());//Save the main drop-down location  
115.                 subEd.setValidate(true);  
116.                 Map<String, List<String>> subDictListMap = (Map<String, List<String>>) DictData.getDict(ed.getSubDict());  
117.                 for (Entry<String, List<String>> entry : subDictListMap.entrySet()) {  
118.                     String refersToFormula = createDictAndReturnRefFormula(sheet, rci, entry.getValue());  
119.                     //Create naming management  
120.                     createName(wb, entry.getKey(), refersToFormula);  
121.                 }  
122.             }  
123.         }  
124.     }  
125.   
126.     /** 
127.      * @param sheet 
128.      * @param rci 
129.      * @param mainDict 
130.      * @return 
131.      */  
132.     private static String createDictAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas) {  
133.         Row row = sheet.createRow(rci.incrementRowIndexAndGet());  
134.         rci.setCellIndex(0);  
135.         int startRow = rci.getRowIndex();  
136.         int startCell = rci.getCellIndex();  
137.         for (String dict : datas) {  
138.             row.createCell(rci.incrementCellIndexAndGet()).setCellValue(dict);  
139.         }  
140.         int endRow = rci.getRowIndex();  
141.         int endCell = rci.getCellIndex();  
142.         String startName = new CellReference(DICT_SHEET, startRow, startCell, true, true).formatAsString();  
143.         String endName = new CellReference(endRow, endCell, true, true).formatAsString();  
144.         String refersToFormula = startName + ":" + endName;  
145.         System.out.println(refersToFormula);  
146.         return refersToFormula;  
147.     }  
148.   
149.     /** 
150.      * @param wb 
151.      * @param nameName 
152.      *            Name that represents naming management 
153.      * @param refersToFormula 
154.      */  
155.     private static void createName(Workbook wb, String nameName, String refersToFormula) {  
156.         Name name = wb.createName();  
157.         name.setNameName(nameName);  
158.         name.setRefersToFormula(refersToFormula);  
159.     }  
160.   
161.     private static ExportDefinition fiterByField(List<ExportDefinition> edList, String field) {  
162.         for (ExportDefinition ed : edList) {  
163.             if (Objects.equals(ed.getField(), field)) {  
164.                 return ed;  
165.             }  
166.         }  
167.         return null;  
168.     }  
169.   
170.     /** 
171.      * @param edList 
172.      * @param wb 
173.      */  
174.     private static Sheet createExportSheet(List<ExportDefinition> edList, Workbook wb) {  
175.         Sheet sheet = wb.createSheet("Export Template");  
176.         RowCellIndex rci = new RowCellIndex(0, 0);  
177.         Row row = sheet.createRow(rci.getRowIndex());  
178.         CellReference cr = null;  
179.         for (ExportDefinition ed : edList) {  
180.             row.createCell(rci.incrementCellIndexAndGet()).setCellValue(ed.getTitle());  
181.             ed.setRowIndex(rci.getRowIndex());  
182.             ed.setCellIndex(rci.getCellIndex());  
183.             cr = new CellReference(ed.getRowIndex() + 1, ed.getCellIndex(), true, true);  
184.             ed.setPoint(cr.formatAsString());  
185.         }  
186.         return sheet;  
187.     }  
188.   
189. }