java将excel导入数据库（）

package com.jay.knowledge;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

/**
 *  作者：Jay
 *  功能：学习用java将excel导入数据库中，任意格式excel
 *  日期：2013.11.13
 */
public class TestImportExcel {
	// 创建数据库的sql
	static String createTableSql = "";
	// 字段类型
	static String colType = "TEXT";
	// 主键
	static String key = "id";
	// 表格字符类型
	static String charSet = "utf8";
	// 表格类型
	static String ENGINE = "InnoDB";
	// 表名称
	static String tableName = "tempExcelToMysql";
	// 默认字段名
	static String colName = "col";
	// 数据库连接
	static Connection conn = null;

	public static void main(String args[]) {
		try {
			// 构建Workbook对象, 只读Workbook对象
			// 直接从本地文件创建Workbook
			// 从输入流创建Workbook
			System.out.println("start load file-------------------------");
			// 创建输入流
			InputStream is = new FileInputStream("E:/users.xls");
			Workbook rwb = Workbook.getWorkbook(is);
			// 读取第一个sheet
			Sheet rs = rwb.getSheet(0); 
			// 列数
			int colNum = rs.getColumns();
			// 行数
			int rowNum = rs.getRows();
			System.out.println("colNum rowNum------------------" + rowNum + ","+ colNum);
			System.out.println("start create ｂａｓｅ-------------------------");
			//创建连接
			getConntion();
			//获取创建table的sql
			String tableSql = getCreateTableSql(rowNum, colNum);
			//
			Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			st.execute(tableSql);
			st.close();
			System.out.println("create ｂａｓｅ end -------------------------");

			//获取插入数据的sql
			String sql = getColName(rowNum, colNum);
			PreparedStatement ps = null;
			String strValue = "";
			ps = conn.prepareStatement(sql);
			//为参数设置值
			for (int i = 0; i < rowNum; i++) {
				strValue = "";
				for (int j = 0; j < colNum; j++) {
					Cell c = rs.getCell(j, i);
					strValue = c.getContents();
					ps.setString(j + 1, strValue);
				}
				ps.addBatch();
			}
			ps.executeBatch();
			conn.commit();

			if (ps != null) {
				ps.close();
			}
			System.out.println(" insert end-------------------------");
			close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 功能：组装创建表格的sql
	 * @param rowNum:行数
	 * @param colNum：列数
	 * @return String：创建表格的sql
	 */
	static String getCreateTableSql(int rowNum, int colNum) {
		// 可以做成可配置文件
		createTableSql = "create table " + tableName + "( `" + key
				+ "` bigint(12) NOT NULL auto_increment, ";
		String temp = "";
		for (int j = 0; j < colNum; j++) {
			temp = temp + "`" + colName + j + "` " + colType + " DEFAULT NULL,";
		}
		createTableSql = createTableSql + " " + temp + " PRIMARY KEY (`" + key
				+ "`)" + ") ENGINE=" + ENGINE + " DEFAULT CHARSET=" + charSet
				+ ";";
		return createTableSql;
	}


	/**
	 * 功能：组装向数据库表中插入数据的sql
	 * @param rowNum:行数
	 * @param colNum：列数
	 * @return String：插入数据的sql
	 */
	static String getColName(int rowNum, int colNum) {
		// 可以做成可配置文件
		String colSql = "";
		String colValue = "";
		for (int j = 0; j < colNum; j++) {
			colSql = colSql + "`" + colName + j + "`,";
			colValue = colValue + "" + "?,";

		}
		return "insert into " + tableName + " ("
				+ colSql.substring(0, colSql.lastIndexOf(",")) + ")values("
				+ colValue.substring(0, colValue.lastIndexOf(",")) + ")";
	}

	/**
	 * 功能：创建数据库连接
	 * @return void
	 */
	static void getConntion() {
		try {
			String driver_class = "com.mysql.jdbc.Driver";
			String connection_url = "jdbc:mysql://localhost:3306/webserve?useUnicode=true&characterEncoding=utf-8";
			String user_name = "root";
			String db_password = "123456";
			Class.forName(driver_class);
			conn = DriverManager.getConnection(connection_url, user_name,db_password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 功能：关闭数据连接
	 * @return void
	 */
	static void close() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}