Descargar datos en Excel. Civilizado

Hay tareas en la industria de TI que, en el contexto de los éxitos en big data , aprendizaje automático , blockchain y otras tendencias de la moda, parecen completamente poco atractivas, pero durante décadas no han dejado de ser relevantes para todo un ejército de desarrolladores. Se tratará de lo antiguo como la tarea mundial de crear y cargar documentos de Excel, que enfrentaron todos los que alguna vez han escrito solicitudes para negocios.





¿Qué posibilidades de construir archivos Excel existen en principio?

1. VBA macros. Hoy en día, por razones de seguridad, la idea de usar macros a menudo no es adecuada.
2. Automatización de Excel con un programa externo a través de la API. Requiere Excel en la misma máquina que el programa que genera informes de Excel. En un momento en que los clientes eran gruesos y estaban escritos en forma de aplicaciones de escritorio de Windows, este método era adecuado (aunque no difería en velocidad y confiabilidad), en las realidades actuales, este es un caso difícil de lograr.
3. Generar archivo XML-Excel directamente. Como ya sabe, Excel admite el formato XML para guardar un documento, que potencialmente puede generarse / modificarse utilizando cualquier medio de trabajo con XML. Este archivo se puede guardar con la extensión .xls, y aunque, estrictamente hablando, no es un archivo xls, Excel lo abre bien. Este enfoque es bastante popular, pero las desventajas incluyen el hecho de que cualquier solución basada en la edición directa del formato XML-Excel es un "hack" único que carece de generalidad.
4. Finalmente, es posible generar archivos de Excel usando bibliotecas de código abierto, de las cuales Apache POI es especialmente conocido. Los desarrolladores de Apache POI han realizado un trabajo titánico en los formatos de documentos binarios de MS Office de ingeniería inversa, y continúan manteniendo y desarrollando esta biblioteca a lo largo de los años. El resultado de esta ingeniería inversa, por ejemplo, se utiliza en Open Office para implementar la preservación de documentos en formatos compatibles con MS Office.

En mi opinión, es el último de los métodos que ahora se prefiere para generar documentos compatibles con MS Office. Por un lado, no requiere la instalación de ningún software propietario en el servidor, y por otro lado, proporciona una API rica que le permite utilizar todas las funcionalidades de MS Office.

Pero el uso directo de Apache POI tiene sus inconvenientes. En primer lugar, esta es una biblioteca Java, y si su aplicación está escrita en más de uno de los lenguajes JVM, difícilmente podrá usarla. En segundo lugar, es una biblioteca de bajo nivel que funciona con conceptos como "celda", "columna", "fuente". Por lo tanto, "frente" al procedimiento escrito para generar un documento se convierte rápidamente en un abundante "fideo" de código difícil de leer, donde no hay separación en un modelo de datos y presentación, es difícil hacer cambios y, en general, dolor y vergüenza. Y una excelente ocasión para delegar la tarea al programador más inexperto: déjelo elegir.

Pero podría ser completamente diferente. El proyecto Xilófono bajo LGPL, construido sobre la base de Apache POI, se basa en una idea que tiene una historia estimada de 15 años. En los proyectos donde participé, se usó en combinación con una amplia variedad de plataformas e idiomas, y, debido a la variedad de formularios realizados con su ayuda en una amplia variedad de proyectos, probablemente ya haya miles. Este es un proyecto de Java que puede funcionar como una utilidad de línea de comandos y como una biblioteca (si tiene código en el lenguaje JVM, puede conectarlo como una dependencia de Maven).

Xilófono implementa el principio de separar el modelo de datos de su presentación. En el procedimiento de carga, debe generar datos en formato XML (sin preocuparse por las celdas, las fuentes y las líneas divisorias), y Xilófono, utilizando una plantilla de Excel y un descriptor que describe cómo rastrear su archivo XML con datos, generará el resultado, como se muestra en el diagrama:


La plantilla del documento (plantilla xls / xlsx) se ve así:


Como regla general, el cliente proporciona la adquisición de dicha plantilla. El cliente involucrado está feliz de participar en la creación de la plantilla: comenzando por elegir la forma correcta del "Consultor" o inventando una desde cero, y terminando con el tamaño de fuente y el ancho de las líneas divisorias. La ventaja de la plantilla es que es fácil realizar modificaciones menores, incluso cuando el informe está completamente desarrollado.

Cuando se realiza el trabajo de "diseño", el desarrollador permanece

1. Cree un procedimiento para descargar los datos necesarios en formato XML.
2. Cree un descriptor que describa el procedimiento para atravesar elementos de un archivo XML y copiar fragmentos de plantilla en el informe resultante
3. Enlace celdas de plantilla a elementos de un archivo XML utilizando expresiones XPath .

Con la carga a XML, todo está más o menos claro: simplemente seleccione la representación XML adecuada de los datos necesarios para completar el formulario. ¿Qué es un descriptor?

Si el formulario que estamos creando no tiene elementos duplicados con diferentes números (como líneas de factura, que son diferentes en diferentes facturas), el descriptor se vería así:

<element name="root"> <output range="A1:Z100"/> </element> 

Aquí, raíz es el nombre del elemento raíz de nuestro archivo de datos XML, y el rango A1: Z100 es el rango rectangular de celdas de la plantilla que se copiará al resultado. Además, como se puede ver en la ilustración anterior, los campos comodín cuyos valores se reemplazan con datos de un archivo XML tienen el formato ~{XPath-} (tilde, corchete, expresión XPath relativa al elemento XML actual, cerrando el corchete).

¿Qué pasa si necesitamos elementos repetidos en el informe? Naturalmente, se pueden representar como elementos de un archivo de datos XML, y un descriptor ayuda a ayudarlo a encontrarlo de la manera correcta. La repetición de elementos en el informe puede tener una dirección vertical (cuando insertamos líneas de factura, por ejemplo) y horizontal (cuando insertamos columnas del informe analítico). Al mismo tiempo, podemos usar la anidación de elementos XML para reflejar la anidación arbitrariamente profunda de elementos de informe repetidos, como se muestra en el diagrama:


Los cuadrados rojos marcan las celdas que serán la esquina superior izquierda del siguiente fragmento rectangular que el generador de informes está acoplando.

Hay otra opción posible para repetir elementos: hojas en un libro de Excel. La capacidad de organizar tal iteración también está disponible.

Considere un ejemplo un poco más complejo. Supongamos que necesitamos obtener un informe resumido como el siguiente:


Deje que el usuario seleccione el rango de años para la descarga, por lo tanto, tanto las filas como las columnas se crean dinámicamente en este informe. La representación XML de los datos para dicho informe podría verse así:


Somos libres de elegir los nombres de las etiquetas a su gusto, la estructura también puede ser arbitraria, pero teniendo en cuenta la facilidad de conversión al informe. Por ejemplo, generalmente escribo los valores que se muestran en la hoja en atributos, porque simplifica las expresiones XPath (es conveniente cuando se parecen a @ ).

La plantilla para dicho informe se verá así (compare las expresiones XPath con los nombres de los atributos de las etiquetas correspondientes):


Ahora viene la parte más interesante: crear un identificador. Dado que este es un informe casi completamente ensamblado dinámicamente, el descriptor es bastante complicado, en la práctica (cuando solo tenemos un "encabezado" del documento, sus líneas y "pie de página") todo es generalmente mucho más simple. Aquí está el descriptor necesario en este caso:


Los elementos descriptores completos se describen en la documentación . En resumen, los elementos básicos de un descriptor significan lo siguiente:

• element : transición al modo de lectura de un elemento de un archivo XML. Puede ser el elemento raíz del descriptor o estar dentro de la iteration . El atributo de name se puede usar para establecer varios filtros para elementos, por ejemplo
  • name="foo" - elementos con la etiqueta name foo
  • name="*" - todos los elementos
  • name="tagname[@attribute='value']" : elementos con un nombre y un valor de atributo específicos
  • name="(before)" , name="(after)" - Elementos "virtuales" que preceden a la iteración y cierran la iteración.
• iteración : transición al modo de iteración. Solo puede estar dentro del element . Se pueden configurar varios parámetros, p.
  • mode="horizontal" - modo de salida mode="horizontal" (vertical por defecto)
  • index=0 - restringe la iteración al primer elemento encontrado
• salida - cambia al modo de salida. Los atributos principales son los siguientes:
  • hoja de origen: la hoja de libro de plantilla de la que se toma el rango de salida. Si no se especifica, se aplica la hoja actual (la última utilizada).
  • range : el rango de la plantilla que se copiará en el documento resultante, por ejemplo, “A1: M10”, “5: 6” o “C: C”. (El uso de rangos de fila de tipo “5: 6” en el modo de salida horizontal y rangos de columna de tipo “C: C” en el modo de salida vertical provocará un error).
  • worksheet : si se define, se crea una nueva hoja en el archivo de salida y la posición de salida se desplaza a la celda A1 de esta hoja. El valor de este atributo, igual a una expresión constante o XPath, se sustituye en el nombre de la nueva hoja.

En realidad, hay muchas más opciones en el descriptor, consulte la documentación.

Bueno, ahora es el momento de descargar Xilófono y comenzar a informar.
Tome el archivo de bintray o Maven Central (Nota: en el momento de leer este artículo, son posibles versiones más recientes). En la carpeta / bin hay un script de shell, cuando lo ejecuta sin parámetros, verá un mensaje sobre los parámetros de la línea de comandos. Para obtener el resultado, necesitamos "alimentar" al xilófono todos los ingredientes previamente preparados:

 xylophone -data testdata.xml -template template.xlsx -descr descriptor.xml -out report.xlsx 

Abra el archivo report.xlsx y asegúrese de obtener exactamente lo que necesitamos:


Dado que la biblioteca ru.curs: xylophone está disponible en Maven Central bajo la licencia LGPL, se puede usar sin problemas en programas en cualquier lenguaje JVM. Quizás el ejemplo más compacto y totalmente funcional se obtiene en Groovy, el código no necesita comentarios:

 @Grab('ru.curs:xylophone:6.1.3') import ru.curs.xylophone.XML2Spreadsheet baseDir = '.' new File(baseDir, 'testdata.xml').withInputStream { input -> new File(baseDir, 'report.xlsx').withOutputStream { output -> XML2Spreadsheet.process(input, new File(baseDir, 'descriptor.xml'), new File(baseDir, 'template.xlsx'), false, output) } } println 'Done.' 

La clase XML2Spreadsheet tiene varias versiones sobrecargadas del método de process estático, pero todas se reducen a la transferencia de los mismos "ingredientes" necesarios para preparar el informe.

Una opción importante que aún no he mencionado es la capacidad de elegir entre analizadores DOM y SAX en la etapa de analizar un archivo con datos XML. Como sabe, el analizador DOM carga todo el archivo en la memoria, construye su representación de objetos y hace posible omitir su contenido de forma arbitraria (incluido el regreso repetido al mismo elemento). El analizador SAX nunca guarda todo el archivo de datos en la memoria, sino que lo procesa como un "flujo" de elementos, evitando que vuelva al elemento nuevamente.

Usar el modo SAX en Xilófono (a través de la -sax línea de comandos -sax o establecer el parámetro useSax método useSax en XML2Spreadsheet.process ) puede ser críticamente útil cuando necesita generar archivos muy grandes. Debido a la velocidad y rentabilidad de los recursos del analizador SAX, la velocidad de generación de archivos aumenta muchas veces. Esto se da a costa de algunas pequeñas restricciones en el descriptor (descrito en la documentación), pero en la mayoría de los casos los informes satisfacen estas restricciones, por lo que recomendaría usar el modo SAX siempre que sea posible.

Espero que les haya gustado el método de carga en Excel a través del Xilófono y que les ahorre mucho tiempo y nervios, como nos salvó.

Y finalmente, enlaces de nuevo:

• las fuentes están aquí: github.com/CourseOrchestra/xylophone
• la documentación está aquí: courseorchestra.imtqy.com/xylophone
• Todos los ejemplos de código de este artículo están aquí: github.com/inponomarev/xylophone-example .