excel数据导入到指定的表中（名称与表字段不对应）
packagecom.service.compare;
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.IOException;
importjava.io.InputStream;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;
importorg.apache.poi.hssf.eventmodel.EventRecordFactory;
importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;
importorg.apache.poi.openxml4j.opc.OPCPackage;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.ss.usermodel.Workbook;
importorg.apache.poi.ss.usermodel.WorkbookFactory;
importcom.service.upfile.Link_Database;
publicclassCom_import{
privateConnectionconn=null;
PreparedStatementpstmt=null;
publicvoidconnect(){
try{
Link_Databasedemo=newLink_Database();
conn=demo.connect();
}catch(Exceptione){
e.printStackTrace();
}
}
publicvoidclose(){
try{
this.conn.close();
}catch(Exceptione){
}
}
publicvoidtest(Stringpath,Stringxmb_bm,Stringcharge_date){
List<Integer>list=newArrayList<Integer>();
try{
//OPCPackagepkg=OPCPackage.open(path);
InputStreamis=newFileInputStream(path);
Workbookwb=WorkbookFactory.create(newFile(path));
Sheetsheet=wb.getSheetAt(0);
for(inti=0;i<sheet.getLastRowNum();i++){
Rowrow=sheet.getRow(i);
if(row!=null){
if(i==0){
Stringstr="电话号码,客户名称,来显,月租,宽带月租,铜缆租用,维护费";
for(intj=0;j<row.getLastCellNum();j++){
Cellcell=row.getCell(j);
if(cell==null||cell.toString().trim().length()==0){
}elseif(str.indexOf(cell.toString())!=-1){
list.add(j);
}
}
}else{
StringBufferstr_charge=newStringBuffer();
for(intw=0;w<list.size();w++){
Cellcell=row.getCell(list.get(w));
if(cell==null||cell.toString().trim().length()==0){
str_charge.append("'0',");
}else{
str_charge.append("'"+cell.toString()+"',");
}
}
if(!str_charge.toString().substring(0,7).equals("'0','0'")){
Stringstr=str_charge.toString().substring(0,str_charge.lastIndexOf(","));
this.import_date(str,xmb_bm,charge_date);
}
}
}
}
}catch(InvalidFormatExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(IOExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
publicvoidimport_date(Stringargs,Stringxmb_bm,Stringcharge_date){
try{
Stringsql_bdth="insertintoCAIWU_COMPARE(TELNUMBER,CUSTOMER,CID_CHARGE,JB_YZF,KD_YZF,TL_YZF,WH_CHARGE,XMB_BM,CHARGE_DATE)values("+args+",'"+xmb_bm+"','"+charge_date+"')";
pstmt=conn.prepareStatement(sql_bdth);
pstmt.execute();
pstmt.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
publicvoiddelete(Stringxmb_bm,Stringcharge_date){
try{
Stringsql="deletefromCAIWU_COMPAREwhereCHARGE_DATE='"+charge_date+"'andXMB_BM='"+xmb_bm+"'";
pstmt=conn.prepareStatement(sql);
pstmt.execute();
pstmt.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
publicstaticvoidmain(String[]args){
Com_importdemo=newCom_import();
demo.connect();
demo.test("D:/desktop/charge_off_data/新新新.xls","TD","2013-01");
//demo.test("D:/apache-tomcat-6.0.30/webapps/yzxt/data/2013-01/话费清单.xlsx","TD","2013-01");
demo.close();
}
}