使用webwork上传excel并导入数据库

package action; import java.io.File; 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.Sheet; import jxl.Workbook; import com.opensymphony.xwork.Action; /** * @author fengzhizi */ public class FileUploadAction implements Action{ File picture; String directory; 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; /* (non-Javadoc) * @see com.opensymphony.xwork.Action#execute() */ public String execute() throws Exception { picture.renameTo(new File(getPicturePath())); try { // 构建Workbook对象, 只读Workbook对象 // 直接从本地文件创建Workbook // 从输入流创建Workbook System.out.println("start load file-------------------------"); InputStream is = new FileInputStream(getPicturePath());// 创建输入 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(); conn.setAutoCommit(false); 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 base end -------------------------"); 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(); } return SUCCESS; } /** * @param directory The directory to set. */ public void setDirectory(String directory) { this.directory = directory; } /** * @return Returns the PicturePath. */ public String getPicturePath() { return directory+picture.getName(); } /** * @param picture The picture to set. */ public void setPicture(File picture) { this.picture = picture; } 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/exceltest?useUnicode=true&characterEncoding=utf-8"; String user_name = "root"; String db_password = "715523715"; 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(); } } } }