在ASP.NET中将数据直接输出成Excel内容
本文实现了将数据库中的数据直接输出到Excel文件格式并在浏览器里输出。下面就是实现的例子：
查看例子
ExcelExport.aspx
<![CDATA[<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0"><meta name="CODE_LANGUAGE" content="Visual Basic 7.0"><meta name="vs_defaultClientScript" content="JavaScript"><meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">]]>
ExcelExport.aspx.vb
<![CDATA[Public Class ExcelExport<br />
Inherits System.Web.UI.Page<br />
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid<br />
#Region " Web 窗体设计器生成的代码 "<br />
'该调用是 Web 窗体设计器所必需的。<br />
<system.diagnostics.debuggerstepthrough> Private Sub InitializeComponent()<br />
End Sub<br />
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) _<br />
Handles MyBase.Init<br />
'CODEGEN: 此方法调用是 Web 窗体设计器所必需的<br />
'不要使用代码编辑器修改它。<br />
InitializeComponent()<br />
End Sub<br />
#End Region<br />
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _<br />
Handles MyBase.Load<br />
'在此处放置初始化页的用户代码<br />
' 定义是否是 SQL Server 数据库，这里为False<br />
Dim blnIsSQLServer As System.Boolean = False<br />
Dim strSQL As String<br />
Dim objDataset As New DataSet()<br />
Dim objConn As Object<br />
Dim strCnn As String<br />
If blnIsSQLServer Then<br />
strCnn = "User ID=sa;Initial Catalog=Northwind;Data Source=./NetSDK;"<br />
objConn = New System.Data.SqlClient.SqlConnection(strCnn)<br />
objConn.Open()<br />
Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter()<br />
strSQL = "Select * from customers where country='USA'"<br />
objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)<br />
objAdapter.Fill(objDataset)<br />
Else<br />
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Test.mdb")<br />
objConn = New System.Data.OleDb.OleDbConnection(strCnn)<br />
objConn.Open()<br />
Dim objAdapter As New System.Data.OleDb.OleDbDataAdapter()<br />
strSQL = "Select Top 10 Title From Document"<br />
objAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand(strSQL, objConn)<br />
objAdapter.Fill(objDataset)<br />
End If<br />
Dim oView As New DataView(objDataset.Tables(0))<br />
DataGrid1.DataSource = oView<br />
DataGrid1.DataBind()<br />
objConn.Close()<br />
objConn.Dispose()<br />
objConn = Nothing<br />
If Request.QueryString("bExcel") = "1" Then<br />
Response.ContentType = "application/vnd.ms-excel"<br />
' 从Content-Type header中去除charset设置<br />
Response.Charset = ""<br />
' 关闭 ViewState<br />
Me.EnableViewState = False<br />
Dim tw As New System.IO.StringWriter()<br />
Dim hw As New System.Web.UI.HtmlTextWriter(tw)<br />
' 获取control的HTML<br />
DataGrid1.RenderControl(hw)<br />
' 把HTML写回浏览器<br />
Response.Write(tw.ToString())<br />
Response.End()<br />
End If<br />
End Sub<br />
End Class<br />
</system.diagnostics.debuggerstepthrough>]]>