java数据excel_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.}