/// <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;
}
}
}
