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.}
转载于:https://my.oschina.net/mengdejun/blog/9436