struts1利用jxl将xml表格数据分类导入数据库不同的表中-白红宇

struts1利用jxl将xml表格数据分类导入数据库不同的表中

阅读量：5264 次

发布时间：2019-06-14

本文共 4304 字，大约阅读时间需要 14 分钟。

需要导入的jar包

import jxl.Sheet;

import jxl.Workbook;

/**

* 功能描述：导入客户信息

* @author row行 column列

* @date Mar 9, 2015 22:16:49 PM

public boolean importDgkhdbqk(InputStream instream){

boolean bool=false;

SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");

Calendar calendar = Calendar.getInstance();

Connection conn=DBConn.getConnection();

Workbook wb=null;

try{

wb=Workbook.getWorkbook(instream);

}catch(Exception ex){

ex.getStackTrace();

}

Sheet sheet=wb.getSheet(0);

String DBHTLX = "";

String YPLX = "";

String YPQSRLX = "";

String YPQSRZJLX = "";

String YPQSRDSF = "";

String tjsj = "";

String tj_year="";

String tj_month="";

String tbjg="";

String sqli1="";

String sqld="";

tbjg=sheet.getCell(2,2).getContents();

tj_year=(sheet.getCell(12,1).getContents()).substring(0,4);

tj_month=(sheet.getCell(13,1).getContents()).substring(0, 2);

calendar.set(Calendar.YEAR,Integer.parseInt(tj_year));

calendar.set(Calendar.MONTH,Integer.parseInt(tj_month));

int lastday = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);

tjsj=tj_year+tj_month+lastday;

ResultSet rs=null;

Statement p=null;

try {

p=conn.createStatement();

} catch (SQLException e1) {

e1.printStackTrace();

}

try{

//导入担保信息

sqld="delete from DHFX_DBTZ where tjsj='"+tjsj+"' and tbjg='"+tbjg+"'";

p.addBatch(sqld);

int i = 6;

while(!(null==sheet.getCell(1,i).getContents()||"".equals(sheet.getCell(1,i).getContents()))){

StringBuffer sql=new StringBuffer();

DBHTLX=sheet.getCell(5,i).getContents();

YPLX=sheet.getCell(6,i).getContents();

YPQSRLX=sheet.getCell(10,i).getContents();

YPQSRZJLX=sheet.getCell(11,i).getContents();

YPQSRDSF=sheet.getCell(14,i).getContents();

if(!(null==DBHTLX||"".equals(DBHTLX))){

DBHTLX=sheet.getCell(5,i).getContents().substring(0, 1);

}

if(!(null==YPLX||"".equals(YPLX))){

YPLX=sheet.getCell(6,i).getContents().substring(0, 2);

}

if(!(null==YPQSRLX||"".equals(YPQSRLX))){

YPQSRLX=sheet.getCell(10,i).getContents().substring(0, 1);

}

if(!(null==YPQSRZJLX||"".equals(YPQSRZJLX))){

YPQSRZJLX=sheet.getCell(11,i).getContents().substring(0, sheet.getCell(11,i).getContents().indexOf("-"));

}

if(!(null==YPQSRDSF||"".equals(YPQSRDSF))){

YPQSRDSF=sheet.getCell(14,i).getContents().substring(0, 1);

}

sql.append("insert into DHFX_DBTZ (NUMB,KHMC,KHH,JJH,DBHTH,DBHTLX,YPLX,YPMC,YPDM,YPQSRMC,YPQSRLX,YPQSRZJLX,YPQSRZJDM,YPPGJZ,YPQSRDSF,SCGZRQ,ZXGZRQ,GZDQRQ,BZRBZNLSX,SPDZYL,TBJG,TJSJ) values (");

sql.append("'"+sheet.getCell(0,i).getContents()+"',");

sql.append("'"+sheet.getCell(1,i).getContents()+"',");

sql.append("'"+sheet.getCell(2,i).getContents()+"',");

sql.append("'"+sheet.getCell(3,i).getContents()+"',");

sql.append("'"+sheet.getCell(4,i).getContents()+"',");

sql.append("'"+DBHTLX+"',");

sql.append("'"+YPLX+"',");

sql.append("'"+sheet.getCell(7,i).getContents()+"',");

sql.append("'"+sheet.getCell(8,i).getContents()+"',");

sql.append("'"+sheet.getCell(9,i).getContents()+"',");

sql.append("'"+YPQSRLX+"',");

sql.append("'"+YPQSRZJLX+"',");

sql.append("'"+sheet.getCell(12,i).getContents()+"',");

sql.append("'"+sheet.getCell(13,i).getContents()+"',");

sql.append("'"+YPQSRDSF+"',");

sql.append("'"+sheet.getCell(15,i).getContents()+"',");

sql.append("'"+sheet.getCell(16,i).getContents()+"',");

sql.append("'"+sheet.getCell(17,i).getContents()+"',");

sql.append("'"+sheet.getCell(18,i).getContents()+"',");

sql.append("'"+sheet.getCell(19,i).getContents()+"',");

sql.append("'"+tbjg+"',");

sql.append("'"+tjsj+"'");

sql.append(")");

i++;

System.out.println(sql);

p.addBatch(sql.toString());

}

sqli1="insert into dhfx_dbxx (KHH,JJH,DBHTH,DBHTLX,YPLX,YPMC,YPDM,YPQSRMC,YPQSRLX,YPQSRZJLX,YPQSRZJDM,YPPGJZ,YPQSRDSF,SCGZRQ,ZXGZRQ,GZDQRQ,BZRBZNLSX,SPDZYL,TJSJ,ZHXGSJ) select KHH,JJH,DBHTH,DBHTLX,YPLX,YPMC,YPDM,YPQSRMC,YPQSRLX,YPQSRZJLX,YPQSRZJDM,YPPGJZ,YPQSRDSF,SCGZRQ,ZXGZRQ,GZDQRQ,BZRBZNLSX,SPDZYL,TJSJ,to_char(sysdate,'yyyymmddhh24miss') as ZHXGSJ from DHFX_DBTZ WHERE NOT EXISTS (select 1 from dhfx_dbxx where DHFX_DBTZ.khh=dhfx_dbxx.khh)";

p.addBatch(sqli1);

p.executeBatch();

p.close();

conn.commit();

bool=true;

}catch(Exception ex){

try{

conn.rollback();

}catch(Exception e){

}

ex.getStackTrace();

System.out.println("异常信息"+ex.getMessage());