java把excel导入数据库中_Java将Excel表格导入数据库，将数据库中的数据导出到Excel表...

总结一下Java将.xls格式的Excel表格导入到Oracle11g数据库。

一、开发环境：

1、jdk "1.8.0_101"

2、Oracle11g

3、eclipse

4、jar包：jxl.jar、ojdbc5.jar

5、Excel表：

6、创建T_USER表：

CREATE TABLE

T_USER

(

id INTEGER PRIMARY KEY,--用户编号

username VARCHAR2(20),--用户名称

password VARCHAR2(15),--密码

email VARCHAR2(30),--邮箱

phone VARCHAR2(15),--电话

address VARCHAR2(50)--地址

);

二、Java代码实现将user.xls文件导入到数据库

1、新建工程toolsUtil，导入jar包

2、新建T_USER实体类：

1 packagecom.Tools.entity;2

3 public classT_USER {4

5 privateString id;6 privateString username;7 privateString password;8 privateString email;9 privateString phone;10 privateString address;11

12 publicT_USER() {13 super();14 }15

16 publicT_USER(String id, String username, String password, String email, String phone, String address) {17 super();18 this.id =id;19 this.username =username;20 this.password =password;21 this.email =email;22 this.phone =phone;23 this.address =address;24 }25

26 publicString getId() {27 returnid;28 }29

30 public voidsetId(String id) {31 this.id =id;32 }33

34 publicString getUsername() {35 returnusername;36 }37

38 public voidsetUsername(String username) {39 this.username =username;40 }41

42 publicString getPassword() {43 returnpassword;44 }45

46 public voidsetPassword(String password) {47 this.password =password;48 }49

50 publicString getEmail() {51 returnemail;52 }53

54 public voidsetEmail(String email) {55 this.email =email;56 }57

58 publicString getPhone() {59 returnphone;60 }61

62 public voidsetPhone(String phone) {63 this.phone =phone;64 }65

66 publicString getAddress() {67 returnaddress;68 }69

70 public voidsetAddress(String address) {71 this.address =address;72 }73

74 @Override75 public inthashCode() {76 final int prime = 31;77 int result = 1;78 result = prime * result + ((address == null) ? 0: address.hashCode());79 result = prime * result + ((email == null) ? 0: email.hashCode());80 result = prime * result + ((id == null) ? 0: id.hashCode());81 result = prime * result + ((password == null) ? 0: password.hashCode());82 result = prime * result + ((phone == null) ? 0: phone.hashCode());83 result = prime * result + ((username == null) ? 0: username.hashCode());84 returnresult;85 }86

87 @Override88 public booleanequals(Object obj) {89 if (this ==obj)90 return true;91 if (obj == null)92 return false;93 if (getClass() !=obj.getClass())94 return false;95 T_USER other =(T_USER) obj;96 if (address == null) {97 if (other.address != null)98 return false;99 } else if (!address.equals(other.address))100 return false;101 if (email == null) {102 if (other.email != null)103 return false;104 } else if (!email.equals(other.email))105 return false;106 if (id == null) {107 if (other.id != null)108 return false;109 } else if (!id.equals(other.id))110 return false;111 if (password == null) {112 if (other.password != null)113 return false;114 } else if (!password.equals(other.password))115 return false;116 if (phone == null) {117 if (other.phone != null)118 return false;119 } else if (!phone.equals(other.phone))120 return false;121 if (username == null) {122 if (other.username != null)123 return false;124 } else if (!username.equals(other.username))125 return false;126 return true;127 }128

129 @Override130 publicString toString() {131 return "T_USER [id=" + id + ", username=" + username + ", password=" + password + ", email=" +email132 + ", phone=" + phone + ", address=" + address + "]";133 }134

135 }

3、数据库连接

1 packagecom.Tools.util;2

3 importjava.io.IOException;4 importjava.sql.Connection;5 importjava.sql.DriverManager;6 importjava.sql.PreparedStatement;7 importjava.sql.ResultSet;8 importjava.sql.SQLException;9 importjava.util.Properties;10

11 public classJDBCConnectionUtil {12

13 //初始化当前线程

14 private static ThreadLocal tlc = new ThreadLocal();15

16 //数据库配置文件

17 private static Properties prop = newProperties();18

19 //加载数据库配置文件

20 static{21 try{22 prop.load(JDBCConnectionUtil.class.getResourceAsStream("/com/Tools/conf/jdbc.properties"));23 try{24 //获取数据库驱动

25 Class.forName(prop.getProperty("oracleDriver"));26 } catch(ClassNotFoundException e) {27 System.out.println("获取数据库驱动报错！");28 e.printStackTrace();29 }30 } catch(IOException e) {31 System.out.println("加载数据库配置文件报错！");32 e.printStackTrace();33 }34 }35

36 /**

37 * 获取数据库连接38 *39 *@return

40 */

41 public staticConnection getJdbcConnection() {42

43 String url = prop.getProperty("url");44 String username = prop.getProperty("username");45 String password = prop.getProperty("password");46

47 Connection conn = null;48 try{49 //创建数据库连接

50 conn =DriverManager.getConnection(url, username, password);51 //将数据库连接放入当前线程

52 tlc.set(conn);53 } catch(SQLException e) {54 System.out.println("获取数据库连接错误！");55 e.printStackTrace();56 }57

58 returnconn;59 }60

61 /**

62 * 释放数据库连接63 *64 *@paramrs65 *@paramps66 *@paramconn67 */

68 public static voidrealeaseConnnection(ResultSet rs, PreparedStatement ps, Connection conn) {69

70 if (rs != null) {71 try{72 rs.close();73 } catch(SQLException e) {74 e.printStackTrace();75 }76 }77

78 if (ps != null) {79 try{80 ps.close();81 } catch(SQLException e) {82 e.printStackTrace();83 }84 }85

86 if (conn != null) {87 try{88 conn.close();89 tlc.remove();90 } catch(SQLException e) {91 e.printStackTrace();92 }93 }94

95 }96

97 }

4、主方法

1 packagecom.Tools.test;2

3 importjava.sql.Connection;4 importjava.sql.PreparedStatement;5 importjava.sql.ResultSet;6 importjava.sql.SQLException;7 importjava.util.ArrayList;8 importjava.util.List;9

10 importcom.Tools.entity.T_USER;11 importcom.Tools.service.IExcelToDBService;12 importcom.Tools.service.impl.ExcelToDBServiceImpl;13 importcom.Tools.util.JDBCConnectionUtil;14

15 public classExcelAndDB {16 public static voidmain(String[] args) {17 /**

18 * 将Excel导入数据库19 */

20 excelToDB();21 }22

23 /**

24 * 将Excel导入数据库25 */

26 public static voidexcelToDB() {27

28 Connection conn = null;29 PreparedStatement ps = null;30 ResultSet rs = null;31 List list = new ArrayList();32

33 //读取Excel表格中的数据

34 IExcelToDBService excelToDBService = newExcelToDBServiceImpl();35 list = excelToDBService.getExcelContentsByT_USER("D:/test");36

37 //获取数据库连接

38 conn =JDBCConnectionUtil.getJdbcConnection();39 //准备执行SQL

40 String sql = "insert into t_user values(?,?,?,?,?,?)";41 //将自动提交设置为false

42 try{43 conn.setAutoCommit(false);44 ps =conn.prepareStatement(sql);45 if (list != null && list.size() > 0) {46 for(T_USER user : list) {47 String id =user.getId();48 String username =user.getUsername();49 String password =user.getPassword();50 String email =user.getEmail();51 String phone =user.getPhone();52 String address =user.getAddress();53

54 ps.setString(1, id);55 ps.setString(2, username);56 ps.setString(3, password);57 ps.setString(4, email);58 ps.setString(5, phone);59 ps.setString(6, address);60

61 ps.executeUpdate();62

63 }64 }65 conn.commit();66 } catch(SQLException e) {67 e.printStackTrace();68 } finally{69 JDBCConnectionUtil.realeaseConnnection(rs, ps, conn);70 }71

72 }73

74 }

5、调用service方法

1 packagecom.Tools.service.impl;2

3 importjava.io.File;4 importjava.util.ArrayList;5 importjava.util.List;6

7 importcom.Tools.entity.T_USER;8 importcom.Tools.service.IExcelToDBService;9

10 importjxl.Sheet;11 importjxl.Workbook;12

13 public class ExcelToDBServiceImpl implementsIExcelToDBService {14

15 /**

16 * 获得Excel表格中的数据17 */

18 @Override19 public ListgetExcelContentsByT_USER(String filePath) {20 List list = new ArrayList();21 //文件路径

22 String path = "";23 //读取该路径filePath下的所有.xls文件

24 File file = newFile(filePath);25 //获取该路径下.xls文件数组集合

26 File[] files =file.listFiles();27 if (files != null && files.length > 0) {28 //遍历文件

29 for (int i = 0; i < files.length; i++) {30 if (files[i].isFile()) {//判断是否是文件

31 path =files[i].getPath();32 //判断文件后缀是否是.xls

33 if (!"".equals(path) && path.endsWith(".xls")) {34 //获取Excel表格中的所有数据

35 try{36 //获取该文件下的所有数据

37 Workbook workbook = Workbook.getWorkbook(newFile(path));38 //Sheet sheetRs = workbook.getSheet("Sheet");//读取“Sheet”中的所有数据

39 Sheet sheetRs = workbook.getSheet(0);//读取Sheet1中的所有数据

40 int rows = sheetRs.getRows();//所有行

41 int cols = sheetRs.getColumns();//所有列

42 for (int j = 1; j < rows; j++) {43 for (int k = 0; k < cols; k++) {44 String id = sheetRs.getCell(k++, j).getContents();45 String username = sheetRs.getCell(k++, j).getContents();46 String password = sheetRs.getCell(k++, j).getContents();47 String email = sheetRs.getCell(k++, j).getContents();48 String phone = sheetRs.getCell(k++, j).getContents();49 String address = sheetRs.getCell(k++, j).getContents();50 T_USER user = newT_USER();51 user.setId(id);52 user.setUsername(username);53 user.setPassword(password);54 user.setEmail(email);55 user.setPhone(phone);56 user.setAddress(address);57 list.add(user);58 }59 }60 } catch(Exception e) {61 System.out.println("获取文件数据报错！");62 e.printStackTrace();63 }64 }65 }66 }67 }68

69 returnlist;70 }71

72 }

6、jdbc.properties

1 oracleDriver=oracle.jdbc.OracleDriver2 url=jdbc:oracle:thin:@127.0.0.1:1521:ORCL3 username=scott4 password=tiger

三、Java代码实现将T_USER中的数据导出到Excel表

1、主方法：

1 packagecom.Tools.test;2

3 importjava.io.File;4 importjava.io.IOException;5 importjava.sql.Connection;6 importjava.sql.PreparedStatement;7 importjava.sql.ResultSet;8 importjava.sql.SQLException;9 importjava.util.ArrayList;10 importjava.util.List;11

12 importcom.Tools.entity.T_USER;13 importcom.Tools.service.IDBToExcelService;14 importcom.Tools.service.IExcelToDBService;15 importcom.Tools.service.impl.DBToExcelServiceImpl;16 importcom.Tools.service.impl.ExcelToDBServiceImpl;17 importcom.Tools.util.JDBCConnectionUtil;18

19 importjxl.Workbook;20 importjxl.write.Label;21 importjxl.write.WritableSheet;22 importjxl.write.WritableWorkbook;23 importjxl.write.WriteException;24

25 public classExcelAndDB {26 public static voidmain(String[] args) {27 /**

28 * 将Excel导入数据库29 */

30 //excelToDB();

31

32 /**

33 * 将数据库中的数据导入到Excel34 */

35 DBToExcel();36 }37

38 /**

39 * 将Excel导入数据库40 */

41 public static voidexcelToDB() {42

43 Connection conn = null;44 PreparedStatement ps = null;45 ResultSet rs = null;46 List list = new ArrayList();47

48 //读取Excel表格中的数据

49 IExcelToDBService excelToDBService = newExcelToDBServiceImpl();50 list = excelToDBService.getExcelContentsByT_USER("D:/test");51

52 //获取数据库连接

53 conn =JDBCConnectionUtil.getJdbcConnection();54 //准备执行SQL

55 String sql = "insert into t_user values(?,?,?,?,?,?)";56 //将自动提交设置为false

57 try{58 conn.setAutoCommit(false);59 ps =conn.prepareStatement(sql);60 if (list != null && list.size() > 0) {61 for(T_USER user : list) {62 String id =user.getId();63 String username =user.getUsername();64 String password =user.getPassword();65 String email =user.getEmail();66 String phone =user.getPhone();67 String address =user.getAddress();68

69 ps.setString(1, id);70 ps.setString(2, username);71 ps.setString(3, password);72 ps.setString(4, email);73 ps.setString(5, phone);74 ps.setString(6, address);75

76 ps.executeUpdate();77

78 }79 }80 conn.commit();81 } catch(SQLException e) {82 e.printStackTrace();83 } finally{84 JDBCConnectionUtil.realeaseConnnection(rs, ps, conn);85 }86

87 }88

89 /**

90 * 将数据库中的数据导出到Excel表格91 */

92 public static voidDBToExcel() {93 WritableWorkbook wbb = null;94

95 //创建可写入的Excel工作簿

96 String fileName = "D://test//userNew.xls";97 File file = newFile(fileName);98 if (!file.exists()) {99 try{100 file.createNewFile();101 } catch(IOException e) {102 System.out.println("新建导出Excel工作簿错误！");103 e.printStackTrace();104 }105 }106

107 //以fileName为文件名来创建一个workbook

108 try{109 wbb =Workbook.createWorkbook(file);110 //创建工作表

111 WritableSheet wSheet = wbb.createSheet("用户信息", 0);112

113 //获得用户信息表T_USER所有数据

114 List list = new ArrayList();115 IDBToExcelService excelToDBService = newDBToExcelServiceImpl();116 list =excelToDBService.getAllUserInfo();117

118 //要插入到的Excel表格的行号，默认从0开始

119 Label labelID = new Label(0, 0, "用户编号");120 Label lableUserName = new Label(1, 0, "用户名");121 Label labelPassword = new Label(2, 0, "密码");122 Label labelEmail = new Label(3, 0, "邮箱");123 Label labelPhone = new Label(4, 0, "电话");124 Label labelAddress = new Label(5, 0, "地址");125

126 wSheet.addCell(labelID);127 wSheet.addCell(lableUserName);128 wSheet.addCell(labelPassword);129 wSheet.addCell(labelEmail);130 wSheet.addCell(labelPhone);131 wSheet.addCell(labelAddress);132

133 for (int i = 0; i < list.size(); i++) {134 Label labelID_i = new Label(0, i + 1, list.get(i).getId() + "");135 Label labelUsername_i = new Label(1, i + 1, list.get(i).getUsername() + "");136 Label labelPassword_i = new Label(2, i + 1, list.get(i).getPassword() + "");137 Label labelEmail_i = new Label(3, i + 1, list.get(i).getEmail() + "");138 Label labelPhone_i = new Label(4, i + 1, list.get(i).getPhone() + "");139 Label labelAddress_i = new Label(5, i + 1, list.get(i).getAddress() + "");140

141 wSheet.addCell(labelID_i);142 wSheet.addCell(labelUsername_i);143 wSheet.addCell(labelPassword_i);144 wSheet.addCell(labelEmail_i);145 wSheet.addCell(labelPhone_i);146 wSheet.addCell(labelAddress_i);147 }148

149 //写进文档

150 wbb.write();151 } catch(Exception e) {152 System.out.println("将数据库中的数据导入到Excel表格中发生错误！");153 e.printStackTrace();154 } finally{155 try{156 //关闭Excel工作簿对象

157 wbb.close();158 } catch(WriteException e) {159 e.printStackTrace();160 } catch(IOException e) {161 e.printStackTrace();162 }163 }164

165 }166

167 }

2、service层：

1 packagecom.Tools.service.impl;2

3 importjava.util.ArrayList;4 importjava.util.List;5

6 importcom.Tools.dao.IDBToExcelDao;7 importcom.Tools.dao.impl.DBToExcelDaoImpl;8 importcom.Tools.entity.T_USER;9 importcom.Tools.service.IDBToExcelService;10

11 public class DBToExcelServiceImpl implementsIDBToExcelService {12 /**

13 * 实现查询T_USER表中所有信息接口功能14 */

15 @Override16 public ListgetAllUserInfo() {17

18 List list = new ArrayList();19 IDBToExcelDao dbToExcelDao = newDBToExcelDaoImpl();20 list =dbToExcelDao.getAllUserInfo();21

22 returnlist;23 }24 }

3、dao层：

1 packagecom.Tools.dao.impl;2

3 importjava.sql.Connection;4 importjava.sql.PreparedStatement;5 importjava.sql.ResultSet;6 importjava.sql.SQLException;7 importjava.util.ArrayList;8 importjava.util.List;9

10 importcom.Tools.dao.IDBToExcelDao;11 importcom.Tools.entity.T_USER;12 importcom.Tools.util.JDBCConnectionUtil;13

14 public class DBToExcelDaoImpl implementsIDBToExcelDao {15

16 /**

17 * 实现查询T_USER表所有信息接口功能18 */

19 @Override20 public ListgetAllUserInfo() {21 Connection conn = null;22 PreparedStatement ps = null;23 ResultSet rs = null;24 List list = new ArrayList();25

26 //获取数据库连接

27 conn =JDBCConnectionUtil.getJdbcConnection();28 String sql = "select * from T_USER";29 try{30 ps =conn.prepareStatement(sql);31 rs =ps.executeQuery();32 while(rs.next()) {33 String id = rs.getString("id");34 String username = rs.getString("username");35 String password = rs.getString("password");36 String email = rs.getString("email");37 String phone = rs.getString("phone");38 String address = rs.getString("address");39

40 T_USER user = newT_USER();41 user.setId(id);42 user.setUsername(username);43 user.setPassword(password);44 user.setEmail(email);45 user.setPhone(phone);46 user.setAddress(address);47

48 list.add(user);49

50 }51 } catch(SQLException e) {52 System.out.println("查询T_USER错误！");53 e.printStackTrace();54 }55

56 returnlist;57 }58

59 }