[ASP.net WebForm] 把GridView查詢結果送到CrystalReport呈現

2011-08-09

11680
0
ASP.net WebForm
2011-08-09

前置作業

1. 先安裝SAP Crystal Reports Version for Visual Studio 2010：如何於Visual Studio 2010中使用Crystal Report來建立報表

2. 之後在Visual Studio 2010專案中按右鍵>新增項目>CrystalReport.rpt

(如果Visual Studio是在開啟狀態下安裝完的話，要重啟Visual Studio，才能正確地新增項目)

以下範例使用資料庫Access

完成畫面(隨便弄，所以很簡陋)

3. 準備CSharp.aspx(C#)，此畫面用來做GridView查詢資料


<%@ Page Debug="true" Language="C#" AutoEventWireup="true" CodeFile="CSharp.aspx.cs"
    Inherits="CSharp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    請輸入Country:<asp:TextBox ID="txt_Country" runat="server" />
    <asp:Button ID="Button1" runat="server" Text="ShowReport" OnClick="Button1_Click" />
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="uid"
        DataSourceID="sds_tb_Person">
        <Columns>
            <asp:BoundField DataField="uid" HeaderText="uid" InsertVisible="False" ReadOnly="True"
                SortExpression="uid" />
            <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
            <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:SqlDataSource runat="server" ID="sds_tb_Person" ConnectionString="<%$ ConnectionStrings:ConnectionStringAccess %>"
        ProviderName="<%$ ConnectionStrings:ConnectionStringAccess.ProviderName %>" SelectCommand="SELECT * FROM [tb_Person] Where (@Country='' Or Country Like '%' + @Country+ '%')">
        <SelectParameters>
            <asp:ControlParameter ControlID="txt_Country" PropertyName="Text" Name="Country"
                Type="String" ConvertEmptyStringToNull="false" />
        </SelectParameters>
    </asp:SqlDataSource>
    </form>
</body>
</html>

後置程式碼：


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Data;
using System.Data.OleDb;

public partial class CSharp : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
  
    }



    #region 顯示報表
    protected void Button1_Click(object sender, EventArgs e)
    {
        DataView dv= (DataView)sds_tb_Person.Select(DataSourceSelectArguments.Empty);
        
    
        Session["data"] = dv;
        //開啟新視窗
        //ClientScript.RegisterClientScriptBlock(typeof(Page),"另開視窗","window.showModalDialog('ViewCR.aspx','_blank','status: on;center: yes;dialogHeight: 600px;dialogWidth: 800px;');",true);
        ClientScript.RegisterClientScriptBlock(typeof(Page), "另開視窗", "window.open('ViewCR.aspx');", true);
    }
    #endregion

       
}

(VB.net使用者的話，請準備VisualBasic.aspx)


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="VisualBasic.aspx.vb" Inherits="VisualBasic" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    請輸入Country:<asp:TextBox ID="txt_Country" runat="server" />
    <asp:Button ID="Button1" runat="server" Text="ShowReport" OnClick="Button1_Click" />
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="uid"
        DataSourceID="sds_tb_Person">
        <Columns>
            <asp:BoundField DataField="uid" HeaderText="uid" InsertVisible="False" ReadOnly="True"
                SortExpression="uid" />
            <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
            <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:SqlDataSource runat="server" ID="sds_tb_Person" ConnectionString="<%$ ConnectionStrings:ConnectionStringAccess %>"
        ProviderName="<%$ ConnectionStrings:ConnectionStringAccess.ProviderName %>" SelectCommand="SELECT * FROM [tb_Person] Where (@Country='' Or Country Like '%' + @Country + '%')">
        <SelectParameters>
            <asp:ControlParameter ControlID="txt_Country" PropertyName="Text" Name="Country"
                Type="String" ConvertEmptyStringToNull="false" />
        </SelectParameters>
    </asp:SqlDataSource>
    </form>
</body>
</html>

(↑以上和CSharp.aspx大同小異)

VB.net的後置程式碼：


Imports System.Data

Partial Class VisualBasic
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
        Dim dv = CType(sds_tb_Person.Select(DataSourceSelectArguments.Empty), DataView)


        Session("data") = dv
        '開啟新視窗
        'ClientScript.RegisterClientScriptBlock(GetType(Page),"另開視窗","window.showModalDialog('ViewCRVB.aspx','_blank','status: on;center: yes;dialogHeight: 600px;dialogWidth: 800px;');",true)
        ClientScript.RegisterClientScriptBlock(GetType(Page), "另開視窗", "window.open('ViewCRVB.aspx');", True)
    End Sub
End Class

4. 剛剛的GridView查詢畫面已做好，接下來要做CrystalReport的呈現畫面

新增一個ViewCR.aspx


<%@ Page Debug="true" Language="C#" AutoEventWireup="true" CodeFile="ViewCR.aspx.cs" Inherits="ViewCR" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
    Namespace="CrystalDecisions.Web" TagPrefix="CR" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
<form id="form1" runat="server">    
   
       <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" 
           AutoDataBind="true"  
           EnableDatabaseLogonPrompt="False" EnableParameterPrompt="False" 
           HasCrystalLogo="False" />    
   
   </form>
</body>
</html>

後置程式碼：


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using CrystalDecisions.CrystalReports.Engine;

public partial class ViewCR : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Response.Cache.SetCacheability(HttpCacheability.NoCache); //clear cache  
        DataView dv = Session["data"] as DataView;
        ReportDocument rd = new ReportDocument();
        rd.Load(Server.MapPath("~/CrystalReport.rpt"));
        rd.SetDataSource(dv);
        CrystalReportViewer1.ReportSource = rd;
    }
}

(VB.net使用者的話，請準備ViewCRVB.aspx)


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ViewCRVB.aspx.vb" Inherits="ViewCRVB" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
    Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true"
        EnableDatabaseLogonPrompt="False" EnableParameterPrompt="False" HasCrystalLogo="False" />
    </form>
</body>
</html>

VB.net的後置程式碼：


Imports System.Data
Imports CrystalDecisions.CrystalReports.Engine

Partial Class ViewCRVB
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Response.Cache.SetCacheability(HttpCacheability.NoCache) 'clear cache  
        Dim dv = CType(Session("data"), DataView)
        Dim rd As New ReportDocument()
        rd.Load(Server.MapPath("~/CrystalReport.rpt"))
        rd.SetDataSource(dv)
        CrystalReportViewer1.ReportSource = rd
    End Sub
End Class

執行結果：

輸入USA後按下Button

程式碼懶人包(.net 4 WebSite專案，若出現遺漏組件參考的話，請重新加入CrystalReport.rpt檔即可)

MSDN台灣區 SQL Server版主的文章也值得參考：[C#][WebForm]Gridview to CrystalReport

相關討論：請問如何把網頁中檢索資料庫的結果送到Crystal Report呢？

回首頁