截图如下:
实现统计的效果:
前台页面代码:
<%@ 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 runat="server">
<title></title>
<link href="../css/common.css?5" type="text/css" rel="stylesheet"/>
<link type="text/css" rel="stylesheet" href="../css/style.css?7"/>
<link href="../css/css404.css" rel="stylesheet" type="text/css" />
<link href="../css/TableStyle.css" rel="stylesheet" type="text/css" />
</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>
 <asp:Button ID="btnSelect" runat="server" Text="统计"
οnclick="btnSelect_Click" CssClass="button" />
<asp:Button ID="btnExport" runat="server" Text="导出到EXCEL"
οnclick="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>
<ItemStyle Wrap="False" HorizontalAlign="Left" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="一月" HeaderText="一月" SortExpression="一月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="二月" HeaderText="二月" SortExpression="二月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="三月" HeaderText="三月" SortExpression="三月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="四月" HeaderText="四月" SortExpression="四月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="五月" HeaderText="五月" SortExpression="五月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="六月" HeaderText="六月" SortExpression="六月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="七月" HeaderText="七月" SortExpression="七月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="八月" HeaderText="八月" SortExpression="八月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="九月" HeaderText="九月" SortExpression="九月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="十月" HeaderText="十月" SortExpression="十月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="十一月" HeaderText="十一月" SortExpression="十一月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="十二月" HeaderText="十二月" SortExpression="十二月"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" ></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="合计" HeaderText="合计" SortExpression="合计"
ItemStyle-HorizontalAlign="Center">
<ItemStyle HorizontalAlign="Center" Wrap="False" Font-Bold="True" ></ItemStyle>
</asp:BoundField>
</Columns>
</asp:GridView>
</div>
</div>
</form>
</body>
</html>
后台代码:
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();
}