public GoogleMapsJSON.Route GenerateGoogleMapsRoute() { //origin=2550+Honey+Creek+Circle+East+Troy&destination=1752+Gateway+Boulevard+Beloit& StringBuilder sb = new StringBuilder(); sb.Append("https://maps.googleapis.com/maps/api/directions/json?key=" + System.Configuration.ConfigurationManager.AppSettings.Get("APIKey") + "&origin=424+College+St+Beloit+WI+53511&destination=424+College+St+Beloit+WI+53511"); if (Clients.Count > 0) { sb.Append("&waypoints=optimize:true|"); } foreach (Client client in Clients) { sb.Append(HttpUtility.UrlEncode(client.Address) + "|"); } HttpWebRequest request = (HttpWebRequest)WebRequest.Create(sb.ToString()); HttpWebResponse response = (HttpWebResponse)request.GetResponse(); string jsonResponse = new StreamReader(response.GetResponseStream()).ReadToEnd(); Rootobject googleResponse = JsonConvert.DeserializeObject <Rootobject>(jsonResponse); if (googleResponse.status != "OK") { throw new FormatException("Bad results from Google"); } GoogleMapsJSON.Route primaryRoute = googleResponse.routes[0]; //re-order clients in this route for optimal driving List <Client> orderedClientList = new List <Client>(); foreach (int order in primaryRoute.waypoint_order) { orderedClientList.Add(Clients[order]); } Clients = orderedClientList; return(primaryRoute); }
private void WriteOutputToExcel(GoogleMapsJSON.Route writtenRoute) { int clientNameIndex = 1, clientAddressIndex = 2, coldMealsCountIndex = 3, hotMealsCountIndex = 4, specialInstructionsIndex = 5, phoneNumberIndex = 6, directionsIndex = 7; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; Microsoft.Office.Interop.Excel._Workbook appWB = app.Workbooks.Add(); Microsoft.Office.Interop.Excel._Worksheet appSheet = appWB.ActiveSheet; appSheet.Cells[1, clientNameIndex] = "Client Name"; appSheet.Cells[1, clientAddressIndex] = "Address"; appSheet.Cells[1, coldMealsCountIndex] = "# Cold Meals"; appSheet.Cells[1, hotMealsCountIndex] = "# Hot Meals"; appSheet.Cells[1, specialInstructionsIndex] = "Special Instructions"; appSheet.Cells[1, phoneNumberIndex] = "Phone Number"; appSheet.Cells[1, directionsIndex] = "Directions"; appSheet.Columns[clientNameIndex].ColumnWidth = 20; appSheet.Columns[directionsIndex].ColumnWidth = 70; appSheet.Columns[clientAddressIndex].ColumnWidth = 30; appSheet.Columns[coldMealsCountIndex].ColumnWidth = 12; appSheet.Columns[hotMealsCountIndex].ColumnWidth = 12; appSheet.Columns[specialInstructionsIndex].ColumnWidth = 20; appSheet.Columns[phoneNumberIndex].ColumnWidth = 15; appSheet.UsedRange.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; appSheet.UsedRange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //appSheet.Columns[VerticalAlignment = VerticalAlignment.Top; int rowIndex = 2; foreach (Client client in currentRoute.Clients) { appSheet.Cells[rowIndex, clientNameIndex] = client.Name; appSheet.Cells[rowIndex, clientAddressIndex] = client.Address; appSheet.Cells[rowIndex, coldMealsCountIndex] = client.ColdMeals; appSheet.Cells[rowIndex, hotMealsCountIndex] = client.HotMeals; appSheet.Cells[rowIndex, specialInstructionsIndex] = client.SpecialInstructions; appSheet.Cells[rowIndex, phoneNumberIndex] = client.PhoneNumber; rowIndex++; } rowIndex = 2; foreach (Leg leg in writtenRoute.legs) { StringBuilder dirString = new StringBuilder(); bool first = true; foreach (Step step in leg.steps) { if (!first) { dirString.Append("\r\n"); } first = false; StringWriter writer = new StringWriter(); HttpUtility.HtmlDecode(step.html_instructions, writer); string encodedString = writer.ToString(); string finalString = Regex.Replace(encodedString, @"<(.|\n)*?>", ""); dirString.Append(finalString + "."); } appSheet.Cells[rowIndex, directionsIndex] = dirString.ToString(); rowIndex++;; } app.Visible = true; LoadingText.Visibility = Visibility.Hidden; appWB.SaveAs(currentRoute.Name + ".xls"); }