/**
* @param path 要解析的excel文件路径
* @param dataTable 要写入到数据库中的表名
* @throws BiffException
* @throws IOException
*/
public boolean insert(String path,String dataTable) throws BiffException, IOException {
boolean falg = false;
DBUtils jdbc=new DBUtils();
File file = new File(path);
String fileName = file.getName();
String path_1 = path;
String path_2 = "d://"+fileName+"";
File f1= new File(path_1);
File f2= new File(path_2);
try {
forJava(f1,f2);
// 创建新的Excel 工作簿
Workbook rwb = null;
rwb = Workbook.getWorkbook(f2);
// 得到工作簿中的第一个表索引即为excel下的sheet1,sheet2,sheet3...
Sheet sheet = rwb.getSheets()[0];
int rsColumns = sheet.getColumns();// 列数
int rsRows = sheet.getRows();// 行数
String simNumber = "" ;//每个单元格中的数据
System.out.println("列数:"+rsColumns);
System.out.println("行数:"+rsRows);
String str="";//拼接要插入的列
//循环行数
for (int i = 1; i < rsRows; i++) {
SimpleDateFormat form = new SimpleDateFormat("yyMMddHHmmssSSS");
long id = Long.parseLong(form.format(new Date()));
System.out.println(id);
String StringId = String.valueOf(i);
//此处求推荐好方法
if(StringId.length() == 1){
StringId = "000"+StringId ;
}
if(StringId.length() == 2){
StringId = "00"+StringId ;
}
if(StringId.length() == 3){
StringId = "0"+StringId ;
}
String sql = "insert into "+dataTable+" values('"+id+StringId+"',";//拼接sql
//循环列数
for (int j = 0; j < rsColumns; j++) {
Cell cell = sheet.getCell(j, i);
simNumber = cell.getContents();
if(j==rsColumns-1){
sql += "'"+ simNumber+"'" ;
}else{
if(j == 20 || j == 21 ){
sql += ""+ simNumber+"," ;
}else{
sql += "'"+ simNumber+"'," ;
}
}
}
sql += ")";
System.out.println(sql);
falg = jdbc.executeUpdate(sql);//执行sql
if(!falg){
f2.delete();
jdbc.closeStmt();
jdbc.closeConnection();
return false;
}
}
if(falg){
f2.delete();
jdbc.closeStmt();
jdbc.closeConnection();
return true;
}
} catch (Exception e) {
f2.delete();
e.printStackTrace();
return false;
}finally{
f2.delete();
jdbc.closeStmt();
jdbc.closeConnection();
}
return false;
}
//复制excel
public static long forJava(File f1,File f2) throws Exception{
long time=new Date().getTime();
int length=2097152;
FileInputStream in=new FileInputStream(f1);
FileOutputStream out=new FileOutputStream(f2);
byte[] buffer=new byte[length];
while(true){
int ins=in.read(buffer);
if(ins==-1){
in.close();
out.flush();
out.close();
return new Date().getTime()-time;
}else
out.write(buffer,0,ins);
}
}