Skip to content

Instantly share code, notes, and snippets.

@pancho508

pancho508/Excel add-in rassini.EXCEL.yaml

Last active December 10, 2019 19:12
Show Gist options
  • Save pancho508/39ff8e00aac3121bfd3db51a4961627c to your computer and use it in GitHub Desktop.
Save pancho508/39ff8e00aac3121bfd3db51a4961627c to your computer and use it in GitHub Desktop.
Download ZIP
Performs a basic Excel API call using plain JavaScript & Promises.
Raw
Excel add-in rassini.EXCEL.yaml
name: Excel add-in rassini
description: Performs a basic Excel API call using plain JavaScript & Promises.
host: EXCEL
api_set: {}
script:
content: "// manejador URL defaulto que usamos\r\nvar theURL =\r\n \"https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=SAB&interval=5min&apikey=S3MG19EN92WBMKNQ\";\r\nvar lastTradePriceOnly = 0;\r\nvar alta = 0;\r\nvar baja = 0;\r\n//cuando el HTML se carge corremos esta funcion\r\n$(document).ready(function() {\r\n // Indicador de cargando\r\n $(\"#stockIndicator\").show();\r\n // invocacion de la funcion doAjax con el URL defaulto como argumento\r\n doAjax(theURL);\r\n // funcion cual es invocada con \"ajaxtrigger\"\r\n $(\".ajaxtrigger\").click(function() {\r\n // Indicador de cargando\r\n $(\"#stockIndicator\").show();\r\n // invocacion de la funcion doAjax con el URL defaulto como argumento\r\n doAjax(theURL);\r\n return false;\r\n });\r\n // funcion cual formatea el resultado de lastVolume\r\n function numberWithCommas(x) {\r\n return x.toString().replace(/\\B(?=(?:\\d{3})+(?!\\d))/g, \",\");\r\n }\r\n // funcion que ejecutara la solicitud de ajax al stock API\r\n function doAjax(url) {\r\n // jQuery $ solicitud ajax\r\n $.ajax({\r\n // tipo de solicitud\r\n url: url,\r\n // clase de informacion que manejaremos\r\n dataType: \"json\",\r\n // el typo de contenido\r\n contentType: \"application/json\",\r\n // si la solicitud es completada correctamente ejecutamos el valor de esta propiedad\r\n success: function(data) {\r\n // el parametro \"data\" es lo que recibimos del servidor\r\n // creamos variables usando el objecto json cual recibimos\r\n var symbol = data[\"Meta Data\"][\"2. Symbol\"];\r\n var lastRefreshed = data[\"Meta Data\"][\"3. Last Refreshed\"];\r\n lastTradePriceOnly = data[\"Time Series (5min)\"][lastRefreshed][\"4. close\"];\r\n var lastVolume = data[\"Time Series (5min)\"][lastRefreshed][\"5. volume\"];\r\n alta = data[\"Time Series (5min)\"][lastRefreshed][\"2. high\"];\r\n baja = data[\"Time Series (5min)\"][lastRefreshed][\"3. low\"];\r\n // console.log(\"ajax...\");\r\n // Aqui los ajuntados al documento HTML usando jquery $\r\n $(\"#stockSymbol\").html(symbol);\r\n $(\"#stockAsk\").html(lastTradePriceOnly);\r\n $(\"#stockVolume\").html(numberWithCommas(lastVolume));\r\n $(\"#alta\").html(alta);\r\n $(\"#baja\").html(baja);\r\n // esconde las palabras rojas carcando\r\n $(\"#stockIndicator\").hide();\r\n }\r\n });\r\n }\r\n});\r\n\r\n// un manejador para invocar getStockData\r\n$(\"#submitStockData\").click(() => tryCatch(getStockData));\r\n// esta funcion es la que le da funcionalidad al boton de Consultar\r\nfunction getStockData() {\r\n // Aqui creamos una variable que sera el string dentro de la caja usando jQuery $\r\n var stockId = $(\"#stockId\").val();\r\n // Aqui creamos la variable con la solicitud HTTP como string\r\n var theURL =\r\n \"https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=\" +\r\n stockId +\r\n \"&interval=5min&apikey=S3MG19EN92WBMKNQ\";\r\n // console.log(theURL);\r\n // Muestra las palabras rojas de cargando\r\n $(\"#stockIndicator\").show();\r\n // Aqui volvemos usar el codigo de la solicitud ajax para obtener el objecto json de el Stock deseado; re assignando las variables sus nuevas correspondencias\r\n function doAjax(url) {\r\n $.ajax({\r\n url: url,\r\n dataType: \"json\",\r\n contentType: \"application/json\",\r\n success: function(data) {\r\n var symbol = data[\"Meta Data\"][\"2. Symbol\"];\r\n var lastRefreshed = data[\"Meta Data\"][\"3. Last Refreshed\"];\r\n lastTradePriceOnly = data[\"Time Series (5min)\"][lastRefreshed][\"4. close\"];\r\n var lastVolume = data[\"Time Series (5min)\"][lastRefreshed][\"5. volume\"];\r\n alta = data[\"Time Series (5min)\"][lastRefreshed][\"2. high\"];\r\n baja = data[\"Time Series (5min)\"][lastRefreshed][\"3. low\"];\r\n console.log(\"ajaxFrom.getStockData\");\r\n $(\"#stockSymbol\").html(symbol);\r\n $(\"#stockAsk\").html(lastTradePriceOnly.toString()); // tenemos que usar .toString() de otra manera te dara un error diciendo que esperaba un string\r\n $(\"#alta\").html(alta.toString());\r\n $(\"#baja\").html(baja.toString());\r\n // Ensena las palabras rojas carcando\r\n $(\"#stockIndicator\").hide();\r\n }\r\n });\r\n }\r\n // invocacion\r\n doAjax(theURL);\r\n}\r\n// manejador que es usado prar correr funcion tryCatch cuando damos click al boton Inserta\r\n$(\"#run\").click(() => tryCatch(run));\r\n// Funcion que le da funcionalidad al boton Inserta a celula\r\nfunction run() {\r\n // creamos una funcion call back que exije excel api\r\n return Excel.run(function(context) {\r\n // creamos una variable que sea igual a la localidad de la celula\r\n var range = context.workbook.getSelectedRange();\r\n // console.log(lastTradePriceOnly); // tambien al un log a range\r\n // creamos una variable que tendra el precio de stock; cual es un arreglo dentro de un arreglo por que el metodo \"values\"\r\n var values = [[]];\r\n // aqui asignamos el valor del primer elemento al predico del stock\r\n values[0][0] = lastTradePriceOnly;\r\n // rendimos el valor a la celula en excel\r\n range.values = values;\r\n range.load(\"address\");\r\n return context.sync().then(function() {\r\n // console.log('The range address was \"' + range.address + '\".');\r\n });\r\n });\r\n}\r\n\r\n(function() {\r\n var js = document.createElement(\"script\");\r\n js.type = \"text/javascript\";\r\n js.async = true;\r\n js.src = \"//www.floatrates.com/scripts/converter.js\";\r\n var sjs = document.getElementsByTagName(\"script\")[0];\r\n sjs.parentNode.insertBefore(js, sjs);\r\n})();\r\n$(\"#clickMe\").click(() => tryCatch(post));\r\n\r\n// Funcion que le da funcionalidad al boton Inserta a celula\r\nfunction post() {\r\n var inputValue = (<HTMLInputElement>document.getElementById(\"pesos\")).value;\r\n // creamos una funcion call back que exije excel api\r\n return Excel.run(function(context) {\r\n // creamos una variable que sea igual a la localidad de la celula\r\n var range = context.workbook.getSelectedRange();\r\n // console.log(lastTradePriceOnly); // tambien al un log a range\r\n // creamos una variable que tendra el precio de stock; cual es un arreglo dentro de un arreglo por que el metodo \"values\"\r\n var values = [[]];\r\n // aqui asignamos el valor del primer elemento al predico del stock\r\n values[0][0] = inputValue;\r\n // rendimos el valor a la celula en excel\r\n range.values = values;\r\n range.load(\"address\");\r\n return context.sync().then(function() {\r\n // console.log('The range address was \"' + range.address + '\".');\r\n });\r\n });\r\n}\r\n\r\n/** Ayudante para invocar una accion y manejar errores **/\r\nfunction tryCatch(callback) {\r\n Promise.resolve()\r\n .then(callback)\r\n .catch(function(error) {\r\n // Nota: en un complemento de producción, querrás notificar al usuario a través de la interfaz de usuario de tu complemento.\r\n console.error(error);\r\n });\r\n}\r\n"
language: typescript
template:
content: "<div>\n\t<script src=\"https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js\"></script>\n\n\t<img src=\"http://mysdelbajio.com.mx/images/rassini.png\" alt=\"Smiley face\" height=\"42\" width=\"150\">\n\t<label for=\"subtitle\">Stock check</label>\n\n\t<form>\n\t\t<div class=\"form-group\">\n\t\t\t<input type=\"text\" class=\"form-control\" id=\"stockId\" placeholder=\"Ingresa el símbolo del stock \">\n </div>\n\t\t\t<button id=\"submitStockData\" type=\"submit\" class=\"ajaxtrigger\">Consultar</button>\n\t\t\t<button id=\"run\" type=\"submit\" >Inserta</button>\n\t</form>\n\n\t<div id=\"stock_miniQuote_head\" class=\"ajaxtrigger\">\n\t\t<span id=\"stockSymbol\"></span>&nbsp;(stock común)\n\t</div>\n\n\t<div id=\"stock_miniQuote\">\n\t\t<div id=\"stockIndicator\">\n\t\t\t<p>Wepa Wepa información de stock ...</p>\n\t\t</div>\n\t\t<div class=\"stock_divider\">\n\t\t\t<div id=\"stock_left\">\n\t\t\t\t<span class=\"stock_label\">Precio</span><br/>\n\t\t\t\t<strong class=\"stock_strong\">$<span id=\"stockAsk\"></span></strong><br/>\n </div>\n\t\t\t\t<div id=\"stock_right\">\n\t\t\t\t\t<span class=\"stock_label\">Alta / Baja</span><br/>\n\t\t\t\t\t<strong class=\"stock_strong\" style=\"color:green;\"><span id=\"alta\"></span></strong><br />\n\t\t\t\t\t<strong class=\"stock_strong\" style=\"color:red;\"><span id=\"baja\"></span></strong><br />\n\t\t</div>\n\t\t\t\t\t<div style=\"clear: both;\"></div>\n\t\t\t\t</div>\n\t\t\t\t<div id=\"stock_body\">\n\t\t\t\t\t<div id=\"stock_body_content\">\n\t\t\t\t\t\t<span class=\"stock_label\">Volumen</span><br/>\n\t\t\t\t\t\t<strong class=\"stock_strong\"><span id=\"stockVolume\"></span></strong>\n\t\t\t\t\t\t<br /><br />\n\t\t\t\t\t</div>\n\t\t\t\t\t\t<div style=\"clear: both;\"></div>\n\t\t\t\t\t</div>\n\t\t\t\t</div>\n\t\t\t\t<div>\n\t\t\t\t\t<!-- Marcos -->\n\t\t\t\t\t<br>\n\t\t\t\t\t<div>\n\t\t\t\t\t\t<form class=\"frConverter\">\n\t\t\t\t\t\t\t<div>\n\t\t\t\t\t\t\t\t<span>Yo Tengo</span><br/>\n\t\t\t\t\t\t\t\t<select name=\"base_currency\" onChange=\"frExCalc();\"></select>\n\t\t\t\t\t\t\t\t<input type=\"hidden\" name=\"default_base\" value=\"usd\"/>\n\t\t\t\t\t\t\t\t<input type=\"text\" name=\"base_value\" value=\"1\"/>\n\t\t</div>\n\t\t\t\t\t\t\t\t<div>\n\t\t\t\t\t\t\t\t\t<span>Yo Quiero</span><br/>\n\t\t\t\t\t\t\t\t\t<select name=\"target_currency\" onChange=\"frExCalc();\"></select>\n\t\t\t\t\t\t\t\t\t<input type=\"hidden\" name=\"default_target\" value=\"mxn\"/>\n\t\t\t\t\t\t\t\t\t<input type=\"text\" name=\"target_value\" id=\"pesos\" value=\"\"/><br/>\n\t\t</div>\n\t\t\t\t\t\t</form>\n\t\t\t\t\t\t<input id=\"clickMe\" type=\"button\" value=\"Poner en Celda\" />\n</div>\n\t\t\t\t\t\t<!-- Marcos -->\n\t\t\t\t\t</div>"
language: html
style:
content: |-
#stockIndicator {
text-align: left;
padding: 10px;
margin: 5px;
color: red;
}
.ajaxtrigger:hover {
cursor: pointer;
cursor: hand;
}
#stock_miniQuote_head {
background-color: #464A55;
color: orange;
font-size: 14px;
font-weight: bold;
padding-bottom: 10px;
padding-left: 10px;
padding-right: 10px;
padding-top: 10px;
}
#stock_miniQuote {
border-bottom-color: #DDDDDD;
border-bottom-left-radius: 5px 5px;
border-bottom-right-radius: 5px 5px;
border-bottom-style: solid;
border-bottom-width: 1px;
border-left-color: #DDDDDD;
border-left-style: solid;
border-left-width: 1px;
border-right-color: #DDDDDD;
border-right-style: solid;
border-right-width: 1px;
border-top-color: initial;
border-top-style: none;
border-top-width: initial;
list-style-type: none;
margin-bottom: 10px;
padding-bottom: 0;
padding-top: 10px;
vertical-align: text-top;
height: 100%;
width: 99%;
}
.stock_divider {
border-bottom: 1px solid #B2B0AD;
padding-bottom: 5px;
}
#stock_left {
float: left;
width: 35%;
height: 50px;
border-right: 1px solid #B2B0AD;
padding: 0 15px;
}
#stock_right {
float: right;
padding: 0 20px;
vertical-align: text-top;
}
.stock_label {
font-size: 14px;
}
.stock_strong {
font-size: 17px;
}
#stock_body {
padding: 10px 0 15px;
}
#stock_body_content {
float: left;
width: 170px;
padding: 0 15px;
}
.oj{
color: orange
}
language: css
libraries: |-
https://appsforoffice.microsoft.com/lib/1/hosted/office.js
@types/office-js
[email protected]/dist/css/fabric.min.css
[email protected]/dist/css/fabric.components.min.css
[email protected]/client/core.min.js
@types/core-js
[email protected]
@types/[email protected]
https://unpkg.com/[email protected]/dist/css/bootstrap.min.css
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment