Excel e Open XML SDK: gerando novas planilhas .xlsx a partir de templates

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Veja neste artigo como gerar planilhas do Excel 2007-2010 empregando um arquivo pré-existente como modelo. A fim de cumprir este objetivo, serão utilizados recursos de leitura e escrita disponibilizados pelo Open Xml SDK. Este framework corresponde à solução da Microsoft para a manipulação de documentos compatíveis com os novos formatos introduzidos a partir do Office 2007.

Independentemente da finalidade a que possa se prestar uma aplicação, a necessidade de se gerarem arquivos do Excel nos quais constam uma série de informações representa um tipo de demanda extremamente comum em muitas soluções de software. Essas planilhas englobam o resultado de consultas visualizadas a partir de interfaces gráficas, contendo dados sobre movimentações financeiras, apontamentos relativos ao estoque de mercadorias, operações de vendas/compras, dentre outros tipos de análise.

Considerando todo este contexto e levando em conta ainda o lançamento do Microsoft Office 2007, esta versão introduziria como grande novidade um novo padrão de arquivos: trata-se do formato conhecido como Office Open XML ou, simplesmente, Open XML.

Documentos criados segundo a especificação Open XML combinam as tecnologias ZIP e XML. Esta abordagem acaba por trazer diversas vantagens, dentre as quais merecem destaque:

  • Arquivos menores, uma vez que todo o conteúdo associado a um documento estará compactado utilizando o padrão ZIP;
  • As diferentes partes que compõem um arquivo estarão contidas em arquivos XML (o que não acontece com imagens, sons e outros tipos de objetos). Esta característica, aliada ao fato do Open XML ser um padrão aberto, torna mais fácil a atuação de fornecedores de soluções em implementar frameworks compatíveis com este formato.

Esta biblioteca encontra-se atualmente (Setembro/2012) em sua versão 2.0, baseando-se em mecanismos de compactação definidos no namespace System.IO.Packaging do .NET. Já as diferentes classes e estruturas que compõem o Open XML SDK estão organizadas a partir do namespace DocumentFormat.OpenXml. Um novo release (versão 2.5) encontra-se em testes, contando inclusive com suporte à versão futura do pacote Office (2013).

O objetivo deste artigo é descrever a geração de planilhas do Excel (extensão .xlsx) a partir das classes disponibilizadas pelo Open XML SDK. A fim de cumprir esta meta, a aplicação de testes empregará um arquivo que funcionará como template e, a partir de cópias do mesmo, serão geradas novas planilhas contendo informações de um catálogo de produtos.

Criando a Aplicação de Testes

Com intuito de demonstrar a manipulação de planilhas .xlsx a partir do .NET Framework, será criado um projeto do tipo Windows Forms Application chamado TesteGeracaoArquivoExcel. O desenvolvimento desta aplicação foi efetuado por meio da utilização do Microsoft Visual Studio 2010 Ultimate Edition, além do Open XML SDK 2.0 (este último poderá ser baixado a partir do link indicado anteriormente neste artigo).

Uma vez que a solução e o projeto correspondente tenham sido gerados, faz-se necessária a inclusão de referências para as bibliotecas WindowsBase.dll (Figura 1), DocumentFormat.OpenXML.dll (Figura 2) e System.Configuration.dll (Figura 3). A primeira destas dlls refere-se aos recursos de compactação do namespace System.IO.Packaging, ao passo que a segunda contempla os mecanismos de geração e leitura de arquivos disponibilizados pelo Open XML SDK. Por fim, a terceira biblioteca será utilizada na manipulação de um arquivo de configurações, contendo definições necessárias à execução da aplicação de testes.

Adicionando ao projeto uma referência à biblioteca WindowsBase.dll

Figura 1: Adicionando ao projeto uma referência à biblioteca WindowsBase.dll

Adicionando ao projeto uma referência à biblioteca DocumentFormat.OpenXML.dll

Figura 2: Adicionando ao projeto uma referência à biblioteca DocumentFormat.OpenXML.dll

Adicionando ao projeto uma referência à biblioteca System.Configuration.dll

Figura 3: Adicionando ao projeto uma referência à biblioteca System.Configuration.dll

Implementação das classes contendo informações de produtos

O exemplo que está sendo implementando tem por objeto permitir a geração de planilhas contendo informações sobre um catálogo de produtos. Para cada item passível de comercialização serão considerados o código de barras, o nome do produto em questão, a categoria/tipo deste, a data em que o mesmo foi cadastrado no catálogo, além de sua quantidade atual em estoque e do preço unitário de venda. A responsabilidade pelo armazenamento destes dados caberá à classe Produto, cujo código-fonte é apresentado na Listagem 1.

Listagem 1: Classe Produto

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TesteGeracaoArquivoExcel
{
    public class Produto
    {
        public string CodigoBarras { get; set; }

        public string NomeProduto { get; set; }

        public string Categoria { get; set; }

        public DateTime DataCadastro { get; set; }

        public int QtdEstoque { get; set; }

        public double PrecoVenda { get; set; }
    }
}

Já na Listagem 2 está a implementação do tipo estático CatalogoProdutos. Esta classe disponibiliza um método chamado ObterCatalogo (também estático), a partir do qual serão retornados todos os itens que se encontram no catálogo de produtos comercializáveis (instâncias do tipo Produto).

O código inserido na definição da operação ObterCatalogo tem validade apenas para efeitos de simulação. Em um cenário real, as informações correspondentes poderiam estar sendo obtidas a partir de uma base de dados, empregando para isto dispositivos de acesso como o ADO.NET ou, até mesmo, mecanismos mais sofisticados como o Entity Framework e o NHibernate.

Listagem 2: Classe CatalogoProdutos

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TesteGeracaoArquivoExcel
{
    public static class CatalogoProdutos
    {
        public static List ObterCatalogo()
        {
            // Inicialização da coleção de objetos
            List _Produtos = _Produtos = new List();

            // Criação de produtos que serão vinculados
            // à coleção de objetos
            Produto produto;

            produto = new Produto();
            produto.CodigoBarras = "7890000000111";
            produto.NomeProduto = "Iron Maiden - Powerslave";
            produto.Categoria = "CDs";
            produto.DataCadastro = new DateTime(2012, 09, 19);
            produto.QtdEstoque = 37;
            produto.PrecoVenda = 44.90;
            _Produtos.Add(produto);

            produto = new Produto();
            produto.CodigoBarras = "7890000000222";
            produto.NomeProduto = "Metallica - Black Album";
            produto.Categoria = "CDs";
            produto.DataCadastro = new DateTime(2012, 09, 10);
            produto.QtdEstoque = 45;
            produto.PrecoVenda = 39.95;
            _Produtos.Add(produto);

            produto = new Produto();
            produto.CodigoBarras = "7890000000333";
            produto.NomeProduto = "Invictus";
            produto.Categoria = "DVDs";
            produto.DataCadastro = new DateTime(2012, 09, 03);
            produto.QtdEstoque = 7;
            produto.PrecoVenda = 16.90;
            _Produtos.Add(produto);

            produto = new Produto();
            produto.CodigoBarras = "7890000000444";
            produto.NomeProduto = "Uma Mente Brilhante";
            produto.Categoria = "DVDs";
            produto.DataCadastro = new DateTime(2012, 08, 20);
            produto.QtdEstoque = 18;
            produto.PrecoVenda = 29.90;
            _Produtos.Add(produto);

            produto = new Produto();
            produto.CodigoBarras = "7890000000555";
            produto.NomeProduto = "Trilogia Senhor dos Anéis";
            produto.Categoria = "Blu-Ray";
            produto.DataCadastro = new DateTime(2012, 08, 13);
            produto.QtdEstoque = 34;
            produto.PrecoVenda = 129.90;
            _Produtos.Add(produto);

            produto = new Produto();
            produto.CodigoBarras = "7890000000666";
            produto.NomeProduto = "Box Star Wars - 6 episódios";
            produto.Categoria = "Blu-Ray";
            produto.DataCadastro = new DateTime(2012, 08, 06);
            produto.QtdEstoque = 55;
            produto.PrecoVenda = 299.90;
            _Produtos.Add(produto);

            produto = new Produto();
            produto.CodigoBarras = "7890000000777";
            produto.NomeProduto = "A Arte da Guerra";
            produto.Categoria = "Livros";
            produto.DataCadastro = new DateTime(2012, 07, 27);
            produto.QtdEstoque = 10;
            produto.PrecoVenda = 10.00;
            _Produtos.Add(produto);

            produto = new Produto();
            produto.CodigoBarras = "7890000000888";
            produto.NomeProduto = "Transformando Suor em Ouro";
            produto.Categoria = "Livros";
            produto.DataCadastro = new DateTime(2012, 07, 20);
            produto.QtdEstoque = 17;
            produto.PrecoVenda = 24.90;
            _Produtos.Add(produto);

            // Devolve a coleção de objetos como resultado
            return _Produtos;
        }
    }
}

Criando a planilha que será utilizada como modelo

Chega agora o momento de se montar o arquivo .xlsx que funcionará como modelo/template, servindo assim de base para a geração de planilhas contendo informações do catálogo de produtos.

A Figura 4 exibe uma aparência possível para este documento. Notam-se no mesmo as seguintes características:

  • A presença de uma única planilha chamada “Catálogo”;
  • Duas linhas de cabeçalho, com a segunda destas contendo os títulos de cada coluna existente;
  • Uma terceira linha em que constarão os dados de produtos. Neste último caso, foram aplicados formatos para os diferentes campos, de maneira que as configurações deste conjunto de células possam ser clonadas. Com esta técnica evita-se toda uma codificação extensa envolvendo a aplicação de máscaras e características visuais, algo que seria obrigatório se uma planilha fosse construída do zero.
Criando o arquivo de modelo Template_CatalogoProdutos.xlsx

Figura 4: Criando o arquivo de modelo Template_CatalogoProdutos.xlsx

Além disso, será preciso definir ainda um parâmetro (“TemplateArquivoExcelProdutos”) dentro da seção appSettings, no arquivo app.config do projeto. Nesta configuração será especificado o caminho completo do documento que funcionará como modelo (Listagem 3).

Listagem 3: Arquivo app.config



  
    
  

Classes do Open XML empregadas na manipulação de planilhas .xlsx

Antes de prosseguir com a construção das classes utilizadas na geração da planilha de catálogo de produtos, serão apresentados alguns dos tipos do Open XML SDK comumente utilizados na manipulação de arquivos do Excel. Na Tabela 1 estão listadas as diferentes estruturas desse framework e que serão empregadas no projeto de exemplo.


TipoFinalidade
SpreadsheetDocumentClasse que representa um arquivo do Excel, sendo o ponto de partida para a leitura/gravação de informações em documentos deste tipo.
SharedStringTablePartElemento que encapsula as diferentes estruturas que definem uma SharedStringTable.
SharedStringTableConsiderando um documento do Excel 2007-2010, existirá dentro do mesmo apenas uma estrutura deste tipo e que é conhecida como SharedStringTable. Trata-se de um tipo de tabela especial, em que as diferentes strings utilizadas pelas planilhas de um documento são agrupadas. Suprimir a repetição de tais valores não apenas permite diminuir o tamanho final de um arquivo, como pode resultar ainda em uma melhor performance (por evitar operações repetitivas de leitura/gravação de valores redundantes).
SharedStringItemClasse que corresponde a uma string dentro de uma SharedStringTable.
InlineStringEstrutura que permite a gravação direta de texto em células, dispensando com isso o uso da SharedStringTable. Pode ser uma boa opção em sequências extensas de texto, quando não haverá a repetição de valores e, consequentemente, a necessidade de se evitarem redundâncias.
SheetElemento-base a partir do qual será possível se obter a representação de uma planilha que conste em um arquivo do Excel.
WorksheetPartElemento em que estão contidas as estruturas que compõem um item conhecido como Worksheet.
WorksheetRepresentação propriamente dita de uma planilha em um documento do Excel.
SheetDataEstruturas em que constam os dados de uma planilha.
RowRepresentação de uma linha dentro de uma planilha.
CellEstrutura que define uma célula pertencente a uma linha.
CellValueTipo que corresponde ao valor associado a uma célula.
CellValuesEnumeration que indica o tipo de dado associado a uma célula. Alguns dos valores possíveis para o mesmo são Number, SharedString, InlineString e Date.
TextClasse utilizada para o preenchimento de strings de texto em células de uma planilha.

Tabela 1: Tipos do Open XML SDK empregados na manipulação de arquivos do Excel

Com exceção da classe SharedStringTablePart que se encontra definida em DocumentFormat.OpenXml.Packaging, todos os tipos mencionados na Tabela 1 pertencem ao namespace DocumentFormat.OpenXml.Spreadsheet.

Implementação das classes voltadas à geração das planilhas .xlsx

SharedStringsHelper (Listagem 4) é o primeiro dos tipos que precisará ser criado para a manipulação de planilhas com dados de produtos. Nesta classe estática foi definida a operação GetIndexSharedString, a qual receberá como parâmetros um valor string e uma instância da tipo SharedStringTablePart, a fim de com isto devolver como resultado um índice numérico. Este último dado equivale à posição de um texto dentro de uma SharedStringTable, devendo ser associado a uma célula que contenha texto e que venha a depender desse mecanismo.

Quanto ao funcionamento do método estático GetIndexSharedString, devem ser destacados os seguintes pontos:

  • Caso um objeto do tipo SharedStringTable não esteja associado ao parâmetro “shareStringPart”, uma nova instância baseada em tal classe é criada e associada a esta referência;
  • Um loop é executado sobre os itens existentes no objeto do tipo SharedStringTable. Se o valor da propriedade InnerText de uma instância da classe SharedStringItem corresponder ao texto que consta em “valor”, o índice desta string é retornado como resultado da execução do método GetIndexSharedString;
  • Se a string pesquisada não tiver sido encontrada no objeto SharedStringTable, uma nova instância do tipo Text é gerada e adicionada a essa estrutura. O índice atual (vinculado à variável “i”) é então devolvido como retorno da operação GetIndexSharedString.

Listagem 4: Classe SharedStringsHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace TesteGeracaoArquivoExcel
{
    public static class SharedStringsHelper
    {
        public static int GetIndexSharedString(
            string valor,
            SharedStringTablePart shareStringPart)
        {
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable =
                    new SharedStringTable();
            }

            int i = 0;
            foreach (SharedStringItem item in shareStringPart
                .SharedStringTable.Elements())
            {
                if (item.InnerText == valor)
                {
                    return i;
                }
                i++;
            }

            shareStringPart.SharedStringTable.AppendChild(
                new SharedStringItem(
                    new DocumentFormat.OpenXml
                        .Spreadsheet.Text(valor)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }
    }
}

Na Listagem 5 está a implementação da classe estática WorkbookPartHelper, em que constam as definições das operações GetSharedStringTablePart e GetWorksheet.

No caso do método GetSharedStringTablePart, inicialmente é verificada a existência de um objeto do tipo SharedStringTablePart e que esteja vinculado ao parâmetro "workbookPart" (acionando para isto a operação GetPartsOfType). Caso essa instância seja encontrada, a mesma é devolvida como resultado da execução de GetSharedStringTablePart. Do contrário, o método genérico AddNewPart é invocado a partir da referência “workbookPart”, de forma que uma nova instância de SharedStringTablePart seja associada a essa estrutura; esta referência é então retornada à construção que acionou a operação GetSharedStringTablePart.

Já o método GetWorksheet recebe como parâmetros uma instância do tipo WorkbookPart, além de uma string com o nome de uma planilha que se deseja encontrar. Através do método Descendants da propriedade Workbook é buscada uma instância cujo nome corresponda à planilha procurada; com esta técnica torna-se possível a obtenção do identificador desta estrutura (variável "relationshipId"). Por fim, o método GetPartById é invocado, com a propriedade Worksheeet do objeto resultante sendo devolvida como retorno da operação GetWorksheet.

Listagem 5: Classe WorkbookPartHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace TesteGeracaoArquivoExcel
{
    public static class WorkbookPartHelper
    {
        public static SharedStringTablePart GetSharedStringTablePart(
            WorkbookPart workbookPart)
        {
            SharedStringTablePart shareStringPart;
            if (workbookPart
                .GetPartsOfType()
                .Count() > 0)
            {
                shareStringPart = workbookPart
                    .GetPartsOfType().First();
            }
            else
            {
                shareStringPart = workbookPart
                    .AddNewPart();
            }

            return shareStringPart;
        }

        public static Worksheet GetWorksheet(
            WorkbookPart workbookPart,
            string nomePlanilha)
        {
            string relationshipId = workbookPart
                .Workbook.Descendants()
                .Where(s => s.Name == nomePlanilha).First().Id;
            WorksheetPart worksheetPart = (WorksheetPart)workbookPart
                .GetPartById(relationshipId);
            return worksheetPart.Worksheet;
        }        
    }
}

A classe estática SheetDataHelper (Listagem 6) será empregada em operações realizadas sobre os dados de uma planilha (tais procedimentos partem sempre de instâncias do tipo SheetData). A seguir estão descritos os diversos métodos definidos em SheetDataHelper.

A operação GetRow recebe como parâmetro um instância do tipo SheetData, além de um número (“rowIndex”) que indica a linha (objeto da classe Row) que se está buscando. Essa última estrutura será obtida acessando o método genérico Elements de SheetData, com uma expressão lambda filtrando a referência cuja propriedade coincide com o valor do parâmetro “rowIndex”.

Baseando-se numa string que representa a coluna e uma instância do tipo Row, a operação GetCell devolverá como resultado uma referência da classe Cell que corresponda à celula esperada. Objetos deste tipo podem ser obtidos por intermédio do método Elements da classe Row, comparando para isto o valor da propriedade CellReference com a possição (coluna + linha) da célula desejada.

O método CloneRow receberá como parâmetros uma instância da classe Row, além de um número que indica a posição do resultado da clonagem desta linha na planilha considerada. O processamento desta operação envolve, primeiramente, uma chamada ao método CloneNode (o parâmetro com o valor “true” indica que todos os elementos-filhos serão copiados). Um loop então percorrerá todas as células da linha em questão, com a nova posição (coluna + linha) sendo atribuída à propriedade CellReference. Conforme já mencionado, esta abordagem evitará a escrita de numerosas e intrincadas instruções voltadas à formatação (aparência e máscaras) das diferentes células.

Existirão também em SheetDataHelper operações utilizadas para a atribuição de valores a células de uma planilha: esta será justamente a função dos métotos SetValorInteiroCelula, SetValorDataHoraCelula, SetValorNumericoCelula e SetValorTextoCelula.

Todas essas operações obtêm a célula a ser alterada por meio de um parâmetro (“célula”) que identifica a coluna respondente, além de uma instância do tipo Row que aponta para a linha que se estará manipulando. Em seguida o método GetCell é então acionado, com o resultado sendo atribuído a uma variável de nome “celula” (do tipo Cell). A partir daqui, ligeiras variações ocorrerão nas instruções que definem tais operações (em virtude do tipo de dado associado a cada uma destas).

O método SetValorInteiroCelula cria uma nova instância do tipo CellValue, repassando ao construtor desta uma string que representa um número inteiro. Essa referência é então associada a uma propriedade também chamada CellValue no objeto “celula”.

As operações SetValorDataHoraCelula e SetValorNumericoCelula são bastante similares a SetValorInteiroCelula.

No caso específico de SetValorDataHoraCelula, o valor DateTime precisará ser convertido via método ToOADate. Este último transforma uma data em um número do tipo double, considerando para isto um formato que é próprio do pacote Office; o valor double é depois convertido mais uma vez, criando-se uma string que será atribuída à propriedade CellValue do objeto “celula”.

Já a operação SetValorNumericoCelula transformará um valor double em texto, com a diferença de que o mesmo deverá estar no formato norte-americano (isto é mais um pré-requisito ao se manipularem planilhas do Excel via código): um objeto CultureInfo será empregado nesta situação, de maneira que se acessem as configurações definidas na propriedade NumberFormat dessa instância.

Dentre estes quatro métodos, provavelmente SetValorTextoCelula seja o que envolve mais instruções na atribuição de um valor a uma célula. Inicialmente, a operação RemoveAllChildren será invocada duas vezes, com o intuito de remover instâncias dos tipos InlineString e CellValue do objeto para o qual “celula” aponta. Se o valor considerado não for vazio ou nulo, o índice que esse texto possui na estrutura SharedStringTable será retornado, para que possa assim ser atribuído à propriedade CellValue da célula em questão (a propriedade DataType também foi alterada, de forma que se aponte para um item do tipo SharedString).

Listagem 6: Classe SheetDataHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace TesteGeracaoArquivoExcel
{
    public static class SheetDataHelper
    {
        public static Row GetRow(
            SheetData sheetData, uint rowIndex)
        {
            return sheetData.Elements()
              .Where(r => r.RowIndex == rowIndex).First();
        }

        public static Cell GetCell(
            string coluna, Row row)
        {
            return row.Elements().Where(
                c => c.CellReference.Value ==
                     coluna + row.RowIndex).First();
        }

        public static Row CloneRow(
            Row rowBase, uint numeroProximaLinha)
        {
            Row row = (Row)(rowBase.CloneNode(true));
            row.RowIndex = numeroProximaLinha;

            string referencia;
            foreach (Cell cell in row.Elements())
            {
                referencia = cell.CellReference.ToString();
                cell.CellReference = referencia.Replace(
                    rowBase.RowIndex.ToString(),
                    numeroProximaLinha.ToString());
            }

            return row;
        }

        public static void SetValorInteiroCelula(
            string coluna, Row row, int valor)
        {
            Cell celula = GetCell(coluna, row);
            celula.CellValue = new CellValue(
                valor.ToString());
        }

        public static void SetValorDataHoraCelula(
            string coluna, Row row, DateTime valor)
        {
            Cell celula = GetCell(coluna, row);
            celula.CellValue = new CellValue(
                valor.ToOADate().ToString());
        }

        public static void SetValorNumericoCelula(
            string coluna, Row row, double valor)
        {
            Cell celula = GetCell(coluna, row);
            celula.CellValue = new CellValue(
                valor.ToString(
                    new CultureInfo("en-US").NumberFormat));
        }	

        public static void SetValorTextoCelula(
            string coluna, Row row, string valor,
            SharedStringTablePart shareStringPart)
        {
            Cell celula = GetCell(coluna, row);
            celula.RemoveAllChildren();
            celula.RemoveAllChildren();

            if (!String.IsNullOrWhiteSpace(valor))
            {
                int indexSharedString = SharedStringsHelper
                    .GetIndexSharedString(
                        valor, shareStringPart);
                celula.DataType = CellValues.SharedString;
                celula.CellValue = new CellValue(
                    indexSharedString.ToString());
            }
        }
    }
}

A Listagem 7 apresenta o código-fonte referente à classe ArquivoExcelProdutos. Este tipo faz uso (por vias diretas ou não) das outras estruturas já declaradas nesta seção.

O construtor de ArquivoExcelProdutos ao ser acionado receberá como parâmetro uma lista de objetos do tipo Produto. Essa coleção conterá os diferentes itens que formam o catálogo, com estes últimos sendo listados em planilhas criadas a partir da aplicação de testes.

Através do método público GerarArquivo ocorrerá a geração de planilhas contendo o catálogo de produtos. Esta operação recebe como parâmetro o nome do arquivo a ser preenchido com as informações de itens comercializáveis, contando com o seguinte funcionamento:

  • Inicialmente uma cópia da planilha que funciona como template é realizada, invocando-se para isto o método Copy da classe File (namespace System.IO);

  • Uma instância da classe SpreadsheetDocument é então criada, fornecendo-se como parâmetros ao construtor o nome do arquivo que receberá as informações de produtos, bem como uma indicação de que o acesso a essa estrutura permitirá também a edição da mesma;
  • Com a instância ao tipo SpreadsheetDocument associada à referência de nome “planilha”, serão criadas duas variáveis. A primeira armazenará uma instância da classe WorkbookPart, ao passo que a segunda um objeto do tipo SharedStringTablePart;

  • Finalmente, é efetuada uma chamada à operação privada PreencherInformacoesProdutos, fornecendo como parâmetro à mesma os objetos armazenados pelas variáveis “workbookPart” e “shareStringPart”.

Quanto à implementação do método PreencherInformacoesProdutos, é importante destacar:

  • Primeiramente foram obtidas instâncias das classes Worksheet e SheetData, as quais irão servir de base para o preenchimento de dados na planilha que se está criando;
  • Em seguida, é localizado o objeto Row que corresponde à linha com toda a formatação a ser adotada para os diferentes registros de produtos. Este conjunto de células precisará ainda ser removido (via método Remove), visto que sua existência é justificada apenas em virtude da necessidade de se criarem outros objetos Row de forma mais simplificada;
  • Após a remoção da linha que contém a formatação da planilha, um loop foreach é executado sobre a coleção de instâncias da classe Produto. Cada um dos objetos desta lista corresponderá a uma nova linha na planilha que se está montando;
  • A inclusão de uma nova linha envolve a clonagem da instância associada à variável “rowBaseFormatacao”, além da utilização de métodos da classe SheetDataHelper para o preenchimento de valores neste conjunto de células (operações SetValorTextoCelula, SetValorDataHoraCelula, SetValorInteiroCelula e SetValorNumericoCelula);
  • Após o loop foreach, o método Save é invocado sobre a instância do tipo Worksheet (variável worksheetCaalogo). Com isto as mudanças efetuadas durante a execução da operação PreencherInformacoesProdutos serão efetivadas.

Listagem 7: Classe ArquivoExcelProdutos

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Configuration;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace TesteGeracaoArquivoExcel
{
    public class ArquivoExcelProdutos
    {
        private List _produtos;

        public ArquivoExcelProdutos(List produtos)
        {
            _produtos = produtos;
        }

        public void GerarArquivo(string nomeArquivo)
        {
            File.Copy(ConfigurationManager
                .AppSettings["TemplateArquivoExcelProdutos"], 
                nomeArquivo, true);

            using (SpreadsheetDocument planilha =
                SpreadsheetDocument.Open(nomeArquivo, true))
            {
                WorkbookPart workbookPart = planilha.WorkbookPart;
                SharedStringTablePart shareStringPart =
                    WorkbookPartHelper
                        .GetSharedStringTablePart(workbookPart);

                PreencherInformacoesProdutos(
                    workbookPart, shareStringPart);
            }
        }

        private void PreencherInformacoesProdutos(
            WorkbookPart workbookPart,
            SharedStringTablePart shareStringPart)
        {
            Worksheet worksheetCatalogo =
                WorkbookPartHelper.GetWorksheet(
                    workbookPart, "Catálogo");
            SheetData sheetDataCatalogo = worksheetCatalogo
                .GetFirstChild();

            Row rowBaseFormatacao =  SheetDataHelper
                .GetRow(sheetDataCatalogo, 3);

            rowBaseFormatacao.Remove();

            Row rowInformacoesCatalogo;
            uint numeroProximaLinha = 3;
            foreach (Produto produto in _produtos)
            {
                rowInformacoesCatalogo = SheetDataHelper.CloneRow(
                    rowBaseFormatacao, numeroProximaLinha);

                SheetDataHelper.SetValorTextoCelula(
                    "A", rowInformacoesCatalogo,
                    produto.CodigoBarras,
                    shareStringPart);
                SheetDataHelper.SetValorTextoCelula(
                    "B", rowInformacoesCatalogo,
                    produto.NomeProduto,
                    shareStringPart);
                SheetDataHelper.SetValorTextoCelula(
                    "C", rowInformacoesCatalogo,
                    produto.Categoria,
                    shareStringPart);
                SheetDataHelper.SetValorDataHoraCelula(
                    "D", rowInformacoesCatalogo,
                    produto.DataCadastro);
                SheetDataHelper.SetValorInteiroCelula(
                    "E", rowInformacoesCatalogo,
                    produto.QtdEstoque);
                SheetDataHelper.SetValorNumericoCelula(
                    "F", rowInformacoesCatalogo,
                     produto.PrecoVenda);

                sheetDataCatalogo.Append(rowInformacoesCatalogo);
                numeroProximaLinha++;
            }

            worksheetCatalogo.Save();
        }
    }
}

Implementando o formulário para a geração de planilhas .xlsx

A fim de concluir a codificação da aplicação de exemplo, chega agora o momento de se proceder com a implementação do formulário a partir do qual será possível se gerar uma planilha em que conste o catálogo de produtos comercializáveis.

O primeiro dos ajustes que será efetuado consiste em renomear o formulário Form1, de maneira que a classe que representa o mesmo passe a se chamar “FormPrincipal”.

Na sequência, serão adicionados dois controles do tipo Button (“btnGerarCatalogo” e “btnSair”), além de um componente do tipo SaveFileDialog (“saveFileDialogCatalogo”). A Tabela 2 lista algumas das propriedades que estarão sendo alteradas em FormPrincipal; para efeitos de simplificação, ajustes relativos ao tamanho e ao posicionamento de controles foram omitidos desta seção.

ControlePropriedadeValor
FormPrincipalTextCatálogo de Produtos - Teste de utilização do Open XML SDK
btnGerarCatalogoTextGerar &Catálogo de Produtos
btnSair
Text&Sair
saveFileDialogCatalogoFilterExcel 2007-2010 (*.xlsx)|*.xlsx

Tabela 2: Valores de algumas ropriedades de controles do formulário FormPrincipal

Já na Listagem 8 está o código-fonte que implementa as funcionalidades de FormPrincipal. É possível observar no evento btnGerarCatalogo_Click o uso da classe ArquivoExcelProdutos: será por meio dessa estrutura que acontecerá a geração do arquivo .xlsx contendo as informações do catálogo de produtos.

Listagem 8: Classe FormPrincipal

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TesteGeracaoArquivoExcel
{
    public partial class FormPrincipal : Form
    {
        public FormPrincipal()
        {
            InitializeComponent();
        }

        private void btnGerarCatalogo_Click(
            object sender, EventArgs e)
        {
            if (saveFileDialogCatalogo.ShowDialog() ==
                DialogResult.OK)
            {
                ArquivoExcelProdutos arq = new ArquivoExcelProdutos(
                    CatalogoProdutos.ObterCatalogo());
                arq.GerarArquivo(saveFileDialogCatalogo.FileName);
                MessageBox.Show("O arquivo " +
                    saveFileDialogCatalogo.FileName +
                    " foi gerado com sucesso!");
            }
        }

        private void btnSair_Click(
            object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

Testando a aplicação de exemplo

Ao se iniciar a execução do projeto implementado neste artigo, será apresentada uma tela como a que consta na Figura 5.

Tela inicial da aplicação TesteGeracaoArquivoExcel

Figura 5: Tela inicial da aplicação TesteGeracaoArquivoExcel

Acionando a opção “Gerar Catálogo de Produtos”, aparecerá uma tela similar à da Figura 6. Dentro desta janela deverá ser especificado o caminho e o nome do arquivo .xlsx que conterá o catálogo de produtos. Ao se confirmar a geração da planilha (Figura 7), o documento correspondente terá sido salvo no diretório selecionado (Figura 8).

Salvando o arquivo .xlsx

Figura 6: Salvando o arquivo .xlsx

Confirmação de que o arquivo .xlsx foi gerado

Figura 7: Confirmação de que o arquivo .xlsx foi gerado

Diretório em que foi salvo o arquivo .xlsx

Figura 8: Diretório em que foi salvo o arquivo .xlsx

Abrindo o documento será possível constatar que os dados de produtos foram inseridos no mesmo, respeitando para isto a formatação definida anteriormente no arquivo que funcionou como template (Figura 9).

Arquivo .xlsx com informações do catálogo de produtos

Figura 9: Arquivo .xlsx com informações do catálogo de produtos

Conclusão

Conforme demonstrado ao longo deste artigo, a geração de um documento .xlsx pode ser simplificada consideravelmente a partir da utilização de um arquivo já existente e que funcione como template. Nessa estrutura podem ser aplicadas todas as formatações necessárias, competindo ao desenvolvedor apenas codificar as instruções que irão alimentar a planilha resultante. Trata-se, portanto, de uma alternativa bastante flexível, além de possibilitar uma economia significativa de esforços na implementação de funcionalidades desse gênero.

Isto não significa que o Open XML SDK seja incapaz de realizar tal tarefa. Este framework é bastante abrangente e oferece total suporte para a construção de planilhas sofisticadas. Contudo, se este procedimento for realizado do zero, a probabilidade de se obter um código relativamente extenso é grande, tornando mais complexas atividades futuras de manutenção.


 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Ficou com alguma dúvida?