Autoxls

• Página del proyecto
• Proyecto en github
• Descarga de ejecutable para windows

autoxls es una herramienta de linea de comandos para automatizar las generación de archivos Excel 2003 a partir de resultados obtenidos de consultas a bases de datos.

Características principales

• Múltiples conexiones a bases de datos
• Mútiples fuentes de datos
• Múltiples consultas/querys/Stored procedures
• Capacidad de procesar múltiples recordsets a partir de una única consulta
• Generáción automatizada de uno o más archivos Excel por ejecución
• Definición dinámica de nombres y textos a partir de “keywords” definidas en la invocación o por un archivo externo de keywords, puede aplicar:
  • Nombre del archivo
  • Nombres de las solapas
  • Textos en la planilla
  • Parámetros de invocación de la consulta
  • Datos de la conexión: servidor, usuario, contraseña
• Definir multiples solapas por archivo
• Automatizar la generación de múltiples objetos
  • textos y filas completas
  • formulas
  • grillas de datos o tablas
• Definir formatos de los objetos
  • anchos de columnas
  • colores
  • tipos de letra
  • Alineaciones
  • formatos númericos

Antes de empezar

Para comenzar, antes de probar la ejecución de esta herramienta, es necesario escribir un archivo de definición de la exportación a realizar. Este no es más que un archivo en formato JSON, que describe datos, archivos, planillas y formatos de la exportación. Nota: para validar el formato del mismo: JSON Editor Online.

Este archivo definirá los siguientes elementos:

• datasources: uno o más conexiones a bases de datos, con las siguientes propiedades:

  • data_connect_str: que establece una conexión a un servidor de datos.
  • data_query: que define la correspondiente consulta. Podrá ser una “query” común o directamente la ejecución de un stored procedure.
  • data_query_file: Archivo dónde encontramos la consulta SQL.

• files: Que define la generación de uno o más archivos Excel. Por cada archivo se podrá definir una o más:

  • sheets: Es decir, solapas de la planilla, por cada una de estas se pueden definir varios objetos Excel:

    • text: Texto estático, normalmente titulos, se define la celda, el texto y el formato.
    • text_rows: Define una lista de textos estáticos, que se escriben a partir de una posición en la fila, una celda a continuación de la otra con un determinado formato
    • text_formated: Una especialización de los objetos de texto, que permite aplicar formatos sobre datos recibidos en las keywords.
    • datagrid: Una grilla de datos, la salida final de los datos recuperados.
    • formulas: Formulas de escel
    • table: Una tabla Excel.

• formats: Cada objetos se “dibuja” con distintos formatos, estos se definene a nivel general. Hay dos tipos, los básicos o “primitivos”, por ejemplo: "right": { "align" : "right" } y los compuestos que se definen como la suma de atributos primitivos, por ejemplo: "encabezado": [ "default_font", "bold", "color", { "bottom" : 1, "bottom_color" : "#0000FF", "text_wrap": "True", "valign": "top" }], en este ejemplo “default_font”, “bold”, “color” son formatos definidos previamente y { “bottom” : 1, “bottom_color” : “#0000FF”, “text_wrap”: “True”, “valign”: “top” } es un primitivo definido en el momento.

• conditional: Formatos condicionales

Nota: Para referencia de las definiciones, ver la documentación del módulo XlsxWriter

Construcción de la cadena Dsn según datasource

* SQL Server: "DRIVER={SQL Server};SERVER=<<server>>;DATABASE=<<database>>;UID=<<usuario>>;PWD=<<password>>" 

Primeros pasos

Para entender el funcionamiento de esta herramienta, vamos a imaginar el siguiente escenario: Tenemos un conjunto de servidores SQL Server y deseamos de forma automatizada generar un informe a una determinada hora de los procesos corriendo en los mismos. Para esto contamos con un clásico stored procedure llamado sp_who2, usando autoxls resulta muy fácil hacer esto. El primer paso es generar la definición del proceso de exportación de datos, esto lo haremos escribiendo un archivo JSON similar a este:

{
	"datasources": {
		"data" : {
			"data_connect_str" : "DRIVER={SQL Server};SERVER=<<server>>;DATABASE=master;UID=<<user>>;PWD=<<passw>>",
			"data_query" : "EXEC sp_who2"
		}
	},
	"files": [
		{
			"filename": "sp_who2 on <<server>>_<<Now>>.xlsx",
			"sheets": [
				{
					"name": "sp_who2 on <<server>>",
					"default_row_height" : 11.5 ,
					"objects": {
						"text": [
							{ "text" : "Resultado del sp_who ejecutado el <<Now>> en <<server>>", "format" : "encabezado_titulo", "at" : "B2" }
						],
						"text_rows": [
							{ "text" : [null,null,null,null,null], "format" : "encabezado_titulo", "at" : "C2" }
						],
						"datagrid": [
							{
								"source" : {"datasource": "data","recordset_index" : 1},
								"at" : "B3",
								"header_format": "encabezado",
								"header_height": 25,
								"freeze_header" : true,
								"datacols" : [
												[ 1, "SPID"						, 8		, "int"				, null ],
												[ 2, "Status"					, 20	, "default"			, null ],
												[ 3, "Login"					, 16	, "default"			, null ],
												[ 4, "HostName"					, 12	, "default"			, null ],
												[11, "ProgramName"				, 60	, "default"			, null ],
												[ 8, "CpuTime"					, 12	, "number"			, null ]

								],
								"autofilter_column_range" : [1,6],
								"subtotals" : [
									{"at" : "END", "format" : "subtotal_int", "total_function" : "2" , "cols_num" : [1] },
									{"at" : "END", "format" : "subtotal", "total_function" : "9" , "cols_num" : [6] }
								]
							}
						]
					}
				}
			]	
		}
	],
	"formats": {
			"default_font"		: { "font_name" : "Verdana", "font_size" : 8, "num_format" : "", "valign" : "top" },
			"right" 			: { "align" : "right" },
			"left" 				: { "align" : "left" },
			"bold" 				: { "bold" : "True" },
			"color"				: { "bg_color": "#C6EFCE" },
			"int_fmt"			: { "num_format" : "#,##0" },
			"number2_fmt"		: { "num_format" : "#,##0.00" },
			"default" 			: [ "default_font", "left" ] ,
			"encabezado_titulo"	: [ "default_font", "bold", "color"],
			"encabezado"		: [ "default_font", "bold", "color", { "bottom" : 1, "bottom_color" : "#0000FF", "text_wrap": "True", "valign": "top" }],
			"subtotal_int" 		: [ "default_font", "right", "bold", "int_fmt" ],
			"subtotal" 			: [ "default_font", "right", "bold", "number2_fmt" ],
			"number" 			: [ "default_font", "right", "number2_fmt" ],
			"int" 				: [ "default_font", "right", "int_fmt" ]
	}
}

TO DO…

Ejecución

uso: autoxls [-h] [-v] [-o "path"] [-n <level>] [-l file] [-f "archivo"]
             [-k '{key:value,key:value}'] [-s]
             ["archivo"]

Generación automatizada de archivos Excel (v0.9)
2014, 2015, 2016 Patricio Moracho <pmoracho@gmal.com>

argumentos posicionales:
  "archivo"                               Archivo de entrada (JSON)

argumentos opcionales:
  -h, --help                              mostrar esta ayuda y salir
  -v, --version                           show program's version number and exit
  -o "path", --outputpath "path"          Carpeta de salida dónde se almacenaran las planillas
  -n <level>, --loglevel <level>          Nivel de log (default: ninguno)
  -l file, --logfile file                 Archivo de log
  -f "archivo", --keywordfile "archivo"   Archivo de keywords del procesos
  -k '{key:value,key:value}', --keywords '{key:value,key:value}'
                                          Keywords del procesos
  -s, --start-excel                       Abrir automáticamente las planillas generadas

Definiciones de keywords

TO DO..

Niveles de log

Utilizar el parámetro -n o --loglevel para indicar el nivel de información que mostrará la herramienta. Por defecto el nivel es NONE, que no mustra ninguna información.

Notas para el desarrollador:

Requisitos iniciales

El proyecto autoxls esta construido usando el lenguaje python, a la fecha no se usan librerías adicionales a las propias de python, pero de todas formas es recomendable preparar antes que nada, un entorno de desarrollo. A continuación expondremos en detalle cuales son los pasos para tener preparado el entorno de desarrollo. Este detalle esta orientado a la implementación sobre Windows 32 bits, los pasos para versiones de 64 bits son sustancialmente distintos, en particular por algunos de los “paquetes” que se construyen a partir de módulos en C o C++, de igual forma la instalación sobre Linux tiene sus grandes diferencias. Eventualmente profundizaremos sobre estos entornos, pero en principio volvemos a señalar que el siguiente detalle aplica a los ambientes Windows de 32 bits:

• Obviamente en primer lugar necesitaremos Python, por ahora únicamente la versión 3.4. La correcta instalación se debe verificar desde la línea de comandos: python --version. Si todo se instaló correctamente se debe ver algo como esto Python 3.4.0, sino verificar que Python.exe se encuentre correctamente apuntado en el PATH.

• Es conveniente pero no mandatorio hacer upgrade de la herramienta pip: python -m pip install --upgrade pip

• Virutalenv. Es la herramienta estándar para crear entornos “aislados” de python. Para no tener conflictos de desarrollo lo que haremos mediante esta herramienta es crear un “entorno virtual” de python en una carpeta del proyecto (venv). Este “entorno virtual” contendrá una copia completa de Python, al activarlo se modifica el PATH al python.exe que apuntará ahora a nuestra carpeta del entorno, evitando cualquier tipo de conflicto con un entorno Python ya existente. La instalación de virtualenv se hara mediante pip install virtualenv

• Descargar el proyecto desde Github, se puede descargar desde la página el proyecto como un archivo Zip, o si contamos con Git sencillamente haremos un git clone https://github.com/pmoracho/parseit.

• El proyecto una vez descomprimido o luego del clonado del repositorio tendrá una estructura de directorios similar a la siguiente:

autoxls.git
   |-dist
   |-tests
   |-tools

Requerimientos adicionales

Autoxls requiere las siguientes librerías:

• XlsxWriter: Estupenda libreria para generar archivos Excel, pip install XlsxWriter.
• pypyodbc para la conectividada con las bases de datos: pip install pypyodbc
• pyinstaller solo si el objetivo final es construir un ejecutable binario, esta herramienta es bastante sencilla, para instalar: pip install pyinstaller

Preparación del entorno virtual local

Para poder ejecutar, o crear la distribución de la herramientas, lo primero que deberemos hacer es armar un entorno python “virtual” que alojaremos en una subcarpeta del directorio principal que llamaremos “venv”. En el proyecto incorporamos una herramienta de automatización de algunas tareas básicas. Se trata de make.py, la forma de ejecutarlo es la siguiente: python tools\make.py <comando> la ejecución si parámetros o mediante el parámetro --help arrojará una salida como lo que sigue:

Automatización de tareas para el proyecto Autoxls
(c) 2016, Patricio Moracho <pmoracho@gmail.com>

Uso: make <command> [<args>]

Los comandos más usados:
   devcheck   Hace una verificación del entorno de desarrollo
   devinstall Realiza la instalación del entorno de desarrollo virtual e instala los requerimientos
   docinstall Intalación de Sphinx
   clear      Elimina archivos innecesarios
   test       Ejecuta todos los tests definidos del proyecto
   build      Construye la distribución binaria de las herramientas del proyecto

argumentos posicionales:
  command     Comando a ejecutar

argumentos opcionales:
  -h, --help  mostrar esta ayuda y salir

Para preparar el entorno virtual simplemente haremos python tools\make.py devinstall, este proceso si resulta exitoso deberá haber realizado las siguientes tareas:

• Creación de un entorno pyhton virtual en la carpeta “venv”, invocable mediante venv\Scripts\activate.bat en Windows o source venv/Scripts/activate en entornos Linux o Cygwin/Mingw (en Windows)
• Instalado todas las dependencias necesarias

Notas adicionales:

• Es recomendable y cómodo, pero entiendo que no es mandatorio, contar con un entorno estilo “Linux”, por ejemplo MinGW, tal como dice la página del proyecto: “MinGW provides a complete Open Source programming tool set which is suitable for the development of native MS-Windows applications, and which do not depend on any 3rd-party C-Runtime DLLs. (It does depend on a number of DLLs provided by Microsoft themselves, as components of the operating system; most notable among these is MSVCRT.DLL, the Microsoft C runtime library. Additionally, threaded applications must ship with a freely distributable thread support DLL, provided as part of MinGW itself).” De este entorno requerimos algunas herramientas de desarrollo: Bash para la línea de comandos y Make para la automatización de varias tareas del proyecto.

• Usar “soft tabs”: Con cualquier editor que usemos configurar el uso del en vez de los espacios, yo prefiero el puro al espacio, entiendo que es válido el otro criterio pero ya los fuentes están con esta configuración, por lo que para evitar problemas al compilar los .py recomiendo seguir usando este criterio. Asimismo configurar en 4 posiciones estos .