![](http://static.oschina.net/uploads/img/201601/15103728_CnLj.gif)
![](http://static.oschina.net/uploads/img/201601/15103728_jnee.gif)
1 package com.excel;
2
3 import java.io.FileOutputStream;
4 import java.io.IOException;
5 import java.io.OutputStream;
6 import java.util.HashSet;
7 import java.util.Map;
8 import java.util.Set;
9 import java.util.regex.Pattern;
10
11 import jxl.Workbook;
12 import jxl.write.Label;
13 import jxl.write.Number;
14 import jxl.write.NumberFormat;
15 import jxl.write.WritableCellFormat;
16 import jxl.write.WritableSheet;
17 import jxl.write.WritableWorkbook;
18 import jxl.write.WriteException;
19 import jxl.write.biff.RowsExceededException;
20
21 /**
22 * 生成excel表格
23 *
24 * @author
25 *
26 */
27 public class ExcelExportUtil {
28 /**
29 * 构造器
30 *
31 */
32 public ExcelExportUtil() {
33
34 }
35
36 /**
37 * 生成具有一定格式excel
38 *
39 * @param sheetName
40 * sheet名称,默认为sheet1
41 * @param nf
42 * 数字类型的格式 如:jxl.write.NumberFormat nf = new
43 * jxl.write.NumberFormat("#.##");默认无格式
44 * @param content
45 * 二维数组,要生成excel的数据来源
46 * @param 合并项
47 * 每一项的数据格式为0,1,0,2 即:把(0,1)和(0,2)合并--->第1列的第一、二个元素合并
48 * @param os
49 * excel输出流
50 * @param row
51 * 需要水平居中的行,默认居左。以逗号分隔的字符串
52 * @param col
53 * 需要水平居中的列,默认居左。以逗号分隔的字符串
54 */
55 public void export(String sheetName, NumberFormat nf, String[][] content,
56 String[] mergeInfo, OutputStream os, String row, String col) {
57
58 if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
59 return;
60 }
61 // 默认名称
62 if (VerifyUtil.isNullObject(sheetName)) {
63 sheetName = "sheet1";
64 }
65 Set<Integer> rows = this.getInfo(row);
66 Set<Integer> cols = this.getInfo(col);
67 WritableWorkbook workbook = null;
68 try {
69 workbook = Workbook.createWorkbook(os);
70 WritableSheet sheet = workbook.createSheet(sheetName, 0);
71 for (int i = 0; i < content.length; i++) {
72 for (int j = 0; j < content[i].length; j++) {
73 if (content[i][j] == null) {
74 content[i][j] = "";
75 }
76 if (isNumber(content[i][j]) && !rows.contains(i)
77 && !cols.contains(j)) {// 处理数字
78 Number number = null;
79 if (VerifyUtil.isNullObject(nf)) {// 数字无格式
80 number = new Number(j, i,
81 Double.valueOf(content[i][j]));
82 } else {// 如果有格式,按格式生成
83 jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat(
84 nf);
85 number = new Number(j, i,
86 Double.valueOf(content[i][j]), wcfn);
87 }
88 sheet.addCell(number);
89 } else {// 处理非数字
90 WritableCellFormat format = new WritableCellFormat();
91 if (rows.contains(i) || cols.contains(j)) {
92 format.setAlignment(jxl.format.Alignment.CENTRE);
93 } else {
94 format.setAlignment(jxl.format.Alignment.LEFT);
95 }
96 format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
97 Label label = new Label(j, i, content[i][j], format);
98 sheet.addCell(label);
99 }
100 }
101 }
102 this.merge(sheet, mergeInfo);
103 workbook.write();
104 } catch (Exception e) {
105 e.printStackTrace();
106
107 } finally {
108 try {
109 workbook.close();
110 os.close();
111 } catch (WriteException e) {
112 // TODO Auto-generated catch block
113 e.printStackTrace();
114 } catch (IOException e) {
115 // TODO Auto-generated catch block
116 e.printStackTrace();
117 }
118 }
119 }
120
121 /**
122 * 生成固定格式的excel,表格都为文本,水平居左,垂直居中
123 *
124 * @param sheetName
125 * sheet名称,默认为sheet1
126 * @param content
127 * 二维数组,要生成excel的数据来源
128 * @param os
129 * excel输出流
130 */
131 public void exportFormatExcel(String[][] content, String sheetName,
132 OutputStream os) {
133 if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
134 return;
135 }
136 // 默认名称
137 if (VerifyUtil.isNullObject(sheetName)) {
138 sheetName = "sheet1";
139 }
140 WritableWorkbook workbook = null;
141 try {
142 workbook = Workbook.createWorkbook(os);
143 WritableSheet sheet = workbook.createSheet(sheetName, 0);
144
145 for (int i = 0; i < content.length; i++) {
146 for (int j = 0; j < content[i].length; j++) {
147 if (content[i][j] == null) {
148 content[i][j] = "";
149 }
150 WritableCellFormat format = new WritableCellFormat();
151 format.setAlignment(jxl.format.Alignment.LEFT);
152 format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
153 Label label = new Label(j, i, content[i][j], format);
154 sheet.addCell(label);
155 }
156 }
157
158 workbook.write();
159 } catch (Exception e) {
160 e.printStackTrace();
161
162 } finally {
163 try {
164 workbook.close();
165 } catch (WriteException e) {
166 // TODO Auto-generated catch block
167 e.printStackTrace();
168 } catch (IOException e) {
169 // TODO Auto-generated catch block
170 e.printStackTrace();
171 }
172 }
173 }
174
175 /**
176 * 生成固定格式的excel,表格都为文本,水平居左,垂直居中
177 *
178 * @param sheetName
179 * sheet名称,默认为sheet1
180 * @param content
181 * Map,要生成excel的数据来源
182 * @param os
183 * excel输出流
184 */
185 public void exportFormatExcel(Map<String, String[][]> content,
186 String[] salary_name_array, String sheetName, OutputStream os)
187 {
188 if (VerifyUtil.isNullObject(content, os) || content.size() == 0) {
189 return;
190 }
191 // 默认名称
192 if (VerifyUtil.isNullObject(sheetName)) {
193 sheetName = "sheet1";
194 }
195 WritableWorkbook workbook = null;
196 try {
197 workbook = Workbook.createWorkbook(os);
198 WritableSheet sheet = workbook.createSheet(sheetName, 0);
199 int index = 0;
200 for (int k = 0; k < salary_name_array.length; k++) {
201 String[][] value = (String[][]) content
202 .get(salary_name_array[k]);
203 if (value != null && value.length > 0) {
204 if (index != 0) {
205 index++;
206 }
207 WritableCellFormat format1 = new WritableCellFormat();
208 format1.setAlignment(jxl.format.Alignment.LEFT);
209 format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
210 Label label1 = new Label(0, index, salary_name_array[k],
211 format1);
212 sheet.addCell(label1);
213 for (int i = 0; i < value.length; i++) {
214 index++;
215 for (int j = 0; j < value[i].length; j++) {
216 if (value[i][j] == null) {
217 value[i][j] = "";
218 }
219 WritableCellFormat format = new WritableCellFormat();
220 format.setAlignment(jxl.format.Alignment.LEFT);
221 format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
222
223 Label label = new Label(j, index, value[i][j],
224 format);
225 sheet.addCell(label);
226 }
227 }
228 }
229 }
230 workbook.write();
231 } catch (Exception e) {
232 e.printStackTrace();
233 } finally {
234 try {
235 workbook.close();
236 } catch (WriteException e) {
237 // TODO Auto-generated catch block
238 e.printStackTrace();
239 } catch (IOException e) {
240 // TODO Auto-generated catch block
241 e.printStackTrace();
242 }
243 }
244 }
245
246 /**
247 * 合并表格
248 * @param sheet
249 * 工作表
250 * @param mergeInfo
251 * 要合并的表格的信息
252 * @throws RowsExceededException
253 * @throws NumberFormatException
254 * @throws WriteException
255 */
256 private void merge(WritableSheet sheet, String[] mergeInfo)
257 throws RowsExceededException, NumberFormatException, WriteException {
258 if (VerifyUtil.isNullObject(sheet) || VerifyUtil.isNull1DArray(mergeInfo)) {
259 return;
260 } else if (!this.isMergeInfo(mergeInfo)) {
261 return;
262 } else {
263 for (String str : mergeInfo) {
264 String[] temp = str.split(",");
265 sheet.mergeCells(Integer.parseInt(temp[1]),
266 Integer.parseInt(temp[0]), Integer.parseInt(temp[3]),
267 Integer.parseInt(temp[2]));
268 }
269 }
270 }
271
272 /**
273 * 处理要居中的行或列的数据
274 *
275 * @param indexes
276 * 行标或列标
277 * @return 行坐标或列坐标组成的集合
278 */
279 private Set<Integer> getInfo(String indexes) {
280 Set<Integer> set = new HashSet<Integer>();
281 if (VerifyUtil.isNullObject(indexes)) {
282 return set;
283 }
284 String[] temp = indexes.split(",", 0);
285 for (String str : temp) {
286 if (isNumeric(str)) {
287 set.add(Integer.parseInt(str));
288 }
289 }
290 return set;
291 }
292
293 /**
294 * 判断字符串是否由纯数字组成
295 *
296 * @param str
297 * 源字符串
298 * @return true是,false否
299 */
300 private boolean isNumeric(String str) {
301 if (VerifyUtil.isNullObject(str)) {
302 return false;
303 }
304 Pattern pattern = Pattern.compile("[0-9]*");
305 return pattern.matcher(str).matches();
306 }
307
308 /**
309 * 判断字符串是否是数字
310 *
311 * @param str
312 * 源字符串
313 * @return true是,false否
314 */
315 private boolean isNumber(String number) {
316 // 判断参数
317 if (VerifyUtil.isNullObject(number)) {
318 return false;
319 }
320 // 查看是否有小数点
321 int index = number.indexOf(".");
322 if (index < 0) {
323 return isNumeric(number);
324 } else {
325 // 如果有多个".",则不是数字
326 if (number.indexOf(".") != number.lastIndexOf(".")) {
327 return false;
328 }
329 String num1 = number.substring(0, index);
330 String num2 = number.substring(index + 1);
331 return isNumeric(num1) && isNumeric(num2);
332 }
333 }
334
335 /**
336 * 判断合并项内容是否合法
337 *
338 * @param mergeInfo
339 * 合并项 每一项的数据格式为0,1,0,2即把(0,1)和(0,2)合并
340 * @return true合法,false非法
341 */
342 private boolean isMergeInfo(String[] mergeInfo) {
343 if (VerifyUtil.isNull1DArray(mergeInfo)) {
344 return false;
345 } else {
346 for (String str : mergeInfo) {
347 String[] temp = str.split(",");
348 if (VerifyUtil.isNull1DArray(temp) || temp.length != 4) {
349 return false;
350 } else {
351 for (String s : temp) {
352 if (!isNumeric(s)) {
353 return false;
354 }
355 }
356 }
357 }
358 }
359 return true;
360 }
361
362 public static void main(String[] args) {
363 ExcelExportUtil ee = new ExcelExportUtil();
364 String[][] content = new String[][] { { "", "第一列", null, "第三列" },
365 { "第一行", "aa", "2.00", "22" }, { "第二行", "bb", "3.01", "32" },
366 { "第三行", "cc", "4.00", "41" } };
367 try {
368 OutputStream os = new FileOutputStream("D:/test2.xls");
369 // ee.export(null,null, content,null, os);
370 ee.export(null, null, content,
371 new String[] { "0,1,0,2", "1,0,3,0" }, os, "0,1", "0");
372 } catch (Exception e) {
373 // TODO Auto-generated catch block
374 e.printStackTrace();
375 }
376 }
377 }
![](http://static.oschina.net/uploads/img/201601/15103728_CnLj.gif)
![](http://static.oschina.net/uploads/img/201601/15103728_jnee.gif)
1 package com.excel;
2
3 import java.io.OutputStream;
4 import java.util.Map;
5
6 import jxl.write.NumberFormat;
7 import jxl.write.WritableSheet;
8
9 public class VerifyUtil {
10
11 public static boolean isNullObject(String[][] content, OutputStream os) {
12 // TODO Auto-generated method stub
13 if(content != null && content.length > 0 && os != null)
14 {
15 return false;
16 }
17 return true;
18 }
19
20 public static boolean isNull2DArray(String[][] content) {
21 // TODO Auto-generated method stub
22 if(content != null && content.length > 0)
23 {
24 return false;
25 }
26 return true;
27 }
28
29 public static boolean isNullObject(NumberFormat nf) {
30 // TODO Auto-generated method stub
31 if(nf != null)
32 {
33 return false;
34 }
35 return true;
36 }
37
38 public static boolean isNullObject(String sheetName) {
39 if(sheetName != null && !"".equals(sheetName.trim()))
40 {
41 return false;
42 }
43 return true;
44 }
45
46 public static boolean isNullObject(Map<String, String[][]> content,
47 OutputStream os) {
48 // TODO Auto-generated method stub
49 if(content != null && content.size() > 0 && os != null)
50 {
51 return false;
52 }
53 return true;
54 }
55
56 public static boolean isNull1DArray(String[] mergeInfo) {
57 // TODO Auto-generated method stub
58 if(mergeInfo != null && mergeInfo.length > 0)
59 {
60 return false;
61 }
62 return true;
63 }
64
65 public static boolean isNullObject(WritableSheet sheet) {
66 // TODO Auto-generated method stub
67 if(sheet != null)
68 {
69 return false;
70 }
71 return true;
72 }
73
74 }
本文旨在学习如何用Java导出Excel,具体细节还需要进一步完善。