Exportando dados para Excel com ADO

Agora que postei um pouco da teoria básica sobre o modelo de classes do ADO, posso cumprir a promessa que fiz de mostrar interação entre o Excel e o ADO através de script, populando uma planilha com os dados trazidos por uma query num banco de dados. Para ver como criar a planilha em si, consulte o post Criando uma planilha através de script já que neste eu vou focar mais a parte do uso do ADO.

De início, vou criar uma conexão com o banco de dados SQL Server pois esse é o banco mais comum entre os Clientes da ABC71. Veja o código usando VBScript:

Dim Cnxn, strCnxn, rs, query
' Conectar ao banco SQL
SetCnxn = CreateObject("ADODB.Connection")
strCnxn = "Provider=SQLOLEDB.1;Data Source=ABCVMS;Initial Catalog=DBTP;Locale Identifier=1046;"
Cnxn.Open strCnxn, "gustavo", ""
Cnxn.Execute ("set language us_english ")
Cnxn.Execute ("set dateformat dmy ")

O comando CreateObject cria uma instância do objeto "ADODB.Connection", isto é, aloca a memória necessária para que possa usar o objeto de conexão. O comando Open que vem logo em seguida estabelece a conexão com o banco de dados. Passo a ele 3 valores: a string de conexão, o nome do usuário do banco de dados e a senha deste usuário. O exemplo traz uma string de conexão para o MS SQL Server; para saber como montar essa string para outros bancos, clique aqui. Os dois comandos Execute que encerram o trecho de código configuram a linguagem e o formato de datas retornados pelo SQL Server.

Já temos a conexão, podemos então submeter uma query no banco de dados:

query = "SELECT * FROM TPUF"
Set rs = Cnxn.Execute (query)
Set folha = objWorkBook.WorkSheets (1)
for j = 0 to rs.Fields.Count - 1
folha.Cells (1,j+2) = rs.Fields.Item(j).Name
next

' Configura o cabeçalho
Set lRng = folha.Range (folha.Cells (1,1), folha.Cells (1,j+1))
lRng.Interior.Color = RGB(240,240,127)
lRng.Interior.Pattern = 1 'xlPatternSolid
lRng.Font.Name = "Tahoma"
lRng.Font.Bold = True
lRng.Font.Size = 11

Neste trecho do código, o comando Execute da conexão submete um SELECT e obtem um Recordset com os registros e campos encontrados. As definições dos campos podem ser consultadas na propriedade Fields, que é uma lista. Através do laço for, esta lista de campos é percorrida e o nome de cada campo é colocado numa coluna da primeira linha da folha de trabalho do Excel. Após o laço for, a coluna com os nomes dos campos é formatada de modo a representar um cabeçalho para os valores a serem recuperados dos registros.

Falta, então, percorrer os registros encontrados e lançar os valores sob a coluna correta na folha da planilha:

i = 2
Do While Not rs.EOF
folha.Cells (i,1) = i - 1 'Número do registro
for j = 0 to rs.Fields.Count - 1
folha.Cells (i,j+2) = rs.Fields.Item(j).Value
next
i = i + 1
rs.MoveNext
Loop

O laço Do While percorre todos os registros retornados pela query, testando se o fim foi atingido através da propriedade EOF (fim de arquivo). O laço for interno percorre a lista de campos do registro atual, lançando o valor de cada um na coluna certa da planilha. Cada registro ocupa uma linha na folha da planilha, sendo que a variável i determina qual é o número da linha. Ela é iniciada com a segunda linha já que a primeira foi preenchida com o cabeçalho.

Como arremate, inclui mais um laço para formatar cada coluna da folha com base no tipo de dado especificado na definição do campo:

for j = 0 to rs.Fields.Count - 1
Set lRng = folha.Range (folha.Cells (1,j+2), folha.Cells (i,j+2))

Select Case rs.Fields.Item(j).Type
Case 4, 5, 6, 14, 131, 139 ' Números c/ decimais
lRng.ColumnWidth = rs.Fields.Item(j).DefinedSize
lRng.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
lRng.HorizontalAlignment = -4152 ' xlRight'

Case 2, 3, 16, 17, 18, 19, 20, 21 ' Números Inteiros
lRng.ColumnWidth = rs.Fields.Item(j).DefinedSize * 4
lRng.NumberFormat = "#,##0_);[Red](#,##0)"
lRng.HorizontalAlignment = -4152 ' xlRight'

Case 7, 64, 133, 135 ' Datas
lRng.ColumnWidth = 12
lRng.NumberFormat = "m/d/yyyy"
lRng.HorizontalAlignment = -4108 ' xlCenter'

Case Else ' Textos e outros
lRng.ColumnWidth = rs.Fields.Item(j).DefinedSize + 3
lRng.NumberFormat = "General"
lRng.HorizontalAlignment = -4131 ' xlLeft'
End Select
next

Neste ponto, a variável i armazena o número da última linha que foi alimentada com valores dos registros. Então, percorro novamente a definição de todos os campos, obtenho a propriedade Type de cada um e determino a formatação mais conveniente. Os tipos de dados são definidos por um enumerado do ADO chamado DataTypeEnum mas nem todos foram incluídos no exemplo. Consulte a documentação desse enumerado para mais detalhes.

Mais Informações
Modelo de Classes do ADO, Referência de Classes do Excel, Criando uma planilha através de script, Formatação de planilhas através de script, Download do script de exemplo.

Modelo de objetos para acesso de dados com ADO

Já faz algum tempo que os IDEs - Ambientes Integrados de Desenvolvimento - vêm facilitando a vida de quem cria programas com acesso a banco de dados. Ambientes como o Delphi e o Visual Studio permitem que se conecte a um banco e se crie telas para extrair ou dar manutenção em dados de forma rápida e intuitiva, bastando alguns cliques do mouse.

Essa abordagem permite criar aplicações rapidamente mas tem alguns problemas. Primeiro, a ferramenta acaba fazendo tudo pra você e o conhecimento para usar a tecnologia acaba se restringindo aos cliques do mouse. Depois, com a aplicação montada apenas visualmente, pode ser bastante trabalhoso caso seja necessário trocar a tecnologia de acesso ao banco. Na ABC71, por exemplo, trocamos o uso do BDE pelo ADO e a troca foi muito mais transparente porque usamos classes para persistência dos dados ao invés dos recursos visuais.

Então, se quisermos montar classes para persistência de dados usando ADO, teremos que ir um pouco mais fundo para desvendar a tecnologia. Esse conhecimento poderá então ser usado em outras situações, como em automatização do acesso a banco de dados através de scripts.

Trabalhar diretamente com a API do ADO é relativamente fácil pois há poucas classes, apenas duas para executar o básico:

Connection: Use essa classe para estabelecer conexão com um banco de dados. Antes de se conectar, é possível configurar:
a) Tempos de espera (timeout) para a conexão e para a execução de comandos;
b) O nível de isolamento das transações (isto é, se a conexão conseguirá ou não enxergar dados que ainda não sofreram Commit);
c) O local padrão para os cursores (se no próprio banco de dados ou se serão tratados no Client)
d) Parâmetros dependente do fabricante do banco de dados.

Para estabelecer uma conexão, use o método Open informando uma string de conexão. Essa "string" é um texto com parâmetros para determinar qual é o tipo do banco de dados, o endereço do servidor, quem é o usuário e sua senha, etc. Os parâmetros aceitos dependem do banco com o qual se quer conectar; no endereço http://www.connectionstrings.com há exemplos dessa string para uma ampla gama de banco de dados.

A classe Connection permite ainda controlar transações no banco de dados, através dos comandos BeginTrans, CommitTrans e RollbackTrans.

O método Execute permite submeter comandos diretamente no banco de dados, tais como UPDATE, INSERT e DELETE. No caso de comandos SELECT, um objeto do tipo RecordSet é retornado.

RecordSet: É uma coleção de registros obtidos no banco de dados. Ela permite navegar pelos registros (comando MoveNext, MoveFirst, MoveLast e MovePrevious) e trabalhar com as informações contidas neles. É importante observar que apenas um registro está posicionado por vez e que, dependendo do modo de edição em que este registro se encontra, você pode submeter alterações nos dados através do próprio Recordset. Use o método AddNew dessa classe para preparar a criação de um novo registro, Delete para removê-lo e Update para efetivar alterações (incluindo a inserção de novos registros).

Obs: quando obtido através do Execute, um RecordSet navega apenas adiante na coleção (não é permitido voltar) e os registros são read-only (não é possível alterar os dados).

Outras classes complementares ampliam as possibilidades do ADO. Por exemplo, para se obter uma coleção de registros que possam ser alterados (modo de edição "gravável'), use a classe Command, ajustando a propriedade ActiveConnection para que ela use uma conexão (Connection) que já esteja estabelecida. Um Command é mais flexível que o Execute da conexão pois pode trabalhar com queries parametrizadas e queries preparadas (para melhorar a performance), pode estabelecer que os registros retornados serão editáveis e a navegação entre os registros pode ir para frente e para trás (desde que o provedor para o banco de dados em questão tenha implementado esse recurso).

Ainda, o Recordset dá acesso a objetos do tipo Field, que representa cada um dos campos existentes no Recordset, fornecendo informações como o nome do campo, seu tipo de dado e o tamanho desse dado.

Pretendo incluir novos posts sobre esse assunto, mostrando exemplos de uso para vários dos recursos do ADO.

Mais Informações
Referência de Classes do ADO, Acessando arquivos texto com ADO

Lendo arquivos texto com ADO

Apesar do sucesso e da grande disseminação do formato XML, há ainda uma vasta gama de situações em que o uso de arquivos texto (TXT) é mais conveniente. Há um Cliente recente da ABC71, por exemplo, que está vindo de um sistema COBOL desenvolvido por eles mesmos. Esse Cliente terá que se integrar ao sistema da Nota Fiscal Eletrônica mas não haverá tempo hábil para implantar o Omega, que possui esta integração de forma nativa. A solução, neste caso, foi fazer com que o sistema COBOL gerasse arquivos no formato texto com campos de tamanho fixo para que pudéssemos colocar as informações no fluxo para a Receita.

Uma forma simples de realizar a leitura desses arquivos texto foi usar o driver ODBC para Arquivos Texto da Microsoft, que acredito ser distribuido com o Windows. Tendo um driver ODBC, fica fácil acessar os dados através de uma conexão ADO já que se usa mecanismos bem conhecidos de acesso a bancos de dados.

O conceito de uso de arquivos TXT como banco de dados é simples: todos os arquivos TXT colocados numa determinada pasta são considerados parte do banco, sendo que cada arquivo é tratado como uma tabela diferente desse banco. Nesses arquivos, cada linha corresponde a um registro. Para ligar tudo isso, é preciso criar um arquivo que descreve como os dados estão organizados em cada TXT; tal arquivo é apropriadamente nomeado schema.ini já que o que ele descreve é justamente o esquema do banco de dados.

O arquivo schema.ini é um arquivo texto comum, criado para ter uma seção para cada tabela. Assim, o nome da "tabela" vai entre um par abre/fecha colchetes numa linha e, nas linhas subsequentes, são colocadas configurações gerais para a "tabela" e uma lista com a definição dos "campos", com seus nomes e tipos de dado esperado. O quadro abaixo mostra um exemplo de schema.ini com duas tabelas. Explico em seguida o significado de cada parâmetro.

[mestre.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=0
DateTimeFormat=YYYYMMDD
CharacterSet=ANSI
Col1=EMPRESA Long width 4
Col2=FILIAL Long width 4
Col3=INFNFE_ID Char width 47
Col4=TOTAL Float width 19
Col5=DATA_ALTER Date width 8
[detalhes.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=0
DateTimeFormat=YYYYMMDD
CharacterSet=ANSI
Col1=INFNFE_ID Char width 47
Col2=SEQ_NF Long width 4
Col3=CODIGO Char width 60
Col4=QTDE Float width 19
Col5=VALOR Float width 19
Col6=DATA_ALTER Date width 8

Nesse exemplo, referenciei dois arquivos como tabelas: mestre.txt e detalhes.txt, cada uma descrita em sua própria seção e cada parâmetro inserido numa linha separada. O significado dos parâmetros é o seguinte:

• ColNameHeader indica se a primeira linha do arquivo texto especifica o nome das colunas. No meu caso, os arquivos têm apenas dados, razão pela qual informei False nas duas "tabelas".
• Format É o modo como os valores dos campos estão distribuídos nas tabelas. Informei FixedLength porque no meu caso cada campo terá uma quantidade fixa de caracteres em todas as linhas (registros) do arquivo. Esse parâmetro poderia ainda assumir os valores TabDelimited (onde o valor de cada campo seria separado por um caracter de tabulação), CSVDelimited (se ao invés do TAB o separador for uma Vírgula) ou Delimited (caso você queira estipular um separador diferente).
• MaxScanRows Esta configuração é usada somente quando se usa o gerenciador do ODBC do Windows para configurar o schema.ini. O número especificado nele determina quantos registros (linhas) o Gerenciador deve ler antes de tentar determinar os tipos de dados dos campos. Tentei usar o Gerenciador mas não fui muito feliz; preferi criar na mão ...
• DateTimeFormat diz qual é o formato das datas incluídas no arquivo. O valor YYYYMMDD significa que, no meu caso, as datas terão 4 dígitos para o ano, 2 para o mês e 2 para o dia, sem separadores.
• CharacterSet é o codepage para os caracteres a serem usados. Especifiquei o conjunto Ansi, que é o valor padrão e atende a maioria dos casos.

Todos esses parâmetros e outros que não usei são explicados no MSDN.

Em seguida, vêm os parâmetros começados com COL que descrevem as colunas (campos) da tabela TXT. A sintaxe é

Coln=NOME TIPO [COMPRIMENTO]

Onde:

• n é um número sequencial, isto é, o primeiro campo é Col1, o segundo Col2 e assim por diante.
• NOME é um nome único para o meu campo. Pode-se usar esses nomes em comandos SELECT para extrair informações do banco de dados.
• TIPO é um dos tipos de dados permitidos.
• [COMPRIMENTO] indica a extensão do campo em quantidade de caracteres. Para o formato de arquivo FixedLength, esse comprimento é obrigatório para todos os campos; nos demais formatos informe apenas para campos do tipo Char.

Para usar no ADO, crie uma conexão com uma Connection String apropriada, como no exemplo:

m_strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\PastaTxt;Extended Properties=""Text;HDR=No"""
m_Cnxn.Open strCnxn, "", ""

O caminho c:\PastaTxt é o local onde coloquei os arquivos TXT e o SCHEMA.INI. Veja que não foi necessário criar uma fonte de dados ODBC para se conectar! Para finalizar, você pode submeter queries a esse banco de dados usando o Execute da conexão:

Set m_ResultSet = m_Cnxn.Execute("select * from mestre.txt")

É interessante observar que foi informado o nome do arquivo TXT como se fosse um nome de tabela.

Mais Informações
Arquivo Schema.ini para o Driver de Arquivo Texto