fagnerx21 0 Denunciar post Postado Dezembro 24, 2007 Olá, eu criei uma página asp.net para gerar relatórios e gráficos no Excel, mas quando testo o programa, gera alguns erros, vou postar o código e o errolog, se alguém puder me ajudar fico grato. Web.Config: <?xml version="1.0"?> <!-- Note: As an alternative to hand editing this file you can use the web admin tool to configure settings for your application. Use the Website->Asp.Net Configuration option in Visual Studio. A full list of settings and comments can be found in machine.config.comments usually located in \Windows\Microsoft.Net\Framework\v2.x\Config --> <configuration> <appSettings/> <connectionStrings> <add name="cn" connectionString="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|\Vendas.xls;Extended Properties=Excel 8.0"/> </connectionStrings> <system.web> <!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. --> <compilation debug="true"> <assemblies> <add assembly="Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"/></assemblies></compilation> <!-- The <authentication> section enables configuration of the security authentication mode used by ASP.NET to identify an incoming user. --> <authentication mode="Windows"/> <!-- The <customErrors> section enables configuration of what to do if/when an unhandled error occurs during the execution of a request. Specifically, it enables developers to configure html error pages to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm"> <error statusCode="403" redirect="NoAccess.htm" /> <error statusCode="404" redirect="FileNotFound.htm" /> </customErrors> --> </system.web> </configuration> HTML: LerDadosExcel.aspx: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <span style="font-size: 25px; color: blue"><strong>Gerar Gráfico No Excel<br /> <br /> <asp:Label ID="Label1" runat="server" Font-Bold="False" Font-Size="12pt" ForeColor="Black" Text="Procurar por:"></asp:Label> <asp:DropDownList ID="ddlExcel" runat="server"> <asp:ListItem>Vendedores</asp:ListItem> <asp:ListItem>Janeiro</asp:ListItem> <asp:ListItem>Fevereiro</asp:ListItem> </asp:DropDownList> <asp:Button ID="btnProcurar" runat="server" OnClick="btnProcurar_Click" Text="Procurar" /><br /> <br /> <asp:GridView ID="gdvExcel" runat="server" CellPadding="4" Font-Bold="False" Font-Size="12pt" ForeColor="#333333" GridLines="None"> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#EFF3FB" /> <EditRowStyle BackColor="#2461BF" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> </strong></span> </div> </form> </body> </html> GerarRelatório.aspx: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="GerarRelatorio.aspx.cs" Inherits="GerarGrafico" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <strong><span style="font-size: 25px; color: #0000ff">Gerar Gráfico No Excel<br /> <br /> <asp:Button ID="btnGerarRelatorio" runat="server" OnClick="btnGerarGrafico_Click" Text="Gerar Relatório" /><br /> <br /> <asp:GridView ID="gdvGrafico" runat="server" CellPadding="4" Font-Bold="False" Font-Size="12pt" ForeColor="#333333" GridLines="None"> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#EFF3FB" /> <EditRowStyle BackColor="#2461BF" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <br /> <asp:Button ID="btnExcelResponse" runat="server" OnClick="btnExcelResponse_Click" Text="Exportar para Excel - via Response" /><br /> <br /> <asp:Button ID="btnExcelObjeto" runat="server" Text="Exportar para XLS usando objeto Excel" OnClick="btnExcelObjeto_Click" /></span></strong></div> </form> </body> </html> C#: Default.aspx.cs: using System; using System.Data; using System.Data.OleDb; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnProcurar_Click(object sender, EventArgs e) { try { //Obtém a string de conexão do arquivo web.config String cn = System.Configuration.ConfigurationManager.ConnectionStrings["cn"].ToString(); OleDbConnection dbcn = new OleDbConnection(cn); String sql; //Monta a instrução sql para obter os dados da planilha selecionada na caixa de listagem ddlExcel sql = "SELECT * FROM [" + ddlExcel.SelectedItem.ToString() + "$]"; dbcn.Open(); //Executa a instrução sql e cria um DataSet para receber os dados OleDbCommand cmd = new OleDbCommand(sql, dbcn); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); //Preenche o DataSet e exibe os dados no GridView da.Fill(ds); gdvExcel.DataSource = ds; gdvExcel.DataBind(); } catch (Exception ex) { throw ex; } } } GerarRelatorio.aspx.cs: using System; using Microsoft.Office.Interop.Excel; using System.Data; using System.Data.OleDb; using System.Reflection; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class GerarGrafico : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnGerarGrafico_Click(object sender, EventArgs e) { try { gdvGrafico.DataSource = ObterDados(); gdvGrafico.DataBind(); } catch (Exception ex) { throw ex; } } private DataSet ObterDados() { //Obtém a string de conexão do arquivo web.config String cn = System.Configuration.ConfigurationManager.ConnectionStrings["cn"].ToString(); OleDbConnection dbcn = new OleDbConnection(cn); String sql; //Define a instrução sql que obterá os dados das 3 planilhas sql = "SELECT V.CODIGO, V.VENDEDORES, J.VENDAS, F.VENDAS, ( J.VENDAS + F.VENDAS ) AS Total" + "FROM [VENDEDORES$] V, [JANEIRO$] J, [FEVEREIRO$] F" + "WHERE ( V.CODIGO = J.CODIGO AND V.CODIGO = F.CODIGO)" + "ORDER BY ( J.VENDAS + F.VENDAS ) DESC"; dbcn.Open(); //Executa o comando sql e preenche o DataSet com o resultado obtido OleDbCommand cmd = new OleDbCommand(sql, dbcn); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(ds); //Retorna o DataSet return ds; } protected void btnExcelResponse_Click(object sender, EventArgs e) { //Declaração de variáveis System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(tw); DataGrid dg = new DataGrid(); dg.DataSource = ObterDados(); //Cabeçalho do relatório hw.WriteLine("<b><u><font size='5'> Relatório - Vendas </font></u></b>"); //Obtém o HTML para o controle dg.HeaderStyle.Font.Bold = true; dg.DataBind(); dg.RenderControl(hw); //Escreve o HTML de volta no navegador Response.ContentType = "application/vnd.ms-excel"; this.EnableViewState = false; Response.Write(tw.ToString()); Response.End(); } protected void btnExcelObjeto_Click(object sender, EventArgs e) { try { Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; //Define os objetos de trabalho para o Excel xlWorkBook = new Excel.Application().Workbooks.Add( Missing.Value ); xlWorkBook.Application.Visible = true; xlWorkSheet = xlWorkBook.ActiveSheet; //Obtém o DataSet contendo os dados DataSet ds = ObterDados(); int i = 2; //Define os nomes dos campos xlWorkSheet.Cells[ 1,1 ] = "Codigo"; xlWorkSheet.Cells[ 1,2 ] = "Vendedores"; xlWorkSheet.Cells[ 1,3 ] = "Janeiro"; xlWorkSheet.Cells[ 1,4 ] = "Fevereiro"; xlWorkSheet.Cells[ 1,5 ] = "Total"; xlWorkSheet.Range[ "$A1:$E1" ].Font.ColorIndex = Excel.Constants.xlColor1; xlWorkSheet.Range[ "$A1:$E1" ].Font.Bold = true; //Saída dos dados foreach( DataRow dr in ds.Tables[0].Rows ) { xlWorkSheet.Cells[ i, 1 ] = dr[0]; xlWorkSheet.Cells[ i, 2 ] = dr[1]; xlWorkSheet.Cells[ i, 3 ] = dr[2]; xlWorkSheet.Cells[ i, 4 ] = dr[3]; //Constroi a formula para calcular a soma xlWorkSheet.Cells[ i,5 ].Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString() ); //Vai para a próxima linha i += 1; } //Define a formatação das colunas xlWorkSheet.Columns.AutoFit(); //Gera o gráfico Excel.Chart chart; chart = xlWorkBook.Charts.Add(); with chart; //Define o tipo de gráfico ChartType = Excel.XlChartType.xlColumnClustered; //Define o intervalo de dados SetSourceData( xlWorkSheet.Range[ "A1:E6" ], 2 ); HasTitle = true; ChartTitle.Characters.Text = "Vendedores - Resumo Vendas"; Axes[ 1, Excel.XlAxisGroup.xlPrimary ].HasTitle = true; Axes[ 1, Excel.XlAxisGroup.xlPrimary ].AxisTitle.Characters.Text = "Vendedores"; Axes[ 2, Excel.XlAxisGroup.xlPrimary ].HasTitle = true; Axes[ 2, Excel.XlAxisGroup.xlPrimary ].AxisTitle.Characters.Text = "Vendas"; endwith; } catch( Exception ex ) { throw ex; } } } ErrorLog: Error 1 The type or namespace name 'Excel' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 78 13 C:\...\ExcelGrafico\ Error 2 The type or namespace name 'Excel' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 79 13 C:\...\ExcelGrafico\ Error 3 The type or namespace name 'Excel' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 81 30 C:\...\ExcelGrafico\ Error 4 The name 'Excel' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 93 62 C:\...\ExcelGrafico\ Error 5 The type or namespace name 'Excel' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 110 13 C:\...\ExcelGrafico\ Error 6 The type or namespace name 'with' could not be found (are you missing a using directive or an assembly reference?) C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 112 13 C:\...\ExcelGrafico\ Error 7 A local variable named 'chart' is already defined in this scope C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 112 18 C:\...\ExcelGrafico\ Error 8 The name 'ChartType' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 114 13 C:\...\ExcelGrafico\ Error 9 The name 'Excel' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 114 25 C:\...\ExcelGrafico\ Error 10 The name 'SetSourceData' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 116 13 C:\...\ExcelGrafico\ Error 11 The name 'HasTitle' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 117 13 C:\...\ExcelGrafico\ Error 12 Property, indexer, or event 'Characters' is not supported by the language; try directly calling accessor method 'Microsoft.Office.Interop.Excel.ChartTitle.get_Characters(object, object)' C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 118 24 C:\...\ExcelGrafico\ Error 13 'Microsoft.Office.Interop.Excel.Axes' is a 'type' but is used like a 'variable' C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 119 13 C:\...\ExcelGrafico\ Error 14 The name 'Excel' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 119 22 C:\...\ExcelGrafico\ Error 15 'Microsoft.Office.Interop.Excel.Axes' is a 'type' but is used like a 'variable' C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 120 13 C:\...\ExcelGrafico\ Error 16 The name 'Excel' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 120 22 C:\...\ExcelGrafico\ Error 17 'Microsoft.Office.Interop.Excel.Axes' is a 'type' but is used like a 'variable' C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 121 13 C:\...\ExcelGrafico\ Error 18 The name 'Excel' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 121 22 C:\...\ExcelGrafico\ Error 19 'Microsoft.Office.Interop.Excel.Axes' is a 'type' but is used like a 'variable' C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 122 13 C:\...\ExcelGrafico\ Error 20 The name 'Excel' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 122 22 C:\...\ExcelGrafico\ Error 21 Only assignment, call, increment, decrement, and new object expressions can be used as a statement C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 123 13 C:\...\ExcelGrafico\ Error 22 The name 'endwith' does not exist in the current context C:\Documents and Settings\Administrador\Meus documentos\Visual Studio 2005\WebSites\ExcelGrafico\GerarRelatorio.aspx.cs 123 13 C:\...\ExcelGrafico\ Por favor, me ajudem. Obrigado Compartilhar este post Link para o post Compartilhar em outros sites
Juliano.net 2 Denunciar post Postado Dezembro 26, 2007 Verifique se seu projeto possui a referência para a DLL do Excel Compartilhar este post Link para o post Compartilhar em outros sites
Roberto Vn 0 Denunciar post Postado Março 6, 2008 Olá. Estou exportando dados para Excel da maneira citata. Mas a aplicação só roda no servidor, no cliente causa uma exception. O office está instalado no servidor e no cliente. Pode me ajudar? Alguma sugestão? Obrigado! Roberto Vn Compartilhar este post Link para o post Compartilhar em outros sites
quintelab 91 Denunciar post Postado Março 6, 2008 Roberto Bem Vindo ao Fórum Imasters, aproveite e de uma olhada em nossas regras: http://forum.imasters.com.br/index.php?act=Help Referente ao seu problema é necessário saber qual erro esta ocorrendo no servidor, coloque um tratamento de erro para ficar mais fácil. Abraços... Compartilhar este post Link para o post Compartilhar em outros sites
