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 Modal49: 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).
Posts Relacionados:
3 comentários:
Po cara justo o exemplo q eu queria do popupmodal vc nao colocou o fonte :(
Me manda por email hkbartsimpson@hotmail.com o arquivo fonte de jquery popup modal com update panel no asp.net !! vlw
@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