Java实现Excel导入数据库,数据库中的数据导入到Excel
maven 加入mysql包和处理Excel的jar包
<dependencies> <!-- MySql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.12</version> </dependency> </dependencies>
数据库:用户表
CREATE TABLE `user_info` ( `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `user_name` varchar(50) NOT NULL COMMENT '用户名称', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', `mobile` varchar(20) NOT NULL COMMENT '手机号', `password` varchar(30) DEFAULT NULL COMMENT '登录密码', `create_time` timestamp NULL DEFAULT NULL COMMENT '创建日期', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期', `del_flg` char(1) DEFAULT '0' COMMENT '删除flag 0:未删除;1:已删除;', PRIMARY KEY (`user_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息';
工具类:
public class DBhepler { String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://url:3306/数据库名"; Connection con = null; ResultSet res = null; public void DataBase() { try { Class.forName(driver); con = DriverManager.getConnection(url, "用户名", "密码"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block System.err.println("装载 JDBC/ODBC 驱动程序失败。"); e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block System.err.println("无法连接数据库"); e.printStackTrace(); } } // 查询 public ResultSet Search(String sql, String str[]) { DataBase(); try { PreparedStatement pst = con.prepareStatement(sql); if (str != null) { for (int i = 0; i < str.length; i++) { pst.setString(i + 1, str[i]); } } res = pst.executeQuery(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return res; } //增删修改 public int AddU(String sql, String str[]) { int a = 0; DataBase(); try { PreparedStatement pst = con.prepareStatement(sql); if (str != null) { for (int i = 0; i < str.length; i++) { pst.setString(i + 1, str[i]); } } a = pst.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return a; } }
实体类:
public class UserInfo { private Integer userId; private String userName; private String email; private String mobile; private String password; private Date createTime; private Date updateTime; private String delFlg; public UserInfo(Integer userId, String userName, String email, String mobile, String password) { super(); this.userId = userId; this.userName = userName; this.email = email; this.mobile = mobile; this.password = password; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName == null ? null : userName.trim(); } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile == null ? null : mobile.trim(); } public String getPassword() { return password; } public void setPassword(String password) { this.password = password == null ? null : password.trim(); } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } public Date getUpdateTime() { return updateTime; } public void setUpdateTime(Date updateTime) { this.updateTime = updateTime; } public String getDelFlg() { return delFlg; } public void setDelFlg(String delFlg) { this.delFlg = delFlg == null ? null : delFlg.trim(); } }
获取数据类:
public class UserService { /** * 获取用户信息 * @return */ public static List<UserInfo> getAllByDb() { List<UserInfo> list = new ArrayList<UserInfo>(); try { DBhepler db = new DBhepler(); String sql = "select * from user_info"; ResultSet rs = db.Search(sql, null); while (rs.next()) { int userId = rs.getInt("user_id"); String userName = rs.getString("user_name"); String email = rs.getString("email"); String mobile = rs.getString("mobile"); String password = rs.getString("password"); UserInfo userInfo = new UserInfo(userId, userName, email, mobile, password); list.add(userInfo); } } catch (SQLException e) { e.printStackTrace(); } return list; } /** * 查询指定目录中电子表格中所有的数据 * @param file * 文件完整路径 * @return */ public static List<UserInfo> getAllByExcel(String file) { List<UserInfo> list = new ArrayList<UserInfo>(); try { Workbook rwb = Workbook.getWorkbook(new File(file)); Sheet rs = rwb.getSheet("用户信息");// 或者rwb.getSheet(0) int clos = rs.getColumns();// 得到所有的列 int rows = rs.getRows();// 得到所有的行 System.out.println(clos + " rows:" + rows); for (int i = 1; i < rows; i++) { for (int j = 0; j < clos; j++) { // 第一个是列数,第二个是行数 String userId = rs.getCell(j++, i).getContents();// 默认最左边编号也算一列 if (userId == null || "".equals(userId))userId = "0"; // 所以这里得j++ String userName = rs.getCell(j++, i).getContents(); String email = rs.getCell(j++, i).getContents(); String mobile = rs.getCell(j++, i).getContents(); String password = rs.getCell(j++, i).getContents(); list.add(new UserInfo(Integer.valueOf(userId), userName, email, mobile, password)); } } } catch (Exception e) { e.printStackTrace(); } return list; } /** * 通过Id判断是否存在 * @param id * @return */ public static boolean isExist(int id) { try { DBhepler db = new DBhepler(); ResultSet rs = db.Search("select * from user_info where user_id=?", new String[] { id + "" }); if (rs.next()) { return true; } } catch (SQLException e) { e.printStackTrace(); } return false; } public static void main(String[] args) { /* * List<StuEntitay> all=getAllByDb(); for (StuEntity stuEntity : all) { * System.out.println(stuEntity.toString()); } */ System.out.println(isExist(1)); } }
导出Excel:
public class ExportExcel { public static void main(String[] args) { try { WritableWorkbook wwb = null; // 创建可写入的Excel工作簿 String fileName = "D://book.xls"; File file=new File(fileName); if (!file.exists()) { file.createNewFile(); } //以fileName为文件名来创建一个Workbook wwb = Workbook.createWorkbook(file); // 创建工作表 WritableSheet ws = wwb.createSheet("用户信息", 0); //查询数据库中所有的数据 List<UserInfo> list= UserService.getAllByDb(); //要插入到的Excel表格的行号,默认从0开始 Label labelId= new Label(0, 0, "编号(userId)");//表示第 Label labelName= new Label(1, 0, "姓名(userName)"); Label labelSex= new Label(2, 0, "email(email)"); Label labelNum= new Label(3, 0, "电话(mobile)"); Label labelPassword= new Label(4, 0, "密码(password)"); ws.addCell(labelId); ws.addCell(labelName); ws.addCell(labelSex); ws.addCell(labelNum); ws.addCell(labelPassword); for (int i = 0; i < list.size(); i++) { Label labelId_i= new Label(0, i+1, list.get(i).getUserId()+""); Label labelName_i= new Label(1, i+1, list.get(i).getUserName()); Label labelSex_i= new Label(2, i+1, list.get(i).getEmail()); Label labelNum_i= new Label(3, i+1, list.get(i).getMobile()+""); Label labelPassword_i= new Label(4, i+1, list.get(i).getPassword()+""); ws.addCell(labelId_i); ws.addCell(labelName_i); ws.addCell(labelSex_i); ws.addCell(labelNum_i); ws.addCell(labelPassword_i); } //写进文档 wwb.write(); // 关闭Excel工作簿对象 wwb.close(); } catch (Exception e) { e.printStackTrace(); } } }
导入mysql:
public class ImportMysql { public static void main(String[] args) { //得到表格中所有的数据 List<UserInfo> listExcel=UserService.getAllByExcel("d://book.xls"); /*//得到数据库表中所有的数据 List<StuEntity> listDb=StuService.getAllByDb();*/ DBhepler db=new DBhepler(); for (UserInfo userInfo : listExcel) { int id=userInfo.getUserId(); if (id == 0|| !UserService.isExist(id)) { //不存在就添加 String sql="insert into user_info (user_name,email,mobile,password,create_time,update_time) values(?,?,?,?,now(),now())"; String[] str=new String[]{userInfo.getUserName(),userInfo.getEmail(),userInfo.getMobile(),userInfo.getPassword()}; db.AddU(sql, str); }else { //存在就更新 String sql="update user_info set user_name=?,email=?,mobile=?,password=?,update_time=now() where user_id=?"; String[] str=new String[]{userInfo.getUserName(),userInfo.getEmail(),userInfo.getMobile(),userInfo.getPassword(),id+""}; db.AddU(sql, str); } } } }