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