poi实现生成下拉选联动

在我们实际的程序开发中，经常需要用到从excel导入数据中系统中，而为了防止用户在excel中乱输入文字，有些需要用到下拉选的地方，就需要从程序中动态生成模板。本例子简单的讲解一下，如何生成级联下拉选。

 

效果图：（选择汽车这个下拉选、后面水果下拉选的值动态改变）

 

级联下拉选的实现步骤：

1.在excel中的另外一个sheet页中，保存着下拉选的值

2.给下拉中的数据创建一个名字( 类似于在excel中操作命名管理器 )

3.使用INDIRECT()函数获取到级联下拉选的值。

4.poi官网上创建级联下拉选的说明

  本例子中，动态excel模板的生成的一个简单思路。

注意：每一个模板字段，都是一个ExportDefinition对象，如果是下拉选的类型，则mainDict字段有值，根据它的值从DictData中加载下拉选的值，如果选择它存在一个级联操作，那么subDict和subField字段有值。subDict也是从DictData中加载数据，subField表示级联的字段

思路：

1.根据一组ExportDefinition定义对象，生成excel的导出模板，此时导出的模板中只有一行到出头数据。

2.创建数据字典页，这个里面保存下拉选需要用到的数据。

3.如果是主下拉选，则给主下拉选创建一个名称管理。

4.如果是主下拉选，并且关联了级联下拉选，则此时需要加载子下拉选的数据，且根据主下拉选中的每一项创建一个名称管理，值为关联的子下拉选的值

5.使用INDIRECT函数、设置数据的有效性等。

 

步骤：

1.创建下拉选的值

Java代码  

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("汽车");  
7.         list.add("水果");  
8.         dict.put("car-dict", list);  
9.         Map<String, List<String>> subMap = new HashMap<>();  
10.         list = new ArrayList<>();  
11.         list.add("宝马");  
12.         list.add("大众");  
13.         subMap.put("汽车", list);  
14.         list = new ArrayList<>();  
15.         list.add("苹果");  
16.         list.add("梨子");  
17.         subMap.put("水果", list);  
18.         dict.put("fruit-dict", subMap);  
19.         list = new ArrayList<>();  
20.         list.add("汽车-1");  
21.         list.add("水果-1");  
22.         dict.put("t-dict", list);  
23.     }  
24.   
25.     /** 获取数据字典中的值 */  
26.     public static Object getDict(String dict) {  
27.         return DictData.dict.get(dict);  
28.     }  
29. }  

 2.创建一个RowCellIndex对象，用于维护，当前创建了下拉选的第几行第几列

Java代码  

1. public class RowCellIndex {  
2.     /** 单元格的行索引 */  
3.     private int rowIndex;  
4.     /** 单元格的列索引 */  
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.创建一个excel列的导出定义对象，省略部分getter和setter方法

Java代码  

1. public class ExportDefinition {  
2.     private String title; // 标题  
3.     private String field; // 字段  
4.     private int rowIndex; // 所在的行  
5.     private int cellIndex; // 所在的列  
6.     private String mainDict; // 主字典-用于加载主字典的数据  
7.     private String subDict; // 子字典-用于加载subField的数据  
8.     private String subField; // 即需要级联的字典  
9.     private String refName; // 主字段所在的位置  
10.     private String point; // 标题的坐标  
11.     private boolean validate;// 是否设置数据的有限性  
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.实现导出

Java代码  

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.  * 生成级联下拉选 
25.  *  
26.  * @描述 
27.  * @作者 huan 
28.  * @时间 2017年3月31日 - 下午9:14:43 
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.准备需要生成excel模板的数据  
36.         List<ExportDefinition> edList = new ArrayList<>(2);  
37.         edList.add(new ExportDefinition("生活用品", "xx", null, null, null));  
38.         edList.add(new ExportDefinition("汽车", "cat", "car-dict", "fruit-dict", "fruit"));  
39.         edList.add(new ExportDefinition("水果", "fruit", "fruit-dict", "", ""));  
40.         edList.add(new ExportDefinition("测试", "yy", "t-dict", null, null));  
41.   
42.         // 2.生成导出模板  
43.         Workbook wb = new HSSFWorkbook();  
44.         Sheet sheet = createExportSheet(edList, wb);  
45.   
46.         // 3.创建数据字典sheet页  
47.         createDictSheet(edList, wb);  
48.   
49.         // 4.设置数据有效性  
50.         setDataValidation(edList, sheet);  
51.   
52.         // 5.保存excel到本地  
53.         OutputStream os = new FileOutputStream("d:/4.xls");  
54.         wb.write(os);  
55.   
56.         System.out.println("模板生成成功.");  
57.     }  
58.    public static void createDataValidateSubList(Sheet sheet, ExportDefinition ed) {  
59.         int rowIndex = ed.getRowIndex();  
60.         CellRangeAddressList cal;  
61.         DVConstraint constraint;  
62.         CellReference cr;  
63.         DataValidation dataValidation;  
64.         System.out.println(ed);  
65.         for (int i = 0; i < 100; i++) {  
66.             int tempRowIndex = ++rowIndex;  
67.             cal = new CellRangeAddressList(tempRowIndex, tempRowIndex, ed.getCellIndex(), ed.getCellIndex());  
68.             cr = new CellReference(rowIndex, ed.getCellIndex() - 1, true, true);  
69.             constraint = DVConstraint.createFormulaListConstraint("INDIRECT(" + cr.formatAsString() + ")");  
70.             dataValidation = new HSSFDataValidation(cal, constraint);  
71.             dataValidation.setSuppressDropDownArrow(false);  
72.             dataValidation.createPromptBox("操作提示", "请选择下拉选中的值");  
73.             dataValidation.createErrorBox("错误提示", "请从下拉选中选择，不要随便输入");  
74.             sheet.addValidationData(dataValidation);  
75.         }  
76.     }  
77.   
78.     /** 
79.      * @param edList 
80.      * @param sheet 
81.      */  
82.     private static void setDataValidation(List<ExportDefinition> edList, Sheet sheet) {  
83.         for (ExportDefinition ed : edList) {  
84.             if (ed.isValidate()) {// 说明是下拉选  
85.                 DVConstraint constraint = DVConstraint.createFormulaListConstraint(ed.getField());  
86.                 if (null == ed.getRefName()) {// 说明是一级下拉选  
87.                     createDataValidate(sheet, ed, constraint);  
88.                 } else {// 说明是二级下拉选  
89.                     createDataValidateSubList(sheet, ed);  
90.                 }  
91.             }  
92.         }  
93.     }  
94.   
95.     /** 
96.      * @param sheet 
97.      * @param ed 
98.      * @param constraint 
99.      */  
100.     private static void createDataValidate(Sheet sheet, ExportDefinition ed, DVConstraint constraint) {  
101.         CellRangeAddressList regions = new CellRangeAddressList(ed.getRowIndex() + 1, ed.getRowIndex() + 100, ed.getCellIndex(), ed.getCellIndex());  
102.         DataValidation dataValidation = new HSSFDataValidation(regions, constraint);  
103.         dataValidation.setSuppressDropDownArrow(false);  
104.         // 设置提示信息  
105.         dataValidation.createPromptBox("操作提示", "请选择下拉选中的值");  
106.         // 设置输入错误信息  
107.         dataValidation.createErrorBox("错误提示", "请从下拉选中选择，不要随便输入");  
108.         sheet.addValidationData(dataValidation);  
109.     }  
110.   
111.     /** 
112.      * @param edList 
113.      * @param wb 
114.      */  
115.     private static void createDictSheet(List<ExportDefinition> edList, Workbook wb) {  
116.         Sheet sheet = wb.createSheet(DICT_SHEET);  
117.         RowCellIndex rci = new RowCellIndex(0, 0);  
118.         for (ExportDefinition ed : edList) {  
119.             String mainDict = ed.getMainDict();  
120.             if (null != mainDict && null == ed.getRefName()) {// 是第一个下拉选  
121.                 List<String> mainDictList = (List<String>) DictData.getDict(mainDict);  
122.                 String refersToFormula = createDictAndReturnRefFormula(sheet, rci, mainDictList);  
123.                 // 创建 命名管理  
124.                 createName(wb, ed.getField(), refersToFormula);  
125.                 ed.setValidate(true);  
126.             }  
127.             if (null != mainDict && null != ed.getSubDict() && null != ed.getSubField()) {// 联动时加载ed.getSubField()的数据  
128.                 ExportDefinition subEd = fiterByField(edList, ed.getSubField());// 获取需要级联的那个字段  
129.                 if (null == subEd) {  
130.                     continue;  
131.                 }  
132.                 subEd.setRefName(ed.getPoint());// 保存主下拉选的位置  
133.                 subEd.setValidate(true);  
134.                 Map<String, List<String>> subDictListMap = (Map<String, List<String>>) DictData.getDict(ed.getSubDict());  
135.                 for (Entry<String, List<String>> entry : subDictListMap.entrySet()) {  
136.                     String refersToFormula = createDictAndReturnRefFormula(sheet, rci, entry.getValue());  
137.                     // 创建 命名管理  
138.                     createName(wb, entry.getKey(), refersToFormula);  
139.                 }  
140.             }  
141.         }  
142.     }  
143.   
144.     /** 
145.      * @param sheet 
146.      * @param rci 
147.      * @param mainDict 
148.      * @return 
149.      */  
150.     private static String createDictAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas) {  
151.         Row row = sheet.createRow(rci.incrementRowIndexAndGet());  
152.         rci.setCellIndex(0);  
153.         int startRow = rci.getRowIndex();  
154.         int startCell = rci.getCellIndex();  
155.         for (String dict : datas) {  
156.             row.createCell(rci.incrementCellIndexAndGet()).setCellValue(dict);  
157.         }  
158.         int endRow = rci.getRowIndex();  
159.         int endCell = rci.getCellIndex();  
160.         String startName = new CellReference(DICT_SHEET, startRow, startCell, true, true).formatAsString();  
161.         String endName = new CellReference(endRow, endCell, true, true).formatAsString();  
162.         String refersToFormula = startName + ":" + endName;  
163.         System.out.println(refersToFormula);  
164.         return refersToFormula;  
165.     }  
166.   
167.     /** 
168.      * @param wb 
169.      * @param nameName 
170.      *            表示命名管理的名字 
171.      * @param refersToFormula 
172.      */  
173.     private static void createName(Workbook wb, String nameName, String refersToFormula) {  
174.         Name name = wb.createName();  
175.         name.setNameName(nameName);  
176.         name.setRefersToFormula(refersToFormula);  
177.     }  
178.   
179.     private static ExportDefinition fiterByField(List<ExportDefinition> edList, String field) {  
180.         for (ExportDefinition ed : edList) {  
181.             if (Objects.equals(ed.getField(), field)) {  
182.                 return ed;  
183.             }  
184.         }  
185.         return null;  
186.     }  
187.   
188.     /** 
189.      * @param edList 
190.      * @param wb 
191.      */  
192.     private static Sheet createExportSheet(List<ExportDefinition> edList, Workbook wb) {  
193.         Sheet sheet = wb.createSheet("导出模板");  
194.         RowCellIndex rci = new RowCellIndex(0, 0);  
195.         Row row = sheet.createRow(rci.getRowIndex());  
196.         CellReference cr = null;  
197.         for (ExportDefinition ed : edList) {  
198.             row.createCell(rci.incrementCellIndexAndGet()).setCellValue(ed.getTitle());  
199.             ed.setRowIndex(rci.getRowIndex());  
200.             ed.setCellIndex(rci.getCellIndex());  
201.             cr = new CellReference(ed.getRowIndex() + 1, ed.getCellIndex(), true, true);  
202.             ed.setPoint(cr.formatAsString());  
203.         }  
204.         return sheet;  
205.     }  
206.   
207. }