在ASP.NET中將資料直接輸出成Excel格式

   本文實現了將資料庫中的資料直接輸出到Excel檔案格式並在瀏覽器裡輸出。下面就是實現的例子：
　　查看例子

　　ExcelExport.aspx

＜%@ Page Language="vb" AutoEventWireup="false" Codebehind="ExcelExport.aspx.vb"
Inherits="aspxWeb.mengxianhui.com.ExcelExport"%＞
＜!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"＞
＜HTML＞
　＜HEAD＞
　　＜title＞ExcelExport＜/title＞
　　＜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"＞
　＜/HEAD＞
＜body MS_POSITIONING="GridLayout"＞
　＜form id="Form1" method="post" runat="server"＞
　　＜asp:datagrid id="DataGrid1" runat="server" CellPadding="4" BackColor="White"
BorderColor="#CC9966" BorderWidth="1px" BorderStyle="None" Width="100%" Height="100%"
Font-Size="9pt" Font-Names="宋體"＞
　　＜SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66"＞＜/SelectedItemStyle＞
　　＜AlternatingItemStyle BackColor="#FFCC99"＞＜/AlternatingItemStyle＞
　　＜ItemStyle BorderWidth="2px" ForeColor="#330099" BorderStyle="Solid"
BorderColor="Black" BackColor="White"＞＜/ItemStyle＞
　　＜HeaderStyle Font-Bold="True" HorizontalAlign="Center" BorderWidth="2px"
ForeColor="#FFFFCC" BorderStyle="Solid" BorderColor="Black" BackColor="#990000"＞＜/HeaderStyle＞
　　＜/asp:datagrid＞
　＜/form＞
＜/body＞
＜/HTML＞
 

　　ExcelExport.aspx.vb

Public Class ExcelExport
Inherits System.Web.UI.Page
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
#Region " Web Form設計器產生的程式碼 "
'該調用是 Web Form設計器所必需的。
＜System.Diagnostics.DebuggerStepThrough()＞ Private Sub InitializeComponent()
End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Init
　'CODEGEN: 此方法調用是 Web Form設計器所必需的
　'不要使用代碼編輯器修改它。
　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 = "User ID=sa;Initial Catalog=Northwind;Data Source=.\NetSDK;"
　　objConn = New System.Data.SqlClient.SqlConnection(strCnn)
　　objConn.Open()
　　Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter()
　　strSQL = "Select * from customers where country='USA'"
　　objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)
　　objAdapter.Fill(objDataset)
　Else
　　strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Test.mdb")
　　objConn = New System.Data.OleDb.OleDbConnection(strCnn)
　　objConn.Open()
　　Dim objAdapter As New System.Data.OleDb.OleDbDataAdapter()
　　strSQL = "Select Top 10 Title From Document"
　　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("bExcel") = "1" Then
　　Response.ContentType = "application/vnd.ms-excel"
　　' 從Content-Type header中去除charset設定
　　Response.Charset = ""
　　' 關閉 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 Sub
End Class