按年份统计数据表信息，并分月份显示！并导出成EXCEL

截图如下：

实现统计的效果：

前台页面代码：

<%@ Page Language="C#" AutoEventWireup="true" EnableViewState="true" CodeBehind="StatisticsTFund.aspx.cs" Inherits="WHQGCBMS.StatisticsTFund" EnableEventValidation="false"%>
<!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>
<body>
    <form id="form1" runat="server" >

<hr />
    <div>
      <div style="width:100%; height: 47px;">
                <br />
                <span style =" vertical-align :middle; font-size:large ">  统计条件：</span>
                <asp:DropDownList ID="ddlYear" runat="server">
                </asp:DropDownList><span style =" vertical-align :middle; font-size:large ">年</span>
                
                &nbsp<asp:Button ID="btnSelect" runat="server" Text="统计"
                    onclick="btnSelect_Click" CssClass="button" />  
                <asp:Button ID="btnExport" runat="server" Text="导出到EXCEL"
                    onclick="btnExport_Click" CssClass="button" Width="139px" />
               </div>
               <br />
                <asp:GridView ID="GVtongjiresult" runat="server"
            AutoGenerateColumns="False" >
                        <RowStyle HorizontalAlign="Center" Wrap="false" VerticalAlign="Middle" />
                         <HeaderStyle BackColor="#449BD5" CssClass="bxk_xy" Wrap="False"
                            VerticalAlign="Middle" />
                        <Columns>
                           <asp:BoundField DataField="类别" HeaderText="类别" SortExpression="类别"
                                 ReadOnly="True" HeaderStyle-HorizontalAlign="Center" >

<HeaderStyle HorizontalAlign="Center"></HeaderStyle>

后台代码：

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindYear();

            }

        }
        /// <summary>
        /// 绑定年份
        /// </summary>
        void BindYear()
        {
            for (int i = 2010; i < 2020; i++)
            {
                ListItem li = new ListItem(i + "", i + "");
                ddlYear.Items.Add(li);
            }
            ddlYear.SelectedValue = DateTime.Now.Year + "";
        }
        /// <summary>
        /// 统计
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSelect_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("类别", typeof(string));
            dt.Columns.Add("一月", typeof(string));
            dt.Columns.Add("二月", typeof(string));
            dt.Columns.Add("三月", typeof(string));
            dt.Columns.Add("四月", typeof(string));
            dt.Columns.Add("五月", typeof(string));
            dt.Columns.Add("六月", typeof(string));
            dt.Columns.Add("七月", typeof(string));
            dt.Columns.Add("八月", typeof(string));
            dt.Columns.Add("九月", typeof(string));
            dt.Columns.Add("十月", typeof(string));
            dt.Columns.Add("十一月", typeof(string));
            dt.Columns.Add("十二月", typeof(string));
            dt.Columns.Add("合计", typeof(string));

string sql = "";

sql = GetSql(sql);
DataSet ds = Maticsoft.DBUtility.DbHelperSQL.Query(sql);

            Cula(dt, ds, "征收总项目数(个)", "c");
            Cula(dt, ds, "征收总面积(M²)", "b");
            Cula(dt, ds, "征收总金额(元)", "p");
            sql = "";
            sql = GetSql(sql + " and [DerateType] = 1 and [IsDerate] = 1 ");

            DataSet ds1 = Maticsoft.DBUtility.DbHelperSQL.Query(sql);
            Cula(dt, ds1, "免收项目数(个)", "c");
            Cula(dt, ds1, "免收面积(M²)", "b");
            sql = "";
            sql = GetSql(sql + " and [DerateType] = 2 and [IsDerate] = 1   ");

            DataSet ds2 = Maticsoft.DBUtility.DbHelperSQL.Query(sql);
            Cula(dt, ds2, "减半征收项目数(个)", "c");
            Cula(dt, ds2, "减半征收面积(M²)", "b");

//sql = "";
//sql = GetSql(sql + " and [DerateType] = 19 and [IsDerate] = 1 ");

            //DataSet ds4 = Maticsoft.DBUtility.DbHelperSQL.Query(sql);
            //Cula(dt, ds4, "一次性征收项目数(个)", "c");
            //Cula(dt, ds4, "一次性征收面积(M2)", "b");

sql = "";
sql = GetSql(sql + " and [DerateType] = 3 and [IsDerate] = 1 ");

            DataSet ds3 = Maticsoft.DBUtility.DbHelperSQL.Query(sql);
            Cula(dt, ds3, "其他项目数(个)", "c");
            Cula(dt, ds3, "其他面积(M²)", "b");

sql = "";
sql = GetSql(sql + " and [IsDerate] = 0 ");

            DataSet ds5 = Maticsoft.DBUtility.DbHelperSQL.Query(sql);
            Cula(dt, ds5, "全额征收项目数(个)", "c");
            Cula(dt, ds5, "全额征收面积(M²)", "b");

            GVtongjiresult.DataSource = dt;
            GVtongjiresult.DataBind();
        }
        /// <summary>
        /// 统计SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        private string GetSql(string strWhere)
        {
            string sql1 = "";

            string sql = "select convert(int,a.yd) as yd,isnull(cd.b,0) as b,isnull(cd.c,0) as c,isnull(cd.p,0) as p from " +
                 "(select '1' as yd union select '2' union select '3' union select '4' union select '5' union select '6' union select '7' union " +
                     "select '8' union select '9' union select '10' union select '11' union select '12') a " +
                 "left join " +
                 "(select convert(decimal(18,2),isnull(sum(ConstructionArea),0)) as b ,isnull(convert(decimal(18,2),sum(PaidIn)),0) as p ,month(UpdateDate)as t ,COUNT(*) as c " +
                 "from [T_Fund]   where   DelMark=0 and OperatorID>0 and year(UpdateDate) = '" + ddlYear.SelectedValue + "' " + sql1 + strWhere + " group by month(UpdateDate)) cd " +
                    "on a.yd=cd.t order by yd ";
            return sql;
        }

        private static void Cula(DataTable dt, DataSet ds, string Name, string col)
        {
            DataRow dr = dt.NewRow();
            dr["类别"] = Name;
            decimal count = 0;

for (int i = 1; i < 13; i++)
{

                dr[i] = ds.Tables[0].Rows[i - 1][col];
                count += Convert.ToDecimal(dr[i]);
            }

dr["合计"] = count;

            dt.Rows.Add(dr);
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            Common.ExcelHelper exhelp = new Common.ExcelHelper();
            exhelp.Export(this,"application/ms-excel", "Report.xls", GVtongjiresult);

}

//不可少，不然提示GRIDVIEW报错，没放在runat="server"的页面中！

        public override void VerifyRenderingInServerForm(Control control)
        {
            //base.VerifyRenderingInServerForm(control);
        }
    }

/// <summary>
        /// 导出成EXECEL
        /// </summary>
        /// <param name="FileType"></param>
        /// <param name="FileName"></param>
        /// <param name="gridview"></param>
        public void Export(Page pagename,string FileType, string FileName, GridView gridview)
        {
            pagename.Response.Clear();
            pagename.Response.Buffer = false;

pagename.Response.Charset = "GB2312";

            pagename.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
            pagename.Response.ContentEncoding = System.Text.Encoding.Default;
            pagename.Response.ContentType = "application/ms-excel";
            pagename.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=GB2312\">");

            pagename.EnableViewState = false;
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            gridview.RenderControl(hw);
            pagename.Response.Write(tw.ToString());
            pagename.Response.End();
        }

posted @ 2011-09-28 19:12 做最好の自己阅读(2694) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

按年份统计数据表信息，并分月份显示！并导出成EXCEL

公告