package com.action.payslip;

import java.io.FileInputStream;
import java.io.InputStream;
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.CellType;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;

public class PayslipExcelImport {
	
	 static String createTableSql = "";// 创建数据库的sql
	 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("D:/user.xls");// 创建输入
	   jxl.Workbook rwb = Workbook.getWorkbook(is);
	   Sheet rs = rwb.getSheet(0); // 读取第一个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 = "";
	   ps = conn.prepareStatement(sql);
	   for (int i = 1; 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/jeecg?useUnicode=true&characterEncoding=utf-8";
	   String user_name = "root";
	   String db_password = "root";
	   Class.forName(driver_class);
	   conn = DriverManager.getConnection(connection_url, user_name,
	     db_password);
	   conn.setAutoCommit(false);
	  } catch (Exception e) {
	   e.printStackTrace();
	  }
	 }
	 static void close() {
	  if (conn != null) {
	   try {
	    conn.close();
	   } catch (SQLException e) {
	    // TODO Auto-generated catch block
	    e.printStackTrace();
	   }
	  }
	 }

}