本文实例讲述了java实现的导出Excel工具类。分享给大家供大家参考，具体如下：
ExcelExportUtil：
|
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
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
|
package com.excel;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.HashSet;import java.util.Map;import java.util.Set;import java.util.regex.Pattern;import jxl.Workbook;import jxl.write.Label;import jxl.write.Number;import jxl.write.NumberFormat;import jxl.write.WritableCellFormat;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;/** * 生成excel表格 * * @author * */public class ExcelExportUtil { /** * 构造器 * */ public ExcelExportUtil() { } /** * 生成具有一定格式excel * * @param sheetName * sheet名称,默认为sheet1 * @param nf * 数字类型的格式 如:jxl.write.NumberFormat nf = new * jxl.write.NumberFormat("#.##");默认无格式 * @param content * 二维数组,要生成excel的数据来源 * @param 合并项 * 每一项的数据格式为0,1,0,2 即：把(0,1)和(0,2)合并--->第1列的第一、二个元素合并 * @param os * excel输出流 * @param row * 需要水平居中的行,默认居左。以逗号分隔的字符串 * @param col * 需要水平居中的列,默认居左。以逗号分隔的字符串 */ public void export(String sheetName, NumberFormat nf, String[][] content, String[] mergeInfo, OutputStream os, String row, String col) { if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) { return; } // 默认名称 if (VerifyUtil.isNullObject(sheetName)) { sheetName = "sheet1"; } Set<Integer> rows = this.getInfo(row); Set<Integer> cols = this.getInfo(col); WritableWorkbook workbook = null; try { workbook = Workbook.createWorkbook(os); WritableSheet sheet = workbook.createSheet(sheetName, 0); for (int i = 0; i < content.length; i++) { for (int j = 0; j < content[i].length; j++) { if (content[i][j] == null) { content[i][j] = ""; } if (isNumber(content[i][j]) && !rows.contains(i) && !cols.contains(j)) {// 处理数字 Number number = null; if (VerifyUtil.isNullObject(nf)) {// 数字无格式 number = new Number(j, i, Double.valueOf(content[i][j])); } else {// 如果有格式,按格式生成 jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat( nf); number = new Number(j, i, Double.valueOf(content[i][j]), wcfn); } sheet.addCell(number); } else {// 处理非数字 WritableCellFormat format = new WritableCellFormat(); if (rows.contains(i) || cols.contains(j)) { format.setAlignment(jxl.format.Alignment.CENTRE); } else { format.setAlignment(jxl.format.Alignment.LEFT); } format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); Label label = new Label(j, i, content[i][j], format); sheet.addCell(label); } } } this.merge(sheet, mergeInfo); workbook.write(); } catch (Exception e) { e.printStackTrace(); } finally { try { workbook.close(); os.close(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** * 生成固定格式的excel,表格都为文本,水平居左,垂直居中 * * @param sheetName * sheet名称,默认为sheet1 * @param content * 二维数组,要生成excel的数据来源 * @param os * excel输出流 */ public void exportFormatExcel(String[][] content, String sheetName, OutputStream os) { if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) { return; } // 默认名称 if (VerifyUtil.isNullObject(sheetName)) { sheetName = "sheet1"; } WritableWorkbook workbook = null; try { workbook = Workbook.createWorkbook(os); WritableSheet sheet = workbook.createSheet(sheetName, 0); for (int i = 0; i < content.length; i++) { for (int j = 0; j < content[i].length; j++) { if (content[i][j] == null) { content[i][j] = ""; } WritableCellFormat format = new WritableCellFormat(); format.setAlignment(jxl.format.Alignment.LEFT); format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); Label label = new Label(j, i, content[i][j], format); sheet.addCell(label); } } workbook.write(); } catch (Exception e) { e.printStackTrace(); } finally { try { workbook.close(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** * 生成固定格式的excel,表格都为文本,水平居左,垂直居中 * * @param sheetName * sheet名称,默认为sheet1 * @param content * Map,要生成excel的数据来源 * @param os * excel输出流 */ public void exportFormatExcel(Map<String, String[][]> content, String[] salary_name_array, String sheetName, OutputStream os) { if (VerifyUtil.isNullObject(content, os) || content.size() == 0) { return; } // 默认名称 if (VerifyUtil.isNullObject(sheetName)) { sheetName = "sheet1"; } WritableWorkbook workbook = null; try { workbook = Workbook.createWorkbook(os); WritableSheet sheet = workbook.createSheet(sheetName, 0); int index = 0; for (int k = 0; k < salary_name_array.length; k++) { String[][] value = (String[][]) content .get(salary_name_array[k]); if (value != null && value.length > 0) { if (index != 0) { index++; } WritableCellFormat format1 = new WritableCellFormat(); format1.setAlignment(jxl.format.Alignment.LEFT); format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); Label label1 = new Label(0, index, salary_name_array[k], format1); sheet.addCell(label1); for (int i = 0; i < value.length; i++) { index++; for (int j = 0; j < value[i].length; j++) { if (value[i][j] == null) { value[i][j] = ""; } WritableCellFormat format = new WritableCellFormat(); format.setAlignment(jxl.format.Alignment.LEFT); format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); Label label = new Label(j, index, value[i][j], format); sheet.addCell(label); } } } } workbook.write(); } catch (Exception e) { e.printStackTrace(); } finally { try { workbook.close(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** * 合并表格 * @param sheet * 工作表 * @param mergeInfo * 要合并的表格的信息 * @throws RowsExceededException * @throws NumberFormatException * @throws WriteException */ private void merge(WritableSheet sheet, String[] mergeInfo) throws RowsExceededException, NumberFormatException, WriteException { if (VerifyUtil.isNullObject(sheet) || VerifyUtil.isNull1DArray(mergeInfo)) { return; } else if (!this.isMergeInfo(mergeInfo)) { return; } else { for (String str : mergeInfo) { String[] temp = str.split(","); sheet.mergeCells(Integer.parseInt(temp[1]), Integer.parseInt(temp[0]), Integer.parseInt(temp[3]), Integer.parseInt(temp[2])); } } } /** * 处理要居中的行或列的数据 * * @param indexes * 行标或列标 * @return 行坐标或列坐标组成的集合 */ private Set<Integer> getInfo(String indexes) { Set<Integer> set = new HashSet<Integer>(); if (VerifyUtil.isNullObject(indexes)) { return set; } String[] temp = indexes.split(",", 0); for (String str : temp) { if (isNumeric(str)) { set.add(Integer.parseInt(str)); } } return set; } /** * 判断字符串是否由纯数字组成 * * @param str * 源字符串 * @return true是，false否 */ private boolean isNumeric(String str) { if (VerifyUtil.isNullObject(str)) { return false; } Pattern pattern = Pattern.compile("[0-9]*"); return pattern.matcher(str).matches(); } /** * 判断字符串是否是数字 * * @param str * 源字符串 * @return true是，false否 */ private boolean isNumber(String number) { // 判断参数 if (VerifyUtil.isNullObject(number)) { return false; } // 查看是否有小数点 int index = number.indexOf("."); if (index < 0) { return isNumeric(number); } else { // 如果有多个".",则不是数字 if (number.indexOf(".") != number.lastIndexOf(".")) { return false; } String num1 = number.substring(0, index); String num2 = number.substring(index + 1); return isNumeric(num1) && isNumeric(num2); } } /** * 判断合并项内容是否合法 * * @param mergeInfo * 合并项 每一项的数据格式为0,1,0,2即把(0,1)和(0,2)合并 * @return true合法,false非法 */ private boolean isMergeInfo(String[] mergeInfo) { if (VerifyUtil.isNull1DArray(mergeInfo)) { return false; } else { for (String str : mergeInfo) { String[] temp = str.split(","); if (VerifyUtil.isNull1DArray(temp) || temp.length != 4) { return false; } else { for (String s : temp) { if (!isNumeric(s)) { return false; } } } } } return true; } public static void main(String[] args) { ExcelExportUtil ee = new ExcelExportUtil(); String[][] content = new String[][] { { "", "第一列", null, "第三列" }, { "第一行", "aa", "2.00", "22" }, { "第二行", "bb", "3.01", "32" }, { "第三行", "cc", "4.00", "41" } }; try { OutputStream os = new FileOutputStream("D:/test2.xls"); // ee.export(null,null, content,null, os); ee.export(null, null, content, new String[] { "0,1,0,2", "1,0,3,0" }, os, "0,1", "0"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }} |
VerifyUtil：
|
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
|
package com.excel;import java.io.OutputStream;import java.util.Map;import jxl.write.NumberFormat;import jxl.write.WritableSheet;public class VerifyUtil { public static boolean isNullObject(String[][] content, OutputStream os) { // TODO Auto-generated method stub if(content != null && content.length > 0 && os != null) { return false; } return true; } public static boolean isNull2DArray(String[][] content) { // TODO Auto-generated method stub if(content != null && content.length > 0) { return false; } return true; } public static boolean isNullObject(NumberFormat nf) { // TODO Auto-generated method stub if(nf != null) { return false; } return true; } public static boolean isNullObject(String sheetName) { if(sheetName != null && !"".equals(sheetName.trim())) { return false; } return true; } public static boolean isNullObject(Map<String, String[][]> content, OutputStream os) { // TODO Auto-generated method stub if(content != null && content.size() > 0 && os != null) { return false; } return true; } public static boolean isNull1DArray(String[] mergeInfo) { // TODO Auto-generated method stub if(mergeInfo != null && mergeInfo.length > 0) { return false; } return true; } public static boolean isNullObject(WritableSheet sheet) { // TODO Auto-generated method stub if(sheet != null) { return false; } return true; }} |
希望本文所述对大家java程序设计有所帮助。
原文链接：http://www.cnblogs.com/FCWORLD/p/3411851.html
