使用 ClosedXML 导出数据-解网

问：

我知道这里有很多关于使用 ClosedXML 将数据导出到 Excel 的材料，但我的情况有点独特。

我下面有一个工作代码，可以成功地将数据从 GridView 导出到 excel 文件，但我的情况是独一无二的，因为当您运行下面的代码时，数据会在页面加载时显示。

我们的问题是我们宁愿不在表单上显示数据。

我们只想看到导出到 excel 按钮。这可能吗？

  <asp:Button ID="Button1" Text="Get Excel file" OnClick="ExportToExcel" runat="server" />

  Imports System
  Imports System.Collections.Generic
  Imports System.Configuration
  Imports System.Data
  Imports System.Data.SqlClient
  Imports System.Drawing
  Imports System.Globalization
  Imports System.IO
  Imports ClosedXML.Excel
  Imports System.Net.Mail
  Imports System.Linq
  Imports System.Net
  Imports System.Text
  Imports System.Threading
  Imports System.Web
  Imports System.Web.Configuration
  Imports System.Web.UI
  Imports System.Web.UI.WebControls
  Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        BindData()
  End Sub

  Private Function GetData() As DataTable
            Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using con As New SqlConnection(constr)
                Using cmd As New SqlCommand("SELECT * FROM mytable")
                    Using sda As New SqlDataAdapter()
                        cmd.Connection = con
                        sda.SelectCommand = cmd
                        Using dt As New DataTable()
                            sda.Fill(dt)
                            Return dt
                        End Using
                    End Using
                End Using
            End Using
    End Function

    Private Sub BindData()
        Dim dt As DataTable = GetData()
        ViewState("Data") = dt
        Dim TotalRecords = Dt.Rows.Count()
        GridView1.DataSource = Dt
        GridView1.DataBind()
    End Sub
    Private Sub ExportGridToExcel()
        Try
            Dim dt As DataTable = CType(ViewState("Data"), DataTable)
            dt.TableName = "Details"

            Using wb As XLWorkbook = New XLWorkbook()
                wb.Worksheets.Add(dt)
                Response.Clear()
                Response.Buffer = True
                Response.Charset = ""
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                Response.AddHeader("content-disposition", "attachment;filename=govt.xlsx")

                Using MyMemoryStream As MemoryStream = New MemoryStream()
                    wb.SaveAs(MyMemoryStream)
                    MyMemoryStream.WriteTo(Response.OutputStream)
                    Response.Flush()
                    dt.Dispose()
                    Response.[End]()
                End Using
            End Using

        Catch ex As Exception
        End Try
    End Sub
    Protected Sub ExportToExcel(sender As Object, e As EventArgs)
        ExportGridToExcel()
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        ' Verifies that the control is rendered 
    End Sub

asp.net vb.net gridview

Protected Sub cmdExport_Click(sender As Object, e As EventArgs)

    Dim cmdSQL As New SqlCommand("SELECT * FROM tblHotelsA
                                 ORDER BY HotelName")

    ExportToExcel(cmdSQL, "Hotels")

End Sub

Sub ExportToExcel(cmdSQL As SqlCommand, sTable As String)

    Try
        Dim dt As DataTable = GetData(cmdSQL)
        dt.TableName = sTable
        Using wb As XLWorkbook = New XLWorkbook()
            wb.Worksheets.Add(dt)
            Response.Clear()
            Response.Buffer = True
            Response.Charset = ""
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            Response.AddHeader("content-disposition", $"attachment;filename={sTable}.xlsx")

            Using MyMemoryStream As MemoryStream = New MemoryStream()
                wb.SaveAs(MyMemoryStream)
                MyMemoryStream.WriteTo(Response.OutputStream)
                Response.Flush()
                dt.Dispose()
                Response.[End]()
            End Using
        End Using

    Catch ex As Exception
    End Try

End Sub

Private Function GetData(cmd As SqlCommand) As DataTable
    Dim constr As String =
        ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Dim dt As New DataTable
    Using con As New SqlConnection(constr)
        Using (cmd)
            cmd.Connection = con
            con.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using

    Return dt

End Function

因此，请注意，在上面，您可以设置将要下载的表格和 excel 文件的“名称”。

因此，如果您使用“MyHotels”，则 excel 文件的名称为 MyHotels.xlsx。

使用 ClosedXML 导出数据

Export Data with ClosedXML

评论

评论