diff --git a/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java b/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java index 3ad408d..9492d9a 100644 --- a/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java +++ b/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java @@ -16,11 +16,13 @@ import com.google.gson.Gson; import jxl.*; import jxl.read.biff.BiffException; +import jxl.write.DateFormat; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang3.math.NumberUtils; +import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.MatchMode; @@ -464,12 +466,16 @@ if(isEmptyRow(sheet.getRow(i))) continue; MarkerDto markerDto = new MarkerDto(); markerDto.setMarkerId(sheet.getCell(indexMap.get("markerId"), i).getContents()); +// String idstr = sheet.getCell(indexMap.get("id"), i).getContents(); +// if(NumberUtils.isDigits(idstr)){ +// markerDto.setId(Long.valueOf(idstr)); +// } if(indexMap.containsKey("id")){ String idstr = sheet.getCell(indexMap.get("id"), i).getContents(); if(NumberUtils.isDigits(idstr)){ markerDto.setId(Long.valueOf(idstr)); MarkerDto marker = getMarkerById(markerDto.getId());//保证id和markerId对应，没有更改 - if(!marker.getMarkerId().equals(marker.getMarkerId())) return 5; + if(!markerDto.getMarkerId().equals(marker.getMarkerId())) return 5; }else if(StringUtils.isNotBlank(idstr)){ return 5; } @@ -508,12 +514,22 @@ markerDto.setLatitude(latCell.getValue()); markerDto.setIsValid(1); markerDto.setRecordType(recordTypeFlag); - markerDto.setCreateTime(sheet.getCell(indexMap.get("createTime"), i).getContents()); + String dateString =sheet.getCell(indexMap.get("creator"), i).getContents(); + Cell timeCell = sheet.getCell(indexMap.get("createTime"), i); + if(timeCell.getType()==CellType.DATE){ + DateCell dc = (DateCell) timeCell; + Date dateValue = dc.getDate(); + SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); + dateString = formatter.format(dateValue); + } + markerDto.setCreateTime(dateString); markerDto.setCreator(sheet.getCell(indexMap.get("creator"), i).getContents()); markerDto.setMemo(sheet.getCell(indexMap.get("memo"), i).getContents()); markerDtos.add(markerDto); } + book.close(); + this.getSession().clear(); batchSave(markerDtos, userCompanyId); } catch (BiffException e) { logger.error(e.getMessage()); diff --git a/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java b/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java index 3ad408d..9492d9a 100644 --- a/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java +++ b/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java @@ -16,11 +16,13 @@ import com.google.gson.Gson; import jxl.*; import jxl.read.biff.BiffException; +import jxl.write.DateFormat; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang3.math.NumberUtils; +import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.MatchMode; @@ -464,12 +466,16 @@ if(isEmptyRow(sheet.getRow(i))) continue; MarkerDto markerDto = new MarkerDto(); markerDto.setMarkerId(sheet.getCell(indexMap.get("markerId"), i).getContents()); +// String idstr = sheet.getCell(indexMap.get("id"), i).getContents(); +// if(NumberUtils.isDigits(idstr)){ +// markerDto.setId(Long.valueOf(idstr)); +// } if(indexMap.containsKey("id")){ String idstr = sheet.getCell(indexMap.get("id"), i).getContents(); if(NumberUtils.isDigits(idstr)){ markerDto.setId(Long.valueOf(idstr)); MarkerDto marker = getMarkerById(markerDto.getId());//保证id和markerId对应，没有更改 - if(!marker.getMarkerId().equals(marker.getMarkerId())) return 5; + if(!markerDto.getMarkerId().equals(marker.getMarkerId())) return 5; }else if(StringUtils.isNotBlank(idstr)){ return 5; } @@ -508,12 +514,22 @@ markerDto.setLatitude(latCell.getValue()); markerDto.setIsValid(1); markerDto.setRecordType(recordTypeFlag); - markerDto.setCreateTime(sheet.getCell(indexMap.get("createTime"), i).getContents()); + String dateString =sheet.getCell(indexMap.get("creator"), i).getContents(); + Cell timeCell = sheet.getCell(indexMap.get("createTime"), i); + if(timeCell.getType()==CellType.DATE){ + DateCell dc = (DateCell) timeCell; + Date dateValue = dc.getDate(); + SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); + dateString = formatter.format(dateValue); + } + markerDto.setCreateTime(dateString); markerDto.setCreator(sheet.getCell(indexMap.get("creator"), i).getContents()); markerDto.setMemo(sheet.getCell(indexMap.get("memo"), i).getContents()); markerDtos.add(markerDto); } + book.close(); + this.getSession().clear(); batchSave(markerDtos, userCompanyId); } catch (BiffException e) { logger.error(e.getMessage()); diff --git a/src/main/java/com/casic/accessControl/marker/web/MarkerController.java b/src/main/java/com/casic/accessControl/marker/web/MarkerController.java index 209bbd4..8c5c84a 100644 --- a/src/main/java/com/casic/accessControl/marker/web/MarkerController.java +++ b/src/main/java/com/casic/accessControl/marker/web/MarkerController.java @@ -312,7 +312,9 @@ try { flag = markerManager.readExcel(file.getInputStream(), companyId); } catch (IOException e) { - e.printStackTrace(); +// e.printStackTrace(); + res.put("message", e.getMessage()); + logger.error(e.getMessage()); return res; } @@ -325,6 +327,9 @@ if (flag == 3) { res.put("message", "经纬度坐标包含无效数据，请检查是否全部为数字，导入失败"); return res; + }if (flag == 4) { + res.put("message", "java读取Excel出错"); + return res; } if (flag == 5) { res.put("message", "编号应该为数字，请保持导出的文件中的值不要进行修改！如果修改，则可能导致丢失数据。导入失败"); diff --git a/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java b/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java index 3ad408d..9492d9a 100644 --- a/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java +++ b/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java @@ -16,11 +16,13 @@ import com.google.gson.Gson; import jxl.*; import jxl.read.biff.BiffException; +import jxl.write.DateFormat; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang3.math.NumberUtils; +import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.MatchMode; @@ -464,12 +466,16 @@ if(isEmptyRow(sheet.getRow(i))) continue; MarkerDto markerDto = new MarkerDto(); markerDto.setMarkerId(sheet.getCell(indexMap.get("markerId"), i).getContents()); +// String idstr = sheet.getCell(indexMap.get("id"), i).getContents(); +// if(NumberUtils.isDigits(idstr)){ +// markerDto.setId(Long.valueOf(idstr)); +// } if(indexMap.containsKey("id")){ String idstr = sheet.getCell(indexMap.get("id"), i).getContents(); if(NumberUtils.isDigits(idstr)){ markerDto.setId(Long.valueOf(idstr)); MarkerDto marker = getMarkerById(markerDto.getId());//保证id和markerId对应，没有更改 - if(!marker.getMarkerId().equals(marker.getMarkerId())) return 5; + if(!markerDto.getMarkerId().equals(marker.getMarkerId())) return 5; }else if(StringUtils.isNotBlank(idstr)){ return 5; } @@ -508,12 +514,22 @@ markerDto.setLatitude(latCell.getValue()); markerDto.setIsValid(1); markerDto.setRecordType(recordTypeFlag); - markerDto.setCreateTime(sheet.getCell(indexMap.get("createTime"), i).getContents()); + String dateString =sheet.getCell(indexMap.get("creator"), i).getContents(); + Cell timeCell = sheet.getCell(indexMap.get("createTime"), i); + if(timeCell.getType()==CellType.DATE){ + DateCell dc = (DateCell) timeCell; + Date dateValue = dc.getDate(); + SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); + dateString = formatter.format(dateValue); + } + markerDto.setCreateTime(dateString); markerDto.setCreator(sheet.getCell(indexMap.get("creator"), i).getContents()); markerDto.setMemo(sheet.getCell(indexMap.get("memo"), i).getContents()); markerDtos.add(markerDto); } + book.close(); + this.getSession().clear(); batchSave(markerDtos, userCompanyId); } catch (BiffException e) { logger.error(e.getMessage()); diff --git a/src/main/java/com/casic/accessControl/marker/web/MarkerController.java b/src/main/java/com/casic/accessControl/marker/web/MarkerController.java index 209bbd4..8c5c84a 100644 --- a/src/main/java/com/casic/accessControl/marker/web/MarkerController.java +++ b/src/main/java/com/casic/accessControl/marker/web/MarkerController.java @@ -312,7 +312,9 @@ try { flag = markerManager.readExcel(file.getInputStream(), companyId); } catch (IOException e) { - e.printStackTrace(); +// e.printStackTrace(); + res.put("message", e.getMessage()); + logger.error(e.getMessage()); return res; } @@ -325,6 +327,9 @@ if (flag == 3) { res.put("message", "经纬度坐标包含无效数据，请检查是否全部为数字，导入失败"); return res; + }if (flag == 4) { + res.put("message", "java读取Excel出错"); + return res; } if (flag == 5) { res.put("message", "编号应该为数字，请保持导出的文件中的值不要进行修改！如果修改，则可能导致丢失数据。导入失败"); diff --git a/src/main/resources/application.properties b/src/main/resources/application.properties index b659f37..c1fbf87 100644 --- a/src/main/resources/application.properties +++ b/src/main/resources/application.properties @@ -20,9 +20,10 @@ #db.default.username=scott #db.default.password=SZHTDB2 db.default.driverClassName=com.mysql.jdbc.Driver -db.default.url=jdbc:mysql://192.168.0.225:3306/ems?useUnicode=true&characterEncoding=UTF-8 -db.default.username=emsuser -db.default.password=emsuser +#db.default.url=jdbc:mysql://192.168.0.225:3306/ems?useUnicode=true&characterEncoding=UTF-8 +db.default.url=jdbc:mysql://119.254.110.71:2019/ems?useUnicode=true&characterEncoding=UTF-8 +db.default.username=root +db.default.password=root # ============================================================================