有排序分頁的GridView匯出Excel

2008-03-20

9495
0
ASP.NET

一般GridView匯出Excel如果用到了排序及分頁的功能，就匯不出去
所以需要把欄位稍為做個處理。將排序及分頁其他的超連結功能替換成字串
這樣就可以匯出了。

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">

        <div>
            <asp:RadioButtonList ID="RadioButtonList1" runat="server" RepeatDirection="Horizontal">
                <asp:ListItem Selected="True">目前頁面</asp:ListItem>
                <asp:ListItem>全部頁面</asp:ListItem>
                <asp:ListItem>前100筆</asp:ListItem>
            </asp:RadioButtonList>
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
                AutoGenerateColumns="False" DataKeyNames="OrderID" DataSourceID="SqlDataSource1"
                EmptyDataText="沒有資料錄可顯示。">
                <Columns>
                    <asp:BoundField DataField="ShipVia" HeaderText="ShipVia" SortExpression="ShipVia" />
                    <asp:BoundField DataField="Freight" HeaderText="Freight" SortExpression="Freight" />
                    <asp:BoundField DataField="ShipName" HeaderText="ShipName" SortExpression="ShipName" />
                    <asp:BoundField DataField="ShipAddress" HeaderText="ShipAddress" SortExpression="ShipAddress" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString1 %>"
                DeleteCommand="DELETE FROM [Orders] WHERE [OrderID] = @OrderID" InsertCommand="INSERT INTO [Orders] ([CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight, @ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry)"
                ProviderName="<%$ ConnectionStrings:NorthwindConnectionString1.ProviderName %>"
                SelectCommand="SELECT [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry] FROM [Orders]"
                UpdateCommand="UPDATE [Orders] SET [CustomerID] = @CustomerID, [EmployeeID] = @EmployeeID, [OrderDate] = @OrderDate, [RequiredDate] = @RequiredDate, [ShippedDate] = @ShippedDate, [ShipVia] = @ShipVia, [Freight] = @Freight, [ShipName] = @ShipName, [ShipAddress] = @ShipAddress, [ShipCity] = @ShipCity, [ShipRegion] = @ShipRegion, [ShipPostalCode] = @ShipPostalCode, [ShipCountry] = @ShipCountry WHERE [OrderID] = @OrderID">
                <DeleteParameters>
                    <asp:Parameter Name="OrderID" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="CustomerID" Type="String" />
                    <asp:Parameter Name="EmployeeID" Type="Int32" />
                    <asp:Parameter Name="OrderDate" Type="DateTime" />
                    <asp:Parameter Name="RequiredDate" Type="DateTime" />
                    <asp:Parameter Name="ShippedDate" Type="DateTime" />
                    <asp:Parameter Name="ShipVia" Type="Int32" />
                    <asp:Parameter Name="Freight" Type="Decimal" />
                    <asp:Parameter Name="ShipName" Type="String" />
                    <asp:Parameter Name="ShipAddress" Type="String" />
                    <asp:Parameter Name="ShipCity" Type="String" />
                    <asp:Parameter Name="ShipRegion" Type="String" />
                    <asp:Parameter Name="ShipPostalCode" Type="String" />
                    <asp:Parameter Name="ShipCountry" Type="String" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="CustomerID" Type="String" />
                    <asp:Parameter Name="EmployeeID" Type="Int32" />
                    <asp:Parameter Name="OrderDate" Type="DateTime" />
                    <asp:Parameter Name="RequiredDate" Type="DateTime" />
                    <asp:Parameter Name="ShippedDate" Type="DateTime" />
                    <asp:Parameter Name="ShipVia" Type="Int32" />
                    <asp:Parameter Name="Freight" Type="Decimal" />
                    <asp:Parameter Name="ShipName" Type="String" />
                    <asp:Parameter Name="ShipAddress" Type="String" />
                    <asp:Parameter Name="ShipCity" Type="String" />
                    <asp:Parameter Name="ShipRegion" Type="String" />
                    <asp:Parameter Name="ShipPostalCode" Type="String" />
                    <asp:Parameter Name="ShipCountry" Type="String" />
                    <asp:Parameter Name="OrderID" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>
            <asp:Button ID="Button1" runat="server" Text="匯出Excel" /></div>
    </form>
</body>
</html>

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

If Me.RadioButtonList1.SelectedIndex = 1 Then

Me.GridView1.AllowPaging = False

Me.GridView1.DataBind()

ElseIf Me.RadioButtonList1.SelectedIndex = 2 Then

Me.GridView1.PageSize = 100

Me.GridView1.DataBind()

End If

GvToExcelOut.Export("Customers.xls", Me.GridView1)

End Sub

End Class

Imports System

Imports System.Data

Imports System.Configuration

Imports System.IO

Imports System.Web

Imports System.Web.Security

Imports System.Web.UI

Imports System.Web.UI.WebControls

Imports System.Web.UI.WebControls.WebParts

Imports System.Web.UI.HtmlControls

Public Class GvToExcelOut

''' <summary>

''' 輸出Excel

''' </summary>

''' <param name="fileName">檔案名稱</param>

''' <param name="gv">GridView</param>

''' <remarks></remarks>

Public Shared Sub Export(ByVal fileName As String, ByVal gv As GridView)

HttpContext.Current.Response.Clear()

HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))

HttpContext.Current.Response.ContentType = "application/ms-excel"

Dim sw As StringWriter = New StringWriter

Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)

' 將gridview塞進新建Table

Dim table As Table = New Table

table.GridLines = gv.GridLines

' 新增表頭

If (Not (gv.HeaderRow) Is Nothing) Then

PrepareControlForExport(gv.HeaderRow)

table.Rows.Add(gv.HeaderRow)

End If

' 新增Rows

For Each row As GridViewRow In gv.Rows

PrepareControlForExport(row)

table.Rows.Add(row)

' 新增Footer

If (Not (gv.FooterRow) Is Nothing) Then

PrepareControlForExport(gv.FooterRow)

table.Rows.Add(gv.FooterRow)

End If

table.RenderControl(htw)

' 輸出

HttpContext.Current.Response.Write(sw.ToString)

HttpContext.Current.Response.End()

End Sub

''' <summary>

''' GridView的欄位處理

''' </summary>

''' <param name="control">欄位</param>

''' <remarks></remarks>

Private Shared Sub PrepareControlForExport(ByVal control As Control)

Dim i As Integer = 0

Do While (i < control.Controls.Count)

Dim current As Control = control.Controls(i)

If (TypeOf current Is LinkButton) Then