jxl最全使用方法-白红宇

jxl最全使用方法

阅读量：5893 次

发布时间：2019-06-19

本文共 7903 字，大约阅读时间需要 26 分钟。

1. package test;
3. import java.io.File;
4. import java.io.IOException;
5. import java.net.MalformedURLException;
6. import java.net.URL;
7. import java.util.ArrayList;
8. import java.util.Calendar;
9. import java.util.Date;
11. import jxl.CellType;
12. import jxl.Workbook;
13. import jxl.format.Alignment;
14. import jxl.format.Border;
15. import jxl.format.BorderLineStyle;
16. import jxl.format.Colour;
17. import jxl.format.ScriptStyle;
18. import jxl.format.UnderlineStyle;
19. import jxl.format.VerticalAlignment;
20. import jxl.read.biff.BiffException;
21. import jxl.write.Blank;
22. import jxl.write.DateFormat;
23. import jxl.write.DateFormats;
24. import jxl.write.DateTime;
25. import jxl.write.Formula;
26. import jxl.write.Label;
27. import jxl.write.Number;
28. import jxl.write.NumberFormat;
29. import jxl.write.WritableCell;
30. import jxl.write.WritableCellFeatures;
31. import jxl.write.WritableCellFormat;
32. import jxl.write.WritableFont;
33. import jxl.write.WritableHyperlink;
34. import jxl.write.WritableImage;
35. import jxl.write.WritableSheet;
36. import jxl.write.WritableWorkbook;
37. import jxl.write.WriteException;
39. /**
40. *
41. * @author why
42. *
43. */
44. public class ExcelTest {
46. /**
47. * @param args
48. * @throws IOException
49. * @throws BiffException
50. * @throws WriteException
51. */
52. public static void main(String[] args) throws IOException, BiffException, WriteException {
53. Workbook wb = Workbook.getWorkbook(new File("src\\test\\test.xls")); // 获得原始文档
54. WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\test_modified.xls"),wb); // 创建一个可读写的副本
57. /**
58. * 定义与设置Sheet
59. */
60. WritableSheet sheet = workbook.getSheet(0);
61. sheet.setName("修改后"); // 给sheet页改名
62. workbook.removeSheet(2); // 移除多余的标签页
63. workbook.removeSheet(3);
65. sheet.mergeCells(0, 0, 4, 0); // 合并单元格
66. sheet.setRowView(0, 600); // 设置行的高度
67. sheet.setColumnView(0, 30); // 设置列的宽度
68. sheet.setColumnView(1, 20); // 设置列的宽度
70. WritableCell cell = null;
71. WritableCellFormat wcf = null;
72. Label label = null;
73. WritableCellFeatures wcfeatures = null;
75. // 更改标题字体
76. cell = sheet.getWritableCell(0,0);
77. WritableFont titleWf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
78. 20,//WritableFont.DEFAULT_POINT_SIZE, // 字号
79. WritableFont.NO_BOLD, // 粗体
80. false, // 斜体
81. UnderlineStyle.NO_UNDERLINE, // 下划线
82. Colour.BLUE2, // 字体颜色
83. ScriptStyle.NORMAL_SCRIPT);
84. wcf = new WritableCellFormat(titleWf);
85. wcf.setBackground(Colour.GRAY_25);// 设置单元格的背景颜色
86. wcf.setAlignment(Alignment.CENTRE); // 设置对齐方式
87. wcf.setBorder(Border.ALL, BorderLineStyle.THICK); // 添加边框
88. cell.setCellFormat(wcf);
90. // 将B3的字体改为仿宋_GB2312
91. cell = sheet.getWritableCell(1,2);
92. WritableFont fs = new WritableFont(WritableFont.createFont("仿宋_GB2312"),
93. 11);
94. wcf = new WritableCellFormat(fs);
95. cell.setCellFormat(wcf);
97. // 将B4的字号改为20
98. cell = sheet.getWritableCell(1,3);
99. WritableFont size20 = new WritableFont(WritableFont.createFont("宋体"),
100. 20);
101. wcf = new WritableCellFormat(size20);
102. cell.setCellFormat(wcf);
104. // 将B5的字体改为加粗
105. cell = sheet.getWritableCell(1,4);
106. WritableFont bold = new WritableFont(WritableFont.createFont("宋体"),
107. 11,
108. WritableFont.BOLD);
109. wcf = new WritableCellFormat(bold);
110. cell.setCellFormat(wcf);
112. // 将B6的字体改为倾斜
113. cell = sheet.getWritableCell(1,5);
114. WritableFont italic = new WritableFont(WritableFont.createFont("宋体"),
115. 11,
116. WritableFont.NO_BOLD,
117. true);
118. wcf = new WritableCellFormat(italic);
119. cell.setCellFormat(wcf);
121. // 将B7字体加下划线
122. cell = sheet.getWritableCell(1,6);
123. WritableFont underline = new WritableFont(WritableFont.createFont("宋体"),
124. 11,
125. WritableFont.NO_BOLD,
126. false,
127. UnderlineStyle.SINGLE);
128. wcf = new WritableCellFormat(underline);
129. cell.setCellFormat(wcf);
131. // 将B8的文字改为“待修改文字-已修改”
132. cell = sheet.getWritableCell(1,7);
133. if (cell.getType() == CellType.LABEL)
134. {
135. Label lc = (Label) cell;
136. lc.setString(lc.getString() + " - 已修改");
137. }
139. // 将B9文字对齐方式改为垂直居中、右对齐
140. cell = sheet.getWritableCell(1,8);
141. WritableFont align = new WritableFont(WritableFont.createFont("宋体"),
142. 11);
143. wcf = new WritableCellFormat(align);
144. wcf.setAlignment(Alignment.RIGHT); // 设置为右对齐
145. wcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置为垂直居中
146. cell.setCellFormat(wcf);
148. // 将E3文字改为自动换行
149. cell = sheet.getWritableCell(4,2);
150. WritableFont justify = new WritableFont(WritableFont.createFont("宋体"),
151. 11);
152. wcf = new WritableCellFormat(justify);
153. wcf.setAlignment(Alignment.JUSTIFY);
154. cell.setCellFormat(wcf);
157. // 将B12的数字有效位数从5位改为7位
158. cell = sheet.getWritableCell(1,11);
159. NumberFormat sevendps = new NumberFormat("#.0000000");
160. wcf = new WritableCellFormat(sevendps);
161. cell.setCellFormat(wcf);
163. // 将B13改为4位科学计数法表示
164. cell = sheet.getWritableCell(1,12);
165. NumberFormat exp4 = new NumberFormat("0.####E0");
166. wcf = new WritableCellFormat(exp4);
167. cell.setCellFormat(wcf);
169. // 将B14改为默认数字表示
170. cell = sheet.getWritableCell(1,13);
171. cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);
173. // 将B15数字类型的值17改为22
174. cell = sheet.getWritableCell(1,14);
175. if (cell.getType() == CellType.NUMBER)
176. {
177. Number n = (Number) cell;
178. n.setValue(42);
179. }
181. // 将B16的值2.71进行加法运算2.71 + 0.1
182. cell = sheet.getWritableCell(1,15);
183. if (cell.getType() == CellType.NUMBER)
184. {
185. Number n = (Number) cell;
186. n.setValue(n.getValue() + 0.1);
187. }
189. // 将B19日期格式改为默认
190. cell = sheet.getWritableCell(1,18);
191. wcf = new WritableCellFormat(DateFormats.FORMAT9);
192. cell.setCellFormat(wcf);
194. // 将B20日期格式改为dd MMM yyyy HH:mm:ss
195. cell = sheet.getWritableCell(1,19);
196. DateFormat df = new DateFormat("dd MMM yyyy HH:mm:ss");
197. wcf = new WritableCellFormat(df);
198. cell.setCellFormat(wcf);
200. // 将B21的日期设置为 2011-6-1 11:18:50
201. cell = sheet.getWritableCell(1,20);
202. if (cell.getType() == CellType.DATE)
203. {
204. DateTime dt = (DateTime) cell;
205. Calendar cal = Calendar.getInstance();
206. cal.set(2011, 5, 1, 11, 18, 50);
207. Date d = cal.getTime();
208. dt.setDate(d);
209. }
212. // 将B24文字添加链接http://www.baidu.com
213. WritableHyperlink link = new WritableHyperlink(1, 23, new URL("http://www.baidu.com"));
214. sheet.addHyperlink(link);
216. // 更改URL链接
217. WritableHyperlink hyperlinks[] = sheet.getWritableHyperlinks();
218. for (int i = 0; i < hyperlinks.length; i++) {
219. WritableHyperlink wh = hyperlinks[i];
220. if (wh.getColumn() == 1 && wh.getRow() == 24) {
221. // 将B25文字链接取消
222. sheet.removeHyperlink(wh,true);//true：保留文字；false：删除文字
223. }else if(wh.getColumn() == 1 && wh.getRow() == 25){
224. try {
225. // 将B26链接更改为http://wuhongyu.javaeye.com
226. wh.setURL(new URL("http://wuhongyu.javaeye.com"));
227. } catch (MalformedURLException e) {
228. e.printStackTrace();
229. }
230. }
231. }
234. // 利用公式取得B29、B30的值
235. Formula f1 = new Formula(1, 28, "SUM(C29:D29)");
236. sheet.addCell(f1);
237. Formula f2 = new Formula(1, 29, "AVERAGE(C30:G30)");
238. sheet.addCell(f2);
240. // 在B32处添加图片，图片大小占10行3列，只支持png格式
241. File file = new File("d:\\shu05.png");
242. WritableImage image = new WritableImage(1, 31, 3, 10, file);
243. sheet.addImage(image);
245. // 在A44出添加内容"Added drop down validation"，并为其添加注释
246. label = new Label(0, 43, "Added drop down validation");
247. wcfeatures = new WritableCellFeatures();
248. wcfeatures.setComment("右边列是个下拉列表");
249. label.setCellFeatures(wcfeatures);
250. sheet.addCell(label);
252. // 在B44处添加一个下拉列表并添加注释
253. Blank b = new Blank(1, 43);
254. wcfeatures = new WritableCellFeatures();
255. ArrayList al = new ArrayList();
256. al.add("why");
257. al.add("landor");
258. al.add("tjm");
259. wcfeatures.setDataValidationList(al);
260. wcfeatures.setComment("这是一个注释");
261. b.setCellFeatures(wcfeatures);
262. sheet.addCell(b);
264. // 为A46添加注释。
265. // 此处比较麻烦，试了多次发现必须将cell强制类型转换、添加CellFeatures再修改注释才可用，不知有没有更好的办法。
266. cell = sheet.getWritableCell(0,45);
267. wcfeatures = new WritableCellFeatures();
268. wcfeatures.setComment("这个注释不会被显示，删了这行还不行,MD");
269. cell.setCellFeatures(wcfeatures);
271. label = (Label) cell;
272. // label.setCellFeatures(wcfeatures);// 直接这样写会报一个警告（“注释已存在”），但那个注释仍会被显示。
273. label.addCellFeatures();
274. label.getWritableCellFeatures().setComment("终于加上注释了，哈哈哈哈");
277. // if (cell instanceof Number) {
278. // Number num = (Number) cell;
279. // num.setCellFeatures(wcfeatures);
280. // } else if (cell instanceof jxl.write.Boolean) {
281. // jxl.write.Boolean bool = (jxl.write.Boolean) cell;
282. // bool.setCellFeatures(wcfeatures);
283. // } else if (cell instanceof jxl.write.DateTime) {
284. // jxl.write.DateTime dt = (jxl.write.DateTime) cell;
285. // dt.setCellFeatures(wcfeatures);
286. // } else {
287. // Label _label = (Label) cell;
288. // _label.setCellFeatures(wcfeatures);
289. // }
291. workbook.write();
292. workbook.close();
293. wb.close();
294. }
296. }