REPORT_ZLCG.aspx（统计，导出）
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="REPORT_ZLCG.aspx.cs" Inherits="GSAO.CGGL.REPORT_ZLCG" %> <!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> <link href="../css/style.css" rel="stylesheet" type="text/css" /> <script language="javascript" type="text/javascript" src="../My97DatePicker/WdatePicker.js"></script> </head> <body> <form id="form1" runat="server"> <table width="99%" class="tbDefault mb5 mt5"> <tr> <td class="tdRight bgEFEFEF" width="50">单位：</td> <td class="tdLeft" width="150"> <asp:DropDownList ID="ddl_YXSM" runat="server" CssClass="ddl" AutoPostBack="True" onselectedindexchanged="ddl_YXSM_SelectedIndexChanged"> </asp:DropDownList> </td> <td class="tdRight bgEFEFEF" width="50">专业：</td> <td class="tdLeft" width="150"> <asp:DropDownList ID="ddl_ZY" runat="server" CssClass="ddl"> </asp:DropDownList> </td> <td class="tdRight bgEFEFEF" width="50">年级：</td> <td class="tdLeft" width="50"> <asp:TextBox ID="txt_NJ" runat="server" class="txt" Width="100%"></asp:TextBox> </td> <td class="tdRight bgEFEFEF" width="80">批准日期：</td> <td class="tdLeft"> <input class="txt" id="txt_KSRQ" runat="server" onfocus="WdatePicker()" style="width:70px;" > 至 <input class="txt mr10" id="txt_JSRQ" runat="server" onfocus="WdatePicker()" style="width:70px;"> <asp:Button ID="btn_search" runat="server" Text="查 询" CssClass="btnStyle mr10" onclick="btn_search_Click" /> <asp:Label ID="lb_total" runat="server" CssClass="mr10"></asp:Label> </td> <td class="tdRight" width="70"> <img src="../images/icon_export.gif" /> <asp:LinkButton ID="lbtnExport" runat="server" onclick="lbtnExport_Click" CssClass="mr10">导出</asp:LinkButton> </td> </tr> </table> <asp:Repeater ID="Repeater1" runat="server"> <HeaderTemplate> <table width="99%" class="tbDefault mt10" border="1"> <tr> <td rowspan="2">院系所</td> <td rowspan="2">专业</td> <td rowspan="2">年级</td> <td colspan="4">专利</td> </tr> <tr> <td>小计</td> <td>发明专利</td> <td>实用新型</td> <td>外观设计</td> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td class="tdLeft"><%# Eval("CGGL_YXSM_MC")%></td> <td class="tdLeft"><%# Eval("CGGL_ZY_NAME")%></td> <td><%# Eval("CGGL_NJ")%></td> <td><%# Eval("小计")%></td> <td><%# !string.IsNullOrEmpty(Eval("s1").ToString()) ? Eval("s1") : 0%></td> <td><%# !string.IsNullOrEmpty(Eval("s2").ToString()) ? Eval("s2") : 0%></td> <td><%# !string.IsNullOrEmpty(Eval("s3").ToString()) ? Eval("s3") : 0%></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> </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.Text; using System.Data; namespace GSAO.CGGL { public partial class REPORT_ZLCG : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Bind_ddl_YXSM(); Bind_ddl_ZY(); } } /// <summary> /// 绑定学院 /// </summary> protected void Bind_ddl_YXSM() { ddl_YXSM.DataSource = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query("select * from TB_YXS order by YXSMC asc").Tables[0]; ddl_YXSM.DataTextField = "YXSMC"; ddl_YXSM.DataValueField = "YXSM"; ddl_YXSM.DataBind(); ddl_YXSM.Items.Insert(0, "全部"); ddl_YXSM.Items[0].Value = "0"; } /// <summary> /// 切换学院 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void ddl_YXSM_SelectedIndexChanged(object sender, EventArgs e) { Bind_ddl_ZY(); } /// <summary> /// 绑定专业 /// </summary> protected void Bind_ddl_ZY() { ddl_ZY.DataSource = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query("select distinct ZYMC from TB_ZY where YXSM=" + ddl_YXSM.SelectedValue + " order by ZYMC asc").Tables[0]; ddl_ZY.DataTextField = "ZYMC"; ddl_ZY.DataValueField = "ZYMC"; ddl_ZY.DataBind(); ddl_ZY.Items.Insert(0, "全部"); ddl_ZY.Items[0].Value = "0"; } /// <summary> /// 查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btn_search_Click(object sender, EventArgs e) { string strWhere = "1=1"; if (ddl_YXSM.SelectedValue != "0") { strWhere += " and CGGL_YXSM=" + ddl_YXSM.SelectedValue; } if (ddl_ZY.SelectedValue != "0") { strWhere += " and CGGL_ZY_NAME='" + ddl_ZY.SelectedValue + "'"; } if (txt_NJ.Text != "") { strWhere += " and CGGL_NJ=" + txt_NJ.Text; } if (txt_KSRQ.Value != "") { strWhere += " and CGGL_ZLCG_PZRQ>='" + txt_KSRQ.Value + "'"; } if (txt_JSRQ.Value != "") { strWhere += " and CGGL_ZLCG_PZRQ<='" + txt_JSRQ.Value + "'"; } StringBuilder sb = new StringBuilder(); sb.Append("select CGGL_YXSM_MC,CGGL_ZY_NAME,CGGL_NJ,"); sb.Append("SUM(case when CHARINDEX('发明',CGGL_ZLCG_LX)>0 then 1 else 0 end)"); sb.Append("+SUM(case when CHARINDEX('实用新型',CGGL_ZLCG_LX)>0 then 1 else 0 end)"); sb.Append("+SUM(case when CHARINDEX('外观设计',CGGL_ZLCG_LX)>0 then 1 else 0 end) as '小计',"); sb.Append("SUM(case when CHARINDEX('发明',CGGL_ZLCG_LX)>0 then 1 else 0 end) as s1,"); sb.Append("SUM(case when CHARINDEX('实用新型',CGGL_ZLCG_LX)>0 then 1 else 0 end) as s2,"); sb.Append("SUM(case when CHARINDEX('外观设计',CGGL_ZLCG_LX)>0 then 1 else 0 end) as s3"); sb.Append(" from CGGL_ZLCG"); sb.Append(" where " + strWhere); sb.Append(" group by CGGL_YXSM_MC,CGGL_ZY_NAME,CGGL_NJ"); sb.Append(" UNION ALL "); sb.Append("select '总计','','',"); sb.Append("SUM(case when CHARINDEX('发明',CGGL_ZLCG_LX)>0 then 1 else 0 end)"); sb.Append("+SUM(case when CHARINDEX('实用新型',CGGL_ZLCG_LX)>0 then 1 else 0 end)"); sb.Append("+SUM(case when CHARINDEX('外观设计',CGGL_ZLCG_LX)>0 then 1 else 0 end) as '小计',"); sb.Append("SUM(case when CHARINDEX('发明',CGGL_ZLCG_LX)>0 then 1 else 0 end) as s1,"); sb.Append("SUM(case when CHARINDEX('实用新型',CGGL_ZLCG_LX)>0 then 1 else 0 end) as s2,"); sb.Append("SUM(case when CHARINDEX('外观设计',CGGL_ZLCG_LX)>0 then 1 else 0 end) as s3"); sb.Append(" from CGGL_ZLCG"); sb.Append(" where " + strWhere); sb.Append(" order by CGGL_YXSM_MC,CGGL_ZY_NAME"); DataSet ds = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query(sb.ToString()); Repeater1.DataSource = ds; Repeater1.DataBind(); lb_total.Text = "总计-" + (ds.Tables[0].Rows.Count - 1).ToString() + "-项"; Repeater1.Visible = true; } /// <summary> /// 导出需要 /// </summary> /// <param name="control"></param> public override void VerifyRenderingInServerForm(Control control) { //base.VerifyRenderingInServerForm(control); } /// <summary> /// 导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void lbtnExport_Click(object sender, EventArgs e) { if (Repeater1.Items.Count > 1) { string ExcelFile = "REPORT_ZL_" + System.DateTime.Now.ToString("yyyyMMddHHmmss"); string header = @"<table><tr><td colspan=7><center><b><font size='3'>专利成果统计表</font></b></center></td></tr>" + "<tr><td colspan=5></td><td>导出时间:</td><td>" + DateTime.Now.ToShortDateString() + "</td></tr></table>"; string bottomer = @"<table><tr><td>制表人：</td><td></td><td></td> <td>审核人：</td><td></td><td></td> <td>审批人：</td><td></td><td></td></tr></table>"; Common_GSAO.Tools.OutputExcel(Repeater1, ExcelFile, header, bottomer, lb_total); } else { Common_GSAO.Tools.ShowAlert("暂无可供导出信息！"); return; } } } }
参考SQL：
select CGGL_YXSM_MC,CGGL_ZY_NAME,CGGL_NJ, SUM(case when CHARINDEX('SCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('SCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('EI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('EI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('SSCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('SSCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('A&HCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('A&HCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('其他',CGGL_LWCG_JSQK)>0 then 1 else 0 end) as '小计', SUM(case when CHARINDEX('SCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('SCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('EI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('EI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('SSCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('SSCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('A&HCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('A&HCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('其他',CGGL_LWCG_JSQK)>0 then 1 else 0 end) FROM CGGL_LWCG group by CGGL_YXSM_MC,CGGL_ZY_NAME,CGGL_NJ UNION ALL select '总计','','', SUM(case when CHARINDEX('SCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('SCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('EI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('EI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('SSCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('SSCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('A&HCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('A&HCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end)+ SUM(case when CHARINDEX('其他',CGGL_LWCG_JSQK)>0 then 1 else 0 end) as '小计', SUM(case when CHARINDEX('SCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('SCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('EI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('EI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('SSCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('SSCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('A&HCI刊源',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('A&HCI检索',CGGL_LWCG_JSQK)>0 then 1 else 0 end), SUM(case when CHARINDEX('其他',CGGL_LWCG_JSQK)>0 then 1 else 0 end) FROM CGGL_LWCG
