private void OutputToExcel() { ExcelPackage package = null; ExcelWorksheet workSheet = null; OpenFileDialog ofd = null; if (InvokeRequired) { ofd = Invoke(new dSelectOutput(selectOutput)) as OpenFileDialog; } if (ofd == null) { return; } workSheet = ExcelToll.Load(ref package, ofd.FileName, txtFormat.Text); if (workSheet == null) { Log("Failed to load worksheet "); return; } // output column locations var customerPO = ExcelToll.GetCellColumn(workSheet, "CUSTOMER PO #", 0); var invoiceNO = ExcelToll.GetCellColumn(workSheet, "INVOICE #", 0); var conId = ExcelToll.GetCellColumn(workSheet, "CONSIGNMENT REFERENCE", 0); var date = ExcelToll.GetCellColumn(workSheet, "DATE DELIVERED", 0); var pickup = ExcelToll.GetCellColumn(workSheet, "Date of Pickup", 0); var pieces1 = ExcelToll.GetCellColumn(workSheet, "Pieces", 0); var pieces2 = ExcelToll.GetCellColumn(workSheet, "Pieces", 1); var anspec = ExcelToll.GetCellColumn(workSheet, "Anspec Date", 0); var courier = ExcelToll.GetCellColumn(workSheet, "Courier", 0); anspec = anspec == 0 ? ExcelToll.GetCellColumn(workSheet, "DBS Date", 0) : anspec; // prevent crash if a column is missing if (customerPO == 0 || invoiceNO == 0 || conId == 0 || date == 0 || pickup == 0 || anspec == 0) { Log("Failed to find one of the columns"); return; } var donelist = new List <Delivery>(); var matches = 0; FindMatchesByInvoiceID(ExcelToll.GetColumnRange(workSheet, "INVOICE #"), workSheet, anspec, pickup, ref matches, donelist, courier, pieces1, pieces2, customerPO, invoiceNO, conId, date); FindMatchesByCustomerPo(ExcelToll.GetColumnRange(workSheet, "CUSTOMER PO #"), workSheet, anspec, pickup, ref matches, donelist, courier, pieces1, pieces2, customerPO, invoiceNO, conId, date); if (donelist.Count == deliveries.Count) { return; } var notDone = deliveries.Where(d => !donelist.Contains(d)).ToList(); deliveries = notDone; Log($"{matches} matches updated"); package.Save(); // show details of deliveries not found in output for manual assignment var frm = new Form2(deliveries); frm.ShowDialog(); // only process the orders that could not be matched to speed up future processing }
private void ReadExcel() { var isNZInput = false; var isAutoUpdate = false; ExcelPackage package = null; ExcelWorksheet workSheet = null; var ofd = new OpenFileDialog { Filter = @"Excel Files|*.xlsx;*.xlsm;*.xls;*.csv;", Title = @"Select Output File" }; if (ofd.ShowDialog() != DialogResult.OK) { return; } workSheet = ExcelToll.Load(ref package, ofd.FileName, "SHIPPED"); //deliveries.Clear(); // only continue if excel file loaded if (package == null) { return; } /*workSheet = ExcelToll.Load(ref package, ofd.FileName, "SHIPPED") ?? * ExcelToll.Load(ref package, ofd.FileName, "BNMA") ?? * ExcelToll.Load(ref package, ofd.FileName, "BNM STATS") ?? * ExcelToll.Load(ref package, ofd.FileName, "ABBOTTS STATS");*/ if (workSheet == null) { // loads packages multiple times // workSheet = ExcelToll.Load(ref package, ofd.FileName, "BNMA") ?? ExcelToll.Load(ref package, ofd.FileName, "BNM STATS")?? ExcelToll.Load(ref package, ofd.FileName, "ABBOTTS STATS"); workSheet = ExcelToll.GetWorksheet(package, "BNMA") ?? ExcelToll.GetWorksheet(package, "BNM STATS") ?? ExcelToll.GetWorksheet(package, "ABBOTTS STATS"); if (package.Workbook.Worksheets.Any(w => w.Name.ToUpper() == "BNMA")) { // if there is a worksheet called BNMA /BNMNZ / BMA then it is reprocessing. isAutoUpdate = true; var work = package.Workbook.Worksheets.FirstOrDefault(w => string.Equals(w.Name, txtFormat.Text, StringComparison.CurrentCultureIgnoreCase)); workSheet = work ?? package.Workbook.Worksheets.First(); } else if (package.Workbook.Worksheets.Any(w => w.Name.ToUpper() == "BNM STATS" || w.Name.ToUpper() == "ABBOTTS STATS")) { isNZInput = true; } else { Log("Failed to load worksheet "); return; } } if (package.Workbook.Worksheets.Any(w => w.Name.ToUpper() == "BNMA")) { // if there is a worksheet called BNMA /BNMNZ / BMA then it is reprocessing. isAutoUpdate = true; var work = package.Workbook.Worksheets.FirstOrDefault(w => string.Equals(w.Name, txtFormat.Text, StringComparison.CurrentCultureIgnoreCase)); workSheet = work ?? package.Workbook.Worksheets.First(); } else if (package.Workbook.Worksheets.Any(w => w.Name.ToUpper() == "BNM STATS" || w.Name.ToUpper() == "ABBOTTS STATS")) { isNZInput = true; } // adds all rows that are are missing the date delivered if (isAutoUpdate) { // read columns into lists var invoiceIds = ExcelToll.GetColumn(workSheet, "INVOICE #"); var customerPOs = ExcelToll.GetColumn(workSheet, "CUSTOMER PO #"); var conIds = ExcelToll.GetColumn(workSheet, "CONSIGNMENT REFERENCE"); var dates = ExcelToll.GetColumn(workSheet, "DATE DELIVERED"); var courier = ExcelToll.GetColumn(workSheet, "COURIER"); if (invoiceIds == null || customerPOs == null || conIds == null || dates == null || courier == null) { Log("Failed to find all columns with the correct names"); return; } // row 94 failed in excel range // changed to default for loop for (var i = 0; i < conIds.Count; i++) { if (i >= dates.Count) { continue; } if (dates[i] == "") { if (string.IsNullOrWhiteSpace(conIds[i])) { continue; } // Console.WriteLine($"{i + 3} {customerPOs[i]} {invoiceIds[i]} {conIds[i]} {courier[i]}"); deliveries.Add(new Delivery(customerPOs[i], invoiceIds[i], conIds[i], "Unknown", courier[i], new DateTime())); } } } else if (isNZInput) { workSheet = package.Workbook.Worksheets.First(w => w.Name.ToUpper() == "BNM STATS" || w.Name.ToUpper() == "ABBOTTS STATS"); var consignmentIds = ExcelToll.GetColumn(workSheet, "Order #"); var courier = ExcelToll.GetColumn(workSheet, "Carrier"); var pickup = ExcelToll.GetColumn(workSheet, "First scan Date"); var pieces = ExcelToll.GetColumn(workSheet, "No. of Cartons"); if (consignmentIds == null || courier == null || pickup == null) { Log("Failed to find all columns with the correct names"); return; } for (var i = 0; i < consignmentIds.Count; i++) { if (string.IsNullOrWhiteSpace(consignmentIds[i])) { continue; } deliveries.Add(new Delivery(string.Empty, "NZ" + consignmentIds[i].Substring(3), consignmentIds[i], "Unknown", courier[i], new DateTime(), DateTime.ParseExact(pickup[i], "d/MM/yyyy", new DateTimeFormatInfo()), pieces[i])); } } // adds all deliveries by default else { // read columns into lists var invoiceIds = ExcelToll.GetColumn(workSheet, "PACKSLIP"); var customerPOs = ExcelToll.GetColumn(workSheet, "CUST REF"); var conIds = ExcelToll.GetColumn(workSheet, "CON NOTE NUMBER"); var pieces = ExcelToll.GetColumn(workSheet, "Cartons"); var pickup = ExcelToll.GetColumn(workSheet, "Shipped Date"); if (invoiceIds == null || customerPOs == null || conIds == null || pickup == null || pieces == null) { Log("Failed to find all columns with the correct names"); return; } for (var i = 0; i < pickup.Count; i++) { if (string.IsNullOrWhiteSpace(pickup[i])) { pickup[i] = pickup[i - 1]; } } for (var i = 0; i < conIds.Count; i++) { if (string.IsNullOrWhiteSpace(conIds[i])) { continue; } deliveries.Add(new Delivery(customerPOs[i], invoiceIds[i], conIds[i], "Unknown", "Toll", new DateTime(), DateTime.ParseExact(pickup[i], "d/MM/yyyy", new DateTimeFormatInfo()), pieces[i]) ); } } // remove certain entries var num = 0; deliveries = deliveries.GroupBy(d => d.conID).Select(group => group.First()).ToList(); deliveries.ForEach(i => i.invoiceID = i.invoiceID.Replace("GS", "")); //deliveries.ForEach(i => i.invoiceID = i.invoiceID.Replace("NZ", "")); deliveries.RemoveAll(i => i.invoiceID.ToUpper() == "SAMPLES" || i.invoiceID.ToUpper() == "PLES" || i.invoiceID.ToUpper() == "REPLACEMENT"); deliveries.RemoveAll(i => i.conID.ToUpper() == "TRANSFER" || int.TryParse(i.conID, out num)); // deliveries.RemoveAll(i => !i.conID.Contains("ARE")); // split into separate lists matched by courier // makes it easier to process groupedDeliveries = deliveries.GroupBy(i => i.courier).Select(grp => grp.ToList()).ToList(); Log($"Done Loading input {deliveries.Count}"); btnRun.Enabled = true; btnOut.Enabled = true; }