crystal report 動態產生欄位 -FieldObject

下載 sample pdf

Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportAppServer


'crystal report  動態產生欄位 自動產生你要的rpt
        '1.準備一個空的報表樣本() : standard.rpt  資料庫欄位必須是空的
        '2.準備一個在應用程式中使用資料的 DataSet : DataSet.xsd (這裡採用Push Model)
        '3 要使用的TABLE  NAME :MYTABLE   MYTABLE SCHEMA需定義在 DataSet.xsd裡喔
        '  MYTABLE 共有五個COLUMN : COLUMN1 COLUMN2 ....等會要跑LOOP自動加入報表裡


        Dim tablename As String = "MYTABLE"
        Dim crReport As ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument
        '載入報表樣本
        Dim Path As String = Request.ServerVariables("APPL_PHYSICAL_PATH")
        crReport.Load(Path & "rpt\standard.rpt")


        '利用ClientDoc來變更REPORT內容
        Dim rasClientDocument As ClientDoc.ISCDReportClientDocument
        rasClientDocument = crReport.ReportClientDocument



        'A設定資料表來源位置
        Dim crLogonInfo As New DataDefModel.PropertyBagClass()
        crLogonInfo.Add("File Path ", "c:\mydir\dataset.xsd")
        Dim connProps As New DataDefModel.PropertyBagClass()
        connProps.Add("Database DLL", "crdb_adoplus.dll")
        connProps.Add("QE_DatabaseType", "ADO.NET (XML)")
        connProps.Add("QE_LogonProperties", crLogonInfo)



        '指定連線方式
        Dim connInfo As New DataDefModel.ConnectionInfoClass
        connInfo.Attributes = connProps
        connInfo.Kind = DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE



        '加入table (必須存在A指定的dataset.xsd中)
        Dim table As DataDefModel.Table = New DataDefModel.TableClass
        table.Alias = tablename
        table.Name = tablename
        table.ConnectionInfo = connInfo
        rasClientDocument.DatabaseController.AddTable(table)


        '在報表的細目(detail)加入資料欄位
        '找到細目區段
        Dim DetailSection As ReportDefModel.Section = rasClientDocument.ReportDefinition.DetailArea.Sections(0)
        Dim fieldIndex As Integer
        Dim tableIndex As Integer
        Dim dField As DataDefModel.Field
        Dim dFieldObject As ReportDefModel.FieldObject
        Dim dTable As DataDefModel.Table


        '欄位的字型顏色定義 非必需   standard.rpt中已有預設的字型(由檔案->選項->字型可以查看)
        Dim dFontColor As ReportDefModel.FontColor
        Dim dFont As ReportDefModel.Font
        dFont = New ReportDefModel.FontClass()
        dFont.Size = 11
        dFont.Name = "標楷體"
        dFontColor = New ReportDefModel.FontColorClass()
        dFontColor.Font = dFont


        Dim begpx As Integer = 200 '第一個資料欄位在細目中的位置
        For j As Integer = 1 To 5
            tableIndex = rasClientDocument.Database.Tables.FindByAlias(tablename)
            Dim temp As DataDefModel.ISCRTable = rasClientDocument.Database.Tables(tableIndex)
            dTable = CType(temp, DataDefModel.Table)
            fieldIndex = dTable.DataFields.Find(dTable.DataFields(j - 1).Name, DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, DataDefModel.CeLocale.ceLocaleUserDefault)
            dField = CType(dTable.DataFields(fieldIndex), DataDefModel.Field)
            dFieldObject = New ReportDefModel.FieldObjectClass()
            dFieldObject.Kind = ReportDefModel.CrReportObjectKindEnum.crReportObjectKindField
            dFieldObject.FieldValueType = dField.Type
            dFieldObject.DataSource = dField.FormulaForm
            dFieldObject.FontColor = dFontColor
            dFieldObject.Height = 211
            dFieldObject.Top = 85
            dFieldObject.Left = begpx
            dFieldObject.Width = 300
            begpx += j * 350
            rasClientDocument.ReportDefController.ReportObjectController.Add(dFieldObject, DetailSection, 0)
        Next


        '產生新報表 做為其他用途
        crReport.SaveAs(Path & "myreport.rpt")


        '當然也可以不用產生新報表 可以直接 ASSIGN DATA SOURCE 產生REPORT的結果
        crReport.SetDataSource(mydata)
        crReport.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, True, "Report")

MS SQL TABLE LIST 取得現有 MS SQL 的資料表清單/欄位清單

'取得現有 MS SQL 的資料表清單 (含擴充屬性的描述)

SELECT o.name AS table_name,  ep.value  AS 說明 

  FROM    sys.objects O

       LEFT JOIN

          sys.extended_properties AS ep

       ON o.object_id = ep.major_id

 WHERE     1 = 1

       AND ISNULL (minor_id, 0) = 0

       AND O.name NOT LIKE 'sys%'

       AND O.type = 'U'

ORDER BY O.name

'取得現有 MS SQL 的資料表欄位清單

SELECT b.colorder,

       a.name AS table_name,

       b.name AS FNAME,

       (SELECT convert (VARCHAR, value)

          FROM ::

                  fn_listextendedproperty (NULL,

                                           'user',

                                           'dbo',

                                           'table',

                                           a.name,

                                           'column',

                                           b.name

               ))

          AS DESCRIPT,

       b.xtype,

       (SELECT    TOP 1

               c.name

          FROM systypes c

         WHERE c.xtype = b.xtype)

          AS ctype,

       b.length,

       b.isnullable,

       ''

  FROM sysobjects a, syscolumns b

 WHERE a.id = b.id AND A.type = 'U'

ORDER BY a.name, b.colorder