creat.java

在Java中读取EXCEL中的数据并在SQL Server2005 中创建数据库

package SQL_XLS;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class CREAT
{
	public static void main(String[] args)
	{
		HashMap<Integer, String> hm =new HashMap<Integer, String>();
		XSL(hm);
		SHU_JU_KU(hm);
	}
	
	@SuppressWarnings("unchecked")
	static HashMap XSL(HashMap hm)
	{
		
		try
		{
			Workbook workbook = Workbook.getWorkbook(new File(
					"e:\\数据库设计书1班.xls"));
			int t =1;
			ArrayList<String> al = new ArrayList<String>();
			for ( t =1; t < workbook.getSheets().length; t++)
			{
				Sheet sheet = workbook.getSheet(t);
		
				String tableName = sheet.getCell(22, 3).getContents();
				String str="";
				str=str+"CREATE TABLE " + tableName + " (";
				String[] columes = { "16_COLNAME", "25_TYPE", "29_LENGTH",
					"33_DEFAULT", "37_ISNULL", "39_PRIMARYKEY",
					"31_DECIMALDIGITS" };
				int startRow = 7;
				int maxRow = sheet.getColumn(0).length;
				for (int i = startRow; i < maxRow; i++) 
				{
					if (i != startRow)
					{
						str=str+",";
					}
					Cell[] rowCell = sheet.getRow(i);
					for (int j = 0; j < columes.length - 1; j++) 
					{
						String tempCell = rowCell[Integer.parseInt(columes[j]
						                .split("_")[0])].getContents().trim();
						if (tempCell.length() > 0)
						{
							if (columes[j].split("_")[1].equals("LENGTH"))
							{
								if (rowCell[Integer.parseInt(columes[j - 1]
									.split("_")[0])].getContents().trim()
									.equals("DECIMAL")) 
								{

									tempCell = "("
										+ tempCell
										+ ","
										+ rowCell[Integer
												.parseInt(columes[columes.length - 1]
														.split("_")[0])]
												.getContents().trim() + ")";
								}
								else 
								{
									tempCell = "(" + tempCell + ")";
								}
							}
							if (columes[j].split("_")[1].equals("ISNULL")
								&& tempCell.equals("NO")) 
							{
								tempCell = "NOT NULL";
							}	
							if (columes[j].split("_")[1].equals("DEFAULT")) 
							{
								tempCell = " DEFAULT (" + tempCell + ")";
							}
							if (columes[j].split("_")[1].equals("PRIMARYKEY"))
							{
								tempCell = " CONSTRAINT  PK_"
									+ tableName
									+ " PRIMARY KEY  ("
									+ rowCell[Integer.parseInt(columes[0]
											.split("_")[0])].getContents()
											.trim() + ")";
							}
							str=str+tempCell;
						}					
						str=str+" ";
					}			
					str=str+"\n";
				}			
				str=str+")";	
				al.add(str);
				hm.put(t, al.get(t-1));		
			}
			return hm;
		} 
		catch (Exception e)
		{
			e.printStackTrace();
			return hm;	
		}
	}
	
	@SuppressWarnings("unchecked")
	static  void SHU_JU_KU(HashMap<Integer, String> hm )
	{
		try
		{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=book";
			Connection Db = DriverManager.getConnection(url,
					"sa", "123");

			Statement st=null;
			Set<?> set=hm.entrySet();
			Iterator<?> Iter = set.iterator();
			
			while (Iter.hasNext()) 
			{
				Map.Entry me = (Map.Entry) Iter.next();
				String creatTable =me.getValue().toString();
				String tableName[]=creatTable.split(" ");
				String shujuku[]=url.split("=");
				st = Db.createStatement();
				st.executeUpdate(creatTable);
				System.out.println("已经在数据库"+shujuku[1]+"中创建"+tableName[3].substring(1)+"表");
			}
			st.close();		
		 }
		 catch (ClassNotFoundException e) 
		 {
			System.err.println("Unable to load the JDBC/ODBC bridge.  "
						+ e.getMessage());
		 } 
		 catch (SQLException e) 
		 {
			System.err.println("Unable to connect to the database. "
					+ e.getMessage());
			System.exit(1);
		}
	}
	
	
}