Beispiel #1
0
        //添加
        private void ReadExcelProductsAndInsert(string path,string name)
        {
            IWorkbook wb = WorkbookFactory.Create(path);

            int count = 0;

            //循环 sheet

            ISheet ist = wb.GetSheetAt(0);

            Guid traderID = Guid.Empty;
            string traderName = "";

            using (var db = new DB())
            {
                string shortname = stringZip(name);
                var query = (from a in db.CainzTrader
                             where a.TraderName.Contains(shortname)
                             select a).FirstOrDefault();
                if (query != null)
                {
                    traderID = query.TraderID;
                    traderName = query.TraderName;
                }
                else
                {
                    FTrader m = new FTrader(name);
                    if (m.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    {
                        traderID = FTrader.TRADER.TraderID;
                        traderName = FTrader.TRADER.TraderName;
                    }
                }

            }
            if (traderID == Guid.Empty) return;

            int rowofPage = ist.LastRowNum + 1;

            int titleRowNo = -1;
            int cdNo = -1;
            int caizhiNo = -1;
            int caizhiEXNo = -1;
            int chicunNo = -1;
            int danjiaNo = -1;
            int gongchangNo = -1;
            int wuliaoNo = -1;
            int yanseNo = -1;
            string gudinggcm = "";

            //查找各个位置
            foreach (IRow row in ist)
            {

                int cellCount = row.Cells.Count;
                for (int cNo = 0; cNo < cellCount; cNo++)
                {
                    ICell icell = row.GetCell(cNo, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if (icell.CellType == CellType.String)
                    {
                        string title = icell.StringCellValue.Trim().Replace("(元/个)", "").Replace("mm", "").Replace(" ", "").Trim();
                        switch (title)
                        {
                            case "商品条形码/CD":
                                {
                                    cdNo = icell.ColumnIndex;
                                } break;
                            case "材质":
                                {
                                    caizhiNo = icell.ColumnIndex;
                                } break;
                            case "材质说明":
                                {
                                    caizhiEXNo = icell.ColumnIndex;
                                } break;
                            case "颜色":
                                {
                                    yanseNo = icell.ColumnIndex;
                                } break;
                            case "单价":
                                {
                                    danjiaNo = icell.ColumnIndex;
                                } break;
                            case "尺寸":
                            case "尺寸()":
                                {
                                    chicunNo = icell.ColumnIndex;
                                } break;
                            case "厂商名":
                            case "订购工厂":
                            case "厂商名称":
                                {
                                    gongchangNo = icell.ColumnIndex;
                                } break;
                            case "物料编号":
                            case "物料名称":
                                {
                                    wuliaoNo = icell.ColumnIndex;
                                } break;
                        }
                    }
                }

                if (cdNo != -1)
                {
                    titleRowNo = row.RowNum;

                    if (gongchangNo == -1)
                    {
                        gudinggcm = findgcm(ist.SheetName, gGongChangDic);
                    }

                    break;
                }

            }

            Hashtable productHT = new Hashtable();
            using (var db = PublicDB.getDB())
            {

            for (int j = titleRowNo + 1; j < rowofPage; j++)
            {
                count++;
                IRow irow = ist.GetRow(j);

                if (irow == null) continue;

                string cdString = getCellString(cdNo, irow);
                if (cdString == "") continue;
                string caizhiString = getCellString(caizhiNo, irow);
                string chicunString = getCellString(chicunNo, irow);
                string danjiaString = getCellString(danjiaNo, irow);
                string yanseString = getCellString(yanseNo, irow);
                string caizhiEXString = getCellString(caizhiEXNo, irow);
                string gongchangString = getCellString(gongchangNo, irow);
                string wuliaoString = getCellString(wuliaoNo, irow);

                string com = cdString + caizhiString + chicunString + yanseString + caizhiEXString + wuliaoString;
                double danjiaDouble = getCellDouble(danjiaNo, irow);

                if (com.Length < 6) continue;
                if (false == productHT.ContainsKey(com))
                {

                        CainzProduct p = new CainzProduct();
                        p.ProductID = Guid.NewGuid();
                        p.ProductBarcode = cdString;
                        p.ProductColor = yanseString;
                        p.ProductMaterial = caizhiString;
                        p.ProductSize = chicunString;
                        p.ProductPrice = (System.Decimal)danjiaDouble;
                        p.TraderID = traderID;
                        p.TraderName = traderName;
                        p.CainzTraderTraderID = traderID;
                        p.Deleted = 0;
                        p.CreateTime = DateTime.Now;
                        p.ModifyTime = DateTime.Now;
                        p.Modified = 0;
                        if (traderID != Guid.Empty)
                        {
                            db.CainzProduct.Add(p);
                        }
                        productHT.Add(com, null);
                    }

                }

            db.SaveChanges();

            }
        }
Beispiel #2
0
        private void ReadProductsAndInsert(string path)
        {
            IWorkbook wb = WorkbookFactory.Create(path);

            int count = 0;

            //循环 sheet
            for (int i = 1; i < wb.NumberOfSheets; i++)
            {

                ISheet ist = wb.GetSheetAt(i);
                if (ist.SheetName.Contains("heet")) continue;

                int rowofPage = ist.LastRowNum + 1;

                int titleRowNo = -1;
                int cdNo = -1;
                int caizhiNo = -1;
                int caizhiEXNo = -1;
                int chicunNo = -1;
                int danjiaNo = -1;
                int gongchangNo = -1;
                int wuliaoNo = -1;
                int yanseNo = -1;
                string gudinggcm = "";

                //查找各个位置
                foreach (IRow row in ist)
                {
                    int cellCount = row.Cells.Count;
                    for (int cNo = 0; cNo < cellCount; cNo++)
                    {
                        ICell icell = row.GetCell(cNo, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        if (icell.CellType == CellType.String)
                        {
                            string title = icell.StringCellValue.Trim().Replace("(元/个)", "").Replace("mm", "").Replace(" ", "").Trim();
                            switch (title)
                            {
                                case "CD":
                                    {
                                        cdNo = icell.ColumnIndex;
                                    } break;
                                case "材质":
                                    {
                                        caizhiNo = icell.ColumnIndex;
                                    } break;
                                case "材质说明":
                                    {
                                        caizhiEXNo = icell.ColumnIndex;
                                    } break;
                                case "颜色":
                                    {
                                        yanseNo = icell.ColumnIndex;
                                    } break;
                                case "单价":
                                    {
                                        danjiaNo = icell.ColumnIndex;
                                    } break;
                                case "尺寸":
                                    {
                                        chicunNo = icell.ColumnIndex;
                                    } break;
                                case "厂商名":
                                case "工厂名":
                                case "厂商名称":
                                    {
                                        gongchangNo = icell.ColumnIndex;
                                    } break;
                                case "物料编号":
                                case "物料名称":
                                    {
                                        wuliaoNo = icell.ColumnIndex;
                                    } break;
                            }
                        }
                    }

                    if (cdNo != -1)
                    {
                        titleRowNo = row.RowNum;

                        if (gongchangNo == -1)
                        {
                            gudinggcm = findgcm(ist.SheetName, gGongChangDic);
                        }

                        break;
                    }

                }

                Hashtable productHT = new Hashtable();

                for (int j = titleRowNo + 1; j < rowofPage; j++)
                {
                    count++;
                    IRow irow = ist.GetRow(j);

                    if (irow == null) continue;

                    string cdString = getCellString(cdNo, irow);
                    if (cdString == "") continue;
                    string caizhiString = getCellString(caizhiNo, irow);
                    string chicunString = getCellString(chicunNo, irow);
                    string danjiaString = getCellString(danjiaNo, irow);
                    string yanseString = getCellString(yanseNo, irow);
                    string caizhiEXString = getCellString(caizhiEXNo, irow);
                    string gongchangString = getCellString(gongchangNo, irow);
                    string wuliaoString = getCellString(wuliaoNo, irow);

                    if (gongchangString == "常熟市帝网织造有限公司")
                    {
                        i++;
                    }
                    Guid factoryID;
                    if (gongchangString == "")
                    {
                        gongchangString = ist.SheetName.Trim();
                    }

                    CainzFactory cc = this.GetFactoryByName(gongchangString);
                    if (cc == null)
                    {
                        cc = this.GetFactoryByName(ist.SheetName.Trim());
                    }

                    if (cc != null)
                    {
                        factoryID = cc.FactoryID;
                        gongchangString = cc.FactoryName;
                    }

                    string com = cdString + caizhiString + chicunString + danjiaString + yanseString + caizhiEXString + wuliaoString + gongchangString;
                    double price = getCellDouble(danjiaNo, irow);

                    if (com.Length < 6) continue;
                    if (false == productHT.ContainsKey(com))
                    {
                        using (var db = PublicDB.getDB())
                        {
                            CainzProduct p = new CainzProduct();
                            p.ProductBarcode = cdString;
                            p.ProductColor = yanseString;
                            p.ProductMaterial = caizhiString;
                            p.ProductSize = chicunString;
                            p.ProductPrice = (System.Decimal)price;
                            p.CreateTime = DateTime.Now;
                            db.CainzProduct.Add(p);
                            db.Entry(p).State = System.Data.Entity.EntityState.Added;
                            db.SaveChanges();
                        }

                        productHT.Add(com, null);
                    }
                }

            }
        }
Beispiel #3
0
        private void btnsynicProduct_Click(object sender, EventArgs e)
        {
            int x, y, z,zz;
            x = y = z = zz=0;
            using (var db2 = new entityTmp.db001Entities())
            {
                var query = (from a in db2.CainzProduct
                             where a.Modified == 1
                             select a).ToList();
                using (var db = PublicDB.getDB())
                {
                    foreach (var cf in query)
                    {
                        var q = from b in db.CainzProduct
                                where b.ProductBarcode == cf.Barcode
                                select b;
                        if (q != null && q.Count() == 1)
                        {
                            x++;
                            entity.CainzProduct p = q.ToList()[0];
                            p.Modified = 1;
                            p.ModifyTime = DateTime.Now;
                            p.ProductMaterial = cf.Material;
                            p.ProductColor = cf.Color;
                            p.ProductPrice = cf.Price;
                            p.ProductSize = cf.Size;
                            db.Entry(p).State = System.Data.Entity.EntityState.Modified;

                        }
                        else if (q == null && q.Count() > 0)
                        {
                            y++;
                        }
                        else
                        {
                            entity.CainzProduct p = new CainzProduct();

                            p.ProductID = Guid.NewGuid();
                            p.Modified = 1;
                            p.ModifyTime = DateTime.Now;
                            p.CreateTime = DateTime.Now;
                            p.Deleted = 0;
                            p.ProductName = "";
                            p.ProductBarcode = cf.Barcode;
                            p.ProductMaterial = cf.Material;
                            p.ProductColor = cf.Color;
                            p.ProductPrice = cf.Price;
                            p.ProductSize = cf.Size;
                            p.ProductID = Guid.NewGuid();
                            var trader = db.CainzTrader.Where(t => t.TraderName == cf.TraderName).FirstOrDefault();
                            if (trader != null)
                            {
                                p.TraderID = trader.TraderID;
                                p.TraderName = trader.TraderName;
                                p.CainzTraderTraderID = trader.TraderID;
                                db.CainzProduct.Add(p);
                            }
                            else
                            {
                                MessageBox.Show(cf.Barcode);
                                zz++;
                            }
                            z++;
                        }

                    }
                    try
                    {
                        db.SaveChanges();
                    }
                    catch (Exception ee)
                    {
                        MessageBox.Show(ee.Message);
                    }
                }
            }
            MessageBox.Show("finished");
        }