Java实现Excel导入数据库，数据库中的数据导入到Excel

 1 package com.javen.db;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 
 9 public class DBhepler {
10     /*String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
11     String url = "jdbc:sqlserver://127.0.0.1;DatabaseName=javenforexcel";*/
12     
13     String driver = "com.mysql.jdbc.Driver";
14     String url = "jdbc:mysql://127.0.0.1:3306/javenforexcel";
15     
16     
17     Connection con = null;
18     ResultSet res = null;
19 
20     public void DataBase() {
21             try {
22                 Class.forName(driver);
23                 con = DriverManager.getConnection(url, "root", "root");
24             } catch (ClassNotFoundException e) {
25                 // TODO Auto-generated catch block
26                   System.err.println("装载 JDBC/ODBC 驱动程序失败。" );  
27                 e.printStackTrace();
28             } catch (SQLException e) {
29                 // TODO Auto-generated catch block
30                 System.err.println("无法连接数据库" ); 
31                 e.printStackTrace();
32             }
33     }
34 
35     // 查询
36     public ResultSet  Search(String sql, String str[]) {
37         DataBase();
38         try {
39             PreparedStatement pst =con.prepareStatement(sql);
40             if (str != null) {
41                 for (int i = 0; i < str.length; i++) {
42                     pst.setString(i + 1, str[i]);
43                 }
44             }
45             res = pst.executeQuery();
46 
47         } catch (Exception e) {
48             // TODO Auto-generated catch block
49             e.printStackTrace();
50         }
51         return res;
52     }
53 
54     // 增删修改
55     public int AddU(String sql, String str[]) {
56         int a = 0;
57         DataBase();
58         try {
59             PreparedStatement pst = con.prepareStatement(sql);
60             if (str != null) {
61                 for (int i = 0; i < str.length; i++) {
62                     pst.setString(i + 1, str[i]);
63                 }
64             }
65             a = pst.executeUpdate();
66         } catch (Exception e) {
67             // TODO Auto-generated catch block
68             e.printStackTrace();
69         }
70         return a;
71     }
72 
73 }

  1 package com.javen.service;
  2 
  3 import java.io.File;
  4 import java.sql.ResultSet;
  5 import java.sql.SQLException;
  6 import java.util.ArrayList;
  7 import java.util.List;
  8 
  9 import jxl.Sheet;
 10 import jxl.Workbook;
 11 
 12 import com.javen.db.DBhepler;
 13 import com.javen.entity.StuEntity;
 14 
 15 /**
 16  * @author Javen
 17  * @Email zyw205@gmail.com
 18  * 
 19  */
 20 public class StuService {
 21     /**
 22      * 查询stu表中所有的数据
 23      * @return 
 24      */
 25     public static List<StuEntity> getAllByDb(){
 26         List<StuEntity> list=new ArrayList<StuEntity>();
 27         try {
 28             DBhepler db=new DBhepler();
 29             String sql="select * from stu";
 30             ResultSet rs= db.Search(sql, null);
 31             while (rs.next()) {
 32                 int id=rs.getInt("id");
 33                 String name=rs.getString("name");
 34                 String sex=rs.getString("sex");
 35                 int num=rs.getInt("num");
 36                 
 37                 //System.out.println(id+" "+name+" "+sex+ " "+num);
 38                 list.add(new StuEntity(id, name, sex, num));
 39             }
 40             
 41         } catch (SQLException e) {
 42             // TODO Auto-generated catch block
 43             e.printStackTrace();
 44         }
 45         return list;
 46     }
 47     
 48     /**
 49      * 查询指定目录中电子表格中所有的数据
 50      * @param file 文件完整路径
 51      * @return
 52      */
 53     public static List<StuEntity> getAllByExcel(String file){
 54         List<StuEntity> list=new ArrayList<StuEntity>();
 55         try {
 56             Workbook rwb=Workbook.getWorkbook(new File(file));
 57             Sheet rs=rwb.getSheet("Test Shee 1");//或者rwb.getSheet(0)
 58             int clos=rs.getColumns();//得到所有的列
 59             int rows=rs.getRows();//得到所有的行
 60             
 61             System.out.println(clos+" rows:"+rows);
 62             for (int i = 1; i < rows; i++) {
 63                 for (int j = 0; j < clos; j++) {
 64                     //第一个是列数，第二个是行数
 65                     String id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
 66                     String name=rs.getCell(j++, i).getContents();
 67                     String sex=rs.getCell(j++, i).getContents();
 68                     String num=rs.getCell(j++, i).getContents();
 69                     
 70                     System.out.println("id:"+id+" name:"+name+" sex:"+sex+" num:"+num);
 71                     list.add(new StuEntity(Integer.parseInt(id), name, sex, Integer.parseInt(num)));
 72                 }
 73             }
 74         } catch (Exception e) {
 75             // TODO Auto-generated catch block
 76             e.printStackTrace();
 77         } 
 78         return list;
 79         
 80     }
 81     
 82     /**
 83      * 通过Id判断是否存在
 84      * @param id
 85      * @return
 86      */
 87     public static boolean isExist(int id){
 88         try {
 89             DBhepler db=new DBhepler();
 90             ResultSet rs=db.Search("select * from stu where id=?", new String[]{id+""});
 91             if (rs.next()) {
 92                 return true;
 93             }
 94         } catch (SQLException e) {
 95             // TODO Auto-generated catch block
 96             e.printStackTrace();
 97         }
 98         return false;
 99     }
100     
101     public static void main(String[] args) {
102         /*List<StuEntity> all=getAllByDb();
103         for (StuEntity stuEntity : all) {
104             System.out.println(stuEntity.toString());
105         }*/
106         
107         System.out.println(isExist(1));
108         
109     }
110     
111 }

 1 package com.javen.excel;
 2 
 3 import java.io.File;
 4 import java.util.List;
 5 
 6 import com.javen.entity.StuEntity;
 7 import com.javen.service.StuService;
 8 
 9 import jxl.Workbook;
10 import jxl.write.Label;
11 import jxl.write.WritableSheet;
12 import jxl.write.WritableWorkbook;
13 
14 public class TestDbToExcel {
15 
16     public static void main(String[] args) {
17         try {
18             WritableWorkbook wwb = null;
19              
20                // 创建可写入的Excel工作簿
21                String fileName = "D://book.xls";
22                File file=new File(fileName);
23                if (!file.exists()) {
24                    file.createNewFile();
25                }
26                //以fileName为文件名来创建一个Workbook
27                wwb = Workbook.createWorkbook(file);
28 
29                // 创建工作表
30                WritableSheet ws = wwb.createSheet("Test Shee 1", 0);
31                
32                //查询数据库中所有的数据
33                List<StuEntity> list= StuService.getAllByDb();
34                //要插入到的Excel表格的行号，默认从0开始
35                Label labelId= new Label(0, 0, "编号(id)");//表示第
36                Label labelName= new Label(1, 0, "姓名(name)");
37                Label labelSex= new Label(2, 0, "性别(sex)");
38                Label labelNum= new Label(3, 0, "薪水(num)");
39                
40                ws.addCell(labelId);
41                ws.addCell(labelName);
42                ws.addCell(labelSex);
43                ws.addCell(labelNum);
44                for (int i = 0; i < list.size(); i++) {
45                    
46                    Label labelId_i= new Label(0, i+1, list.get(i).getId()+"");
47                    Label labelName_i= new Label(1, i+1, list.get(i).getName());
48                    Label labelSex_i= new Label(2, i+1, list.get(i).getSex());
49                    Label labelNum_i= new Label(3, i+1, list.get(i).getNum()+"");
50                    ws.addCell(labelId_i);
51                    ws.addCell(labelName_i);
52                    ws.addCell(labelSex_i);
53                    ws.addCell(labelNum_i);
54                }
55              
56               //写进文档
57                wwb.write();
58               // 关闭Excel工作簿对象
59                wwb.close();
60              
61         } catch (Exception e) {
62             // TODO Auto-generated catch block
63             e.printStackTrace();
64         } 
65     }
66 }

 1 package com.javen.excel;
 2 
 3 import java.util.List;
 4 
 5 import com.javen.db.DBhepler;
 6 import com.javen.entity.StuEntity;
 7 import com.javen.service.StuService;
 8 
 9 /**
10  * @author Javen
11  * @Email zyw205@gmail.com
12  * 
13  */
14 public class TestExcelToDb {
15     public static void main(String[] args) {
16         //得到表格中所有的数据
17         List<StuEntity> listExcel=StuService.getAllByExcel("d://book.xls");
18         /*//得到数据库表中所有的数据
19         List<StuEntity> listDb=StuService.getAllByDb();*/
20         
21         DBhepler db=new DBhepler();
22         
23         for (StuEntity stuEntity : listExcel) {
24             int id=stuEntity.getId();
25             if (!StuService.isExist(id)) {
26                 //不存在就添加
27                 String sql="insert into stu (name,sex,num) values(?,?,?)";
28                 String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};
29                 db.AddU(sql, str);
30             }else {
31                 //存在就更新
32                 String sql="update stu set name=?,sex=?,num=? where id=?";
33                 String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""};
34                 db.AddU(sql, str);
35             }
36         }
37     }
38 }

1 智慧云端日记  http://www.cnblogs.com/zyw-205520/p/3762954.html