crystal report 動態變更線條 LineObject

 'crystal report  動態變更線條  LineObject

       Dim crReport1 As ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument

        '載入報表
        Dim Path As String = Request.ServerVariables("APPL_PHYSICAL_PATH")
        crReport1.Load(Path & "rpt\STANDARD.rpt")

        '找到已存在的線條物件
        Dim Line As LineObject = crReport1.ReportDefinition.ReportObjects("Line1")

        '隱藏線條
        'Line.ObjectFormat.EnableSuppress = True
        '變更線條樣式
        Line.LineStyle = LineStyle.SingleLine
        '變更線條位置
        Line.Left = 100
       '變更線條顏色
        Line.LineColor = Drawing.Color.Red

        '

        '

crystal report 動態變更文字欄位 TextObject

'crystal report  動態變更文字欄位  TextObject

  Dim crReport1 As ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument

        '載入報表
        Dim Path As String = Request.ServerVariables("APPL_PHYSICAL_PATH")
        crReport1.Load(Path & "rpt\STANDARD.rpt")

   '找到已存在的文字欄位

        Dim txtobj As TextObject

        txtobj = crReport1.ReportDefinition.ReportObjects("TEXT1")

        txtobj.Text = "變更後內容"

        txtobj.Height = 285

        txtobj.Top = 1985

        txtobj.Left = 300

        txtobj.Width = 2000

        txtobj.ObjectFormat.EnableCanGrow = True

crystal report 動態變更公式欄位 FormulaFields

'crystal report  動態變更公式 FormulaFields

Dim crReport1 As ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument

        '載入報表
        Dim Path As String = Request.ServerVariables("APPL_PHYSICAL_PATH")
        crReport1.Load(Path & "rpt\STANDARD.rpt")

        '找到公式欄位
        Dim fieldObj As FormulaFieldDefinition
        fieldObj = crReport1.DataDefinition.FormulaFields("公式1")

        '變更公式內容
        fieldObj.Text = "{TABLE.COLUMNA}"

gzip

Imports System.IO

Imports System.IO.Compression

 

             

  Response.Filter = New GZipStream(Response.Filter, CompressionMode.Compress)

  Response.AppendHeader("Content-Encoding", "gzip")

  Response.Write("yourdata")

  Response.Flush()

crystal report 動態產生文字標題欄位 TextObject

'準備一個報表樣本() : standard.rpt

        '載入報表樣本
        Dim crReport As ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument
        Dim Path As String = Request.ServerVariables("APPL_PHYSICAL_PATH")
        crReport.Load(Path & "rpt\standard.rpt")
        Dim rasClientDocument As ClientDoc.ISCDReportClientDocument = crReport.ReportClientDocument
        '找到pagehead(頁首)區段
        Dim pageSection As CrystalDecisions.ReportAppServer.ReportDefModel.Section = rasClientDocument.ReportDefinition.PageHeaderArea.Sections(0)

        '在報表的pagehead(頁首)區段加入文字標題欄位 create TextObject
        Dim txtObject As CrystalDecisions.ReportAppServer.ReportDefModel.TextObject
        Dim txtObjectParagraph As ReportDefModel.Paragraph
        Dim txtObjectText As ReportDefModel.ParagraphTextElement

        '欄位的字型顏色定義
        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

        txtObject = New CrystalDecisions.ReportAppServer.ReportDefModel.TextObject
        txtObject.Name = "text1"
        txtObject.Height = 285
        txtObject.Top = 1985
        txtObject.Left = begpx
        txtObject.Width = textbox1.Text.Trim.Length * 300  '變數 要加入的文字內容的寬度
        txtObject.Format.EnableCanGrow = True

        '處理Text objects中的文字內容
        txtObjectParagraph = New ReportDefModel.Paragraph
        txtObjectText = New ReportDefModel.ParagraphTextElement
        txtObjectText.Text = textbox1.Text.Trim '變數 要加入的文字內容
        txtObjectParagraph.ParagraphElements.Add(txtObjectText)
        txtObjectParagraph.FontColor = dFontColor
        txtObject.Paragraphs.Add(txtObjectParagraph)

        '加入Text objects
        rasClientDocument.ReportDefController.ReportObjectController.Add(txtObject, pageSection, 0)

ASP.NET DataTable 常用的操作

ASP.NET DataTable 常用的操作


Imports System.Data

        '以下程式順序無直接關連性

       '建立DataTable
        Dim dt As DataTable
        dt.Columns.Add("CODE")
        dt.Columns.Add("CODE_NAME")
        dt.Columns.Add("RATE", GetType(Double))
        dt.Columns.Add("TOT_AMT", GetType(System.Decimal))
        dt.TableName = "測試資料"

        '新增一筆資料
        Dim newRow As DataRow = dt.NewRow()
        newRow("CODE") = "A"
        newRow("CODE_NAME") = "促銷方案A"
        newRow("RATE") = 0.5
        newRow("TOT_AMT") = 3000
        dt.Rows.Add(newRow)

        '設定PrimaryKey
        dt.PrimaryKey = New DataColumn() {dt.Columns("CODE")}
        '尋找該代碼SOMECODE 所代表的名稱 ,若找不到會傳回Null 值
        Dim 代碼名稱 As String = dt.Rows.Find("SOMECODE").Item("CODE_NAME").ToString()


        '衍伸性欄位
        '新增一個欄位"分數"  將欄位"分數文字" (文字型態的欄位) 轉成數字型態
        Dim cola As DataColumn = New DataColumn("分數", GetType(System.Double))
        cola.Expression = "Convert(分數文字, 'System.Double')"
        dt.Columns.Add(cola)

        '新增一個欄位 "性别說明" 將欄位"性别"  轉換成中文說明
        Dim colb As DataColumn = New DataColumn("性别說明")
        colb.Expression = " iif(性别='F','女性','男性') "
        dt.Columns.Add(colb)

        '新增一個欄位 "付款年月" 將欄位"PAY_DATE" 轉換成年月
        Dim colc As DataColumn = New DataColumn("付款年月")
        colc.Expression = "  Substring(PAY_DATE,1,3) + Substring(PAY_DATE,5,2) "
        dt.Columns.Add(colc)


        '新增自動編號的欄位
        Dim dcID As DataColumn = New DataColumn("編號", GetType(System.Int32))
        dcID.AutoIncrement = True
        dcID.AutoIncrementSeed = 1
        dcID.AutoIncrementStep = 1
        dt.Columns.Add(dcID)


        'DataTable 逐列 變更內容或讀取內容
        For Each dr As DataRow In dt.Rows
            dr("AAA") = ""
            dr("CCC") = dr("AAA") + dr("BBB")
        Next


        'DataTable 尋找資料性别為F的資料 ,將結果用字串顯示
        Dim dr_Select As DataRow()
        dr_Select = dt.Select("性别='F'")
        Dim allname As String = ""
        For i As Integer = 0 To dr_Select.Length - 1
            allname += dr_Select(i).Item("名字") + ","
        Next
        allname = allname.TrimEnd(",")

        'DataTable aggregate functions
        '筆數統計 COUNT
        Dim totalrROW As Integer = dt.Compute("COUNT(1)", "CODE IN ('A','B') ")

        '金額小計 SUM
        Dim totalFee As Double = dt.Compute("SUM(TOT_AMT) + SUM(EXT_AMT) ", "CODE IN ('A','B') ")


        'DataTable distinct
        Dim columns As String() = {"COLUMN_A", "COLUMN_B"}
        Dim dtDist1 As DataTable = dt.DefaultView.ToTable(True, columns)
        '或
        Dim dtDist2 As DataTable = dt.DefaultView.ToTable(True, New String() {"COLUMN_A", "COLUMN_A"})


        'DataTable delete
        '將找到的資料逐筆刪除
        Dim xRows As DataRow()
        xRows = dt.Select("CODE IN ('A','B')")
        For Each item As DataRow In xRows
            dt.Rows.Remove(item)
        Next


        '將DataTable 使用指定的檔案 將目前的內容寫成 XML
        dt.WriteXml("C:\TEMP\" & dt.TableName & ".xml")

        '目前的資料結構撰寫成 XML 結構描述，寫入至指定的檔案。
        dt.WriteXmlSchema("C:\TEMP\dataset.xsd")

MS SQL INDEX LIST 取得現有 MS SQL 的資料表的index清單

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

SELECT TBL.NAME AS TABLE_NAME,
        I.NAME AS INDEX_NAME,
        I.INDEX_ID,
        (CASE IC.IS_INCLUDED_COLUMN
            WHEN 1 THEN IC.INDEX_COLUMN_ID
            ELSE IC.KEY_ORDINAL
         END)
           AS [ID],
        CLMNS.NAME AS COLUMN_NAME,
        CLMNS.COLUMN_ID AS COLUMN_POSITION,
        IC.IS_DESCENDING_KEY AS DESCEND , I.IS_UNIQUE
   FROM          SYS.TABLES AS TBL
              INNER JOIN
                 SYS.INDEXES AS I
              ON (I.OBJECT_ID = TBL.OBJECT_ID)
           INNER JOIN
              SYS.INDEX_COLUMNS AS IC
           ON (IC.COLUMN_ID > 0
               AND (   IC.KEY_ORDINAL > 0
                    OR IC.PARTITION_ORDINAL = 0
                    OR IC.IS_INCLUDED_COLUMN != 0))
              AND (IC.INDEX_ID = CAST (I.INDEX_ID AS INT)
                   AND IC.OBJECT_ID = I.OBJECT_ID)
        INNER JOIN
           SYS.COLUMNS AS CLMNS
        ON CLMNS.OBJECT_ID = IC.OBJECT_ID AND CLMNS.COLUMN_ID = IC.COLUMN_ID
  WHERE 1 = 1
 --(I.NAME='???')
 --AND (TBL.NAME=???'  )
 ORDER BY TBL.NAME , INDEX_ID ASC , [ID]

crystal report 浮水印(watermark) 簽章(STAMP)

下載 sample pdf

下載 sample pdf

crystal report 浮水印(watermark) 簽章(STAMP)

準備兩個當浮水印圖檔
A.confidential.wmf 背景透通
B.confidential.jpg 沒有透通

'步驟
1.在頁首a放入這兩張圖,頁首a的區段專家"放置在隨後區段"勾選起來
2.將頁首a高度拉大,圖檔移至頁首a的最下方(這樣浮水印就會出現在頁面靠中間的位置))


如果報表中沒有線條這兩種圖檔呈現出的結果是一樣的
但是如果報表中有線條

3.在細目中插入藍色線條
4.在細目中插入文字物件,但是這個文字物件不放入任何文字,且將它拉的和藍線一樣長
   並將文字物件的"框線"-"線條樣式"-"上"設定成單線,將"色彩"設定為綠色

設計畫面 左圖為A 右圖為B

預覽畫面

此時預覽結果發現 兩張圖都被綠線壓過 , A圖以透通的方式顯示藍線,
B圖藍線完全被蓋掉


結論
以浮水印的需求 (浮水印應該是在最下層)
A/B圖檔都可以但需使用非一般的線條,要用文字物件的框線也就是綠線


以簽章(STAMP)的需求,例如收據章 (圖案應該是在最上層)
若有壓到"方塊"或"線條",則只有A圖檔可以

收據章範例

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

regex

'移除HTML TAG
Regex.Replace(String, "<.*?>", "")
'移除 (.)
Regex.Replace(String.Split("-")(1), "\((.+)\)", "") 
'連續3個以上4
Regex.IsMatch((String, "4{3,}")

'身分證字號格式
Regex.IsMatch(idno, "^([a-zA-Z]+\d+|\d+[a-zA-Z]+)[a-zA-Z0-9]*$")


Regex("\(([\u4e00-\u9fa5]+)\)")
Regex.IsMatch(String, "NAME|DESC|RMK|ORIGIN|CONTENT", RegexOptions.IgnoreCase)

ASP.NET GRIDVIEW

<asp:TemplateField HeaderText="head"  HeaderStyle-Wrap="False">
                            <ItemTemplate>
                             <asp:LinkButton id="field2" runat="server" cssclass="pathLink" Text='<%# Eval("field2") %>'  CommandName="commA" CausesValidation="False" CommandArgument='<%# Eval("field1", "field1={0}") + "&" + Eval("field2", "field2={0}")  %>' ></asp:LinkButton>
                            </ItemTemplate>
</asp:TemplateField>




 <asp:TemplateField HeaderText="標題" HeaderStyle-Wrap="false">
        <ItemTemplate>
           <%#Eval("field1").insert(3, ".")%>  <%#LEFT(Eval("field1"),3) %>
          <asp:Label ID="Hyphen" runat="server" Text="-" Visible='<%# Eval("field2") = "01" %>' >
        </ItemTemplate>
    </asp:TemplateField>