Excel x .NET Framework: gerando planilhas .xls sem o uso de Interop/COM

Por mais que mecanismos como os Web Services ganhem a cada dia mais espaço na integração entre sistemas, muitas companhias têm em arquivos como planilhas do Excel um importante meio para a transferência de informações. Atualmente, o formato mais empregado na geração de tais arquivos é o Office Open XML, padrão este introduzido a partir do Office 2007 e que combina tecnologias como compactação zip e XML.

A manipulação de arquivos no padrão Open XML também é suportada pela plataforma .NET, com isto acontecendo por meio do framework Open XML SDK (o qual é fornecido pela própria Microsoft). Maiores informações sobre este tecnologia podem ser encontradas no link:

http://msdn.microsoft.com/en-us/office/bb265236.aspx

Embora as versões mais recentes do pacote Office enfatizem a utilização deste novo padrão de arquivos, em muitos casos os desenvolvedores se verão às voltas com arquivos ainda no formato .xls (Excel 97-2003). Quando isto ocorre, a alternativa mais comum está no uso de funcionalidades de Interop para o acesso a planilhas deste tipo.

O mecanismo conhecido Interop combina recursos da plataforma .NET e de uma tecnologia conhecida como COM, tendo como objetivo principal possibilitar a comunicação entre softwares instalados no sistema operacional Windows e projetos criados a partir do Framework. Também de autoria da Microsoft, o padrão COM (sigla de "Component Object Model") é anterior ao surgimento do próprio .NET, estando baseado no consumo de bibliotecas (arquivos .dll) registradas em um computador e que expõem uma ou mais funcionalidades genéricas.

Qualquer aplicação .NET que manipule arquivos .xls via Interop dependerá, por sua vez, da presença de componentes Office no ambiente em que será implantada. Contudo, a instalação de tais recursos nem sempre será possível em um cenário real de produção, fato este que pode se transformar num grande empecilho.

Levando em consideração tais fatos, foi concebido um projeto conhecido como NPOI, o qual viabiliza a realização de operações de leitura/escrita em planilhas do Excel, sem que isto implique na obrigação de instalar recursos do pacote Office (dispensando assim o uso de Interop). Atualmente, a biblioteca NPOI (que é gratuita) encontra-se na versão 2.0 beta 1, com suporte à geração de arquivos nos formatos .xls, .xlsx (Excel 2007-2010) e .docx (Word 2007-2010). Maiores informações podem ser obtidas através do seguinte link:

http://npoi.codeplex.com/

O objetivo deste artigo é descrever a geração de arquivos .xls a partir das classes disponibilizadas pelo projeto NPOI. Para isto, será criada uma aplicação de testes que utiliza um arquivo do Excel 97-2003 como template; a partir de cópias deste último serão geradas novas planilhas, as quais conterão informações de um catálogo de produtos.

Criando a Aplicação de Testes

Com intuito de demonstrar a manipulação de planilhas .xls a partir da biblioteca NPOI, será criado um projeto do tipo Windows Forms Application chamado TesteExcelNPOI. O desenvolvimento desta aplicação foi efetuado por meio da utilização do Microsoft Visual Studio 2012 Professional, além do NPOI 2.0 beta 1 (este último poderá ser baixado a partir do link indicado anteriormente neste artigo).

Uma vez que o projeto TesteExcelNPOI tenha sido gerado, incluir neste último referências que apontem para as bibliotecas NPOI.dll (Figura 1) e System.Configuration.dll (Figura 2). A primeira destas dlls contempla os mecanismos de geração e leitura de arquivos disponibilizados pelo Open XML SDK. Já a segunda biblioteca será usada na manipulação de um arquivo de configurações, o qual contém definições utilizadas na execução da aplicação de testes.

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

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

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

O exemplo que está sendo desenvolvido neste arquivo tem por meta permitir a geração de planilhas contendo informações sobre um catálogo de produtos. Trata-se da adaptação de uma outra aplicação que desenvolvi anteriormente, em um artigo que abordava o framework Open XML SDK:

//www.devmedia.com.br/excel-e-open-xml-sdk-gerando-novas-planilhas-xlsx-a-partir-de-templates/25854

Para os diferentes itens comercializados foram levados em conta dados como 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 seu preço unitário de venda. A representação deste conjunto de informações será feita por meio da classe Produto, cuja implementação está detalhada na Listagem 1.

Listagem 1: Classe Produto

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

namespace TesteExcelNPOI
{
    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 definição do tipo CatalogoProdutos. Esta classe estática disponibiliza um método chamado ObterCatalogo, a partir do qual serão retornados todos os itens que se encontram no catálogo (instâncias baseadas no tipo Produto).

Conforme pode ser facilmente observado, o código que implementa a operação estática ObterCatalogo tem validade apenas para efeitos de simulação. Em um caso real, as informações correspondentes seriam geralmente obtidas a partir de uma base de dados (via mecanismos de acesso como o Entity Framework ou, até mesmo, a partir de estruturas mais básicas disponibilizadas pela tecnologia ADO.NET).

Listagem 2: Classe CatalogoProdutos

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

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

            // 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

Prosseguindo com a construção do projeto de exemplo, será montado agora o arquivo .xls que funcionará como modelo/template, a fim de permitir com isto a geração de planilhas contendo informações do catálogo de produtos.

A Figura 3 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;
• As demais linhas que corresponderão às informações do catálogo não foram preenchidas com dados. O único ajuste realizado aqui está no formato empregado para cada coluna. Os campos “Cód. Barras”, “Nome do Produto” e “Categoria” são do tipo texto; “Data Cadastro” conterá datas, baseando-se para isto no padrão dd/mm/aaaa; “Qtd. Estoque” será um valor numérico inteiro; já a coluna “Preço Venda” armazenará valor monetários com 2 casas decimais.

Figura 3: Criando o arquivo de modelo Template_CatalogoProdutos.xls

Será preciso também definir 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 template (Listagem 3).

Listagem 3: Arquivo app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0"
             sku=".NETFramework,Version=v4.5" />
    </startup>
  <appSettings>
    <add key="TemplateArquivoExcelProdutos"
         value="C:\Devmedia\Template_CatalogoProdutos.xls"/>
  </appSettings>
</configuration>

Classes da biblioteca NPOI empregadas na manipulação de planilhas .xls

Na Tabela 1 estão listados alguns tipos do framework NPOI comumente utilizados na manipulação de arquivos do Excel.

Tabela 1: Tipos de NPOI empregados na manipulação de arquivos do Excel

Com exceção da classe HSSFWorkbook que está definida em NPOI.HSSF.UserModel, todos os outros tipos mencionados na Tabela 1 pertencem ao namespace NPOI.SS.UserModel.

Implementação das classes para geração do catálogo

Na classe estática SheetHelper (Listagem 4) está sendo implementado o método GetCell. Esta operação exemplifica um tipo de estrutura conhecida como Extension Method, a qual permite adicionar novas funcionalidades a uma construção de código pré-existente (sem que isto implique na criação de interfaces ou classes derivadas do tipo original, no caso ISheet).

O método GetCell recebe como parâmetro uma instância da classe ISheet precedida pela palavra-chave “this” (isto é uma característica típica de um Extension Method), além de dois outros parâmetros que correspondem aos números que identificam uma linha e uma coluna dentro de uma planilha.

Quanto ao funcionamento da operação GetCell, é possível observar o seguinte:

• Os índices que referenciam linhas e colunas iniciam em zero. Por essa razão, ao se tentar manipular uma determina célula (combinação de linha + coluna) tais índices são calculados subtraindo-se uma unidade dos valores informados como parâmetro;
• Inicialmente é feita uma tentativa de obter uma instância da classe IRow, acionando-se para isto o método GetRow através do parâmetro “sheet”; caso seja retornado o valor null (indicativo de que uma linha ainda não foi gerada para aquela posição), a operação CreateRow é acionada;
• A partir da linha que foi obtida ou, mesmo, incluída no primeiro passo, é realizada uma chamada ao método GetCell (definido em IRow). Novamente, se o objeto (no caso uma instância do tipo ICell) não existir, uma operação específica será invocada com o objetivo de se criar tal estrutura. O objeto ICell correspondente será então devolvido como resultado da execução deste Extension Method.

Listagem 4: Classe SheetHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.SS.UserModel;

namespace TesteExcelNPOI
{
    public static class SheetHelper
    {
        public static ICell GetCell(
            this ISheet sheet, int linha, int coluna)
        {
            IRow row;
            int indiceLinha = linha - 1;
            row = sheet.GetRow(indiceLinha);
            if (row == null)
                row = sheet.CreateRow(indiceLinha);

            ICell cell;
            int indiceColuna = coluna - 1;
            cell = row.GetCell(indiceColuna);
            if (cell == null)
                cell = row.CreateCell(indiceColuna);

            return cell;
        }
    }
}

A Listagem 5 apresenta o código-fonte referente à classe ArquivoExcelProdutos. Esta última faz uso do tipo SheetHelper, o qual já foi especificado anteriormente nesta seção.

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

Através do método GerarArquivo ocorrerá a geração de arquivos contendo informações do catálogo de produtos. Esta operação recebe como parâmetro o nome do arquivo que será gerado, possuindo o seguinte funcionamento:

• Inicialmente é criada uma instância do tipo FileStream (namespace System.IO), por meio da leitura do template declarado no arquivo app.config;
• Uma nova instância da classe HSSFWorkbook é então gerada, repassando-se ao construtor da mesma o objeto FileStream criado anteriormente. A referência do tipo HSSFWorkbook está sendo associada ao atributo privado "_workbookCatalogo", de forma que possa ser utilizada posteriormente;
• Por fim, serão invocados os métodos PreencherInformacoesProdutos e FinalizarGravacaoArquivo, com o intuito de preencher uma planilha com os dados do catálogo e salvar o resultado disto em um novo arquivo .xls.

Quanto às instruções que implementam o método PreencherInformacoesProdutos, é possível destacar:

• Primeiramente foi obtida uma instância do tipo ISheet, a qual corresponde à planilha "Catálogo" existente dentro do arquivo definido como template;
• A planilha é então preenchida, utilizando-se para isto um loop foreach a fim de acessar cada uma das instâncias da classe Produto. Chamadas ao método GetCell possibilitam o acesso às diferentes células; a invocação do método SetCellValue (especificado em ICell) permite associar um valor a uma célula da planilha.

A operação FinalizarGravacaoArquivo parte de uma nova instância do tipo FileStream, acionando o método Write do objeto associado à variável "_workbookCatalogo" e efetivando com isso a gravação de um novo arquivo .xls.

Listagem 5: Classe ArquivoExcelProdutos

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace TesteExcelNPOI
{
    public class ArquivoExcelProdutos
    {
        private List<Produto> _produtos;
        private string _nomeArquivo;
        private HSSFWorkbook _workbookCatalogo;

        public ArquivoExcelProdutos(List<Produto> produtos)
        {
            this._produtos = produtos;
        }

        public void GerarArquivo(string nomeArquivo)
        {
            this._nomeArquivo = nomeArquivo;

            FileStream file = new FileStream(ConfigurationManager
                .AppSettings["TemplateArquivoExcelProdutos"],
                    FileMode.Open, FileAccess.Read);
            _workbookCatalogo = new HSSFWorkbook(file);

            PreencherInformacoesProdutos();
            FinalizarGravacaoArquivo();
        }

        private void PreencherInformacoesProdutos()
        {
            ISheet sheetCatalogo = 
                _workbookCatalogo.GetSheet("Catálogo");

            int numeroProximaLinha = 3;
            foreach (Produto produto in _produtos)
            {
                sheetCatalogo.GetCell(numeroProximaLinha, 1)
                    .SetCellValue(produto.CodigoBarras);
                sheetCatalogo.GetCell(numeroProximaLinha, 2)
                    .SetCellValue(produto.NomeProduto);
                sheetCatalogo.GetCell(numeroProximaLinha, 3)
                    .SetCellValue(produto.Categoria);
                sheetCatalogo.GetCell(numeroProximaLinha, 4)
                    .SetCellValue(produto.DataCadastro);
                sheetCatalogo.GetCell(numeroProximaLinha, 5)
                    .SetCellValue(produto.QtdEstoque);
                sheetCatalogo.GetCell(numeroProximaLinha, 6)
                    .SetCellValue(produto.PrecoVenda);
                numeroProximaLinha++;
            }
        }

        public void FinalizarGravacaoArquivo()
        {
            using (FileStream file = new FileStream(
                _nomeArquivo, FileMode.Create))
            {
                _workbookCatalogo.Write(file);
                file.Close();
            }
        }
    }
}

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

Concluindo este projeto de exemplo, será implementado agora o formulário a partir do qual ocorrerá a geração da planilha com o catálogo de produtos.

O primeiro dos ajustes necessários 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.

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

Na Listagem 6 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 .xls contendo as informações do catálogo.

Listagem 6: 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.Threading.Tasks;
using System.Windows.Forms;

namespace TesteExcelNPOI
{
    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

Iniciando a execução do projeto descrito neste artigo, será apresentada uma tela como a que consta na Figura 4.

Figura 4: Tela inicial da aplicação TesteExcelNPOI

Figura 4: Tela inicial da aplicação TesteExcelNPOI

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

Figura 5: Salvando o arquivo .xls

Figura 6: Confirmação de que o arquivo .xls foi gerado

Figura 6: Confirmação de que o arquivo .xls foi gerado

Figura 7: Diretório em que foi salvo o arquivo .xls

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

Figura 8: Arquivo .xls com informações do catálogo de produtos

Conclusão

A biblioteca NPOI pode se revelar como uma excelente alternativa em situações que exijam a manipulação de arquivos .xls. Por dispensar o uso de componentes COM (e todas as dificuldades decorrentes da configuração destes), este framework simplifica não apenas o processo de desenvolvimento, mas também diminui significativamente o trabalho de implantação em aplicações que dependam de recursos do Office 97-2003.

Espero que este artigo possa ser útil no seu dia-a-dia. Até uma próxima oportunidade!