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;
	}
}