O NeoMatrix Tech está de casa nova!

Você deverá ser redirecionado em 6 segundos. Se não, visite:
http://www.leonelfraga.com/neomatrixtech
e atualize seus favoritos.

Aviso IMPORTANTÍSSIMO!

Aviso aos navegantes:

O NeoMatrix Tech mudou de casa!!!

A partir de agora, acessem pelo novo endereço:

http://www.leonelfraga.com/neomatrixtech

Ué... mas é só o domínio mudou de lugar?

R: Na verdade, não é bem assim hehe. Este domínio que você acessa agora aponta para um blog hospedado no Blogger, enquanto no novo, aponta para um blog na plataforma Wordpress, hospedagem própria, muito mais rápida e com um layout mais agradável de ler ;)

Não vou fechar este domínio igual ao que eu fiz com o NM Light (que já está 100% na nova plataforma). Talvez beeeeeeem depois eu faça isso.

Todos os posts daqui se encontram lá, e novos posts serão colocados somente no novo endereço.
A única coisa que não consegui importar foram os comentários. Mas em breve vai ter um post contando sobre a epopéia que foi migrar o NeoMatrix Tech!

Somente vou fechar a área de comentários daqui. Caso queiram comentar, favor ver o post correspondente no "Novo NeoMatrix Tech" e comentem por lá. É bem melhor! (pena que os permalinks "amigáveis para SEO" não funcionam lá, dá erro 404 e não consigo fazer a configuração funcionar. E olha que eu já vi vários artigos falando desse assunto :( ).

Quem assina o feed, já está lendo o conteúdo do novo NeoMatrix Tech!

sábado, 14 de fevereiro de 2009

Exemplo de uso – Manipulando Planilhas Excel com ADO.NET

Agora sim, finalmente temos a conclusão de dois artigos (este e este outro :-) ) que tratam de como manipular, seja lendo ou escrevendo uma planilha do Excel fazendo o uso das classes do ADO.NET e do provider OleDB.

Nos artigos mencionados, foram apresentados dois métodos: um para fazer a leitura de uma planilha e jogar em um DataTable e outro para varrer um DataTable e criar uma nova planilha a partir dele.

Agora, vamos criar uma aplicação ASP.NET na qual o usuário tem na tela um gridview com o resultado de um DataTable e um botão para exportar este grid para uma planilha e outro botão que permitirá fazer o upload de uma planilha e ter seus dados listados no grid.

No Visual Studio, crie um website habilitado para AJAX, coloque os arquivos de suporte necessários (no nosso exemplo, o jQuery, o jqModal, e imagens) e crie uma classe (na pasta App_Code) chamada Auxiliar. Depois iremos fazer o design da página.

Coloque o seguinte código na classe Auxiliar:

   1: using System;
   2: using System.Data;
   3: using System.Configuration;
   4: using System.Web;
   5: using System.Web.Security;
   6: using System.Web.UI;
   7: using System.Web.UI.WebControls;
   8: using System.Web.UI.WebControls.WebParts;
   9: using System.Web.UI.HtmlControls;
  10: using System.Data.OleDb;
  11: using System.IO;
  12:  
  13: /// <summary>
  14: /// Classe auxiliar do Exemplo de Importação/Exportação Excel -> DataTable via ADO.NET
  15: /// </summary>
  16: public class Auxiliar
  17: {
  18:     public Auxiliar()
  19:     {
  20:         //
  21:         // TODO: Add constructor logic here
  22:         //
  23:     }
  24:  
  25:     public DataTable CreateSampleTable()
  26:     {
  27:         //Estrutura
  28:         DataTable dt = new DataTable();
  29:         dt.TableName = "ExemploDataTable2Excel";
  30:         dt.Columns.Add(new DataColumn("CODIGO"));
  31:         dt.Columns.Add(new DataColumn("NOME"));
  32:         dt.Columns.Add(new DataColumn("SEXO"));
  33:         dt.Columns.Add(new DataColumn("PROFISSAO"));
  34:  
  35:         
  36:         //Dados Exemplo
  37:         dt.Rows.Add(1, "CLODOVIL HERNANDES", "G", "DE*PUTA*DO ESTADUAL" );
  38:         dt.Rows.Add(2, "JUCA KIFOURI", "M", "JORNALISTA");
  39:         dt.Rows.Add(3, "BENJAMIN 'NARIGUDO' BACK", "M", "APRESENTADOR DO ESTÁDIO 97" );
  40:         dt.Rows.Add(4, "MILLY LACOMBE", "L", "REPÓRTER MALA" );
  41:         dt.Rows.Add(5, "ROGÉRIO CENI", "M", "GOLEIRO DO TRICOLAÇO DO MORUMBI" );
  42:         dt.Rows.Add(6, "MICHELE", "F", "DELÍCIA DO BBB 9 (ops... MODELO)" );
  43:         dt.Rows.Add(6, "MARIA DAS GRAÇAS MENEGHEL", "B", "ATRIZ DE FILME EDUCATIVO");
  44:         dt.Rows.Add(6, "ALEXANDRE FROTA", "B", "ATOR DE FILME EDUCATIVO");
  45:         dt.Rows.Add(6, "GALVÃO BUENO", "M", "LOCUTOR MALA DA REDE BOBO");
  46:  
  47:         return dt;
  48:     }
  49:  
  50:     /// <summary>
  51:     /// Importa os dados de uma planilha Excel para um datatable
  52:     /// </summary>
  53:     /// <param name="pWorksheetPath">Caminho do arquivo xls</param>
  54:     /// <param name="pPlanName">Nome da planilha a ser importada.</param>
  55:     /// <returns></returns>
  56:     public DataTable ExcelPlan2DataTable(string pWorksheetPath, string pPlanName)
  57:     {
  58:         string cnnString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", pWorksheetPath);
  59:         string isql = "select * from [{0}$]";
  60:         System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(cnnString);
  61:         System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(String.Format(isql, pPlanName), cnn);
  62:         DataSet ds = new DataSet();
  63:         DataTable dt = new DataTable();
  64:         try
  65:         {
  66:             cnn.Open();
  67:             da.Fill(ds);
  68:             dt = ds.Tables[0];
  69:         }
  70:         finally
  71:         {
  72:             cnn.Close();
  73:             cnn.Dispose();
  74:             da.Dispose();
  75:             ds.Dispose();
  76:         }
  77:         return dt;
  78:     }
  79:  
  80:     /// <summary>
  81:     /// Exporta um DataTable para uma Planilha Excel
  82:     /// </summary>
  83:     /// <param name="pBaseWorksheetPath">Caminho da Planilha Base</param>
  84:     /// <param name="pTempWorksheetPath">Caminho da Planilha a ser salva (temporária)</param>
  85:     /// <param name="pDtOrigem">Datatable a ser exportado</param>
  86:     /// <param name="pNomePlanOutput">Nome da planilha de destino</param>
  87:     /// <param name="pOrdenar">String de Ordenação (DataView)</param>
  88:     /// <returns>Array de bytes com a planilha no formato Microsoft Excel.</returns>
  89:     /// <remarks>A planilha de destino deverá ter OS MESMOS CAMPOS do datatable de origem.</remarks>
  90:     public byte[] DataTable2ExcelPlan(string pBaseWorksheetPath, string pTempWorksheetPath, DataTable pDtOrigem, string pOrdenar)
  91:     {
  92:         //Copia a planilha de origem
  93:         File.Copy(pBaseWorksheetPath, pTempWorksheetPath, true);
  94:         //Fim da rotina de cópia
  95:  
  96:         //Instancia planilha de destino, criando um DataTable para inserir os dados
  97:         string cnnString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", pTempWorksheetPath);
  98:         string sqlInsertGen = "insert into [{0}$] ({1}) values ({2})";
  99:  
 100:         System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(cnnString);
 101:         System.Data.OleDb.OleDbCommand cd = new System.Data.OleDb.OleDbCommand();
 102:         cd.Connection = cnn;
 103:  
 104:         //Cria uma nova planilha no Excel
 105:  
 106:         //Cria nova planilha e Monta SQL de inserção
 107:         string cmdCreateTable = "create table {0} ({1})";
 108:         string Campos = "";
 109:         string Values = "";
 110:         string CamposCreate = "";
 111:         int contador = 0;
 112:         foreach (DataColumn dc in pDtOrigem.Columns)
 113:         {
 114:             Campos += dc.ColumnName + ",";
 115:             Values += "\"{" + contador.ToString() + "}\",";
 116:             CamposCreate += dc.ColumnName + " varchar(30),";
 117:             contador++;
 118:         }
 119:  
 120:         Campos = Campos.Substring(0, Campos.Length - 1);
 121:         Values = Values.Substring(0, Values.Length - 1);
 122:         CamposCreate = CamposCreate.Substring(0, CamposCreate.Length - 1);
 123:         string sqlInsert = String.Format(sqlInsertGen, pDtOrigem.TableName, Campos, Values);
 124:  
 125:         //Fim da montagem do SQL de inserção
 126:  
 127:         try
 128:         {
 129:             cnn.Open();
 130:             cd.CommandText = String.Format(cmdCreateTable, pDtOrigem.TableName, CamposCreate);
 131:             cd.ExecuteNonQuery();
 132:  
 133:             if (!pOrdenar.Equals(""))
 134:             {
 135:                 DataView dv = pDtOrigem.DefaultView;
 136:                 dv.Sort = pOrdenar;
 137:                 pDtOrigem = dv.ToTable();
 138:             }
 139:  
 140:             //Cópia dos dados
 141:             foreach (DataRow rOrigem in pDtOrigem.Rows)
 142:             {
 143:                 object[] valores = new object[pDtOrigem.Columns.Count];
 144:                 int ctValores = 0;
 145:                 foreach (DataColumn cOrigem in pDtOrigem.Columns)
 146:                 {
 147:                     valores[ctValores] = rOrigem[cOrigem.ColumnName].ToString();
 148:                     ctValores++;
 149:                 }
 150:                 cd.CommandText = String.Format(sqlInsert, valores);
 151:                 cd.ExecuteNonQuery();
 152:             }
 153:         }
 154:         finally
 155:         {
 156:             cnn.Close();
 157:             cnn.Dispose();
 158:             cd.Dispose();
 159:         }
 160:         //Fim da inserção dos dados na planilha
 161:  
 162:         //Captura a planilha de destino para um stream e saída no array de bytes
 163:         FileStream fs = new FileStream(pTempWorksheetPath, FileMode.Open);
 164:         byte[] planOutput = new byte[fs.Length];
 165:  
 166:         try
 167:         {
 168:             fs.Position = 0;
 169:             fs.Read(planOutput, 0, (int)fs.Length);
 170:         }
 171:         finally
 172:         {
 173:             fs.Close();
 174:             fs.Dispose();
 175:             File.Delete(pTempWorksheetPath);
 176:         }
 177:         //Fim da captura do arquivo final
 178:         return planOutput;
 179:     }
 180:  
 181: }

Como você pode ver, ela possui os dois métodos de manipulação de planilha já explicados nos outros artigos e um que cria um DataTable de exemplo.

Agora vamos trabalhar o site em si.

Coloque o arquivo uploadFIle.aspx e seu respectivo código (você pode encontrá-lo aqui) no projeto e faça as adaptações necessárias (dependendo do tipo de projeto, algumas modificações na diretiva Page e a classe do code-behind não tem namespace). Em seguida, coloque o seguinte código na página Default.aspx:

   1: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
   2: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
   3: <html xmlns="http://www.w3.org/1999/xhtml">
   4: <head runat="server">
   5:     <title>Exemplo Importação Planilha Excel para DataTable</title>
   6:     <link type="text/css" rel="stylesheet" href="css/jqModal.css" />
   7:     <script type="text/javascript" src="javascript/jquery-1.2.6.pack.js"></script>
   1:  
   2:     <script type="text/javascript" src="javascript/jqModal.js">
   1: </script>
   2:     <style type="text/css">
   3:         .oculto {visibility:hidden;}
   4:     </style>
   5: </head>
   6: <body>
   7:     <form id="form1" runat="server">
   8:         <asp:ScriptManager ID="ScriptManager1" runat="server" />
   9:         <div id="Principal">
  10:             <div style="width: 100%; text-align: center">
  11:                 <span>Exemplo de Importação de Planilha Excel para DataTable</span></div>
  12:             <asp:UpdatePanel runat="server" ID="Upd1">
  13:                 <Triggers>
  14:                     <asp:PostBackTrigger ControlID="btnExportar" />
  15:                 </Triggers>
  16:                 <ContentTemplate>
  17:                     <div style="width: 100%; text-align: center">
  18:                         <asp:Button runat="server" ID="btnImportar" Text="Importar Planilha" OnClientClick="$dvUpload.jqmShow();return false;" />
  19:                         <asp:Button runat="server" ID="btnExportar" Text="Exportar Dados do Grid" OnClick="btnExportar_Click" />
  20:                         <input type="button" id="btnSinalizaUpload" style="visibility:hidden" onclick='<%=btnProcessar.ClientID + ".click();"%>' />
  21:                         <asp:Button runat="server" ID="btnProcessar" CssClass="oculto" OnClick="btnProcessar_Click" />
  22:                     </div>
  23:                     <br />
  24:                     <asp:GridView runat="server" ID="gvDados" AutoGenerateColumns="false" Width="100%">
  25:                         <HeaderStyle BackColor="red" Font-Names="Verdana" HorizontalAlign="Center" ForeColor="black"
  26:                             Font-Bold="true" />
  27:                         <RowStyle BackColor="beige" Font-Names="Verdana" />
  28:                         <Columns>
  29:                             <asp:BoundField DataField="CODIGO" HeaderText="Código" />
  30:                             <asp:BoundField DataField="NOME" HeaderText="Nome" />
  31:                             <asp:BoundField DataField="SEXO" HeaderText="Sexo" />
  32:                             <asp:BoundField DataField="PROFISSAO" HeaderText="Profissão" />
  33:                         </Columns>
  34:                     </asp:GridView>
  35:                 </ContentTemplate>
  36:             </asp:UpdatePanel>
  37:         </div>
  38:         <div id="dvUpload" class="jqmWindow">
  39:             <div style="width: 100%; text-align: center">
  40:                 <span>Selecione o Arquivo Excel para importação</span></div>
  41:                 <iframe src="uploadFile.aspx" style="border:none 0px white;width:400px;height:80px;"></iframe>
  42:             <div style="text-align: center; width: 100%;">
  43:                 <input type="button" value="Fechar esta janela" onclick="$dvUpload.jqmHide();return false;" />
  44:             </div>
  45:         </div>
  46:  
  47:         <script type="text/javascript">
  48:                 //Inicializa o Modal
  49:                 var $dvUpload = $('#dvUpload').jqm({modal:true,toTop:true,trigger:false});
  50:         
</script>
   8:  
   9:     </form>
  10: </body>
  11: </html>

E também o code-behind:

   1: using System;
   2: using System.Data;
   3: using System.Configuration;
   4: using System.Web;
   5: using System.Web.Security;
   6: using System.Web.UI;
   7: using System.Web.UI.WebControls;
   8: using System.Web.UI.WebControls.WebParts;
   9: using System.Web.UI.HtmlControls;
  10: using System.IO;
  11:  
  12: public partial class _Default : System.Web.UI.Page 
  13: {
  14:     protected void Page_Load(object sender, EventArgs e)
  15:     {
  16:         if (!IsPostBack)
  17:         { 
  18:             Auxiliar au = new Auxiliar();
  19:             gvDados.DataSource = au.CreateSampleTable();
  20:             gvDados.DataBind();
  21:         }
  22:     }
  23:  
  24:     protected void btnProcessar_Click(object sender, EventArgs e)
  25:     {
  26:         if (Session["AREA_UPLOAD"] != null)
  27:         {
  28:             Auxiliar au = new Auxiliar();
  29:             string Path = Server.MapPath(this.AppRelativeVirtualPath);
  30:             FileInfo fi = new FileInfo(Path);
  31:             string PlanPath = fi.Directory + "\\plan\\tempimp" + Session.SessionID + ".xls";
  32:             try
  33:             {
  34:                 FileStream fs = new FileStream(PlanPath, FileMode.Create);
  35:                 fs.Write((byte[])Session["AREA_UPLOAD"], 0, (Session["AREA_UPLOAD"] as byte[]).Length);
  36:                 fs.Close();
  37:                 gvDados.DataSource = au.ExcelPlan2DataTable(PlanPath, "ExemploExcel2DataTable");
  38:                 gvDados.DataBind();
  39:             }
  40:             finally
  41:             {
  42:                 File.Delete(PlanPath);
  43:                 Session.Remove("AREA_UPLOAD");
  44:             }
  45:         }
  46:         
  47:         ScriptManager.RegisterStartupScript(this, this.GetType(), "teste", "alert('caralho com asa fumegante e voador');", true);
  48:     }
  49:  
  50:     protected void btnExportar_Click(object sender, EventArgs e)
  51:     {
  52:         Response.Buffer = true;
  53:         Response.ContentType = "application/excel";
  54:         Auxiliar aux = new Auxiliar();
  55:         try
  56:         {
  57:             string Path = Server.MapPath(this.AppRelativeVirtualPath);
  58:             FileInfo fi = new FileInfo(Path);
  59:  
  60:  
  61:             byte[] plan = aux.DataTable2ExcelPlan(fi.Directory + "\\plan\\planbase.xls", fi.Directory + "\\plan\\plantemp.xls", aux.CreateSampleTable(), "");
  62:             if (plan.Length != 0)
  63:             {
  64:                 string att = "attachment";
  65:                 Response.AddHeader("Content-Disposition", att + "; filename=planilha.xls");
  66:                 BinaryWriter bw = new BinaryWriter(Response.OutputStream);
  67:                 bw.Write(plan);
  68:                 bw.Flush();
  69:                 bw.Close();
  70:             }
  71:         }
  72:         finally
  73:         {
  74:         }
  75:         Response.End();
  76:  
  77:     }
  78: }

Você poderá baixar o exemplo completo utilizando o link no final do artigo.

O design da página, como você pode ver, é bem simples. Para maiores explicações de como funciona a parte de upload, sugiro que dê uma lida nesta série de artigos, onde explico os processos que acontecem no div dvUpload, na página uploadFile.aspx e no botão btnSinalizaUpload e o porquê do botão btnProcessar está oculto e tem o seu evento OnClick disparado por outro botão :-)

Vamos começar com o botão “Exportar Dados do Grid”:

Como este botão modifica o stream de resposta da página, ele não poderá ser utilizado com AJAX.

Pera lá! Então o que ele está fazendo dentro de um UpdatePanel?

Pois é… está dentro de um UpdatePanel. Mas dentro da declaração deste UpdatePanel, foi colocada a seção <Triggers>, que serve para fazer com que os controles disparem eventos assíncronos ou não (usando AJAX ou não). Explico:

Se colocarmos na seção <Triggers> um controle PostBackTrigger, informaremos que o controle com o ID colocado no atributo ControlID irá disparar um postback normal, não disparando as rotinas AJAX.

Por outro lado, se colocarmos um controle AsyncPostBackTrigger, poderemos informar na propriedade ControlID um controle que está fora do UpdatePanel e que irá disparar as rotinas de atualização assíncrona dele, ou seja, irá funcionar com AJAX.

Vendo o código de seu evento OnClick, primeiramente mudamos o MIME type da resposta para application/excel, que quer dizer que o navegador estará recebendo uma planilha em formato Excel.

Em seguida, criamos uma instância da classe Auxiliar (variável aux), e guardamos o path completo da página na variável Path, através do método Server.MapPath (que retorna o caminho físico a partir de um caminho virtual), recebendo como parâmetro a propriedade AppRelativeVirtualPath do objeto Page.

Colocamos essa variável Path em uma instância da classe FileInfo, com a qual extrairemos a seguir somente o caminho do diretório em que a nossa aplicação está hospedada.

Agora vamos criar um Array de Byte, utilizando o método DataTable2ExcelPlan, que recebe como parâmetros uma planilha em branco, que será utilizada como base da conexão OleDB e que já deve existir no caminho proposto, uma planilha de trabalho temporária, que será criada conforme a necessidade, e por fim o DataTable a ser exportado.

Este DataTable é criado pelo mesmo método que alimenta o gridview, o método CreateSampleTable da classe Auxiliar. Se você estiver utilizando uma query para alimentar o gridview, deverá executar o mesmo método que utilizou no Bind do grid em questão.

O retorno do método DataTable2ExcelPlan é um array de bytes com o conteúdo da planilha gerada a partir do DataTable.

Se a planilha for gerada com sucesso, alteramos o header da resposta HTTP indicando que há um arquivo anexo com o método AddHeader do objeto Response, e em seguida escrevemos o array de bytes no OutputStream através de um BinaryWriter.

Na importação da planilha, acionada pelo botão btnProcessar, que está oculto na página e é disparado indiretamente através da página uploadFile.aspx, temos primeiro a verificação do arquivo na variável de sessão AREA_UPLOAD, para em seguida pegar o caminho da nossa aplicação, da mesma forma que fizemos anteriormente.

Para que a nossa conexão OleDB funcione, é necessário que a planilha exista em disco. Para isso, utilizamos um FileStream, informando em seu construtor o caminho do arquivo e o modo de operação, que setamos em FileMode.Create.

Note que no nome do arquivo que iremos salvar, colocamos o SessionID da sessão atual. Como vários usuários poderão fazer upload ao mesmo tempo, isto se torna necessário para que não haja conflitos.

Criado o FileStream, chamamos o método Write, ao qual passamos na sobrecarga que estamos utilizando um array de bytes (o arquivo que fizemos upload), a posição inicial e a quantidade de bytes a ser escrita, e em seguida fechamos o FileStream. Isto é importantíssimo, pois se o FileStream estiver aberto a nossa planilha não poderá ser lida.

Em seguida, utilizamos como DataSource do nosso gridview o método ExcelPlan2DataTable da classe Auxiliar, informando como parâmetros o caminho da planilha a ser lida e o nome da planilha dentro do arquivo XLS que queremos ler.

Damos o DataBind() no grid e voilá! A planilha aparece no grid :-)

E como essa planilha que salvamos através do FileStream é apenas temporária, no final de tudo iremos apagar este arquivo que foi gerado e remover a variável de sessão AREA_UPLOAD.

E é isso! Uma aplicação ASP.NET que faz o uso de Upload com efeitos AJAX e lendo uma planilha Excel, que com a sua imaginação poderá ser guardada em banco de dados, e outras coisas mais :-)

Um abraço e até a próxima!

  Exemplo Aplicação Exportando e Importando Planilhas Excel com ASP.NET (40 KB).

3 comentários:

Anônimo,  19 de nov de 2009 15:40:00  

Po cara justo o exemplo q eu queria do popupmodal vc nao colocou o fonte :(

Anônimo,  19 de nov de 2009 15:41:00  

Me manda por email hkbartsimpson@hotmail.com o arquivo fonte de jquery popup modal com update panel no asp.net !! vlw

Leonel Fraga de Oliveira 19 de nov de 2009 21:07:00  

@Anonimo:

Na categoria de ASP.NET tem vários exemplos de popup modal (usando jQuery).

Para o anônimo que mandou o email: Já enviei o email com os exemplos :-)
O post em questão que tem o fonte é sobre "turbinando o gridview"!

Abraços!


Postar um comentário

Para tornar este artigo ainda mais interessante, escreva suas críticas (desde que construtivas e sem ofenças), elogios, sugestões, complementos, dúvidas, etc, etc, etc!!!

  © Blogger templates ProBlogger Template by Ourblogtemplates.com 2008 - Editado e configurado por Leonel F.

Voltar ao TOPO