using System; using System.Collections.Generic; using System.Configuration; using System.IO; using System.Linq; using System.Net; using System.Text; using System.Threading.Tasks; using Newtonsoft.Json; using OfficeOpenXml; namespace Ingeneo.Console.Geo { class Program { static void Main(string[] args) { // Create a request for the URL. string apiKey = ConfigurationManager.AppSettings.Get("Google.API.Key"); #region Leer Excel List direcciones = new List(); string pathExcel = ConfigurationManager.AppSettings.Get("Path.Excel"); // path to your excel file FileInfo fileInfo = new FileInfo(pathExcel); ExcelPackage package = new ExcelPackage(fileInfo); ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault(); // get number of rows and columns in the sheet int rows = worksheet.Dimension.Rows; int columns = worksheet.Dimension.Columns; string cadena_excel = ""; // loop through the worksheet rows and columns (in this case the info start in second row): for (int i = 2; i <= rows; i++) { for (int j = 1; j <= columns; j++) { if (worksheet.Cells[i, j].Value == null) { break; } else { cadena_excel += worksheet.Cells[i, j].Value.ToString(); cadena_excel += "+"; } //System.Console.WriteLine(cadena_excel); #region Formatting string cadena_excel = cadena_excel.Replace(" A ", "A "); cadena_excel = cadena_excel.Replace(" a ", "a "); cadena_excel = cadena_excel.Replace("#", "+numero+"); cadena_excel = cadena_excel.Replace("-", " "); cadena_excel = cadena_excel.Replace(".", " "); cadena_excel = cadena_excel.Replace("ÑON", "ión"); //ión cadena_excel = cadena_excel.Replace("Ã-", "i"); //í cadena_excel = cadena_excel.Replace("Ñ", "ñ"); // ? cadena_excel = cadena_excel.Replace(" AUT ", " autopista"); cadena_excel = cadena_excel.Replace(" AUTP ", " autopista"); cadena_excel = cadena_excel.Replace(" CL ", " calle "); cadena_excel = cadena_excel.Replace(" Cl ", " calle "); cadena_excel = cadena_excel.Replace(" cl ", " calle "); cadena_excel = cadena_excel.Replace(" CLL ", " calle "); cadena_excel = cadena_excel.Replace(" Cll ", " calle "); cadena_excel = cadena_excel.Replace(" CRA ", " Carrera "); cadena_excel = cadena_excel.Replace(" CR ", " Carrera "); cadena_excel = cadena_excel.Replace(" Cr ", " Carrera "); cadena_excel = cadena_excel.Replace(" cr ", " Carrera "); cadena_excel = cadena_excel.Replace(" CRR ", " Carrera "); cadena_excel = cadena_excel.Replace(" Crr ", " Carrera "); cadena_excel = cadena_excel.Replace(" BR ", " Barrio "); cadena_excel = cadena_excel.Replace(" BRR ", " Barrio "); cadena_excel = cadena_excel.Replace(" Br ", " Barrio "); cadena_excel = cadena_excel.Replace(" Brr ", " Barrio "); cadena_excel = cadena_excel.Replace(" brr ", " Barrio "); cadena_excel = cadena_excel.Replace(" AV ", " Avenida "); cadena_excel = cadena_excel.Replace(" Av ", " Avenida "); cadena_excel = cadena_excel.Replace(" av ", " Avenida "); cadena_excel = cadena_excel.Replace(" KM ", " Kilometro "); cadena_excel = cadena_excel.Replace(" Km ", " Kilometro "); cadena_excel = cadena_excel.Replace(" km ", " Kilometro "); cadena_excel = cadena_excel.Replace(" SEC ", " Sector "); cadena_excel = cadena_excel.Replace(" C CIAL ", " Centro Comercial "); cadena_excel = cadena_excel.Replace(" CC ", " Centro Comercial "); cadena_excel = cadena_excel.Replace(" CC ", " Centro Comercial "); cadena_excel = cadena_excel.Replace(" C.C ", " Centro Comercial "); cadena_excel = cadena_excel.Replace(" LC ", " Local "); cadena_excel = cadena_excel.Replace(" DG ", " Diagonal "); cadena_excel = cadena_excel.Replace(" TV ", " Transversal "); cadena_excel = cadena_excel.Replace(" TVR ", " Transversal "); cadena_excel = cadena_excel.Replace(" TRANSV ", " Transversal "); cadena_excel = cadena_excel.Replace(" VDA ", " Vereda "); cadena_excel = cadena_excel.Replace(" VRD ", " Vereda "); cadena_excel = cadena_excel.Replace(" LT ", " Lote "); cadena_excel = cadena_excel.Replace(" PA ", " Parcela "); cadena_excel = cadena_excel.Replace(" MZ ", " Manzana "); cadena_excel = cadena_excel.Replace(" Mz ", " Manzana "); cadena_excel = cadena_excel.Replace(" mz ", " Manzana "); cadena_excel = cadena_excel.Replace(" VTE ", " Variante "); cadena_excel = cadena_excel.Replace(" FCA ", " Finca "); cadena_excel = cadena_excel.Replace(" N ", " Número "); cadena_excel = cadena_excel.Replace(" No ", " Número "); cadena_excel = cadena_excel.Replace("+AUT ", " autopista"); cadena_excel = cadena_excel.Replace("+AUTP ", " autopista"); cadena_excel = cadena_excel.Replace("+CL ", " calle "); cadena_excel = cadena_excel.Replace("+Cl ", " calle "); cadena_excel = cadena_excel.Replace("+cl ", " calle "); cadena_excel = cadena_excel.Replace("+CLL ", " calle "); cadena_excel = cadena_excel.Replace("+Cll ", " calle "); cadena_excel = cadena_excel.Replace("+CRA ", " Carrera "); cadena_excel = cadena_excel.Replace("+CR ", " Carrera "); cadena_excel = cadena_excel.Replace("+Cr ", " Carrera "); cadena_excel = cadena_excel.Replace("+cr ", " Carrera "); cadena_excel = cadena_excel.Replace("+CRR ", " Carrera "); cadena_excel = cadena_excel.Replace("+Crr ", " Carrera "); cadena_excel = cadena_excel.Replace("+BR ", " Barrio "); cadena_excel = cadena_excel.Replace("+BRR ", " Barrio "); cadena_excel = cadena_excel.Replace("+Br ", " Barrio "); cadena_excel = cadena_excel.Replace("+Brr ", " Barrio "); cadena_excel = cadena_excel.Replace("+brr ", " Barrio "); cadena_excel = cadena_excel.Replace("+AV ", " Avenida "); cadena_excel = cadena_excel.Replace("+Av ", " Avenida "); cadena_excel = cadena_excel.Replace("+av ", " Avenida "); cadena_excel = cadena_excel.Replace("+KM ", " Kilometro "); cadena_excel = cadena_excel.Replace("+Km ", " Kilometro "); cadena_excel = cadena_excel.Replace("+km ", " Kilometro "); cadena_excel = cadena_excel.Replace("+SEC ", " Sector "); cadena_excel = cadena_excel.Replace("+C CIAL ", " Centro Comercial "); cadena_excel = cadena_excel.Replace("+CC ", " Centro Comercial "); cadena_excel = cadena_excel.Replace("+CC ", " Centro Comercial "); cadena_excel = cadena_excel.Replace("+C.C ", " Centro Comercial "); cadena_excel = cadena_excel.Replace("+LC ", " Local "); cadena_excel = cadena_excel.Replace("+DG ", " Diagonal "); cadena_excel = cadena_excel.Replace("+TV ", " Transversal "); cadena_excel = cadena_excel.Replace("+TVR ", " Transversal "); cadena_excel = cadena_excel.Replace("+TRANSV ", " Transversal "); cadena_excel = cadena_excel.Replace("+VDA ", " Vereda "); cadena_excel = cadena_excel.Replace("+VRD ", " Vereda "); cadena_excel = cadena_excel.Replace("+LT ", " Lote "); cadena_excel = cadena_excel.Replace("+PA ", " Parcela "); cadena_excel = cadena_excel.Replace("+MZ ", " Manzana "); cadena_excel = cadena_excel.Replace("+Mz ", " Manzana "); cadena_excel = cadena_excel.Replace("+mz ", " Manzana "); cadena_excel = cadena_excel.Replace("+VTE ", " Variante "); cadena_excel = cadena_excel.Replace("+FCA ", " Finca "); cadena_excel = cadena_excel.Replace("+N ", " Número "); cadena_excel = cadena_excel.Replace("+No ", " Número "); // Fin 1 + // Ini 2 + cadena_excel = cadena_excel.Replace("+AUT+", " autopista"); cadena_excel = cadena_excel.Replace("+AUTP+", " autopista"); cadena_excel = cadena_excel.Replace("+CL+", " calle "); cadena_excel = cadena_excel.Replace("+Cl+", " calle "); cadena_excel = cadena_excel.Replace("+cl+", " calle "); cadena_excel = cadena_excel.Replace("+CLL+", " calle "); cadena_excel = cadena_excel.Replace("+Cll+", " calle "); cadena_excel = cadena_excel.Replace("+CRA+", " Carrera "); cadena_excel = cadena_excel.Replace("+CR+", " Carrera "); cadena_excel = cadena_excel.Replace("+Cr+", " Carrera "); cadena_excel = cadena_excel.Replace("+cr+", " Carrera "); cadena_excel = cadena_excel.Replace("+CRR+", " Carrera "); cadena_excel = cadena_excel.Replace("+Crr+", " Carrera "); cadena_excel = cadena_excel.Replace("+BR+", " Barrio "); cadena_excel = cadena_excel.Replace("+BRR+", " Barrio "); cadena_excel = cadena_excel.Replace("+Br+", " Barrio "); cadena_excel = cadena_excel.Replace("+Brr+", " Barrio "); cadena_excel = cadena_excel.Replace("+brr+", " Barrio "); cadena_excel = cadena_excel.Replace("+AV+", " Avenida "); cadena_excel = cadena_excel.Replace("+Av+", " Avenida "); cadena_excel = cadena_excel.Replace("+av+", " Avenida "); cadena_excel = cadena_excel.Replace("+KM+", " Kilometro "); cadena_excel = cadena_excel.Replace("+Km+", " Kilometro "); cadena_excel = cadena_excel.Replace("+km+", " Kilometro "); cadena_excel = cadena_excel.Replace("+SEC+", " Sector "); cadena_excel = cadena_excel.Replace("+C CIAL+", " Centro Comercial "); cadena_excel = cadena_excel.Replace("+CC+", " Centro Comercial "); cadena_excel = cadena_excel.Replace("+CC+", " Centro Comercial "); cadena_excel = cadena_excel.Replace("+C.C+", " Centro Comercial "); cadena_excel = cadena_excel.Replace("+LC+", " Local "); cadena_excel = cadena_excel.Replace("+DG+", " Diagonal "); cadena_excel = cadena_excel.Replace("+TV+", " Transversal "); cadena_excel = cadena_excel.Replace("+TVR+", " Transversal "); cadena_excel = cadena_excel.Replace("+TRANSV+", " Transversal "); cadena_excel = cadena_excel.Replace("+VDA+", " Vereda "); cadena_excel = cadena_excel.Replace("+VRD+", " Vereda "); cadena_excel = cadena_excel.Replace("+LT+", " Lote "); cadena_excel = cadena_excel.Replace("+PA+", " Parcela "); cadena_excel = cadena_excel.Replace("+MZ+", " Manzana "); cadena_excel = cadena_excel.Replace("+Mz+", " Manzana "); cadena_excel = cadena_excel.Replace("+mz+", " Manzana "); cadena_excel = cadena_excel.Replace("+VTE+", " Variante "); cadena_excel = cadena_excel.Replace("+FCA+", " Finca "); cadena_excel = cadena_excel.Replace("+N+", " Número "); cadena_excel = cadena_excel.Replace("+No+", " Número "); // Ini - Nomenclatura - DIAN 2020 cadena_excel = cadena_excel.Replace(" AC ", " Administración "); cadena_excel = cadena_excel.Replace(" AD ", " Avenida calle "); cadena_excel = cadena_excel.Replace(" ADL ", " Adelante "); cadena_excel = cadena_excel.Replace(" AER ", " Aeropuerto "); cadena_excel = cadena_excel.Replace(" AG ", " Agencia "); cadena_excel = cadena_excel.Replace(" AGP ", " Agrupación "); cadena_excel = cadena_excel.Replace(" AK ", " Avenida carrera "); cadena_excel = cadena_excel.Replace(" AL ", " Altillo "); cadena_excel = cadena_excel.Replace(" ALD ", " Al lado "); cadena_excel = cadena_excel.Replace(" ALM ", " Almacén "); cadena_excel = cadena_excel.Replace(" AP ", " Apartamento "); cadena_excel = cadena_excel.Replace(" APTDO ", " Apartado "); cadena_excel = cadena_excel.Replace(" ATR ", " Atrás "); cadena_excel = cadena_excel.Replace(" AUT ", " Autopista "); cadena_excel = cadena_excel.Replace(" AV ", " Avenida "); cadena_excel = cadena_excel.Replace(" AVIAL ", " Anillo vial "); cadena_excel = cadena_excel.Replace(" BG ", " Bodega "); cadena_excel = cadena_excel.Replace(" BL ", " Bloque "); cadena_excel = cadena_excel.Replace(" BLV ", " Boulevard "); cadena_excel = cadena_excel.Replace(" BRR ", " Barrio "); cadena_excel = cadena_excel.Replace(" C ", " Corregimiento "); cadena_excel = cadena_excel.Replace(" CA ", " Casa "); cadena_excel = cadena_excel.Replace(" CAS ", " Caserío "); cadena_excel = cadena_excel.Replace(" CC ", " Centro comercial "); cadena_excel = cadena_excel.Replace(" CD ", " Ciudadela "); cadena_excel = cadena_excel.Replace(" CEL ", " Célula "); cadena_excel = cadena_excel.Replace(" CEN ", " Centro "); cadena_excel = cadena_excel.Replace(" CIR ", " Circular "); cadena_excel = cadena_excel.Replace(" CL ", " Calle "); cadena_excel = cadena_excel.Replace(" CLJ ", " Callejón "); cadena_excel = cadena_excel.Replace(" CN ", " Camino "); cadena_excel = cadena_excel.Replace(" CON ", " Conjunto residencial "); cadena_excel = cadena_excel.Replace(" CONJ ", " Conjunto "); cadena_excel = cadena_excel.Replace(" CR ", " Carrera "); cadena_excel = cadena_excel.Replace(" CRT ", " Carretera "); cadena_excel = cadena_excel.Replace(" CRV ", " Circunvalar "); cadena_excel = cadena_excel.Replace(" CS ", " Consultorio "); cadena_excel = cadena_excel.Replace(" DG ", " Diagonal "); cadena_excel = cadena_excel.Replace(" DP ", " Depósito "); cadena_excel = cadena_excel.Replace(" DPTO ", " Departamento "); cadena_excel = cadena_excel.Replace(" DS ", " Depósito sótano "); cadena_excel = cadena_excel.Replace(" ED ", " Edificio "); cadena_excel = cadena_excel.Replace(" EN ", " Entrada "); cadena_excel = cadena_excel.Replace(" ES ", " Escalera "); cadena_excel = cadena_excel.Replace(" ESQ ", " Esquina "); cadena_excel = cadena_excel.Replace(" ESTE ", " Este "); cadena_excel = cadena_excel.Replace(" ET ", " Etapa "); cadena_excel = cadena_excel.Replace(" EX ", " Exterior "); cadena_excel = cadena_excel.Replace(" FCA ", " Finca "); cadena_excel = cadena_excel.Replace(" GJ ", " Garaje "); cadena_excel = cadena_excel.Replace(" GS ", " Garaje sótano "); cadena_excel = cadena_excel.Replace(" GT ", " Glorieta "); cadena_excel = cadena_excel.Replace(" HC ", " Hacienda "); cadena_excel = cadena_excel.Replace(" HG ", " Hangar "); cadena_excel = cadena_excel.Replace(" IN ", " Interior "); cadena_excel = cadena_excel.Replace(" IP ", "Inspección de Policía "); cadena_excel = cadena_excel.Replace(" IPD ", " Inspección Departamental "); cadena_excel = cadena_excel.Replace(" IPM ", " Inspección Municipal "); cadena_excel = cadena_excel.Replace(" KM ", " Kilómetro "); cadena_excel = cadena_excel.Replace(" LC ", " Local "); cadena_excel = cadena_excel.Replace(" LM ", " Local mezzanine "); cadena_excel = cadena_excel.Replace(" LT ", " Lote "); cadena_excel = cadena_excel.Replace(" MD ", " Módulo "); cadena_excel = cadena_excel.Replace(" MJ ", " Mojón "); cadena_excel = cadena_excel.Replace(" MLL ", " Muelle "); cadena_excel = cadena_excel.Replace(" MN ", " Mezzanine "); cadena_excel = cadena_excel.Replace(" MZ ", " Manzana "); cadena_excel = cadena_excel.Replace(" NORTE ", " Norte "); cadena_excel = cadena_excel.Replace(" O ", " Oriente "); cadena_excel = cadena_excel.Replace(" OCC ", " Occidente "); cadena_excel = cadena_excel.Replace(" OESTE ", " Oeste "); cadena_excel = cadena_excel.Replace(" OF ", " Oficina "); cadena_excel = cadena_excel.Replace(" P ", " Piso "); cadena_excel = cadena_excel.Replace(" PA ", " Parcela "); cadena_excel = cadena_excel.Replace(" PAR ", " Parque "); cadena_excel = cadena_excel.Replace(" PD ", " Predio "); cadena_excel = cadena_excel.Replace(" PH ", " Penthouse "); cadena_excel = cadena_excel.Replace(" PJ ", " Pasaje "); cadena_excel = cadena_excel.Replace(" PL ", " Planta "); cadena_excel = cadena_excel.Replace(" PN ", " Puente "); cadena_excel = cadena_excel.Replace(" POR ", " Portería "); cadena_excel = cadena_excel.Replace(" POS ", " Poste "); cadena_excel = cadena_excel.Replace(" PQ ", " Parqueadero "); cadena_excel = cadena_excel.Replace(" PRJ ", " Paraje "); cadena_excel = cadena_excel.Replace(" PS ", " Paseo "); cadena_excel = cadena_excel.Replace(" PT ", " Puesto "); cadena_excel = cadena_excel.Replace(" PW ", " Park Way "); cadena_excel = cadena_excel.Replace(" RP ", " Round Point "); cadena_excel = cadena_excel.Replace(" SA ", " Salón "); cadena_excel = cadena_excel.Replace(" SC ", " Salón comunal "); cadena_excel = cadena_excel.Replace(" SD ", " Salida "); cadena_excel = cadena_excel.Replace(" SEC ", " Sector "); cadena_excel = cadena_excel.Replace(" SL ", " Solar "); cadena_excel = cadena_excel.Replace(" SM ", " Súper manzana "); cadena_excel = cadena_excel.Replace(" SS ", " Semisótano "); cadena_excel = cadena_excel.Replace(" ST ", " Sótano "); cadena_excel = cadena_excel.Replace(" SUITE ", " Suite "); cadena_excel = cadena_excel.Replace(" SUR ", " Sur "); cadena_excel = cadena_excel.Replace(" TER ", " Terminal "); cadena_excel = cadena_excel.Replace(" TERPLN ", " Terraplén "); cadena_excel = cadena_excel.Replace(" TO ", " Torre "); cadena_excel = cadena_excel.Replace(" TV ", " Transversal "); cadena_excel = cadena_excel.Replace(" TZ ", " Terraza "); cadena_excel = cadena_excel.Replace(" UN ", " Unidad "); cadena_excel = cadena_excel.Replace(" UR ", " Unidad residencial "); cadena_excel = cadena_excel.Replace(" URB ", " Urbanización "); cadena_excel = cadena_excel.Replace(" VRD ", " Vereda "); cadena_excel = cadena_excel.Replace(" VTE ", " Variante "); cadena_excel = cadena_excel.Replace(" ZN ", " Zona "); cadena_excel = cadena_excel.Replace(" ZF ", " Zona franca "); // Ini 1 + cadena_excel = cadena_excel.Replace("+AC ", " Administración "); cadena_excel = cadena_excel.Replace("+AD ", " Avenida calle "); cadena_excel = cadena_excel.Replace("+ADL ", " Adelante "); cadena_excel = cadena_excel.Replace("+AER ", " Aeropuerto "); cadena_excel = cadena_excel.Replace("+AG ", " Agencia "); cadena_excel = cadena_excel.Replace("+AGP ", " Agrupación "); cadena_excel = cadena_excel.Replace("+AK ", " Avenida carrera "); cadena_excel = cadena_excel.Replace("+AL ", " Altillo "); cadena_excel = cadena_excel.Replace("+ALD ", " Al lado "); cadena_excel = cadena_excel.Replace("+ALM ", " Almacén "); cadena_excel = cadena_excel.Replace("+AP ", " Apartamento "); cadena_excel = cadena_excel.Replace("+APTDO ", " Apartado "); cadena_excel = cadena_excel.Replace("+ATR ", " Atrás "); cadena_excel = cadena_excel.Replace("+AUT ", " Autopista "); cadena_excel = cadena_excel.Replace("+AV ", " Avenida "); cadena_excel = cadena_excel.Replace("+AVIAL ", " Anillo vial "); cadena_excel = cadena_excel.Replace("+BG ", " Bodega "); cadena_excel = cadena_excel.Replace("+BL ", " Bloque "); cadena_excel = cadena_excel.Replace("+BLV ", " Boulevard "); cadena_excel = cadena_excel.Replace("+BRR ", " Barrio "); cadena_excel = cadena_excel.Replace("+C ", " Corregimiento "); cadena_excel = cadena_excel.Replace("+CA ", " Casa "); cadena_excel = cadena_excel.Replace("+CAS ", " Caserío "); cadena_excel = cadena_excel.Replace("+CC ", " Centro comercial "); cadena_excel = cadena_excel.Replace("+CD ", " Ciudadela "); cadena_excel = cadena_excel.Replace("+CEL ", " Célula "); cadena_excel = cadena_excel.Replace("+CEN ", " Centro "); cadena_excel = cadena_excel.Replace("+CIR ", " Circular "); cadena_excel = cadena_excel.Replace("+CL ", " Calle "); cadena_excel = cadena_excel.Replace("+CLJ ", " Callejón "); cadena_excel = cadena_excel.Replace("+CN ", " Camino "); cadena_excel = cadena_excel.Replace("+CON ", " Conjunto residencial "); cadena_excel = cadena_excel.Replace("+CONJ ", " Conjunto "); cadena_excel = cadena_excel.Replace("+CR ", " Carrera "); cadena_excel = cadena_excel.Replace("+CRT ", " Carretera "); cadena_excel = cadena_excel.Replace("+CRV ", " Circunvalar "); cadena_excel = cadena_excel.Replace("+CS ", " Consultorio "); cadena_excel = cadena_excel.Replace("+DG ", " Diagonal "); cadena_excel = cadena_excel.Replace("+DP ", " Depósito "); cadena_excel = cadena_excel.Replace("+DPTO ", " Departamento "); cadena_excel = cadena_excel.Replace("+DS ", " Depósito sótano "); cadena_excel = cadena_excel.Replace("+ED ", " Edificio "); cadena_excel = cadena_excel.Replace("+EN ", " Entrada "); cadena_excel = cadena_excel.Replace("+ES ", " Escalera "); cadena_excel = cadena_excel.Replace("+ESQ ", " Esquina "); cadena_excel = cadena_excel.Replace("+ESTE ", " Este "); cadena_excel = cadena_excel.Replace("+ET ", " Etapa "); cadena_excel = cadena_excel.Replace("+EX ", " Exterior "); cadena_excel = cadena_excel.Replace("+FCA ", " Finca "); cadena_excel = cadena_excel.Replace("+GJ ", " Garaje "); cadena_excel = cadena_excel.Replace("+GS ", " Garaje sótano "); cadena_excel = cadena_excel.Replace("+GT ", " Glorieta "); cadena_excel = cadena_excel.Replace("+HC ", " Hacienda "); cadena_excel = cadena_excel.Replace("+HG ", " Hangar "); cadena_excel = cadena_excel.Replace("+IN ", " Interior "); cadena_excel = cadena_excel.Replace("+IP ", "Inspección de Policía "); cadena_excel = cadena_excel.Replace("+IPD ", " Inspección Departamental "); cadena_excel = cadena_excel.Replace("+IPM ", " Inspección Municipal "); cadena_excel = cadena_excel.Replace("+KM ", " Kilómetro "); cadena_excel = cadena_excel.Replace("+LC ", " Local "); cadena_excel = cadena_excel.Replace("+LM ", " Local mezzanine "); cadena_excel = cadena_excel.Replace("+LT ", " Lote "); cadena_excel = cadena_excel.Replace("+MD ", " Módulo "); cadena_excel = cadena_excel.Replace("+MJ ", " Mojón "); cadena_excel = cadena_excel.Replace("+MLL ", " Muelle "); cadena_excel = cadena_excel.Replace("+MN ", " Mezzanine "); cadena_excel = cadena_excel.Replace("+MZ ", " Manzana "); cadena_excel = cadena_excel.Replace("+NORTE ", " Norte "); cadena_excel = cadena_excel.Replace("+O ", " Oriente "); cadena_excel = cadena_excel.Replace("+OCC ", " Occidente "); cadena_excel = cadena_excel.Replace("+OESTE ", " Oeste "); cadena_excel = cadena_excel.Replace("+OF ", " Oficina "); cadena_excel = cadena_excel.Replace("+P ", " Piso "); cadena_excel = cadena_excel.Replace("+PA ", " Parcela "); cadena_excel = cadena_excel.Replace("+PAR ", " Parque "); cadena_excel = cadena_excel.Replace("+PD ", " Predio "); cadena_excel = cadena_excel.Replace("+PH ", " Penthouse "); cadena_excel = cadena_excel.Replace("+PJ ", " Pasaje "); cadena_excel = cadena_excel.Replace("+PL ", " Planta "); cadena_excel = cadena_excel.Replace("+PN ", " Puente "); cadena_excel = cadena_excel.Replace("+POR ", " Portería "); cadena_excel = cadena_excel.Replace("+POS ", " Poste "); cadena_excel = cadena_excel.Replace("+PQ ", " Parqueadero "); cadena_excel = cadena_excel.Replace("+PRJ ", " Paraje "); cadena_excel = cadena_excel.Replace("+PS ", " Paseo "); cadena_excel = cadena_excel.Replace("+PT ", " Puesto "); cadena_excel = cadena_excel.Replace("+PW ", " Park Way "); cadena_excel = cadena_excel.Replace("+RP ", " Round Point "); cadena_excel = cadena_excel.Replace("+SA ", " Salón "); cadena_excel = cadena_excel.Replace("+SC ", " Salón comunal "); cadena_excel = cadena_excel.Replace("+SD ", " Salida "); cadena_excel = cadena_excel.Replace("+SEC ", " Sector "); cadena_excel = cadena_excel.Replace("+SL ", " Solar "); cadena_excel = cadena_excel.Replace("+SM ", " Súper manzana "); cadena_excel = cadena_excel.Replace("+SS ", " Semisótano "); cadena_excel = cadena_excel.Replace("+ST ", " Sótano "); cadena_excel = cadena_excel.Replace("+SUITE ", " Suite "); cadena_excel = cadena_excel.Replace("+SUR ", " Sur "); cadena_excel = cadena_excel.Replace("+TER ", " Terminal "); cadena_excel = cadena_excel.Replace("+TERPLN ", " Terraplén "); cadena_excel = cadena_excel.Replace("+TO ", " Torre "); cadena_excel = cadena_excel.Replace("+TV ", " Transversal "); cadena_excel = cadena_excel.Replace("+TZ ", " Terraza "); cadena_excel = cadena_excel.Replace("+UN ", " Unidad "); cadena_excel = cadena_excel.Replace("+UR ", " Unidad residencial "); cadena_excel = cadena_excel.Replace("+URB ", " Urbanización "); cadena_excel = cadena_excel.Replace("+VRD ", " Vereda "); cadena_excel = cadena_excel.Replace("+VTE ", " Variante "); cadena_excel = cadena_excel.Replace("+ZN ", " Zona "); cadena_excel = cadena_excel.Replace("+ZF ", " Zona franca "); cadena_excel = cadena_excel.Replace("+AC+", " Administración "); cadena_excel = cadena_excel.Replace("+AD+", " Avenida calle "); cadena_excel = cadena_excel.Replace("+ADL+", " Adelante "); cadena_excel = cadena_excel.Replace("+AER+", " Aeropuerto "); cadena_excel = cadena_excel.Replace("+AG+", " Agencia "); cadena_excel = cadena_excel.Replace("+AGP+", " Agrupación "); cadena_excel = cadena_excel.Replace("+AK+", " Avenida carrera "); cadena_excel = cadena_excel.Replace("+AL+", " Altillo "); cadena_excel = cadena_excel.Replace("+ALD+", " Al lado "); cadena_excel = cadena_excel.Replace("+ALM+", " Almacén "); cadena_excel = cadena_excel.Replace("+AP+", " Apartamento "); cadena_excel = cadena_excel.Replace("+APTDO+", " Apartado "); cadena_excel = cadena_excel.Replace("+ATR+", " Atrás "); cadena_excel = cadena_excel.Replace("+AUT+", " Autopista "); cadena_excel = cadena_excel.Replace("+AV+", " Avenida "); cadena_excel = cadena_excel.Replace("+AVIAL+", " Anillo vial "); cadena_excel = cadena_excel.Replace("+BG+", " Bodega "); cadena_excel = cadena_excel.Replace("+BL+", " Bloque "); cadena_excel = cadena_excel.Replace("+BLV+", " Boulevard "); cadena_excel = cadena_excel.Replace("+BRR+", " Barrio "); cadena_excel = cadena_excel.Replace("+C+", " Corregimiento "); cadena_excel = cadena_excel.Replace("+CA+", " Casa "); cadena_excel = cadena_excel.Replace("+CAS+", " Caserío "); cadena_excel = cadena_excel.Replace("+CC+", " Centro comercial "); cadena_excel = cadena_excel.Replace("+CD+", " Ciudadela "); cadena_excel = cadena_excel.Replace("+CEL+", " Célula "); cadena_excel = cadena_excel.Replace("+CEN+", " Centro "); cadena_excel = cadena_excel.Replace("+CIR+", " Circular "); cadena_excel = cadena_excel.Replace("+CL+", " Calle "); cadena_excel = cadena_excel.Replace("+CLJ+", " Callejón "); cadena_excel = cadena_excel.Replace("+CN+", " Camino "); cadena_excel = cadena_excel.Replace("+CON+", " Conjunto residencial "); cadena_excel = cadena_excel.Replace("+CONJ+", " Conjunto "); cadena_excel = cadena_excel.Replace("+CR+", " Carrera "); cadena_excel = cadena_excel.Replace("+CRT+", " Carretera "); cadena_excel = cadena_excel.Replace("+CRV+", " Circunvalar "); cadena_excel = cadena_excel.Replace("+CS+", " Consultorio "); cadena_excel = cadena_excel.Replace("+DG+", " Diagonal "); cadena_excel = cadena_excel.Replace("+DP+", " Depósito "); cadena_excel = cadena_excel.Replace("+DPTO+", " Departamento "); cadena_excel = cadena_excel.Replace("+DS+", " Depósito sótano "); cadena_excel = cadena_excel.Replace("+ED+", " Edificio "); cadena_excel = cadena_excel.Replace("+EN+", " Entrada "); cadena_excel = cadena_excel.Replace("+ES+", " Escalera "); cadena_excel = cadena_excel.Replace("+ESQ+", " Esquina "); cadena_excel = cadena_excel.Replace("+ESTE+", " Este "); cadena_excel = cadena_excel.Replace("+ET+", " Etapa "); cadena_excel = cadena_excel.Replace("+EX+", " Exterior "); cadena_excel = cadena_excel.Replace("+FCA+", " Finca "); cadena_excel = cadena_excel.Replace("+GJ+", " Garaje "); cadena_excel = cadena_excel.Replace("+GS+", " Garaje sótano "); cadena_excel = cadena_excel.Replace("+GT+", " Glorieta "); cadena_excel = cadena_excel.Replace("+HC+", " Hacienda "); cadena_excel = cadena_excel.Replace("+HG+", " Hangar "); cadena_excel = cadena_excel.Replace("+IN+", " Interior "); cadena_excel = cadena_excel.Replace("+IP+", "Inspección de Policía "); cadena_excel = cadena_excel.Replace("+IPD+", " Inspección Departamental "); cadena_excel = cadena_excel.Replace("+IPM+", " Inspección Municipal "); cadena_excel = cadena_excel.Replace("+KM+", " Kilómetro "); cadena_excel = cadena_excel.Replace("+LC+", " Local "); cadena_excel = cadena_excel.Replace("+LM+", " Local mezzanine "); cadena_excel = cadena_excel.Replace("+LT+", " Lote "); cadena_excel = cadena_excel.Replace("+MD+", " Módulo "); cadena_excel = cadena_excel.Replace("+MJ+", " Mojón "); cadena_excel = cadena_excel.Replace("+MLL+", " Muelle "); cadena_excel = cadena_excel.Replace("+MN+", " Mezzanine "); cadena_excel = cadena_excel.Replace("+MZ+", " Manzana "); cadena_excel = cadena_excel.Replace("+NORTE+", " Norte "); cadena_excel = cadena_excel.Replace("+O+", " Oriente "); cadena_excel = cadena_excel.Replace("+OCC+", " Occidente "); cadena_excel = cadena_excel.Replace("+OESTE+", " Oeste "); cadena_excel = cadena_excel.Replace("+OF+", " Oficina "); cadena_excel = cadena_excel.Replace("+P+", " Piso "); cadena_excel = cadena_excel.Replace("+PA+", " Parcela "); cadena_excel = cadena_excel.Replace("+PAR+", " Parque "); cadena_excel = cadena_excel.Replace("+PD+", " Predio "); cadena_excel = cadena_excel.Replace("+PH+", " Penthouse "); cadena_excel = cadena_excel.Replace("+PJ+", " Pasaje "); cadena_excel = cadena_excel.Replace("+PL+", " Planta "); cadena_excel = cadena_excel.Replace("+PN+", " Puente "); cadena_excel = cadena_excel.Replace("+POR+", " Portería "); cadena_excel = cadena_excel.Replace("+POS+", " Poste "); cadena_excel = cadena_excel.Replace("+PQ+", " Parqueadero "); cadena_excel = cadena_excel.Replace("+PRJ+", " Paraje "); cadena_excel = cadena_excel.Replace("+PS+", " Paseo "); cadena_excel = cadena_excel.Replace("+PT+", " Puesto "); cadena_excel = cadena_excel.Replace("+PW+", " Park Way "); cadena_excel = cadena_excel.Replace("+RP+", " Round Point "); cadena_excel = cadena_excel.Replace("+SA+", " Salón "); cadena_excel = cadena_excel.Replace("+SC+", " Salón comunal "); cadena_excel = cadena_excel.Replace("+SD+", " Salida "); cadena_excel = cadena_excel.Replace("+SEC+", " Sector "); cadena_excel = cadena_excel.Replace("+SL+", " Solar "); cadena_excel = cadena_excel.Replace("+SM+", " Súper manzana "); cadena_excel = cadena_excel.Replace("+SS+", " Semisótano "); cadena_excel = cadena_excel.Replace("+ST+", " Sótano "); cadena_excel = cadena_excel.Replace("+SUITE+", " Suite "); cadena_excel = cadena_excel.Replace("+SUR+", " Sur "); cadena_excel = cadena_excel.Replace("+TER+", " Terminal "); cadena_excel = cadena_excel.Replace("+TERPLN+", " Terraplén "); cadena_excel = cadena_excel.Replace("+TO+", " Torre "); cadena_excel = cadena_excel.Replace("+TV+", " Transversal "); cadena_excel = cadena_excel.Replace("+TZ+", " Terraza "); cadena_excel = cadena_excel.Replace("+UN+", " Unidad "); cadena_excel = cadena_excel.Replace("+UR+", " Unidad residencial "); cadena_excel = cadena_excel.Replace("+URB+", " Urbanización "); cadena_excel = cadena_excel.Replace("+VRD+", " Vereda "); cadena_excel = cadena_excel.Replace("+VTE+", " Variante "); cadena_excel = cadena_excel.Replace("+ZN+", " Zona "); cadena_excel = cadena_excel.Replace("+ZF+", " Zona franca "); // Fin - Nomenclatura - DIAN 2020 cadena_excel = cadena_excel.Replace("\u0081", " "); cadena_excel = cadena_excel.Replace(" ", "+"); #endregion Formatting string } System.Console.WriteLine(cadena_excel); direcciones.Add(cadena_excel); cadena_excel = ""; } #endregion // Indica en qué fila y columna comienza a escribir los datos int col_number = 4; int row_number = 2; float latitud; float longitud; string direccionFormateada; foreach (string item in direcciones) { var location = GetGeo(item, apiKey); #region Escribir Excel if (location.results.Count == 0) { //Nothing latitud = 0; longitud = 0; direccionFormateada = "No data found"; } else { latitud = location.results[0].geometry.location.lat; longitud = location.results[0].geometry.location.lng; direccionFormateada = location.results[0].formatted_address; } //create a fileinfo object of an excel file on the disk FileInfo file_1 = new FileInfo(pathExcel); //create a new Excel package from the file using (ExcelPackage excelPackage = new ExcelPackage(file_1)) { //create an instance of the the first sheet in the loaded file ExcelWorksheet worksheet_1 = excelPackage.Workbook.Worksheets[1]; //add some data worksheet_1.Cells[row_number, col_number].Value = latitud; worksheet_1.Cells[row_number, (col_number + 1)].Value = longitud; worksheet_1.Cells[row_number, (col_number + 2)].Value = direccionFormateada; //save the changes excelPackage.Save(); } row_number++; #endregion } } public static Result GetGeo(string address, string apiKey) { Result geoResponse = new Result(); try { string url = string.Format("https://maps.googleapis.com/maps/api/geocode/json?address={0}&key={1}", address, apiKey); WebRequest request = WebRequest.Create(url); // If required by the server, set the credentials. request.Credentials = CredentialCache.DefaultCredentials; // Get the response. WebResponse response = request.GetResponse(); // Display the status. //System.Console.WriteLine(((HttpWebResponse)response).StatusDescription); // Get the stream containing content returned by the server. // The using block ensures the stream is automatically closed. using (Stream dataStream = response.GetResponseStream()) { // Open the stream using a StreamReader for easy access. StreamReader reader = new StreamReader(dataStream); // Read the content. string responseFromServer = reader.ReadToEnd(); geoResponse = JsonConvert.DeserializeObject(responseFromServer); // Display the content. //System.Console.WriteLine(responseFromServer); } // Close the response. response.Close(); } catch (Exception ex) { System.Console.WriteLine(ex.Message); } return geoResponse; } } public class Result { public List results { get; set; } public string status { get; set; } } public class GEOResponse { public List address_components { get; set; } public string formatted_address { get; set; } public Geometry geometry { get; set; } } public class AddressComponents { public string long_name { get; set; } public string short_name { get; set; } public string[] types { get; set; } } public class Geometry { public Location location { get; set; } public string location_type { get; set; } public ViewPort view_port { get; set; } public string place_id { get; set; } public PlusCode plus_code { get; set; } public string[] types { get; set; } } public class Location { public float lat { get; set; } public float lng { get; set; } } public class ViewPort { public Location northeast { get; set; } public Location southwest { get; set; } } public class PlusCode { public string compound_code { get; set; } public string global_code { get; set; } } }