[ASP.NET] GridView 加上總計並匯出Excel的小技巧

2010-08-16

10972
0
ASP.NET

摘要:[ASP.NET] GridView 加上總計並匯出Excel的小技巧

有時候要做些GridView，但客戶只要求能匯出有金額總計的Excel，

這時候常常覺得如果要為了這點東西做個報表，就會覺得有點懶，

所以就在footer加上總計，所以就直接匯出Excel，

用個簡單的範例來看，

匯出的Excel結果

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>範例Excel</title>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
        <asp:SqlDataSource ID="SqlDS_MT" runat="server"
            ConnectionString="<%$ ConnectionStrings:testCarsh_conndb %>"
            CancelSelectOnNullParameter="False" SelectCommand="SELECT * FROM [Maintain]">
        </asp:SqlDataSource>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataSourceID="SqlDS_MT" >
    <Columns>
        <asp:BoundField DataField="BADYNUM" HeaderText="車牌號碼" SortExpression="BADYNUM" />

        <asp:BoundField DataField="Mtdate" HeaderText="維修日期" SortExpression="Mtdate"  DataFormatString="{0:d}"/>

        <asp:BoundField DataField="ItemNum" HeaderText="零件編號" SortExpression="ItemNum" />

        <asp:BoundField DataField="ItemName" HeaderText="維修名稱" SortExpression="ItemName" />


        <asp:BoundField DataField="Unit" HeaderText="單位" SortExpression="Unit" />


        <asp:BoundField DataField="Price" HeaderText="單價" SortExpression="Price" />


        <asp:BoundField DataField="Num" HeaderText="數量" SortExpression="Num" />

        <asp:BoundField DataField="TotalPrice" HeaderText="總價" SortExpression="TotalPrice" />

        <asp:BoundField DataField="FacName" HeaderText="維修廠名" SortExpression="FacNameP" />

    </Columns>
    </asp:GridView>

            <div>
                <asp:Label ID="LabCount" runat="server" Text=""></asp:Label>
                <asp:Label ID="LabPage" runat="server" Text=""></asp:Label>
            </div>
            <div>
                <asp:Button ID="btnXLS" runat="server" style="margin-left:10px;" Text="匯出Excel" />
            </div>
    </div>
    </form>
</body>
</html>

有一個好用的小技巧，是在統計資料有幾筆共幾頁時，往往可能要去SQL下個Count來計算，可是這樣就做了一次查詢，

好啦，又開始發懶，所以我直接把SqlDataSource裡面的資料做了統計，

但記得要把SqlDataSource的DataSourceMode改為DataSet(預設是DataSet)，

Protected Sub SqlDS_MT_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDS_MT.Selected

'計算資料筆數

Me.LabCount.Text = "共 " & e.AffectedRows.ToString() & " 筆資料"

End Sub

Protected Sub GridView_DataBound(ByVal sender As Object, ByVal e As EventArgs) Handles GridView1.DataBound

'計算頁數

Me.LabPage.Text = "，共 " & GridView1.PageCount.ToString() & "頁"

End Sub

接著我要在footer做手腳，因為我的GridView有九個欄位，所以一共會產生九欄，

GridView所產生的也是<tr><td>這種HTML標籤，這時候就很像再用DreamWeaver一樣，

我先把這九欄清除，然後做合併儲存格的動作，

Protected Sub GridView_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowCreated

If Not (e.Row.RowType = DataControlRowType.Footer) Then

Exit Sub

End If

Dim footer As TableCellCollection = e.Row.Cells

'清除Footer原本的Cells

footer.Clear()

Dim tc1, tc2, tc3 As New TableCell()

'第一個cell

tc1.Text = "訂單總數："

tc1.ColumnSpan = 2

tc1.HorizontalAlign = HorizontalAlign.Right

footer.Add(tc1)

'第二個cell

footer.Add(New TableCell())

'第三個Cell

tc2.Text = "合計金額："

tc2.ColumnSpan = 4

tc2.HorizontalAlign = HorizontalAlign.Right

footer.Add(tc2)

'第四個Cell

tc3.ColumnSpan = 2

tc3.HorizontalAlign = HorizontalAlign.Right

footer.Add(tc3)

End Sub

但這個做出來的Footer只是空的欄位，接下來就可以把合計的筆數和金額，

把筆數丟到第二個TableCell，然後金額放入第四個TableCell，再修改一下GridView_DataBound

Protected Sub GridView_DataBound(ByVal sender As Object, ByVal e As EventArgs) Handles GridView1.DataBound

If Me.GridView1.Rows.Count = 0 Then

Exit Sub

End If

Dim sum As Integer = 0

For Each row As GridViewRow In Me.GridView1.Rows

sum += Double.Parse(row.Cells(7).Text)

Me.GridView1.FooterRow.Cells(1).Text = Me.GridView1.Rows.Count()

Me.GridView1.FooterRow.Cells(3).Text = Math.Round((sum), 1).ToString()

Me.LabPage.Text = "，共 " & GridView1.PageCount.ToString() & "頁"

End Sub

在這邊的合計金額我直接把所有Row的值做累加，而不再去資料庫做一次統計，

而在放入Cell中時，是從0開始，所以第二欄就是Cells(1)，第四欄就是Cells(3)，

頁面上就可以看到

匯出的Excel也會包含合計的資訊

回首頁