总结一下Java将.xls格式的Excel表格导入到Oracle11g数据库。
一、开发环境：
1、jdk版本：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 package com.Tools.entity;
2
3 public class T_USER {
4
5 private String id;
6 private String username;
7 private String password;
8 private String email;
9 private String phone;
10 private String address;
11
12 public T_USER() {
13 super();
14 }
15
16 public T_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 public String getId() {
27 return id;
28 }
29
30 public void setId(String id) {
31 this.id = id;
32 }
33
34 public String getUsername() {
35 return username;
36 }
37
38 public void setUsername(String username) {
39 this.username = username;
40 }
41
42 public String getPassword() {
43 return password;
44 }
45
46 public void setPassword(String password) {
47 this.password = password;
48 }
49
50 public String getEmail() {
51 return email;
52 }
53
54 public void setEmail(String email) {
55 this.email = email;
56 }
57
58 public String getPhone() {
59 return phone;
60 }
61
62 public void setPhone(String phone) {
63 this.phone = phone;
64 }
65
66 public String getAddress() {
67 return address;
68 }
69
70 public void setAddress(String address) {
71 this.address = address;
72 }
73
74 @Override
75 public int hashCode() {
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 return result;
85 }
86
87 @Override
88 public boolean equals(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 @Override
130 public String toString() {
131 return "T_USER [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email
132 + ", phone=" + phone + ", address=" + address + "]";
133 }
134
135 }3、创建数据库连接
1 package com.Tools.util;
2
3 import java.io.IOException;
4 import java.sql.Connection;
5 import java.sql.DriverManager;
6 import java.sql.PreparedStatement;
7 import java.sql.ResultSet;
8 import java.sql.SQLException;
9 import java.util.Properties;
10
11 public class JDBCConnectionUtil {
12
13 // 初始化当前线程
14 private static ThreadLocal<Connection> tlc = new ThreadLocal<Connection>();
15
16 // 数据库配置文件
17 private static Properties prop = new Properties();
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 static Connection 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 return conn;
59 }
60
61 /**
62 * 释放数据库连接
63 *
64 * @param rs
65 * @param ps
66 * @param conn
67 */
68 public static void realeaseConnnection(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 package com.Tools.test;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.ArrayList;
8 import java.util.List;
9
10 import com.Tools.entity.T_USER;
11 import com.Tools.service.IExcelToDBService;
12 import com.Tools.service.impl.ExcelToDBServiceImpl;
13 import com.Tools.util.JDBCConnectionUtil;
14
15 public class ExcelAndDB {
16 public static void main(String[] args) {
17 /**
18 * 将Excel导入数据库
19 */
20 excelToDB();
21 }
22
23 /**
24 * 将Excel导入数据库
25 */
26 public static void excelToDB() {
27
28 Connection conn = null;
29 PreparedStatement ps = null;
30 ResultSet rs = null;
31 List<T_USER> list = new ArrayList<T_USER>();
32
33 // 读取Excel表格中的数据
34 IExcelToDBService excelToDBService = new ExcelToDBServiceImpl();
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 package com.Tools.service.impl;
2
3 import java.io.File;
4 import java.util.ArrayList;
5 import java.util.List;
6
7 import com.Tools.entity.T_USER;
8 import com.Tools.service.IExcelToDBService;
9
10 import jxl.Sheet;
11 import jxl.Workbook;
12
13 public class ExcelToDBServiceImpl implements IExcelToDBService {
14
15 /**
16 * 获得Excel表格中的数据
17 */
18 @Override
19 public List<T_USER> getExcelContentsByT_USER(String filePath) {
20 List<T_USER> list = new ArrayList<T_USER>();
21 // 文件路径
22 String path = "";
23 // 读取该路径filePath下的所有.xls文件
24 File file = new File(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(new File(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 = new T_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 return list;
70 }
71
72 }6、编辑数据库配置文件：jdbc.properties
oracleDriver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:ORCL
username=scott
password=tiger三、Java代码实现将T_USER中的数据导出到Excel表
1、主方法：
1 package com.Tools.test;
2
3 import java.io.File;
4 import java.io.IOException;
5 import java.sql.Connection;
6 import java.sql.PreparedStatement;
7 import java.sql.ResultSet;
8 import java.sql.SQLException;
9 import java.util.ArrayList;
10 import java.util.List;
11
12 import com.Tools.entity.T_USER;
13 import com.Tools.service.IDBToExcelService;
14 import com.Tools.service.IExcelToDBService;
15 import com.Tools.service.impl.DBToExcelServiceImpl;
16 import com.Tools.service.impl.ExcelToDBServiceImpl;
17 import com.Tools.util.JDBCConnectionUtil;
18
19 import jxl.Workbook;
20 import jxl.write.Label;
21 import jxl.write.WritableSheet;
22 import jxl.write.WritableWorkbook;
23 import jxl.write.WriteException;
24
25 public class ExcelAndDB {
26 public static void main(String[] args) {
27 /**
28 * 将Excel导入数据库
29 */
30 // excelToDB();
31
32 /**
33 * 将数据库中的数据导入到Excel
34 */
35 DBToExcel();
36 }
37
38 /**
39 * 将Excel导入数据库
40 */
41 public static void excelToDB() {
42
43 Connection conn = null;
44 PreparedStatement ps = null;
45 ResultSet rs = null;
46 List<T_USER> list = new ArrayList<T_USER>();
47
48 // 读取Excel表格中的数据
49 IExcelToDBService excelToDBService = new ExcelToDBServiceImpl();
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 void DBToExcel() {
93 WritableWorkbook wbb = null;
94
95 // 创建可写入的Excel工作簿
96 String fileName = "D://test//userNew.xls";
97 File file = new File(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<T_USER> list = new ArrayList<T_USER>();
115 IDBToExcelService excelToDBService = new DBToExcelServiceImpl();
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 package com.Tools.service.impl;
2
3 import java.util.ArrayList;
4 import java.util.List;
5
6 import com.Tools.dao.IDBToExcelDao;
7 import com.Tools.dao.impl.DBToExcelDaoImpl;
8 import com.Tools.entity.T_USER;
9 import com.Tools.service.IDBToExcelService;
10
11 public class DBToExcelServiceImpl implements IDBToExcelService {
12 /**
13 * 实现查询T_USER表中所有信息接口功能
14 */
15 @Override
16 public List<T_USER> getAllUserInfo() {
17
18 List<T_USER> list = new ArrayList<T_USER>();
19 IDBToExcelDao dbToExcelDao = new DBToExcelDaoImpl();
20 list = dbToExcelDao.getAllUserInfo();
21
22 return list;
23 }
24 }3、dao层：
1 package com.Tools.dao.impl;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.ArrayList;
8 import java.util.List;
9
10 import com.Tools.dao.IDBToExcelDao;
11 import com.Tools.entity.T_USER;
12 import com.Tools.util.JDBCConnectionUtil;
13
14 public class DBToExcelDaoImpl implements IDBToExcelDao {
15
16 /**
17 * 实现查询T_USER表所有信息接口功能
18 */
19 @Override
20 public List<T_USER> getAllUserInfo() {
21 Connection conn = null;
22 PreparedStatement ps = null;
23 ResultSet rs = null;
24 List<T_USER> list = new ArrayList<T_USER>();
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 = new T_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 return list;
57 }
58
59 }
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
