转载Java导入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 [e-mail] *  */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 [e-mail] *  */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 [e-mail] *  */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