廢話(huà)不多說(shuō)了，直接給大家貼代碼了，具體代碼如下所示：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
|
import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.Arrays;import java.util.List;import org.apache.poi.hssf.usermodel.DVConstraint;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFDataValidation;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.DataValidation;import org.apache.poi.ss.usermodel.Name;import org.apache.poi.ss.util.CellRangeAddressList;public class ExcelLinkage { // 樣式 private HSSFCellStyle cellStyle; // 初始化省份數(shù)據(jù) private List<String> province = new ArrayList<String>(Arrays.asList("湖南", "廣東")); // 初始化數(shù)據(jù)（湖南的市區(qū)） private List<String> hnCity = new ArrayList<String>(Arrays.asList("長(zhǎng)沙市", "邵陽(yáng)市")); // 初始化數(shù)據(jù)（廣東市區(qū)） private List<String> gdCity = new ArrayList<String>(Arrays.asList("深圳市", "廣州市")); public void setDataCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) { cellStyle = workbook.createCellStyle(); // 設(shè)置邊框 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 設(shè)置背景色 cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 設(shè)置居中 cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 設(shè)置字體 HSSFFont font = workbook.createFont(); font.setFontName("宋體"); font.setFontHeightInPoints((short) 11); // 設(shè)置字體大小 cellStyle.setFont(font);// 選擇需要用到的字體格式 // 設(shè)置單元格格式為文本格式（這里還可以設(shè)置成其他格式,可以自行百度） HSSFDataFormat format = workbook.createDataFormat(); cellStyle.setDataFormat(format.getFormat("@")); } /** * 創(chuàng)建數(shù)據(jù)域（下拉聯(lián)動(dòng)的數(shù)據(jù)） * * @param workbook * @param hideSheetName * 數(shù)據(jù)域名稱(chēng) */ private void creatHideSheet(HSSFWorkbook workbook, String hideSheetName) { // 創(chuàng)建數(shù)據(jù)域 HSSFSheet sheet = workbook.createSheet(hideSheetName); // 用于記錄行 int rowRecord = 0; // 獲取行（從0下標(biāo)開(kāi)始） HSSFRow provinceRow = sheet.createRow(rowRecord); // 創(chuàng)建省份數(shù)據(jù) this.creatRow(provinceRow, province); // 根據(jù)省份插入對(duì)應(yīng)的市信息 rowRecord++; for (int i = 0; i < province.size(); i++) { List<String> list = new ArrayList<String>(); // 我這里是寫(xiě)死的 ， 實(shí)際中應(yīng)該從數(shù)據(jù)庫(kù)直接獲取更好 if (province.get(i).toString().equals("湖南")) { // 將省份名稱(chēng)放在插入市的第一列， 這個(gè)在后面的名稱(chēng)管理中需要用到 list.add(0, province.get(i).toString()); list.addAll(hnCity); } else { list.add(0, province.get(i).toString()); list.addAll(gdCity); } //獲取行 HSSFRow Cityrow = sheet.createRow(rowRecord); // 創(chuàng)建省份數(shù)據(jù) this.creatRow(Cityrow, list); rowRecord++; } } /** * 創(chuàng)建一列數(shù)據(jù) * * @param currentRow * @param textList */ public void creatRow(HSSFRow currentRow, List<String> text) { if (text != null) { int i = 0; for (String cellValue : text) { // 注意列是從（1）下標(biāo)開(kāi)始 HSSFCell userNameLableCell = currentRow.createCell(i++); userNameLableCell.setCellValue(cellValue); } } } /** * 名稱(chēng)管理 * * @param workbook * @param hideSheetName * 數(shù)據(jù)域的sheet名 */ private void creatExcelNameList(HSSFWorkbook workbook, String hideSheetName) { Name name; name = workbook.createName(); // 設(shè)置省名稱(chēng) name.setNameName("province"); name.setRefersToFormula(hideSheetName + "!$A$1:$" + this.getcellColumnFlag(province.size())+ "$1"); // 設(shè)置省下面的市 for (int i = 0; i < province.size(); i++) { List<String> num = new ArrayList<String>(); if (province.get(i).toString().equals("湖南")) { name = workbook.createName(); num.add(0,province.get(i).toString()); num.addAll(hnCity); name.setNameName(province.get(i).toString()); name.setRefersToFormula(hideSheetName + "!$B$" + (i + 2) + ":$" + this.getcellColumnFlag(num.size()) + "$" + (i + 2)); } else { name = workbook.createName(); num.add(0,province.get(i).toString()); num.addAll(gdCity); name.setNameName(province.get(i).toString()); name.setRefersToFormula(hideSheetName + "!$B$" + (i + 2) + ":$" + this.getcellColumnFlag(num.size()) + "$" + (i + 2)); } } } // 根據(jù)數(shù)據(jù)值確定單元格位置（比如：28-AB） private String getcellColumnFlag(int num) { String columFiled = ""; int chuNum = 0; int yuNum = 0; if (num >= 1 && num <= 26) { columFiled = this.doHandle(num); } else { chuNum = num / 26; yuNum = num % 26; columFiled += this.doHandle(chuNum); columFiled += this.doHandle(yuNum); } return columFiled; } private String doHandle(final int num) { String[] charArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; return charArr[num - 1].toString(); } /** * 使用已定義的數(shù)據(jù)源方式設(shè)置一個(gè)數(shù)據(jù)驗(yàn)證 * * @param formulaString * @param naturalRowIndex * @param naturalColumnIndex * @return */ public DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex) { // 加載下拉列表內(nèi)容 DVConstraint constraint = DVConstraint .createFormulaListConstraint(formulaString); // 設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上。 // 四個(gè)參數(shù)分別是：起始行、終止行、起始列、終止列 int firstRow = naturalRowIndex; int lastRow = naturalRowIndex; int firstCol = naturalColumnIndex - 1; int lastCol = naturalColumnIndex - 1; CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); // 數(shù)據(jù)有效性對(duì)象 DataValidation data_validation_list = new HSSFDataValidation(regions, constraint); return data_validation_list; } /** * 創(chuàng)建一列數(shù)據(jù) * * @param hssfSheet */ public void creatAppRow(HSSFSheet hssfSheet, int naturalRowIndex) { // 獲取行 HSSFRow hssfRow = hssfSheet.createRow(naturalRowIndex); HSSFCell province = hssfRow.createCell(0); province.setCellValue(""); province.setCellStyle(cellStyle); HSSFCell City = hssfRow.createCell(1); City.setCellValue(""); City.setCellStyle(cellStyle); // 得到驗(yàn)證對(duì)象 DataValidation data_validation_list1 = this.getDataValidationByFormula( "province", naturalRowIndex, 1); DataValidation data_validation_list2 = this .getDataValidationByFormula("INDIRECT($A" + (naturalRowIndex + 1) + ")", naturalRowIndex, 2); // 工作表添加驗(yàn)證數(shù)據(jù) hssfSheet.addValidationData(data_validation_list1); hssfSheet.addValidationData(data_validation_list2); } public void Export() { try { FileOutputStream outputStream = new FileOutputStream(file); // 創(chuàng)建excel HSSFWorkbook workbook = new HSSFWorkbook(); // 設(shè)置sheet 名稱(chēng) HSSFSheet excelSheet = workbook.createSheet("excel"); // 設(shè)置樣式 this.setDataCellStyles(workbook, excelSheet); // 創(chuàng)建一個(gè)隱藏頁(yè)和隱藏?cái)?shù)據(jù)集 this.creatHideSheet(workbook, "shutDataSource"); // 設(shè)置名稱(chēng)數(shù)據(jù)集 this.creatExcelNameList(workbook, "shutDataSource"); // 創(chuàng)建一行數(shù)據(jù) for (int i = 0; i < 50; i++) { this.creatAppRow(excelSheet,i); } workbook.write(outputStream); outputStream.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } public static void main(String[] args) { ExcelLinkage linkage = new ExcelLinkage(); linkage.Export(); }} |
總結(jié)
以上所述是小編給大家介紹的Java 使用POI生成帶聯(lián)動(dòng)下拉框的excel表格，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)服務(wù)器之家網(wǎng)站的支持！
原文鏈接：http://www.cnblogs.com/cjbbk/archive/2017/09/15/7527276.html
