jiangchengexcel.java

本系统对学生信息操作控制流程和相关数据信息进行了较为全面且科学高效的管理

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 JiangchengExcel implements Serializable
{
	private Connection con=null;
	Statement stmt=null;
	ResultSet rst=null;
	String sql="";
	String str="";
	Cell cell=null;
	public JiangchengExcel()
	{
		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)
	{
		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()>5)
				{
					System.out.print("Excel中列数超过了你选择的字段值,操作已中止!");
					return 2;
				}
				if(sh.getColumns()<5)
				{
					System.out.print("Excel中列数少于你选择的字段值,操作已中止!");
					return 3;
				}
			 
			for(int i=0;i<sh.getRows();i++)
			{
				for(int j=0;j<sh.getColumns();j++)
				{
					cell=sh.getCell(j,i);
					if(j!=sh.getColumns()-1)
					{
						if(cell.getType()==CellType.DATE){	
						str+="'"+(FormateDate(cell))+"',";//处理日期
						}else{
							str+="'"+cell.getContents()+"',";//excel中列
						}	
					}
					else{
						str+="'"+cell.getContents()+"'";
					}
				}
				
				sql="insert into jiangcheng(jiangchengName,studentId,jiangchengDate,jiangchengType,jiangchengDescript) 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;
	}
}