imp.java

用JAVE编写的Excel导入数据库源程序代码.

package ff;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class imp {
	public static void main(String args[])
	{
		
	    Connection	 conn=null;
	    int 		property;
	    String url="jdbc:mysql://localhost:3306/carddb?useUnicode=true&characterEncoding=gbk";
	    String username="house";
	    String pwd="wenzhouhouse";
	    //建立数据库连接
	    try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
			} catch (InstantiationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
	    	try {
				conn = DriverManager.getConnection(url,username,pwd);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
			//获得文件列表
			String filepath=args[0];
			System.out.println("filepath="+filepath);
			property=Integer.parseInt(args[1]);
			File path =new File(filepath);
		    if(!path.exists())
			 {
		    	System.out.println("please input filepath param");
		    	return ;	
			 }
		    
		    //得到文件列表
			String[] list;
			list=path.list();
			
			if(list==null)
			 {
			  
				return;	
			}
			if (list.length==0)
			{
				return;
			}
			
			Workbook book=null;
			Sheet sheet=null;
			int row,sex;
			String nickname,company,realname,mobile,phone,office,s_sex;
			String web,address,fax,companydetail;
			String demand,cityname;
			StringBuffer sql =new StringBuffer();
			PreparedStatement stat=null;
			int rownum=0;
			for(int j=0;j<list.length;j++)
		    {
				//打开excel文件
				try {
					book=Workbook.getWorkbook(new File(filepath,list[j]));
					} catch (BiffException e) {
				// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					//获得第一个工作表对象
					sheet=book.getSheet(0);
					//取得工作表的总行数
					row=sheet.getRows();
					//取得工作表的一行
					//将读取的每一行EXCEL数据插入到数据表中
					//property=Integer.parseInt(sheet.getCell(0,0).getContents());
					for(int i=1;i<row;i++)
					{
						sql.append("insert into db_carduser(F_NickName,F_property,F_Company,F_RealName,F_Mobile,F_Phone")
						.append(",F_Office,F_Sex,F_Web,F_address,F_fax,F_CompanyDetail,F_Demand,F_Cityname,F_UserType,F_Vip,F_Status) ");
						nickname=sheet.getCell(0,i).getContents();  //昵称
						company=sheet.getCell(2,i).getContents();    //公司
						realname=sheet.getCell(3,i).getContents();  //真实名称
						mobile=sheet.getCell(4,i).getContents();
						phone=sheet.getCell(5,i).getContents();
						office=sheet.getCell(6,i).getContents();
						s_sex=sheet.getCell(7,i).getContents();
						sex=1;
						if (s_sex.equalsIgnoreCase("先生"))
							sex=1;
						else if (s_sex.equalsIgnoreCase("女士"))
							sex=2;
						web=sheet.getCell(8,i).getContents();
						address=sheet.getCell(10,i).getContents();
						fax=sheet.getCell(11,i).getContents();
						demand=sheet.getCell(15,i).getContents();
						cityname=sheet.getCell(18,i).getContents();
						companydetail=sheet.getCell(36, i).getContents();
						
						//插入数据库
						sql.append(" values(");
						
						sql.append("'").append(nickname).append("',").append(property).append(",'")
						.append(company).append("','").append(realname).append("','").append(mobile)
						.append("','").append(phone).append("','")
						.append(office).append("',").append(sex).append(",'").append(web).append("','")
						.append(address).append("','").append(fax).append("','").append(companydetail)
						.append("','").append(demand).append("','").append(cityname).append("',100,0,1)");
						//System.out.println(sql.toString());
					   	try {
							stat=conn.prepareStatement(sql.toString());
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
							try {
								stat.executeUpdate();
							} catch (SQLException e1) {
								// TODO Auto-generated catch block
								e1.printStackTrace();
							}
							catch(Exception e)
							{
								e.printStackTrace();
							}
							sql.delete(0,sql.length());
							
						}
					//关闭工作表
					book.close();
					new File(filepath,list[j]).delete();
		    }
		 			//关闭连接
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			conn=null;
		//关闭连接
		
	}
	
}