需要注意的地方:Workbook不能读取xlsx文件,另存为xls文件就行了
package com.testimport;
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;
public class ImportFileTo {
public ImportFileTo() {
}
static String createTableSql = "excel2";// 创建数据库的sql
// static String colType = "TEXT";// 字段类型
// static String key = "id";// 主键
// static String charSet = "utf8";// 表格字符类型
// static String ENGINE = "InnoDB";// 表格类型
static String tableName = "testimport";// 表名称
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("e:\\testimport.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 base-------------------------");
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 base 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 = 1; i < rowNum; i++) {
strValue = "";
System.out.println("本行第一列--"+rs.getCell(0, i).getContents());
System.out.println("上一行第一列--"+rs.getCell(0, i - 1).getContents());
if (rs.getCell(0, i).getContents().equals(rs.getCell(0, i - 1).getContents())) {
// 跳过
System.out.println("跳出");
continue;
} else {
for (int j = 0; j < colNum; j++) {
Cell c = rs.getCell(j, i);
strValue = c.getContents();
System.out.println(j + "----" + i);
ps.setString(j + 1, strValue);
}
ps.addBatch();
}
}
try {
ps.executeBatch();
} catch (Exception e) {
e.printStackTrace();
}
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
// + ";";
createTableSql = "CREATE TABLE testimport"
+ "(code character varying(10),"
+ " name character varying(20),"
+ "remarks character varying(200))" + "WITH (OIDS=FALSE);"
+ "ALTER TABLE testimport OWNER TO disaster; ";
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 + "" + "?,";
}
String hql = "insert into " + tableName + " values("
+ colValue.substring(0, colValue.lastIndexOf(",")) + ")";
return hql;
// return "insert into " + tableName + " ("
// + colSql.substring(0, colSql.lastIndexOf(",")) + ")values("
// + colValue.substring(0, colValue.lastIndexOf(",")) + ")";
}
static void getConntion() {
try {
String driver_class = "org.postgresql.Driver";
String connection_url = "jdbc:postgresql://192.168.0.106:5432/disasterTest?useUnicode=true&characterEncoding=utf-8";
String user_name = "disaster";// 输入数据库的用户名
String db_password = "disaster";// 输入数据库的密码
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();
}
}
}
// public static void main(String args[]) {
// System.out.println(" this is a test ");
// Connection conn = null;
// String url = "jdbc:postgresql://192.168.0.106:5432/disasterTest";
// try {
// Class.forName("org.postgresql.Driver");
// } catch (ClassNotFoundException e1) {
// // TODO Auto-generated catch block
// e1.printStackTrace();
// }
//
// try {
// conn = DriverManager.getConnection(url, "disaster", "disaster");
// } catch (SQLException e1) {
// // TODO Auto-generated catch block
// e1.printStackTrace();
// }
//
// String sql =
// "copy testimport from 'e:/1.csv' delimiter as',' csv quote as '"'";
// Statement stmt = null;
// ResultSet rs = null;
// try {
// stmt = conn.createStatement();
// rs = stmt.executeQuery(sql);
// while (rs.next()) {
// System.out.println(rs.getInt(1));
// }
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
}