在ASP.NET中将数据直接输出成Excel内容
2024-07-10 12:57:12
供稿：网友
本文实现了将数据库中的数据直接输出到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
