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.