Este método recebe como parâmetros um arquivo XLS em branco, que será utilizado para a conexão OLEDB funcionar (parâmetro pBlankWorkseetPath), o caminho da planilha que iremos criar a partir deste arquivo em branco e que efetivamente irá receber os dados do DataTable (parâmetro pDestWorksheetPath), o DataTable a ser exportado, e uma string de ordenação, que é do mesmo formato utilizado na propriedade Sort de um objeto DataView.

Primeiramente, copiamos o arquivo em branco para o arquivo no caminho completo (Caminho + Nome de Arquivo) indicado no parâmetro pDestWorksheetPath, utilizando o método estático Copy da classe File (no namespace System.IO), que pede como parâmetros na sobrecarga que utilizamos o nome do arquivo de origem, o nome do arquivo de destino e um indicador se o arquivo será sobre-escrito (de acordo com as novas regras ortográficas, acho! :P). Será esta cópia do arquivo que será utilizada na string de conexão OLEDB que iremos utilizar na linha abaixo.

Mas... não poderíamos simplesmente criar diretamente um arquivo do Excel? Sim, só que para isso precisaríamos instanciar um processo do Microsoft Excel  (via objeto OLE) através da nossa aplicação e aí sim criar a planilha a partir desta instância. E isto consome mais recursos de processamento e memória do que uma simples conexão OLEDB. E outro detalhe: o Excel TEM que estar instalado na máquina, no caso que vou mostrar isso não é necessário :-)

Em seguida, criamos a string de conexão da mesma forma do artigo anterior, utilizando o OLEDB como provedor de dados, e criamos uma instrução INSERT genérica, na variável sqlInsert, que receberá nos seus parâmetros de formatação o nome da tabela (veja, já colocamos o nome finalizado de cifrão - $ - e entre chaves), o nome dos campos a serem inseridos e os valores.

Com isto, inicializamos os objetos Connection e Command do ADO.NET, utilizando o provider OleDb.

O ideal é que o arquivo base tenha somente uma planilha, que é o mínimo possível em uma pasta de trabalho do Excel. Isto porque iremos criar uma planilha com o mesmo nome da tabela no objeto DataTable que passamos no parâmetro pDtOrigem. Portanto, a propriedade TableName deste DataTable deverá estar preenchida.

Para criar uma nova planilha através de uma conexão ADO.NET, usaremos o comando SQL CREATE TABLE em sua forma usual mesmo, da mesma forma que faríamos se utilizássemos um SQL Server.

Sim, é isso mesmo o que você leu! Vamos fazer igualzinho como um BD comum! Executaremos este comando DDL e quando abrirmos o arquivo Excel esta planilha estará lá.

Na varável cmdCreateTable, os parâmetros de formatação receberão o nome da tabela e a definição de cada um dos campos da planilha.

Foram criadas mais quatro variáveis auxiliares a saber:

• Campos: Receberá a sequencia de campos da tabela, que será utilizada na nossa instrução Insert.
• Values: Receberá os valores que serão colocados na instrução Insert.
• CamposCreate: Receberá a definição dos campos para a instrução Create Table, com o nome e o tipo de dado de cada campo.
• Contador: utilizada para sequenciar parâmetros de formatação para os valores da instrução Insert.

Declaradas as variáveis, varremos a lista de colunas do nosso DataTable de origem (a propriedade Columns) através de um laço foreach, e dentro dele concatenamos o nome do campo com uma vírgula (no caso da variável Campos); concatenamos aspas duplas com um indexador de formatação, algo como "{0}","{1}", utilizando-se barra invertida para escapar o caracter aspa dupla na string e finalizando com vírgula (no caso da variável Values); concatenamos o nome de cada campo com o tipo varchar(30) (caso da variável CamposCreate); e incrementamos o nosso contador.

Note que vamos definir TODOS OS CAMPOS como caracter. Mas isto não é importante agora, o tipo de dado real será definido na maneira como colocamos o dado dentro do Insert.

E por falar nisso, reparou como ficará o resultado da variável Values, que são justamente esses valores? Cada valor será colocado entre aspas duplas dentro da nossa instrução Insert final. Isto sim fará com que a célula da planilha fique com formato texto. Caso queira colocar valores numéricos, basta colocar o número sem aspas.

Feito isso, eliminamos a vírgula que sobra de cada string e criamos a nossa instrução Insert "completa", colocando-a na variável sqlInsert. O "completa", entre aspas, significa que os valores ainda não estão nesta instrução, em seu lugar estão parâmetros de formatação para uma futura instrução String.Format().

Vamos trabalhar aqui com strings por ser mais simples de manipular :-)

Agora é que vamos fazer o trabalho de criação da nova planilha!

Depois das nossas variáveis já pré-formatadas (putz, agora tem ou não tem o hífen? preciso estudar mais hehe), iremos executar o comando CREATE TABLE, colocando no CommandText do objeto Command a nossa string de Create Table já colocando os parâmetros de formatação: a propriedade TableName do DataTable pDtOrigem, e a variável CamposCreate que alimentamos no foreach anterior. Feito isso, executamos com o método ExecuteNonQuery do Command.

Se no parâmetro pOrdenar for informado alguma coisa, uma string de ordenação (por exemplo, NOME ASC, caso a sua tabela tenha um campo chamado NOME e você quer ordená-lo de forma ascendente por este campo) o nosso método criará um DataView a partir do DataTable, irá colocar o valor de pOrdenar na propriedade Sort deste DataView e em seguida o retornará um DataTable ordenado para o próprio DataTable de origem.

Agora vamos tratar de inserir as linhas do DataTable na planilha:

Através de um laço foreach, varremos a coleção Rows do nosso DataTable de Origem e colocando cada linha da iteração em um objeto DataRow chamado rOrigem, declarado na definição do laço.

Dentro do laço, criamos um array de Object de uma dimensão, com quantidade de elementos igual a quantidade de colunas do DataTable. Este array guardará o valor de cada coluna da linha corrente da iteração, e ele será utilizado para montar a instrução de Insert definitiva, através de um comando String.Format, que em uma das suas sobrecargas recebe como parâmetros a string a ser formatada e um array de Object, que deve ter suas dimensões igual a quantidade de parâmetros de formatação.

Criado o array, inicializamos um contador auxiliar.

Em seguida, faremos um laço foreach aninhado a este, que varrerá cada coluna da linha corrente do DataTable, e dentro dele o valor desta coluna será atrubuido na posição do array indicada pelo contador. Note que utilizamos o método ToString() para fazer a atribuição. Incrementamos o nosso contador.

Feita a atribuição, montamos o CommandText do nosso Command, formatando a variável sqlInsert com o array dos valores que atribuimos no foreach acima e executamos com o método ExecuteNonQuery.

Acabando-se as linhas, finalizamos nossos objetos de conexão e teremos uma planilha novinha em folha com os dados do DataTable!

Mais para frente (Próxmo artigo? ;-) ) faremos uma aplicação ASP.NET que exportará e importará uma planilha! Aguarde ;-)