亂馬客

前言

一般的Application如果有使用到Report的話，因為顯示報表的行為都一致，所以都會包一個共用的Report Viewer的程式! 目前我們有個案子是使用Crystal Report 2008(SP2)，所以就建立一支CRViewer.aspx來處理顯示報表。

實作

接下來我們一步步來建立共用的Report Viewer:

1.建立一個Store Procedure(CR_RPT_TEST)接受一個參數為@ROW_CNT，表示要產生的筆數。

    @ROW_CNT INT = 10 --產生幾筆資料
AS
BEGIN
    SET NOCOUNT ON 
    --準備資料
    DECLARE @EMPLOYEE TABLE
    (
        ID INT IDENTITY(1,1) 
        , EMP_ID VARCHAR(32)
        , EMP_NAME NVARCHAR(32)
        , BIRTHDAY DATETIME
    ) 
    DECLARE @CNT INT, @BASE_DAY DATETIME
    SET @CNT = 1
    SET @BASE_DAY = CAST('1975/10/01' AS DATETIME)
    
    WHILE (@CNT <= @ROW_CNT)
    BEGIN
        INSERT INTO @EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES(LTRIM(STR(@CNT)), N'員工姓名:' + LTRIM(STR(@CNT)) , DATEADD(DAY, @CNT, @BASE_DAY))
        SET @CNT = @CNT + 1
    END
    SELECT *
    FROM @EMPLOYEE A
 
END

2.建立一個Crystal Report報表(CRTestRpt.rpt)，資料來源為CR_RPT_TEST，並多加一個dt的字串參數(設定亂數值清CR Cache)。

3.將該報表放到Web Site專案的RPT目錄之中。

4.加入Javascript file(MyFramework.js)，做為開啟報表之用(__OpenCrystalReport)。

    var iScreenWidth, iScreenHeight, iTaskLine, itop, ileft;
    var strReportWinName = vstrWinName + "_Window";
    iTaskLine = 100;
    iScreenWidth = (screen.width - iTaskLine) ;
    iScreenHeight =(screen.height - iTaskLine) ;
    itop = 20;
    ileft = 20;
    var sParams = "width=" + iScreenWidth.toString() + ",height=" + iScreenHeight.toString() 
    + ",top=" + itop.toString() + ",left=" + ileft.toString() + ",resizable=yes,status=1,scrollbars=yes";  
    var vstrURL = "./CRViewer.aspx" + vstrParaList;
    window.open(vstrURL, strReportWinName , sParams);
}

5.加入StringHelper類別，做為頁面間QueryString傳遞加解密之用。我們用到的是 EncryptQueryString & DecryptQueryString(修改自ASP.NET 3.5 Enterprise Application Development with Visual Studio 2008)。因為我們不想讓該URL被別人Copy去用，所以加入了myKey去加解密，最簡單的就是用登入者帳號(Session["strUsrID"])來當Key，所以其他人將該URL Copy過去，也解不開該QueryString。

{
    public const char QUERY_STRING_DELIMITER = '&';

    private static RijndaelManaged _cryptoProvider;
    //128 bit encyption: DO NOT CHANGE
    private static readonly byte[] Key = { 18, 19, 8, 24, 8, 22, 2, 25, 17, 5, 11, 9, 13, 15, 06, 9 };
    private static readonly byte[] IV = { 14, 2, 16, 7, 5, 9, 33, 23, 23, 47, 16, 12, 1, 32, 25, 9 };
    static StringHelpers()
    {
        _cryptoProvider = new RijndaelManaged();
        _cryptoProvider.Mode = CipherMode.CBC;
        _cryptoProvider.Padding = PaddingMode.PKCS7;
    }

    public static string Encrypt(string unencryptedString)
    {
        return Encrypt(unencryptedString, string.Empty);
    }

    public static string Encrypt(string unencryptedString, string myKey)
    {

        //byte[] bytIn = ASCIIEncoding.ASCII.GetBytes(unencryptedString);
        //如果內容有unicode的話，要用utf8編碼
        byte[] bytIn = UTF8Encoding.UTF8.GetBytes(unencryptedString);
        byte[] bytKey;
        if(string.IsNullOrEmpty(myKey)){
            bytKey = Key;
        }else{
            bytKey = ASCIIEncoding.ASCII.GetBytes(myKey);
            Array.Resize(ref bytKey, 16);
        }
        // Create a MemoryStream
        MemoryStream ms = new MemoryStream();
        // Create Crypto Stream that encrypts a stream
        CryptoStream cs = new CryptoStream(ms, _cryptoProvider.CreateEncryptor(bytKey, IV), CryptoStreamMode.Write);
        // Write content into MemoryStream
        cs.Write(bytIn, 0, bytIn.Length);
        cs.FlushFinalBlock();
        byte[] bytOut = ms.ToArray();
        //因為url不能吃+所以要轉成@@@
        return Convert.ToBase64String(bytOut).Replace("+", "@@@");
    }

    public static string Decrypt(string encryptedString)
    {
        return Decrypt(encryptedString, string.Empty);
    }

    public static string Decrypt(string encryptedString, string myKey)
    {
        if (encryptedString.Trim().Length != 0)
        {
            //如果有人改加密字串的話，解就會發生錯誤，所以錯誤就回傳空字串
            try
            {
                // Convert from Base64 to binary 在解開前要先將@@@轉成+
                byte[] bytIn = Convert.FromBase64String(encryptedString.Replace("@@@", "+"));
                byte[] bytKey;
                if(string.IsNullOrEmpty(myKey)){
                    bytKey = Key;
                }else{
                    bytKey = ASCIIEncoding.ASCII.GetBytes(myKey);
                    Array.Resize(ref bytKey, 16);
                }
                // Create a MemoryStream
                MemoryStream ms = new MemoryStream(bytIn, 0, bytIn.Length);
                // Create a CryptoStream that decrypts the data
                CryptoStream cs = new CryptoStream(ms, _cryptoProvider.CreateDecryptor(bytKey, IV), CryptoStreamMode.Read);

                // Read the Crypto Stream
                StreamReader sr = new StreamReader(cs);
                return sr.ReadToEnd();
            }
            catch
            {
                return "";
            }
        }
        else
        {
            return "";
        }
    }

    public static NameValueCollection DecryptQueryString(string queryString)
    {
        return DecryptQueryString(queryString, string.Empty);
    }

    public static NameValueCollection DecryptQueryString(string queryString, string myKey)
    {
        if (queryString.Length != 0)
        {
            //Decode the string
            string decodedQueryString = HttpUtility.UrlDecode(queryString);
            //Decrypt the string
            string decryptedQueryString = StringHelpers.Decrypt(decodedQueryString, myKey );
            //Now split the string based on each parameter
            string[] actionQueryString = decryptedQueryString.Split(new char[] { QUERY_STRING_DELIMITER });
            NameValueCollection newQueryString = new NameValueCollection();
            //loop around for each name value pair.
            for (int index = 0; index < actionQueryString.Length; index++)
            {
                string[] queryStringItem = actionQueryString[index].Split(new char[] { '=' });
                if(queryStringItem.Length > 1)
                    newQueryString.Add(queryStringItem[0], queryStringItem[1]);
            }
            return newQueryString;
        }
        else
        {
            //No query string was passed in.
            return null;
        }
    }

    public static string EncryptQueryString(NameValueCollection queryString)
    {
        return EncryptQueryString(queryString, string.Empty);
    }

    public static string EncryptQueryString(NameValueCollection queryString, string myKey)
    {
        //create a string for each value in the query string passed in.
        string tempQueryString = "";
        for (int index = 0; index < queryString.Count; index++)
        {
            tempQueryString += queryString.GetKey(index) + "=" + queryString[index];
            if (index != queryString.Count - 1)
            {
                tempQueryString += QUERY_STRING_DELIMITER;
            }
        }
        return EncryptQueryString(tempQueryString, myKey);
    }

    public static string EncryptQueryString(string queryString)
    {
        return EncryptQueryString(queryString, string.Empty);
    }

    public static string EncryptQueryString(string queryString, string myKey)
    {
        return "?" + HttpUtility.UrlEncode(StringHelpers.Encrypt(queryString, myKey));
    }
}

6.新增共用的Report Viewer程式(CRViewer.aspx)。

7.在工具列中將CrystalReportViewer(ID=crRptViewer)，及CrystalReportSource(ID=crSource)拉到頁面上，並設定CrystalReportViewer的ReportSourceID為crSource。

    <CR:CrystalReportViewer ID="crRptViewer" runat="server" AutoDataBind="true" 
    ReportSourceID="crSource" EnableDatabaseLogonPrompt="False" 
        EnableParameterPrompt="False" ReuseParameterValuesOnRefresh="True" 
        ToolPanelView="None" PrintMode="ActiveX" HasCrystalLogo="false" HasToggleParameterPanelButton="False" />
    <CR:CrystalReportSource ID="crSource" runat="server">
    </CR:CrystalReportSource>
</form>

8.切換到CRViewer.aspx.cs，加入開啟報表，設定資料庫及設定參數值的程式! 其中解QueryString的Key設定為Session["strUsrID"]，該值為登入的使用者代號。

{
    protected void Page_Load(object sender, EventArgs e)
    {

        //取得要解querystring，所使用的Key, 用Session["strUsrID"]來當Key
        string myKey = (string)Session["strUsrID"];
        if (string.IsNullOrEmpty(myKey))
        {
            myKey = "";
        }
        if (Page.IsPostBack == false)
        {
            Session["rptQS"] = Request.QueryString.ToString();
        }
        string rptQS = (string)Session["rptQS"];
        //使用myKey去解querystring
        NameValueCollection paList = StringHelpers.DecryptQueryString(rptQS, myKey);
        if (paList != null && paList.Count > 0)
        {
            //取得report Name
            string rptName = paList["rpt"];
            if (!string.IsNullOrEmpty(rptName))
            {
                //取出Report
                string rptPath = Path.Combine(Server.MapPath("~/RPT/"), rptName + @".rpt");
                                    //設定Report File
                    crSource.Report.FileName = rptPath;
                    //設定Report的資料庫連接資訊
                    SetReportDBInfoByApID();
                    //設定參數值
                    SetReportParameters(paList);
                    crSource.ReportDocument.Refresh();
                
            }
        }
    }


    /// <summary>
    /// 設定Report的資料資訊
    /// </summary>
    /// <param name="apid"></param>
    private void SetReportDBInfoByApID()
    {
        //connection string可讀取自web.config
        string connString = "Data Source=127.0.0.1;Initial Catalog=PaidTimeOff;User ID=sa;Password=0935;";
        //確定是SQL Server
        SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connString);
        CrystalDecisions.CrystalReports.Engine.Tables tbls = crSource.ReportDocument.Database.Tables;  //rptDoc.Database.Tables;
        SetReportDBInfo(sb, ref tbls);

        //檢查是否有SubReport
        foreach (ReportDocument rpt in crSource.ReportDocument.Subreports)
        {
            CrystalDecisions.CrystalReports.Engine.Tables subTbls = rpt.Database.Tables;
            SetReportDBInfo(sb, ref subTbls);
        }

    }

    /// <summary>
    /// 依SqlConnectionStringBuilder的屬性來設定Report的DB資訊
    /// </summary>
    /// <param name="sb"></param>
    /// <param name="tbls"></param>
    private void SetReportDBInfo(SqlConnectionStringBuilder sb, ref CrystalDecisions.CrystalReports.Engine.Tables tbls)
    {
        foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in tbls)
        {
            TableLogOnInfo logInfo = tbl.LogOnInfo;
            logInfo.ConnectionInfo.ServerName = sb.DataSource;
            logInfo.ConnectionInfo.DatabaseName = sb.InitialCatalog;
            logInfo.ConnectionInfo.UserID = sb.UserID;
            logInfo.ConnectionInfo.Password = sb.Password;
            tbl.ApplyLogOnInfo(logInfo);
        }
    }

    /// <summary>
    /// 設定報表參數
    /// </summary>
    /// <param name="paList"></param>
    private void SetReportParameters(NameValueCollection paList)
    {
        
        for (int i = 0; i < paList.Count; i++)
        {
            try
            {
                ParameterField pa = crRptViewer.ParameterFieldInfo[paList.GetKey(i)];
                ParameterDiscreteValue paValue = new ParameterDiscreteValue();
                paValue.Value = paList.Get(i);
                pa.CurrentValues.Clear();
                pa.CurrentValues.Add(paValue);
            }
            catch (Exception ex)
            {

            }
        }
    }
}

9.新增一個測試報表的程式(Default.aspx)，在畫面上拉一個Button，在Click事件中加入要開啟的報表及參數。

<table>
  <tr>
    <td width="30%">
        報表參數(筆數)
    </td>
    <td width="70%">
        <asp:TextBox ID="txtCount" runat="server" Text="100"></asp:TextBox>
        <asp:Button ID="btnOpenWin" runat="server" Text="開啟報表" OnClick="btnOpenWin_Click" />
    </td>
  </tr>
</table>
</form>

 

{
    protected void Page_Load(object sender, EventArgs e)
    {
        //設定使用者的帳號到Session之中，做為加密的Key
        Session["strUsrID"] = "rainmaker";

    }
    protected void btnOpenWin_Click(object sender, EventArgs e)
    {
        string myKey = (string)Session["strUsrID"];
        NameValueCollection paList = new NameValueCollection();
        //設定報表名稱
        paList.Add("rpt", "CRTestRpt");
        //設定報表的參數
        paList.Add("@ROW_CNT", txtCount.Text );
        paList.Add("dt", DateTime.Now.Ticks.ToString());
        //設定querystring
        string paraList = StringHelpers.EncryptQueryString(paList, myKey);
        Page.RegisterStartupScript("OpenRpt", @"<script language='javascript'>__OpenCrystalReport('CRTestRpt','" + paraList  + "');</script>");
    }
}

結論

以上提供簡單的Asp.NET Crystal Report Viewer架構分享給大家。如果有其他的方式，也請讓我知道，謝謝!

以前有使用過Crystal Report 10，那時元件只有CrystalReportViewer，所以Viewer的Source就要透過ReportDocument來處理哦! 所以如果沒有CrystalReportSource元件的話，就用ReportDocument來操作，都還蠻類似的!

另外，之前在裝Crystal Report 2008的Update 或是Service Pack時，它都會在檢查磁碟空間(check disk space)時，檢查超久，我的空間明明有100G，他還一直在檢查中! 後來我發現，只要開啟檔案總管，開到它要安裝的目錄，如我的電腦Path是C:\Program Files\Business Objects! 他的檢查馬上就會通過，然後開始安裝! ^_^

參考

水晶报表‏技巧汇总

Hhow to use Ccrystal Rreport with asp.net

ASP.NET 3.5 Enterprise Application Development with Visual Studio 2008

範例

附上完整的範例(VS2008 & Crystal Report 2008 sp2):CRViewerTest2.rar

 

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^