Excel导入导出

数据连接池
static Connection con = null;
	static ResultSet rs = null;
	static PreparedStatement ps=null;

	public static void main(String[] args) {
		System.out.println(getConn());
	}
		//数据源 Data Sources
		private static final String cname="com.mysql.jdbc.Driver";
		private static final String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
		private static final String username="用户名";
		private static final String password="密码";
		
		 
		
		
		static {
			try {
				Class.forName(cname);
				} catch (Exception e) {
				// TODO: handle exception
			}
		}
		
		/**
		 * 连接数据库
		 * @param no
		 * @return Connection
		 */
		
		public static Connection getConn() {
			Connection con=null;
			try {
				con=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return con;
		}
		
		/**
		 * 关闭连接
		 * @param Connection  PreparedStatement ResultSet 
		 * @return void
		 */
		
		public static void close(Connection conn,PreparedStatement ps,ResultSet rs) {
			try {
				if(conn!=null && !conn.isClosed()) {
					conn.close();
				}
				if(ps!=null) {
					ps.close();
				}
				if(rs!=null) {
					rs.close();
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		public static void close(Connection conn, PreparedStatement ps) {
			close(conn, ps);
		}
		public static void close(Connection conn) {
			close(conn,null);
		}
		
		
		  //根据传过来的sql语句增加或修改
	    public int AddU(String sql){
	        int a =0;
	        try {
	        	con=DBhepler.getConn();
	            ps=con.prepareStatement(sql);
	            a=ps.executeUpdate();
	        } catch (SQLException e) {
	            // TODO Auto-generated catch block
	            e.printStackTrace();
	        }
	        return a;
	    }

需要包
jxl-2.6.12.jar

dao层
static Connection con = null;
	static ResultSet rs = null;
	static PreparedStatement ps=null;
	 /**
     * 查询数据库中Student表中所有的数据
     */
    public static List<Stu> getAllByDb(){
        List<Stu> list = new ArrayList<Stu>();
        String sql = "select * from student";
        System.out.println(sql);
        try {
        	con=DBhepler.getConn();
            ps=con.prepareStatement(sql);
            rs=ps.executeQuery();
            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();
        }
        for (Stu s : list) {
			System.out.println(s);
		}
        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 {
        	con=DBhepler.getConn();
        	ps=con.prepareStatement("select *from student where id="+id+"");
        	rs=ps.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return false;
    }
    

实体类
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;
	    }

导入数据库
 public static void main(String[] args) {
	        //得到表格中所有的数据
	        List<Stu> listExcel=StuService.getAllByExcel("d://book.xls");
	        /*//得到数据库表中所有的数据
	        List<Stu> listDb=StuService.getAllByDb();*/
	        DBhepler db=new DBhepler();
	        for (Stu stuEntity : listExcel) {
	            int id=stuEntity.getId();
	            if (!StuService.isExist(id)) {
	                //不存在就添加
	                String sql="insert into student (id,name,sex,num) values("+stuEntity.getId()+",'"+stuEntity.getName()+"','"+stuEntity.getSex()+"',"+stuEntity.getNum()+")";
	                //String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};
	                db.AddU(sql);
	            }else {
	                //存在就更新
	                String sql="update student set name='"+stuEntity.getName()+"',sex='"+stuEntity.getSex()+"',num="+stuEntity.getNum()+" where id="+stuEntity.getId()+"";
	                db.AddU(sql);
	            }
	        }
	        System.out.println("数据更新成功！");
	    }

从数据库导出到Excel表
 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();
		                 for (Stu s : list) {
							System.out.println(s);
						}
		                 //要插入到的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();
		          } 
		      }