您的位置：首页 > 博客中心 > 数据库 >

exl导入数据库

时间：2022-03-14 02:27

java]

/**
* @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<Object>> list = new ArrayList<List<Object>>();
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<rownum;i++){
List<Object> row = new ArrayList<Object>();
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.size();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;
}