把Excel表里面的数据导入到数据
package cn.labsoft.labos.utils;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Random;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import cn.labsoft.labos.framework.common.exception.GlobalException;
public class Excel2Db {
public static void main(String[] args){
Excel2Db re=new Excel2Db();
iDBManager2000 DbaObj = new iDBManager2000();
Connection con=null;
ResultSet rs=null;
PreparedStatement ps=null;
Date date =new Date();
long d=date.getTime();
int sheetNum=3;
for (int i = 0; i < sheetNum; i++) {
try {
String str="C:/Users/Administrator/Desktop/ItemName.xls";
String value[][]=null;
value=re.readExcel(str, i);
for (int j = 0; j < value.length-1; j++){
String cell[]= value[j];
//System.out.println(Arrays.asList(cell)+"------------------");
try {
if (DbaObj.OpenConnection()){
con=DbaObj.Conn;
String sqlinto="INSERT INTO v_klg_item(`id`,`create_time`,`last_upd_time`,`is_del`,`sort`,`version`,`name`,price,score) " +
"VALUES(?,?,?,?,?,?,?,?,?)";
String id =Excel2Db.getRandomString(32);
String itemName=cell[1];
//System.out.println(itemName);
ps=con.prepareStatement(sqlinto);
ps.setString(1,id);
ps.setLong(2, d);
ps.setLong(3, d);
ps.setString(4,"0");
ps.setInt(5, 0);
ps.setInt(6, 0);
ps.setString(7, itemName);
ps.setDouble(8, 0);
ps.setDouble(9, 0);
System.out.println(ps.toString()+"--------------");
ps.executeUpdate();
}else {
System.out.println("---------没有插入");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DbaObj.CloseConnection();
}
}
System.out.println("数据插入完成===============================================共"+(value.length-1)+"条");
}catch (Exception e) {
e.printStackTrace();
}finally{
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 生成随机字符串
* @param length
* @return String
*/
public static String getRandomString(int length) { //length表示生成字符串的长度
String base = "abcdef0123456789";
Random random = new Random();
StringBuffer sb = new StringBuffer();
for (int i = 0; i < length; i++) {
int number = random.nextInt(base.length());
sb.append(base.charAt(number));
}
return sb.toString();
}
/**
* 读取Excel表里的数据
* @param filePath
* @param sheetNum
* @return String[][]
* @throws Exception
*/
public String[][] readExcel(String filePath, int sheetNum) throws Exception {
try {
InputStream is = new FileInputStream(filePath);
// 创建一个工作薄Workbook
Workbook rwb = Workbook.getWorkbook(is);
// 检查工作表的有效性
int sheets = rwb.getNumberOfSheets();
if (sheetNum < 0 || sheetNum >= sheets || sheets == 0)
throw new Exception("没此工作表");
// 获取一个工作表Sheet
Sheet sht = rwb.getSheet(sheetNum);
//System.err.println("工作表: "+sht.getName()+sht.getRows());
// 获取行列值
Cell cell[] = null;
String value[][] = null;
value = new String[sht.getRows()][];
for (int r = 0; r < sht.getRows(); r++) {
cell = sht.getRow(r);
value[r] = new String[cell.length];
for (int c = 0; c < cell.length; c++) {
value[r][c] = cell[c].getContents();
}
}
if(null!=is){
is.close();
}
return value;
} catch (Exception e) {
//e.printStackTrace();
throw new GlobalException("" + e.getMessage());
}
}
}