java实现读写Excel数据

1.package com.mysql.odbc;   
2.import java.sql.Connection;   
3.import java.sql.DriverManager;   
4.import java.sql.PreparedStatement;   
5.import java.sql.ResultSet;   
6.import java.sql.SQLException;   
7.import java.sql.Statement;   
8.  
9.public class OdbcExcel {   
10.private final String DRIVERCLASSNAME = "sun.jdbc.odbc.JdbcOdbcDriver";   
11.private Connection con = null;   
12.// 注:链接字符串必须加上readonly=false属性，否则无法修改.更多选项看参照Access数据源配置的高级选项.   
13.// 更多链接字符串可在 http://www.connectionstrings.com 上查看.   
14.// Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;   
15.private String url = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=Test..xls;READONLY=FALSE";   
16.  
17.public OdbcExcel() throws Exception {   
18.   Class.forName(DRIVERCLASSNAME);   
19.   con = DriverManager.getConnection(url);   
20.  
21.}   
22.  
23.public int write() throws Exception {   
24.   Statement statement = con.createStatement();   
25.   // 创建一个工作簿(表),使用完后一定要关闭,否则会出错！   
26.   int x = statement.executeUpdate("create table 测试(编号 NUMBER,用户名 TEXT)");   
27.   statement.close();   
28.   return x;   
29.}   
30.  
31.public void read() throws SQLException {   
32.   Statement statement = con.createStatement();   
33.   /**  
34.   * 查询时表明应该用 [tablename$] 或[worksheetname$] 这是微软ODBC的保留字，否测会出现找不到引擎.  
35.   * Excel会把首行的值当成字段值.即列名. SQL syntax "SELECT * FROM [sheet1$]". I.e.  
36.   * excel worksheet name followed by a "$" and wrapped in "[" "]"  
37.   * brackets.  
38.   * */  
39.   ResultSet result = statement.executeQuery("SELECT * FROM [测试$]");   
40.   while (result.next()) {   
41.    System.out.println(result.getInt(1) + "\t" + result.getString(2));   
42.   }   
43.   result.close();   
44.}   
45.  
46.public int update() throws SQLException {   
47.   Statement statement = con.createStatement();   
48.   int x = statement.executeUpdate("insert into 测试 values(1,'孟德军')");   
49.   /**  
50.   * int x = statement.executeUpdate("insert into 测试 values(1,'孟德军')");  
51.   * String sql="alter table 测试 add 密码 TEXT";  
52.   * */  
53.   statement.close();   
54.   return x;   
55.}   
56.  
57.public void close() throws SQLException {   
58.   // 一定要关闭连接对象,否则文档会出错.   
59.  
60.   if (con != null) {   
61.    con.close();   
62.   }   
63.}   
64.  
65.public static void main(String[] args) throws Exception {   
66.   OdbcExcel excel = new OdbcExcel();   
67.   // 创建工作簿(表)   
68.   excel.write();   
69.   // 写入一条数据   
70.   excel.update();   
71.   // 读取写入的数据   
72.   excel.read();   
73.   // 关闭对象   
74.   excel.close();   
75.}   
76.  
77.}