VB .NET
- Exportando dados do Crystal Reports para o Excel
VB .NET
- Exportando dados do Crystal Reports para o Excel
Este é mais um artigo que escrevo que trata da exportação dos dados do VB .NET para o Excel. A novidade é que irei exportar os dados de um relatório gerado no Crystal Reports.
O exemplo deste artigo foi criado no Visual Studio 2008 pois vamos precisar do Crystal Reports. Vamos precisar também de uma referência ao objeto Excel para podermos trabalhar com uma instância do Excel na aplicação.
Usarei também o banco de dados Northwind.mdf do SQL Server 2005 Express Edition onde irei trabalhar com as tabelas Categories e Products.
Abra o Visual Studio 2008 e crie um novo projeto do tipo Windows Applicaton com o nome Exportar_Para_Excel;
A conexão com o banco de dados será definida na aplicação conforme abaixo:
Para criar a configuração em My.MySettings.Default.SQLConnection1.) Selecione o menu Project; 2.) Selecione Exporta_Para_Excel Properties; 3.) Selecione a Tab Settings; 4.) Digite [SQLConnection] na coluna Name; 5.) Selecione (Connectionstring) na coluna Type; 6.) Selecione o escopo Application; 7.) Defina o valor para a Connectionstring;
 |
Altere o nome do formulário form1.vb para ExportaParaExcel.vb e a partir da ToolBox inclua os seguintes controles no formulário:
a seguir defina o seguinte leiaute no formulário:
Vamos agora incluir uma referência ao Excel; Para isso clique com o botão direito do mouse sobre o nome do projeto e selecione Add Reference;
Na janela Add Reference encontre a referência ao Excel : Microsoft.Office.Interop.Excel selecione-a e clique no botão OK;
Os namespaces usados no projeto deverão estar assim definidos:
Imports
SystemImports
System.IOImports
System.DataImports
System.Data.SqlClientImports
CrystalDecisionsImports
CrystalDecisions.SharedImports
CrystalDecisions.CrystalReports.EngineImports
Microsoft.Office.InteropImports
Microsoft.Office.Interop.Excel
Devemos
também definir variáveis objetos que serão usadas no projeto:
'Cria objeto Command
Private sqlcom As SqlCommand'Cria objeto SQL DataAdapter
Private sqldap As SqlDataAdapter'Cria DataSet Tipado
Private dset As DataSet_Excel'Atribui a conexÆo a vari vel string
Private sqlconstr As String = My.MySettings.Default.SQLConnection'Cria objeto Crystal Report
Private Rpt As Report_ExcelPara facilitar o entendimento eu vou descrever as etapas necessárias para gerar o relatório e exportá-lo para o Excel. Eu não vou dar detalhes de como criar o relatório no Crystal Reports pois o nosso objetivo é a rotina para exportar os dados do relatório.
| Nota: Você pode acompanhar como gerar relatórios no Crystal Reports no artigo: VB.NET - Meu primeiro relatório usando o Crystal Reports e também na minha vídeo aula: VIDEO AULA - Apresentando o Crystal Reports |
Para trabalhar com os dados eu vou criar um DataSet para representar em memória as tabelas Products e Categories:
No menu Project -> Add New Item inclua o template DataSet com o nome DataSet_Excel.xsd e defina o DataSet com as duas tabelas:
A seguir crie um novo relatório com o nome Report_Excel.rpt com o seguinte leiaute:
Onde esta a conexão com o banco de dados ? Ora, esta no arquivo app.Config conforme na seção connectionStrings conforme trecho de código exibido abaixo:
|
<? xml version="1.0" encoding="utf-8" ?>< configuration>< configSections></ configSections>
</ connectionStrings>..... ..... ..... </configuration> |
Agora temos que incluir o código no evento Click do botão Gerar Relatório:
Private Sub btnGerarRelatorio_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGerarRelatorio.Click 'Define o cursor da tela no modo de espera
Me.Cursor = Cursors.WaitCursor
'Define um valor nulo para o Crystal Report Viewer
CrystalReportViewerExport.ReportSource = Nothing
Try
'Define a instânccia SQL Connection na memória
sqlcon = New SqlConnection(sqlconstr)
'Abre a conexão
sqlcon.Open()
'Foi criado uma instância do SqlDataAdapter
'pois ele será usado em dois lugares em uma instância unica
sqldap = New SqlDataAdapter()
'Cria uma instância do Sql Command na memória
sqlcom = New SqlCommand()
With sqlcom
'Define a conexão SQL ativa para o objeto SQL Command atual
.Connection = sqlcon
'Define o tipo do command
.CommandType = CommandType.Text
'Define a consulta SQL
.CommandText = "SELECT CategoryID,CategoryName,Description FROM Categories ORDER BY CategoryID"
End With
'atribui o objeto commando ao SqlDataAdapter.
sqldap.SelectCommand = sqlcom
'define a instância do tipo DataSet_Excel na memória
dset = New DataSet_Excel()
'Carrega a tabela categories
sqldap.Fill(dset.Tables("Categories"))
'Cancela o command SQL , mas temos uma instância do Sql Command na memoria
sqlcom.Cancel()
With sqlcom
'Define a conexão SQL ativa ao objeto SQL Command atual
.Connection = sqlcon
'Define o tipo de command
.CommandType = CommandType.Text
'Defina consulta SQL
.CommandText = "SELECT ProductID,ProductName,CategoryID,UnitPrice,ReorderLevel FROM Products ORDER BY ProductID"
End With
'Atribui o objeto SQL Command ao SqlDataAdapter.
sqldap.SelectCommand = sqlcom
'Carrega a tabela Products
sqldap.Fill(dset.Tables("Products"))
'Cria uma instância do relatório
Rpt = New Report_Excel()
'Atribui o datasoure ao relatório
Rpt.SetDataSource(dset)
'Exibe o relatório
CrystalReportViewerExport.ReportSource = Rpt
'Atualiza o relatório
CrystalReportViewerExport.Refresh()
Catch ex As Exception
'Se ocorreu um erro exibe a mensagem
MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
'cancela e libera o objeto SQL Command
sqlcom.Cancel() : sqlcom.Dispose()
'cancela e libera o objeto SQL Connection
sqlcon.Close() : sqlcon.Dispose()
End Try
'Depois de completar a tarefa o cursor volta ao normal
Me.Cursor = Cursors.Default
End Sub |
A rotina acima já foi comentada de forma que não há necessidade de falar mais nada sobre ela. Executando este código iremos obter o relatório exibido no CrystalReportViewer no formulário:
O relatório contém 9 páginas e vamos agora mostrar as rotinas usadas para exportar o relatório gerando um arquivo CrystalExporta.xls na pasta F:\Dados\.
Nota: você pode escolher outro nome e deve escolher um local diferente para guardar o arquivo Excel gerado;
O código do evento Click do botão Exportar é dado a seguir:
Private Sub btnExportar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportar.Click 'Se o usuário tentar clicar o botão Expotar antes de carregar o relatório irá gerar um erro
'Então validamos
If CrystalReportViewerExport.ReportSource Is Nothing Then
'Se o relatório não foi carreago exibe a menasgem
MessageBox.Show("Tentativa de exportar dados inválidos.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
'Depois de clicar em OK sai
Exit Sub
End If
'Define o modo do cursor em espera Me.Cursor = Cursors.WaitCursor 'Se o tipo de exportação é Crystal Report If RadioButtonCrystal.Checked = True Then 'Chama a procedore deletaArquivo deletaArquivo("F:\dados\CrystalExporta.xls") 'Cria objeto ExportOptions Dim CrExportOptions As ExportOptions 'Cria um novo arquivo de destino Dim CrDiskFileDestinationOptions As New DiskFileDestinationOptions() 'Cria um arquivo do tipo Excel Dim CrFormatTypeOptions As New ExcelFormatOptions Try
'Define onde o aruqivo excel vai ser gerado
CrDiskFileDestinationOptions.DiskFileName = "F:\Dados\CrystalExporta.xls"
'Define opções
CrExportOptions = Rpt.ExportOptions
'Define diversas opções With CrExportOptions 'Define o tipo de destino .ExportDestinationType = ExportDestinationType.DiskFile 'Define o tipo de saida .ExportFormatType = ExportFormatType.Excel 'Define o Destino .DestinationOptions = CrDiskFileDestinationOptions 'Define o formato .FormatOptions = CrFormatTypeOptions End With 'Exporta o relatorio Rpt.Export() Catch ex As Exception 'Se ocorreu um erro exibe a mensagem MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error) Finally 'Depois de completar a tarefa remove a instãncia da memória CrFormatTypeOptions = Nothing CrDiskFileDestinationOptions = Nothing End Try ElseIf RadioButtonExcel.Checked = True Then 'Chama a rotina deletaArquivo deletaArquivo("F:\Dados\CrystalExporta.xlsx") 'Cria objeto excel application Dim xlapp As New Excel.Application 'Cria objeto excel workbook Dim xlbook As Excel.Workbook = Nothing 'Cria objeto excel sheet Dim xlsheets As Excel.Worksheet = Nothing Try
'Inclui um workbook para a excel Application
xlbook = xlapp.Workbooks.Add
'Define o primeiro nome da folha para Category
xlbook.Worksheets.Item(1).Name = "Category"
'Define o segundo nome da folha como Products
xlbook.Worksheets.Item(2).Name = "Products"
'Define a folha Category como folha ativa xlsheets = xlbook.Worksheets(1) 'chama a rotina salvaDadosFolhaCategoria salvaDadosFolhaCategoria(xlbook, xlsheets, dset.Tables("Categories").Rows.Count) 'Define a folha Products como ativa xlsheets = xlbook.Worksheets(2) 'chama a rotina salvaDadosFolhaProdutos salvaDadosFolhaProdutos(xlbook, xlsheets, dset.Tables("Products").Rows.Count) 'Cria o workbook com dadaos xlbook.SaveAs("F:\Dados\CrystalExport") Catch ex As Exception 'se ocorrer um erro avisa MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error) Finally 'Fecha o book Excel xlbook.Close() : xlbook = Nothing 'Encerra o Excel xlapp.Quit() : xlapp = Nothing End Try End If 'Se a exportação terminou então exibe a mensagem MessageBox.Show("Relatório exportado com sucesso.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information) 'retorna o cursor ao normal Me.Cursor = Cursors.Default End Sub |
Neste código temos os dois modos de gerar o arquivo .xls para o Excel. Ele já esta todo comentado.
O código acima usa algumas rotinas que irei mostrar em seguida e que também já estão comentadas:
1- rotina DeletaArquivo:
Private Sub deletaArquivo(ByVal Filename As String) 'Verifica se existe uma pasta
If My.Computer.FileSystem.FileExists(Filename) = True Then
'Se achar então deleta o arquivo
My.Computer.FileSystem.DeleteFile(Filename)
End If
End Sub |
2- Rotina salvaDadosFolhaCategoria :
Private Sub salvaDadosFolhaCategoria(ByRef Cat_WorkBook As Excel.Workbook, ByRef Cal_WorkSheet As Excel.Worksheet, _
ByVal Total As Int32)
'Define o cabeçalho da coluna na folha Category
Cal_WorkSheet.Cells(1, 1) = "Category ID"
Cal_WorkSheet.Cells(1, 2) = "Category"
Cal_WorkSheet.Cells(1, 3) = "Description"
'Formata a coluna Headers
Cal_WorkSheet.Range("A1:C1").Font.Name = "Calibri"
Cal_WorkSheet.Range("A1:C1").Font.Size = 12
Cal_WorkSheet.Range("A1:C1").Font.FontStyle = "Bold"
Cat_WorkBook.Worksheets("Category").Columns("A:A").ColumnWidth = 11.43
Cat_WorkBook.Worksheets("Category").Columns("B:B").ColumnWidth = 30
Cat_WorkBook.Worksheets("Category").Columns("C:C").ColumnWidth = 60
' -----------------------------------------------------------------------
'Cria objeto excel range
Dim xlrange As Excel.Range
For index As Integer = 0 To Total - 1
'Define os valores nas células
Cal_WorkSheet.Cells((index + 2), 1) = dset.Tables("Categories").Rows(index)("CategoryID").ToString()
Cal_WorkSheet.Cells((index + 2), 2) = dset.Tables("Categories").Rows(index)("CategoryName").ToString()
Cal_WorkSheet.Cells((index + 2), 3) = dset.Tables("Categories").Rows(index)("Description").ToString()
Next
'Formata intervalo de datas
xlrange = Cal_WorkSheet.Range("A2", ("A" + (Total + 1).ToString())) _
: xlrange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter _
: xlrange.NumberFormat = "#"
xlrange = Cal_WorkSheet.Range("B2", ("B" + (Total + 1).ToString())) _
: xlrange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
xlrange = Cal_WorkSheet.Range("C2", ("C" + (Total + 1).ToString())) _
: xlrange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
' -----------------------------------------------------------------------
'Define o atributo autofit para ativar as colunas
Cal_WorkSheet.Range("A1:C1").EntireColumn.AutoFit()
End Sub
|
3- Rotina salvaDadosFolhaProdutos:
Private Sub salvaDadosFolhaProdutos(ByRef Prd_WorkBook As Excel.Workbook, ByRef Prd_WorkSheet As Excel.Worksheet, _
ByVal Total As Int32)
'Define o cabeçalho da coluna na folha Products
Prd_WorkSheet.Cells(1, 1) = "Product ID"
Prd_WorkSheet.Cells(1, 2) = "Product"
Prd_WorkSheet.Cells(1, 3) = "Unit Price"
Prd_WorkSheet.Cells(1, 4) = "Reorder Level"
'Formata o cabeçalho da coluna
Prd_WorkSheet.Range("A1:D1").Font.Name = "Calibri"
Prd_WorkSheet.Range("A1:D1").Font.Size = 12
Prd_WorkSheet.Range("A1:D1").Font.FontStyle = "Bold"
Prd_WorkBook.Worksheets("Products").Columns("A:A").ColumnWidth = 11.43
Prd_WorkBook.Worksheets("Products").Columns("B:B").ColumnWidth = 60
Prd_WorkBook.Worksheets("Products").Columns("C:C").ColumnWidth = 12
Prd_WorkBook.Worksheets("Products").Columns("D:D").ColumnWidth = 14
' -----------------------------------------------------------------------
'Cria um objeto range
Dim xlrange As Excel.Range
'Cria uma variavel boolean para achar valores zeros
Dim iszerofound As Boolean = False
For index As Integer = 0 To Total - 1
'Atribui os valores as células
Prd_WorkSheet.Cells((index + 2), 1) = dset.Tables("Products").Rows(index)("ProductID").ToString()
Prd_WorkSheet.Cells((index + 2), 2) = dset.Tables("Products").Rows(index)("ProductName").ToString()
Prd_WorkSheet.Cells((index + 2), 3) = dset.Tables("Products").Rows(index)("UnitPrice").ToString()
'verifica valores zero
If Convert.ToInt32(dset.Tables("Products").Rows(index)("ReorderLevel")) <= 0 Then
iszerofound = True
Else
iszerofound = False
End If
Prd_WorkSheet.Cells((index + 2), 4) = Convert.ToInt32(dset.Tables("Products").Rows(index)("ReorderLevel")).ToString("00")
'Se um valor zero foi achado format e destaca a linha
If iszerofound = True Then
Prd_WorkSheet.Range("A" + (index + 2).ToString + ":D" + (index + 2).ToString).Font.Name = "Calibri"
Prd_WorkSheet.Range("A" + (index + 2).ToString + ":D" + (index + 2).ToString).Font.Size = 12
Prd_WorkSheet.Range("A" + (index + 2).ToString + ":D" + (index + 2).ToString).Font.FontStyle = "Bold"
Prd_WorkSheet.Range("A" + (index + 2).ToString + ":D" + (index + 2).ToString).Font.Color = RGB(255, 49, 49)
Prd_WorkSheet.Range("A" + (index + 2).ToString + ":D" + (index + 2).ToString).Interior.Color = RGB(208, 226, 171)
End If
Next 'Formata o intervalo da data
xlrange = Prd_WorkSheet.Range("A2", ("A" + (Total + 1).ToString())) _
: xlrange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter _
: xlrange.NumberFormat = "#"
xlrange = Prd_WorkSheet.Range("B2", ("B" + (Total + 1).ToString())) _
: xlrange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
xlrange = Prd_WorkSheet.Range("C2", ("C" + (Total + 1).ToString())) _
: xlrange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight _
: xlrange.NumberFormat = "#,##0.00"
xlrange = Prd_WorkSheet.Range("D2", ("D" + (Total + 1).ToString())) _
: xlrange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter _
: xlrange.NumberFormat = "##"
' -----------------------------------------------------------------------
'Define o atributo autofit para ativar as colunas
Prd_WorkSheet.Range("A1:D1").EntireColumn.AutoFit()
End Sub |
Executando o projeto e gerando o relatório e exportando para o Excel iremos obter o arquivo CrystalExporta.xls que pode ser aberto no Excel . Veja a seguir o resultado:
 |
 |
Veja que com um pouco criatividade conseguimos gerar e exportar relatórios para o Excel sem muito esforço.
Pegue o projeto completo aqui:
Export_To_Excel.zip
Até mais ver...
referências:
José Carlos Macoratti