将gridview的内容导出到Excel中去

Excel截图：

 

前台Code：

Code
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" EnableEventValidation = "false" AutoEventWireup="false" CodeFile="of_EquipmentEnquiries.aspx.vb" Inherits="_of_of_EquipmentEnquiries" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<table class="DETAIL">
    <tr>
        <td style="width:100%">
            <asp:GridView ID="gvEquipmentDeprec" runat="server"  Width ="100%" 
            AutoGenerateColumns="False" AllowPaging="True" pagesize="20"
            PagerStyle-HorizontalAlign ="Right">
            <Columns>     
            <asp:TemplateField HeaderText="No.">
                <HeaderStyle HorizontalAlign="Center" />
                <ItemStyle Width="30px"></ItemStyle>
            </asp:TemplateField>
            <asp:BoundField HeaderText="部门" DataField="DEPT_NAME" >
            </asp:BoundField>
            <asp:BoundField HeaderText="资产编号" DataField="EQUIPMENT_ID" >
            </asp:BoundField>
            <asp:BoundField HeaderText="品名" DataField="TYPE_NAME" >
            </asp:BoundField>
            <asp:BoundField HeaderText="原值" DataField="ORIGINAL_VAL" >
                <ItemStyle HorizontalAlign="Right"></ItemStyle>
            </asp:BoundField>
            <asp:TemplateField HeaderText="累折">
                <ItemStyle HorizontalAlign="Right"></ItemStyle>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="净值">
                <ItemStyle HorizontalAlign="Right"></ItemStyle>
            </asp:TemplateField>
            <asp:BoundField HeaderText="年限" DataField="AGE" >
            </asp:BoundField>
            <asp:TemplateField HeaderText="残值">
                <ItemStyle HorizontalAlign="Right"></ItemStyle>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="每台月折旧">
                <ItemStyle HorizontalAlign="Right"></ItemStyle>
            </asp:TemplateField>
            <asp:BoundField HeaderText="购入时间" DataField="GET_TIME" >
            </asp:BoundField>
            <asp:TemplateField HeaderText="备注">
            </asp:TemplateField>
            </Columns>
            <RowStyle CssClass="GridViewRowStyle"></RowStyle>
            <SelectedRowStyle CssClass="GridViewSelectedRowStyle"></SelectedRowStyle>
            <PagerStyle CssClass="GridViewPagerStyle" HorizontalAlign="Right"></PagerStyle>
            <HeaderStyle CssClass="GridViewHeaderStyle"></HeaderStyle>
            <AlternatingRowStyle CssClass="GridViewAlternatingRowStyle"></AlternatingRowStyle> 
            </asp:GridView>
        </td>
    </tr>
</table>
<table class="OPERATION">
    <tr>
        <td><asp:Button ID="btnExcel" runat="server" Text=""></asp:Button></td>
    </tr>
</table>

</asp:Content>

 

后台Code:

 

Code
''' <summary>
    '''GridView绑定
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub GvBind()
        '代码省略
    End Sub

''' <summary>
    ''' Excel
    ''' </summary>
    ''' <param name="FileType"></param>
    ''' <param name="FileName"></param>
    ''' <remarks></remarks>
    Private Sub Exports(ByVal FileType As String, ByVal FileName As String)
        Try
            Response.Charset = "GB2321"
            Response.ContentEncoding = System.Text.Encoding.UTF7
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString)
            Response.ContentType = FileType
            Me.EnableViewState = False
            Dim tw As New StringWriter
            Dim hw As New HtmlTextWriter(tw)

            '暂时把gvEquipmentDeprec变成不分页
            gvEquipmentDeprec.AllowPaging = False
            GvBind()

            gvEquipmentDeprec.RenderControl(hw)

            '把gvEquipmentDeprec变回分页
            gvEquipmentDeprec.AllowPaging = True
            GvBind()

            Response.Write(tw.ToString)
            Response.End()
        Catch ex As Exception
        End Try 
    End Sub

''' <summary>
    ''' 重写VerifyRenderingInServerForm
    ''' </summary>
    ''' <param name="control"></param>
    ''' <remarks></remarks>
    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
    End Sub

''' <summary>
    ''' 导出Excel
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        Exports("application/ms-excel", "固定资产折旧.xls")
    End Sub

 

需要注意的几点：

1.需要overrides一下VerifyRenderingInServerForm方法(这一点非常重要，否则在点击按钮后会报错)

2.在页面的page属性中加入EnableEventValidation = "false"