java EXCEL 数据 导入 Mysql

public class ImportExcel {

private static final Logger logger = Logger.getLogger(ImportExcel.class);

private static String createTableSql="";//创建数据库的sql

private static String colType = "varchar(200)";//字段类型

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

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

private static String tableName="Excel_Import_Data";//表名称

private static String colName="col"; //默认字段名

private static Connection conn = null;

private static SessionFactory session = new SessionFactory();

public static void main(String[] args){

try{

logger.debug("start load file-------------------------");

String separator = File.separator ;

InputStream in = null; 

in = new FileInputStream("C:" +separator + "Users"

+ separator +"temp"+ separator +"Desktop"

+ separator +"员工生日提醒"+ separator 

+"人员情况-生日发送.xls");//创建输入

jxl.Workbook rwb = Workbook.getWorkbook(in);

Sheet[] sheet = rwb.getSheets();

Sheet rs = rwb.getSheet(0); //读取第一个sheet

int colNum = rs.getColumns();//列数

int rowNum = rs.getRows();//行数

logger.debug("colNum rowNum------------------"+rowNum+","+colNum);

conn = session.getConnection();

session.startTransaction(); //开启事务

try {

logger.debug("----create table start -------------------------");

String tableSql = getCreateTableSql(rowNum,colNum,rs);

//String tableSql = "reate table " + tableName + "";

Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

st.execute(tableSql);

session.commitTransaction();

st.close();

logger.debug("---create table end -------------------------");

} catch (Exception ee) {

session.rollbackTransaction();

session.colseConnection();

logger.debug("create table error session start rollbackTransaction................");

logger.error(ee);

}

try {

String sql = getColName(rowNum,colNum,rs);

PreparedStatement ps=null;

String strValue="";

ps = conn.prepareStatement(sql);

for(int i=0;i<rowNum;i++){

logger.debug("########################## index row : " + i + "##########################");

if(i == 0 || i == 1){

continue;

}

strValue="";

for(int j=0; j<colNum; j++){

Cell cc = rs.getCell(j, 1);

String name = cc.getContents();

Cell c = rs.getCell(j, i);

strValue = c.getContents();

strValue = strValue.trim().replace("#N/A", "");

logger.debug("index: " + j+1 + "  ," + name + ": " + strValue);

ps.setString(j+1,strValue.trim());

}

ps.addBatch();

}

ps.executeBatch();

session.commitTransaction();

if(ps!=null){

ps.close();

}

} catch (Exception ee) {

session.rollbackTransaction();

session.colseConnection();

logger.debug("insert data session start rollbackTransaction................");

logger.error(ee);

}

session.colseConnection();

}catch(Exception e){

try {

session.rollbackTransaction();

session.colseConnection();

logger.debug("session start rollbackTransaction................");

} catch (Exception ee) {

logger.error(ee);

}

logger.error(e);

}

}

private static String getColName(int rowNum, int colNum,Sheet rs) {

logger.debug("|---->getColName(int rowNum: " + rowNum + " , int colNum: " + colNum + ")");

//可以做成可配置文件

String colSql="";

String colValue="";

String insertData = "insert";

for(int i=0; i<colNum; i++){

Cell c = rs.getCell(i, 1);

String strValue = c.getContents();

strValue = strValue.trim().replace("#N/A", "");

colSql += "`" + strValue +"`";

colValue += ""+"?";

if(i < colNum -1){

colSql +=",";

colValue += ",";

}

};

logger.debug("colSql:" + colSql);

colName = "insert into "+tableName+" ("+colSql+")values("+colValue+")";

logger.debug("return : " + colName);

return colName;

}

private static String getCreateTableSql(int rowNum, int colNum,Sheet rs) {

logger.debug("|---->getCreateTableSql(int rowNum: " + rowNum + " , int colNum: " + colNum + ")");

createTableSql="create table "+tableName+"( `id` bigint(12) NOT NULL auto_increment, ";

String temp="";

for(int i=0; i<colNum; i++){

Cell c = rs.getCell(i, 1);

String strValue = c.getContents();

strValue = strValue.trim().replace("#N/A", "");

logger.debug("col index: " + i + "  ,strValue:" + strValue);

temp += "`"+ strValue +"` "+ colType +" DEFAULT NULL,";

}

createTableSql +=" "+ temp +" PRIMARY KEY (`id`)" +

") ENGINE="+ENGINE+" DEFAULT CHARSET="+charSet+";";

logger.debug("return: " + createTableSql);

return createTableSql;

}

}