使用 SQL 指令執行報表

1. 使用SQL指令執行報表 不需設定Crystal Report連線

15. Imports System.Data.SqlClient Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared ‘轉OFFICE需要

16. Dim objRpt As New CrystalReport1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cnn As SqlConnection Dim connectionString As String Dim sql As String connectionString = "data source=MYCHAT-57ED6516;initial catalog=pubs;user id=abc;password=123;" cnn = New SqlConnection(connectionString) cnn.Open() sql = procesSQL() Dim dscmd As New SqlDataAdapter(sql, cnn) Dim ds As New DataSet1 dscmd.Fill(ds, "authors") objRpt.SetDataSource(ds.Tables(1)) CrystalReportViewer1.ReportSource = objRpt CrystalReportViewer1.Refresh() End Sub

17. Public Function procesSQL() As String Dim sql As String Dim inSql As String Dim firstPart As String Dim lastPart As String Dim selectStart As Integer Dim fromStart As Integer Dim fields As String() Dim i As Integer Dim MyText As TextObject inSql = TextBox1.Text inSql = inSql.ToUpper selectStart = inSql.IndexOf("SELECT") fromStart = inSql.IndexOf("FROM") selectStart = selectStart + 6 firstPart = inSql.Substring(selectStart, (fromStart - selectStart)) lastPart = inSql.Substring(fromStart, inSql.Length - fromStart)

18. fields = firstPart.Split(",") firstPart = "" For i = 0 To fields.Length - 1 If i > 0 Then firstPart = firstPart & " , " _ & fields(i).ToString() & " AS COLUMN" & i + 1 MyText = CType(objRpt.ReportDefinition.ReportObjects("Text" _ & i + 1), TextObject) MyText.Text = fields(i).ToString() Else firstPart = firstPart & fields(i).ToString() & _ " AS COLUMN" & i + 1 MyText = CType(objRpt.ReportDefinition.ReportObjects("Text" & _ i + 1), TextObject) MyText.Text = fields(i).ToString() End If Next sql = "SELECT " & firstPart & " " & lastPart Return sql End Function

20. 使用SQL指令執行報表 需設定Crystal Report連線

27. Imports System.Data.SqlClient Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared ‘轉OFFICE需要

28. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cnn As SqlConnection Dim connectionString As String Dim sql As String connectionString = "data source=MYCHAT-57ED6516;initial catalog=pubs;user id=abc;password=123;" cnn = New SqlConnection(connectionString) cnn.Open() sql = "select au_id,au_lname,au_fname from authors where au_lname like '%a%'" Dim dscmd As New SqlDataAdapter(sql, cnn) Dim ds As New DataSet2 dscmd.Fill(ds, "authors") MsgBox(ds.Tables(1).Rows.Count) cnn.Close() Dim objRpt1 As New CrystalReport2 objRpt1.SetDataSource(ds.Tables(1)) CrystalReportViewer1.ReportSource = objRpt1 CrystalReportViewer1.Refresh() End Sub

29. 轉EXCEL Try Dim CrExportOptions As ExportOptions Dim CrDiskFileDestinationOptions As New _ DiskFileDestinationOptions() Dim CrFormatTypeOptions As New ExcelFormatOptions CrDiskFileDestinationOptions.DiskFileName = "c:\crystalExport.xls" CrExportOptions = objRpt1.ExportOptions With CrExportOptions .ExportDestinationType = ExportDestinationType.DiskFile .ExportFormatType = ExportFormatType.Excel .DestinationOptions = CrDiskFileDestinationOptions .FormatOptions = CrFormatTypeOptions End With objRpt1.Export() MessageBox.Show("export to EXCEL succeed!") Catch ex As Exception MsgBox(ex.ToString) End Try

30. 轉PDF Try Dim CrExportOptions As ExportOptions Dim CrDiskFileDestinationOptions As New _ DiskFileDestinationOptions() Dim CrFormatTypeOptions As New PdfRtfWordFormatOptions() CrDiskFileDestinationOptions.DiskFileName = "c:\crystalExport.pdf" CrExportOptions = objRpt1.ExportOptions With CrExportOptions .ExportDestinationType = ExportDestinationType.DiskFile .ExportFormatType = ExportFormatType.PortableDocFormat .DestinationOptions = CrDiskFileDestinationOptions .FormatOptions = CrFormatTypeOptions End With objRpt1.Export() MessageBox.Show("export to PDF succeed!") Catch ex As Exception MsgBox(ex.ToString) End Try

31. 轉WORD Try Dim CrExportOptions As ExportOptions Dim CrDiskFileDestinationOptions As New _ DiskFileDestinationOptions() Dim CrFormatTypeOptions As New PdfRtfWordFormatOptions CrDiskFileDestinationOptions.DiskFileName = "c:\aa.doc" CrExportOptions = objRpt1.ExportOptions With CrExportOptions .ExportDestinationType = ExportDestinationType.DiskFile .ExportFormatType = ExportFormatType.WordForWindows .DestinationOptions = CrDiskFileDestinationOptions .FormatOptions = CrFormatTypeOptions End With objRpt1.Export() MessageBox.Show("export to WORD succeed!") Catch ex As Exception MsgBox(ex.ToString) End Try

32. 轉XML Dim cnn As SqlConnection Dim connectionString As String Dim sql As String connectionString = "data source=MYCHAT-57ED6516;initial catalog=pubs;user id=sa;password=rc890830;" cnn = New SqlConnection(connectionString) cnn.Open() sql = "select au_id,au_lname,au_fname from authors" Dim dscmd As New SqlDataAdapter(sql, cnn) Dim ds As New DataSet2 dscmd.Fill(ds, "authors") ds.WriteXml("c:\abc.xml") cnn.Close() MessageBox.Show("export to XML succeed!")

33. 指定CrystalReport上物件的資料 Dim a As TextObject a = objRpt1.ReportDefinition.ReportObjects.Item("Text1") ' 指到crystalreport上的Text1物件 a.Text = "hahaha" ' 指定crystalreport上Text1的內容

34. CrystalReport的使用 Dim cryRpt As New ReportDocument Dim a As TextObject Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click cryRpt.Load("C:\Documents and Settings\Administrator\桌面\report\report\CrystalReport4.rpt") a = cryRpt.ReportDefinition.ReportObjects.Item("Text2") ' 指到crystalreport4上的Text2物件 a.Text = "hahaha" ' 指定crystalreport4上Text2的內容 CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub

35. 插入圖表

36. 插入圖表

37. 插入圖表

38. 插入圖表

39. 插入圖表