JAVA生成EXCEL模板

2020-03-14 16:02:55来源：博客园阅读 ()

JAVA生成EXCEL模板

JAVA生成excel模板，支持
1、必填字段前加 红色 * 
2、定义可选值下拉列表 valList
3、定义名称并通过名称设置可选值 refName（名称在sheet2,sheet2自动隐藏）
4、支持设置多字段联动 indirectTitle
5、自定义隐藏列，自定义列宽，自定义标题行高度

效果如图:

代码如下:

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 }