java 操作 Excel 最常用的就是JXL(java excel api)和POI，今先看下JXL吧。首先可以到http://www.andykhan.com/jexcelapi/download.html 下载最新的jxl.jar，里面有它的jar包和API，内有一个小例子，可以参考一下。
JXL 用起来挺简单的，不过相应的其功能也并不是十分强大，对于一般的简单的excel操作还可以，对于太复杂的还是有些吃力，基本的操作也就是以下几项内容。
首先，要创建一个可读写的工作簿（WritableWorkbook）：
1 WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\test.xls"));如果是想要修改一个已存在的excel工作簿，则需要先获得它的原始工作簿，再创建一个可读写的副本：
1 Workbook wb = Workbook.getWorkbook(new File("src\\test\\test.xls")); // 获得原始文档
2 WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\test_modified.xls"),wb); // 创建一个可读写的副本然后，取得我们要操作的sheet，并对其进行相应的操作，如改名、合并单元格、设置列宽、行高等:
1 WritableSheet sheet = workbook.getSheet(0);
2 sheet.setName("修改后"); // 给sheet页改名
3 workbook.removeSheet(2); // 移除多余的标签页
4 workbook.removeSheet(3);
5
6 sheet.mergeCells(0, 0, 4, 0); // 合并单元格
7 sheet.setRowView(0, 600); // 设置行的高度
8 sheet.setColumnView(0, 30); // 设置列的宽度
9 sheet.setColumnView(1, 20); // 设置列的宽度之后，就是对单元格的操作了，可以通过如下方法获取一个单元格，其中两个参数分别为列、行的位置，从0开始计数，如（2,3）就代表单元格C4：
1 WritableCell cell = sheet.getWritableCell(2,3);通过WritableFont、WritableCellFormat等对象可以设置单元格的字体、样式等外观：
1 WritableFont titleWf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
2 20,//WritableFont.DEFAULT_POINT_SIZE, // 字号
3 WritableFont.NO_BOLD, // 粗体
4 false, // 斜体
5 UnderlineStyle.NO_UNDERLINE, // 下划线
6 Colour.BLUE2, // 字体颜色
7 ScriptStyle.NORMAL_SCRIPT);
8 WritableCellFormat wcf = new WritableCellFormat(titleWf);
9 wcf.setBackground(Colour.GRAY_25);// 设置单元格的背景颜色
10 wcf.setAlignment(Alignment.CENTRE); // 设置对齐方式
11 wcf.setBorder(Border.ALL, BorderLineStyle.THICK); // 添加边框
12 cell.setCellFormat(wcf);在jxl中，有几种常用的数据类型，根据单元格内数据类型的不同，每个WritableCell可以根据其类型被转换为它的一个子类型，以便对不同类型的数据进行专门的处理，通常可以做这样的转换：
1 WritableCell fromCell = sheet0.getWritableCell(j, i);
2
3 if (fromCell instanceof jxl.write.Number) {
4 jxl.write.Number num = (jxl.write.Number) fromCell;
5 } else if (fromCell instanceof jxl.write.Boolean) {
6 jxl.write.Boolean bool = (jxl.write.Boolean) fromCell;
7 } else if (fromCell instanceof jxl.write.DateTime) {
8 jxl.write.DateTime dt = (jxl.write.DateTime) fromCell;
9 } else if(fromCell instanceof Label){
10 Label _label = (Label) fromCell;
11 }当然，操作完成之后一定不要忘了刷新（或者叫写入？）和关掉工作簿：
1 workbook.write();
2 workbook.close();下面是参考jxl api里那个例子写的，把我自己觉得常用的excel操作基本都包含了：
1 package test;
2
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;
10
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;
38
39 /**
40 *
41 * @author why
42 *
43 */
44 public class ExcelTest {
45
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); // 创建一个可读写的副本
55
56
57 /**
58 * 定义与设置Sheet
59 */
60 WritableSheet sheet = workbook.getSheet(0);
61 sheet.setName("修改后"); // 给sheet页改名
62 workbook.removeSheet(2); // 移除多余的标签页
63 workbook.removeSheet(3);
64
65 sheet.mergeCells(0, 0, 4, 0); // 合并单元格
66 sheet.setRowView(0, 600); // 设置行的高度
67 sheet.setColumnView(0, 30); // 设置列的宽度
68 sheet.setColumnView(1, 20); // 设置列的宽度
69
70 WritableCell cell = null;
71 WritableCellFormat wcf = null;
72 Label label = null;
73 WritableCellFeatures wcfeatures = null;
74
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);
89
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);
96
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);
103
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);
111
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);
120
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);
130
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 }
138
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);
147
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);
155
156
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);
162
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);
168
169 // 将B14改为默认数字表示
170 cell = sheet.getWritableCell(1,13);
171 cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);
172
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 }
180
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 }
188
189 // 将B19日期格式改为默认
190 cell = sheet.getWritableCell(1,18);
191 wcf = new WritableCellFormat(DateFormats.FORMAT9);
192 cell.setCellFormat(wcf);
193
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);
199
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 }
210
211
212 // 将B24文字添加链接http://www.baidu.com
213 WritableHyperlink link = new WritableHyperlink(1, 23, new URL("http://www.baidu.com"));
214 sheet.addHyperlink(link);
215
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 }
232
233
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);
239
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);
244
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);
251
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);
263
264 // 为A46添加注释。
265 // 此处比较麻烦，试了多次发现必须将cell强制类型转换、添加CellFeatures再修改注释才可用，不知有没有更好的办法。
266 cell = sheet.getWritableCell(0,45);
267 wcfeatures = new WritableCellFeatures();
268 wcfeatures.setComment("这个注释不会被显示，删了这行还不行,MD");
269 cell.setCellFeatures(wcfeatures);
270
271 label = (Label) cell;
272 // label.setCellFeatures(wcfeatures);// 直接这样写会报一个警告（“注释已存在”），但那个注释仍会被显示。
273 label.addCellFeatures();
274 label.getWritableCellFeatures().setComment("终于加上注释了，哈哈哈哈");
275
276
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 // }
290
291 workbook.write();
292 workbook.close();
293 wb.close();
294 }
295
296 }=============================================================================================
xl读取excel和写excel基本类似，只是WritableWorkbook换成了Workbook； WritableSheet换成了 Sheet ；Label 换成了Cell。
1 import java.io.File;
2 import java.io.IOException;
3 import jxl.Cell;
4 import jxl.Sheet;
5 import jxl.Workbook;
6 import jxl.read.biff.BiffException;
7 public class ReadExcel {
8 public static void main(String[] args) {
9 try {
10 //选取指定的excel
11 Workbook workbook = Workbook.getWorkbook(new File("F:\\test.xls"));
12 //选取制定的sheet
13 Sheet sheet = workbook.getSheet(0);
14 //选取指定的cell
15 //遍历循环得到所要的cell值
16 for(int j = 0 ;j<sheet.getRows() ; j++)
17 for(int i = 0 ;i<sheet.getColumns();i++){
18 Cell cell = sheet.getCell(i,j);
19 //获取该cell的值
20 String var1 = cell.getContents();
21 //打印输出该值
22 System.out.println(var1);
23 }
24 } catch (BiffException e) {
25 e.printStackTrace();
26 } catch (IOException e) {
27 e.printStackTrace();
28 }
29 }
30 }
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
