利用JXL(JExcelApi) 操作Excel文档
2010-03-12 09:53:16| 分类：
java_5
| 标签：
|举报
|字号大中小 订阅
http://coderdream.javaeye.com/blog/250628
1、工具类：
Java代码
1. package com.coderdream.jxl;
2.
3. import java.io.File;
4. import java.io.IOException;
5.
6. import jxl.Cell;
7. import jxl.Sheet;
8. import jxl.Workbook;
9. import jxl.format.Alignment;
10. import jxl.format.Border;
11. import jxl.format.BorderLineStyle;
12. import jxl.format.Colour;
13. import jxl.format.VerticalAlignment;
14. import jxl.read.biff.BiffException;
15. import jxl.write.Label;
16. import jxl.write.WritableCellFormat;
17. import jxl.write.WritableFont;
18. import jxl.write.WritableSheet;
19. import jxl.write.WritableWorkbook;
20. import jxl.write.WriteException;
21.
22. public class JxlUtil {
23.
24. /**
25. * @param fileName
26. * @throws IOException
27. */
28. public static void createExcel(String filename) {
29. File f = new File(filename);
30.
31. WritableWorkbook wwb = null;
32. // 创建Excel工作表
33. WritableSheet ws = null;
34.
35. try {
36. f.createNewFile();
37. wwb = Workbook.createWorkbook(f);
38.
39. ws = wwb.createSheet("Sheet1", 0);// 创建sheet
40. ws.addCell(new Label(0, 8, "ABCD"));
41.
42. // 输出流
43. wwb.write();
44.
45. // 关闭流
46. wwb.close();
47. } catch (WriteException e) {
48. e.printStackTrace();
49. } catch (IOException e) {
50. e.printStackTrace();
51. }
52. }
53.
54. /**
55. * @param fileName
56. * @throws IOException
57. */
58. public static void writeExcel(String filename) {
59. writeExcel(new File(filename));
60. }
61.
62. /**
63. * @param fileName
64. * @throws IOException
65. */
66. public static void writeExcel(File file) {
67.
68. WritableWorkbook wwb = null;
69. // 创建Excel工作表
70. WritableSheet sheet1 = null;
71. Workbook wb = null;
72. try {
73. // Excel获得文件
74. wb = Workbook.getWorkbook(file);
75.
76. // 打开一个文件的副本，并且指定数据写回到原文件
77. wwb = Workbook.createWorkbook(file, wb);
78.
79. // 读取第一张工作表
80. sheet1 = wwb.getSheet(0);
81.
82. Label l = new Label(0, 0, "姓名");// 第1行
83. sheet1.addCell(l);
84. l = new Label(1, 0, "电话");
85. sheet1.addCell(l);
86. l = new Label(2, 0, "地址");
87. sheet1.addCell(l);
88. l = new Label(0, 1, "小祝");// 第2行
89. sheet1.addCell(l);
90. l = new Label(1, 1, "1314***0974");
91. sheet1.addCell(l);
92. l = new Label(2, 1, "武汉武昌");
93. sheet1.addCell(l);
94.
95. // 添加一个工作表
96. WritableSheet sheet2 = wwb.createSheet("第二页", 1);
97. sheet2.addCell(new Label(0, 0, "第二页的测试数据"));
98.
99. // 输出流
100. wwb.write();
101.
102. // 关闭流
103. wwb.close();
104. } catch (WriteException e) {
105. e.printStackTrace();
106. } catch (IOException e) {
107. e.printStackTrace();
108. } catch (BiffException e) {
109. e.printStackTrace();
110. }
111. }
112.
113. /**
114. * @param fileName
115. * @throws IOException
116. */
117. public static void writeExcelWithFormat(String filename) {
118. writeExcelWithFormat(new File(filename));
119. }
120.
121. /**
122. * 增 加带格式的内容
123. *
124. * @param fileName
125. * @throws IOException
126. */
127. public static void writeExcelWithFormat(File file) {
128.
129. WritableWorkbook wwb = null;
130. // 创建Excel工作表
131. WritableSheet sheet1 = null;
132. Workbook wb = null;
133. try {
134. // Excel获得文件
135. wb = Workbook.getWorkbook(file);
136.
137. // 打开一个文件的副本，并且指定数据写回到原文件
138. // 原文件中某个Cell(单元格)的内容如果没有被修改，则会保留。
139. wwb = Workbook.createWorkbook(file, wb);
140.
141. // 读取第一张工作表
142. sheet1 = wwb.getSheet(0);
143. // 合并单元格(左列，左行，右列，右行)从第1行第1列到第1行第3列
144. sheet1.mergeCells(0, 0, 2, 0);
145. Label header = new Label(0, 0, "通讯录", getHeader());
146. sheet1.addCell(header);// 写入头
147.
148. Label l = new Label(0, 1, "姓名", getTitle());// 第1行
149. sheet1.addCell(l);
150. l = new Label(1, 1, "电话", getTitle());
151. sheet1.addCell(l);
152. l = new Label(2, 1, "地址", getTitle());
153. sheet1.addCell(l);
154. l = new Label(0, 2, "小祝", getNormolCell());// 第2行
155. sheet1.addCell(l);
156. l = new Label(1, 2, "1314***0974", getNormolCell());
157. sheet1.addCell(l);
158. l = new Label(2, 2, "武汉武昌", getNormolCell());
159. sheet1.addCell(l);
160.
161. sheet1.setColumnView(0, 20);// 设置列宽
162. sheet1.setColumnView(1, 20);
163. sheet1.setColumnView(2, 40);
164. sheet1.setRowView(0, 800);// 设置行高
165. sheet1.setRowView(1, 500);
166. sheet1.setRowView(2, 500);
167.
168. // 添加一个工作表
169. WritableSheet sheet2 = wwb.createSheet("第二页", 1);
170. sheet2.addCell(new Label(0, 0, "第二页的测试数据"));
171.
172. // 输出流
173. wwb.write();
174.
175. // 关闭流
176. wwb.close();
177. } catch (WriteException e) {
178. e.printStackTrace();
179. } catch (IOException e) {
180. e.printStackTrace();
181. } catch (BiffException e) {
182. e.printStackTrace();
183. }
184. }
185.
186. /**
187. * 设 置头的样式
188. *
189. * @return
190. */
191. public static WritableCellFormat getHeader() {
192. WritableFont font = new WritableFont(WritableFont.TIMES, 24,
193. WritableFont.BOLD);// 定义字体
194. WritableCellFormat format = null;
195. try {
196. font.setColour(Colour.BLUE);// 蓝色字体
197.
198. format = new WritableCellFormat(font);
199.
200. format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
201. format.setVerticalAlignment(VerticalAlignment.CENTRE);// 上下居中
202. format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
203. format.setBackground(Colour.YELLOW);// 黄色背景
204. } catch (WriteException e1) {
205. e1.printStackTrace();
206. }
207. return format;
208. }
209.
210. /**
211. * 设 置标题样式
212. *
213. * @return
214. */
215. public static WritableCellFormat getTitle() {
216. WritableFont font = new WritableFont(WritableFont.TIMES, 14);
217. WritableCellFormat format = null;
218. try {
219. font.setColour(Colour.BLUE);// 蓝色字体
220.
221. format = new WritableCellFormat(font);
222.
223. format.setAlignment(Alignment.CENTRE);
224. format.setVerticalAlignment(VerticalAlignment.CENTRE);
225. format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
226. } catch (WriteException e) {
227. e.printStackTrace();
228. }
229. return format;
230. }
231.
232. /**
233. * 设 置其他单元格样式
234. *
235. * @return
236. */
237. public static WritableCellFormat getNormolCell() {
238. // 12号字体,上下左右居中,带黑色边框
239. WritableFont font = new WritableFont(WritableFont.TIMES, 12);
240. WritableCellFormat format = new WritableCellFormat(font);
241. try {
242. format.setAlignment(jxl.format.Alignment.CENTRE);
243. format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
244. format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
245. } catch (WriteException e) {
246. e.printStackTrace();
247. }
248. return format;
249. }
250.
251. public static void readExcel(String filename) {
252. readExcel(new File(filename));
253. }
254.
255. public static void readExcel(File filename) {
256.
257. Workbook wb = null;
258. try {
259. wb = Workbook.getWorkbook(filename);
260. Sheet s = wb.getSheet(0);// 第1个sheet
261. Cell c = null;
262. int row = s.getRows();// 总行数
263. int col = s.getColumns();// 总列数
264. for (int i = 0; i < row; i++) {
265. for (int j = 0; j < col; j++) {
266. c = s.getCell(j, i);
267. System.out.print(c.getContents() + " ");
268. }
269. System.out.println();
270. }
271.
272. } catch (IOException e) {
273. e.printStackTrace();
274. } catch (BiffException e) {
275. e.printStackTrace();
276. }
277. }
278. }
package com.coderdream.jxl;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class JxlUtil {
/**
* @param fileName
* @throws IOException
*/
public static void createExcel(String filename) {
File f = new File(filename);
WritableWorkbook wwb = null;
// 创建Excel工作表
WritableSheet ws = null;
try {
f.createNewFile();
wwb = Workbook.createWorkbook(f);
ws = wwb.createSheet("Sheet1", 0);// 创建sheet
ws.addCell(new Label(0, 8, "ABCD"));
// 输出流
wwb.write();
// 关闭流
wwb.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* @param fileName
* @throws IOException
*/
public static void writeExcel(String filename) {
writeExcel(new File(filename));
}
/**
* @param fileName
* @throws IOException
*/
public static void writeExcel(File file) {
WritableWorkbook wwb = null;
// 创建Excel工作表
WritableSheet sheet1 = null;
Workbook wb = null;
try {
// Excel获得文件
wb = Workbook.getWorkbook(file);
// 打开一个文件的副本，并且指定数据写回到原文件
wwb = Workbook.createWorkbook(file, wb);
// 读取第一张工作表
sheet1 = wwb.getSheet(0);
Label l = new Label(0, 0, "姓名");// 第1行
sheet1.addCell(l);
l = new Label(1, 0, "电话");
sheet1.addCell(l);
l = new Label(2, 0, "地址");
sheet1.addCell(l);
l = new Label(0, 1, "小祝");// 第2行
sheet1.addCell(l);
l = new Label(1, 1, "1314***0974");
sheet1.addCell(l);
l = new Label(2, 1, "武汉武昌");
sheet1.addCell(l);
// 添加一个工作表
WritableSheet sheet2 = wwb.createSheet("第二页", 1);
sheet2.addCell(new Label(0, 0, "第二页的测试数据"));
// 输出流
wwb.write();
// 关闭流
wwb.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
}
/**
* @param fileName
* @throws IOException
*/
public static void writeExcelWithFormat(String filename) {
writeExcelWithFormat(new File(filename));
}
/**
* 增加带格式的内容
*
* @param fileName
* @throws IOException
*/
public static void writeExcelWithFormat(File file) {
WritableWorkbook wwb = null;
// 创建Excel工作表
WritableSheet sheet1 = null;
Workbook wb = null;
try {
// Excel获得文件
wb = Workbook.getWorkbook(file);
// 打开一个文件的副本，并且指定数据写回到原文件
// 原文件中某个Cell(单元格)的内容如果没有被修改，则会保留。
wwb = Workbook.createWorkbook(file, wb);
// 读取第一张工作表
sheet1 = wwb.getSheet(0);
// 合并单元格(左列，左行，右列，右行)从第1行第1列到第1行第3列
sheet1.mergeCells(0, 0, 2, 0);
Label header = new Label(0, 0, "通讯录", getHeader());
sheet1.addCell(header);// 写入头
Label l = new Label(0, 1, "姓名", getTitle());// 第1行
sheet1.addCell(l);
l = new Label(1, 1, "电话", getTitle());
sheet1.addCell(l);
l = new Label(2, 1, "地址", getTitle());
sheet1.addCell(l);
l = new Label(0, 2, "小祝", getNormolCell());// 第2行
sheet1.addCell(l);
l = new Label(1, 2, "1314***0974", getNormolCell());
sheet1.addCell(l);
l = new Label(2, 2, "武汉武昌", getNormolCell());
sheet1.addCell(l);
sheet1.setColumnView(0, 20);// 设置列宽
sheet1.setColumnView(1, 20);
sheet1.setColumnView(2, 40);
sheet1.setRowView(0, 800);// 设置行高
sheet1.setRowView(1, 500);
sheet1.setRowView(2, 500);
// 添加一个工作表
WritableSheet sheet2 = wwb.createSheet("第二页", 1);
sheet2.addCell(new Label(0, 0, "第二页的测试数据"));
// 输出流
wwb.write();
// 关闭流
wwb.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
}
/**
* 设置头的样式
*
* @return
*/
public static WritableCellFormat getHeader() {
WritableFont font = new WritableFont(WritableFont.TIMES, 24,
WritableFont.BOLD);// 定义字体
WritableCellFormat format = null;
try {
font.setColour(Colour.BLUE);// 蓝色字体
format = new WritableCellFormat(font);
format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
format.setVerticalAlignment(VerticalAlignment.CENTRE);// 上下居中
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
format.setBackground(Colour.YELLOW);// 黄色背景
} catch (WriteException e1) {
e1.printStackTrace();
}
return format;
}
/**
* 设置标题样式
*
* @return
*/
public static WritableCellFormat getTitle() {
WritableFont font = new WritableFont(WritableFont.TIMES, 14);
WritableCellFormat format = null;
try {
font.setColour(Colour.BLUE);// 蓝色字体
format = new WritableCellFormat(font);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
} catch (WriteException e) {
e.printStackTrace();
}
return format;
}
/**
* 设置其他单元格样式
*
* @return
*/
public static WritableCellFormat getNormolCell() {
// 12号字体,上下左右居中,带黑色边框
WritableFont font = new WritableFont(WritableFont.TIMES, 12);
WritableCellFormat format = new WritableCellFormat(font);
try {
format.setAlignment(jxl.format.Alignment.CENTRE);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
} catch (WriteException e) {
e.printStackTrace();
}
return format;
}
public static void readExcel(String filename) {
readExcel(new File(filename));
}
public static void readExcel(File filename) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(filename);
Sheet s = wb.getSheet(0);// 第1个sheet
Cell c = null;
int row = s.getRows();// 总行数
int col = s.getColumns();// 总列数
for (int i = 0; i < row; i++) {
for (int j = 0; j < col; j++) {
c = s.getCell(j, i);
System.out.print(c.getContents() + " ");
}
System.out.println();
}
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
}
}
2、 测试代码：
Java代码
1. public class JxlTest {
2.
3. /**
4. * @param args
5. */
6. public static void main(String[] args) {
7. String filename = "D:/newExcel.xls";
8.
9. String filename2 = "D:/newExcel2.xls";
10. JxlUtil.createExcel(filename);
11. JxlUtil.writeExcel(filename);
12.
13. JxlUtil.createExcel(filename2);
14. JxlUtil.writeExcelWithFormat(filename2);
15. JxlUtil.readExcel(filename2);
16. }
17.
18. }
public class JxlTest {
/**
* @param args
*/
public static void main(String[] args) {
String filename = "D:/newExcel.xls";
String filename2 = "D:/newExcel2.xls";
JxlUtil.createExcel(filename);
JxlUtil.writeExcel(filename);
JxlUtil.createExcel(filename2);
JxlUtil.writeExcelWithFormat(filename2);
JxlUtil.readExcel(filename2);
}
}
输出结果：
Java代码
1. 通讯录
2. 姓名 电话 地址
3. 小祝 1314***0974 武 汉武昌
4.
5.
6.
7.
8.
9. ABCD
评论这张
转发至微博
转发至微博
评论