public List <ItemWithQty> ConvertToItemWithQtyList(List <WCF_ItemWithQty> list) { List <ItemWithQty> list1 = new List <ItemWithQty>(); foreach (WCF_ItemWithQty i in list) { Item item = new Item(); item = cpoc.GetItemByItem_ID(i.Item_ID); ItemWithQty it = new ItemWithQty(); it.Item_ID = item.Item_ID; it.Category_ID = item.Category_ID; it.Item_Name = item.Item_Name; it.Reorder_Level = item.Reorder_Level; it.Reorder_Qty = item.Reorder_Qty; it.UOM = item.UOM; it.Bin_ID = item.Bin_ID; it.FirstSupplier_ID = item.FirstSupplier_ID; it.SecondSupplier_ID = item.SecondSupplier_ID; it.ThirdSupplier_ID = item.ThirdSupplier_ID; it.Inventory = item.Inventory; it.Status = item.Status; it.Qty = i.Qty; list1.Add(it); } return(list1); }
public static Task <string> FromExcel(FileInfo excelFile, IDataService dataService) { List <Item> items = null; dataService.GetItems(((itemNumbers, materials, exception) => { if (exception != null) { throw exception; } items = itemNumbers.ToList(); })); var cocs = new List <COC>(); //try //{ using (var excel = new ExcelPackage(excelFile)) { var stopwatch = new Stopwatch(); stopwatch.Start(); var worksheet = excel.Workbook.Worksheets[1]; var endRow = worksheet.Dimension.End.Row; var qty = 0; for (var i = 1; i <= endRow; i++) { var qtyParse = int.TryParse(worksheet.Cells[i, 3].Value.ToString(), out var cellQty); if (qtyParse == false) { continue; } if (i != endRow && worksheet.Cells[i, 1].Value.ToString() == worksheet.Cells[i + 1, 1].Value.ToString()) { if (qty == 0) { qty = cellQty; } var qtyBelowParse = int.TryParse(worksheet.Cells[i, 3].Value.ToString(), out var qtyBelow); if (qtyBelowParse == false) { continue; } qty += qtyBelow; continue; } if (qty == 0) { qty = cellQty; } var cocNumberParse = int.TryParse(worksheet.Cells[i, 1].Value.ToString(), out var cocNumber); if (cocNumberParse == false) { qty = 0; continue; } var dateParse = DateTime.TryParse(worksheet.Cells[i, 4].Value.ToString(), out var inventoryDate); if (dateParse == false) { inventoryDate = DateTime.Now; } var itemNumber = worksheet.Cells[i, 2].Value.ToString(); var newItem = items.FirstOrDefault(itm => string.Equals(itm.ItemName, itemNumber, StringComparison.CurrentCultureIgnoreCase)); ItemWithQty itemWithQty; if (newItem == null) { newItem = new Item { ItemName = itemNumber }; dataService.Add(newItem); items.Add(newItem); itemWithQty = new ItemWithQty { Item = newItem, Quantity = qty }; } else { itemWithQty = new ItemWithQty { Item = newItem, Quantity = qty }; } var coc = new COC { Item = itemWithQty, CocNumber = cocNumber, InventoryDate = inventoryDate }; qty = 0; cocs.Add(coc); } dataService.BulkAdd(cocs); stopwatch.Stop(); Console.WriteLine(stopwatch.Elapsed); var message = $"Success"; dataService.SaveData(); return(Task.FromResult(result: message)); } //} //catch (NullReferenceException exception) //{ // Console.WriteLine(exception); // throw; // Task.FromResult(result: Malformed excel file + Environment.NewLine exception.Message); //} }
private static IEnumerable <ItemWithQty> GetItems(ICollection <Item> items, List <PacklisteData> packlisteData, IDataService dataService) { if (items == null) { throw new ArgumentNullException(nameof(items)); } var packlisteItems = new List <ItemWithQty>(); var quantityColumn = packlisteData.Find(c => c.Data != null && (c.Data.IndexOf("qty", StringComparison.OrdinalIgnoreCase) >= 0 || c.Data.IndexOf("antal", StringComparison.OrdinalIgnoreCase) >= 0)).ColumnNumber; var itemData = packlisteData.Where(d => d.ColumnNumber == 3 && (d.Data?.IndexOf("industri", StringComparison.OrdinalIgnoreCase) < 0 && d.Data.IndexOf("total", StringComparison.OrdinalIgnoreCase) < 0 && d.Data.IndexOf("item", StringComparison.OrdinalIgnoreCase) < 0 && d.Data.IndexOf("id", StringComparison.OrdinalIgnoreCase) < 0 && d.Data.IndexOf("varenum", StringComparison.OrdinalIgnoreCase) < 0)); foreach (var item in itemData) { var newItem = items.FirstOrDefault(itm => string.Equals(itm.ItemName, item.Data, StringComparison.CurrentCultureIgnoreCase)); ItemWithQty itemWithQty; float quantityValue; var quantityResult = float.TryParse( packlisteData.Find(f => f.RowNumber == item.RowNumber && f.ColumnNumber == quantityColumn)? .Data, out quantityValue); if (quantityResult) { var floatResult = float.TryParse(quantityValue.ToString(), out quantityValue); if (!floatResult) { quantityValue = 0f; } } else { quantityValue = 0f; } if (newItem == null) { newItem = new Item { ItemName = item.Data }; dataService.Add(newItem); items.Add(newItem); itemWithQty = new ItemWithQty { Item = newItem, Quantity = quantityValue }; } else { itemWithQty = new ItemWithQty { Item = newItem, Quantity = quantityValue }; } packlisteItems.Add(itemWithQty); } return(packlisteItems); }