/// <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;
}
}
/// <summary>
/// 读取Excel表，返回BOM集合
/// </summary>
/// <param name="fileName">文件路径</param>
/// <param name="client">客户信息</param>
/// <returns></returns>
public List <ExtractInventoryTool_BOM> ExcelToBOMList(string fileName, ExtractInventoryTool_Client client, out string errorMessage)
{
List <ExtractInventoryTool_BOM> result = new List <ExtractInventoryTool_BOM>();
errorMessage = string.Empty;
#region 读取客户配置
string clientRuleConfig = ConfigurationManager.AppSettings[client.UniqueCode + "BOM"].ToString();
ExtractInventoryTool_BOMImportRule clientRule = JsonConvert.DeserializeObject <ExtractInventoryTool_BOMImportRule>(clientRuleConfig);
#endregion
IWorkbook workbook = null;
ISheet sheet = null;
List <string> uniqueCodeList = new List <string>();
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
#region 获取Excel
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
sheet = workbook.GetSheetAt(clientRule.St);
if (sheet == null)
{
return(result);
}
#endregion
#region 读取Excel
IRow vehicleCodeRow = sheet.GetRow(clientRule.VR);
#region 读取BOM的思路
// 每一行都是唯一一个物料，供应商代码和物料号作为唯一标识，这里做一个行列的双循环，在每一行中，有相同车型代码
// 的不同用量，先收集用量不为0的所有用量，然后按车型代码分组，取同种车型代码用量最大的用量，加入到最终结果中
// Meritar_Jeffrey 2021/04/05 02:25:25
#endregion
#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(),
SupplierCode = row["SupplierCode"].ToString(),
UniqueCode = row["UniqueCode"].ToString()
});
}
#endregion
for (int i = clientRule.MR; i <= sheet.LastRowNum; i++)//外循环--行
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.GetCell(clientRule.MC) == null)
{
continue;
}
string materialCode = ConvertCellToString(row.GetCell(clientRule.MC));
string supplierCode = ConvertCellToString(row.GetCell(clientRule.SC));
if (string.IsNullOrEmpty(materialCode))
{
continue;
}
List <ExtractInventoryTool_BOM> rowBomList = new List <ExtractInventoryTool_BOM>();
//判断物料是否已备案
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);
}
bool isExist = false;
string uniqueCode = rowMaterial.UniqueCode;
//判断是否存在重复行
// 这里如果有重复行不再直接返回，而且新增bom和老bom取最大值
// Meritar_Jeffrey 2021/04/16 09:45:14
if (uniqueCodeList.Contains(uniqueCode))//这里判断一下有没有同种物料重复，如果有，直接返回，输出重复物料
{
//errorMessage = string.Format("第{0}行物料号{1}供应商代码{2}存在重复BOM记录", i + 1, materialCode, supplierCode);
//return result;
isExist = true;
}
else
{
uniqueCodeList.Add(uniqueCode);
}
for (int j = clientRule.VC; j <= row.LastCellNum - 3; j++)//内循环--列
{
ICell cell = row.GetCell(j);
if (cell == null || string.IsNullOrEmpty(cell.ToString()))
{
continue;
}
int unitUsage = 0;
unitUsage = Convert.ToInt32(cell.ToString());
if (unitUsage == 0)
{
continue;
}
ExtractInventoryTool_BOM bomCell = new ExtractInventoryTool_BOM();
bomCell.VehicleModelCode = Regex.Match(ConvertCellToString(vehicleCodeRow.GetCell(j)), client.RegexRule).Value;
bomCell.UnitUsage = unitUsage;
rowBomList.Add(bomCell);
}
IEnumerable <IGrouping <string, ExtractInventoryTool_BOM> > group = rowBomList.GroupBy(v => v.VehicleModelCode);
foreach (var groupItem in group)
{
ExtractInventoryTool_BOM bom = new ExtractInventoryTool_BOM();
bom.UnitUsage = groupItem.Max(v => v.UnitUsage);
bom.VehicleModelCode = groupItem.Key;
bom.Material = rowMaterial.Oid;
bom.UpdateTime = DateTime.Now;
bom.UniqueCode = uniqueCode + "v" + groupItem.Key;
if (isExist)
{
var existBom = result.FirstOrDefault(b => b.UniqueCode.Equals(bom.UniqueCode));
if (existBom == null)
{
result.Add(bom);
}
else
{
existBom.UnitUsage = Math.Max(existBom.UnitUsage, bom.UnitUsage);
existBom.UpdateTime = DateTime.Now;
}
}
else
{
result.Add(bom);
}
}
}
#endregion
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}