Inserindo, atualizando dados e Criando uma nova planilha em Excel via ADO.NET (.NET - C#, VB)
Continuando o assunto de como manipular planilhas Excel via ADO.NET, vou colocar um método que exporta o conteúdo de um DataTable para uma planilha Excel:
1: public void DataTable2ExcelPlan(string pBlankWorksheetPath, string pDestWorksheetPath, DataTable pDtOrigem, string pOrdenar)
2: {
3: //Copia a planilha de origem
4: File.Copy(pBlankWorksheetPath, pTempWorksheetPath, true);
5: //Fim da rotina de cópia
6:
7: //Instancia planilha de destino, criando um DataTable para inserir os dados
8: string cnnString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", pDestWorksheetPath);
9: string sqlInsertGen = "insert into [{0}$] ({1}) values ({2})";
10:
11: System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(cnnString);
12: System.Data.OleDb.OleDbCommand cd = new System.Data.OleDb.OleDbCommand();
13: cd.Connection = cnn;
14:
15: //Cria uma nova planilha no Excel
16:
17: //Cria nova planilha e Monta SQL de inserção
18: string cmdCreateTable = "create table {0} ({1})";
19: string Campos = "";
20: string Values = "";
21: string CamposCreate = "";
22: int contador = 0;
23: foreach (DataColumn dc in pDtOrigem.Columns)
24: {
25: Campos += dc.ColumnName + ",";
26: Values += "\"{" + contador.ToString() + "}\",";
27: CamposCreate += dc.ColumnName + " varchar(30),";
28: contador++;
29: }
30:
31: Campos = Campos.Substring(0, Campos.Length - 1);
32: Values = Values.Substring(0, Values.Length - 1);
33: CamposCreate = CamposCreate.Substring(0, CamposCreate.Length - 1);
34: string sqlInsert = String.Format(sqlInsertGen, pDtOrigem.TableName, Campos, Values);
35:
36: //Fim da montagem do SQL de inserção
37:
38: try
39: {
40: cnn.Open();
41: cd.CommandText = String.Format(cmdCreateTable, pDtOrigem.TableName, CamposCreate);
42: cd.ExecuteNonQuery();
43:
44: if (!pOrdenar.Equals(""))
45: {
46: DataView dv = pDtOrigem.DefaultView;
47: dv.Sort = pOrdenar;
48: pDtOrigem = dv.ToTable();
49: }
50:
51: //Cópia dos dados
52: foreach (DataRow rOrigem in pDtOrigem.Rows)
53: {
54: object[] valores = new object[pDtOrigem.Columns.Count];
55: int ctValores = 0;
56: foreach (DataColumn cOrigem in pDtOrigem.Columns)
57: {
58: valores[ctValores] = rOrigem[cOrigem.ColumnName].ToString();
59: ctValores++;
60: }
61: cd.CommandText = String.Format(sqlInsert, valores);
62: cd.ExecuteNonQuery();
63: }
64: }
65: finally
66: {
67: cnn.Close();
68: cnn.Dispose();
69: cd.Dispose();
70: }
71: //Fim da inserção dos dados na planilha
72: }
Veja a explicação deste código após o break.
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 ;-)
3 comentários:
blz, Leonel parbens pelo blog, esta otimo
esta serie de post tem me ajudado muito.
sou um programdor novato em .net, estou comum problema e gostaria que voce me ajudasse. tenho um um arquivo no excel, com 3 planilhas que estão formatadas(textos, graficos e formatação: cores etc.) tenho que abrir essa planila que sera meu modelo e edita-la salvando em um novo arquivo excel, com dados vindos do banco de dados.
como faco pra preencher as planilhas apartir do modelo, sem perder a formatação e salvando em um novo arquivo excel.
Att.André Luiz FPS
Obrigado
@André Luiz:
Putz, vc me pegou na parte do "manter a formatação". Isso eu não saberia como fazer via ADO.NET, porém, você pode vincular dados externos na planilha diretamente do Excel e depois copiar.
Esses códigos que eu postei, servem mais para você exportar dados para uma planilha, aí o que será feito com ela, é por conta do usuário :-)
E ele interpreta o arquivo XLS como se fosse um banco SQL, sem se importar com formatação, essas coisas.
Abraços!
valeu pela atenção leonel, vo tentar e ver oque consigo.
abraço
Att. Andre Luiz FPS
Postar um comentário