Cuidados na utilização de Interop em aplicações do Integration Services

Embora mecanismos como os Web Services tenham atualmente uma importância fundamental na integração entre diferentes sistemas, arquivos-texto, planilhas do Excel e, até mesmo, bancos de dados do Access ainda são utilizados em larga escala por muitas organizações. O uso de um arquivo no intercâmbio de dados está normalmente associado ao processamento de um grande volume de transações, algo que se revelaria provavelmente impraticável se implementado através de um Web Service. Neste último caso, questões como o tráfego de um grande volume de informações em uma rede podem se constituir num fator limitante, degradando consideravelmente a performance do processo como um todo.

O SQL Server Integration Services (ou simplesmente Integration Services) representa a solução que a Microsoft disponibiliza para estes tipos de cenários. Trata-se de uma ferramenta que é parte integrante do SQL Server, com a implementação de funcionalidades a partir do mesmo envolvendo a criação de pacotes (packages).

Um package é uma estrutura extremamente flexível, já que possibilita a conexão a qualquer banco de dados que suporte os padrões OleDb e/ou ADO.NET (incluindo nisto planilhas do Excel e arquivos do Access), além de fornecer alternativas bastante flexíveis para a manipulação de arquivos-texto.

A implementação de estruturas baseadas em recursos do Integration Services acontece por meio do Visual Studio. Cada package criado contará com um conjunto de tarefas, as quais são modeladas seguindo um fluxo lógico de execução. A seguir são listados alguns dos tipos de tarefas de uso mais comum em projetos para o Integration Services:

• Execute SQL Task: permite a execução de comandos em uma base relacional. Dentre as operações possíveis estão a invocação de stored procedures e a realização de consultas que retornam um conjunto de registros;
• Foreach Loop Container: usado na iteração sobre um agrupamento de informações, como por exemplo um conjunto de registros. Um elemento deste tipo conta, normalmente, com uma ou mais tarefas em seu interior, com tais atividades correspondendo a operações que são executadas sobre cada item da coleção que se está manipulando
• Data Flow Task: engloba os diversos mecanismos empregados para a obtenção de dados a partir de uma fonte de origem, a provável transformação destes em informação útil, seguida da posterior carga em um repositório de destino. Este processo é conhecido popularmente pelo nome de ETL (sigla do inglês “Extraction, Transformation and Load”);
• Execute Package Task: tarefa utilizada quando um package precisa acionar outro pacote, a fim de executar as funcionalidades disponibilizadas por este último;
• Script Task: talvez um dos componentes de maior flexibilidade dentro de soluções voltadas ao Integration Services. Graças a este tipo de tarefa é possível a inserção de trechos de código escritos em .NET, viabilizando assim a realização de operações com o acesso a bancos de dados, validações empregando expressões regulares etc.

Considerando agora a importação via Integration Services de dados que constem em arquivos do Excel, alguns cuidados precisarão ser tomados com o intuito de garantir que esta atividade ocorra sem erros no processamento de informações:

• As planilhas que existirem num arquivo Excel devem contar com uma estrutura similar à de tabelas de um banco de dados. Isto implica na presença de uma primeira linha em que cada célula funciona como título das colunas a serem consideradas, ao passo que as linhas subsequentes correspondem a registros que serão carregados no repositório de destino (uma tabela de uma base relacional, por exemplo);
• Cabeçalhos com células mescladas e linhas contendo observações/legendas não poderão existir em tais planilhas, já que impedem que estas disponham de uma estrutura capaz de ser transposta para tabelas de banco de dados convencionais.

Não há como fugir destas recomendações para a importação de dados do Excel ao se empregar um componente do tipo Data Flow Task. No entanto, haverá situações (e isto não será tão incomum) em que se fará necessário processar planilhas que não estão em conformidade com essas diretrizes (cabeçalhos formados por várias linhas, células preenchidas em pontos que não deveriam conter informações etc.).

Quando isto acontecer, não restará aos desenvolvedores outra alternativa senão remover as células necessárias via componente Script Task, deixando assim as planilhas em uma disposição que possa ser compreendida por uma solução criada para o Integration Services. Se o arquivo em questão tiver a extensão .xls (formato do Excel 97-2003), será preciso então empregar os recursos de Interop na manipulação do mesmo.

Interop é um mecanismo que se baseia, dentro da plataforma .NET, na utilização de recursos da tecnologia COM para a comunicação entre softwares instalados no sistema operacional Windows e aplicações construídas a partir do Framework. Já o padrão COM (Component Object Model) foi criado pela própria Microsoft, tendo surgido inclusive antes do próprio .NET. O objetivo principal do mesmo é fornecer alternativas para a interoperabilidade entre sistemas, sendo seu uso bem comum em softwares que dependam de recursos oferecidos pelo pacote Office.

A Figura 1 apresenta um exemplo de planilha em que as células de cabeçalho precisariam ser removidas. Trata-se de um arquivo contendo informações de cargos e salários, o qual seria importado mensalmente numa aplicação hipotética responsável por cálculos de folha de pagamento.

Figura 1: Arquivo do Excel com cabeçalho que precisaria ser removido

Uma classe poderia então ser implementada a fim de acertar a planilha que foi demonstrada. Na Listagem 1 está o código-fonte para o tipo ArquivoExcel, sendo que o mesmo faz uso dos recursos de Interop disponibilizados pelo Microsoft Excel.

O construtor de ArquivoExcel recebe como informações o caminho do arquivo (parâmetro “arquivo”), além do nome da planilha que será acertada (parâmetro “nomePlanilhaMovimentacao”). Uma instância do aplicativo Microsoft Excel será disponibilizada em memória, com isto acontecendo através da criação de um objeto do tipo Application (namespace Microsoft.Office.Interop.Excel). Já o atributo de nome “_sheet” conterá uma referência para a classe Worksheet, a qual representa uma planilha dentro de um arquivo Excel.

É possível observar ainda em ArquivoExcel a presença dos seguintes métodos:

• EliminarLinha: operação que recebe como parâmetro um número de linha e, através do uso de uma instância do tipo Range, remove o conjunto de células correspondentes;
• FecharArquivo: encerra a edição do arquivo, liberando ainda a instância do Excel que se encontrava ativa na memória do computador em uso (por meio da invocação do método Quit do objeto do tipo Application).

OBSERVAÇÃO IMPORTANTE: o objetivo deste artigo não é estabelecer uma discussão profunda acerca do uso de Excel via Interop, mas sim demonstrar um possível caso de uso do mesmo, destacando ainda ao final quais cuidados precisarão ser tomados.

Listagem 1: Classe ArquivoExcel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;

namespace TesteInterop.Utils.Excel
{
    public class ArquivoExcel
    {
        private Application _app;
        private Workbooks _books;
        private Workbook _book;
        private Sheets _sheets;
        private Worksheet _sheet;

        public ArquivoExcel(
            string arquivo,
            string nomePlanilhaMovimentacao)
        {
            // Gera uma instância do Excel
            _app = new Application();

            if (_book == null)
            {
                _books = _app.Workbooks;
                _book = _books.Open(arquivo,
                    Type.Missing, Type.Missing, Type.Missing, 
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                _sheets = _book.Worksheets;
                _sheet = (Worksheet)_sheets[1];
                _sheet.Name = nomePlanilhaMovimentacao;
            }
        }

        public void EliminarLinha(int numeroLinha)
        {
            Range range = _sheet.get_Range(
                String.Format("A{0}:A{0}",
                numeroLinha.ToString()),
                Type.Missing);
            range.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
        }

        public void FecharArquivo()
        {
            // Fecha a edição do arquivo Excel
            _book.Save();
            _book.Close(false, Type.Missing, Type.Missing);

            // Encerra a instância do Excel
            _app.Quit();
        }
    }
}

Já dentro do componente Script Task empregado no acerto da planilha existiria um método Main similar àquele que consta na Listagem 2. Importante lembrar que esta operação (Main) estará presente sempre que uma tarefa Script Task for empregada. No exemplo serão removidas as três primeiras linhas da planilha de cargos e salários, produzindo-se como resultado uma planilha como a que é demonstrada na Figura 2.

Listagem 2: Exemplo de método Main que utiliza a classe ArquivoExcel

public void Main()
{
    string nomeArquivo;
    string nomePlanilha;
            
    // Outras operações
    ...

    ArquivoExcel arquivo = new ArquivoExcel(
        nomeArquivo, nomePlanilha);
    arquivo.EliminarLinha(1);
    arquivo.EliminarLinha(2);
    arquivo.EliminarLinha(3);
    arquivo.FecharArquivo();

    Dts.TaskResult = (int)ScriptResults.Success;
}

Figura 2: Arquivo do Excel após ajustes

A funcionalidade implementada na classe ArquivoExcel atende perfeitamente à necessidade de se formatar a planilha, deixando esta última estruturada numa forma que possa ser compreendida por um componente Data Flow Task do Integration Services. Entretanto, a execução sucessiva do package em que constar este tratamento acarretará em um efeito colateral: sucessivas instâncias do Microsoft Excel permanecerão em memória (Figura 3), não sendo descartadas ao término da execução do pacote.

Imaginando que a aplicação discutida fique hospedada em um servidor em que o package de importação é acionado diversas vezes, poderá se chegar a um momento no qual o uso de recursos do mesmo ficará comprometido (já que a memória empregada na manipulação de arquivos não é desalocada).

Figura 3: Instâncias do Excel não liberadas e que permaneceram em memória

A solução para este problema passará, forçosamente, pela utilização de chamadas a operações do Garbage Collector (GC), dispositivo este responsável pelo gerenciamento de memória em aplicações .NET. Na Listagem 3 está uma nova versão para a classe ArquivoExcel, com os ajustes necessários já realizados.

Merecem destaque nesta nova implementação do tipo ArquivoExcel os seguintes pontos:

• Está sendo implementada a interface IDisposable. Estruturas que manipulem recursos ditos como “não-gerenciáveis” (ou seja, que não são nativos do .NET Framework, justamente o caso da utilização de COM/Interop) devem derivar desta interface, a fim de que o Garbage Collector invoque o método Dispose; esta última operação representa o local em que os recursos serão liberados;
• Todas as instâncias de objetos do Excel têm o valor null associado às suas referências, com isto acontecendo ao término de sua utilização. Esse é o caso do método LiberarProcessoExcel, o qual é invocado pelas operações FecharArquivo e Dispose (esta última seria acionada num último instante pelo Garbage Collector, se algum erro em um momento qualquer impedir uma chamada a FecharArquivo);
• Dentro de LiberarProcessoExcel é executado ainda o método Quit sobre o objeto do tipo Application, além de chamadas a outro método de nome ReleaseComObject fornecendo como parâmetro cada uma das instâncias criadas anteriormente;
• A operação ReleaseComObject faz uso do método estático ReleaseComObject da classe Marshal (namespace System.Runtime.InteropServices), visando com isso desalocar a memória dos objetos COM que haviam sido instanciados.

Listagem 3: Classe ArquivoExcel após ajustes

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;

namespace TesteInterop.Utils.Excel
{
    public class ArquivoExcel : IDisposable
    {
        private Application _app;
        private Workbooks _books;
        private Workbook _book;
        private Sheets _sheets;
        private Worksheet _sheet;

        public ArquivoExcel(
            string arquivo,
            string nomePlanilhaMovimentacao)
        {
            // Gera uma instância do Excel
            _app = new Application();

            if (_book == null)
            {
                _books = _app.Workbooks;
                _book = _books.Open(arquivo,
                    Type.Missing, Type.Missing, Type.Missing, 
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                _sheets = _book.Worksheets;
                _sheet = (Worksheet)_sheets[1];
                _sheet.Name = nomePlanilhaMovimentacao;
            }
        }

        public void EliminarLinha(int numeroLinha)
        {
            Range range = _sheet.get_Range(
                String.Format("A{0}:A{0}",
                numeroLinha.ToString()),
                Type.Missing);
            range.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
            ReleaseComObject(range);
            range = null;
        }

        public void FecharArquivo()
        {
            // Fecha a edição do arquivo Excel
            _book.Save();
            _book.Close(false, Type.Missing, Type.Missing);
            LiberarProcessoExcel();
        }

        public void Dispose()
        {
            LiberarProcessoExcel();
        }

        private void LiberarProcessoExcel()
        {
            // Libera objetos relativos ao arquivo que
            // está sendo manipulado
            ReleaseComObject(_sheet);
            _sheet = null;

            ReleaseComObject(_sheets);
            _sheets = null;

            ReleaseComObject(_book);
            _book = null;

            ReleaseComObject(_books);
            _books = null;

            // Encerra a instância do Excel
            _app.Quit();
            ReleaseComObject(_app);
            _app = null;
        }

        private void ReleaseComObject(object o)
        {
            // Liberar instância do Interop
            if (o != null)
                System.Runtime.InteropServices
                    .Marshal.ReleaseComObject(o);
        }
    }
}

Por fim, o método Main do controle Script Task também necessita passar por uma readequação, conforme indicado na Listagem 4.

A manipulação da instância da classe ArquivoExcel está sendo feita dentro de um bloco try-finally. Ocorrendo ou não problemas, a instância que estaria associada à variável de nome “arquivo” é liberada (definindo-se null como valor da mesma). Em seguida são invocadas as seguintes operações da classe GC (a qual representa o Garbage Collector):

• Collect: força a execução do Garbage Collector, com a intenção de garantir a liberação de recursos;
• WaitForPendingFinalizers: suspende o processamento atual, aguardando a execução do mecanismo de Garbage Collector.

Listagem 4: Método Main após as adequações

public void Main()
{
    ArquivoExcel arquivo = null;

    string nomeArquivo;
    string nomePlanilha;
            
    // Outras operações
    ...
    
    try
    {
        ArquivoExcel arquivo = new ArquivoExcel(
            nomeArquivo, nomePlanilha);
        arquivo.EliminarLinha(1);
        arquivo.EliminarLinha(2);
        arquivo.EliminarLinha(3);
        arquivo.FecharArquivo();
    }
    finally
    {
        arquivo = null;

        // Libera objetos da memória e não mais em uso até este ponto
        System.GC.Collect();
        System.GC.WaitForPendingFinalizers();
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

Este artigo procurou abordar uma situação bastante específica, mas que requer cuidados ao se implementar uma solução que dependa do uso de Interop dentro do Integration Services. Espero que esse conteúdo possa auxiliá-lo em cenários como o apresentado aqui. Até uma próxima oportunidade!