您当前的位置: 首页 » ASP/ASP.NET编程 » 在ASP.NET中将数据直接输出成Excel格式
在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 窗体设计器生成的代码 "
'该调用是 Web 窗体设计器所必需的。
＜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 窗体设计器所必需的
'不要使用代码编辑器修改它。
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
查看例子
<以下为引用的内容：
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 窗体设计器生成的代码 "
'该调用是 Web 窗体设计器所必需的。
＜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 窗体设计器所必需的
'不要使用代码编辑器修改它。
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