package com.mysql.odbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class OdbcExcel {
private final String DRIVERCLASSNAME = "sun.jdbc.odbc.JdbcOdbcDriver";
private Connection con = null;
// 注:链接字符串必须加上readonly=false属性,否则无法修改.更多选项看参照Access数据源配置的高级选项.
// 更多链接字符串可在 http://www.connectionstrings.com 上查看.
// Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
private String url = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=Test..xls;READONLY=FALSE";
public OdbcExcel() throws Exception {
Class.forName(DRIVERCLASSNAME);
con = DriverManager.getConnection(url);
}
public int write() throws Exception {
Statement statement = con.createStatement();
// 创建一个工作簿(表),使用完后一定要关闭,否则会出错!
int x = statement.executeUpdate("create table 测试(编号 NUMBER,用户名 TEXT)");
statement.close();
return x;
}
public void read() throws SQLException {
Statement statement = con.createStatement();
/**
* 查询时表明应该用 [tablename$] 或[worksheetname$] 这是微软ODBC的保留字,否测会出现找不到引擎.
* Excel会把首行的值当成字段值.即列名. SQL syntax "SELECT * FROM [sheet1$]". I.e.
* excel worksheet name followed by a "$" and wrapped in "[" "]"
* brackets.
* */
ResultSet result = statement.executeQuery("SELECT * FROM [测试$]");
while (result.next()) {
System.out.println(result.getInt(1) + "\t" + result.getString(2));
}
result.close();
}
public int update() throws SQLException {
Statement statement = con.createStatement();
int x = statement.executeUpdate("insert into 测试 values(1,'孟德军')");
/**
* int x = statement.executeUpdate("insert into 测试 values(1,'孟德军')");
* String sql="alter table 测试 add 密码 TEXT";
* */
statement.close();
return x;
}
public void close() throws SQLException {
// 一定要关闭连接对象,否则文档会出错.
if (con != null) {
con.close();
}
}
public static void main(String[] args) throws Exception {
OdbcExcel excel = new OdbcExcel();
// 创建工作簿(表)
excel.write();
// 写入一条数据
excel.update();
// 读取写入的数据
excel.read();
// 关闭对象
excel.close();
}
}