好难做的一个GridView
来源：互联网 发布：linux find命令 编辑：程序博客网 时间：2024/05/01 17:30
昨天老大突然旧事重提,要用GridView来实现一个报表数,我晕,前些日子做这都搞得我头晕,现在又来了,没办法,谁叫我是小弟呢,干吧.要求如实现的结果如下
数据的表就三张,分别为:
接顺序称他为A,B,C三张表
a表brh_id表示分店ID,brh_name表示分店名称
b表model_no车型,model_desc备注信息(这里没用),model_class级别型号,chass_type级别标识,engine_type车型类别
c表orderid标识列,model_no车型,order_count订单数,cfm_count确认数,date_month日期,brh_id分店编号
需求是通过分店和月份得到上面Excel一样的格式数据.我到这怎么做呢.没办法,慢慢来,慢慢想.首先我想办法得到月份的各分店订单数,确认数.可是分店名在另一个表,怎么得呢,这边只有分店编号,先要联表查询才能再横向,太难做了,没办法,找找GOOGLE大师去,搜了半天,没结果...还好本公司虽就三个编码的,但都是老程序员了,有一个SQL又超强,哈,找他吧,找他一问,真是办法百出,但有点麻烦,终于经过一个小时的思考和寻找,老大哥发来一个动态SQL,第一眼看,傻了,这SQL看不懂,没办法,一步一步分析吧,一点一点的分析,一点一点的磨,终于搞懂了,哈,出来了.生成结果如下:
动态SQL语句如下:
@date_month varchar(30),
@group_id varchar(20)
as
declare @sql varchar(8000)
set @sql='select model_no, model_class'
select @sql=@sql+',max(case brh_name when '''+a.brh_name+''' then order_count end) ['+a.brh_name+'.订单] ,
max(case brh_name when '''+a.brh_name+''' then cfm_count end) ['+a.brh_name+'.确认]'
from (select distinct brh_name from g_branch where brh_name!='深圳仁孚管理公司') as a
set @sql= @sql+'from modelOrderinfo where date_month='''+@date_month+''' and group_id='+@group_id+' group by model_no, model_class'
print @sql
exec(@sql)
然后封装成了一个存储过程(branchOrder),这里出来了,但还有级别,头大,想了半天没办法,写一个临时表吧,然数据导到临时表中,再在临时表添加一列,将b表的chass_type加进来.加到临时表了怎么办呢,还是没出级别,这是我老大出来现招,说你可以通过group by chass_type 得到级别数和级别里各分店的订单,确认数的总和,然后插入临时表,这样model_no为空,然后你可以通过排序的方式得到你想的结果,可是太难理解了,老大还真是好人,耐心的给我说了两三次,功夫不凡有心人,终于让我明白了,哈,继续干,嘿嘿,结果还真出来了.真的是经验就经验,你会做,你不一定会想到这里.这就是工作长的经验了.说了这么多我把生成的存储过程序发出来,存储过程如下:
create proc branchOrders
@date_month varchar(30),
@group_id varchar(20)
as
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#temporder]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#temporder]
create table #temporder
(
tempid int identity(1,1) primary key,
model_no varchar(20),
model_class varchar(10),
[成都仁孚.order_count] int,
[成都仁孚.cfm_count] int,
[东莞仁孚.order_count] int,
[东莞仁孚.cfm_count] int,
[佛山仁孚.order_count] int,
[佛山仁孚.cfm_count] int,
[广星行.order_count] int,
[广星行.cfm_count] int,
[广州仁孚.order_count] int,
[广州仁孚.cfm_count] int,
[湖南仁孚.order_count] int,
[湖南仁孚.cfm_count] int,
[惠州仁孚.order_count] int,
[惠州仁孚.cfm_count] int,
[江门仁孚.order_count] int,
[江门仁孚.cfm_count] int,
[鹏星行.order_count] int,
[鹏星行.cfm_count] int,
[仁孚怡邦.order_count] int,
[仁孚怡邦.cfm_count] int,
[深圳华润.order_count] int,
[深圳华润.cfm_count] int,
[深圳仁孚.order_count] int,
[深圳仁孚.cfm_count] int,
[中山仁孚.order_count] int,
[中山仁孚.cfm_count] int,
[珠海仁孚.order_count] int,
[珠海仁孚.cfm_count] int,
chass_type varchar(20)
)
insert into #temporder(model_no,model_class,[成都仁孚.order_count],[成都仁孚.cfm_count],[东莞仁孚.order_count],[东莞仁孚.cfm_count],
[佛山仁孚.order_count],[佛山仁孚.cfm_count],[广星行.order_count],[广星行.cfm_count],[广州仁孚.order_count],[广州仁孚.cfm_count],
[湖南仁孚.order_count],[湖南仁孚.cfm_count],[惠州仁孚.order_count],[惠州仁孚.cfm_count],[江门仁孚.order_count],[江门仁孚.cfm_count],
[鹏星行.order_count],[鹏星行.cfm_count],[仁孚怡邦.order_count],[仁孚怡邦.cfm_count],[深圳华润.order_count],[深圳华润.cfm_count],
[深圳仁孚.order_count],[深圳仁孚.cfm_count],[中山仁孚.order_count],[中山仁孚.cfm_count],[珠海仁孚.order_count],[珠海仁孚.cfm_count])
exec branchOrder @date_month,@group_id
update #temporder set chass_type=isnull(g.chass_type,t.chass_type) from #temporder t inner join g_model g on t.model_no=g.model_no
insert into #temporder(chass_type,[成都仁孚.order_count],[成都仁孚.cfm_count],[东莞仁孚.order_count],[东莞仁孚.cfm_count],
[佛山仁孚.order_count],[佛山仁孚.cfm_count],[广星行.order_count],[广星行.cfm_count],[广州仁孚.order_count],[广州仁孚.cfm_count],
[湖南仁孚.order_count],[湖南仁孚.cfm_count],[惠州仁孚.order_count],[惠州仁孚.cfm_count],[江门仁孚.order_count],[江门仁孚.cfm_count],
[鹏星行.order_count],[鹏星行.cfm_count],[仁孚怡邦.order_count],[仁孚怡邦.cfm_count],[深圳华润.order_count],[深圳华润.cfm_count],
[深圳仁孚.order_count],[深圳仁孚.cfm_count],[中山仁孚.order_count],[中山仁孚.cfm_count],[珠海仁孚.order_count],[珠海仁孚.cfm_count])
select chass_type,sum([成都仁孚.order_count]),sum([成都仁孚.cfm_count]),sum([东莞仁孚.order_count]),sum([东莞仁孚.cfm_count]),
sum([佛山仁孚.order_count]),sum([佛山仁孚.cfm_count]),sum([广星行.order_count]),sum([广星行.cfm_count]),
sum([广州仁孚.order_count]),sum([广州仁孚.cfm_count]),sum([湖南仁孚.order_count]),sum([湖南仁孚.cfm_count]),
sum([惠州仁孚.order_count]),sum([惠州仁孚.cfm_count]),sum([江门仁孚.order_count]),sum([江门仁孚.cfm_count]),
sum([鹏星行.order_count]),sum([鹏星行.cfm_count]),sum([仁孚怡邦.order_count]),sum([仁孚怡邦.cfm_count]),
sum([深圳华润.order_count]),sum([深圳华润.cfm_count]),sum([深圳仁孚.order_count]),sum([深圳仁孚.cfm_count]),
sum([中山仁孚.order_count]),sum([中山仁孚.cfm_count]),sum([珠海仁孚.order_count]),sum([珠海仁孚.cfm_count])
from #temporder group by chass_type
select * from #temporder
order by chass_type,model_no
最终的结果如下:
后面还有级别的一行,太长了,截不全,呵呵
剩下的功能就全在前台写了,就不细说了,主要就是在dataTabel加了一行小计,在GridView里做了Header和总和.和修饰,生成的最终结果如下:
CS里相关代码
#region 数据绑定并小计
private void GridViewBind(string month,string group_id)
{
orderconfrimation_srv ocf = new orderconfrimation_srv();
DataTable dt = ocf.FindBrhOrder(month, group_id);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
for (int i = 3; i < dt.Columns.Count - 1; i++)
{
int orderTotal = 0;
int cfmTotal = 0;
int count = 0;
for (int j = 0; j < dt.Rows.Count; j++)
{
if (dt.Rows[j]["model_no"].ToString().Equals(""))
{
dr["model_no"] = "小计";
if (!dt.Rows[j][i].ToString().Equals(""))
{
if (i % 2 != 0)
{
orderTotal += Convert.ToInt32(dt.Rows[j][i]);
}
else
{
cfmTotal += Convert.ToInt32(dt.Rows[j][i]);
}
count++;
}
}
}
if (count > 0)
{
if (i % 2 != 0)
{
dr[i] = orderTotal.ToString();
}
else
{
dr[i] = cfmTotal.ToString();
}
}
}
dt.Rows.Add(dr);
}
gv_Order.DataSource = dt;
gv_Order.DataBind();
}
#endregion
GridView的RowDataBound事件里做如下:
protected void gv_Order_RowDataBound(object sender, GridViewRowEventArgs e)
{
#region 实现Header自定义
if (e.Row.RowType == DataControlRowType.Header)
{
ArrayList brhNameList = new ArrayList();
for (int i = 1; i < e.Row.Cells.Count-1; i++)
{
if (e.Row.Cells[i].Text.IndexOf('.') > 0)
{
if (i % 2 == 0)
{
string temp = e.Row.Cells[i].Text;
brhNameList.Add(temp.Replace(".cfm_count", null));
}
}
}
brhNameList.Add("总计");
string dateMonth = "";
e.Row.Cells.Clear();
string date = Calendar_MONTH_DATE.Text;
DateTime tempdate = Convert.ToDateTime(date);
string group_id = DDL_model_group.SelectedValue;
dateMonth += NumTodigital(tempdate.Month);
if (group_id.Equals("3"))
{
dateMonth += "月国产车";
}
else
{
dateMonth += "月进口车";
}
dateMonth = dateMonth + " "+tempdate.Year.ToString();
StringBuilder strHeader = new StringBuilder();
strHeader.Append("<tr style='Height:30px;font-size:16px;font-weight:bolder'><th colspan='2'>集团中心汇总</th><th colspan='31'>" + dateMonth + "</th></tr>");
strHeader.Append("<tr><th rowspan='2'>级别/型号</th><th rowspan='2'>析别编码</th>");
for (int i = 0; i < brhNameList.Count; i++)
{
if (brhNameList[i].Equals("总计"))
{
strHeader.Append("<th colspan='3'>" + brhNameList[i] + "</th>");
}
else
{
strHeader.Append("<th colspan='2'>" + brhNameList[i].ToString().Substring(0,2) + "</th>");
}
}
strHeader.Append("</tr><tr>");
for (int i = 0; i < brhNameList.Count; i++)
{
if (brhNameList[i].Equals("总计"))
{
strHeader.Append("<th>订</th><th>确</th><th>%</th>");
}
else
{
strHeader.Append("<th>订</th><th>确</th>");
}
}
strHeader.Append("</tr>");
TableCell tc = new TableCell();
tc.Text = strHeader.ToString();
e.Row.Cells.Add(tc);
}
#endregion
e.Row.Cells[0].HorizontalAlign = HorizontalAlign.Left;
#region 计算总计和修饰
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[0].Visible = false;
int orderCount = 0;
int confirmation = 0;
for (int i = 3; i < e.Row.Cells.Count-1; i++)
{
e.Row.Cells[i].Width = Unit.Point(20);
if (i % 2 != 0)
{
if (e.Row.Cells[i].Text.Equals("") || e.Row.Cells[i].Text.Equals(" "))
{
orderCount += 0;
}
else
{
orderCount += Convert.ToInt32(e.Row.Cells[i].Text);
}
}
else
{
if (e.Row.Cells[i].Text.Equals("") || e.Row.Cells[i].Text.Equals(" "))
{
confirmation += 0;
}
else
{
confirmation += Convert.ToInt32(e.Row.Cells[i].Text);
}
}
}
if (e.Row.Cells[1].Text.Equals("") || e.Row.Cells[1].Text.Equals(" "))
{
e.Row.Cells[1].Text = e.Row.Cells[e.Row.Cells.Count - 1].Text;
e.Row.Cells[1].ColumnSpan = 2;
e.Row.Font.Bold = true;
e.Row.BackColor = Color.Aquamarine;
e.Row.Cells[2].Visible = false;
}
else if(e.Row.Cells[1].Text.Equals("小计"))
{
e.Row.BackColor = Color.Aquamarine;
e.Row.Cells[1].ColumnSpan = 2;
e.Row.Font.Bold = true;
e.Row.Cells[2].Visible = false;
}
else
{
e.Row.Cells[1].Width = Unit.Pixel(250);
}
e.Row.Cells[1].HorizontalAlign = HorizontalAlign.Left;
e.Row.Cells[e.Row.Cells.Count - 1].Visible = false;
double rate = 0;
if (confirmation != 0)
{
rate = Convert.ToDouble(orderCount) / Convert.ToDouble(confirmation);
}
TableCell tc1 = new TableCell();
tc1.Text = orderCount + "</td><td>" + confirmation + "</td><td>" + Convert.ToInt32(rate * 100) + "%";
e.Row.Cells.Add(tc1);
}
#endregion
}
转换月份格式
private string NumTodigital(int month)
{
string toMonth="";
switch (month)
{
case 1:
toMonth = "一";
break;
case 2:
toMonth = "二";
break;
case 3:
toMonth = "三";
break;
case 4:
toMonth = "四";
break;
case 5:
toMonth = "五";
break;
case 6:
toMonth = "六";
break;
case 7:
toMonth = "七";
break;
case 8:
toMonth = "八";
break;
case 9:
toMonth = "九";
break;
case 10:
toMonth = "十";
break;
case 11:
toMonth = "十一";
break;
case 12:
toMonth = "十二";
break;
default:
break;
}
return toMonth;
}
- 好难做的一个GridView
- 一个GridView的PagerTemplate分页
- 一个美观的gridview样式
- Android 一个简单的GridView
- 如何把一个Gridview的数据导入另一Gridview中？
- GridView增加一个统计行的方法
- GridView增加一个统计行的方法
- 一个GridView编辑删除的例子
- GridView增加一个统计行的方法
- 一个还不错的gridview 样式
- 一个GridView编辑删除的例子
- GridView增加一个统计行的方法
- 一个简单的GridView分页通用程序
- 一个比较好的GridView 控件
- 使用GridView出现的一个bug
- 自己封装的一个GridView控件
- GridView载入位图的一个例子
- GridView编写的一个图片浏览器
- 漫游庐山
- chmod命令详细用法（转）
- 甲骨文将以74亿美元收购Sun
- 给自己一点动力！
- 占豪收评---侃侃朝核问题
- 好难做的一个GridView
- Dll文件的注入
- c#字符处理小练习
- WWW的核心——HTTP协议
- string浅拷贝及其在dll中运用的注意事项
- 漫游庐山
- ASCCII 对照表
- C#中String类的几个方法(IndexOf、LastIndexOf、Substring)
- 漫游庐山
