diff --git a/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java b/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java index a214041..22296bb 100644 --- a/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java +++ b/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java @@ -40,7 +40,7 @@ } //新增，可展示自定义名称 public void addHeaderAlias(String... alias) { - if (alias == null) { + if (alias == null||alias.length==0) { return; } enableAlias = true; diff --git a/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java b/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java index a214041..22296bb 100644 --- a/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java +++ b/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java @@ -40,7 +40,7 @@ } //新增，可展示自定义名称 public void addHeaderAlias(String... alias) { - if (alias == null) { + if (alias == null||alias.length==0) { return; } enableAlias = true; 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 fd44f19..ccd55b4 100644 --- a/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java +++ b/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java @@ -1,6 +1,5 @@ package com.casic.accessControl.marker.manager; -import com.casic.accessControl.common.CommonEnum; import com.casic.accessControl.core.ext.export.CsvExportor; import com.casic.accessControl.core.ext.export.TableModel; import com.casic.accessControl.core.hibernate.HibernateEntityDao; @@ -10,20 +9,18 @@ import com.casic.accessControl.marker.domain.Marker; import com.casic.accessControl.user.domain.Company; import com.casic.accessControl.user.domain.User; -import com.casic.accessControl.user.manager.UserManager; import com.casic.accessControl.util.DataTable; import com.casic.accessControl.util.DataTableParameter; import com.casic.accessControl.util.DataTableUtils; import com.casic.accessControl.util.DateUtils; -import com.casic.accessControl.xls.Common; import com.google.gson.Gson; -import com.google.gson.reflect.TypeToken; import jxl.*; import jxl.read.biff.BiffException; 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.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.MatchMode; @@ -31,8 +28,6 @@ import org.hibernate.criterion.Restrictions; import org.slf4j.Logger; import org.slf4j.LoggerFactory; -import org.springframework.beans.factory.annotation.Autowired; -import org.springframework.cache.annotation.Cacheable; import org.springframework.stereotype.Service; import javax.annotation.Resource; @@ -41,8 +36,6 @@ import java.io.File; import java.io.IOException; import java.io.InputStream; -import java.io.PrintWriter; -import java.text.DecimalFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; @@ -362,6 +355,9 @@ for (int i = 0; i < columns; i++) {//解析出来excel的头，找出对应位置,以适应excel文件头部顺序的变化 String name = sheet.getCell(i, 0).getContents(); switch (name) { + case "编号": + indexMap.put("id", i); + break; case "管线种类": indexMap.put("markerObjectType", i); recordTypeFlag = 1; @@ -378,6 +374,12 @@ case "管线埋深": indexMap.put("depth", i); break; + case "所属区域": + indexMap.put("area", i); + break; + case "所属线路": + indexMap.put("line", i); + break; case "所属道路": indexMap.put("road", i); break; @@ -421,7 +423,7 @@ case "井深": indexMap.put("depth", i); break; - case "管点特征": + case "特征管点": indexMap.put("markerObjectType", i); recordTypeFlag = 3; break; @@ -461,6 +463,17 @@ //TODO LIST:写一个判断excel行内容全部为空的方法 if(isEmptyRow(sheet.getRow(i))) continue; MarkerDto markerDto = new MarkerDto(); + markerDto.setMarkerId(sheet.getCell(indexMap.get("markerId"), i).getContents()); + 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; + }else if(StringUtils.isNotBlank(idstr)){ + return 5; + } + } if (recordTypeFlag == 1) { markerDto.setPipeMaterial(sheet.getCell(indexMap.get("pipeMaterial"), i).getContents()); markerDto.setPipeDiameter(sheet.getCell(indexMap.get("pipeDiameter"), i).getContents()); @@ -476,28 +489,23 @@ markerDto.setMarkerObjectType(sheet.getCell(indexMap.get("markerObjectType"), i).getContents()); markerDto.setDepth(sheet.getCell(indexMap.get("depth"), i).getContents()); markerDto.setRoad(sheet.getCell(indexMap.get("road"), i).getContents()); + markerDto.setLine(sheet.getCell(indexMap.get("line"), i).getContents()); + markerDto.setArea(sheet.getCell(indexMap.get("area"), i).getContents()); markerDto.setConstructTime(sheet.getCell(indexMap.get("constructTime"), i).getContents()); markerDto.setOwnerComp(sheet.getCell(indexMap.get("ownerComp"), i).getContents()); markerDto.setMarkerObjectId(sheet.getCell(indexMap.get("markerObjectId"), i).getContents()); - markerDto.setMarkerId(sheet.getCell(indexMap.get("markerId"), i).getContents()); + markerDto.setMarkerType(sheet.getCell(indexMap.get("markerType"), i).getContents()); markerDto.setMarkerDepth(sheet.getCell(indexMap.get("markerDepth"), i).getContents()); Cell loCell1 = sheet.getCell(indexMap.get("longitude"), i); Cell latCell1 =sheet.getCell(indexMap.get("latitude"), i); - /*String longtitudetmp = sheet.getCell(indexMap.get("longitude"), i).getContents();*/ -// c10.getType() == CellType.NUMBER if(loCell1.getType()!= CellType.NUMBER||latCell1.getType()!=CellType.NUMBER){ - //经纬度坐标格式不符合要求 return 3; } - NumberCell longCell = (NumberCell)sheet.getCell(indexMap.get("longitude"), i); - NumberCell latCell =(NumberCell)sheet.getCell(indexMap.get("latitude"), i); - double longtitudetmp = longCell.getValue(); - double latitudetmp = latCell.getValue(); - /* markerDto.setLongitude(Double.valueOf(empty2zero(longtitudetmp)));*/ - markerDto.setLongitude(longtitudetmp); - markerDto.setLatitude(latitudetmp); -// markerDto.setLatitude(Double.valueOf(empty2zero(sheet.getCell(indexMap.get("latitude"), i).getContents()))); + NumberCell longCell = (NumberCell)loCell1; + NumberCell latCell =(NumberCell)latCell1; + markerDto.setLongitude( longCell.getValue()); + markerDto.setLatitude(latCell.getValue()); markerDto.setIsValid(1); markerDto.setRecordType(recordTypeFlag); markerDto.setCreateTime(sheet.getCell(indexMap.get("createTime"), i).getContents()); @@ -593,42 +601,42 @@ excelName = "管线信息"; excelColumns = "id,markerObjectType,pipeMaterial,pipeDiameter,layStyle,depth," + "area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,markerDepth,creator,createTime,memo"; + "markerId,markerType,markerDepth,creator,longitude,latitude,createTime,memo"; alias ="编号,管线种类,管线材质,管线直径,埋设方式,管线埋深," + "所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } else if (type == 2) { excelName = "管线附属物信息"; excelColumns = "id,markerObjectType,depth," + "area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,markerDepth,creator,createTime,memo"; + "markerId,markerType,markerDepth,creator,longitude,latitude,createTime,memo"; alias ="编号,附属物名称,井深," + "所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } else if (type == 3) { excelName = "管线特征管点信息"; excelColumns = "id,markerObjectType,depth," + "area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,markerDepth,creator,createTime,memo"; - alias ="编号,管点特征,特征点埋深," + + "markerId,markerType,markerDepth,creator,longitude,latitude,createTime,memo"; + alias ="编号,特征管点,特征点埋深," + "所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } else if (type == 4) { excelName = "交叉穿越点信息"; excelColumns = "id,markerObjectType,pipeMaterial,pipeDiameter,depth,belowType," + "belowDepth,belowDiameter,belowMaterial,area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,markerDepth,creator,createTime,memo"; + "markerId,markerType,markerDepth,creator,longitude,latitude,createTime,memo"; alias ="编号,上层管种类,上层管材料,上层管直径,上层管埋深,下层管种类," + "下层管埋深,下层管直径,下层管材料,所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } else {//全部导出的，在地图上 excelName = "标识器信息"; excelColumns = "id,markerObjectType,pipeMaterial,pipeDiameter,depth,layStyle,belowType," + "belowDepth,belowDiameter,belowMaterial,area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,creator,markerDepth,createTime,memo"; - alias ="编号,上层管种类,上层管材料,上层管直径,上层管埋深,埋设方式,下层管种类," + - "下层管埋深,下层管直径,下层管材料,所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "markerId,markerType,creator,longitude,latitude,markerDepth,createTime,memo"; +// alias ="编号,上层管种类,上层管材料,上层管直径,上层管埋深,埋设方式,下层管种类," + +// "下层管埋深,下层管直径,下层管材料,所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + +// "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } String[] columns = excelColumns.split(","); String[] headerAlias = alias.split(","); diff --git a/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java b/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java index a214041..22296bb 100644 --- a/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java +++ b/src/main/java/com/casic/accessControl/core/ext/export/TableModel.java @@ -40,7 +40,7 @@ } //新增，可展示自定义名称 public void addHeaderAlias(String... alias) { - if (alias == null) { + if (alias == null||alias.length==0) { return; } enableAlias = true; 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 fd44f19..ccd55b4 100644 --- a/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java +++ b/src/main/java/com/casic/accessControl/marker/manager/MarkerManager.java @@ -1,6 +1,5 @@ package com.casic.accessControl.marker.manager; -import com.casic.accessControl.common.CommonEnum; import com.casic.accessControl.core.ext.export.CsvExportor; import com.casic.accessControl.core.ext.export.TableModel; import com.casic.accessControl.core.hibernate.HibernateEntityDao; @@ -10,20 +9,18 @@ import com.casic.accessControl.marker.domain.Marker; import com.casic.accessControl.user.domain.Company; import com.casic.accessControl.user.domain.User; -import com.casic.accessControl.user.manager.UserManager; import com.casic.accessControl.util.DataTable; import com.casic.accessControl.util.DataTableParameter; import com.casic.accessControl.util.DataTableUtils; import com.casic.accessControl.util.DateUtils; -import com.casic.accessControl.xls.Common; import com.google.gson.Gson; -import com.google.gson.reflect.TypeToken; import jxl.*; import jxl.read.biff.BiffException; 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.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.MatchMode; @@ -31,8 +28,6 @@ import org.hibernate.criterion.Restrictions; import org.slf4j.Logger; import org.slf4j.LoggerFactory; -import org.springframework.beans.factory.annotation.Autowired; -import org.springframework.cache.annotation.Cacheable; import org.springframework.stereotype.Service; import javax.annotation.Resource; @@ -41,8 +36,6 @@ import java.io.File; import java.io.IOException; import java.io.InputStream; -import java.io.PrintWriter; -import java.text.DecimalFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; @@ -362,6 +355,9 @@ for (int i = 0; i < columns; i++) {//解析出来excel的头，找出对应位置,以适应excel文件头部顺序的变化 String name = sheet.getCell(i, 0).getContents(); switch (name) { + case "编号": + indexMap.put("id", i); + break; case "管线种类": indexMap.put("markerObjectType", i); recordTypeFlag = 1; @@ -378,6 +374,12 @@ case "管线埋深": indexMap.put("depth", i); break; + case "所属区域": + indexMap.put("area", i); + break; + case "所属线路": + indexMap.put("line", i); + break; case "所属道路": indexMap.put("road", i); break; @@ -421,7 +423,7 @@ case "井深": indexMap.put("depth", i); break; - case "管点特征": + case "特征管点": indexMap.put("markerObjectType", i); recordTypeFlag = 3; break; @@ -461,6 +463,17 @@ //TODO LIST:写一个判断excel行内容全部为空的方法 if(isEmptyRow(sheet.getRow(i))) continue; MarkerDto markerDto = new MarkerDto(); + markerDto.setMarkerId(sheet.getCell(indexMap.get("markerId"), i).getContents()); + 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; + }else if(StringUtils.isNotBlank(idstr)){ + return 5; + } + } if (recordTypeFlag == 1) { markerDto.setPipeMaterial(sheet.getCell(indexMap.get("pipeMaterial"), i).getContents()); markerDto.setPipeDiameter(sheet.getCell(indexMap.get("pipeDiameter"), i).getContents()); @@ -476,28 +489,23 @@ markerDto.setMarkerObjectType(sheet.getCell(indexMap.get("markerObjectType"), i).getContents()); markerDto.setDepth(sheet.getCell(indexMap.get("depth"), i).getContents()); markerDto.setRoad(sheet.getCell(indexMap.get("road"), i).getContents()); + markerDto.setLine(sheet.getCell(indexMap.get("line"), i).getContents()); + markerDto.setArea(sheet.getCell(indexMap.get("area"), i).getContents()); markerDto.setConstructTime(sheet.getCell(indexMap.get("constructTime"), i).getContents()); markerDto.setOwnerComp(sheet.getCell(indexMap.get("ownerComp"), i).getContents()); markerDto.setMarkerObjectId(sheet.getCell(indexMap.get("markerObjectId"), i).getContents()); - markerDto.setMarkerId(sheet.getCell(indexMap.get("markerId"), i).getContents()); + markerDto.setMarkerType(sheet.getCell(indexMap.get("markerType"), i).getContents()); markerDto.setMarkerDepth(sheet.getCell(indexMap.get("markerDepth"), i).getContents()); Cell loCell1 = sheet.getCell(indexMap.get("longitude"), i); Cell latCell1 =sheet.getCell(indexMap.get("latitude"), i); - /*String longtitudetmp = sheet.getCell(indexMap.get("longitude"), i).getContents();*/ -// c10.getType() == CellType.NUMBER if(loCell1.getType()!= CellType.NUMBER||latCell1.getType()!=CellType.NUMBER){ - //经纬度坐标格式不符合要求 return 3; } - NumberCell longCell = (NumberCell)sheet.getCell(indexMap.get("longitude"), i); - NumberCell latCell =(NumberCell)sheet.getCell(indexMap.get("latitude"), i); - double longtitudetmp = longCell.getValue(); - double latitudetmp = latCell.getValue(); - /* markerDto.setLongitude(Double.valueOf(empty2zero(longtitudetmp)));*/ - markerDto.setLongitude(longtitudetmp); - markerDto.setLatitude(latitudetmp); -// markerDto.setLatitude(Double.valueOf(empty2zero(sheet.getCell(indexMap.get("latitude"), i).getContents()))); + NumberCell longCell = (NumberCell)loCell1; + NumberCell latCell =(NumberCell)latCell1; + markerDto.setLongitude( longCell.getValue()); + markerDto.setLatitude(latCell.getValue()); markerDto.setIsValid(1); markerDto.setRecordType(recordTypeFlag); markerDto.setCreateTime(sheet.getCell(indexMap.get("createTime"), i).getContents()); @@ -593,42 +601,42 @@ excelName = "管线信息"; excelColumns = "id,markerObjectType,pipeMaterial,pipeDiameter,layStyle,depth," + "area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,markerDepth,creator,createTime,memo"; + "markerId,markerType,markerDepth,creator,longitude,latitude,createTime,memo"; alias ="编号,管线种类,管线材质,管线直径,埋设方式,管线埋深," + "所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } else if (type == 2) { excelName = "管线附属物信息"; excelColumns = "id,markerObjectType,depth," + "area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,markerDepth,creator,createTime,memo"; + "markerId,markerType,markerDepth,creator,longitude,latitude,createTime,memo"; alias ="编号,附属物名称,井深," + "所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } else if (type == 3) { excelName = "管线特征管点信息"; excelColumns = "id,markerObjectType,depth," + "area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,markerDepth,creator,createTime,memo"; - alias ="编号,管点特征,特征点埋深," + + "markerId,markerType,markerDepth,creator,longitude,latitude,createTime,memo"; + alias ="编号,特征管点,特征点埋深," + "所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } else if (type == 4) { excelName = "交叉穿越点信息"; excelColumns = "id,markerObjectType,pipeMaterial,pipeDiameter,depth,belowType," + "belowDepth,belowDiameter,belowMaterial,area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,markerDepth,creator,createTime,memo"; + "markerId,markerType,markerDepth,creator,longitude,latitude,createTime,memo"; alias ="编号,上层管种类,上层管材料,上层管直径,上层管埋深,下层管种类," + "下层管埋深,下层管直径,下层管材料,所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } else {//全部导出的，在地图上 excelName = "标识器信息"; excelColumns = "id,markerObjectType,pipeMaterial,pipeDiameter,depth,layStyle,belowType," + "belowDepth,belowDiameter,belowMaterial,area,line,road,constructTime,ownerComp,markerObjectId," + - "markerId,markerType,creator,markerDepth,createTime,memo"; - alias ="编号,上层管种类,上层管材料,上层管直径,上层管埋深,埋设方式,下层管种类," + - "下层管埋深,下层管直径,下层管材料,所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + - "标识器ID,标识器类型,标识器埋深,安装部门,最后修改时间,备注"; + "markerId,markerType,creator,longitude,latitude,markerDepth,createTime,memo"; +// alias ="编号,上层管种类,上层管材料,上层管直径,上层管埋深,埋设方式,下层管种类," + +// "下层管埋深,下层管直径,下层管材料,所属区域,所属线路,所属道路,建设年代,权属单位,标示对象ID," + +// "标识器ID,标识器类型,标识器埋深,安装部门,经度,纬度,最后修改时间,备注"; } String[] columns = excelColumns.split(","); String[] headerAlias = alias.split(","); 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 f50471e..209bbd4 100644 --- a/src/main/java/com/casic/accessControl/marker/web/MarkerController.java +++ b/src/main/java/com/casic/accessControl/marker/web/MarkerController.java @@ -291,7 +291,7 @@ */ @RequestMapping(value = "excelMarkers") @ResponseBody - public Map test(@RequestParam(value = "excelFile", required = true) MultipartFile file, HttpSession session) { + public Map importMarkers(@RequestParam(value = "excelFile", required = true) MultipartFile file, HttpSession session) { User user = (User) session.getAttribute(StringUtils.SYS_USER);//获取当前登录用户 Long companyId = user.getCompany().getId(); @@ -326,6 +326,10 @@ res.put("message", "经纬度坐标包含无效数据，请检查是否全部为数字，导入失败"); return res; } + if (flag == 5) { + res.put("message", "编号应该为数字，请保持导出的文件中的值不要进行修改！如果修改，则可能导致丢失数据。导入失败"); + return res; + } return res; } res.put("success", true);