package excel;
import java.io.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
import conn.DataBaseConnection;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class KechengExcel implements Serializable
{
	private Connection con=null;
	Statement stmt=null;
	ResultSet rst=null;
	String sql="";
	String str="";
	Cell cell=null;
	public KechengExcel()
	{
		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);
			//InputStream fs=new FileInputStream(path);
			wb=Workbook.getWorkbook(fs);
			sh=wb.getSheet(0);
			if(sh.getColumns()>4)
				{
					System.out.print("Excel中列數超過了數據庫字段值,操作已中止!");
					return 2;
				}
				if(sh.getColumns()<4)
				{
					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);
					str+="'"+cell.getContents()+"',";//excel中列
				}
				str+="'"+1+"'";
				sql="insert into kecheng 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 int Exist(String key)
	{
		try{
			rst=stmt.executeQuery("select * from kecheng where courseId='"+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\\course.xls";
		CourseExcel courseExcel=new CourseExcel();
		courseExcel.addExcel(path);
	}*/
}