Java操作excel!!
首先需要下载jxl.jar包
然后附上代码
代码
1 package test;
2
3 import java.io.FileInputStream;
4 import java.io.FileNotFoundException;
5 import java.io.IOException;
6 import java.io.InputStream;
7 import java.util.Date;
8
9 import jxl.Cell;
10 import jxl.CellType;
11 import jxl.DateCell;
12 import jxl.LabelCell;
13 import jxl.NumberCell;
14 import jxl.Sheet;
15 import jxl.Workbook;
16 import jxl.read.biff.BiffException;
17
18 public class Test2 {
19
20 /**
21 * @param args
22 */
23 public static void main(String[] args) {
24 try {
25 //构建workbook对象
26 jxl.Workbook rwb = null;
27 //从本地文件中读取文件
28 InputStream is = new FileInputStream("D:\\test.xls");
29 //从输入流中创建workbook
30 rwb = Workbook.getWorkbook(is);
31 //获取第一张Sheet表
32 Sheet rs = rwb.getSheet(0);
33 //获取第一行，第一列的值
34 Cell c00 = rs.getCell(0, 0);
35 String strc00 = c00.getContents();
36 //获取第一行，第二列的值
37 Cell c10 = rs.getCell(1, 0);
38 String strc10 = c10.getContents();
39 //获取第二行，第二列的值
40 Cell c11 = rs.getCell(1, 1);
41 String strc11 = c11.getContents();
42 System.out.println("Cell(0, 0)" + " value : " + strc00 + "; type : " + c00.getType());
43 System.out.println("Cell(1, 0)" + " value : " + strc10 + "; type : " + c10.getType());
44 System.out.println("Cell(1, 1)" + " value : " + strc11 + "; type : " + c11.getType());
45 String strc001 = null;
46 double strc101 = 0.00;
47 Date strc111 = null;
48 Cell c001 = rs.getCell(0, 0);
49 Cell c101 = rs.getCell(1, 0);
50 Cell c111 = rs.getCell(1, 1);
51 if(c001.getType() == CellType.LABEL)
52 {
53 LabelCell labelc00 = (LabelCell)c001;
54 strc001 = labelc00.getString();
55 }
56 if(c101.getType() == CellType.NUMBER)
57 {
58 NumberCell numc10 = (NumberCell)c101;
59 strc101 = numc10.getValue();
60 }
61 if(c111.getType() == CellType.DATE)
62 {
63 DateCell datec11 = (DateCell)c111;
64 strc111 = datec11.getDate();
65 }
66 System.out.println("Cell(0, 0)" + " value : " + strc001 + "; type : " + c001.getType());
67 System.out.println("Cell(1, 0)" + " value : " + strc101 + "; type : " + c101.getType());
68 System.out.println("Cell(1, 1)" + " value : " + strc111 + "; type : " + c111.getType());
69 rwb.close();
70
71 } catch (FileNotFoundException e) {
72 // TODO Auto-generated catch block
73 e.printStackTrace();
74 } catch (BiffException e) {
75 // TODO Auto-generated catch block
76 e.printStackTrace();
77 } catch (IndexOutOfBoundsException e) {
78 // TODO Auto-generated catch block
79 e.printStackTrace();
80 } catch (IOException e) {
81 // TODO Auto-generated catch block
82 e.printStackTrace();
83 }
84
85 }
86
87 }
88
2
3 import java.io.FileInputStream;
4 import java.io.FileNotFoundException;
5 import java.io.IOException;
6 import java.io.InputStream;
7 import java.util.Date;
8
9 import jxl.Cell;
10 import jxl.CellType;
11 import jxl.DateCell;
12 import jxl.LabelCell;
13 import jxl.NumberCell;
14 import jxl.Sheet;
15 import jxl.Workbook;
16 import jxl.read.biff.BiffException;
17
18 public class Test2 {
19
20 /**
21 * @param args
22 */
23 public static void main(String[] args) {
24 try {
25 //构建workbook对象
26 jxl.Workbook rwb = null;
27 //从本地文件中读取文件
28 InputStream is = new FileInputStream("D:\\test.xls");
29 //从输入流中创建workbook
30 rwb = Workbook.getWorkbook(is);
31 //获取第一张Sheet表
32 Sheet rs = rwb.getSheet(0);
33 //获取第一行，第一列的值
34 Cell c00 = rs.getCell(0, 0);
35 String strc00 = c00.getContents();
36 //获取第一行，第二列的值
37 Cell c10 = rs.getCell(1, 0);
38 String strc10 = c10.getContents();
39 //获取第二行，第二列的值
40 Cell c11 = rs.getCell(1, 1);
41 String strc11 = c11.getContents();
42 System.out.println("Cell(0, 0)" + " value : " + strc00 + "; type : " + c00.getType());
43 System.out.println("Cell(1, 0)" + " value : " + strc10 + "; type : " + c10.getType());
44 System.out.println("Cell(1, 1)" + " value : " + strc11 + "; type : " + c11.getType());
45 String strc001 = null;
46 double strc101 = 0.00;
47 Date strc111 = null;
48 Cell c001 = rs.getCell(0, 0);
49 Cell c101 = rs.getCell(1, 0);
50 Cell c111 = rs.getCell(1, 1);
51 if(c001.getType() == CellType.LABEL)
52 {
53 LabelCell labelc00 = (LabelCell)c001;
54 strc001 = labelc00.getString();
55 }
56 if(c101.getType() == CellType.NUMBER)
57 {
58 NumberCell numc10 = (NumberCell)c101;
59 strc101 = numc10.getValue();
60 }
61 if(c111.getType() == CellType.DATE)
62 {
63 DateCell datec11 = (DateCell)c111;
64 strc111 = datec11.getDate();
65 }
66 System.out.println("Cell(0, 0)" + " value : " + strc001 + "; type : " + c001.getType());
67 System.out.println("Cell(1, 0)" + " value : " + strc101 + "; type : " + c101.getType());
68 System.out.println("Cell(1, 1)" + " value : " + strc111 + "; type : " + c111.getType());
69 rwb.close();
70
71 } catch (FileNotFoundException e) {
72 // TODO Auto-generated catch block
73 e.printStackTrace();
74 } catch (BiffException e) {
75 // TODO Auto-generated catch block
76 e.printStackTrace();
77 } catch (IndexOutOfBoundsException e) {
78 // TODO Auto-generated catch block
79 e.printStackTrace();
80 } catch (IOException e) {
81 // TODO Auto-generated catch block
82 e.printStackTrace();
83 }
84
85 }
86
87 }
88
代码
package test;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.WritableFont;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class Test3 {
/**
* @param args
*/
public static void main(String[] args) {
try {
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(
"C:\\test.xls"));
jxl.write.WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);
//1.添加Label对象
jxl.write.Label labelC = new jxl.write.Label(0, 0, "This is a Label cell");
ws.addCell(labelC);
//添加带有字型Formatting的对象
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
jxl.write.Label labelCF = new jxl.write.Label(1, 0, "This is a Label Cell", wcfF);
ws.addCell(labelCF);
//添加带有字体颜色Formatting的对象
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
jxl.write.Label labelCFC = new jxl.write.Label(2, 0, "This is a Label Cell1W", wcfFC);
ws.addCell(labelCFC);
//2.添加Number对象
jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
ws.addCell(labelN);
//添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
ws.addCell(labelNF);
//3.添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
ws.addCell(labelB);
//4.添加DateTime对象
jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date());
ws.addCell(labelDT);
//添加带有formatting的DateFormat对象
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3, new java.util.Date(), wcfDF);
ws.addCell(labelDTF);
//写入Exel工作表
wwb.write();
//关闭Excel工作薄对象
wwb.close();
System.out.println("添加excel成功");
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.WritableFont;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class Test3 {
/**
* @param args
*/
public static void main(String[] args) {
try {
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(
"C:\\test.xls"));
jxl.write.WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);
//1.添加Label对象
jxl.write.Label labelC = new jxl.write.Label(0, 0, "This is a Label cell");
ws.addCell(labelC);
//添加带有字型Formatting的对象
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
jxl.write.Label labelCF = new jxl.write.Label(1, 0, "This is a Label Cell", wcfF);
ws.addCell(labelCF);
//添加带有字体颜色Formatting的对象
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
jxl.write.Label labelCFC = new jxl.write.Label(2, 0, "This is a Label Cell1W", wcfFC);
ws.addCell(labelCFC);
//2.添加Number对象
jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
ws.addCell(labelN);
//添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
ws.addCell(labelNF);
//3.添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
ws.addCell(labelB);
//4.添加DateTime对象
jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date());
ws.addCell(labelDT);
//添加带有formatting的DateFormat对象
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3, new java.util.Date(), wcfDF);
ws.addCell(labelDTF);
//写入Exel工作表
wwb.write();
//关闭Excel工作薄对象
wwb.close();
System.out.println("添加excel成功");
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
代码
1 package test;
2
3 import java.io.FileInputStream;
4 import java.io.FileNotFoundException;
5 import java.io.IOException;
6 import java.io.InputStream;
7
8 import jxl.Cell;
9 import jxl.Sheet;
10 import jxl.Workbook;
11 import jxl.read.biff.BiffException;
12
13 public class Test1 {
14 public static void main(String[] args) {
15 try {
16 //构建workbook对象
17 jxl.Workbook rwb = null;
18 //从本地文件中读取文件
19 InputStream is = new FileInputStream("D:\\test.xls");
20 //从输入流中创建workbook
21 rwb = Workbook.getWorkbook(is);
22 //获取第一张Sheet表
23 Sheet rs = rwb.getSheet(0);
24 //获取表的总列数和总行数
25 int rsColumns = rs.getColumns();
26 int rsRows = rs.getRows();
27 //获取指定的单元格的对象引用
28 for (int i = 0; i < rsRows; i++) {
29 for (int j = 0; j < rsColumns; j++) {
30 Cell cell = rs.getCell(j, i);
31 System.out.print(cell.getContents() + " ");
32 }
33 System.out.println();
34 }
35 rwb.close();
36
37 } catch (FileNotFoundException e) {
38 // TODO Auto-generated catch block
39 e.printStackTrace();
40 } catch (BiffException e) {
41 // TODO Auto-generated catch block
42 e.printStackTrace();
43 } catch (IndexOutOfBoundsException e) {
44 // TODO Auto-generated catch block
45 e.printStackTrace();
46 } catch (IOException e) {
47 // TODO Auto-generated catch block
48 e.printStackTrace();
49 }
50
51 }
52
53 }
54
2
3 import java.io.FileInputStream;
4 import java.io.FileNotFoundException;
5 import java.io.IOException;
6 import java.io.InputStream;
7
8 import jxl.Cell;
9 import jxl.Sheet;
10 import jxl.Workbook;
11 import jxl.read.biff.BiffException;
12
13 public class Test1 {
14 public static void main(String[] args) {
15 try {
16 //构建workbook对象
17 jxl.Workbook rwb = null;
18 //从本地文件中读取文件
19 InputStream is = new FileInputStream("D:\\test.xls");
20 //从输入流中创建workbook
21 rwb = Workbook.getWorkbook(is);
22 //获取第一张Sheet表
23 Sheet rs = rwb.getSheet(0);
24 //获取表的总列数和总行数
25 int rsColumns = rs.getColumns();
26 int rsRows = rs.getRows();
27 //获取指定的单元格的对象引用
28 for (int i = 0; i < rsRows; i++) {
29 for (int j = 0; j < rsColumns; j++) {
30 Cell cell = rs.getCell(j, i);
31 System.out.print(cell.getContents() + " ");
32 }
33 System.out.println();
34 }
35 rwb.close();
36
37 } catch (FileNotFoundException e) {
38 // TODO Auto-generated catch block
39 e.printStackTrace();
40 } catch (BiffException e) {
41 // TODO Auto-generated catch block
42 e.printStackTrace();
43 } catch (IndexOutOfBoundsException e) {
44 // TODO Auto-generated catch block
45 e.printStackTrace();
46 } catch (IOException e) {
47 // TODO Auto-generated catch block
48 e.printStackTrace();
49 }
50
51 }
52
53 }
54
代码
package test;
import java.io.File;
import java.io.IOException;
import jxl.CellType;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WriteException;
public class Test4 {
/**
* @param args
*/
public static void main(String[] args) {
try {
// 创建只读的Excel工作薄的对象
jxl.Workbook rw = jxl.Workbook
.getWorkbook(new File("D:\\test.xls"));
// 创建可写入的Excel工作薄对象
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(
"C:\\22.xls"), rw);
// 读取第一张工作表
jxl.write.WritableSheet ws = wwb.getSheet(0);
// 获得第一个单元格对象
jxl.write.WritableCell wc = ws.getWritableCell(0, 0);
// 判断单元格的类型, 做出相应的转化
if (wc.getType() == CellType.LABEL) {
Label l = (Label) wc;
l.setString("The value has been modified.");
}
// 写入Excel对象
wwb.write();
// 关闭可写入的Excel对象
wwb.close();
// 关闭只读的Excel对象
rw.close();
System.out.println("复制成功");
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IndexOutOfBoundsException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
import java.io.File;
import java.io.IOException;
import jxl.CellType;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WriteException;
public class Test4 {
/**
* @param args
*/
public static void main(String[] args) {
try {
// 创建只读的Excel工作薄的对象
jxl.Workbook rw = jxl.Workbook
.getWorkbook(new File("D:\\test.xls"));
// 创建可写入的Excel工作薄对象
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(
"C:\\22.xls"), rw);
// 读取第一张工作表
jxl.write.WritableSheet ws = wwb.getSheet(0);
// 获得第一个单元格对象
jxl.write.WritableCell wc = ws.getWritableCell(0, 0);
// 判断单元格的类型, 做出相应的转化
if (wc.getType() == CellType.LABEL) {
Label l = (Label) wc;
l.setString("The value has been modified.");
}
// 写入Excel对象
wwb.write();
// 关闭可写入的Excel对象
wwb.close();
// 关闭只读的Excel对象
rw.close();
System.out.println("复制成功");
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IndexOutOfBoundsException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
就这样操作excel的基本应用就有了。非常希望和各位交流。
