使用VB.NET将Gridview导出到Excel

本文介绍了使用VB.NET将Gridview导出到Excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个带有gridview的页面，该页面正在使用母版页.导出按钮位于母版页上，我只需要从该页上导出实际页上的gridview数据.

I have a page with a gridview on it, which is using a master page. The export button is on the Master page from which I need to export ONLY the gridview data that is on the actual page.

gridview中有一些隐藏的列，这些列不能包含在导出到Excel的数据中.

There are some hidden columns on the gridview and these must not be included on the data exported to Excel.

如何做到这一点而又不抓住gridview周围的任何其他格式(即页面本身)?

How can I do this with out grabbing any other formatting around the gridview (i.e on the page itself)?

我基本上是在以下URL上使用代码:导出将Gridview数据转换为Excel-更改标题名称(我将其转换为VB.NET)，但似乎正在将网格中的所有数据导出到Excel，包括隐藏的列.

I am basically using the code on this URL: Export Gridview Data to Excel - Change the header name (I converted to VB.NET), but it seems to be exporting ALL data on the grid to Excel, including the hidden columns.

推荐答案

我实际上找到了另一种方法，它不影响页面本身，而只需要在母版页上完成即可.

I actually found another way, which does not affect the page itself, but only needs to be done on the masterpage.

不需要公共替代子VerifyRenderingInServerForm(作为控件的控件):

Public Overrides Sub VerifyRenderingInServerForm(control As Control) is not required :

'在母版页中导出点击事件:

'Export Click event in Masterpage :

Public Sub ButExportExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButExportExcel.Click

'*** take the paging and sorting out of the excel spreadsheet

Try
Dim sgv As GridView = CType(ContentPlaceHolder_body.FindControl("SummaryGridView"), GridView)
        If (sgv IsNot Nothing) Then
            sgv.AllowPaging = False
            sgv.AllowSorting = False
            sgv.DataBind()
        End If

        Dim sExportFileName As String = ""

        sExportFileName = Path.GetFileName(Request.PhysicalPath)
        sExportFileName = sExportFileName.Substring(0, sExportFileName.Length - 5) & ".xls"

        Export2(sExportFileName, sgv)

Catch ex As Exception

End Try

End Sub

'Export Sub
Public Sub Export2(ByVal fileName As String, ByVal gv As GridView)
    Dim sExportFileName As String = ""
    Dim sStringToReplace As String = ""

    gv.HeaderStyle.ForeColor = Drawing.Color.Black
    gv.HeaderStyle.BackColor = Drawing.Color.White
    gv.RowStyle.BackColor = Drawing.Color.White
    gv.HeaderStyle.Font.Bold = True
    gv.HeaderStyle.Font.Size = 10

    sExportFileName = Path.GetFileName(Request.PhysicalPath)
    sExportFileName = sExportFileName.Substring(0, sExportFileName.Length - 5) & ".xls"

    Dim attachment As String = "attachment; filename=" & sExportFileName
    HttpContext.Current.Response.ClearContent()
    HttpContext.Current.Response.AddHeader("content-disposition", attachment)
    HttpContext.Current.Response.ContentType = "application/ms-excel"
    Dim stw As New StringWriter()
    Dim htextw As New HtmlTextWriter(stw)

    Dim parent As Control = gv.Parent
    Dim GridIndex As Integer = 0
    If parent IsNot Nothing Then
        GridIndex = parent.Controls.IndexOf(gv)
        parent.Controls.Remove(gv)
    End If

    gv.RenderControl(htextw)

    If parent IsNot Nothing Then
        parent.Controls.AddAt(GridIndex, gv)
    End If

    'gv.RenderControl(htextw)
    HttpContext.Current.Response.Write(stw.ToString())
    Dim fi As New FileInfo(Server.MapPath("../JumpStart.css"))
    Dim sb As New System.Text.StringBuilder()
    Dim sr As StreamReader = fi.OpenText()
    'sStringToReplace = "class=""generalheader"""
    While sr.Peek() >= 0
        sb.Append(sr.ReadLine())
    End While
    sr.Close()

    Dim outputHtml = "<html><head><style type='text/css'>" + sb.ToString() + "</style></head>" + stw.ToString() + "</html>"

    Response.Write(outputHtml.ToString)

    'Response.Write("<html><head><style type='text/css'>" + sb.ToString() + "</style></head>" + stw.ToString() + "</html>")
    stw = Nothing
    htextw = Nothing
    Response.Flush()
    Response.[End]()


End Sub

这篇关于使用VB.NET将Gridview导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！