package com.test;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class ExcelUtils2 {
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
InputStream is = new FileInputStream("D:/synchronize/user.xls");// 创建输入
jxl.Workbook rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0); // 读取第一个sheet
int colNum = rs.getColumns();// 列数
int rowNum = rs.getRows();// 行数
// getConntion();
// String tableSql = getCreateTableSql(rowNum, colNum);
// Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
// st.execute(tableSql);
// st.close();
// String sql = getColName(rowNum, colNum);
// PreparedStatement ps = null;
String strValue = "";
// ps = conn.prepareStatement(sql);
for (int i = 0; i < rowNum; i++) {
System.out.print(i + " -->" );
strValue = "";
for (int j = 0; j < colNum; j++) {
Cell c = rs.getCell(j, i);
strValue = c.getContents();
System.out.print(strValue + " " );
// ps.setString(j + 1, strValue);
}
System.out.println();
// ps.addBatch();
}
// ps.executeBatch();
// conn.commit();
// if (ps != null) {
// ps.close();
// }
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_connection_url = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8";
// String user_name = "root";
// String db_password = "root";
// Class.forName("com.mysql.jdbc.Driver");
// conn = DriverManager.getConnection(driver_connection_url, user_name,db_password);
// }catch(Exception e){
// e.printStackTrace();
// }
// }
static void close() {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
543453453
最新推荐文章于 2024-07-25 14:31:34 发布