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);
              }
          }
      }
}