Java实现将Excel导入数据库和从数据库中导出为Excel
实现的功能：
- 用Java实现从Excel导入数据库，如果存在就更新
- 将数据库中的数据导出为Excel
1、添加jxl.jar mysql-connector-java.1.7-bin.jar包到项目的lib目录下
2、Excel文件目录:D://book.xls
3、数据库名：Stu
4、表名:student
5、编写类：连接mysql的字符串方法、插入的方法、实体类
表结构如下 ：
连接数据库的工具类
DBhelper.java
package com.cn.gao; import java.sql.*; public class DBhelper { Connection conn = null; ResultSet rs = null; //连接数据库 public void connect(){ try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Stu","root","1234"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block // e.printStackTrace(); System.out.println("数据库驱动加载失败！"); } catch (SQLException e) { // TODO Auto-generated catch block // e.printStackTrace(); System.out.println("数据库连接失败！"); } } //查询 public ResultSet Search(String sql, String str[]){ connect(); try { PreparedStatement pst = conn.prepareStatement(sql); if(str != null){ for(int i=0;i<str.length-1;i++){ pst.setString(i+1, str[i]); } pst.setInt(str.length, Integer.parseInt(str[str.length-1])); } rs = pst.executeQuery(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return rs; } //增删修改 public int AddU(String sql, String str[]){ int a =0; connect(); try { PreparedStatement pst = conn.prepareStatement(sql); if(str != null){ for(int i=0;i<str.length-1;i++){ pst.setString(i+1, str[i]); } pst.setInt(str.length, Integer.parseInt(str[str.length-1])); } a = pst.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return a; } }
表的实体如下
Stu.java
package com.cn.gao; //学生实体类 public class Stu { private int id; private String name; private String sex; private int num; public Stu(){ } public Stu(int id,String name,String sex,int num){ this.id = id; this.name = name; this.sex = sex; this.num = num; } public String toString(){ return "Stu[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表中所有的数据、操作数据（查询、更新）
StuService.java
package com.cn.gao; import java.io.File; import java.io.IOException; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; //实现Excel导入数据库核心类，读取Excel表中所有的数据，操作数据（查询、更新） public class StuService { /** * 查询数据库中Student表中所有的数据 */ public static List<Stu> getAllByDb(){ List<Stu> list = new ArrayList<Stu>(); DBhelper db = new DBhelper(); String sql = "select * from student"; ResultSet rs = db.Search(sql, null); try { while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); String sex = rs.getString("sex"); int num = rs.getInt("num"); list.add(new Stu(id,name,sex,num)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } /** * 查询指定目录中Excel表格中所有数据 * @param file为文件完整路径 * @return */ public static List<Stu> getAllByExcel(String file){ List<Stu> list = new ArrayList<Stu>(); 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:"+clos+" rows:"+rows); for(int i=1;i<rows;i++){ int j=0; //第一个是列数，第二个是行数 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 Stu(Integer.parseInt(id), name, sex, Integer.parseInt(num))); } } catch (BiffException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } /** * 通过id判断是否存在 * @param id * @return */ public static boolean isExist(int id){ try { DBhelper db=new DBhelper(); ResultSet rs=db.Search("select * from student 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<Stu> all=getAllByDb(); for (Stu stuEntity : all) { System.out.println(stuEntity.toString()); }*/ System.out.println(isExist(1)); } }
将数据库中的数据导出到Excel表
FromDbToExcel.java
package com.cn.gao; import java.io.File; import java.util.List; import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; public class FromDbToExcel { 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<Stu> 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工作簿对象 System.out.println("数据导出成功!"); wwb.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
将Excel表中的数据导入到MySql数据库
FromExcelToDb.java
package com.cn.gao; import java.util.List; public class FromExcelToDb { public static void main(String[] args) { //得到表格中所有的数据 List<Stu> listExcel=StuService.getAllByExcel("d://book.xls"); /*//得到数据库表中所有的数据 List<Stu> listDb=StuService.getAllByDb();*/ DBhelper db=new DBhelper(); for (Stu stuEntity : listExcel) { int id=stuEntity.getId(); if (!StuService.isExist(id)) { //不存在就添加 String sql="insert into student (name,sex,num) values(?,?,?)"; String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""}; db.AddU(sql, str); }else { //存在就更新 String sql="update student set name=?,sex=?,num=? where id=?"; String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""}; db.AddU(sql, str); } } System.out.println("数据更新成功！"); } }
