//////////////////////////////////////////////// private void AddPackingItem(PackingListItem item) { ParkingParserItem parserItem = null; DatabaseItem dataItem = databaseInfor.FindItem(item.GetNameProduct()); if (dataItem == null) { this.errorCode += item.GetNameProduct() + ": is empty in database\r\n"; dataItem = new DatabaseItem(); } if (listPackingParser == null) { listPackingParser = new List <ParkingParserItem>(); } //add new if (listPackingParser.Count == 0) { parserItem = new ParkingParserItem(item.GetNameProduct(), item, dataItem); listPackingParser.Add(parserItem); return; } //add to exist for (int index = 0; index < listPackingParser.Count; index++) { if (item.GetNameProduct() == listPackingParser.ElementAt(index).GetNameProduct()) { listPackingParser.ElementAt(index).AddNewItemInList(item); return; } } parserItem = new ParkingParserItem(item.GetNameProduct(), item, dataItem); //add new listPackingParser.Add(parserItem); return; }
public void AddNewItemInList(PackingListItem item) { if (listItem == null) { listItem = new List <PackingListItem>(); } if (item != null && this.databaseItem != null) { int phan_nguyen = (int)(item.GetQuantity() / databaseItem.GetMaxPacketSize()); int phan_du = (int)(item.GetQuantity()) - (int)(phan_nguyen * databaseItem.GetMaxPacketSize()); if (phan_du == 0 || phan_nguyen == 0) { if (phan_nguyen == 0) { item.SetNeedMerger(true); } else { item.SetNeedMerger(false); } listItem.Add(item); } else { //add phan nguyen item.SetQuantity(phan_nguyen * databaseItem.GetMaxPacketSize()); item.SetNeedMerger(false); listItem.Add(item); //add phan du PackingListItem item_du = new PackingListItem(); item_du.SetColor1(item.GetColor1()); item_du.SetColor2(item.GetColor2()); item_du.SetNameProduct(item.GetNameProduct()); item_du.SetProductSize(item.GetProductSize()); item_du.SetQuantity(phan_du); item_du.SetNeedMerger(true); listItem.Add(item_du); } } }
public ParkingParserItem(String name, PackingListItem item, DatabaseItem databaseItem) { this.nameProduct = name; this.databaseItem = databaseItem; this.listItem = new List <PackingListItem>(); if (item != null) { int phan_nguyen = (int)(item.GetQuantity() / databaseItem.GetMaxPacketSize()); int phan_du = (int)(item.GetQuantity()) - (int)(phan_nguyen * databaseItem.GetMaxPacketSize()); if (phan_du == 0 || phan_nguyen == 0) { if (phan_nguyen == 0) { item.SetNeedMerger(true); } else { item.SetNeedMerger(false); } listItem.Add(item); } else { //add phan nguyen item.SetQuantity(phan_nguyen * databaseItem.GetMaxPacketSize()); item.SetNeedMerger(false); listItem.Add(item); //add phan du PackingListItem item_du = new PackingListItem(); item_du.SetColor1(item.GetColor1()); item_du.SetColor2(item.GetColor2()); item_du.SetNameProduct(item.GetNameProduct()); item_du.SetProductSize(item.GetProductSize()); item_du.SetQuantity(phan_du); item_du.SetNeedMerger(true); listItem.Add(item_du); } } }
public void AddMergerItem(PackingListItem packingItem) { MergerItem item = new MergerItem(); item.SetColor1(packingItem.GetColor1()); item.SetColor2(packingItem.GetColor2()); item.SetNameProduct(packingItem.GetNameProduct()); item.SetProductSize(packingItem.GetProductSize()); item.SetQuantity(packingItem.GetQuantity()); if (this.mergerList == null) { this.mergerList = new List <MergerItem>(); } this.mergerList.Add(item); }
//////////////////////////////////////////////////////////////////// private void CreateRowValueSheet_1(PackingListItem item, DatabaseItem databaseItem) { NPOI.SS.UserModel.IRow r; if (item.GetQuantity() < databaseItem.GetMaxPacketSize()) { r = sheet_1.CreateRow(rowIndex_sheet_1); rowIndex_sheet_1++; numberPacket_sheet_1++; double valueMerger = item.GetQuantity(); if (item.GetMergerList() != null && item.GetMergerList().Count > 0) { foreach (MergerItem merger in item.GetMergerList()) { valueMerger += merger.GetQuantity(); } } for (int idex = 0; idex < Sheet_1_Header.Length; idex++) { ICell cell = r.CreateCell(idex); cell.CellStyle = sheet_1_headerStyle; switch (idex) { case 0: cell.SetCellValue(this.packingParser.GetDateTime()); break; case 1: cell.SetCellValue(item.GetNameProduct()); break; case 2: cell.SetCellValue(item.GetColor1()); break; case 3: cell.SetCellValue(item.GetColor2()); break; case 4: cell.SetCellValue(item.GetProductSize()); break; case 5: cell.SetCellValue(item.GetQuantity()); if (valueMerger > item.GetQuantity()) { cell.CellStyle = sheet_1_headerStyle_2; } break; case 6: cell.SetCellValue(1); break; case 7: cell.SetCellValue(numberPacket_sheet_1); break; case 8: if (valueMerger <= item.GetQuantity()) { cell.SetCellValue(valueMerger); } break; case 9: cell.SetCellValue(databaseItem.GetNetWeight()); break; case 10: cell.SetCellValue(databaseItem.GetAllWeight()); break; case 11: cell.SetCellValue(databaseItem.GetPacketInformation()); break; default: break; } } } else { for (int tmp = 0; tmp < (int)(item.GetQuantity() / databaseItem.GetMaxPacketSize()); tmp++) { numberPacket_sheet_1++; r = sheet_1.CreateRow(rowIndex_sheet_1); rowIndex_sheet_1++; for (int idex = 0; idex < Sheet_1_Header.Length; idex++) { ICell cell = r.CreateCell(idex); cell.CellStyle = sheet_1_headerStyle; switch (idex) { case 0: cell.SetCellValue(this.packingParser.GetDateTime()); break; case 1: cell.SetCellValue(item.GetNameProduct()); break; case 2: cell.SetCellValue(item.GetColor1()); break; case 3: cell.SetCellValue(item.GetColor2()); break; case 4: cell.SetCellValue(item.GetProductSize()); break; case 5: cell.SetCellValue(databaseItem.GetMaxPacketSize()); break; case 6: cell.SetCellValue(1); break; case 7: cell.SetCellValue(numberPacket_sheet_1); break; case 8: cell.SetCellValue(databaseItem.GetMaxPacketSize()); break; case 9: cell.SetCellValue(databaseItem.GetNetWeight()); break; case 10: cell.SetCellValue(databaseItem.GetAllWeight()); break; case 11: cell.SetCellValue(databaseItem.GetPacketInformation()); break; default: break; } } } } if (item.GetMergerList() != null && item.GetMergerList().Count > 0) { foreach (MergerItem merger in item.GetMergerList()) { r = sheet_1.CreateRow(rowIndex_sheet_1); rowIndex_sheet_1++; for (int idex = 0; idex < Sheet_1_Header.Length; idex++) { ICell cell = r.CreateCell(idex); cell.CellStyle = sheet_1_headerStyle; switch (idex) { case 0: cell.SetCellValue(this.packingParser.GetDateTime()); break; case 1: cell.SetCellValue(merger.GetNameProduct()); break; case 2: cell.SetCellValue(merger.GetColor1()); break; case 3: cell.SetCellValue(merger.GetColor2()); break; case 4: cell.SetCellValue(merger.GetProductSize()); break; case 5: cell.SetCellValue(merger.GetQuantity()); cell.CellStyle = sheet_1_headerStyle_2; break; case 6: break; case 7: cell.SetCellValue(numberPacket_sheet_1); break; case 8: break; case 9: //cell.SetCellValue(databaseItem.GetNetWeight()); break; case 10: //cell.SetCellValue(databaseItem.GetAllWeight()); break; case 11: //cell.SetCellValue(databaseItem.GetPacketInformation()); break; default: break; } } } r = sheet_1.GetRow(rowIndex_sheet_1 - (1 + item.GetMergerList().Count)); String fomular = "SUM(F" + (rowIndex_sheet_1 - item.GetMergerList().Count) + ":F" + rowIndex_sheet_1 + ")"; if (r.GetCell(8) != null) { r.GetCell(8).CellFormula = fomular; } NPOI.SS.Util.CellRangeAddress range_1 = new NPOI.SS.Util.CellRangeAddress(rowIndex_sheet_1 - (1 + item.GetMergerList().Count), rowIndex_sheet_1 - 1, 6, 6); NPOI.SS.Util.CellRangeAddress range_2 = new NPOI.SS.Util.CellRangeAddress(rowIndex_sheet_1 - (1 + item.GetMergerList().Count), rowIndex_sheet_1 - 1, 8, 8); sheet_1.AddMergedRegion(range_1); sheet_1.AddMergedRegion(range_2); } }
public CombineMergerListItem(PackingListItem packItem) { this.packItem = packItem; listCombineItem = new List <CombineItem>(); }
public bool PackingListParser(String file) { bool addItem; PackingListItem item = null; if (!File.Exists(file)) { this.errorCode = "Find not found\r\n"; return(false); } listPackingParser = new List <ParkingParserItem>(); try { // get sheets list from xlsx FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read); XSSFWorkbook wb = new XSSFWorkbook(fs); if (wb.Count <= 0) { this.errorCode = "Sheet in file is empty \r\n"; return(false); } XSSFSheet sh = (XSSFSheet)wb.GetSheetAt(0); int index = 0; while (sh.GetRow(index) != null) { //ignore if (index == 0 || index == 1 || index == 2) { index++; continue; } //handle date time if (index == 3) { if (sh.GetRow(index).Cells.Count >= 4) { var cell = sh.GetRow(index).GetCell(3); if (cell != null && cell.CellType == NPOI.SS.UserModel.CellType.String && cell.StringCellValue != null) { int position = cell.StringCellValue.IndexOf("日期"); if (position >= 0) { position = cell.StringCellValue.IndexOf(":"); if (position >= 0) { this.dateTime = cell.StringCellValue.Substring(position + 1); } } } } index++; continue; } //handle date time if (index == 4) { if (this.dateTime == "") { if (sh.GetRow(index).Cells.Count >= 0) { var cell = sh.GetRow(index).GetCell(0); if (cell != null && cell.CellType == NPOI.SS.UserModel.CellType.String && cell.StringCellValue != null) { int position = cell.StringCellValue.IndexOf("主 旨"); if (position >= 0) { position = cell.StringCellValue.IndexOf(":"); int last_post = cell.StringCellValue.IndexOf("日"); if (position >= 0 && (last_post - 1) > position) { this.dateTime = cell.StringCellValue.Substring(position + 1, last_post - position); } } } } } index++; continue; } //ignore if (index == 5) { index++; continue; } addItem = false; if (sh.GetRow(index).Cells.Count >= 5) { var cell = sh.GetRow(index).GetCell(4); if (cell != null) { if (cell.CellType == NPOI.SS.UserModel.CellType.String && cell.StringCellValue != null) { addItem = true; item = new PackingListItem(); item.SetNameProduct(cell.StringCellValue); } else if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric) { addItem = true; item = new PackingListItem(); item.SetNameProduct(cell.NumericCellValue + ""); } else if (cell.CellType == NPOI.SS.UserModel.CellType.Blank) { fs.Close(); return(true); } } } if (!addItem) { index++; continue; } if (sh.GetRow(index).Cells.Count >= 6) { var cell = sh.GetRow(index).GetCell(5); if (cell != null) { if (cell.CellType == NPOI.SS.UserModel.CellType.String && cell.StringCellValue != null) { item.SetColor1(cell.StringCellValue); } else if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric) { item.SetColor1(cell.NumericCellValue + ""); } } } if (sh.GetRow(index).Cells.Count >= 7) { var cell = sh.GetRow(index).GetCell(6); if (cell != null) { if (cell.CellType == NPOI.SS.UserModel.CellType.String && cell.StringCellValue != null) { item.SetColor2(cell.StringCellValue); } else if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric) { item.SetColor2(cell.NumericCellValue + ""); } } } if (sh.GetRow(index).Cells.Count >= 8) { var cell = sh.GetRow(index).GetCell(7); if (cell != null) { if (cell.CellType == NPOI.SS.UserModel.CellType.String && cell.StringCellValue != null) { item.SetProductSize(cell.StringCellValue); } else if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric) { item.SetProductSize(cell.NumericCellValue + ""); } } } if (sh.GetRow(index).Cells.Count >= 9) { var cell = sh.GetRow(index).GetCell(8); if (cell != null) { if (cell.CellType == NPOI.SS.UserModel.CellType.String && cell.StringCellValue != null) { item.SetQuantity(Convert.ToDouble(cell.StringCellValue)); } else if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric) { item.SetQuantity(cell.NumericCellValue); } } } //add to list AddPackingItem(item); index++; } fs.Close(); } catch (IOException ex) { this.errorCode = ex.Message + "\r\n"; return(false); } return(true); }
private void SliptToMerger(ParkingParserItem parserItem) { PackingListItem packingItem; int base_index = 0; double totalQunatity = 0; double numberItem = 0; foreach (PackingListItem item in parserItem.GetListItem()) { if (item.IsNeedMerger()) { totalQunatity += item.GetQuantity(); numberItem++; } } if (totalQunatity >= parserItem.GetDatabaseItem().GetMaxPacketSize()) { double sliptQuantity = 0; PackingListItem itemAdd = null; for (int run = 0; run < parserItem.GetListItem().Count; run++) { if (parserItem.GetListItem().ElementAt(run).IsNeedMerger()) { base_index = run; break; } } //Need to slipt parserItem.GetListItem().ElementAt(base_index).SetNeedMerger(false); sliptQuantity = parserItem.GetDatabaseItem().GetMaxPacketSize() - parserItem.GetListItem().ElementAt(base_index).GetQuantity(); while (sliptQuantity > 0) { bool found = false; for (int run = base_index + 1; run < parserItem.GetListItem().Count; run++) { if (parserItem.GetListItem().ElementAt(run).IsNeedMerger()) { if (parserItem.GetListItem().ElementAt(run).GetQuantity() > sliptQuantity) { found = true; itemAdd = new PackingListItem(); itemAdd.SetColor1(parserItem.GetListItem().ElementAt(run).GetColor1()); itemAdd.SetColor2(parserItem.GetListItem().ElementAt(run).GetColor2()); itemAdd.SetNameProduct(parserItem.GetListItem().ElementAt(run).GetNameProduct()); itemAdd.SetNeedMerger(false); itemAdd.SetProductSize(parserItem.GetListItem().ElementAt(run).GetProductSize()); itemAdd.SetQuantity(sliptQuantity); parserItem.GetListItem().ElementAt(run).SetQuantity(parserItem.GetListItem().ElementAt(run).GetQuantity() - sliptQuantity); sliptQuantity = 0; parserItem.GetListItem().ElementAt(base_index).AddMergerItem(itemAdd); } else { found = true; packingItem = parserItem.GetListItem().ElementAt(run); packingItem.SetNeedMerger(false); parserItem.GetListItem().RemoveAt(run); parserItem.GetListItem().ElementAt(base_index).AddMergerItem(packingItem); sliptQuantity -= packingItem.GetQuantity(); } break; } } if (!found) { break; } } SliptToMerger(parserItem); } else /*less than*/ if (numberItem != 0) { for (int run = 0; run < parserItem.GetListItem().Count; run++) { if (parserItem.GetListItem().ElementAt(run).IsNeedMerger()) { parserItem.GetListItem().ElementAt(run).SetNeedMerger(false); base_index = run; break; } } numberItem -= 1; while (numberItem > 0) { packingItem = null; if (base_index >= parserItem.GetListItem().Count) { break; } for (int run = base_index; run < parserItem.GetListItem().Count; run++) { if (parserItem.GetListItem().ElementAt(run).IsNeedMerger()) { packingItem = parserItem.GetListItem().ElementAt(run); packingItem.SetNeedMerger(false); parserItem.GetListItem().RemoveAt(run); break; } } if (packingItem != null) { parserItem.GetListItem().ElementAt(base_index).AddMergerItem(packingItem); } numberItem -= 1; } } }
private void CreateMergerItem(int type, ParkingParserItem item, PackingListItem packItem) { NPOI.SS.UserModel.IRow row; int start_index; int end_index; int total = Sheet_2_GetNumberOfColum(); CombineMergerListItem combine = new CombineMergerListItem(packItem); combine.RunCombineMergerListItem(); List <CombineItem> listCombine = combine.GetListCombineItem(); ICell cell; ICell cellSum = null; start_index = sheet_2_rowIndex; for (int itemIndex = 0; itemIndex < listCombine.Count; itemIndex++) { //create new row row = sheet_2.CreateRow(sheet_2_rowIndex); sheet_2_rowIndex++; for (int run = 0; run < Sheet_2_GetNumberOfColum(); run++) { cell = row.CreateCell(run); cell.CellStyle = sheet_2_mainStyle_2; switch (run) { case 0: if (itemIndex == 0) { sheet_2_numberPacket++; cell.SetCellValue(sheet_2_numberPacket + ""); } break; case 1: if (itemIndex == 0) { cell.SetCellValue(1); } break; case 2: cell.SetCellValue(packItem.GetNameProduct()); break; case 3: cell.SetCellValue(listCombine.ElementAt(itemIndex).GetColor1()); break; case 4: cell.SetCellValue(listCombine.ElementAt(itemIndex).GetColor2()); break; default: if ((total - 4) == run) { if (itemIndex == 0) { cellSum = cell; } } else if ((total - 3) == run) { if (itemIndex == 0) { cell.SetCellValue("PCS"); } } else if ((total - 2) == run) { if (itemIndex == 0) { String formula = item.GetDatabaseItem().GetNetWeight() + "*B" + sheet_2_rowIndex; cell.SetCellFormula(formula); } } else if ((total - 1) == run) { if (itemIndex == 0) { String formula = item.GetDatabaseItem().GetAllWeight() + "*B" + sheet_2_rowIndex; cell.SetCellFormula(formula); } } else { cell.SetCellValue(""); } break; } } foreach (CombineEle element in listCombine.ElementAt(itemIndex).GetListElement()) { int size_index = Sheet_2_GetColumIndex(type, element.GetSize()); ICell cellSize = row.GetCell(size_index); cellSize.SetCellValue(element.GetQuality()); } } end_index = sheet_2_rowIndex - 1; String columName = Sheet_2_GetStringOfColum(Sheet_2_GetNumberOfColum() - 4); String formulaSum = "SUM(F" + (start_index + 1) + ":" + columName + (end_index + 1) + ")"; if (cellSum != null) { cellSum.SetCellFormula(formulaSum); } if (end_index != start_index) { NPOI.SS.Util.CellRangeAddress range_1 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, 0, 0); NPOI.SS.Util.CellRangeAddress range_2 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, 1, 1); NPOI.SS.Util.CellRangeAddress range_3 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 4, total - 4); NPOI.SS.Util.CellRangeAddress range_4 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 3, total - 3); NPOI.SS.Util.CellRangeAddress range_5 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 2, total - 2); NPOI.SS.Util.CellRangeAddress range_6 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 1, total - 1); sheet_2.AddMergedRegion(range_1); sheet_2.AddMergedRegion(range_2); sheet_2.AddMergedRegion(range_3); sheet_2.AddMergedRegion(range_4); sheet_2.AddMergedRegion(range_5); sheet_2.AddMergedRegion(range_6); sheet_2.GetRow(start_index).GetCell(0).CellStyle = sheet_2_mergerStyle; sheet_2.GetRow(start_index).GetCell(1).CellStyle = sheet_2_mergerStyle; sheet_2.GetRow(start_index).GetCell(total - 4).CellStyle = sheet_2_mergerStyle; sheet_2.GetRow(start_index).GetCell(total - 3).CellStyle = sheet_2_mergerStyle; sheet_2.GetRow(start_index).GetCell(total - 2).CellStyle = sheet_2_mergerStyle; sheet_2.GetRow(start_index).GetCell(total - 1).CellStyle = sheet_2_mergerStyle; } }
private void CreateNotMergerItem(int type, ParkingParserItem item, PackingListItem packItem) { ICell cell; NPOI.SS.UserModel.IRow row; //create new row row = sheet_2.CreateRow(sheet_2_rowIndex); sheet_2_rowIndex++; int phan_nguyen = (int)(packItem.GetQuantity() / item.GetDatabaseItem().GetMaxPacketSize()); int size_index = Sheet_2_GetColumIndex(type, packItem.GetProductSize()); for (int run = 0; run < Sheet_2_GetNumberOfColum(); run++) { cell = row.CreateCell(run); cell.CellStyle = sheet_2_mainStyle_2; switch (run) { case 0: if (phan_nguyen > 1) { double from = sheet_2_numberPacket + 1; double to = sheet_2_numberPacket + phan_nguyen; cell.SetCellValue(from + " - " + to); sheet_2_numberPacket += phan_nguyen; } else { sheet_2_numberPacket++; cell.SetCellValue(sheet_2_numberPacket + ""); } break; case 1: if (phan_nguyen > 0) { cell.SetCellValue(phan_nguyen); } else { cell.SetCellValue(1); } break; case 2: cell.SetCellValue(packItem.GetNameProduct()); break; case 3: cell.SetCellValue(packItem.GetColor1()); break; case 4: cell.SetCellValue(packItem.GetColor2()); break; default: if (run == size_index) { if (phan_nguyen > 0) { String formula = item.GetDatabaseItem().GetMaxPacketSize() + "*$B" + sheet_2_rowIndex; cell.SetCellFormula(formula); } else { cell.SetCellValue(packItem.GetQuantity()); } } else { int total = Sheet_2_GetNumberOfColum(); if ((total - 4) == run) { String columName = Sheet_2_GetStringOfColum(Sheet_2_GetNumberOfColum() - 4); String formula = "SUM(F" + sheet_2_rowIndex + ":" + columName + sheet_2_rowIndex + ")"; cell.SetCellFormula(formula); } else if ((total - 3) == run) { cell.SetCellValue("PCS"); } else if ((total - 2) == run) { String formula = item.GetDatabaseItem().GetNetWeight() + "*B" + sheet_2_rowIndex; cell.SetCellFormula(formula); } else if ((total - 1) == run) { String formula = item.GetDatabaseItem().GetAllWeight() + "*B" + sheet_2_rowIndex; cell.SetCellFormula(formula); } else { cell.SetCellValue(""); } } break; } } }