你的位置:首页 > Java教程

[Java教程]Java实现Excel导入数据库,数据库中的数据导入到Excel


实现的功能:

  • Java实现Excel导入数据库,如果存在就更新
  • 数据库中的数据导入到Excel

 

1、添加jxl.jar mysql-connector-java.1.7-bin.jar包到项目的lib目录下­

2、Excel文件目录:D://book.xls

3、数据库名:javenforexcel

4、表名:stu

5、编写类:连接mysql的字符串方法、插入的方法、实体类­­

表结构如下 :

 

 

 

连接数据库的工具类

复制代码
package com.javen.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DBhepler {  /*String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";  String url = "jdbc:sqlserver://127.0.0.1;DatabaseName=javenforexcel";*/    String driver = "com.mysql.jdbc.Driver";  String url = "jdbc:mysql://127.0.0.1:3306/javenforexcel";      Connection con = null;  ResultSet res = null;  public void DataBase() {      try {        Class.forName(driver);        con = DriverManager.getConnection(url, "root", "root");      } 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;  }}
复制代码

 

表的实体如下

复制代码
package com.javen.entity;/** * @author Javen * @Email zyw205@gmail.com * */public class StuEntity {  private int id;  private String name;  private String sex;  private int num;        public StuEntity() {  }  public StuEntity(int id, String name, String sex, int num) {    this.id = id;    this.name = name;    this.sex = sex;    this.num = num;  }    @Override  public String toString() {    return "StuEntity [id=" + id + ", name=" + name + ", sex=" + sex        + ", num=" + num + "]";  }  public int getId() {    return id;  }  public void setId(int id) {    this.id = id;  }  public String getName() {    return name;  }  public void setName(String name) {    this.name = name;  }  public String getSex() {    return sex;  }  public void setSex(String sex) {    this.sex = sex;  }  public int getNum() {    return num;  }  public void setNum(int num) {    this.num = num;  }        }
复制代码

Java实现Excel导入数据核心类 读取Excel表中所有的数据、操作数据(查询、更新)

复制代码
package com.javen.service;import java.io.File;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import jxl.Sheet;import jxl.Workbook;import com.javen.db.DBhepler;import com.javen.entity.StuEntity;/** * @author Javen * @Email zyw205@gmail.com * */public class StuService {  /**   * 查询stu表中所有的数据   * @return   */  public static List<StuEntity> getAllByDb(){    List<StuEntity> list=new ArrayList<StuEntity>();    try {      DBhepler db=new DBhepler();      String sql="select * from stu";      ResultSet rs= db.Search(sql, null);      while (rs.next()) {        int id=rs.getInt("id");        String name=rs.getString("name");        String sex=rs.getString("sex");        int num=rs.getInt("num");                //System.out.println(id+" "+name+" "+sex+ " "+num);        list.add(new StuEntity(id, name, sex, num));      }          } catch (SQLException e) {      // TODO Auto-generated catch block      e.printStackTrace();    }    return list;  }    /**   * 查询指定目录中电子表格中所有的数据   * @param file 文件完整路径   * @return   */  public static List<StuEntity> getAllByExcel(String file){    List<StuEntity> list=new ArrayList<StuEntity>();    try {      Workbook rwb=Workbook.getWorkbook(new File(file));      Sheet rs=rwb.getSheet("Test Shee 1");//或者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 id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++          String name=rs.getCell(j++, i).getContents();          String sex=rs.getCell(j++, i).getContents();          String num=rs.getCell(j++, i).getContents();                    System.out.println("id:"+id+" name:"+name+" sex:"+sex+" num:"+num);          list.add(new StuEntity(Integer.parseInt(id), name, sex, Integer.parseInt(num)));        }      }    } catch (Exception e) {      // TODO Auto-generated catch block      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 stu where id=?", new String[]{id+""});      if (rs.next()) {        return true;      }    } catch (SQLException e) {      // TODO Auto-generated catch block      e.printStackTrace();    }    return false;  }    public static void main(String[] args) {    /*List<StuEntity> all=getAllByDb();    for (StuEntity stuEntity : all) {      System.out.println(stuEntity.toString());    }*/        System.out.println(isExist(1));      }  }
复制代码

数据的数据导入到Excel表

复制代码
package com.javen.excel;import java.io.File;import java.util.List;import com.javen.entity.StuEntity;import com.javen.service.StuService;import jxl.Workbook;import jxl.write.Label;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;public class TestDbToExcel {  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("Test Shee 1", 0);               //查询数据库中所有的数据        List<StuEntity> list= StuService.getAllByDb();        //要插入到的Excel表格的行号,默认从0开始        Label labelId= new Label(0, 0, "编号(id)");//表示第        Label labelName= new Label(1, 0, "姓名(name)");        Label labelSex= new Label(2, 0, "性别(sex)");        Label labelNum= new Label(3, 0, "薪水(num)");               ws.addCell(labelId);        ws.addCell(labelName);        ws.addCell(labelSex);        ws.addCell(labelNum);        for (int i = 0; i < list.size(); i++) {                   Label labelId_i= new Label(0, i+1, list.get(i).getId()+"");          Label labelName_i= new Label(1, i+1, list.get(i).getName());          Label labelSex_i= new Label(2, i+1, list.get(i).getSex());          Label labelNum_i= new Label(3, i+1, list.get(i).getNum()+"");          ws.addCell(labelId_i);          ws.addCell(labelName_i);          ws.addCell(labelSex_i);          ws.addCell(labelNum_i);        }              //写进文档        wwb.write();       // 关闭Excel工作簿对象        wwb.close();           } catch (Exception e) {      // TODO Auto-generated catch block      e.printStackTrace();    }   }}
复制代码

Excel表中的数据导入到MySql数据库

 

复制代码
package com.javen.excel;import java.util.List;import com.javen.db.DBhepler;import com.javen.entity.StuEntity;import com.javen.service.StuService;/** * @author Javen * @Email zyw205@gmail.com * */public class TestExcelToDb {  public static void main(String[] args) {    //得到表格中所有的数据    List<StuEntity> listExcel=StuService.getAllByExcel("d://book.xls");    /*//得到数据库表中所有的数据    List<StuEntity> listDb=StuService.getAllByDb();*/        DBhepler db=new DBhepler();        for (StuEntity stuEntity : listExcel) {      int id=stuEntity.getId();      if (!StuService.isExist(id)) {        //不存在就添加        String sql="insert into stu (name,sex,num) values(?,?,?)";        String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};        db.AddU(sql, str);      }else {        //存在就更新        String sql="update stu set name=?,sex=?,num=? where id=?";        String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""};        db.AddU(sql, str);      }    }  }}
复制代码
 
 

源代码下载地址 http://download.csdn.net/detail/zyw_java/7430807