在ASP.NET中将数据直接输出成Excel内容-优快云博客

本文链接：https://blog.youkuaiyun.com/tqh2000/article/details/1297093

本文介绍了一个简单的ASP.NET页面示例，用于展示如何从数据库中读取数据并将其以Excel格式呈现给用户。示例中包含了针对SQL Server和Access数据库的操作，并通过DataGrid控件显示数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ExcelExport.aspx

&lt;%@ Page Language=&quot;vb&quot; AutoEventWireup=&quot;false&quot; Codebehind=&quot;ExcelExport.aspx.vb&quot;

Inherits=&quot;aspxWeb.mengxianhui.com.ExcelExport&quot;%&gt;

&lt;!DOCTYPE HTML PUBLIC &quot;-//W3C//DTD HTML 4.0 Transitional//EN&quot;&gt;&lt;HTML&gt;

 &lt;HEAD&gt;    &lt;title&gt;ExcelExport&lt;/title&gt;

 &lt;meta name=&quot;GENERATOR&quot; content=&quot;Microsoft Visual Studio.NET 7.0&quot;&gt;

  &lt;meta name=&quot;CODE_LANGUAGE&quot; content=&quot;Visual Basic 7.0&quot;&gt;

 &lt;meta name=&quot;vs_defaultClientScript&quot; content=&quot;JavaScript&quot;&gt;

  &lt;meta name=&quot;vs_targetSchema&quot; content=&quot;http://schemas.microsoft.com/intellisense/ie5&quot;&gt;

&lt;/HEAD&gt;  &lt;body MS_POSITIONING=&quot;GridLayout&quot;&gt;

   &lt;form id=&quot;Form1&quot; method=&quot;post&quot; runat=&quot;server&quot;&gt;

     &lt;asp:datagrid id=&quot;DataGrid1&quot; runat=&quot;server&quot; CellPadding=&quot;4&quot; BackColor=&quot;White&quot;

     BorderColor=&quot;#CC9966&quot; BorderWidth=&quot;1px&quot; BorderStyle=&quot;None&quot; Width=&quot;100%&quot;

Height=&quot;100%&quot;        Font-Size=&quot;9pt&quot; Font-Names=&quot;宋体&quot;&gt;

    &lt;SelectedItemStyle Font-Bold=&quot;True&quot; ForeColor=&quot;#663399&quot; BackColor=&quot;#FFCC66&quot;&gt;

&lt;/SelectedItemStyle&gt;        &lt;AlternatingItemStyle BackColor=&quot;#FFCC99&quot;&gt;

&lt;/AlternatingItemStyle&gt;

      &lt;ItemStyle BorderWidth=&quot;2px&quot; ForeColor=&quot;#330099&quot; BorderStyle=&quot;Solid&quot;

  BorderColor=&quot;Black&quot; BackColor=&quot;White&quot;&gt;&lt;/ItemStyle&gt;

   &lt;HeaderStyle Font-Bold=&quot;True&quot; HorizontalAlign=&quot;Center&quot; BorderWidth=&quot;2px&quot;

     ForeColor=&quot;#FFFFCC&quot; BorderStyle=&quot;Solid&quot; BorderColor=&quot;Black&quot; BackColor=&quot;#990000&quot;&gt;&lt;/HeaderStyle&gt;

   &lt;/asp:datagrid&gt;    &lt;/form&gt;  &lt;/body&gt;&lt;/HTML&gt;

ExcelExport.aspx.vb

Public Class ExcelExport

 Inherits System.Web.UI.Page

 Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid#Region &quot;

Web 窗体设计器生成的代码 &quot;    '该调用是 Web 窗体设计器所必需的。

   &lt;System.Diagnostics.DebuggerStepThrough()&gt;

Private Sub InitializeComponent()

 End Sub    Private Sub Page_Init(ByVal sender As System.Object,

 ByVal e As System.EventArgs) _     Handles MyBase.Init

   'CODEGEN: 此方法调用是 Web 窗体设计器所必需的

    '不要使用代码编辑器修改它。        InitializeComponent()

  End Sub#End Region  Private Sub Page_Load(ByVal sender

 As System.Object, ByVal e As System.EventArgs) _    Handles

 MyBase.Load    '在此处放置初始化页的用户代码    ' 定义是否是 SQL Server 数据库，

这里为False    Dim blnIsSQLServer As System.Boolean = False

Dim strSQL As String    Dim objDataset As New DataSet()

Dim objConn As Object

 Dim strCnn As String    If blnIsSQLServer Then

  strCnn = &quot;User ID=sa;Initial Catalog=Northwind;Data Source=./NetSDK;&quot;

   objConn = New System.Data.SqlClient.SqlConnection(strCnn)

 objConn.Open()

  Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter()

   strSQL = &quot;Select * from customers where country='USA'&quot;

    objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)

     objAdapter.Fill(objDataset)

  Else

 strCnn = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; +

 Server.MapPath(&quot;Test.mdb&quot;)

 objConn = New System.Data.OleDb.OleDbConnection(strCnn)

 objConn.Open()

Dim objAdapter As New System.Data.OleDb.OleDbDataAdapter()

strSQL = &quot;Select Top 10 Title From Document&quot;

objAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand(strSQL, objConn)

 objAdapter.Fill(objDataset)    End If

Dim oView As New DataView(objDataset.Tables(0))

   DataGrid1.DataSource = oView    DataGrid1.DataBind()

  objConn.Close()    objConn.Dispose()    objConn = Nothing

  If Request.QueryString(&quot;bExcel&quot;) = &quot;1&quot; Then

   Response.ContentType = &quot;application/vnd.ms-excel&quot;

  ' 从Content-Type header中去除charset设置

   Response.Charset = &quot;&quot;      ' 关闭 ViewState

    Me.EnableViewState = False      Dim tw As New System.IO.StringWriter()

  Dim hw As New System.Web.UI.HtmlTextWriter(tw)      ' 获取control的HTML

    DataGrid1.RenderControl(hw)      ' 把HTML写回浏览器

    Response.Write(tw.ToString())      Response.End()

 End If  End SubEnd Class

Trackback: http://tb.blog.youkuaiyun.com/TrackBack.aspx?PostId=6935