xueshengexcel.java

JSP开发的学生信息管理系统

package excel;
import java.io.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import conn.DataBaseConnection;
import jxl.Cell;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.CellType;
import jxl.read.biff.BiffException;
import java.text.SimpleDateFormat;
public class XueshengExcel implements Serializable
{
	private Connection con=null;
	Statement stmt=null;
	ResultSet rst=null;
	String sql="";
	String str="";
	Cell cell=null;
	public XueshengExcel()
	{
		try
		{
			this.con=DataBaseConnection.getConnection();
			stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		}
		catch(SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
	}
		
	//导入Excell
	public int addExcel(String path,String field,int n)//字段个数
	{
		Workbook wb=null;
		Sheet sh=null;
		try
		{
			File f=new File(path);
			InputStream fs=new FileInputStream(f);
			wb=Workbook.getWorkbook(fs);
			sh=wb.getSheet(0);
			if(sh.getColumns()>n)
				{
					System.out.print("Excel中列数超过了你选择的字段值,操作已中止!");
					return 2;
				}
				if(sh.getColumns()<n)
				{
					System.out.print("Excel中列数少于你选择的字段值,操作已中止!");
					return 3;
				}
			 
			for(int i=0;i<sh.getRows();i++)
			{
				/*if(Exist(sh.getCell(0,i).getContents())==1)
					{
						System.out.println("Excel中第"+(i+1)+"行的课程编号在数据库中已存!");
						return i;
					}//判断数据库中是否有此记录值*/
				for(int j=0;j<sh.getColumns();j++)
				{
					cell=sh.getCell(j,i);
					if(cell.getType()==CellType.DATE){
						
						str+="'"+(FormateDate(cell))+"',";//处理日期
					}
					else{
						str+="'"+cell.getContents()+"',";//excel中列
					}
					
				}
				str+="'"+666666+"',";//密码为默认的6个6
				str+="'"+1+"'";//标记为在校
				sql="insert into xuesheng("+field+") values("+str+")";
				stmt.executeUpdate(sql);
				str="";
				
			} 		
		}catch (IOException e)
		 {
            e.printStackTrace();
            return 0;
         }
         catch(BiffException ex)
         {
            ex.printStackTrace();
            return 0;
         }
         catch(SQLException sqlE)
         {
         	sqlE.printStackTrace();
         	return 10;
         }
         finally{
			
					try{	
		  	    		 if(stmt!=null){
		  	    		 	stmt.close();
		  	    		 }
		  	    		 if(con!=null){
		  	    		 	con.close();
		  	    		 }
		  	    		 
		  	    		 wb.close();
		  	    		}catch(Exception e)
		  	    		{
		  	    			e.printStackTrace();
		  	    		}
		  	    	}	      
		return 1;
	}
	
	public String FormateDate(Cell formateCell){
		try{
			java.util.Date mydate=null;
			DateCell datecell=(DateCell)formateCell;
			mydate=datecell.getDate();
			//long time=(mydate.getTime()/1000)-60*60*8;
			//mydate.setTime(time*1000);
			Calendar cal=Calendar.getInstance();
			SimpleDateFormat formater=new SimpleDateFormat("yyyy-MM-dd");
			return formater.format(mydate);
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	
	public int Exist(String key)
	{
		try{
			rst=stmt.executeQuery("select * from xuesheng where studentId='"+key+"'");
			if(rst.next())
			{
				return 1;
			}
			}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    	return 0;
	}

/*	
	public static void main(String[] args)throws Exception
	{
		String path="..\\..\\excel\\学生1.xls";
		XueshengExcel xueshengExcel=new XueshengExcel();
		String field="speciality,stuClass,studentId,stuName,stuSex,birthDate,jiguan,shenfenId,jiatingTelphone,password,flag";
		int j=9;
		int i=xueshengExcel.addExcel(path,field,j);
		System.out.println(i);
	}*/
}