excel转成数据库

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.*;

public class test {
 static String createTableSql = "excel";// 创建数据库的sql

 static String colType = "TEXT";// 字段类型

 static String key = "id";// 主键

 static String charSet = "utf8";// 表格字符类型

 static String ENGINE = "InnoDB";// 表格类型

 static String tableName = "et0";// 表名称

 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("C://Users//Administrator//Desktop//1.xls");// 创建输入

   jxl.Workbook rwb = Workbook.getWorkbook(is);
   Sheet rs = rwb.getSheet(1); // 读取第一个sheet
   int colNum = rs.getColumns();// 列数
   int rowNum = rs.getRows();// 行数

   System.out.println("colNum rowNum------------------" + rowNum + ","
     + colNum);
   System.out.println("start create ｂａｓｅ-------------------------");

   getConntion();

   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 -------------------------");

   String sql = getColName(rowNum, colNum);
   PreparedStatement ps = null;
   String strValue = "";
   conn.setAutoCommit(false);
   ps = conn.prepareStatement(sql);
   // System.out.println("rownum===================" + rowNum);
   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();
  }
 }

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

 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(",")) + ")";
 }

 static void getConntion() {

  try {
   String driver_class = "com.mysql.jdbc.Driver";
   String connection_url = "jdbc:mysql://localhost:3306/excel?useUnicode=true&characterEncoding=utf-8";
   String user_name = "root";// 输入数据库的用户名
   String db_password = "112221";// 输入数据库的密码

   Class.forName(driver_class);
   conn = DriverManager.getConnection(connection_url, user_name,
     db_password);
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 static void close() {
  if (conn != null) {
   try {
    conn.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }
}