U8导入根据Excel导入BOM

    用友的Bom输入过程比较繁琐,虽然有实施工具可以通过Excel生成bom.但是那个Excel需要的格式内容繁多,本质来说是给实施顾问用的,对用户来说,并不友好.

    本文给出了一个通过Excel模板整理一个最少内容的BOM结构.支持多层子件的关联,先在u8中生成一个完成的BOM结构,至于缺少的内容,完全可以通过批量修改(甚至于通过Excel批量生成update语句)的方式,再更新其他缺少的内容,大为降低BOM输入的工作量

    U8的BOM涉及

bom_bom bom主档

bom_opcomponent BOM子件资料

bom_parent BOM母件资料

bom_opcomponentopt 子件选项资料

bom_opcomponentsub 子件替代料

通过直接插入数据的方式生成BOM结构

Excel格式(原始文件参考资源中的Excel格式,红色字段为必须输入的)

Excel第2行,是根母件的编码

 

通过指定Excel文件后,利用NPOI组件选择导入,把Excel读取到系统的中,利用Devexpress的树结构,循环递归生成,生成树结构参考代码:

  txtMsg.Text = "";

            _dataSource.Clear();
            _nodes.Clear();
            trvStruct.Nodes.Clear();


            //XtraMessageBox.Show("请确保需要到导入的数据位于Excel中的第1张表", "重要提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);

            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Excel文件|*.xls;*.xlsx";
            if (dlg.ShowDialog() != DialogResult.OK)
                return;

            ctlPath.Text = dlg.FileName;

            DevExpress.Utils.WaitDialogForm fm = new DevExpress.Utils.WaitDialogForm();
            fm.Caption = "正在读取数据...";
            try
            {
                DataTable dt = NPOIUtils.Import(dlg.FileName, fm);

                if (dt == null)
                    return;
                int i = 0;

                foreach (DataRow row in dt.Rows)
                {
                    if (row["子件编码"].ToString() != "")
                    {
                        SourceExl item = new SourceExl
                        {
                            BaseQty = row["基本用量"].ToString() == "" ? 0 : decimal.Parse(row["基本用量"].ToString()),
                            InvCode = row["子件编码"].ToString(),
                            InvStd = row["子件规格"].ToString(),
                            InvName = row["子件名称"].ToString(),
                            LevelChar = row["级别"].ToString().Trim(),
                            LineId = i,
                            NQty = row["使用数量"].ToString() == "" ? 0 : decimal.Parse(row["使用数量"].ToString()),
                            Price =0,// row["单价"].ToString() == "" ? 0 : decimal.Parse(row["单价"].ToString()),
                            Unit = row["子件计量单位"].ToString(),
                            SubChangeInvCode = row["替代料存货编码"].ToString(),
                            Version =10,// row["技术BOM版本号"].ToString() == "" ? 0 : int.Parse(row["技术BOM版本号"].ToString()),
                            BaseUnitQty = row["基础数量"].ToString() == "" ? 0 : decimal.Parse(row["基础数量"].ToString()),
                            Level = row["级别"].ToString().Trim().Length,
                            OpSeq = row["工序行号"].ToString(),
                            AuxUnitCode = row["辅助计量单位编码"].ToString().Trim(),
                            AuxBaseQtyN = 0m,
                            ChangeRate = 0m
                        };
                        i++;
                        _dataSource.Add(item);
                    }
                }


                foreach (var item in _dataSource)
                {
                    TreeNode node = new TreeNode();
                    node.Name = item.LineId.ToString();
                    node.Text = item.InvCode;
                    if (item.Level == 0)
                    {
                        _nodes.Add(item.LineId, node);
                        trvStruct.Nodes.Add(node);
                        continue;
                    }
                    var parentInv = (from parItem in _dataSource
                                     where parItem.Level == item.Level - 1
                                    && parItem.LineId < item.LineId
                                     select parItem).Max(t => t.LineId);
                    TreeNode parentNode = _nodes[parentInv];
                    parentNode.Nodes.Add(node);
                    _nodes.Add(item.LineId, node);
                }

,即可写入U8生成U8的BOM

U8的连接字符串位于:App.Config中

主要的三张表的写入过程为:

int parent = 0;
                int child = 0;
                GetId(1, "bom_bom", ref parent, ref child);
                //parent += 1;
                //child += 1;
                bom_bom newbom = new bom_bom();
                newbom.BomId = child;
                newbom.BomType = 1;
                newbom.Version = NewBOMVersion;
                newbom.VersionDesc = "升级版本";
                newbom.VersionEffDate = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
                newbom.VersionEndDate = DateTime.Parse("2099/12/31");
                newbom.CreateDate = DateTime.Parse(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd"));
                newbom.CreateUser = Config.UserName;
                newbom.UpdCount = 0;
                newbom.VTid = 30442;
                newbom.Status = 3;
                newbom.RelsDate = DateTime.Parse(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd"));
                newbom.RelsUser = Config.UserName;
                newbom.CreateTime = DateTime.Now;
                newbom.Define11 = TopInvCode + " " + NewBOMVersion;
                db.bom_boms.InsertOnSubmit(newbom);
                db.SubmitChanges();

                bom_parent parentItem = new bom_parent();
                parentItem.AutoId = Guid.NewGuid();
                parentItem.BomId = child;
                string invCode = _dataSource.SingleOrDefault(t => t.LineId == int.Parse(node.Name)).InvCode;
                parentItem.ParentId = db.bas_parts.SingleOrDefault(t => t.InvCode == invCode).PartId;
                parentItem.ParentScrap = 0;
                parentItem.SharingPartId = 0;
                db.bom_parents.InsertOnSubmit(parentItem);
                db.SubmitChanges();

                int parentOpt = 0;
                int childOpt = 0;

                GetId(node.Nodes.Count, "bom_opcomponent", ref parent, ref child);
                GetId(node.Nodes.Count, "bom_opcomponentopt", ref parentOpt, ref childOpt);

                int loop = 0;
                foreach (TreeNode subNode in node.Nodes)
                {
                    ImportNodeToU8(db, subNode);
                    int subLineId = int.Parse(subNode.Name);
                    int childId = child - (node.Nodes.Count - 1) + loop;
                    int childOptId = childOpt - (node.Nodes.Count - 1) + loop;
                    bom_opcomponentopt opt = new bom_opcomponentopt();
                    opt.OptionsId = childOptId;
                    opt.Offset = 0;
                    opt.WIPType = 3;
                    opt.AccuCostFlag = true;
                    opt.OptionalFlag = false;
                    opt.MutexRule = 2;
                    opt.PlanFactor = 100;

                    db.bom_opcomponentopts.InsertOnSubmit(opt);

                    bom_opcomponent opc = new bom_opcomponent();
                    opc.OpComponentId = childId;
                    opc.BomId = newbom.BomId;
                    opc.SortSeq = (loop + 1) * 10;
                    var subInv = _dataSource.SingleOrDefault(t => t.LineId == subLineId);
                    opc.OpSeq = subInv.OpSeq;
                    opc.ComponentId = db.bas_parts.SingleOrDefault(t => t.InvCode == subInv.InvCode).PartId;
                    opc.EffBegDate = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
                    opc.EffEndDate = DateTime.Parse("2099/12/31");
                    opc.FVFlag = 1;
                    opc.BaseQtyN = subInv.BaseQty;
                    opc.BaseQtyD = subInv.BaseUnitQty;
                    opc.CompScrap = 0;
                    opc.ByproductFlag = false;
                    opc.ProductType = 1;
                    opc.OptionsId = childOptId;
                   // opc.Define26 = (double)subInv.Price;

                    if (subInv.AuxUnitCode != null && subInv.AuxUnitCode != "")
                    {
                        opc.ChangeRate = subInv.ChangeRate;
                        opc.AuxBaseQtyN = subInv.AuxBaseQtyN;
                        opc.AuxUnitCode = subInv.AuxUnitCode.Trim();
                    }

                    db.bom_opcomponents.InsertOnSubmit(opc);
                    loop++;
                }
                db.SubmitChanges();

详细代码:

链接：https://pan.baidu.com/s/1RBpl1-Ko0AONKZpOirIZ3g 
提取码：BPAB