将web中多控件数据导入到EXECL

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI

Public Class WebForm1
    Inherits System.Web.UI.Page

#Region " Web 窗体设计器生成的代码 "

    '该调用是 Web 窗体设计器所必需的。
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents RadioButton1 As System.Web.UI.WebControls.RadioButton
    Protected WithEvents RButton As System.Web.UI.WebControls.RadioButton
    Protected WithEvents RadioButton2 As System.Web.UI.WebControls.RadioButton
    Protected WithEvents lblTeacher As System.Web.UI.WebControls.Label
    Protected WithEvents txtTeacher As System.Web.UI.WebControls.TextBox
    Protected WithEvents btnTeacher As System.Web.UI.WebControls.Button
    Protected WithEvents pnlTeacher As System.Web.UI.WebControls.Panel
    Protected WithEvents Panel1 As System.Web.UI.WebControls.Panel
    Protected WithEvents myDataGrid As System.Web.UI.WebControls.DataGrid
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Button2 As System.Web.UI.WebControls.Button

    '注意: 以下占位符声明是 Web 窗体设计器所必需的。
    '不要删除或移动它。
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
        '不要使用代码编辑器修改它。
        InitializeComponent()
    End Sub

#End Region
    Dim sqlConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    Dim sql As String  '定义SQL语句
    Dim strGridCaption As String   '定义显示在DATAGRID标题

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     
    End Sub

    '读取并绑定数据
    Public Sub ToDataGrid(ByVal sqlString As String, ByVal dtGrid As DataGrid, ByVal tbDate As String, ByVal strGrid As String)
        dtGrid.Dispose()
        sqlConnection.Open()
        Dim sqlCMD As SqlDataAdapter = New SqlDataAdapter(sql, sqlConnection)
        Dim Result As DataSet = New DataSet
        sqlCMD.Fill(Result, "tbDate")
        Dim myTable As Data.DataTable = New Data.DataTable
        myTable = Result.Tables("tbDate")
        If myTable.Rows.Count >= 1 Then
            Dim dr As Data.DataRow
            dr = myTable.NewRow()
            dr(0) = "合计"
            dr(1) = "数量"
            dr(2) = "第三"
            myTable.Rows.Add(dr)
        End If
        dtGrid.DataSource = myTable
        dtGrid.DataBind()
        dtGrid.Caption = strGrid
        Dim i As Integer
        '   i = dtGrid.Columns.Count()
        i = myTable.Rows.Count

        Label1.Text = "总记录：" & i & "条" & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;统计时间" & Format(Now, "d")
        sqlConnection.Close()
    End Sub
    '将DATAGRID中显示的函数转换到EXCEL文件中

    Public Function ToExcel(ByVal ctl As System.Web.UI.Control, ByVal ctl2 As System.Web.UI.Control)
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=execl.xls")
        Response.ContentType = "application/vnd.ms-execl"
        HttpContext.Current.Response.Charset = "utf-8"
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default
        ctl.Page.EnableViewState = True
        ctl2.Page.EnableViewState = True
        Dim tw As System.IO.StringWriter = New System.IO.StringWriter
        Dim hw As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(tw)
        Dim hw2 As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(tw)
        ctl.Visible = True
        ctl.RenderControl(hw)
        ctl2.Visible = True
        ctl2.RenderControl(hw)
        HttpContext.Current.Response.Write(tw.ToString())
        HttpContext.Current.Response.End()
    End Function

    Private Sub btnTeacher_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTeacher.Click
        sql = "select right(AI.a_id,10) as 论文编号,AI.a_name as 论文名称,AI.a_publiction as 发表刊物,O.o_name as 办公室,TA.t_serial as 作者序号 from article_info AI,teacher_article TA,office O,teacher_info TI where AI.a_id in (select a_id from teacher_article where t_id ='100001') and AI.a_id = TA.a_id and TA.t_id = '100001' and TI.t_id = '100001' and TI.O_ID = O.O_ID"
        Dim s As String
        s = "湖南商学院科研管理信息系统教师个人科研论文统计结果"
        s += "<br>"
        s += "&nbsp;&nbsp;&nbsp;&nbsp;" & "教师编号：" & Trim(txtTeacher.Text)
        ToDataGrid(sql, myDataGrid, "article_info", s)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        ToExcel(myDataGrid, Label1)
    End Sub
End Class