业务场景：xlsx表格当中有5个层级，在导入项目中时要确保页面展示的结构为树形，但是有一些的零部件从属（父类编号）是一样的，这样就给导入表格的程序导致分别不出有一些的零部件是在哪一级的父类下面，所以导入成功了也没有能够展示出树形。所以在这里就想到当表格导入的时候在分辨不出当前零部件属于哪个父类下的时候就通过算法找到最近的父类然后关联上。这样导入进去的表格就能够展示树形了。
主表实体类
public class BaseChanpinbomEntity implements java.io.Serializable {/**主键*/private java.lang.String id;/**创建人名称*/@Excel(name="创建人名称")private java.lang.String createName;/**创建人登录名称*/@Excel(name="创建人登录名称")private java.lang.String createBy;/**创建日期*/@Excel(name="创建日期",format = "yyyy-MM-dd")private java.util.Date createDate;/**更新人名称*/@Excel(name="更新人名称")private java.lang.String updateName;/**更新人登录名称*/@Excel(name="更新人登录名称")private java.lang.String updateBy;/**更新日期*/@Excel(name="更新日期",format = "yyyy-MM-dd")private java.util.Date updateDate;/**产品名称*/@Excel(name="产品名称")private java.lang.String chanpinName;/**版本*/@Excel(name="版本")private java.lang.String banben;/**审核状态1.未审核 2.已审核*/@Excel(name="审核状态")private java.lang.String shenheState;/**审核人*/@Excel(name="审核人")private java.lang.String shenHeRen;/**审核日期*/@Excel(name="审核日期",format = "yyyy-MM-dd")private java.util.Date shenHeRiQi;/**物料编码*/@Excel(name="物料编码")private java.lang.String wuLiaoBianMa;/**单位*/@Excel(name="单位")private java.lang.String danwei;
子表实体类
//实体类public class baseChanpinbomsublistEntity implements java.io.Serializable {/**主键*/private java.lang.String id;/**创建人名称*/@Excel(name="创建人名称")private java.lang.String createName;/**创建人登录名称*/@Excel(name="创建人登录名称")private java.lang.String createBy;/**创建日期*/@Excel(name="创建日期",format = "yyyy-MM-dd")private java.util.Date createDate;/**更新人名称*/@Excel(name="更新人名称")private java.lang.String updateName;/**更新人登录名称*/@Excel(name="更新人登录名称")private java.lang.String updateBy;/**更新日期*/@Excel(name="更新日期",format = "yyyy-MM-dd")private java.util.Date updateDate;/**部件图号*/@Excel(name="部件图号")private java.lang.String bujiantuhao;/**部件名称*/@Excel(name="部件名称")private java.lang.String bujianmingcheng;/**零件图号*/@Excel(name="零件图号")private java.lang.String lingjiantuhao;/**零件名称*/@Excel(name="零件名称")private java.lang.String lingjianmingcheng;/**工艺*/@Excel(name="工艺")private java.lang.String gongyi;/**零件生产*/@Excel(name="零件生产")private java.lang.String lianjianshengchan;/**每台*/@Excel(name="每台")private java.lang.String meitai;/**材料*/@Excel(name="材料")private java.lang.String cailiao;/**材料厂商*/@Excel(name="材料厂商")private java.lang.String cailiaochangshang;/**型号及规格*/@Excel(name="型号及规格")private java.lang.String xihaoguige;/**单件*/@Excel(name="单件")private java.lang.String danJian;/**产品重量 (总重)*/@Excel(name="产品重量")private java.lang.String chanpinzhongliang;/**来源*/@Excel(name="来源")private java.lang.String laiyuan;/**备注*/@Excel(name="备注")private java.lang.String beizhu;/**物料编码*/@Excel(name="物料编码")private java.lang.String wuLiaoBianMa;/**序号*/@Excel(name="序号")private java.lang.Integer xuhao;/**父类ID */@Excel(name="父类ID")private java.lang.String parentid;/**父类部件图号*/@Excel(name="父类部件图号")private java.lang.String parentTuhao;/**父类部件名称*/@Excel(name="父类部件名称")private java.lang.String parentname;/**父类零件图号*/@Excel(name="父类零件图号")private java.lang.String parentlingjiantuhao;/**单位*/@Excel(name="单位")private java.lang.String danwei;
前端页面用的easyui
导入表格代码
/*** 导入表* @param request* @param response* @return*/public AjaxJson importExcel(HttpServletRequest request, HttpServletResponse response) {AjaxJson j = new AjaxJson();MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();MultipartFile file = null ;for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {file = entity.getValue();// 获取上传文件对象try {//导入文件之前先把物料全部都加上 Object data[][] = ImportExcelUtil.getIntegralData07(file.getInputStream());//添加产品BOM主表 ,产品BOM还要添加到物料表中BaseChanpinbomEntity baseChanpinbomEntity = baseChanpinbomService.saveByChanPinBom(file, data);//获取导入总行数int ZongHangShu = data.length;//导入表格baseChanpinbomService.importBOM(file, data,ZongHangShu,baseChanpinbomEntity);//添加 Bom子表的 父类IDbaseChanpinbomService.updateBOM(data, ZongHangShu,baseChanpinbomEntity);j.setMsg("文件导入成功！");} catch (Exception e) {e.printStackTrace();j.setMsg("文件导入失败！");}finally{try {file.getInputStream().close();} catch (IOException e) {e.printStackTrace();}}}return j;}
/*** 导入 Excel表 * @throws Exception */public void importBOM(MultipartFile file,Object data[][],int ZongHangShu , BaseChanpinbomEntity baseChanpinbomEntity) {//获取导入总行数for (int i = 0; i < ZongHangShu-3; i++) {BaseChanpinbomsublistEntity baseChanpinbomsublistEntity = new BaseChanpinbomsublistEntity();/**产品BOM ID */baseChanpinbomsublistEntity.setBaseChanpinbomID(baseChanpinbomEntity.getId());/**创建人名称*/baseChanpinbomsublistEntity.setCreateName(ResourceUtil.getSessionUserName().getRealName());/**创建人登录名称*/baseChanpinbomsublistEntity.setCreateBy(ResourceUtil.getSessionUserName().getCreateBy());/**创建日期*/baseChanpinbomsublistEntity.setCreateDate(new Date());/**部件图号*/String bujiantuhao = (String) data[i+3][0];bujiantuhao = this.kongGeTiHuan(bujiantuhao);if(!StringUtil.isEmpty(bujiantuhao)){baseChanpinbomsublistEntity.setBujiantuhao(bujiantuhao);}/**部件名称*/String bujianmingcheng = (String) data[i+3][1];bujianmingcheng = this.kongGeTiHuan(bujianmingcheng);if(!StringUtil.isEmpty(bujianmingcheng)){baseChanpinbomsublistEntity.setBujianmingcheng(bujianmingcheng);}/**零件图号*/String lingjiantuhao = (String) data[i+3][2];lingjiantuhao = this.kongGeTiHuan(lingjiantuhao);if(!StringUtil.isEmpty(lingjiantuhao)){baseChanpinbomsublistEntity.setLingjiantuhao(lingjiantuhao);}/**零件名称*/String lingjianmingcheng = (String) data[i+3][3];lingjianmingcheng = this.kongGeTiHuan(lingjianmingcheng);if(!StringUtil.isEmpty(lingjianmingcheng)){baseChanpinbomsublistEntity.setLingjianmingcheng(lingjianmingcheng);}/**工艺*/String gongyi = (String) data[i+3][4];gongyi = this.kongGeTiHuan(gongyi);if(!StringUtil.isEmpty(gongyi)){baseChanpinbomsublistEntity.setGongyi(gongyi);}/**零件生产*/String lianjianshengchan = (String) data[i+3][5];lianjianshengchan = this.kongGeTiHuan(lianjianshengchan);if(!StringUtil.isEmpty(lianjianshengchan)){baseChanpinbomsublistEntity.setLianjianshengchan(lianjianshengchan);}/**每台*/String meitai = (String) data[i+3][6];meitai = this.kongGeTiHuan(meitai);if(!StringUtil.isEmpty(meitai)){baseChanpinbomsublistEntity.setMeitai(meitai);}/**材料*/String cailiao = (String) data[i+3][7];cailiao = this.kongGeTiHuan(cailiao);if(!StringUtil.isEmpty(cailiao)){baseChanpinbomsublistEntity.setCailiao(cailiao);}/**材料厂商*/String cailiaochangshang = (String) data[i+3][8];cailiaochangshang = this.kongGeTiHuan(cailiaochangshang);if(!StringUtil.isEmpty(cailiaochangshang)){baseChanpinbomsublistEntity.setCailiaochangshang(cailiaochangshang);}/**型号及规格*/String xihaoguige = (String) data[i+3][9];xihaoguige = this.kongGeTiHuan(xihaoguige);if(!StringUtil.isEmpty(xihaoguige)){baseChanpinbomsublistEntity.setXihaoguige(xihaoguige);}/**单件*/String danJian = (String) data[i+3][10];danJian = this.kongGeTiHuan(danJian);if(!StringUtil.isEmpty(danJian)){baseChanpinbomsublistEntity.setDanJian(danJian);}/**产品重量 (总重) */String chanpinzhongliang = (String) data[i+3][11];chanpinzhongliang = this.kongGeTiHuan(chanpinzhongliang);if(!StringUtil.isEmpty(chanpinzhongliang)){baseChanpinbomsublistEntity.setChanpinzhongliang(chanpinzhongliang);}/**来源*/String laiyuan = (String) data[i+3][12];laiyuan = this.kongGeTiHuan(laiyuan);if(!StringUtil.isEmpty(laiyuan)){baseChanpinbomsublistEntity.setLaiyuan(laiyuan);}/**备注*/String beizhu = (String) data[i+3][13];beizhu = this.kongGeTiHuan(beizhu);if(!StringUtil.isEmpty(beizhu)){baseChanpinbomsublistEntity.setBeizhu(beizhu);}/**父类零件图号 (从属)*/String parentlingjiantuhao = (String) data[i+3][14];parentlingjiantuhao = this.kongGeTiHuan(parentlingjiantuhao);if(!StringUtil.isEmpty(parentlingjiantuhao)){baseChanpinbomsublistEntity.setParentlingjiantuhao(parentlingjiantuhao);}/**物料编码*/String wuLiaoBianMa = (String) data[i+3][15];wuLiaoBianMa = this.kongGeTiHuan(wuLiaoBianMa);if(!StringUtil.isEmpty(wuLiaoBianMa)){baseChanpinbomsublistEntity.setWuLiaoBianMa(wuLiaoBianMa);}/**单位*/String danwei = (String) data[i+3][17];danwei = this.kongGeTiHuan(danwei);if(!StringUtil.isEmpty(danwei)){baseChanpinbomsublistEntity.setDanwei(danwei);}/**序号 后期用于查出唯一父类的判断字段*/String xuhao = (String) data[i+3][16];xuhao = this.kongGeTiHuan(xuhao);if(!StringUtil.isEmpty(xuhao)){baseChanpinbomsublistEntity.setXuhao(Integer.parseInt(xuhao));systemService.save(baseChanpinbomsublistEntity);}}}
把表格中的数据都插入数据库之后，还是查找父类，树形结构必要条件之一就是每个子项只能有一个父类。
/*** 添加子表的 父类ID* @throws Exception */public void updateBOM(Object data[][],int ZongHangShu,BaseChanpinbomEntity baseChanpinbomEntity ){Session session = this.getSession();//产品BOM IDString baseChanpinbomID = baseChanpinbomEntity.getId();//导入表格成功之后根据父类零件图号找到父类对象还要赋值,parentId,parentTuhao,parentname,for (int i = 0; i < ZongHangShu-3; i++) {/**零件图号*/String lingjiantuhao = (String) data[i+3][2];/**父类零件图号 (从属)*/String parentlingjiantuhao = (String) data[i+3][14];/**序号*/String xuhao = (String) data[i+3][16];if(StringUtil.isNotEmpty(parentlingjiantuhao)){ //父类对象BaseChanpinbomsublistEntity baseChanpinbomsublistEntityByParent = new BaseChanpinbomsublistEntity();//本级对象BaseChanpinbomsublistEntitybaseChanpinbomsublistEntity = new BaseChanpinbomsublistEntity() ;BaseChanpinbomsublistEntity Entity = new BaseChanpinbomsublistEntity();if(StringUtil.isNotEmpty(parentlingjiantuhao) && StringUtil.isNotEmpty(lingjiantuhao)){//查找本级对象baseChanpinbomsublistEntity = baseChanpinbomDao.findlingjiantuhao(lingjiantuhao,parentlingjiantuhao,xuhao,baseChanpinbomID);List<BaseChanpinbomsublistEntity> list1 = baseChanpinbomDao.findBaseChanpinbomsublistByParentList(parentlingjiantuhao,xuhao,baseChanpinbomID);if(null == list1 || list1.size() ==0){List<BaseChanpinbomsublistEntity> list2 = baseChanpinbomDao.findBaseChanpinbomsublistBybujiantuhao(parentlingjiantuhao, xuhao,baseChanpinbomID);list1.addAll(list2);}//查找最近的父类baseChanpinbomsublistEntityByParent = this.findBaseChanpinbomsublistEntityByParent(list1, baseChanpinbomsublistEntity);}else if(StringUtil.isNotEmpty(parentlingjiantuhao) && StringUtil.isEmpty(lingjiantuhao)){//针对于第一级子类 如底笼//查找本级对象baseChanpinbomsublistEntity = baseChanpinbomDao.findTuhaoAndXuhao(parentlingjiantuhao,xuhao,baseChanpinbomID);List<BaseChanpinbomsublistEntity> list3 = baseChanpinbomDao.findBaseChanpinbomsublistBybujiantuhaoList(parentlingjiantuhao,baseChanpinbomID);//查找最近的父类baseChanpinbomsublistEntityByParent = this.findBaseChanpinbomsublistEntityByParent(list3, baseChanpinbomsublistEntity);}if(baseChanpinbomsublistEntityByParent!=null){/**父类ID */if(StringUtil.isNotEmpty(baseChanpinbomsublistEntityByParent.getId())){//本级对象 根据零件图号找到零件对象if(baseChanpinbomsublistEntity!=null){baseChanpinbomsublistEntity.setParentid(baseChanpinbomsublistEntityByParent.getId());}}/**父类部件图号*/if(StringUtil.isNotEmpty(baseChanpinbomsublistEntityByParent.getBujiantuhao())){if(baseChanpinbomsublistEntity!=null){baseChanpinbomsublistEntity.setParentTuhao(baseChanpinbomsublistEntityByParent.getBujiantuhao());}}/**父类部件名称*/if(StringUtil.isNotEmpty(baseChanpinbomsublistEntityByParent.getBujianmingcheng())){if(baseChanpinbomsublistEntity!=null){baseChanpinbomsublistEntity.setParentname(baseChanpinbomsublistEntityByParent.getBujianmingcheng());}}}if(baseChanpinbomsublistEntity!=null && StringUtil.isNotEmpty(baseChanpinbomsublistEntity.getId())){Entity = baseChanpinbomsublistEntity;session.merge(Entity);}}}}
/*** 查找最近的父类 * @return* @throws Exception */public BaseChanpinbomsublistEntity findBaseChanpinbomsublistEntityByParent(List<BaseChanpinbomsublistEntity> list,BaseChanpinbomsublistEntity baseChanpinbomsublistEntity){BaseChanpinbomsublistEntity baseChanpinbomsublistEntityByParent = new BaseChanpinbomsublistEntity();if(list.size()>0 && baseChanpinbomsublistEntity!= null){int[] arr = new int [list.size()];Map<String,BaseChanpinbomsublistEntity> map = new HashMap<String, BaseChanpinbomsublistEntity>();for (int j = 0; j < list.size(); j++) {//本级序号-父类序号=相差数，相差数最小的就是最靠近本级的父类Integer benji = baseChanpinbomsublistEntity.getXuhao();//父类序号Integer fuleixuhao = list.get(j).getXuhao();int xiangchashu = benji-fuleixuhao;if(xiangchashu > 0){arr[j] = xiangchashu;map.put(String.valueOf(xiangchashu), list.get(j));}}if(arr.length>1){for(int i = 0;i<arr.length-1;i++){//外层循环n-1for(int j = 0;j<arr.length-i-1;j++){//内层循环n-i-1if(arr[j]>arr[j+1]){//从第一个开始，往后两两比较大小，如果前面的比后面的大，交换位置int tmp = arr[j];arr[j] = arr[j+1];arr[j+1] = tmp;}}}}if(map.size()>0){baseChanpinbomsublistEntityByParent = map.get(String.valueOf(arr[0]));}}return baseChanpinbomsublistEntityByParent;}
这个问题是本人项目当中月到的真是业务场景，虽然是解决了但是在导入表格时效率还是比较慢的，还就是在实体类的时候添加一个层级字段，更为方便以后的业务拓展找数据的时候更快速的找到你所需要的数据，这个本人也是经过这次之后才能树形层级字段的重要性，特此提醒。
如果广大网友有更好的经验可以加本人QQ一起学习分享经验，谢谢。 QQ：510482904
导入数据全部找到父类之后，查看一下最后展示树形结果
