//解析FreeCountry的packing List(不需要summary的版本) public void ParseFreeCountryPackingListV2(int preId) { var preReceiveOrderInDb = _context.PreReceiveOrders.Find(preId); _ws = _wb.Worksheets[2]; //首先检查文件是否符合FC模板格式,并计算PO summary的数量 var poList = CheckFCTemplateAndReturnPOSummaryIndexList(preReceiveOrderInDb); _context.POSummaries.AddRange(poList); var cartonList = new List <RegularCartonDetail>(); foreach (var po in poList) { var startIndex = po.Id; po.Id = 0; //获取size的数量 var endColumnIndex = 12; while (_ws.Cells[startIndex + 2, endColumnIndex].Value2 != "Pack") { endColumnIndex += 1; } //获取SKU的数量 var endRowIndex = startIndex + 3; while (_ws.Cells[endRowIndex, 1].Value2 != "Totals") { endRowIndex += 1; } endColumnIndex -= 1; endRowIndex -= 1; for (int i = startIndex + 3; i <= endRowIndex; i++) { var cartonRange = _ws.Cells[i, 1].Value2.ToString(); var purchaseOrder = _ws.Cells[i, 2].Value2.ToString(); var style = _ws.Cells[i, 3].Value2.ToString(); var customer = _ws.Cells[i, 4].Value2.ToString(); var colorCode = _ws.Cells[i, 9].Value2.ToString(); var color = _ws.Cells[i, 10].Value2.ToString(); var cartons = (int)_ws.Cells[i, 11].Value2; for (int j = 12; j <= endColumnIndex; j++) { var size = _ws.Cells[startIndex + 2, j].Value2.ToString(); var pcs = (int)_ws.Cells[i, j].Value2; var newCartnDetail = new RegularCartonDetail { CartonRange = cartonRange, PurchaseOrder = purchaseOrder, Style = style, Customer = customer, Color = color, ColorCode = colorCode, Cartons = j == 12 ? cartons : 0, PcsBundle = pcs.ToString(), PcsPerCarton = pcs, Quantity = pcs * cartons, POSummary = po, Comment = "", OrderType = OrderType.SolidPack, Operator = _userName, Adjustor = "", Receiver = "", Batch = po.Batch, Vendor = Vendor.FreeCountry, SizeBundle = size, Status = Status.NewCreated }; cartonList.Add(newCartnDetail); } } } _context.RegularCartonDetails.AddRange(cartonList); _context.SaveChanges(); _wb.Save(); var killer = new ExcelKiller(); killer.Dispose(); }
public string GeneratePreallocatingReport(string container) { var cartonDetails = _context.RegularCartonDetails .Include(x => x.POSummary) .Where(x => x.POSummary.Container == container && x.Cartons != 0); _ws = _wb.Worksheets[1]; _ws.Cells[1, 2] = container; var startIndex = 4; var maxLocs = 6; var parser = new StringParser(); foreach (var c in cartonDetails) { _ws.Cells[startIndex, 1] = c.CartonRange; _ws.Cells[startIndex, 2] = c.PurchaseOrder; _ws.Cells[startIndex, 3] = c.Style; _ws.Cells[startIndex, 4] = c.Customer; _ws.Cells[startIndex, 5] = c.Color; _ws.Cells[startIndex, 6] = c.Cartons; if (c.PreLocation == null) { startIndex++; continue; } var list = parser.ParseStrToPreLoc(c.PreLocation).ToList(); for (var i = 0; i < list.Count(); i++) { if (list[i].Plts == 1) { _ws.Cells[startIndex, 8 + (i * 2)] = list[i].Ctns; } else { _ws.Cells[startIndex, 8 + (i * 2)] = list[i].Ctns + "X" + list[i].Plts; } _ws.Cells[startIndex, 9 + (i * 2)] = list[i].Location; } maxLocs = Math.Max(maxLocs, list.Count()); startIndex++; } //补全Loc在表中显示的数量 if (maxLocs > 6) { var dif = maxLocs - 6; for (int i = 0; i < dif; i++) { _ws.Cells[3, 20 + i] = (6 + i + 1).ToString() + "th"; _ws.Cells[3, 21 + i] = "LOC"; } } var fullPath = @"D:\OtherReport\Pre-allocatingReport-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx"; _wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing); _excel.Quit(); var killer = new ExcelKiller(); killer.Dispose(); return(fullPath); }