//解析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);
}