/// <summary> /// 读取Excel表,返回库存集合 /// </summary> /// <param name="sheetName">sheet名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <param name="fileName">文件路径</param> /// <returns></returns> public List <ExtractInventoryTool_Inventory> ExcelToInventoryList(string sheetName, string fileName, ExtractInventoryTool_Client client, out string errorMessage) { IWorkbook workbook = null; ISheet sheet = null; List <ExtractInventoryTool_Inventory> result = new List <ExtractInventoryTool_Inventory>(); errorMessage = string.Empty; List <string> uniqueCodeList = new List <string>(); try { using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { if (fileName.IndexOf(".xlsx") > 0) // 2007版本 { workbook = new XSSFWorkbook(fs); } else if (fileName.IndexOf(".xls") > 0) // 2003版本 { workbook = new HSSFWorkbook(fs); } if (string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheetAt(0); } else { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } if (sheet == null) { return(result); } #region 获取所有当前客户的物料 DataTable dt = new ExtractInventoryTool_MaterialBLL().QueryMaterialByClientID(client.Oid.ToString()); List <ExtractInventoryTool_Material> materialList = new List <ExtractInventoryTool_Material>(); foreach (DataRow row in dt.Rows) { materialList.Add(new ExtractInventoryTool_Material() { Oid = Int32.Parse(row["Oid"].ToString()), Code = row["Code"].ToString(), Name = row["Name"].ToString(), SupplierCode = row["SupplierCode"].ToString(), Supplier = row["Supplier"].ToString(), UniqueCode = row["UniqueCode"].ToString() }); } #endregion #region 读取Sheet int rowNum = sheet.LastRowNum; for (int i = 5; i <= rowNum; i++) { //一行就是一条库存信息 IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.GetCell(1) == null) { continue; } string materialCode = ConvertCellToString(row.GetCell(1)); string supplierCode = ConvertCellToString(row.GetCell(3)); //判断物料是否已备案 ExtractInventoryTool_Material rowMaterial = materialList .FirstOrDefault(m => m.Code.Trim().Equals(materialCode) && m.SupplierCode.Trim().Equals(supplierCode)); if (rowMaterial == null) { errorMessage = string.Format("第{0}行物料号{1}供应商代码{2}没有在系统备案", i + 1, materialCode, supplierCode); return(result); } string uniqueCode = rowMaterial.UniqueCode; //判断是否存在重复行 if (uniqueCodeList.Contains(uniqueCode))//这里判断一下有没有同种物料重复,如果有,直接返回,输出重复物料 { errorMessage = string.Format("第{0}行物料号{1}供应商代码{2}存在重复库存记录", i + 1, materialCode, supplierCode); return(result); } uniqueCodeList.Add(uniqueCode); ExtractInventoryTool_Inventory inventory = new ExtractInventoryTool_Inventory(); inventory.SysInventory = Convert.ToInt32(ConvertCellToString(row.GetCell(5), "0")); //系统库存 inventory.Min = Convert.ToInt32(ConvertCellToString(row.GetCell(6), "0")); //MIN inventory.Max = Convert.ToInt32(ConvertCellToString(row.GetCell(7), "0")); //MAX inventory.HUB = Convert.ToInt32(ConvertCellToString(row.GetCell(8), "0")); //HUB库存 inventory.InTransit = Convert.ToInt32(ConvertCellToString(row.GetCell(9), "0")); //在途库存 inventory.Total = Convert.ToInt32(ConvertCellToString(row.GetCell(10), "0")); //总库存 inventory.Material = rowMaterial.Oid; result.Add(inventory); } #endregion } return(result); } catch (Exception ex) { throw ex; } }
public bool ImportInventory(List <ExtractInventoryTool_Inventory> inventoryList, bool isCover, out string errorMessage) { lock (lockObj) { try { errorMessage = string.Empty; if (inventoryList == null || inventoryList.Count == 0) { errorMessage = "库存信息为空"; return(false); } //先查询出所有的BOM唯一码 DataTable allInventory = GetAllUniqueCode("Inventory", "Material"); List <ExtractInventoryTool_Inventory> allBOMList = new List <ExtractInventoryTool_Inventory>(); foreach (DataRow row in allInventory.Rows) { allBOMList.Add(new ExtractInventoryTool_Inventory() { Oid = Int32.Parse(row["Oid"].ToString()), Material = Int32.Parse(row["Material"].ToString()) }); } StringBuilder insertStrbd = new StringBuilder(); StringBuilder updateStrbd = new StringBuilder(); List <SQLiteParameter[]> insertParamList = new List <SQLiteParameter[]>(); List <SQLiteParameter[]> updateParamList = new List <SQLiteParameter[]>(); foreach (var inventory in inventoryList) { ExtractInventoryTool_Inventory exitsInventory = allBOMList.FirstOrDefault(m => m.Material == inventory.Material); if (exitsInventory != null && exitsInventory.Oid != 0)//导入数据如果表里有就更新,没有就新建 { if (!isCover) { errorMessage = "存在重复数据,是否覆盖?"; return(true); } inventory.Oid = exitsInventory.Oid; } SQLiteParameter[] parameter = { SQLiteHelper.MakeSQLiteParameter("@Oid", DbType.Int32, inventory.Oid), SQLiteHelper.MakeSQLiteParameter("@Material", DbType.Int32, inventory.Material), SQLiteHelper.MakeSQLiteParameter("@SysInventory", DbType.Int32, inventory.SysInventory), SQLiteHelper.MakeSQLiteParameter("@Min", DbType.Int32, inventory.Min), SQLiteHelper.MakeSQLiteParameter("@Max", DbType.Int32, inventory.Max), SQLiteHelper.MakeSQLiteParameter("@HUB", DbType.Int32, inventory.HUB), SQLiteHelper.MakeSQLiteParameter("@InTransit", DbType.Int32, inventory.InTransit), SQLiteHelper.MakeSQLiteParameter("@Total", DbType.Int32, inventory.Total) }; if (exitsInventory != null && exitsInventory.Oid != 0) { updateParamList.Add(parameter); } else { insertParamList.Add(parameter); } } //添加新数据 insertStrbd.Append(@"Insert into Inventory (Material,SysInventory,Min,Max,HUB,InTransit,Total) ") .Append(@"values ( ") .Append(@"@Material,@SysInventory,@Min,@Max,@HUB,@InTransit,@Total ") .Append(@")"); new SQLiteHelper().ExecuteNonQueryBatch(insertStrbd.ToString(), insertParamList); updateStrbd.Append(@"Update Inventory set Material=@Material,SysInventory=@SysInventory,Min=@Min,Max=@Max,HUB=@HUB,InTransit=@InTransit,Total=@Total ") .Append(@" WHERE Oid=@Oid"); new SQLiteHelper().ExecuteNonQueryBatch(updateStrbd.ToString(), updateParamList); return(true); } catch (Exception ex) { throw ex; } } }