GridView 顯示小計及總計

ASP.NET2.0之後新增的GridView的確非常的好用,不過在頁面的靈活度表現上,常常會需要大家多花點心思去設計還有修改,當然所有Table做得到的他都一定做得到,畢竟GridView最後產生的結果也是Html的Table Tag,由於最近收到使用GridView來製做內含頁小計以及總計的顯示方法,記錄下來跟大家分享一下

需要顯示的結果
客戶　客戶編號　購買數量　百分比
Andy　0001　　　5　　　　50%
Bill　0002　　　3　　　　30%
Ivan　0004　　　2　　　　20%
小計　　　　　　10　　　　100%
總計　　　　　　10　　　　100%

先來說明一下做法
GridView 基本上分三種資料顯示的區塊"Header、Data、Footer"
這次為了要做到我所要的功能就必須在Footer的部分來做手腳

先給大家看一下完成的結果




詳細程式碼如下:

1.新增資料庫並使用我附註所列的Sql語法新增Table及資料
2.編寫RowDataBound事件
　注意下面程式碼sum1,sum2必須宣告在Global並於Footer處理結束時將兩個數值重新設定為0，避免計算有問題，我這邊是使用int型態，可以根據要精算的小數點位數更改為Double來處理，相關StoredProcedure回傳資料也要記得同時修改

switch (e.Row.RowType)
{
case DataControlRowType.DataRow:
DataRowView Row = (DataRowView)e.Row.DataItem;
sum1 += Convert.ToInt32(Row[2].ToString());
sum2 += Convert.ToInt32(Row[3].ToString());
e.Row.Cells[3].Text = Row[3].ToString() + "%";
break;
case DataControlRowType.Footer:
e.Row.Cells[0].Text = "小計";
e.Row.Cells[2].Text = sum1.ToString();
e.Row.Cells[3].Text = sum2.ToString() + "%";
　　　　　　　　　sum1=0;
　　　　　　　　　sum2=0;
break;
default:
break;
}

3.編寫PreRender事件

//新增Footer
GridViewRow footer = new GridViewRow(-1, -1, DataControlRowType.Footer, DataControlRowState.Normal);
TableCell dtc0 = new TableCell();
TableCell dtc1 = new TableCell();
TableCell dtc2 = new TableCell();
TableCell dtc3 = new TableCell();
DataTable dt = new DataTable();
DataView dv = new DataView();
int iCount = 0;
int iPercent = 0;
dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty); //Get DataView
dt = dv.ToTable();
iCount = Convert.ToInt32(dt.Compute("sum(Count)", ""));
iPercent = Convert.ToInt32(dt.Compute("sum(Percent)", ""));

dtc0.Text = "總計";
dtc1.Text = " ";
dtc2.Text = iCount.ToString();
dtc3.Text = iPercent.ToString() + "%";
footer.Controls.Add(dtc0);
footer.Controls.Add(dtc1);
footer.Controls.Add(dtc2);
footer.Controls.Add(dtc3);
GridView1.Controls[0].Controls.AddAt(GridView1.Controls[0].Controls.Count - 1, footer);

附註:
sql語法

--新增Table
CREATE TABLE [dbo].[Orders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Product] [varchar](10) NOT NULL,
[Personal_ID] [varchar](10) NOT NULL,
[Personal_Name] [varchar](10) NOT NULL,
[Purchase_Date] [datetime] NOT NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[Product] ASC
) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------
--新增資料
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('Wii','Andy','0001','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('PSP','Andy','0001','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('NDSL','Andy','0001','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('XBOX','Andy','0001','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('PS3','Andy','0001','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('Wii','Bill','0002','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('PSP','Bill','0002','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('NDSL','Jay','0003','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('XBOX','Jay','0003','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('PS3','Jay','0003','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('Wii','Ivan','0004','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('PSP','Ivan','0004','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('NDSL','Ivan','0004','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('XBOX','Ivan','0004','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('PS3','Louis','0005','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('Wii','Louis','0005','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('PSP','Kenny','0006','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('NDSL','Kenny','0006','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('XBOX','Kenny','0006','2008/06/19')
insert into Orders(Product,Personal_Name,Personal_ID,Purchase_Date)
values('PS3','Kenny','0006','2008/06/19')


Stored Procedure

ALTER PROCEDURE [dbo].[prOrderStatistic]

AS
BEGIN
SET NOCOUNT ON;

declare @Total as int
select @Total = count(*) from Orders with (nolock)
select Personal_Name,Personal_ID,'Count' = count(*), 'Percent'=count(*)*100/@Total from Orders
group by Personal_Name,Personal_ID order by 'Count' desc


END