JAVA生成excel模板，支持1、必填字段前加 红色 * 2、定义可选值下拉列表 valList3、定义名称并通过名称设置可选值 refName（名称在sheet2,sheet2自动隐藏）4、支持设置多字段联动 indirectTitle5、自定义隐藏列，自定义列宽，自定义标题行高度效果如图:
代码如下:
1、引入依赖
1 <dependency>
2 <groupId>org.apache.poi</groupId>
3 <artifactId>poi</artifactId>
4 <version>3.9</version>
5 </dependency>
6 <dependency>
7 <groupId>org.apache.poi</groupId>
8 <artifactId>poi-ooxml</artifactId>
9 <version>3.9</version>
10 </dependency>
2、ExcelTemp.java
1 package com.excel;
2
3 import com.lix.common.StringUtil;
4 import org.apache.poi.ss.usermodel.*;
5 import org.apache.poi.ss.util.CellRangeAddressList;
6 import org.apache.poi.xssf.usermodel.*;
7
8 import java.io.File;
9 import java.io.FileOutputStream;
10 import java.io.OutputStream;
11 import java.util.ArrayList;
12 import java.util.Arrays;
13 import java.util.List;
14 import java.util.Optional;
15
16 /**
17 * @author svice
18 * @date 2020/3/12 16:38
19 */
20 public class ExcelTemp {
21
22 private final int EXCEL_MAX_LINE_NUM = 1000000;
23
24 private int titleHeight = 0;
25
26 private List<ExcelTempName> names = new ArrayList<>();
27
28 private List<ExcelTempField> fields = new ArrayList<>();
29
30 public List<ExcelTempName> getNames() {
31 return names;
32 }
33
34 public List<ExcelTempField> getFields() {
35 return fields;
36 }
37
38 public void setTitleHeight(int titleHeight) throws Exception {
39 if (titleHeight > 1000) {
40 throw new Exception("titleHeight不能超过1000");
41 } else {
42 this.titleHeight = titleHeight;
43 }
44 }
45
46 public void save(String fileName) throws Exception {
47 if (fields.size() == 0) {
48 throw new Exception("字段列表为空");
49 }
50
51 XSSFWorkbook workBook = new XSSFWorkbook();
52 XSSFSheet sheet1 = workBook.createSheet("sheet1");
53 XSSFRow row0 = sheet1.createRow(0);
54
55 if (titleHeight > 0) {
56 row0.setHeight((short) (titleHeight * 20));
57 }
58
59 XSSFCellStyle cellStyle = workBook.createCellStyle();
60 DataFormat format = workBook.createDataFormat();
61 // 单元格文本格式
62 cellStyle.setDataFormat(format.getFormat("@"));
63 cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
64 cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
65 cellStyle.setBorderTop((short) 1);
66 cellStyle.setBorderRight((short) 1);
67 cellStyle.setBorderBottom((short) 1);
68 cellStyle.setBorderLeft((short) 1);
69 // 垂直居中
70 cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
71 // 水平居中
72 cellStyle.setAlignment(HorizontalAlignment.CENTER);
73
74 Font fontRed = workBook.createFont();
75 fontRed.setColor(IndexedColors.RED.getIndex());
76
77 Font fontBlack = workBook.createFont();
78 fontBlack.setColor(IndexedColors.BLACK.getIndex());
79
80 for (int colIndex = 0; colIndex < fields.size(); colIndex++) {
81 ExcelTempField field = fields.get(colIndex);
82 XSSFCell cell = row0.createCell(colIndex);
83 cell.setCellStyle(cellStyle);
84
85 if (field.isHidden()) {
86 sheet1.setColumnWidth(colIndex, 0);
87 } else if (field.getWidth() > 0) {
88 sheet1.setColumnWidth(colIndex, field.getWidth() * 256);
89 }
90
91 // 标题文本
92 if (field.isRequire()) {
93 XSSFRichTextString richTextString = new XSSFRichTextString("*" + field.getTitle());
94 richTextString.applyFont(0, 1, fontRed);
95 richTextString.applyFont(1, field.getTitle().length(), fontBlack);
96 cell.setCellValue(richTextString);
97 } else {
98 cell.setCellValue(field.getTitle());
99 }
100
101 // 设置数据有效性下拉列表
102 if (field.getValList().size() > 0) {
103 CellRangeAddressList addressList = new CellRangeAddressList(1, EXCEL_MAX_LINE_NUM, colIndex, colIndex);
104 String[] values = field.getValList().toArray(new String[]{});
105 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet1);
106 XSSFDataValidationConstraint col2 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(values);
107 XSSFDataValidation dv2 = (XSSFDataValidation) dvHelper.createValidation(col2, addressList);
108 sheet1.addValidationData(dv2);
109 } else if (StringUtil.isNotEmpty(field.getRefName())) {
110 if (this.names.stream().anyMatch(n -> n.getName().equals(field.getRefName()))) {
111 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet1);
112 DataValidationConstraint dvc3 = dvHelper.createFormulaListConstraint(field.getRefName());
113 CellRangeAddressList col3 = new CellRangeAddressList(1, EXCEL_MAX_LINE_NUM, colIndex, colIndex);
114 XSSFDataValidation dv3 = (XSSFDataValidation) dvHelper.createValidation(dvc3, col3);
115 sheet1.addValidationData(dv3);
116 } else {
117 throw new Exception("名称:" + field.getRefName() + "无效");
118 }
119 } else if (StringUtil.isNotEmpty(field.getIndirectTitle())) {
120 List<String> fieldNames = new ArrayList<>();
121 for (ExcelTempField excelTempField : this.fields) {
122 fieldNames.add(excelTempField.getTitle());
123 }
124 int indirectColIndex = fieldNames.indexOf(field.getIndirectTitle());
125 if (indirectColIndex != -1) {
126 String indirectColName = ColNameUtil.getColName(indirectColIndex);
127 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet1);
128 CellRangeAddressList col4 = new CellRangeAddressList(1, EXCEL_MAX_LINE_NUM, colIndex, colIndex);
129 DataValidationConstraint dvc4 = dvHelper.createFormulaListConstraint("INDIRECT($" + indirectColName + "2)");
130 XSSFDataValidation dv4 = (XSSFDataValidation) dvHelper.createValidation(dvc4, col4);
131 sheet1.addValidationData(dv4);
132 } else {
133 throw new Exception("列名称:" + field.getIndirectTitle() + "无效");
134 }
135 }
136
137
138 }
139
140 if (names.size() > 0) {
141 XSSFSheet sheet2 = workBook.createSheet("sheet2");
142 workBook.setSheetHidden(1, true);
143 for (int colIndex = 0; colIndex < names.size(); colIndex++) {
144 ExcelTempName excelName = names.get(colIndex);
145 String title = excelName.getName();
146 List<String> valList = excelName.getValList();
147
148 XSSFRow titleRow = colIndex == 0 ? sheet2.createRow(0) : sheet2.getRow(0);
149 XSSFCell nameTitle = Optional.ofNullable(titleRow.getCell(colIndex)).orElse(titleRow.createCell(colIndex));
150 nameTitle.setCellValue(title);
151 nameTitle.setCellStyle(cellStyle);
152 String colName = ColNameUtil.getColName(colIndex);
153 for (int i = 0; i < valList.size(); i++) {
154 String val = valList.get(i);
155 int rowNum = i + 1;
156 XSSFRow row = sheet2.getRow(rowNum);
157 if (null == row) {
158 row = sheet2.createRow(rowNum);
159 }
160 row.createCell(colIndex).setCellValue(val);
161 }
162 XSSFName dicRangea = workBook.createName();
163 dicRangea.setRefersToFormula("sheet2!$" + colName + "$2:$" + colName + "$" + (valList.size() + 1));
164 dicRangea.setNameName(title);
165 }
166 }
167
168 File file = new File(fileName);
169 if (!file.exists()) {
170 boolean newFile = file.createNewFile();
171 }
172 OutputStream os = new FileOutputStream(file);
173 workBook.write(os);
174 os.close();
175 }
176
177 public static void main(String[] args) throws Exception {
178 ExcelTemp excelTemp = new ExcelTemp();
179 excelTemp.setTitleHeight(50);
180
181 ExcelTempField col1 = new ExcelTempField("姓名");
182 col1.setRequire(true);
183 excelTemp.getFields().add(col1);
184
185 ExcelTempField col2 = new ExcelTempField("性别");
186 col2.setRequire(true);
187 col2.setValList(Arrays.asList("男", "女"));
188 excelTemp.getFields().add(col2);
189
190 ExcelTempField col3 = new ExcelTempField("服装");
191 col3.setIndirectTitle("性别");
192 excelTemp.getFields().add(col3);
193
194 ExcelTempField col4 = new ExcelTempField("年龄段");
195 col4.setRefName("年龄段");
196 excelTemp.getFields().add(col4);
197
198 ExcelTempField col5 = new ExcelTempField("隐藏列");
199 col5.setHidden(true);
200 excelTemp.getFields().add(col5);
201
202 ExcelTempField col6 = new ExcelTempField("宽度80");
203 col6.setWidth(80);
204 excelTemp.getFields().add(col6);
205
206 ExcelTempName name1 = new ExcelTempName();
207 name1.setName("男");
208 name1.getValList().add("男装一号");
209 name1.getValList().add("男装二号");
210 name1.getValList().add("男装三号");
211 excelTemp.getNames().add(name1);
212
213 ExcelTempName name2 = new ExcelTempName();
214 name2.setName("女");
215 name2.getValList().add("女装一号");
216 name2.getValList().add("女装二号");
217 name2.getValList().add("女装三号");
218 name2.getValList().add("女装四号");
219 name2.getValList().add("女装五号");
220 excelTemp.getNames().add(name2);
221
222 ExcelTempName name3 = new ExcelTempName();
223 name3.setName("年龄段");
224 name3.getValList().add("幼年");
225 name3.getValList().add("童年");
226 name3.getValList().add("青年");
227 name3.getValList().add("中年");
228 name3.getValList().add("老年");
229 excelTemp.getNames().add(name3);
230
231 excelTemp.save("D://temp/excel_temp_test_" + System.currentTimeMillis() + ".xlsx");
232 }
233 }
3、ExcelTempName.java
1 package com.excel;
2
3 import java.util.ArrayList;
4 import java.util.List;
5
6 /**
7 * @author svice
8 * @date 2020/3/12 16:38
9 */
10 public class ExcelTempName {
11
12 private String name;
13
14 private List<String> valList = new ArrayList<>();
15
16 public String getName() {
17 return name;
18 }
19
20 public void setName(String name) {
21 this.name = name;
22 }
23
24 public List<String> getValList() {
25 return valList;
26 }
27
28 public void setValList(List<String> valList) {
29 this.valList = valList;
30 }
31 }
4、ExcelTempField .java
1 package com.excel;
2
3 import org.apache.poi.xssf.usermodel.XSSFSheet;
4 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
5
6 import java.util.ArrayList;
7 import java.util.HashMap;
8 import java.util.List;
9 import java.util.Map;
10
11 /**
12 * @author svice
13 * @date 2020/3/12 16:38
14 */
15 public class ExcelTempField {
16
17 private String title;
18
19 private boolean require = false;
20
21 private List<String> valList = new ArrayList<>();
22
23 private String refName;
24
25 private String indirectTitle;
26
27 private int width;
28
29 private boolean hidden = false;
30
31 public ExcelTempField(String title) {
32 this.title = title;
33 }
34
35 public String getTitle() {
36 return title;
37 }
38
39 public void setTitle(String title) {
40 this.title = title;
41 }
42
43 public boolean isRequire() {
44 return require;
45 }
46
47 public void setRequire(boolean require) {
48 this.require = require;
49 }
50
51 public List<String> getValList() {
52 return valList;
53 }
54
55 public void setValList(List<String> valList) {
56 this.valList = valList;
57 }
58
59 public String getRefName() {
60 return refName;
61 }
62
63 public void setRefName(String refName) {
64 this.refName = refName;
65 }
66
67 public String getIndirectTitle() {
68 return indirectTitle;
69 }
70
71 public void setIndirectTitle(String indirectTitle) {
72 this.indirectTitle = indirectTitle;
73 }
74
75 public int getWidth() {
76 return width;
77 }
78
79 public void setWidth(int width) {
80 this.width = width;
81 }
82
83 public boolean isHidden() {
84 return hidden;
85 }
86
87 public void setHidden(boolean hidden) {
88 this.hidden = hidden;
89 }
90 }
5、ColNameUtil.java
1 package com.excel;
2
3 /**
4 * @author svice
5 * @date 2020/3/13 0:03
6 */
7 public class ColNameUtil {
8 public static String getColName(int colIndex) throws Exception {
9 switch (colIndex) {
10 case 0:
11 return "A";
12 case 1:
13 return "B";
14 case 2:
15 return "C";
16 case 3:
17 return "D";
18 case 4:
19 return "E";
20 case 5:
21 return "F";
22 case 6:
23 return "G";
24 case 7:
25 return "H";
26 case 8:
27 return "I";
28 case 9:
29 return "J";
30 case 10:
31 return "K";
32 case 11:
33 return "L";
34 case 12:
35 return "M";
36 case 13:
37 return "N";
38 case 14:
39 return "O";
40 case 15:
41 return "P";
42 case 16:
43 return "Q";
44 case 17:
45 return "R";
46 case 18:
47 return "S";
48 case 19:
49 return "T";
50 case 20:
51 return "U";
52 case 21:
53 return "V";
54 case 22:
55 return "W";
56 case 23:
57 return "X";
58 case 24:
59 return "Y";
60 case 25:
61 return "Z";
62 case 26:
63 return "AA";
64 case 27:
65 return "AB";
66 case 28:
67 return "AC";
68 case 29:
69 return "AD";
70 case 30:
71 return "AE";
72 case 31:
73 return "AF";
74 case 32:
75 return "AG";
76 case 33:
77 return "AH";
78 case 34:
79 return "AI";
80 case 35:
81 return "AJ";
82 case 36:
83 return "AK";
84 case 37:
85 return "AL";
86 case 38:
87 return "AM";
88 case 39:
89 return "AN";
90 case 40:
91 return "AO";
92 case 41:
93 return "AP";
94 case 42:
95 return "AQ";
96 case 43:
97 return "AR";
98 case 44:
99 return "AS";
100 case 45:
101 return "AT";
102 case 46:
103 return "AU";
104 case 47:
105 return "AV";
106 case 48:
107 return "AW";
108 case 49:
109 return "AX";
110 case 50:
111 return "AY";
112 case 51:
113 return "AZ";
114 case 52:
115 return "BA";
116 case 53:
117 return "BB";
118 case 54:
119 return "BC";
120 case 55:
121 return "BD";
122 case 56:
123 return "BE";
124 case 57:
125 return "BF";
126 case 58:
127 return "BG";
128 case 59:
129 return "BH";
130 case 60:
131 return "BI";
132 case 61:
133 return "BJ";
134 case 62:
135 return "BK";
136 case 63:
137 return "BL";
138 case 64:
139 return "BM";
140 case 65:
141 return "BN";
142 case 66:
143 return "BO";
144 case 67:
145 return "BP";
146 case 68:
147 return "BQ";
148 case 69:
149 return "BR";
150 case 70:
151 return "BS";
152 case 71:
153 return "BT";
154 case 72:
155 return "BU";
156 case 73:
157 return "BV";
158 case 74:
159 return "BW";
160 case 75:
161 return "BX";
162 case 76:
163 return "BY";
164 case 77:
165 return "BZ";
166 case 78:
167 return "CA";
168 case 79:
169 return "CB";
170 case 80:
171 return "CC";
172 case 81:
173 return "CD";
174 case 82:
175 return "CE";
176 case 83:
177 return "CF";
178 case 84:
179 return "CG";
180 case 85:
181 return "CH";
182 case 86:
183 return "CI";
184 case 87:
185 return "CJ";
186 case 88:
187 return "CK";
188 case 89:
189 return "CL";
190 case 90:
191 return "CM";
192 case 91:
193 return "CN";
194 case 92:
195 return "CO";
196 case 93:
197 return "CP";
198 case 94:
199 return "CQ";
200 case 95:
201 return "CR";
202 case 96:
203 return "CS";
204 case 97:
205 return "CT";
206 case 98:
207 return "CU";
208 case 99:
209 return "CV";
210 case 100:
211 return "CW";
212 case 101:
213 return "CX";
214 case 102:
215 return "CY";
216 case 103:
217 return "CZ";
218 case 104:
219 return "DA";
220 case 105:
221 return "DB";
222 case 106:
223 return "DC";
224 case 107:
225 return "DD";
226 case 108:
227 return "DE";
228 case 109:
229 return "DF";
230 case 110:
231 return "DG";
232 case 111:
233 return "DH";
234 case 112:
235 return "DI";
236 case 113:
237 return "DJ";
238 case 114:
239 return "DK";
240 case 115:
241 return "DL";
242 case 116:
243 return "DM";
244 case 117:
245 return "DN";
246 case 118:
247 return "DO";
248 case 119:
249 return "DP";
250 case 120:
251 return "DQ";
252 case 121:
253 return "DR";
254 case 122:
255 return "DS";
256 case 123:
257 return "DT";
258 case 124:
259 return "DU";
260 case 125:
261 return "DV";
262 case 126:
263 return "DW";
264 case 127:
265 return "DX";
266 case 128:
267 return "DY";
268 case 129:
269 return "DZ";
270 case 130:
271 return "EA";
272 case 131:
273 return "EB";
274 case 132:
275 return "EC";
276 case 133:
277 return "ED";
278 case 134:
279 return "EE";
280 case 135:
281 return "EF";
282 case 136:
283 return "EG";
284 case 137:
285 return "EH";
286 case 138:
287 return "EI";
288 case 139:
289 return "EJ";
290 case 140:
291 return "EK";
292 case 141:
293 return "EL";
294 case 142:
295 return "EM";
296 case 143:
297 return "EN";
298 case 144:
299 return "EO";
300 case 145:
301 return "EP";
302 case 146:
303 return "EQ";
304 case 147:
305 return "ER";
306 case 148:
307 return "ES";
308 case 149:
309 return "ET";
310 case 150:
311 return "EU";
312 case 151:
313 return "EV";
314 case 152:
315 return "EW";
316 case 153:
317 return "EX";
318 case 154:
319 return "EY";
320 case 155:
321 return "EZ";
322 case 156:
323 return "FA";
324 case 157:
325 return "FB";
326 case 158:
327 return "FC";
328 case 159:
329 return "FD";
330 case 160:
331 return "FE";
332 case 161:
333 return "FF";
334 case 162:
335 return "FG";
336 case 163:
337 return "FH";
338 case 164:
339 return "FI";
340 case 165:
341 return "FJ";
342 case 166:
343 return "FK";
344 case 167:
345 return "FL";
346 case 168:
347 return "FM";
348 case 169:
349 return "FN";
350 case 170:
351 return "FO";
352 case 171:
353 return "FP";
354 case 172:
355 return "FQ";
356 case 173:
357 return "FR";
358 case 174:
359 return "FS";
360 case 175:
361 return "FT";
362 case 176:
363 return "FU";
364 case 177:
365 return "FV";
366 case 178:
367 return "FW";
368 case 179:
369 return "FX";
370 case 180:
371 return "FY";
372 case 181:
373 return "FZ";
374 case 182:
375 return "GA";
376 case 183:
377 return "GB";
378 case 184:
379 return "GC";
380 case 185:
381 return "GD";
382 case 186:
383 return "GE";
384 case 187:
385 return "GF";
386 case 188:
387 return "GG";
388 case 189:
389 return "GH";
390 case 190:
391 return "GI";
392 case 191:
393 return "GJ";
394 case 192:
395 return "GK";
396 case 193:
397 return "GL";
398 case 194:
399 return "GM";
400 case 195:
401 return "GN";
402 case 196:
403 return "GO";
404 case 197:
405 return "GP";
406 case 198:
407 return "GQ";
408 case 199:
409 return "GR";
410 case 200:
411 return "GS";
412 case 201:
413 return "GT";
414 case 202:
415 return "GU";
416 case 203:
417 return "GV";
418 case 204:
419 return "GW";
420 case 205:
421 return "GX";
422 case 206:
423 return "GY";
424 case 207:
425 return "GZ";
426 default:
427 throw new Exception("超过最大限制");
428 }
429 }
430 }
