| 将DataGrid输出到Excel文件-.NET教程,数据库应用 |
|
|
|
在web from上输出数据到excel有两种方法，一个是有数据库直接导出；另外一个方法是由datagrid直接输出到excel文件。下面得代码实现了这两个功能。注意：在使用时要引用microsoft office web components 9.0 com组件，另外注意设置要保存文件得目录具有匿名可修改的权限。
datagridtoexcel.aspx <![CDATA[<%@ page language="vb" enableviewstate="false" autoeventwireup="false" codebehind="datagridtoexcel.<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>x.vb"
inherits="<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>xweb.mengxianhui.com.datagridtoexcel"%>
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<title id="mengxianhui" runat="server"></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" style="font-size:9pt">
<form id="form1" method="post" runat="server">
<<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:label id="label1" runat="server"></<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:label>
<<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:textbox id="xlfile" runat="server"></<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:textbox>
<br>
<br>
<<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:button id="exportdatabase2excel" runat="server" />
<<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:button id="exportdatagrid2excel" runat="server" />
<br>
<<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:datagrid id="datagrid1" runat="server" autogeneratecolumns="false" bordercolor="#cc9966"
borderstyle="none" borderwidth="1px" backcolor="white" cellpadding="4">
<itemstyle forecolor="#330099" backcolor="white"></itemstyle>
<headerstyle font-bold="true" forecolor="#ffffcc" backcolor="#990000"></headerstyle>
<columns>
<<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:boundcolumn datafield="title"></<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:boundcolumn>
<<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:boundcolumn datafield="author"></<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:boundcolumn>
</columns>
</<a href="http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank">asp</a>:datagrid>
</form>
</body>
</html>
]]>
datagridtoexcel.aspx.vb <![CDATA[imports system
imports system.data
imports system.data.oledb
imports owc
public class datagridtoexcel
inherits system.web.ui.page
protected withevents xlfile as system.web.ui.webcontrols.textbox
protected withevents datagrid1 as system.web.ui.webcontrols.datagrid
protected withevents exportdatagrid2excel as system.web.ui.webcontrols.button
protected withevents exportdatabase2excel as system.web.ui.webcontrols.button
protected withevents label1 as system.web.ui.webcontrols.label
protected mengxianhui as new htmlgenericcontrol()
private cnn as oledbconnection = new oledbconnection("provider=microsoft.jet.oledb.4.0;data source="_
+ server.mappath("test.mdb"))
private <a href="http://www.chinaitpower.com/dev/database/mssql/index.html" target="_blank">sql</a> as oledbcommand = new oledbcommand("select top 50 title,author from document", cnn)
#region " web form designer generated code "
this call is required by the web form designer.
<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: this method call is required by the web form designer
do not modify it using the code editor.
initializecomponent()
end sub
#end region
private sub page_load(byval sender as system.object, byval e as system.eventargs)_
handles mybase.load
label1.text = "请输入要保存得<a href="http://www.chinaitpower.com/soft/tools/file/index.html" target="_blank">文件</a>名字："
exportdatagrid2excel.text = "由datagrid生成excel<a href="http://www.chinaitpower.com/soft/tools/file/index.html" target="_blank">文件</a>"
exportdatabase2excel.text = "<a href="http://www.chinaitpower.com/dev/database/index.html" target="_blank">数据库</a>直接生成excel<a href="http://www.chinaitpower.com/soft/tools/file/index.html" target="_blank">文件</a>"
datagrid1.columns(0).headerstyle.horizontalalign = horizontalalign.center
datagrid1.columns(0).headertext = "文章名称"
datagrid1.columns(1).headertext = "作者"
datagrid1.columns(0).headerstyle.font.bold = true
datagrid1.style.add("font-size", "9pt")
mengxianhui.innertext = "【孟宪会之精彩世界】－ 将datagrid输出到excel<a href="http://www.chinaitpower.com/soft/tools/file/index.html" target="_blank">文件</a>"
me.binddatagrid()
end sub
private sub binddatagrid()
cnn.open()
dim reader as oledbdatareader = <a href="http://www.chinaitpower.com/dev/database/mssql/index.html" target="_blank">sql</a>.executereader()
me.datagrid1.datasource = reader
me.datagrid1.databind()
reader.close()
cnn.close()
end sub
private sub writedatagrid2excel()
dim xlsheet as new spreadsheetclass()
cnn.open()
dim reader as oledbdatareader = me.<a href="http://www.chinaitpower.com/dev/database/mssql/index.html" target="_blank">sql</a>.executereader()
dim numbercols as integer = reader.fieldcount
dim row as integer = 2
dim i as integer = 0
输出标题
for i = 0 to numbercols - 1
xlsheet.activesheet.cells(1, i + 1) = reader.getname(i).tostring()
next
输出字段内容
while (reader.read())
for i = 0 to numbercols - 1
xlsheet.activesheet.cells(row, i + 1) = reader.getvalue(i).tostring()
next
row = row + 1
end while
reader.close()
cnn.close()
try
xlsheet.activesheet.export(server.mappath(".") + "\images\" + me.xlfile.text,_
owc.sheetexportactionenum.ssexportactionnone)
catch e as system.runtime.interopservices.comexception
response.write("错误：" + e.message)
end try
end sub
private sub writedatagrid2excel2()
dim xlsheet as new spreadsheetclass()
dim i as integer = 0
dim j as integer = 0
response.end()
输出标题
dim oitem as datagridcolumn
for each oitem in datagrid1.columns
xlsheet.activesheet.cells(1, i + 1) = oitem.headertext
xlsheet.activesheet.range(xlsheet.activesheet.cells(1, 1),_
xlsheet.activesheet.cells(1, i + 1)).font.bold = true
设置格式
xlsheet.range(xlsheet.cells(1, 1), xlsheet.cells(1, i + 1)).font.bold = true
xlsheet.range(xlsheet.cells(1, 1), xlsheet.cells(1, i + 1)).font.color = "red"
i = i + 1
next
dim numbercols as integer = datagrid1.items.item(0).cells.count
输出字段内容
for j = 0 to datagrid1.items.count - 1
for i = 0 to numbercols - 1
xlsheet.range(xlsheet.cells(2, 2), xlsheet.cells(j + 2, i + 1)).font.color = "blue"
xlsheet.range("a2:b14").wraptext = true
xlsheet.range(xlsheet.cells(2, 1), xlsheet.cells(j + 2, i + 1)).autofitcolumns()
xlsheet.activesheet.cells(j + 2, i + 1) = datagrid1.items.item(j).cells(i).text.replace(" ", " ")
next
next
try
xlsheet.activesheet.export(server.mappath(".") + "\images\" + me.xlfile.text,_
owc.sheetexportactionenum.ssexportactionnone)
catch e as system.runtime.interopservices.comexception
response.write("错误：" + e.message)
end try
end sub
private sub exportdatagrid2excel_click(byval sender as object,_
byval e as system.eventargs) handles exportdatagrid2excel.click
if (me.xlfile.text.trim() <> "") then
me.writedatagrid2excel2()
end if
end sub
private sub exportdatabase2excel_click(byval sender as object, _
byval e as system.eventargs) handles exportdatabase2excel.click
if (me.xlfile.text.trim() <> "") then
me.writedatagrid2excel()
end if
end sub
end class
]]>
文章整理：西部数码--专业提供 域名注册、 虚拟主机服务
http://www.west263.com
以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢! |
|
|
