iT邦幫忙

0

[Report] SQL Report製作報表 & 將報表匯出成EXCEL

環境VS2013
製作報表
1.建立參考
2.建立REPORT VIEW
3.建立報表
3.1.產生連線
3.2.產生DATASET
3.3.產生報表

用程式控制報表值
4.資料繫結databind
5.webpage中將報表匯出

先加入參考
https://ithelp.ithome.com.tw/upload/images/20180205/20106764BCNZhT4PFV.png
加入這2個控制項
https://ithelp.ithome.com.tw/upload/images/20180205/20106764eUvDqUWODM.png
按下[設計新報表]
https://ithelp.ithome.com.tw/upload/images/20180205/20106764l0m9tCE68d.png
資料集屬性:新增或選取資料來源 & 新增或選取資料來源
https://ithelp.ithome.com.tw/upload/images/20180205/20106764KX7RYR9Fs9.png
選擇資料連接:新增連接
https://ithelp.ithome.com.tw/upload/images/20180205/20106764eglPTevRAk.png
加入連接:輸入帳密 & 輸入資料庫
https://ithelp.ithome.com.tw/upload/images/20180205/20106764tbnN4ayT8r.png
選擇資料連接
https://ithelp.ithome.com.tw/upload/images/20180205/201067648hf55wpUFo.png
選擇您的資料庫物件
https://ithelp.ithome.com.tw/upload/images/20180205/20106764WfVpGxlIou.png

https://ithelp.ithome.com.tw/upload/images/20180205/20106764vyBEwwbTQJ.png
資料集屬性
https://ithelp.ithome.com.tw/upload/images/20180205/20106764TN3gzcQKa1.png
排列欄位
https://ithelp.ithome.com.tw/upload/images/20180205/20106764wcAyDa2lYC.png
選擇配置
https://ithelp.ithome.com.tw/upload/images/20180205/201067644xY9jtFups.png
選擇樣式
https://ithelp.ithome.com.tw/upload/images/20180205/20106764ZJPgIVeWKg.pnghttps://ithelp.ithome.com.tw/upload/images/20180205/20106764ZqyNlG09rD.png

做完會產生3個檔案

1.webconfig裡多了
2.多了DataSet.xsd
3.Report.rdlc
https://ithelp.ithome.com.tw/upload/images/20180205/20106764k1SQYnDSFI.png
https://ithelp.ithome.com.tw/upload/images/20180205/20106764vadcgyGzsA.png
https://ithelp.ithome.com.tw/upload/images/20180205/20106764HpxTxGLTkQ.png
報表屬性設定
設為內容 & 永遠複製
https://ithelp.ithome.com.tw/upload/images/20180205/20106764qrtVTEnHxn.png

用code 給CR dateset

這樣報表就會做顯示了

         DataSet myds = getDateSet(YMDs, YMDe, iUnit, sOrderBy);

        this.ReportViewer1.LocalReport.ReportPath = @"Report1.rdlc";
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;

        ReportViewer1.LocalReport.DataSources.Clear();
        this.ReportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", myds.Tables[0]));
        this.ReportViewer1.LocalReport.Refresh();
private static DataSet getDateSet(string YMDs, string YMDe, string iUnit, string sOrderBy)
   { 
      SqlConnection sqlConn = new SqlConnection(control. ConnStr);

      SqlCommand sqlCmd = new SqlCommand("exec sp_xxx  @YMDs,@YMDe ,@iUnit ,@OrderBy", sqlConn);

       sqlCmd.Parameters.Add(new SqlParameter("@YMDs", YMDs));
       sqlCmd.Parameters.Add(new SqlParameter("@YMDe", YMDe));
       sqlCmd.Parameters.Add(new SqlParameter("@iUnit", iUnit));
       sqlCmd.Parameters.Add(new SqlParameter("@OrderBy", sOrderBy));

       SqlDataAdapter myda = new SqlDataAdapter(sqlCmd); 
       sqlCmd.CommandTimeout = 6000;
       DataSet myds = new DataSet();
       sqlConn.Open();
       myda.Fill(myds);
       sqlConn.Close();
       return myds;
   }

將報表匯出成EXCEL

protected void Button1_Click(object sender, EventArgs e)
      {
           DataSet myds = getDateSet( );

          this.ReportViewer1.LocalReport.ReportPath = @"Report1.rdlc ";
          ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;

          ReportViewer1.LocalReport.DataSources.Clear();
          this.ReportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", myds.Tables[0]));
          this.ReportViewer1.LocalReport.Refresh();

          //---------------------------------export--------------------------
          Microsoft.Reporting.WebForms.   Warning[] warnings;
          string[] streamIds;
          string mimeType = string.Empty;
          string encoding = string.Empty;
          string extension = string.Empty;
          byte[] bytes = ReportViewer1.LocalReport.Render("EXCEL", null, out mimeType, out encoding, out extension, out streamIds, out warnings);
          //支援的格式包括 Excel、PDF、Word 和 Image。  

          Response.Buffer = true;
          Response.Clear();
          Response.ContentType = mimeType;
          Response.AddHeader("content-disposition", "attachment; filename=" + "Report1" + "." + extension);
          Response.BinaryWrite(bytes); // create the file
          Response.Flush(); // send it to the client to download

      } 

報表設計

ReportView可以隨畫面大小縮放
設計工具
Height="100%" Width="100%" AsyncRendering="False" SizeToReportContent="True"
https://ithelp.ithome.com.tw/upload/images/20180205/20106764Hu69SUBkr1.png
Ref
https://forums.asp.net/t/1556522.aspx?RDLC+Export+directly+to+Excel+or+PDF+from+codebehind


尚未有邦友留言

立即登入留言