Descarregue dados no Excel. Civilizado

Existem tarefas na indústria de TI que, no contexto de sucessos em big data , aprendizado de máquina , blockchain e outras tendências da moda, parecem completamente pouco atraentes, mas há décadas não deixam de ser relevantes para todo um exército de desenvolvedores. Será sobre o antigo como a tarefa mundial de criar e enviar documentos do Excel, que enfrentou todos os que já escreveram aplicativos para negócios.





Que possibilidades de criação de arquivos do Excel existem em princípio?

1. Macros VBA. Atualmente, por razões de segurança, a idéia de usar macros geralmente não é adequada.
2. Automação do Excel com um programa externo através da API. Requer o Excel na mesma máquina que o programa que gera relatórios do Excel. No momento em que os clientes eram espessos e gravados na forma de aplicativos de desktop para Windows, esse método era adequado (embora não diferisse em velocidade e confiabilidade), nas realidades atuais esse dificilmente é um caso possível.
3. Gere arquivo XML-Excel diretamente. Como você sabe, o Excel oferece suporte ao formato XML para salvar um documento, que pode ser potencialmente gerado / modificado usando qualquer meio de trabalhar com XML. Esse arquivo pode ser salvo com a extensão .xls e, embora estritamente não seja um arquivo xls, o Excel o abre bem. Essa abordagem é bastante popular, mas as desvantagens incluem o fato de que qualquer solução baseada na edição direta do formato XML-Excel é um "hack" único, desprovido de generalidade.
4. Por fim, é possível gerar arquivos do Excel usando bibliotecas de código aberto, das quais o Apache POI é especialmente conhecido. Os desenvolvedores do Apache POI fizeram um trabalho titânico nos formatos binários de documentos de engenharia reversa do MS Office e continuam a manter e desenvolver essa biblioteca ao longo dos anos. O resultado dessa engenharia reversa, por exemplo, é usado no Open Office para implementar a preservação de documentos em formatos compatíveis com o MS Office.

Na minha opinião, é o último dos métodos que agora é preferido para gerar documentos compatíveis com o MS Office. Por um lado, não requer a instalação de nenhum software proprietário no servidor e, por outro lado, fornece uma API rica que permite usar todas as funcionalidades do MS Office.

Mas o uso direto do Apache POI tem suas desvantagens. Primeiramente, esta é uma biblioteca Java e, se seu aplicativo estiver gravado em mais de uma das linguagens da JVM, você dificilmente poderá usá-lo. Em segundo lugar, é uma biblioteca de baixo nível que trabalha com conceitos como "célula", "coluna", "fonte". Portanto, "de frente" o procedimento escrito para gerar um documento rapidamente se transforma em um "macarrão" abundante de código difícil de ler, onde não há separação em um modelo de dados e apresentação, é difícil fazer alterações e, em geral, dor e vergonha. E uma excelente ocasião para delegar a tarefa ao programador mais inexperiente - deixe-o escolher.

Mas poderia ser completamente diferente. O projeto xilofone sob a LGPL, construído com base no Apache POI, é baseado em uma idéia que tem uma história estimada em 15 anos. Nos projetos em que participei, ele foi usado em combinação com uma grande variedade de plataformas e idiomas - e, devido à variedade de formulários criados com sua ajuda em uma grande variedade de projetos, provavelmente já existem milhares. Este é um projeto Java que pode funcionar como um utilitário de linha de comando e como uma biblioteca (se você tiver um código na linguagem JVM, poderá conectá- lo como uma dependência do Maven).

O xilofone implementa o princípio de separar o modelo de dados da sua apresentação. No procedimento de upload, você precisa gerar dados no formato XML (sem se preocupar com células, fontes e linhas divisórias), e o Xilophone, usando um modelo do Excel e um descritor que descreve como rastrear seu arquivo XML com dados, gerará o resultado, conforme mostrado no diagrama:


O modelo de documento (modelo xls / xlsx) é mais ou menos assim:


Como regra, a compra desse modelo é fornecida pelo cliente. O cliente envolvido está feliz em participar da criação do modelo: começando por escolher o formulário certo no "Consultor" ou inventando um a partir do zero e terminando com o tamanho da fonte e a largura das linhas divisórias. A vantagem do modelo é que é fácil fazer pequenas edições, mesmo quando o relatório está totalmente desenvolvido.

Quando o trabalho de "design" é concluído, o desenvolvedor permanece

1. Crie um procedimento para baixar os dados necessários no formato XML.
2. Crie um descritor que descreva o procedimento para atravessar elementos de um arquivo XML e copiar fragmentos de modelo no relatório resultante
3. Vincule células de modelo a elementos de um arquivo XML usando expressões XPath .

Com o upload para XML, tudo fica mais ou menos claro: basta selecionar a representação XML apropriada dos dados necessários para preencher o formulário. O que é um descritor?

Se o formulário que estamos criando não tiver elementos duplicados com números diferentes (como linhas de fatura, que são diferentes em faturas diferentes), o descritor terá a seguinte aparência:

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

Aqui raiz é o nome do elemento raiz do nosso arquivo de dados XML e o intervalo A1: Z100 é o intervalo retangular de células do modelo que será copiado para o resultado. Além disso, como pode ser visto na ilustração anterior, os campos curinga cujos valores são substituídos pelos dados de um arquivo XML têm o formato ~{XPath-} (til, colchete, expressão XPath em relação ao elemento XML atual, fechando o colchete).

E se precisarmos repetir elementos no relatório? Naturalmente, eles podem ser representados como elementos de um arquivo de dados XML, e um descritor ajuda a ajudá-lo da maneira certa. A repetição de elementos no relatório pode ter uma direção vertical (quando inserimos linhas de fatura, por exemplo) e horizontal (quando inserimos colunas do relatório analítico). Ao mesmo tempo, podemos usar o aninhamento de elementos XML para refletir o aninhamento arbitrário e profundo de elementos de relatório repetidos, conforme mostrado no diagrama:


Os quadrados vermelhos marcam as células que serão o canto superior esquerdo do próximo fragmento retangular que o gerador de relatórios está encaixando.

Há outra opção possível para repetir elementos: folhas em uma pasta de trabalho do Excel. A capacidade de organizar essa iteração também está disponível.

Considere um exemplo um pouco mais complexo. Suponha que precisamos obter um relatório de resumo como o seguinte:


Deixe o usuário selecionar o intervalo de anos para descarregar, portanto, linhas e colunas são criadas dinamicamente neste relatório. A representação XML dos dados para esse relatório pode ter a seguinte aparência:


Temos a liberdade de escolher os nomes das tags ao seu gosto, a estrutura também pode ser arbitrária, mas tendo em vista a facilidade de conversão no relatório. Por exemplo, geralmente escrevo os valores exibidos na planilha em atributos, porque simplifica as expressões XPath (é conveniente quando elas se parecem com @ ).

O modelo para esse relatório terá a seguinte aparência (compare expressões XPath com os nomes de atributo das tags correspondentes):


Agora vem a parte mais interessante: criar um identificador. Como este é um relatório quase completamente montado dinamicamente, o descritor é bastante complicado, na prática (quando só temos um "cabeçalho" do documento, suas linhas e "rodapé") tudo geralmente é muito mais simples. Aqui está o descritor necessário neste caso:


Os elementos completos do descritor são descritos na documentação . Em resumo, os elementos básicos de um descritor significam o seguinte:

• elemento - transição para o modo de leitura de um elemento de um arquivo XML. Pode ser o elemento raiz do descritor ou estar dentro da iteration . O atributo name pode ser usado para definir vários filtros para elementos, por exemplo
  • name="foo" - elementos com o nome de tag foo
  • name="*" - todos os elementos
  • name="tagname[@attribute='value']" - elementos com um nome e valor de atributo específicos
  • name="(before)" , name="(after)" - elementos "virtuais" que precedem a iteração e fecham a iteração.
• iteração - transição para o modo de iteração. Só pode ser element interno. Vários parâmetros podem ser definidos, p.
  • mode="horizontal" - modo de saída mode="horizontal" (vertical por padrão)
  • index=0 - restringe a iteração ao primeiro elemento encontrado
• saída - alterna para o modo de saída. Os principais atributos são os seguintes:
  • sourcesheet - A planilha do livro de modelos da qual o intervalo de saída é obtido. Se não especificado, a folha atual (última usada) é aplicada.
  • range - o intervalo do modelo a ser copiado para o documento resultante, por exemplo, "A1: M10", "5: 6" ou "C: C". (Usar intervalos de linhas do tipo “5: 6” no modo de saída horizontal e intervalos de colunas do tipo “C: C” no modo de saída vertical causará um erro).
  • worksheet - se definida, uma nova planilha é criada no arquivo de saída e a posição de saída é deslocada para a célula A1 desta planilha. O valor desse atributo, igual a uma constante ou expressão XPath, é substituído no nome da nova planilha.

Na realidade, existem muito mais opções no descritor, consulte a documentação.

Bem, agora é hora de baixar o Xilophone e começar a relatar.
Pegue o arquivo da bandeja ou do Maven Central (NB: no momento da leitura deste artigo, são possíveis versões mais recentes). Na pasta / bin, há um script de shell; quando você o executa sem parâmetros, você verá um prompt sobre os parâmetros da linha de comando. Para obter o resultado, precisamos "alimentar" com o xilofone todos os ingredientes previamente preparados:

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

Abra o arquivo report.xlsx e verifique se obtemos exatamente o que precisamos:


Como a biblioteca ru.curs: xylophone está disponível no Maven Central sob a licença LGPL, ela pode ser usada sem problemas em programas em qualquer idioma da JVM. Talvez o exemplo mais compacto e completo de trabalho seja obtido no Groovy, o código não precisa de comentários:

 @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.' 

A classe XML2Spreadsheet possui várias versões sobrecarregadas do método de process estático, mas todas se resumem a transferir os mesmos "ingredientes" necessários para a preparação do relatório.

Uma opção importante que ainda não mencionei é a capacidade de escolher entre analisadores DOM e SAX no estágio de analisar um arquivo com dados XML. Como você sabe, o analisador DOM carrega o arquivo inteiro na memória, constrói sua representação de objeto e possibilita ignorar seu conteúdo de maneira arbitrária (incluindo retornar repetidamente ao mesmo elemento). O analisador SAX nunca coloca o arquivo de dados inteiro na memória, mas processa-o como um "fluxo" de elementos, impedindo que ele retorne ao elemento novamente.

O uso do modo SAX no xilofone (por meio da -sax linha de comando -sax ou da configuração do parâmetro useSax método useSax como XML2Spreadsheet.process ) pode ser extremamente útil quando você precisar gerar arquivos muito grandes. Devido à velocidade e lucratividade dos recursos do analisador SAX, a velocidade de geração de arquivos aumenta muitas vezes. Isso é fornecido à custa de algumas pequenas restrições no descritor (descritas na documentação), mas na maioria dos casos os relatórios atendem a essas restrições, portanto, recomendo usar o modo SAX sempre que possível.

Espero que você tenha gostado do método de upload para o Excel via xilofone e economize muito tempo e nervosismo - como você nos salvou.

E, finalmente, links novamente:

• fontes estão aqui: github.com/CourseOrchestra/xylophone
• a documentação está aqui: courseorchestra.imtqy.com/xylophone
• todos os exemplos de código deste artigo estão aqui: github.com/inponomarev/xylophone-example .