Gerando gráficos no Excel

ASP.NET 2.0 - Gerando gráficos no Excel

O Excel é uma ferramenta poderosa para análise de dados, e, uma das tarefas mais comuns hoje em dia é o manuseamento de documentos Excel. Quer você goste ou não o Excel reina absoluto na maioria das empresas para armazenamento de informações empresariais.

Neste tutorial vamos mostrar como podemos interagir com o Excel selecionado dados de planilhas e usando os recursos gráficos do Excel em páginas ASP.NET via linguagem VB.NET.

Uma visão geral do problema

Vamos preparar uma planilha Excel (Eu estou usando a versão 2000 em português) usando os dados disponíveis da tabela de vendas bimestrais para um empresa. Vou usar só cinco vendedores para facilitar a digitação dos dados. Vamos então criar as planilhas no Excel...

Irei criar um arquivo Excel chamado Vendas.xls e neste arquivo criar três planilhas de dados para os 5 primeiros colocados das vendas Bimestrais de uma empresa hipotética.

Assim teremos:

Uma planilha chamada Vendedores onde iremos selecionar os 5 primeiros vendedores e atribuir um código a cada um deles. (O código pode ser qualquer número inteiro);
Uma planilha chamada Janeiro que mostra as vendas para o mês de Janeiro;
Uma planilha chamada Fevereiro que mostra as vendas para o mês de Fevereiro;


Vendedores	Janeiro	Fevereiro

Abaixo temos uma visão geral da planilha Vendas.xls:

Interagindo com o Excel

Para interagir com o Excel devemos usar objetos COM e efetuar a referência destes objetos em nosso projeto. Para tratar com os arquivos Excel como se fossem tabelas podemos usar a namespace System.Data.OleDb de modo a pode usar consultas SQL para obter dados das planilhas. Vamos criar nosso site ASP.NET e referenciar os objetos necessários para interagir com o Excel.

Nota: Fazendo uma analogia com um banco de dados, o arquivo Excel Vendas.xls (WorkBook) pode ser considerado o banco de dados enquanto as planilhas: Vendedores, Janeiro e Fevereiro podem ser consideradas as tabelas.

Abra o Visual Web Developer Express Edition e crie um novo web site no menu File -> New Web Site.. com o nome de relatorioExcel.

Na janela Solution Explorer clique com o botão direito do mouse sobre o nome do projeto e selecione a opção Add Reference...

Na janela Add Reference selecione a aba COM e em seguida o componente Excel instalado na sua máquina. No meu caso é o Microsoft Excel 10.0 Object Library. Clique no botão OK.

Criando a interface para obter os dados das planilhas

Nossa primeira tarefa será criar uma página web que servirá como interface com o usuário de modo a obter os dados de cada uma das planilhas criadas no arquivo Excel Vendas.xls.

Vamos alterar o nome do arquivo Default.aspx para lerDadosExcel.aspx. Para fazer isto clique sobre o nome do arquivo e selecione a opção Rename fornecendo o novo nome.

Selecione o modo Design e inclua um controle Label e altere sua propriedade Text para : Procurar por:

A seguir inclua um controle DropDownList, altere seu ID para ddlExcel e selecione a opção : Edit Items e informe os itens : Vendedores,Janeiro e Fevereiro conforme figura abaixo:

A seguir inclua um controle Button e altere seu ID para btnProcurar e sua propriedade Text para Procurar.

Finalmente inclua um controle GridView e altere seu ID para gdvExcel e selecione a opção Auto Format que mais lhe convier:

Como vamos ter que acessar as planilhas do Excel teremos que efetuar uma conexão com cada planilha usando uma string de conexão como para acessar as tabelas de um banco de dados.

Para isto vamos incluir no arquivo web.config , na seção <configuration> a string de conexão que vamos usar para efetuar a conexão com o Excel:

</connectionStrings>

.....

Para que a string de conexão funcione vamos ter que incluir o arquivo Vendas.xls na pasta App_Data da aplicação. Selecione este item na janela Solution Explorer e clique com o botão direito do mouse selecionando a opção Add Existeng Item e a seguir procure pelo arquivo Excel e o inclua na pasta. Neste momento o seu projeto deverá possuir as seguintes referências e arquivos: (Eu poderia usar o arquivo em outro local mas teria que mudar a string de conexão)

A seguir vamos incluir, via code-behind, o código abaixo no evento Click do botão de comando Procurar:

Imports System.Data.oledb

Imports System.data

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub btnProcurar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnProcurar.Click

Try

'obtendo a string de conexão do arquivo web.config

Dim conexaoExcel As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("conexaoExcel").ToString()

Dim dbConn As New OleDbConnection(conexaoExcel)

Dim strSQL As String

'montando a instrução SQL para obter os dados da planilha selecionada na caixa de listagem ddlExcel

strSQL = "SELECT * FROM [" & ddlExcel.SelectedItem.ToString() & "$]"

dbConn.Open()

'Executando a instrução SQL e criando um DataSet para receber os dados

Dim cmd As New OleDbCommand(strSQL, dbConn)

Dim dsExcel As New DataSet

Dim daExcel As New OleDbDataAdapter(cmd)

'preenchendo o DataSet e exibindo os dados no gridView

daExcel.Fill(dsExcel)

gdvExcel.DataSource = dsExcel

gdvExcel.DataBind()

Catch ex As Exception

Throw ex

End Try

End Sub

End Class

O código acima dispensa maiores comentários, estamos apenas usando a string de conexão gravada no arquivo web.config e montando a instrução SQL com base na seleção feita pelo usuário no dropdownlist, ddlExcel, esta seleção fornece o nome da planilha de onde iremos extrair os dados.

A seguir criamos um objeto DataSet (dsExcel) e apos executar o comando SQL preenchemos o DataSet com os dados obtidos e exibimos no controle GridView.

Abaixo temos um exemplo de resultado da execução da página:

Criando a interface para gerar os gráficos Excel

Vamos criar agora a página web que será a interface a partir da qual o usuário poderá gerar relatórios Excel.

Vamos incluir um novo formulário web no projeto. Na janela Solution Explorer clique com o botão direito no nome do projeto e selecione a opção Add New Item. A seguir informe o nome do formulário como gerarRelatorio.aspx e marque a opção - Place code in separate file. Clique no botão Add.

A seguir selecione o arquivo gerarRelatorio.aspx na janela Solution Explorer e no modo Design inclua 3 controles Buttons e um controle GridView conforme o leiaute abaixo:

Define as seguintes propriedades para os controles:

Button 1 - Text = Gerar Relatorio - ID = btnGerarRelatorio

GridView - ID = dgvRelatorio (Auto Format = Classic)

Button2 - Text = Exportar para Excel - via Response - ID= btnExcelResponse

Button3 - Text = Exportar para XLS usando objeto Excel - ID= btnExcelObjects

Vamos incluir o código via code-behind no arquivo gerarRelatorio.aspx.vb. De início necessitamos declarar os namespaces usados no projeto:

Imports System.Data.OleDb
Imports System.Data
Imports System.Reflection

A seguir vamos incluir o código abaixo no evento Click do botão - Gerar Relatório :

Protected Sub btnGerarRelatorio_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGerarRelatorio.Click

Try

dgvRelatorio.DataSource = obterDados()

dgvRelatorio.DataBind()

Catch ex As Exception

Throw ex

End Try

End Sub

Neste código eu estou invocando a rotina obterDados() e atribuindo o resultado (um dataset) para exibição no controle GridView da página web.

Vejamos então a seguir o código da rotina obterDados():

Private Function obterDados() As DataSet

'obtém a string de conexão do arquvo web.config

Dim conexao As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("conexaoExcel").ToString()

Dim dbConn As New OleDbConnection(conexao)

Dim strSQL As String

'definindo a instrução SQL que obterá os dados das 3 planilhas
strSQL = "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"

dbConn.Open()

'executando o comando SQL e preenchendo o datatset com o resultado obtido
Dim cmd As New OleDbCommand(strSQL, dbConn)

Dim dsExcel As New DataSet

Dim daExcel As New OleDbDataAdapter(cmd)

daExcel.Fill(dsExcel)

' Retornado o dataset

Return dsExcel

End Function

No código acima eu vou comentar apenas a instrução SQL que foi montada de forma a obter os dados das 3 planilhas do arquivo Vendas.xls:

strSQL = "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"

A instrução SELECT - SELECT V.Codigo, V.Vendedores, J.Vendas, F.Vendas, (J.Vendas+F.Vendas) AS Total

Seleciona o Codigo e os Vendedores da tabela Vendedores, as Vendas da planilha Janeiro e Fevereiro e define um campo chamado Total representando a soma das vendas das duas planilhas.

A instrução - FROM [Vendedores$] V, [Janeiro$] J, [Fevereiro$] F

Determina as planilhas e o aliás usado para identificar as planilhas : V para a planilha Vendedores, J para Janeiro e F para Fevereiro

A instrução - WHERE(V.Codigo = J.Codigo And V.Codigo = F.Codigo)

selecione os valores quando o código das tabelas forem idênticos de forma a obter todos os dados das planilhas.

A instrução - "ORDER BY (J.Vendas+F.Vendas) DESC"

Apenas o resultado por ordem do campo Total em ordem Decrescente.

Executando a página e clicando no botão Gerar Relatório iremos obter o seguinte resultado:

Vejamos agora o código do botão - Exportar para o Excel - via Response:

Protected Sub btnExcelResponse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcelResponse.Click

'declaração de variáveis

Dim tw As New System.IO.StringWriter()

Dim hw As New System.Web.UI.HtmlTextWriter(tw)

Dim dgGrid As New DataGrid()

dgGrid.DataSource = obterDados()

'cabeçalho do relatorio

hw.WriteLine(" Relatório - Vendas ")

' Obtem o HTML para o controle

dgGrid.HeaderStyle.Font.Bold = True

dgGrid.DataBind()

dgGrid.RenderControl(hw)

' Escreve o HTML de volta no navegador

Response.ContentType = "application/vnd.ms-excel"

Me.EnableViewState = False

Response.Write(tw.ToString())

Response.End()

End Sub

Este código estamos enviando via objeto Response o resultado obtido para o Excel.

Criamos um objeto do tipo StringWriter, qual armazenará a saída do controle.

Estamos usando também a classe HtmlTextWriter que grava caracteres de marcação e texto em servidor ASP.NET em um fluxo de saída de controle. Essa classe fornece recursos de formatação que controles de servidor ASP.NET usar ao processamento de marcação para clientes.

Note que usamos o contentType igual a : "application/vnd.ms-excel" e que criarmos um controle DataGrid para exibir o resultado. Abaixo temos o resultado do processamento:

Finalmente vamos ao código do botão - Exportar para XLS usando objeto Excel - que irá gerar o gráfico Excel com base nos dados das planilhas:

Protected Sub btnExcelObjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcelObjects.Click

Try

Dim xlWorkBook As Excel.Workbook

Dim xlWorkSheet As Excel.Worksheet

'define os objetos de trabalho para o Excel

xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)

xlWorkBook.Application.Visible = True

xlWorkSheet = xlWorkBook.ActiveSheet

'Obtem o dataset contendo os dados

Dim dsData As DataSet = obterDados()

Dim i As Integer = 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

' saida dos dados

For Each dr As DataRow In dsData.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 proxima linha

i = i + 1

' define formatação das colunas

xlWorkSheet.Columns.AutoFit()

' gera o grafico

Dim chart As Excel.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"

End With

Catch ex As Exception

Throw ex

End Try

End Sub

Para desenvolver soluções que usam o Microsoft Office Excel, você pode interagir com os objetos fornecidos pelo modelo de objeto do Excel.
Na maioria das vezes, o modelo de objeto diretamente emula a interface do usuário. Assim, o objeto Application representa o aplicativo inteiro, e cada objeto Workbook contém uma coleção de objetos Worksheet. A representação das células é feita pelo objeto Range , que permite que você trabalhe com células individuais ou grupos de células.

Ao executar o código para geração do gráfico para este exemplo iremos obter:

Pegue o projeto completo aqui : relatorioExcel.zip

Sem necessitar de muita prática e nem usar muito código acessamos dados de planilhas do Excel e geramos gráficos com base nos dados obtidos.

Eu sei é apenas ASP.NET, mas eu gosto...

José Carlos Macoratti