//添加
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();
}
}
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);
}
}
}
}
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");
}