import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import jxl.*;                                             

import javax.swing.JOptionPane;

import jxl.Sheet;

public class Dialog extends JFrame
{
	private JTextField textField;
	private JLabel label;
	private JButton button1,button3;
	private InputStream input;//to read the file
	private OutputStream output;//to write the file
	
	private File fileName;
	private String fileStyle="mdb";    //記錄選擇的文件類型,默認為ACCESS文件
	private InportExcel read;            //一個讀EXCEL文件的類
	private InportMdb tableMdb;          //一個讀ACCESS文件的類
 
	Connection conn = null; 
	Statement stmt = null; 
	ResultSet rs = null; 
	String driver ="oracle.jdbc.driver.OracleDriver"; 
	String url ="jdbc:oracle:thin:@10.130.6.53:1521:oradb"; 
	String user ="SYSTEM"; 
	String pwd ="MANAGER"; 
	
	public Dialog()
	{
		super("import");

		Container container=getContentPane();
		container.setLayout(new FlowLayout());

		label=new JLabel("file name ");
		container.add(label);

		textField=new JTextField(30);
		textField.setEditable(false);
		container.add(textField);

		button3=new JButton("search file");
		container.add(button3);

		button1=new JButton("import");
		container.add(button1);
		
		
		ButtonHandler handler=new ButtonHandler();
		button1.addActionListener(handler);
		button3.addActionListener(handler);

		setSize(550,120);
		setVisible(true);
	}

	public class ButtonHandler implements ActionListener
	{		
		public void actionPerformed(ActionEvent e)
		{
			if(e.getSource()==button1)
			{	try{importdb( fileName.toString() );}
				catch(IOException ioException)
				{}
			}
			if(e.getSource()==button3)
			{
				try{openFile();}
				catch(IOException ioException)
				{}
			}
		}

	};
	public void openFile()throws IOException
	{
		//show JFileChooser
		JFileChooser fileChooser = new JFileChooser();
		fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
		
		int result = fileChooser.showOpenDialog(fileChooser);
		if(result == JFileChooser.CANCEL_OPTION)
			return;
		fileName = fileChooser.getSelectedFile();
		if(fileName==null||fileName.getName().equals(""))
				JOptionPane.showMessageDialog(this,"Invalid FIle Name","Invalid File Name,please select again",JOptionPane.ERROR_MESSAGE);
		textField.setText(fileName.toString());
		
		
	}
	public void closeFile()
	{
		try
		{
			if(input!=null)
				input.close();
			System.exit(0);
		}
		catch(IOException ioException)
		{
			JOptionPane.showMessageDialog(this,"Error closing file","Error",JOptionPane.ERROR_MESSAGE);
			System.exit(1);
		}
	}
	public void importdb(String file)throws IOException
	{
		String inFile;
		String extension;

		inFile = file.substring( 0, file.length( ) - 4 );
		extension = file.substring( file.length( ) - 4 );
    
	    if( !(extension.equals( ".xls" )|extension.equals( ".mdb" )) )
		{
			JOptionPane.showMessageDialog(this, "Not a suitable file!" );
			return;
		}
	    else if (extension.equals( ".xls" ))
    	{
    		try {
    			//插入一個Excel文件
    			System.out.print("xlsok");
    			
				inputExcel();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}	    		
    	}
	    else if (extension.equals( ".mdb" ))
    	{
    		try{
    			//插入一個ACCESS文件
    			inputMdb();
    			
    		}catch(SQLException e)
    		{
    			e.printStackTrace();
    		}catch(ClassNotFoundException e)
    		{
    			e.printStackTrace();
    		}
    	}
    
		
	}
	private void inputMdb()throws SQLException,ClassNotFoundException
	{
		//讀一個access文件
		tableMdb = new InportMdb(fileName.toString());
		tableMdb.connect();
		
		//將文件中的表信息存儲到tableSet中
		ResultSet tableSet = tableMdb.getTables();
		
		//連接信息
		try {
			Class.forName(driver); 
			System.out.println("加載驅動成功！"); 
			conn = DriverManager.getConnection(url,user,pwd); 
			System.out.println("連接數據庫成功！"); 
			stmt = conn.createStatement(); 
			
			
			//對文件中的每一張表執行導入操作
			while(tableSet.next())
			{
				//得到表名
				String name = tableSet.getString("TABLE_NAME");
				System.out.println(""+name);
				ResultSet colSet;
				ResultSet content;
				colSet = tableMdb.readColumn(name);
				content = tableMdb.readContent(name);
				
				String sql="create table "+name+" (";
				
				while(colSet.next())
				{
					String colName=colSet.getString("COLUMN_NAME");
					sql+=colName + " char(20),";
					
				}
				sql=sql.substring(0,sql.length()-1)+")";
				//System.out.println(sql);
				stmt.execute(sql);
				while(content.next())
				{
			    	colSet = tableMdb.readColumn(name);
					
					String sql2="insert into "+name+" values(";
					String sql3="select * from "+name+" where ";
					
					while(colSet.next())
					{
						
						String colName=colSet.getString("COLUMN_NAME");
						String c=content.getString(colName);
						sql2+="'"+c+"',";
						sql3+=colName+"='"+c+"' and ";
					    
					}
					sql3=sql3.substring(0, sql3.length()-5);
					ResultSet exit = stmt.executeQuery(sql3);
					
					if (exit.next())
					{
						JOptionPane.showMessageDialog(null, "該記錄已存在!");
					}
					else
					{
						sql2=sql2.substring(0,sql2.length()-1)+")";
						stmt.execute(sql2);
					}
					
				}
			}
			stmt.close();
			conn.close();

			} 
			catch (SQLException e) { e.printStackTrace();
									JOptionPane.showMessageDialog(null, e.getMessage().toString());} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		tableMdb.closeConn();
	}
	
	
	//插入一個個EXCEL文件
	private void inputExcel()throws SQLException,ClassNotFoundException
	{
		//讀一個EXCEL文件
		read = new InportExcel(fileName.toString());
		read.read();
		
		Sheet[] sheet = read.getSheets();
		
		for (int i=0;i<=sheet.length-1;i++)
		{	
			//對文件中的每一個表執行插入操作
			inputSheet(sheet[i]);
			System.out.println(""+sheet[i].getName());
		}
	}
	
	private void inputSheet(Sheet sheet)
	{
		read.read(sheet);
		int r = read.getRows();
	
		int c = sheet.getColumns();
		
		String[][] s = read.getStrings();
		
		String sql= "create table "+sheet.getName()+" (";
		
		for(int i=0;i<=c-1;i++)
		{
			sql+=s[0][i]+" char(20),";
		}
		sql+="primary key("+s[0][0]+"))";
						
		try {
			Class.forName(driver); 
			System.out.println("加載驅動成功！"); 
			conn = DriverManager.getConnection(url,user,pwd); 
			System.out.println("連接數據庫成功！"); 
			stmt = conn.createStatement(); 
			stmt.execute(sql);
		
		
		for(int i=1;i<=r-1;i++)
		{
			sql="insert into "+sheet.getName()+" values('";
			String sql3="select * from "+sheet.getName()+" where ";
			for(int j=0;j<=c-1;j++)
			{
				if(j==c-1)
				{
					sql+=s[i][j]+"')";
					sql3+=s[0][j]+"='"+s[i][j]+"'";
				}
				else
				{
				sql+=s[i][j]+"','";
				sql3+=s[0][j]+"='"+s[i][j]+"' and ";
			    }
				
			}
			ResultSet re=stmt.executeQuery(sql3);
			if (re.next())
			{
				JOptionPane.showMessageDialog(null, "該記錄已存在!");
			}
			else{
			stmt.executeUpdate(sql);
			}
		}
		stmt.close();
		conn.close();

		} 
		catch (SQLException e) { e.printStackTrace();
								JOptionPane.showMessageDialog(null, e.getMessage().toString());} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

				
	}

		
	public static void main(String args[])
	{
		Dialog app=new Dialog();

		app.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	}
	
}