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

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 [email protected] *  */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));            }    }

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

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 [email protected] *  */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);            }        }    }}