Java将Excel表格导入数据库，将数据库中的数据导出到Excel表

总结一下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 }

posted @ 2020-09-27 14:05 JohnYu35 阅读(919) 评论(0) 收藏举报

刷新页面返回顶部