批量导入时间oracle excel,读取Excel数据、批量导入到Oracle数据库

/**

* @Description: 得到Excel文档，把文档中的数据批量导入到数据库中

* 1、找到上传的数据，2、把数据放到List集合中，3、把List集合中的数据更新到数据库

* @return void

* @throws

*/

public String cmdImpQxsj() {

String sysGuid = getWorkDTO().getAsString("sysGuid");

String webPath = WebConfig.getInstance().getContext_path();

String filePath = this.getFilePath(sysGuid);

String longPath = webPath+filePath;

HSSFWorkbook workbook = POIExcelUtil.getExistHSSFWorkbook(new File(longPath));

List> list = new ArrayList>();

if (workbook != null) {

HSSFSheet sheet = workbook.getSheetAt(0);

if (sheet != null) {

int rownum = sheet.getLastRowNum();

int columnnum = sheet.getRow(0).getLastCellNum();

for(int i=3;i

List row = new ArrayList();

Object value = "";

for (int j = 1; j < columnnum-1; j++) {

Cell cell = sheet.getRow(i).getCell(j);

if (cell == null) {

row.add("");

} else if (j==2 || j==3 || j == 5

|| j==6 || j==7 || j == 9 || j==10

|| j==11 || j == 12 || j==13 || j == 15

|| j==16 || j==18 || j == 19 || j==20) {

if(cell.getStringCellValue() != null || !"".equals(cell.getStringCellValue())){

value = cell.getStringCellValue();

row.add(value);

}

}else if(j == 1){

if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

int d = (int)cell.getNumericCellValue();

String numb = d+"";

row.add(numb);

}else if(cell.getStringCellValue() != null || !"".equals(cell.getStringCellValue())){

value = cell.getStringCellValue();

row.add(value);

}

}else if (j == 4 || j == 14 || j == 17){

if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

value = cell.getDateCellValue();

} else {

value = cell.getStringCellValue();

}

if (value == null || "".equals(value.toString())) {

row.add("");

} else {

SimpleDateFormat sdf = new SimpleDateFormat(

"yyyy-MM-dd HH:mm");

if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

try {

value = sdf.format(value);

} catch (Exception e) {

value = "";

}

}

row.add(value);

}

}else if (j == 8){

if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

value = cell.getDateCellValue();

} else {

value = cell.getStringCellValue();

}

if (value == null || "".equals(value.toString())) {

row.add("");

} else {

SimpleDateFormat sdf = new SimpleDateFormat(

"yyyy-MM-dd HH:mm:ss");

if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

try {

value = sdf.format(value);

} catch (Exception e) {

value = "";

}

}

row.add(value);

}

}

}

list.add(row);

}

try {

impToDB(list);

getWorkDTO().put("jsonString", "{success:true}");

} catch (SQLException e) {

getWorkDTO().put("jsonString", "{failure:true}");

e.printStackTrace();

return ICnsExResult.RUSULT_ERROR;

}

}

}

return ICnsExResult.RUSULT_SUCCESS;

}

/**

* @throws SQLException

* @Description: 把Excel数据导入到数据库

* @return void

* @throws

*/

@SuppressWarnings("static-access")

public void impToDB(List> list) throws SQLException{

String sql = "";

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd KK:mm:ss");

String cretDate = format.format(new Date());//导入时间

String jobID = getSessionDTO().getAsString(SessionDTO.LOGIN_USR_JOBID);//导入上传人工号

sql = "insert into us_app.tb_ope_qxsc(guid,qxgd,qxmc,gddqzt," +

"dqztxgsj,clqk,gdsqr,sqrzb,fxsj,zy,jzmc,qxlb,sbmc,jxr," +

"jxyssj,wxr,xqr,xqsj,tjpzr,bz,gzpbh,scsj,scgh) " +

"values(sys_guid(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

Connection con = this.baseDAO.toGetSession().connection();

PreparedStatement ps = con.prepareStatement(sql);

final int batchSize = 60; //设置批处理数据的条数

int count = 0;

for(int p=0;p

List> singleList = (List>)list.get(p);

/*try {

fileDate = new SimpleDateFormat("yyyy-MM-dd KK:mm:ss").parse(cretDate);

date1 = sdf.parse((String) singleList.get(3));

date2 = fmat.parse((String) singleList.get(7));

date3 = sdf.parse((String) singleList.get(13));

date4 = sdf.parse((String) singleList.get(16));

} catch (ParseException e) {

e.printStackTrace();

} */

ps.setString(1, (String) singleList.get(0));

ps.setString(2, (String) singleList.get(1));

ps.setString(3, (String) singleList.get(2));

if("".equals(singleList.get(3))){

ps.setTimestamp(4, null);

}else{

ps.setTimestamp(4, returnDate((String) singleList.get(3)));

}

ps.setString(5, (String) singleList.get(4));

ps.setString(6, (String) singleList.get(5));

ps.setString(7, (String) singleList.get(6));

if("".equals(singleList.get(7))){

ps.setTimestamp(8, null);

}else{

ps.setTimestamp(8, returnDate2((String) singleList.get(7)));

}

ps.setString(9, (String) singleList.get(8));

ps.setString(10, (String) singleList.get(9));

ps.setString(11, (String) singleList.get(10));

ps.setString(12, (String) singleList.get(11));

ps.setString(13, (String) singleList.get(12));

if("".equals(singleList.get(13))){

ps.setTimestamp(14, null);

}else{

ps.setTimestamp(14, returnDate((String) singleList.get(13)));

}

ps.setString(15, (String) singleList.get(14));

ps.setString(16, (String) singleList.get(15));

if("".equals(singleList.get(16))){

ps.setTimestamp(17, null);

}else{

ps.setTimestamp(17, returnDate((String) singleList.get(16)));

}

ps.setString(18, (String) singleList.get(17));

ps.setString(19, (String) singleList.get(18));

ps.setString(20, (String) singleList.get(19));

ps.setTimestamp(21, new java.sql.Timestamp(new java.util.Date().getTime()));

ps.setString(22, jobID);

ps.addBatch();

if(++count % batchSize == 0) {

ps.executeBatch();

ps.clearBatch();

}

}

ps.executeBatch();

ps.clearBatch();

}

public Timestamp returnDate(String date){

Timestamp dateTime = null;

try{

DateFormat dateFormat;

dateFormat = new SimpleDateFormat("yyyy-MM-dd kk:mm",Locale.ENGLISH);//设定格式

dateFormat.setLenient(false);

java.util.Date timeDate = dateFormat.parse(date);//util类型

dateTime = new java.sql.Timestamp(timeDate.getTime());//Timestamp类型,timeDate.getTime()返回一个long型

}catch(Exception ex){

ex.printStackTrace();

}

return dateTime;

}

public Timestamp returnDate2(String date){

Timestamp dateTime = null;

try{

DateFormat dateFormat;

dateFormat = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss",Locale.ENGLISH);//设定格式

dateFormat.setLenient(false);

java.util.Date timeDate = dateFormat.parse(date);//util类型

dateTime = new java.sql.Timestamp(timeDate.getTime());//Timestamp类型,timeDate.getTime()返回一个long型

}catch(Exception ex){

ex.printStackTrace();

}

return dateTime;

}