public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors) { bool rtn = true; var targetFile = new FileInfo(filePath); if (!targetFile.Exists) { errors.Add("导入的数据文件不存在"); return(false); } var excelFile = new ExcelQueryFactory(filePath); using (XLWorkbook wb = new XLWorkbook(filePath)) { int headId = 0; var inventoryType = "抽检"; //第一个Sheet using (IXLWorksheet wws = wb.Worksheets.First()) { //对应列头 excelFile.AddMapping <WMS_Inventory_DModel>(x => x.Inventory_HName, "盘点名称(必输)"); excelFile.AddMapping <WMS_Inventory_DModel>(x => x.PartCode, "物料编码(必输)"); excelFile.AddMapping <WMS_Inventory_DModel>(x => x.InventoryQty, "盘点数量(必输)"); excelFile.AddMapping <WMS_Inventory_DModel>(x => x.InvName, "库房名称"); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.SubInvId, "子库存"); excelFile.AddMapping <WMS_Inventory_DModel>(x => x.Lot, "批次号(必输)"); excelFile.AddMapping <WMS_Inventory_DModel>(x => x.Remark, "备注"); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.Attr1, ""); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.Attr2, ""); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.Attr3, ""); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.Attr4, ""); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.Attr5, ""); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.CreatePerson, "创建人"); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.CreateTime, "创建时间"); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.ModifyPerson, "修改人"); //excelFile.AddMapping<WMS_Inventory_DModel>(x => x.ModifyTime, "修改时间"); //SheetName,第一个Sheet var excelContent = excelFile.Worksheet <WMS_Inventory_DModel>(0); //开启事务 using (DBContainer db = new DBContainer()) { var tran = db.Database.BeginTransaction(); //开启事务 int rowIndex = 0; //检查数据正确性 foreach (var row in excelContent) { rowIndex += 1; string errorMessage = String.Empty; var model = new WMS_Inventory_DModel(); model.Id = row.Id; model.Inventory_HName = row.Inventory_HName; model.PartCode = row.PartCode; model.InventoryQty = row.InventoryQty; model.InvName = row.InvName; model.Lot = row.Lot; //model.SubInvId = row.SubInvId; model.Remark = row.Remark; model.Attr1 = row.Inventory_HName;//用来标记是这次新增的记录,为了做验证检查 //model.Attr1 = row.Attr1; //model.Attr2 = row.Attr2; //model.Attr3 = row.Attr3; //model.Attr4 = row.Attr4; //model.Attr5 = row.Attr5; //model.CreatePerson = row.CreatePerson; //model.CreateTime = row.CreateTime; //model.ModifyPerson = row.ModifyPerson; //model.ModifyTime = row.ModifyTime; if (!String.IsNullOrEmpty(errorMessage)) { rtn = false; errors.Add(string.Format("第 {0} 列发现错误:{1}{2}", rowIndex, errorMessage, "<br/>")); wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage; continue; } //执行额外的数据校验 try { AdditionalCheckExcelData(db, ref model); } catch (Exception ex) { rtn = false; errorMessage = ex.Message; errors.Add(string.Format("第 {0} 列发现错误:{1}{2}", rowIndex, errorMessage, "<br/>")); wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage; continue; } headId = model.HeadId; inventoryType = model.InventoryType; //写入数据库 Expression <Func <WMS_Inventory_D, bool> > exp = x => x.PartId == model.PartId && x.InvId == model.InvId && x.Lot == model.Lot && x.HeadId == model.HeadId; WMS_Inventory_D entity; entity = db.WMS_Inventory_D.FirstOrDefault(exp); //WMS_Inventory_D entity1 = m_Rep.GetSingleWhere(model.Id); if (entity != null) { entity.InventoryQty = model.InventoryQty; entity.Attr1 = model.Attr1; } else { entity = new WMS_Inventory_D(); entity.Id = model.Id; entity.HeadId = model.HeadId; entity.PartId = model.PartId; entity.SnapshootQty = 0; entity.InventoryQty = model.InventoryQty; entity.Lot = model.Lot; entity.InvId = model.InvId; entity.SubInvId = model.SubInvId; entity.Remark = model.Remark; entity.Attr1 = model.Attr1; //entity.Attr2 = model.Attr2; //entity.Attr3 = model.Attr3; //entity.Attr4 = model.Attr4; //entity.Attr5 = model.Attr5; //entity.CreatePerson = model.CreatePerson; //entity.CreateTime = model.CreateTime; //entity.ModifyPerson = model.ModifyPerson; //entity.ModifyTime = model.ModifyTime; entity.CreatePerson = oper; entity.CreateTime = DateTime.Now; entity.ModifyPerson = oper; entity.ModifyTime = DateTime.Now; db.WMS_Inventory_D.Add(entity); } try { db.SaveChanges(); } catch (Exception ex) { rtn = false; //将当前报错的entity状态改为分离,类似EF的回滚(忽略之前的Add操作) db.Entry(entity).State = System.Data.Entity.EntityState.Detached; errorMessage = ex.InnerException.InnerException.Message; errors.Add(string.Format("第 {0} 列发现错误:{1}{2}", rowIndex, errorMessage, "<br/>")); wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage; } } if (rtn) { //tran.Commit(); if (inventoryType == "抽检") { ObjectParameter returnValue = new ObjectParameter("ReturnValue", typeof(string)); db.Configuration.EnsureTransactionsForFunctionsAndCommands = false; db.P_WMS_SpecialInventory(oper, headId, returnValue); if (returnValue.Value == DBNull.Value) { tran.Commit(); } else { tran.Rollback(); } //db.P_WMS_SpecialInventory(oper, headId, returnValue); //if (String.IsNullOrEmpty(SpecialInventory(ref errors, oper, headId))) //if (String.IsNullOrEmpty(db.P_WMS_SpecialInventory(oper, headId, returnValue)) //{ // tran.Commit(); //必须调用Commit(),不然数据不会保存 //} //else //{ // tran.Rollback(); //出错就回滚 //} } else { tran.Commit(); } } else { tran.Rollback(); //出错就回滚 } } } wb.Save(); //if (inventoryType == "抽检") //{ // if (String.IsNullOrEmpty(SpecialInventory(ref errors, oper, headId))) // { // rtn = true; //必须调用Commit(),不然数据不会保存 // } // else // { // rtn = false; ; //出错就回滚 // } //} return(rtn); } }