Java对Excel导入数据库功能实例
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(); } } }}