private void generateVescoSpreadSheet() { Excel.Application excelObj2 = new Excel.Application(); Excel.Workbook vescoExcelWorkbook = null; Excel.Worksheet vescoExcelWorksheet = null; Excel.Worksheet locationWorkSheet = null; Excel.Range locationRange = null; Excel.Worksheet driverExcelWorksheet = null; Excel.Range driverRange = null; ExecutedOrder tempAssExOrder; try { vescoExcelWorkbook = excelObj2.Workbooks.Open(Application.StartupPath + "\\Templates\\Associated Template.xlsx"); // Re-Optimize if (checkBox1.Checked) { driverExcelWorksheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.get_Item("Driver"); driverRange = driverExcelWorksheet.UsedRange; int driverRowCount = driverRange.Rows.Count; int driverCount = 0; String tempDriverId; DateTime tempDateTime; DateTime earliestStartTime; DateTime dayEndsAt; double hoursElapsed; double newDrivingHours; double newDutyHours; // loop through all the drivers for (int i = 2; i <= driverRowCount; i++) // Start at the second row and skip the header { tempDriverId = driverRange[i, 1].Value2.ToString(); // find if this driver has been added to the list tempAssExOrder = assExOrders.Find( delegate(ExecutedOrder aeo) { return(aeo.Driver == tempDriverId); } ); if (tempAssExOrder != null) { tempDateTime = DateTime.FromOADate(driverRange[i, 4].Value2); earliestStartTime = tempAssExOrder.dtDevliveryDateTime.Date.AddHours(tempDateTime.Hour); dayEndsAt = earliestStartTime.AddHours(driverRange[i, 3].Value2); hoursElapsed = tempAssExOrder.dtDevliveryDateTime.Subtract(earliestStartTime).TotalHours; newDrivingHours = Convert.ToDouble(driverRange[i, 2].Value2) - hoursElapsed; newDrivingHours = newDrivingHours > 0 ? newDrivingHours : 0.0; newDutyHours = Convert.ToDouble(driverRange[i, 3].Value2) - hoursElapsed; newDutyHours = newDutyHours > 0 ? newDutyHours : 0.0; Console.WriteLine("Earliest Start Time -> " + earliestStartTime); Console.WriteLine("Available Driving Hours -> " + driverRange[i, 2].Value2); Console.WriteLine("Available Duty Hours -> " + driverRange[i, 3].Value2); Console.WriteLine("Last Delivery Time -> " + tempAssExOrder.dtDevliveryDateTime); Console.WriteLine("\tHours Elapsed -> " + hoursElapsed); Console.WriteLine("\tNew Earliest Start Time -> " + tempAssExOrder.dtDevliveryDateTime); Console.WriteLine("\tNew Driving Hours -> " + newDrivingHours); Console.WriteLine("\tNew Duty Hours -> " + newDutyHours); Console.WriteLine(); driverExcelWorksheet.Cells[i, 2] = newDrivingHours; driverExcelWorksheet.Cells[i, 3] = newDutyHours; driverExcelWorksheet.Cells[i, 4] = tempAssExOrder.dtDevliveryDateTime; driverExcelWorksheet.Cells[i, 5] = tempAssExOrder.DeliverTo; driverCount++; assExOrders.Remove(tempAssExOrder); } } } Console.Out.WriteLine(vescoOrders.Count()); List <String> badAssociatedOrders = AssociatedConverter.badOrders; foreach (String b in badAssociatedOrders) { Console.Out.WriteLine("Bad Associated Order#," + b); } Console.WriteLine(Application.StartupPath); vescoExcelWorksheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.get_Item("RouteStop"); //Location based stop delay locationWorkSheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.get_Item("Location"); locationRange = locationWorkSheet.UsedRange; int locationRowCount = locationRange.Rows.Count; List <SpecialLocation> specialLocations = new List <SpecialLocation>(); SpecialLocation specialLocation; for (int i = 2; i <= locationRowCount; i++) // Start at the second row and skip the header { if (locationRange[i, 6].Value2 != null) { specialLocation = new SpecialLocation(); specialLocation.Name = locationRange[i, 1].Value2.ToString(); specialLocation.Address = locationRange[i, 2].Value2.ToString(); specialLocation.StopDelay = locationRange[i, 6].Value2.ToString(); specialLocations.Add(specialLocation); } } specialLocation = new SpecialLocation(); String stopActionCode; String windowStart = null; int row = 2; //Add the converted orders foreach (VescoOrder vo in vescoOrders) { vescoExcelWorksheet.Cells[row, 1] = vo.Job; vescoExcelWorksheet.Cells[row, 2] = vo.Sequence; vescoExcelWorksheet.Cells[row, 3] = vo.StopAction; vescoExcelWorksheet.Cells[row, 4] = vo.Location; vescoExcelWorksheet.Cells[row, 5] = vo.Address; specialLocation = specialLocations.Find(item => item.Name == vo.Location && item.Address == vo.Address); stopActionCode = vo.StopAction.Split('-')[0]; if ("11".Equals(stopActionCode) || "12".Equals(stopActionCode)) { if (specialLocation != null) { vescoExcelWorksheet.Cells[row, 6] = specialLocation.StopDelay; } else { vescoExcelWorksheet.Cells[row, 6] = "60"; } } else { vescoExcelWorksheet.Cells[row, 6] = vo.StopDelay; } if (vo.WindowStart != null) { windowStart = (vo.WindowStart.Equals(vo.WindowEnd) ? null : vo.WindowStart); } else { windowStart = null; } vescoExcelWorksheet.Cells[row, 7] = windowStart; vescoExcelWorksheet.Cells[row, 8] = vo.WindowEnd; vescoExcelWorksheet.Cells[row, 9] = vo.Hazardous; vescoExcelWorksheet.Cells[row, 10] = vo.Overweight; vescoExcelWorksheet.Cells[row, 11] = vo.OriginalLegNumber; vescoExcelWorksheet.Cells[row, 12] = vo.ContainerNumber; vescoExcelWorksheet.Cells[row, 13] = vo.SteamshipLine; vescoExcelWorksheet.Cells[row, 14] = vo.LiveLoad; vescoExcelWorksheet.Cells[row, 15] = vo.OriginalLegType; vescoExcelWorksheet.Cells[row, 16] = vo.DispatcherSequence; row++; } // Delete the Vesco file first since I can't figure out how to get rid of the confirmation dialog File.Delete(Application.StartupPath + "\\Templates\\Vesco.xlsx"); vescoExcelWorkbook.SaveAs(Application.StartupPath + "\\Templates\\Vesco.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show("Error: Could not read " + label7.Text + ". Original error: " + ex.Message); } finally { if (vescoExcelWorksheet != null) { Marshal.ReleaseComObject(vescoExcelWorksheet); } if (vescoExcelWorkbook != null) { Marshal.ReleaseComObject(vescoExcelWorkbook); } if (excelObj2 != null) { excelObj2.Quit(); Marshal.ReleaseComObject(excelObj2); } } Vesco vesco = new Vesco(checkBox1.Checked, vescoOrders, badOrders, label7.Text, "Associated"); vesco.Show(); this.Hide(); vesco.performSteps(); }
private void shouldContinue() { if (isPmFileLoaded && isCosFileLoaded) { this.Cursor = Cursors.WaitCursor; Console.Out.WriteLine("File Name" + openFileDialog1.FileName); Excel.Application excelObj2 = new Excel.Application(); Excel.Workbook vescoExcelWorkbook = null; Excel.Worksheet vescoExcelWorksheet = null; Excel.Worksheet driverExcelWorksheet = null; Excel.Range driverRange = null; ExecutedOrder tempSwExOrder; try { Console.Out.WriteLine(vescoOrders.Count()); List <String> badSouthwestOrders = SouthwestConverter.badOrders; foreach (String b in badSouthwestOrders) { Console.Out.WriteLine("Bad Southwest Order#," + b); } vescoExcelWorkbook = excelObj2.Workbooks.Open(Application.StartupPath + "\\Templates\\Southwest Template.xlsx"); // Re-Optimize if (checkBox1.Checked) { driverExcelWorksheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.get_Item("Driver"); driverRange = driverExcelWorksheet.UsedRange; int driverRowCount = driverRange.Rows.Count; int driverCount = 0; String tempDriverId; DateTime tempDateTime; DateTime earliestStartTime; DateTime dayEndsAt; double hoursElapsed; double newDrivingHours; double newDutyHours; // loop through all the drivers for (int i = 2; i <= driverRowCount; i++) // Start at the second row and skip the header { tempDriverId = driverRange[i, 1].Value2.ToString(); // find if this driver has been added to the list tempSwExOrder = swExOrders.Find( delegate(ExecutedOrder aeo) { return(aeo.Driver == tempDriverId); } ); if (tempSwExOrder != null) { tempDateTime = DateTime.FromOADate(driverRange[i, 4].Value2); earliestStartTime = tempSwExOrder.dtDevliveryDateTime.Date.AddHours(tempDateTime.Hour); dayEndsAt = earliestStartTime.AddHours(driverRange[i, 3].Value2); hoursElapsed = tempSwExOrder.dtDevliveryDateTime.Subtract(earliestStartTime).TotalHours; newDrivingHours = Convert.ToDouble(driverRange[i, 2].Value2) - hoursElapsed; newDrivingHours = newDrivingHours > 0 ? newDrivingHours : 0.0; newDutyHours = Convert.ToDouble(driverRange[i, 3].Value2) - hoursElapsed; newDutyHours = newDutyHours > 0 ? newDutyHours : 0.0; Console.WriteLine("Earliest Start Time -> " + earliestStartTime); Console.WriteLine("Available Driving Hours -> " + driverRange[i, 2].Value2); Console.WriteLine("Available Duty Hours -> " + driverRange[i, 3].Value2); Console.WriteLine("Last Delivery Time -> " + tempSwExOrder.dtDevliveryDateTime); Console.WriteLine("\tHours Elapsed -> " + hoursElapsed); Console.WriteLine("\tNew Earliest Start Time -> " + tempSwExOrder.dtDevliveryDateTime); Console.WriteLine("\tNew Driving Hours -> " + newDrivingHours); Console.WriteLine("\tNew Duty Hours -> " + newDutyHours); Console.WriteLine(); driverExcelWorksheet.Cells[i, 2] = newDrivingHours; driverExcelWorksheet.Cells[i, 3] = newDutyHours; driverExcelWorksheet.Cells[i, 4] = tempSwExOrder.dtDevliveryDateTime; driverExcelWorksheet.Cells[i, 5] = tempSwExOrder.DeliverTo; driverCount++; swExOrders.Remove(tempSwExOrder); } } } vescoExcelWorksheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.Item[1]; int row = 2; String stopActionCode; // String windowEnd = null; String windowStart = null; //Add the converted orders foreach (VescoOrder vo in vescoOrders) { vescoExcelWorksheet.Cells[row, 1] = vo.Job; vescoExcelWorksheet.Cells[row, 2] = vo.Sequence; vescoExcelWorksheet.Cells[row, 3] = vo.StopAction; vescoExcelWorksheet.Cells[row, 4] = vo.Location; vescoExcelWorksheet.Cells[row, 5] = vo.Address; stopActionCode = vo.StopAction.Split('-')[0]; if ("11".Equals(stopActionCode) || "12".Equals(stopActionCode)) { vescoExcelWorksheet.Cells[row, 6] = "60"; } else { vescoExcelWorksheet.Cells[row, 6] = vo.StopDelay; } if (vo.WindowStart != null && vo.WindowEnd != null) { if (vo.WindowStart.Equals(vo.WindowEnd)) { // Both the window start and end times are the same. Increment the window end time by an hour for a more accomodating window. DateTime dt = DateTime.Parse(vo.WindowStart, System.Globalization.CultureInfo.CurrentCulture); windowStart = dt.AddHours(-1).ToString("HH:mm:ss"); } else { windowStart = vo.WindowStart; } } else { windowStart = vo.WindowStart; } vescoExcelWorksheet.Cells[row, 7] = windowStart; vescoExcelWorksheet.Cells[row, 8] = vo.WindowEnd; vescoExcelWorksheet.Cells[row, 9] = vo.Hazardous; vescoExcelWorksheet.Cells[row, 10] = vo.Overweight; vescoExcelWorksheet.Cells[row, 11] = vo.OriginalLegNumber; vescoExcelWorksheet.Cells[row, 12] = vo.ContainerNumber; vescoExcelWorksheet.Cells[row, 13] = vo.SteamshipLine; vescoExcelWorksheet.Cells[row, 14] = vo.LiveLoad; vescoExcelWorksheet.Cells[row, 15] = vo.OriginalLegType; vescoExcelWorksheet.Cells[row, 16] = vo.DispatcherSequence; row++; } // Delete the file first since I can't figure out how to get rid of the confirmation dialog File.Delete(Application.StartupPath + "\\Templates\\Vesco.xlsx"); vescoExcelWorkbook.SaveAs(Application.StartupPath + "\\Templates\\Vesco.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show("Error: Could not read " + label7.Text + ". Original error: " + ex.Message); } finally { if (vescoExcelWorksheet != null) { Marshal.ReleaseComObject(vescoExcelWorksheet); } if (vescoExcelWorkbook != null) { Marshal.ReleaseComObject(vescoExcelWorkbook); } if (excelObj2 != null) { excelObj2.Quit(); Marshal.ReleaseComObject(excelObj2); } } Vesco vesco = new Vesco(checkBox1.Checked, vescoOrders, badOrders, label7.Text + "," + label1.Text, "SouthWest", runDate); vesco.Show(); vesco.SouthWestForm = this; this.Hide(); vesco.performSteps(); } }