Consumir webservices desde Microsoft Excel – GET y POST

En este artículo trataré sobre la forma de consumir WebServices desde Microsoft excel, usando este programa como un cliente sencillo para WebServices sin autenticación.

Consumir WebServices desde Excel
Consumir WebServices desde Excel

Vale la pena recordar que un WebService es un medio para permitir transacciones de datos entre aplicaciones heterógeneas.

Un WebService dispuesto en una máquina, expone una serie de métodos que permiten unas transacciones bien definidas con su base de datos; como por ejemplo: consultar cierta información, agregar, editar o eliminar registros.

Así pues un WebService define unos mecanismos restringuidos con los que se van a poder realizar ciertas transacciones sobre los datos de un servidor. Para hacerlas más seguras, generalmente se usan conexiones autenticadas y encriptadas por SSL.

En Microsoft Excel es posible realizar el consumo de WebServices sin autenticación para integrar información de otras fuentes, sin tener que importarlas o copiarlas desde sus orígenes.  En Excel se pueden consumir WebServices que usan métodos GET y POST para el paso de parámetros al servidor. En seguida vamos a tratar en más detalle sobre el uso en cada uno de ellos:

 

1. Consumir WebServices con métodos GET

Para consumir WebServices con envío de parámetros por el método GET, se usa una fórmula llamada SERVICIOWEB(url), que recibe como parámetro la url del webservice y su variables.

Ejemplo:

En este ejemplo deseamos usar un WebService para que nos traiga la tasa de cambio actual de COP$ a USD$,  con el fin de actualizar los precios en pesos colombianos de unos productos con precios en dólares.

Para eso usaremos el siguiente WebService:

https://free.currencyconverterapi.com/api/v6/convert?q=USD_COP&compact=ultra

donde podemos ver los parámetros GET en q= USD_COP y compact=ultra

Para consumir el WebService, se introduce la url dentro de la fórmula SERVICIOWEB, quedando así:

=SERVICIOWEB("https://free.currencyconverterapi.com/api/v6/convert?q=USD_COP&compact=ultra")

Cuando la terminamos de introducir nos retorna la tasa de cambio en un formato JSON compacto así:

{"USD_COP":3078.25}

Para extraer el valor, y cambiar el punto (.) por una coma (,), que es el formato decimal usado en Colombia, usamos la siguiente fórmula:

=SUSTITUIR(EXTRAE(F1;ENCONTRAR(":";F1)+1;LARGO(F1)-ENCONTRAR(":";F1)-1);".";",")

Así nuestro ejemplo en Excel quedaría de la siguiente forma:

Consumir webservices en Excel método get
Consumir webservices en Excel método get

Cada vez que ingresemos a esta página se recalculará el valor en la celda de la tasa de cambio, con el valor retornado por el WebService.

 

2. Consumir Webservices con método POST

Cuando tenemos un WebService al que se le envían los parámetros por el método POST, no se puede usar la fórmula SERVICIOWEB. Esto ocurre porque cuando se usa el método POST los parámetros no se envían en la url.

Así que para este tipo de WebServices se requiere crear una macro para el envío de los parámetros POST.

Ejemplo:

En esta ocasión vamos a trabajar con un WebService que entrega información de entrada y salida de un lugar, desde una fecha inicial a una fecha final:

Consumir webservices en Excel método post
Consumir webservices en Excel método post

Entonces nuestro objetivo es consumirlo y organizarlo en una hoja de Excel. En la hoja de Excel reservamos unas celdas para incluir los parámetros de entrada del webservice (Fecha Incial, Fecha Final y Ubicación), y un botón llamado Actualizar Datos para consumir el WebService con los parámetros indicados a la izquierda:

Consumir webservices en Excel post Consumo
Consumir webservices en Excel post Consumo

Ahora incluimos el siguiente código para el consumo del webservice:

Sub Webservice()

' Se ingresan los encabezados de los datos en la hoja
ActiveWorkbook.Sheets("Hoja1").Range("A:F").Clear
ActiveWorkbook.Sheets("Hoja1").Range("A" & 1).Value = "Fecha"
ActiveWorkbook.Sheets("Hoja1").Range("B" & 1).Value = "Ubicación"
ActiveWorkbook.Sheets("Hoja1").Range("C" & 1).Value = "CC"
ActiveWorkbook.Sheets("Hoja1").Range("D" & 1).Value = "Lista"
ActiveWorkbook.Sheets("Hoja1").Range("E" & 1).Value = "Hora Ingreso"
ActiveWorkbook.Sheets("Hoja1").Range("F" & 1).Value = "Hora Salida"

' Se almacenan los parámetros de entrada del WebService, ubicados en las celdas 
fi = ActiveWorkbook.Sheets("Hoja1").Cells(2, 8).Value
ff = ActiveWorkbook.Sheets("Hoja1").Cells(2, 9).Value
Ubicacion = ActiveWorkbook.Sheets("Hoja1").Cells(2, 10).Value

' Se crea el objeto que consumirá el webservice
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")

' Se crea el objeto que almacenará el resultado del webservice en formato XML
Set xmlDoc = CreateObject("MSXML2.DOMDocument")

URL = "http://localhost:57371/WebService.asmx/GetData"

' Se gestiona el webservice con su URL y demás parámetros
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
Payload = "fi=" + fi + "&ff=" + ff + "&ubicacion=" + Ubicacion
objHTTP.send (Payload)

' Se guarda el XML de respuesta
xmlDoc.LoadXML objHTTP.responseText

' Se leen los nodos del XML
Set FechaNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/fecha/text()")
Set CCNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/cc/text()")
Set HoraiNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/horai/text()")
Set HorafNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/horaf/text()")
Set UbicacionNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/ubicacion/text()")
Set ListaNodes = xmlDoc.SelectNodes("/ArrayOfResultado/Resultado/lista/text()")

' Se escriben los datos del XML en las filas de la hoja de Excel
For i = 0 To (FechaNodes.Length - 1)
  Fecha = FechaNodes(i).NodeValue
  CC = CCNodes(i).NodeValue
  Horai = HoraiNodes(i).NodeValue
  Ubicacion = UbicacionNodes(i).NodeValue
  Lista = ListaNodes(i).NodeValue
  Horaf = HorafNodes(i).NodeValue

  ActiveWorkbook.Sheets("Hoja1").Range("A" & i + 2).Value = Fecha
  ActiveWorkbook.Sheets("Hoja1").Range("B" & i + 2).Value = Ubicacion
  ActiveWorkbook.Sheets("Hoja1").Range("C" & i + 2).Value = CC
  ActiveWorkbook.Sheets("Hoja1").Range("D" & i + 2).Value = Lista
  ActiveWorkbook.Sheets("Hoja1").Range("E" & i + 2).Value = Horai
  ActiveWorkbook.Sheets("Hoja1").Range("F" & i + 2).Value = Horaf
Next

End Sub

Finalmente, cuando presionamos el botón Actualizar Datos, tendremos los datos consumidos del WebService, organizados en la hoja de Excel:

Consumir webservices en Excel post Consumo Final
Consumir webservices en Excel post Consumo Final

Como podemos ver, las funciones de consumo de WebServices nos permite transacciones desde Excel a otras aplicaciones, sin importar en qué arquitectura, base de datos o lenguajes de programación fueron construídas, sólo se requieren los métodos y los parámetros necesarios para intercambiar información entre las aplicaciones.

Este artículo hace parte del sistema de divulgación de conocimiento de ITSoftware SAS.

Si te gustó, por favor no olvides compartirlo en las redes sociales. 😉

Dejar una contestacion

Tu dirección de correo electrónico no será publicada.

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.